Example usage for java.sql PreparedStatement executeQuery

List of usage examples for java.sql PreparedStatement executeQuery

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. executeQuery

Introduction

In this page you can find the example usage for java.sql PreparedStatement executeQuery.

Prototype

ResultSet executeQuery() throws SQLException;

Source Link

Document

Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.

Usage

From source file:com.synyx.greetingcard.dao.JdbcWhitelistDao.java

/**
 * Returns a List of all domains configured in the whitelist.
 * This method returns an empty List if connection to the database fails.
 * @return a List of the domains//from   w  ww .  j  a v a 2s .co  m
 */
public List<String> getWhitelist() throws DataAccessException {
    List<String> domains = new ArrayList<String>();
    Connection conn = null;
    try {
        conn = DriverManager.getConnection(connection, user, pass);
        if (conn != null) {
            PreparedStatement stmt = conn.prepareStatement("SELECT * FROM " + tableName);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                domains.add(rs.getString(column));
            }
        } else {
            throw new DataAccessException("Couldn't connect to whitelist database");
        }
    } catch (SQLException ex) {
        throw new DataAccessException("Accessing whitelist DB failed", ex);
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
                log.error("Closing connection failed", ex);
            }
        }
    }
    return domains;
}

From source file:com.alibaba.druid.pool.dbcp.TestIdleForKylin.java

public void test_idle() throws Exception {
    MockDriver driver = MockDriver.instance;

    // BasicDataSource dataSource = new BasicDataSource();
    DruidDataSource dataSource = new DruidDataSource();
    dataSource.setUrl("jdbc:mock:xxx");
    dataSource.setDriverClassName("com.alibaba.druid.mock.MockDriver");
    dataSource.setInitialSize(1);/* w w w.j a  v a2s . c o  m*/
    dataSource.setMaxActive(10);
    dataSource.setMaxIdle(10);
    dataSource.setMinIdle(0);
    dataSource.setMinEvictableIdleTimeMillis(50000 * 1);
    dataSource.setTimeBetweenEvictionRunsMillis(500);
    dataSource.setTestWhileIdle(true);
    dataSource.setTestOnBorrow(false);
    dataSource.setValidationQuery("SELECT 1");

    {
        Connection conn = dataSource.getConnection();

        // Assert.assertEquals(dataSource.getInitialSize(), driver.getConnections().size());
        System.out.println("raw size : " + driver.getConnections().size());

        PreparedStatement stmt = conn.prepareStatement("SELECT 1");
        ResultSet rs = stmt.executeQuery();
        rs.close();
        stmt.close();

        conn.close();
        System.out.println("raw size : " + driver.getConnections().size());
    }

    {
        Connection conn = dataSource.getConnection();

        // Assert.assertEquals(dataSource.getInitialSize(), driver.getConnections().size());
        System.out.println("raw size : " + driver.getConnections().size());

        conn.close();
        System.out.println("raw size : " + driver.getConnections().size());
    }

    {
        int count = 4;
        Connection[] connections = new Connection[4];
        for (int i = 0; i < count; ++i) {
            connections[i] = dataSource.getConnection();
        }
        System.out.println("raw size : " + driver.getConnections().size());
        for (int i = 0; i < count; ++i) {
            connections[i].close();
        }
        System.out.println("raw size : " + driver.getConnections().size());

        System.out.println("----------sleep for evict");
        Thread.sleep(dataSource.getMinEvictableIdleTimeMillis() * 2);
        System.out.println("raw size : " + driver.getConnections().size());
    }

    System.out.println("----------raw close all connection");
    for (MockConnection rawConn : driver.getConnections()) {
        rawConn.close();
    }

    Thread.sleep(dataSource.getMinEvictableIdleTimeMillis() * 2);
    System.out.println("raw size : " + driver.getConnections().size());
    {
        Connection conn = dataSource.getConnection();
        System.out.println("raw size : " + driver.getConnections().size());
        conn.close();
        System.out.println("raw size : " + driver.getConnections().size());
    }

    dataSource.close();
}

