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:edu.jhu.pha.vospace.oauth.MySQLOAuthProvider2.java

/**
 * Returns the user the share is made for
 * @param shareId//from  ww w .j av  a 2  s . co m
 * @return
 */
public static synchronized List<String> getShareUsers(final String shareId) {
    return DbPoolServlet.goSql("Get share user logins",
            "select identity from user_identities JOIN user_groups ON user_groups.user_id = user_identities.user_id JOIN container_shares ON user_groups.group_id = container_shares.group_id AND container_shares.share_id = ?",
            new SqlWorker<List<String>>() {
                @Override
                public List<String> go(Connection conn, PreparedStatement stmt) throws SQLException {
                    stmt.setString(1, shareId);
                    List<String> returnVect = new Vector<String>();
                    ResultSet rs = stmt.executeQuery();
                    while (rs.next()) {
                        returnVect.add(rs.getString(1));
                    }
                    return returnVect;
                }
            });
}

From source file:com.sql.SystemError.java

/**
 * Gathers a list of errors based on type and count total of them
 *
 * @return//from w  ww .  j a  v  a  2 s  . co  m
 */
public static List<SystemErrorModel> getErrorCounts() {
    List<SystemErrorModel> list = new ArrayList();
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "SELECT exceptionType, COUNT(*) AS 'num' " + "FROM SystemError "
                + "WHERE timeOccurred >= CAST(CURRENT_TIMESTAMP AS DATE) " + "AND username != 'andrew.schmidt' "
                + "AND username != 'anthony.perk' " + "GROUP BY exceptionType";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()) {
            SystemErrorModel item = new SystemErrorModel();
            item.setExceptionType(rs.getString("exceptionType") == null ? "" : rs.getString("exceptionType"));
            item.setNumber(rs.getInt("num"));
            list.add(item);
        }
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
    return list;
}

From source file:com.ibm.research.rdf.store.jena.impl.DB2Graph.java

private static ResultSet getRS(PreparedStatement stmt, List<String> params, boolean isException)
        throws SQLException {
    for (int i = 0; i < params.size(); i++) {
        if (isException) {
            stmt.setObject(i + 1, null);
        } else {//from  ww  w  .j a  v a  2s.c  om
            stmt.setObject(i + 1, params.get(i));
        }
    }
    return stmt.executeQuery();
}

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

/**
 * returns user base on id//from w  w  w . ja v  a 2 s.co m
 * @param con DB connection
 * @param userId user id
 * @return user object
 */
