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.tethrnet.manage.db.SystemStatusDB.java

/**
 * returns all key placement statuses//  ww w . ja  v  a2  s . c  om
 *
 * @param con DB connection object
 * @param userId user id
 */
private static List<HostSystem> getAllSystemStatus(Connection con, Long userId) {

    List<HostSystem> hostSystemList = new ArrayList<HostSystem>();
    try {

        PreparedStatement stmt = con.prepareStatement("select * from status where user_id=? order by id asc");
        stmt.setLong(1, userId);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            HostSystem hostSystem = SystemDB.getSystem(con, rs.getLong("id"));
            hostSystem.setStatusCd(rs.getString("status_cd"));
            hostSystemList.add(hostSystem);
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }
    return hostSystemList;

}

From source file:ca.qc.adinfo.rouge.variable.db.PersistentVariableDb.java

public static Variable getPersitentVariable(DBManager dbManager, String key) {

    Connection connection = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;/*from  w ww.  j  av  a  2  s  . c  om*/

    String sql = "SELECT value, version FROM rouge_persistant_variable WHERE `key` = ?";

    try {
        connection = dbManager.getConnection();
        stmt = connection.prepareStatement(sql);

        stmt.setString(1, key);

        rs = stmt.executeQuery();

        if (rs.next()) {
            JSONObject jSonObject = JSONObject.fromObject(rs.getString("value"));

            return new Variable(key, new RougeObject(jSonObject), rs.getLong("version"));
        } else {
            return null;
        }

    } catch (SQLException e) {

        log.error(e);
        return null;

    } finally {

        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(connection);
    }
}

From source file:com.keybox.manage.db.SystemStatusDB.java

/**
 * returns all key placement statuses/*from  ww w . j  a  va 2 s.  c om*/
 *
 * @param con DB connection object
 * @param userId user id
 */
private static List<HostSystem> getAllSystemStatus(Connection con, Long userId) {

    List<HostSystem> hostSystemList = new ArrayList<>();
    try {

        PreparedStatement stmt = con.prepareStatement("select * from status where user_id=? order by id asc");
        stmt.setLong(1, userId);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            HostSystem hostSystem = SystemDB.getSystem(con, rs.getLong("id"));
            hostSystem.setStatusCd(rs.getString(STATUS_CD));
            hostSystemList.add(hostSystem);
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }
    return hostSystemList;

}

From source file:com.wso2telco.util.DbUtil.java

public static String getContextIDForHashKey(String hashKey) throws AuthenticatorException, SQLException {
    String sessionDataKey = null;

    String sql = "select contextid from sms_hashkey_contextid_mapping where hashkey=?";

    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;// w  ww  . j a v a 2  s .com
    try {
        connection = getConnectDBConnection();
        ps = connection.prepareStatement(sql);
        ps.setString(1, hashKey);
        rs = ps.executeQuery();
        while (rs.next()) {
            sessionDataKey = rs.getString("contextid");
        }
    } catch (SQLException e) {
        handleException(e.getMessage(), e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, rs, ps);
    }
    return sessionDataKey;
}

From source file:fll.db.NonNumericNominees.java

/**
 * Get all subjective categories know for the specified tournament.
 * /*from w  ww . ja  va2 s  .co m*/
 * @throws SQLException
 */
public static Set<String> getCategories(final Connection connection, final int tournamentId)
        throws SQLException {
    final Set<String> result = new HashSet<>();
    PreparedStatement get = null;
    ResultSet rs = null;
    try {
        get = connection
                .prepareStatement("SELECT DISTINCT category FROM non_numeric_nominees WHERE tournament = ?");
        get.setInt(1, tournamentId);
        rs = get.executeQuery();
        while (rs.next()) {
            final String category = rs.getString(1);
            result.add(category);
        }
    } finally {
        SQLFunctions.close(rs);
        SQLFunctions.close(get);
    }
    return result;
}

From source file:com.tethrnet.manage.db.UserDB.java

/**
 * returns users based on sort order defined
 * @param sortedSet object that defines sort order
 * @return sorted user list//from   w ww  .ja  va 2s . c  om
 */
public static SortedSet getUserSet(SortedSet sortedSet) {

    ArrayList<User> userList = new ArrayList<User>();

    String orderBy = "";
    if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
        orderBy = "order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
    }
    String sql = "select * from  users where enabled=true " + orderBy;

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            User user = new User();
            user.setId(rs.getLong("id"));
            user.setEmail(rs.getString("email"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setAuthType(rs.getString("auth_type"));
            user.setUserType(rs.getString("user_type"));
            userList.add(user);

        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);

    sortedSet.setItemList(userList);
    return sortedSet;
}

From source file:com.chaosinmotion.securechat.server.commands.ForgotPassword.java

/**
 * Process a forgot password request. This generates a token that the
 * client is expected to return with the change password request.
 * @param requestParams//from  ww w. j ava  2s . c o m
 * @throws SQLException 
 * @throws IOException 
 * @throws ClassNotFoundException 
 * @throws JSONException 
 * @throws NoSuchAlgorithmException 
 */