From source file:net.mms_projects.copy_it.api.http.pages.v1.ClipboardGet.java

public FullHttpResponse onGetRequest(HttpRequest request, Database database, HeaderVerifier headerVerifier)
        throws Exception {
    if (!headerVerifier.getConsumerScope().canRead() || !headerVerifier.getUserScope().canRead())
        throw new ErrorException(NO_READ_PERMISSION);
    PreparedStatement statement = database.getConnection().prepareStatement(SELECT_CONTENT);
    statement.setInt(1, headerVerifier.getUserId());
    ResultSet result = statement.executeQuery();
    if (result.first()) {
        final JSONObject json = new JSONObject();
        json.put(CONTENT, result.getString(DATA));
        json.put(LAST_UPDATED, result.getInt(LAST_UPDATED));
        result.close();//from   www . j  a  va 2  s  .c  o m
        return new DefaultFullHttpResponse(request.getProtocolVersion(), OK,
                Unpooled.copiedBuffer(json.toString(), CharsetUtil.UTF_8));
    }
    result.close();
    throw new NoContentException();
}

From source file:oobbit.orm.Categories.java

public boolean isCategory(String id) throws SQLException {
    PreparedStatement statement = getConnection()
            .prepareStatement("SELECT * FROM categories WHERE category_id = ?;");
    statement.setString(1, id);//w  w w . ja v  a 2  s . c om

    ResultSet query = statement.executeQuery();

    return query.next();
}

From source file:dao.UserSearchQuery.java

/**
 * This method lists all the results for the search text from directories
 * @param conn the connection// w w  w.  j a  v a 2 s  .  com
 * @param collabrumId the collabrumid
 * @return HashSet the set that has the list of moderators for these collabrums.
 * @throws BaseDaoException - when error occurs
 **/
public HashSet run(Connection conn, String sString) throws BaseDaoException {

    if ((RegexStrUtil.isNull(sString) || conn == null)) {
        return null;
    }

    ResultSet rs = null;
    StringBuffer sb = new StringBuffer(
            "select hdlogin.loginid, login, fname, lname, hits, membersince, LEFT(description, 160) as info from hdlogin left join usertab on hdlogin.loginid=usertab.loginid left join yourkeywords on hdlogin.loginid=yourkeywords.loginid left join mykeywords on hdlogin.loginid=mykeywords.loginid where ");

    ArrayList columns = new ArrayList();
    columns.add("login");
    columns.add("email");
    columns.add("description");
    columns.add("fname");
    columns.add("lname");
    columns.add("interests");
    columns.add("yourkeyword");
    columns.add("keyword");
    //columns.add("industry");
    columns.add("city");
    sb.append(sqlSearch.getConstraint(columns, sString));
    sb.append(" group by login order by hits DESC");
    logger.info("search query string" + sb.toString());

    try {
        PreparedStatement stmt = conn.prepareStatement(sb.toString());
        rs = stmt.executeQuery();

        Vector columnNames = null;
        Userpage userpage = null;
        HashSet pendingSet = new HashSet();

        if (rs != null) {
            columnNames = dbutils.getColumnNames(rs);
        } else {
            return null;
        }

        while (rs.next()) {
            userpage = (Userpage) eop.newObject(DbConstants.USER_PAGE);
            for (int j = 0; j < columnNames.size(); j++) {
                if (((String) (columnNames.elementAt(j))).equalsIgnoreCase("membersince")) {
                    try {
                        userpage.setValue(DbConstants.DATE,
                                GlobalConst.dncalendar.getDisplayDate(rs.getTimestamp("membersince")));
                    } catch (ParseException e) {
                        throw new BaseDaoException(
                                "could not parse the date for membersince in UserSearchQuery()"
                                        + rs.getTimestamp("membersince"),
                                e);
                    }
                } else {
                    userpage.setValue((String) columnNames.elementAt(j),
                            (String) rs.getString((String) columnNames.elementAt(j)));
                }
            }
            pendingSet.add(userpage);
        }
        return pendingSet;
    } catch (Exception e) {
        throw new BaseDaoException(
                "Error occured while executing search in userpage run query " + sb.toString(), e);
    }
}

