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 the first system that authorized keys has not been tried
 *
 * @param userId user id//from ww  w  . jav a 2 s .  com
 * @return hostSystem systems for authorized_keys replacement
 */
public static HostSystem getNextPendingSystem(Long userId) {

    HostSystem hostSystem = null;
    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(
                "select * from status where (status_cd like ? or status_cd like ? or status_cd like ?) and user_id=? order by id asc");
        stmt.setString(1, HostSystem.INITIAL_STATUS);
        stmt.setString(2, HostSystem.AUTH_FAIL_STATUS);
        stmt.setString(3, HostSystem.PUBLIC_KEY_FAIL_STATUS);
        stmt.setLong(4, userId);
        ResultSet rs = stmt.executeQuery();

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

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

}

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

/**
 * returns the first system that authorized keys has not been tried
 *
 * @param userId user id//from  w  w  w .j  ava2  s  .  c  o  m
 * @return hostSystem systems for authorized_keys replacement
 */
public static HostSystem getNextPendingSystem(Long userId) {

    HostSystem hostSystem = null;
    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(
                "select * from status where (status_cd like ? or status_cd like ? or status_cd like ?) and user_id=? order by id asc");
        stmt.setString(1, HostSystem.INITIAL_STATUS);
        stmt.setString(2, HostSystem.AUTH_FAIL_STATUS);
        stmt.setString(3, HostSystem.PUBLIC_KEY_FAIL_STATUS);
        stmt.setLong(4, userId);
        ResultSet rs = stmt.executeQuery();

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

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

}

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

/**
 * returns all system ids for user//from www  .  ja  v a 2 s  .c  o  m
 *
 * @param con    DB connection
 * @param userId user id
 * @return system
 */