public static User getUser(Connection con, Long userId) {

    User user = null;
    try {
        PreparedStatement stmt = con.prepareStatement("select * from  users where id=?");
        stmt.setLong(1, userId);
        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.setPassword(rs.getString("password"));
            user.setAuthType(rs.getString("auth_type"));
            user.setUserType(rs.getString("user_type"));
            user.setSalt(rs.getString("salt"));
            user.setProfileList(UserProfileDB.getProfilesByUser(con, userId));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    return user;
}

From source file:FacultyAdvisement.StudentRepository.java

public static Student read(DataSource ds, String key) throws SQLException {
    String studentSQL = "SElECT * FROM STUDENT JOIN USERTABLE on EMAIL = USERNAME WHERE EMAIL = ?";
    Student student = new Student();

    if (ds == null) {
        throw new SQLException("ds is null; Can't get data source");
    }//  w  ww. j  a va  2s  .  c  o  m

    Connection conn = ds.getConnection();

    if (conn == null) {
        throw new SQLException("conn is null; Can't get db connection");
    }

    try {

        PreparedStatement sqlStatement = conn.prepareStatement(studentSQL);

        sqlStatement.setString(1, key);

        ResultSet result = sqlStatement.executeQuery();
        while (result.next()) {
            student.setId(result.getString("STUID"));
            student.setFirstName(result.getString("firstname"));
            student.setLastName(result.getString("lastname"));
            student.setMajorCode(result.getString("majorcode"));
            student.setPhoneNumber(result.getString("phone"));
            student.setUsername(key);
            student.setPassword(result.getString("password"));
        }

    } finally {
        conn.close();
    }

    return student;
}

From source file:FacultyAdvisement.StudentRepository.java

public static Student readById(DataSource ds, String key) throws SQLException {
    String studentSQL = "SElECT * FROM STUDENT WHERE STUID = ?";
    Student student = new Student();

    if (ds == null) {
        throw new SQLException("ds is null; Can't get data source");
    }//from  ww  w . ja va  2 s .  c o  m

    Connection conn = ds.getConnection();

    if (conn == null) {
        throw new SQLException("conn is null; Can't get db connection");
    }

    try {

        PreparedStatement sqlStatement = conn.prepareStatement(studentSQL);

        sqlStatement.setString(1, key);

        ResultSet result = sqlStatement.executeQuery();
        while (result.next()) {
            student.setId(key);
            student.setFirstName(result.getString("firstname"));
            student.setLastName(result.getString("lastname"));
            student.setMajorCode(result.getString("majorcode"));
            student.setPhoneNumber(result.getString("phone"));
            student.setUsername(result.getString("email"));

        }

    } finally {
        conn.close();
    }

    return student;
}

From source file:FacultyAdvisement.StudentRepository.java

public static String getPicture(DataSource ds, String key) throws SQLException {

    Blob image = null;//from   w w  w.  ja va  2  s  . c  o m

    Connection conn = ds.getConnection();
    if (conn == null) {
        throw new SQLException("conn is null; Can't get db connection");
    }
    try {
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM USERTABLE WHERE USERNAME = ?");
        ps.setString(1, key);
        ResultSet result = ps.executeQuery();
        while (result.next()) {
            image = result.getBlob("IMAGE");
        }
    } finally {
        conn.close();
    }

    if (image != null) {
        return "ImageServlet?username=" + key;
    } else {
        return "/resources/default-image.png";
    }
}

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

/**
 * returns the shared secret based on user id
 *
 * @param userId user id//from w  w  w. ja  va 2 s. co m
 * @return auth object
 */
public static String getSharedSecret(Long userId) {

    String sharedSecret = null;
    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement("select * from users where id like ?");
        stmt.setLong(1, userId);
        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            sharedSecret = EncryptionUtil.decrypt(rs.getString("otp_secret"));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    return sharedSecret;

}

From source file:com.novartis.opensource.yada.util.YADAUtils.java

/**
 * One-liner execution of a jdbc-parameter-less sql statement, returning an SQL {@link java.sql.ResultSet}.
 * <strong>Note: This method opens a db connection but DOES NOT CLOSE IT. 
 * Use the static method {@link ConnectionFactory#releaseResources(ResultSet)} to close it from 
 * the calling method</strong>//from   w  w  w .  j  a v a 2s  .  c  o m
 * @param sql the query to execute
 * @return a {@link java.sql.ResultSet} object containing the result of the query
 * @throws YADAConnectionException when the datasource is inaccessible
 * @throws YADASQLException when the JDBC configuration or execution fails
 */
public static ResultSet executePreparedStatement(String sql) throws YADAConnectionException, YADASQLException {
    ResultSet rs = null;
    try {
        Connection c = ConnectionFactory.getConnectionFactory().getConnection(ConnectionFactory.YADA_APP);
        PreparedStatement p = c.prepareStatement(sql);
        rs = p.executeQuery();
    } catch (SQLException e) {
        throw new YADASQLException(e.getMessage(), e);
    }
    return rs;
}

From source file:ca.qc.adinfo.rouge.leaderboard.db.LeaderboardDb.java

public static HashMap<String, Leaderboard> getLeaderboards(DBManager dbManager) {

    Connection connection = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;/*  w ww .  j  a v a2  s. c o m*/
    HashMap<String, Leaderboard> returnValue = new HashMap<String, Leaderboard>();

    String sql = "SELECT `key` FROM rouge_leaderboards;";

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

        rs = stmt.executeQuery();

        while (rs.next()) {

            String key = rs.getString("key");
            Leaderboard leaderboard = getLeaderboard(dbManager, key);

            if (leaderboard == null) {
                returnValue.put(key, new Leaderboard(key));
            } else {
                returnValue.put(key, leaderboard);
            }
        }

        return returnValue;

    } catch (SQLException e) {
        log.error(stmt);
        log.error(e);
        return null;

    } finally {

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