From source file:dk.netarkivet.common.utils.DBUtils.java

/** Returns the version of a table according to schemaversions, or 0
 * for the initial, unnumbered version.//  ww  w.  jav a  2 s .c o  m
 *
 * NB: the provided connection is not closed
 *
 * @param connection connection to the database.
 * @param tablename The name of a table in the database.
 * @return Version of the given table.
 * @throws IOFailure if DB table schemaversions does not exist
 */
public static int getTableVersion(Connection connection, String tablename) throws IOFailure {
    ArgumentNotValid.checkNotNull(connection, "Connection connection");
    ArgumentNotValid.checkNotNullOrEmpty(tablename, "String tablenname");
    PreparedStatement s = null;
    int version = 0;
    try {
        s = connection.prepareStatement("SELECT version FROM schemaversions" + " WHERE tablename = ?");
        s.setString(1, tablename);
        ResultSet res = s.executeQuery();
        if (!res.next()) {
            log.warn("As yet unknown tablename '" + tablename + "' in table schemaversions. The table"
                    + " should be automatically created in the database " + "when it is first needed.");
        } else {
            version = res.getInt(1);
            if (res.wasNull()) {
                log.warn("Null table version for '" + tablename + "'");
            }
        }
        return version;
    } catch (SQLException e) {
        String msg = "SQL Error checking version of table " + tablename + "\n"
                + ExceptionUtils.getSQLExceptionCause(e);
        log.warn(msg, e);
        throw new IOFailure(msg, e);
    } finally {
        closeStatementIfOpen(s);
    }
}

From source file:dao.UserSearchBizAwareQuery.java

/**
 * This method lists all the results for the search text from directories
 * @param conn the connection// w w w. ja  v  a 2 s  .  c  o  m
 * @param collabrumId the collabrumid
 * @return HashSet the set that has the list of moderators for these collabrums.
 * @throws BaseDaoException - when error occurs
 **/
public HashSet run(Connection conn, String sString, String bid) throws BaseDaoException {

    if (RegexStrUtil.isNull(sString) || conn == null || RegexStrUtil.isNull(bid)) {
        return null;
    }

    ResultSet rs = null;
    //StringBuffer sb = new StringBuffer("select distinct c2.login, c2.fname, c2.lname, c1.hits, c1.membersince, LEFT(c1.description, 160) as info from usertab c1 left outer join hdlogin c2 on c1.loginid=c2.loginid where "); 

    StringBuffer sb = new StringBuffer(
            "select login, fname, lname, hits, membersince, LEFT(description, 160) as info, hdlogin.bid, bsearch from business, hdlogin left join usertab on hdlogin.loginid=usertab.loginid left join yourkeywords on hdlogin.loginid=yourkeywords.loginid left join mykeywords on hdlogin.loginid=mykeywords.loginid where business.bid=hdlogin.bid and (");

    ArrayList columns = new ArrayList();
    columns.add("description");
    columns.add("fname");
    columns.add("lname");
    columns.add("interests");
    columns.add("yourkeyword");
    columns.add("keyword");
    //columns.add("industry");
    columns.add("city");
    sb.append(sqlSearch.getConstraint(columns, sString));
    sb.append(") group by login order by hits DESC");
    logger.info("search query string" + sb.toString());

    try {
        PreparedStatement stmt = conn.prepareStatement(sb.toString());
        rs = stmt.executeQuery();

        Vector columnNames = null;
        Userpage userpage = null;
        HashSet pendingSet = new HashSet();

        if (rs != null) {
            columnNames = dbutils.getColumnNames(rs);
        } else {
            return null;
        }

        while (rs.next()) {
            userpage = (Userpage) eop.newObject(DbConstants.USER_PAGE);
            for (int j = 0; j < columnNames.size(); j++) {
                if (((String) (columnNames.elementAt(j))).equalsIgnoreCase("membersince")
                        && (rs.getTimestamp("membersince") != null)) {
                    try {
                        userpage.setValue(DbConstants.DATE,
                                GlobalConst.dncalendar.getDisplayDate(rs.getTimestamp("membersince")));
                    } catch (ParseException e) {
                        throw new BaseDaoException(
                                "could not parse the date for membersince in UserSearchBizAwareQuery()"
                                        + rs.getTimestamp("membersince"),
                                e);
                    }
                } else {
                    userpage.setValue((String) columnNames.elementAt(j),
                            (String) rs.getString((String) columnNames.elementAt(j)));
                }
            }
            pendingSet.add(userpage);
        }
        return pendingSet;
    } catch (Exception e) {
        throw new BaseDaoException(
                "Error occured while executing search in userpage run query " + sb.toString(), e);
    }
}