public static void processRequest(JSONObject requestParams)
        throws SQLException, ClassNotFoundException, IOException, NoSuchAlgorithmException, JSONException {
    String username = requestParams.optString("username");

    /*
     * Step 1: Convert username to the userid for this
     */
    Connection c = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    int userID = 0;
    String retryID = UUID.randomUUID().toString();

    try {
        c = Database.get();
        ps = c.prepareStatement("SELECT userid " + "FROM Users " + "WHERE username = ?");
        ps.setString(1, username);
        rs = ps.executeQuery();
        if (rs.next()) {
            userID = rs.getInt(1);
        }

        if (userID == 0)
            return;
        ps.close();
        rs.close();

        /*
         * Step 2: Generate the retry token and insert into the forgot 
         * database with an expiration date 1 hour from now.
         */

        Timestamp ts = new Timestamp(System.currentTimeMillis() + 3600000);
        ps = c.prepareStatement("INSERT INTO ForgotPassword " + "    ( userid, token, expires ) " + "VALUES "
                + "    ( ?, ?, ?)");
        ps.setInt(1, userID);
        ps.setString(2, retryID);
        ps.setTimestamp(3, ts);
        ps.execute();
    } finally {
        if (rs != null)
            rs.close();
        if (ps != null)
            ps.close();
        if (c != null)
            c.close();
    }

    /*
     * Step 3: formulate a JSON string with the retry and send
     * to the user. The format of the command we send is:
     * 
     * { "cmd": "forgotpassword", "token": token }
     */

    JSONObject obj = new JSONObject();
    obj.put("cmd", "forgotpassword");
    obj.put("token", retryID);
    MessageQueue.getInstance().enqueueAdmin(userID, obj.toString(4));
}

From source file:com.tethrnet.manage.db.UserDB.java

/**
 * returns all admin users based on sort order defined
 * @param sortedSet object that defines sort order
 * @return sorted user list//ww w.j a  v  a2  s  .c  o  m
 */
public static SortedSet getAdminUserSet(SortedSet sortedSet) {

    ArrayList<User> userList = new ArrayList<User>();

    String orderBy = "";
    if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
        orderBy = "order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
    }
    String sql = "select * from  users where enabled=true and user_type like '" + User.ADMINISTRATOR + "' "
            + orderBy;

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            User user = new User();
            user.setId(rs.getLong("id"));
            user.setEmail(rs.getString("email"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setAuthType(rs.getString("auth_type"));
            user.setUserType(rs.getString("user_type"));
            userList.add(user);

        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);

    sortedSet.setItemList(userList);
    return sortedSet;
}

From source file:com.wso2telco.core.mnc.resolver.dao.OperatorDAO.java

public static String getOperatorByMCCMNC(String mcc, String mnc) throws MobileNtException {

    Connection conn = null;//w ww .  j  a  va 2 s . com
    PreparedStatement ps = null;
    ResultSet rs = null;
    String operator = null;

    try {

        String sql = "SELECT operatorname FROM operators WHERE mcc = ? AND mnc = ?";

        conn = getDBConnection();
        ps = conn.prepareStatement(sql);
        ps.setString(1, mcc);
        ps.setString(2, mnc);
        rs = ps.executeQuery();

        while (rs.next()) {

            operator = rs.getString("operatorname");
            log.debug("operator in getOperatorByMCCMNC: " + operator);
        }
    } catch (SQLException e) {

        log.error("database operation error in getOperatorByMCCMNC : ", e);
        handleException("Error occured while getting operator for mcc : " + mcc + " and mnc : " + mnc
                + "from the database", e);
    } catch (Exception e) {

        log.error("error in getOperatorByMCCMNC : ", e);
        handleException("Error occured while getting operator for mcc : " + mcc + " and mnc : " + mnc
                + "from the database", e);
    } finally {
        DbUtils.closeAllConnections(ps, conn, rs);
    }

    return operator;
}

From source file:com.microsoft.sqlserver.jdbc.connection.PoolingTest.java

/**
 * setup connection, get connection from pool, and test threads
 * /* w w w. jav a  2  s.  co  m*/
 * @param ds
 * @throws SQLException
 */
private static void connect(DataSource ds) throws SQLException {
    Connection con = null;
    PreparedStatement pst = null;
    ResultSet rs = null;

    try {
        con = ds.getConnection();
        pst = con.prepareStatement("SELECT SUSER_SNAME()");
        pst.setQueryTimeout(5);
        rs = pst.executeQuery();

        // TODO : we are commenting this out due to AppVeyor failures. Will investigate later.
        // assertTrue(countTimeoutThreads() >= 1, "Timeout timer is missing.");

        while (rs.next()) {
            rs.getString(1);
        }
    } finally {
        if (rs != null) {
            rs.close();
        }

        if (pst != null) {
            pst.close();
        }

        if (con != null) {
            con.close();
        }
    }
}