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.keybox.manage.db.SystemDB.java

/**
 * returns all system ids/* w ww  .  ja  va  2  s. com*/
 *
 * @param con DB connection
 * @return system
 */
public static List<Long> getAllSystemIds(Connection con) {

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

    try {
        PreparedStatement stmt = con.prepareStatement("select * from  system");
        ResultSet rs = stmt.executeQuery();

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

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

    return systemIdList;

}

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

/**
 * returns a list of system ids for a given profile
 *
 * @param con       DB con/*from  ww  w  . j ava  2 s  .  c  om*/
 * @param profileId profile id
 * @return list of host systems
 */
public static List<Long> getSystemIdsByProfile(Connection con, Long profileId) {

    List<Long> systemIdList = new ArrayList<Long>();
    try {
        PreparedStatement stmt = con.prepareStatement(
                "select * from  system s, system_map m where s.id=m.system_id and m.profile_id=? order by display_nm asc");
        stmt.setLong(1, profileId);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            systemIdList.add(rs.getLong("id"));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);
    } catch (Exception e) {
        e.printStackTrace();
    }
    return systemIdList;
}

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

/**
 * returns user base on username/*from w w  w. j  av  a 2 s  . co m*/
 *
 * @param con DB connection
 * @param uid username id
 * @return user object
 */