From source file:oobbit.orm.Categories.java

public Category getOne(String id) throws SQLException, NothingWasFoundException {
    PreparedStatement statement = getConnection()
            .prepareStatement("SELECT * FROM categories WHERE category_id = ?;");
    statement.setString(1, id);//from  w w w  .  j  a v  a  2s.  c o  m

    ResultSet query = statement.executeQuery();

    if (query.next()) {
        Category category = new Category();
        category.parse(query);
        return category;
    } else {
        throw new NothingWasFoundException("No such category!");
    }
}

From source file:com.bounswe2015group5.xplore.AllCommentsServlet.java

private JSONArray getAllComments(String contribId) {
    try {//from ww w  .java  2  s .  com
        JSONArray result = new JSONArray();
        DBConnection conn = new DBConnection();
        String sql = "select " + "Comment.Content, " + "Comment.Date, " + "User2.Name, " + "User2.Surname "
                + "from Comment " + "inner join User2 " + "on Comment.UserID = User2.ID "
                + "where Comment.ContribID = ?;";
        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setInt(1, Integer.parseInt(contribId));
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            JSONObject row = new JSONObject();
            row.put("content", rs.getString("Content"));
            row.put("date", rs.getString("Date"));
            row.put("name", rs.getString("Name"));
            row.put("surname", rs.getString("Surname"));
            result.put(row);
        }
        rs.close();
        stmt.close();
        conn.close();
        return result;
    } catch (SQLException | ClassNotFoundException | NumberFormatException | JSONException e) {
        return new JSONArray();
    }
}

From source file:GameList.java

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    //no get post required

    DatabaseHandler db = new DatabaseHandler();

    conn = db.makeConnection();//w ww . jav  a2 s .c om

    String[][] gameList = new String[3][2];
    gameList[0][0] = "null";
    gameList[0][1] = "null";
    gameList[1][0] = "null";
    gameList[1][1] = "null";
    gameList[2][0] = "null";
    gameList[2][1] = "null";

    String query = "SELECT id,hoster,playercount FROM game";

    try {
        PreparedStatement prStmt = conn.prepareStatement(query);
        ResultSet rs = prStmt.executeQuery();

        int i = 0;

        while (rs.next()) {
            if (Integer.parseInt(rs.getString("playercount")) < 4) {
                //first hoster then id
                gameList[i][0] = rs.getString("hoster");
                gameList[i][1] = rs.getString("id");
                i++;
                if (i == 3)
                    break;
            }
        }

        if (i == 0) {
            json.put("reply", "nogamefound");
            //System.out.println("nogamefound");
        } else {
            json.put("reply", "gamefound");
            //System.out.println("gamefound");
        }

        for (i = 0; i < 3; i++) {
            //gameHost0->hoster & gameId0->1
            json.put("gameHost" + String.valueOf(i), gameList[i][0]);
            json.put("gameId" + String.valueOf(i), gameList[i][1]);
            //System.out.println("gamehost: "+gameList[i][0]+" gameId: "+gameList[i][1]);
        }

        response.setContentType("application/json");
        response.setCharacterEncoding("UTF-8");
        response.getWriter().write(json.toString());

    } catch (SQLException ex) {
        Logger.getLogger(GameList.class.getName()).log(Level.SEVERE, null, ex);
    }

    //System.out.println(json);

    db.closeAllConnections(conn);

}