public static List<Long> getAllSystemIdsForUser(Connection con, Long userId) {

    List<Long> systemIdList = new ArrayList<Long>();

    try {
        PreparedStatement stmt = con.prepareStatement(
                "select distinct system_id from  system_map m, user_map um where m.profile_id=um.profile_id and um.user_id=?");
        stmt.setLong(1, userId);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            systemIdList.add(rs.getLong("system_id"));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    return systemIdList;

}

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

/**
 * returns public keys based on sort order defined
 *
 * @param sortedSet object that defines sort order
 * @param userId user id// w w w  .j  ava2s .  com
 * @return sorted script list
 */
public static SortedSet getPublicKeySet(SortedSet sortedSet, Long userId) {

    ArrayList<PublicKey> publicKeysList = new ArrayList<>();

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

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        stmt.setLong(1, userId);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            PublicKey publicKey = new PublicKey();
            publicKey.setId(rs.getLong("id"));
            publicKey.setKeyNm(rs.getString(KEY_NM));
            publicKey.setPublicKey(rs.getString(PUBLIC_KEY));
            publicKey.setProfile(ProfileDB.getProfile(con, rs.getLong(PROFILE_ID)));
            publicKey.setType(SSHUtil.getKeyType(publicKey.getPublicKey()));
            publicKey.setFingerprint(SSHUtil.getFingerprint(publicKey.getPublicKey()));
            publicKey.setCreateDt(rs.getTimestamp(CREATE_DT));
            publicKeysList.add(publicKey);

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

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

    sortedSet.setItemList(publicKeysList);
    return sortedSet;
}

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

/**
 * returns all system ids for user/*from w w  w .  j  av a 2s.c  om*/
 *
 * @param con    DB connection
 * @param userId user id
 * @return system
 */
public static List<Long> getAllSystemIdsForUser(Connection con, Long userId) {

    List<Long> systemIdList = new ArrayList<>();

    try {
        PreparedStatement stmt = con.prepareStatement(
                "select distinct system_id from  system_map m, user_map um where m.profile_id=um.profile_id and um.user_id=?");
        stmt.setLong(1, userId);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            systemIdList.add(rs.getLong("system_id"));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    return systemIdList;

}

From source file:com.zimbra.cs.mailbox.util.MetadataDump.java

private static List<Row> getRevisionRows(DbConnection conn, int groupId, int mboxId, int itemId,
        boolean fromDumpster) throws ServiceException {
    PreparedStatement stmt = null;
    ResultSet rs = null;/* w w  w.  j a v a 2  s.  c o m*/
    try {
        String sql = "SELECT * FROM " + DbMailItem.getRevisionTableName(groupId, fromDumpster)
                + " WHERE mailbox_id = " + mboxId + " AND item_id = " + itemId
                + " ORDER BY mailbox_id, item_id, version DESC";
        stmt = conn.prepareStatement(sql);
        rs = stmt.executeQuery();
        List<Row> rows = new ArrayList<Row>();
        while (rs.next()) {
            Row row = new Row();
            ResultSetMetaData rsMeta = rs.getMetaData();
            int cols = rsMeta.getColumnCount();
            for (int i = 1; i <= cols; i++) {
                String colName = rsMeta.getColumnName(i);
                String colValue = rs.getString(i);
                if (rs.wasNull())
                    colValue = null;
                row.addColumn(colName, colValue);
            }
            rows.add(row);
        }
        return rows;
    } catch (SQLException e) {
        throw ServiceException.INVALID_REQUEST("No such item: mbox=" + mboxId + ", item=" + itemId, e);
    } finally {
        DbPool.closeResults(rs);
        DbPool.closeStatement(stmt);
    }
}

From source file:dsd.dao.DAOProvider.java

/**
 * the insert row function done in a secure way
 * //from  w ww.  j  av  a 2 s . co m
 * @param table
 * @param fields
 * @param con
 * @param valueArray
 * @return
 * @throws SQLException
 */
public static int InsertRowSecure(String table, String fields, Connection con, Object[] valueArray)
        throws SQLException {
    try {
        String values = "";
        if (valueArray.length > 0) {
            values = "?";
        }
        for (int i = 1; i < valueArray.length; i++) {
            values += ",?";
        }

        PreparedStatement command = con
                .prepareStatement(String.format("insert into %s (%s) values (%s)", table, fields, values));

        for (int i = 0; i < valueArray.length; i++) {
            SetParameter(command, valueArray[i], i + 1);
        }

        command.executeUpdate();

        command = con.prepareStatement(String.format("select Max(ID) from %s", table));
        ResultSet rs = command.executeQuery();
        rs.next();

        return rs.getInt(1);
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    return 0;
}

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

/**
 * returns public key base on id//www .  j av a 2 s  . com
 *
 * @param con         DB connection
 * @param publicKeyId key id
 * @return script object
 */
public static PublicKey getPublicKey(Connection con, Long publicKeyId) {

    PublicKey publicKey = null;
    try {
        PreparedStatement stmt = con.prepareStatement("select * from  public_keys where id=?");
        stmt.setLong(1, publicKeyId);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            publicKey = new PublicKey();
            publicKey.setId(rs.getLong("id"));
            publicKey.setKeyNm(rs.getString("key_nm"));
            publicKey.setPublicKey(rs.getString("public_key"));
            publicKey.setProfile(ProfileDB.getProfile(con, rs.getLong("profile_id")));
            publicKey.setType(rs.getString("type"));
            publicKey.setFingerprint(rs.getString("fingerprint"));
            publicKey.setCreateDt(rs.getTimestamp("create_dt"));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    return publicKey;
}

From source file:com.concursive.connect.web.modules.common.social.rating.dao.Rating.java

public static int queryUserRating(Connection db, int userId, int objectId, String table, String uniqueField)
        throws SQLException {
    int existingVote = -1;
    PreparedStatement pst = db.prepareStatement(
            "SELECT rating FROM " + table + "_rating WHERE " + uniqueField + " = ? AND enteredby = ? ");
    pst.setInt(1, objectId);//from   w ww . j av a 2 s .  c o m
    pst.setInt(2, userId);
    ResultSet rs = pst.executeQuery();
    if (rs.next()) {
        existingVote = rs.getInt("rating");
    }
    rs.close();
    pst.close();
    return existingVote;
}

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

/**
 * returns public key base on id/*from w ww. j av  a2  s. c  om*/
 *
 * @param con         DB connection
 * @param publicKeyId key id
 * @return script object
 */
public static PublicKey getPublicKey(Connection con, Long publicKeyId) {

    PublicKey publicKey = null;
    try {
        PreparedStatement stmt = con.prepareStatement("select * from  public_keys where id=?");
        stmt.setLong(1, publicKeyId);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            publicKey = new PublicKey();
            publicKey.setId(rs.getLong("id"));
            publicKey.setKeyNm(rs.getString(KEY_NM));
            publicKey.setPublicKey(rs.getString(PUBLIC_KEY));
            publicKey.setProfile(ProfileDB.getProfile(con, rs.getLong(PROFILE_ID)));
            publicKey.setType(rs.getString("type"));
            publicKey.setFingerprint(rs.getString("fingerprint"));
            publicKey.setCreateDt(rs.getTimestamp(CREATE_DT));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    return publicKey;
}