public static User getUserByUID(Connection con, String uid) {

    User user = null;
    try {
        PreparedStatement stmt = con
                .prepareStatement("select * from  users where lower(username) like lower(?) and enabled=true");
        stmt.setString(1, uid);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            user = new User();
            user.setId(rs.getLong("id"));
            user.setEmail(rs.getString("email"));
            user.setUsername(rs.getString("username"));
            user.setUserType(rs.getString("user_type"));
            user.setProfileList(UserProfileDB.getProfilesByUser(con, user.getId()));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    return user;
}

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

private static int lookupMailboxIdFromEmail(DbConnection conn, String email)
        throws SQLException, ServiceException {
    PreparedStatement stmt = null;
    ResultSet rs = null;// ww  w  . j a  v a 2  s. c  o m
    try {
        String sql = "SELECT id FROM mailbox WHERE comment=?";
        stmt = conn.prepareStatement(sql);
        stmt.setString(1, email.toUpperCase());
        rs = stmt.executeQuery();
        if (!rs.next())
            throw ServiceException.INVALID_REQUEST("Account " + email + " not found on this host", null);
        return rs.getInt(1);
    } finally {
        DbPool.closeResults(rs);
        DbPool.closeStatement(stmt);
    }
}

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

/**
 * returns user base on username//from  ww w . j  av  a2  s. c o m
 *
 * @param con DB connection
 * @param uid username id
 * @return user object
 */
public static User getUserByUID(Connection con, String uid) {

    User user = null;
    try {
        PreparedStatement stmt = con
                .prepareStatement("select * from  users where lower(username) like lower(?) and enabled=true");
        stmt.setString(1, uid);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            user = new User();
            user.setId(rs.getLong("id"));
            user.setFirstNm(rs.getString("first_nm"));
            user.setLastNm(rs.getString("last_nm"));
            user.setEmail(rs.getString("email"));
            user.setUsername(rs.getString("username"));
            user.setUserType(rs.getString("user_type"));
            user.setProfileList(UserProfileDB.getProfilesByUser(con, user.getId()));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    return user;
}

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

/**
 * returns all systems//  w  ww .j av a  2s. c o m
 *
 * @return system list
 */
public static List<HostSystem> getAllSystems() {

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

    Connection con = null;

    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement("select * from  system");
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            HostSystem hostSystem = new HostSystem();
            hostSystem.setId(rs.getLong("id"));
            hostSystem.setDisplayNm(rs.getString("display_nm"));
            hostSystem.setUser(rs.getString("user"));
            hostSystem.setHost(rs.getString("host"));
            hostSystem.setPort(rs.getInt("port"));
            hostSystem.setAuthorizedKeys(rs.getString("authorized_keys"));
            hostSystem.setStatusCd(rs.getString("status_cd"));
            hostSystemList.add(hostSystem);
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    DBUtils.closeConn(con);

    return hostSystemList;

}

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

/**
 * returns terminal logs for user session for host system
 *
 * @param con       DB connection//from w ww . j a  va 2 s. c  o  m
 * @param sessionId session id
 * @return session output for session
 */
public static List<HostSystem> getHostSystemsForSession(Connection con, Long sessionId) {

    List<HostSystem> hostSystemList = new ArrayList<HostSystem>();
    try {
        PreparedStatement stmt = con.prepareStatement(
                "select distinct instance_id, system_id from terminal_log where session_id=?");
        stmt.setLong(1, sessionId);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            HostSystem hostSystem = SystemDB.getSystem(con, rs.getLong("system_id"));
            hostSystem.setInstanceId(rs.getInt("instance_id"));
            hostSystemList.add(hostSystem);
        }

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

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

}

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

public static ReturnResult processRequest(Login.UserInfo userinfo, JSONObject requestParams)
        throws ClassNotFoundException, SQLException, IOException {
    String deviceid = requestParams.optString("deviceid");
    MessageReturnResult mrr = new MessageReturnResult();

    /*//  w  w  w . j  a v  a2s.  c o m
     * Save message to the database.
     */

    Connection c = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        /*
         * Get the device ID for this device. Verify it belongs to the
         * user specified
         */
        c = Database.get();
        ps = c.prepareStatement("SELECT deviceid " + "FROM Devices " + "WHERE deviceuuid = ? AND userid = ?");
        ps.setString(1, deviceid);
        ps.setInt(2, userinfo.getUserID());
        rs = ps.executeQuery();

        int deviceID = 0;
        if (rs.next()) {
            deviceID = rs.getInt(1);
        }

        rs.close();
        ps.close();
        if (deviceID == 0) {
            return new ReturnResult(Errors.ERROR_UNKNOWNDEVICE, "Unknown device");
        }

        /*
         * Run query to get messages
         */

        ps = c.prepareStatement("SELECT Messages.messageid, " + "    Messages.senderid, "
                + "    Users.username, " + "    Messages.toflag, " + "    Messages.received, "
                + "    Messages.message " + "FROM Messages, Users " + "WHERE Messages.deviceid = ? "
                + "  AND Messages.senderid = Users.userid");
        ps.setInt(1, deviceID);

        rs = ps.executeQuery();
        while (rs.next()) {
            int messageID = rs.getInt(1);
            int senderID = rs.getInt(2);
            String senderName = rs.getString(3);
            boolean toflag = rs.getBoolean(4);
            Timestamp received = rs.getTimestamp(5);
            byte[] message = rs.getBytes(6);

            mrr.addMessage(messageID, senderID, senderName, toflag, received, message);
        }

        /*
         * Return messages
         */
        return mrr;
    } finally {
        if (rs != null)
            rs.close();
        if (ps != null)
            ps.close();
        if (c != null)
            c.close();
    }
}

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

/**
 * returns a list of systems for a given profile
 *
 * @param con       DB connection/*from  ww w .  ja v a 2s  . c om*/
 * @param profileId profile id
 * @return list of host systems
 */
public static List<HostSystem> getSystemsByProfile(Connection con, Long profileId) {

    List<HostSystem> hostSystemList = new ArrayList<HostSystem>();
    try {
        PreparedStatement stmt = con.prepareStatement(
                "select * from  system s, system_map m where s.id=m.system_id and m.profile_id=? order by display_nm asc");
        stmt.setLong(1, profileId);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            HostSystem hostSystem = new HostSystem();
            hostSystem.setId(rs.getLong("id"));
            hostSystem.setDisplayNm(rs.getString("display_nm"));
            hostSystem.setUser(rs.getString("user"));
            hostSystem.setHost(rs.getString("host"));
            hostSystem.setPort(rs.getInt("port"));
            hostSystem.setAuthorizedKeys(rs.getString("authorized_keys"));
            hostSystem.setEnabled(rs.getBoolean("enabled"));
            hostSystemList.add(hostSystem);
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);
    } catch (Exception e) {
        e.printStackTrace();
    }
    return hostSystemList;
}

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

/**
 * returns all systems//  ww  w . ja v a 2 s .co m
 *
 * @return system list
 */
public static List<HostSystem> getAllSystems() {

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

    Connection con = null;

    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement("select * from  system");
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            HostSystem hostSystem = new HostSystem();
            hostSystem.setId(rs.getLong("id"));
            hostSystem.setDisplayNm(rs.getString(DISPLAY_NM));
            hostSystem.setUser(rs.getString("user"));
            hostSystem.setHost(rs.getString("host"));
            hostSystem.setPort(rs.getInt("port"));
            hostSystem.setAuthorizedKeys(rs.getString(AUTHORIZED_KEYS));
            hostSystem.setStatusCd(rs.getString(STATUS_CD));
            hostSystemList.add(hostSystem);
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    DBUtils.closeConn(con);

    return hostSystemList;

}