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.SessionAuditDB.java

/**
 * returns terminal logs for user session for host system
 *
 * @param con       DB connection/*from  ww  w  .  ja  v a2 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<>();
    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.nabla.dc.server.handler.fixed_asset.Asset.java

static public <P> boolean validateDepreciationPeriod(final Connection conn, final IAssetRecord asset,
        @Nullable final P pos, final IErrorList<P> errors) throws SQLException, DispatchException {
    final PreparedStatement stmt = StatementFormat.prepare(conn,
            "SELECT t.min_depreciation_period, t.max_depreciation_period"
                    + " FROM fa_asset_category AS t INNER JOIN fa_company_asset_category AS r ON r.fa_asset_category_id=t.id"
                    + " WHERE r.id=?;",
            asset.getCompanyAssetCategoryId());
    try {//from w  w w  . ja  v a2s. c o m
        final ResultSet rs = stmt.executeQuery();
        try {
            if (!rs.next()) {
                errors.add(pos, asset.getCategoryField(), ServerErrors.UNDEFINED_ASSET_CATEGORY_FOR_COMPANY);
                return false;
            }
            if (rs.getInt("min_depreciation_period") > asset.getDepreciationPeriod()
                    || rs.getInt("max_depreciation_period") < asset.getDepreciationPeriod()) {
                errors.add(pos, asset.getDepreciationPeriodField(), CommonServerErrors.INVALID_VALUE);
                return false;
            }
        } finally {
            rs.close();
        }
    } finally {
        stmt.close();
    }
    return true;
}

From source file:com.oracle.tutorial.jdbc.StoredProcedureJavaDBSample.java

public static void getSupplierOfCoffee(String coffeeName, String[] supplierName) throws SQLException {
    Connection con = DriverManager.getConnection("jdbc:default:connection");
    PreparedStatement pstmt = null;
    ResultSet rs = null;/*  w w w  .j  a  v a2  s  .  com*/

    String query = "select SUPPLIERS.SUP_NAME " + "from SUPPLIERS, COFFEES "
            + "where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " + "and ? = COFFEES.COF_NAME";

    pstmt = con.prepareStatement(query);
    pstmt.setString(1, coffeeName);
    rs = pstmt.executeQuery();

    if (rs.next()) {
        supplierName[0] = rs.getString(1);
    } else {
        supplierName[0] = null;
    }
}

From source file:gridool.util.jdbc.JDBCUtils.java

/**
 * Execute an SQL SELECT query with replacement parameters.  The
 * caller is responsible for closing the connection.
 * /*from w  w  w  .j  a  v  a2  s. co  m*/
 * @param conn The connection to execute the query in.
 * @param sql The query to execute.
 * @param params The replacement parameters.
 * @return The object represents ResultSet.
 */
public static ResultSet fetch(Connection conn, String sql, Object[] params) throws SQLException {
    ResultSet rs = null;
    try {
        PreparedStatement stmt = conn.prepareStatement(sql);
        fillStatement(stmt, params);
        verboseQuery(sql, params);
        rs = stmt.executeQuery();
    } catch (SQLException e) {
        rethrow(e, sql, params);
    }
    return rs;
}

From source file:genericepayadmin.AddIpBean.java

public static ArrayList getTreasury(Connection con) throws Exception {
    PreparedStatement ps = null;
    ResultSet rs = null;/*from   w ww  .  j  a  va 2 s  .  c om*/
    ArrayList al = new ArrayList();

    try {
        String sql = "select wbser.active, wbser.id, gdep.dept_name, wbser.ipaddress,wbser.checksum from webservice_validator wbser,generic_dept gdep "
                + " where gdep.DEPT_ID=wbser.deptid ";
        ps = con.prepareStatement(sql);

        rs = ps.executeQuery();
        while (rs.next()) {
            AddIpBean tbean = new AddIpBean();
            tbean.setId(rs.getString("id"));
            tbean.setDept_name(rs.getString("dept_name"));
            tbean.setIpaddress(rs.getString("ipaddress"));
            tbean.setChecksum(rs.getString("checksum"));
            tbean.setStatus(rs.getString("active"));
            al.add(tbean);
        }
    } catch (Exception e) {
        System.out.println(e.getMessage());
    } finally {
        if (ps != null)
            ps.close();
        if (rs != null)
            rs.close();
    }
    return al;
}

From source file:at.becast.youploader.database.SQLite.java

/**
 * Returns the DB Version for App Update purposes
 * /*from   w w w .j a  v a  2  s .  com*/
 * @return An Integer with the current DB Version or 0 if there was a Error getting the Version
 * @throws SQLException
 */
public static int getVersion() throws SQLException {
    PreparedStatement prest = null;
    String sql = "PRAGMA `user_version`";
    prest = c.prepareStatement(sql);
    ResultSet rs = prest.executeQuery();
    if (rs.next()) {
        int version = rs.getInt(1);
        rs.close();
        return version;
    } else {
        return 0;
    }
}

From source file:au.org.ala.sds.GeneraliseOccurrenceLocations.java

private static void run(String startAt) throws SQLException, SearchResultException {
    Connection conn = occurrenceDataSource.getConnection();
    PreparedStatement pst = conn.prepareStatement(
            "SELECT id, scientific_name, latitude, longitude, generalised_metres, raw_latitude, raw_longitude FROM raw_occurrence_record LIMIT ?,?");
    int offset = startAt == null ? 0 : Integer.parseInt(startAt);
    int stride = 10000;
    int recCount = 0;
    pst.setInt(2, stride);//from  w  ww.j  a  v  a 2 s.  c  o  m
    ResultSet rs;

    for (pst.setInt(1, offset); true; offset += stride, pst.setInt(1, offset)) {
        rs = pst.executeQuery();
        if (!rs.isBeforeFirst()) {
            break;
        }
        while (rs.next()) {
            recCount++;

            String rawScientificName = (rs.getString("scientific_name"));
            int id = rs.getInt("id");
            String latitude = rs.getString("latitude");
            String longitude = rs.getString("longitude");
            String generalised_metres = rs.getString("generalised_metres");
            String raw_latitude = rs.getString("raw_latitude");
            String raw_longitude = rs.getString("raw_longitude");

            if (StringUtils.isEmpty(rawScientificName))
                continue;
            if (StringUtils.isEmpty(latitude) || StringUtils.isEmpty(longitude))
                continue;

            // See if it's sensitive
            SensitiveTaxon ss = sensitiveSpeciesFinder.findSensitiveSpecies(rawScientificName);
            if (ss != null) {
                Map<String, String> facts = new HashMap<String, String>();
                facts.put(FactCollection.DECIMAL_LATITUDE_KEY, latitude);
                facts.put(FactCollection.DECIMAL_LONGITUDE_KEY, longitude);

                ValidationService service = ServiceFactory.createValidationService(ss);
                ValidationOutcome outcome = service.validate(facts);
                Map<String, Object> result = outcome.getResult();

                String speciesName = ss.getTaxonName();
                if (StringUtils.isNotEmpty(ss.getCommonName())) {
                    speciesName += " [" + ss.getCommonName() + "]";
                }

                if (!result.get("decimalLatitude").equals(facts.get("decimalLatitude"))
                        || !result.get("decimalLongitude").equals(facts.get("decimalLongitude"))) {
                    if (StringUtils.isEmpty(generalised_metres)) {
                        logger.info("Generalising location for " + id + " '" + rawScientificName
                                + "' using Name='" + speciesName + "', Lat=" + result.get("decimalLatitude")
                                + ", Long=" + result.get("decimalLongitude"));
                        //rawOccurrenceDao.updateLocation(id, result.get("decimalLatitude"), result.get("decimalLongitude"), result.getGeneralisationInMetres(), latitude, longitude);
                    } else {
                        if (generalised_metres != result.get("generalisationInMetres")) {
                            logger.info("Re-generalising location for " + id + " '" + rawScientificName
                                    + "' using Name='" + speciesName + "', Lat=" + result.get("decimalLatitude")
                                    + ", Long=" + result.get("decimalLongitude"));
                            //rawOccurrenceDao.updateLocation(id, result.get("decimalLatitude"), result.get("decimalLongitude"), result.getGeneralisationInMetres());
                        }
                    }
                } else {
                    logger.info("Not generalising location for " + id + " '" + rawScientificName
                            + "' using Name='" + speciesName + "', Lat=" + result.get("decimalLatitude")
                            + ", Long=" + result.get("decimalLongitude") + " - "
                            + result.get("dataGeneralizations"));
                }
            } else {
                // See if was sensitive but not now
                if (StringUtils.isNotEmpty(generalised_metres)) {
                    logger.info("De-generalising location for " + id + " '" + rawScientificName + "', Lat="
                            + raw_latitude + ", Long=" + raw_longitude);
                    //rawOccurrenceDao.updateLocation(id, raw_latitude, raw_longitude, null, null, null);
                }
            }
        }
        rs.close();
        logger.info("Processed " + recCount + " occurrence records.");
    }

    rs.close();
    pst.close();
    conn.close();
}

From source file:edu.jhu.pha.vospace.storage.SwiftStorageManager.java

public static String generateRandomCredentials(final String username) {
    return DbPoolServlet.goSql("Generate random credentials",
            "select username, apikey from storage_users_pool where user_id IS NULL limit 1;",
            new SqlWorker<String>() {
                @Override//from  w  ww. j av a 2 s  .  com
                public String go(Connection conn, PreparedStatement stmt) throws SQLException {
                    ResultSet rs = stmt.executeQuery();
                    if (rs.next()) {
                        String user = rs.getString("username");
                        String password = rs.getString("apikey");
                        PreparedStatement prep = conn.prepareStatement(
                                "update storage_users_pool SET user_id = (select user_id from user_identities where identity = ?) where username = ?");
                        prep.setString(1, username);
                        prep.setString(2, user);
                        prep.execute();

                        logger.debug(username + " " + user);

                        StringWriter writer = new StringWriter();
                        JsonFactory f = new JsonFactory();
                        try {
                            JsonGenerator g = f.createJsonGenerator(writer);

                            g.writeStartObject();
                            g.writeStringField("username", user);
                            g.writeStringField("apikey", password);
                            g.writeEndObject();

                            g.close(); // important: will force flushing of output, close underlying output stream

                            return writer.getBuffer().toString();
                        } catch (JsonGenerationException ex) {
                            throw new InternalServerErrorException(
                                    "Error generating user storage credentials. " + ex.getMessage());
                        } catch (IOException e) {
                            throw new InternalServerErrorException(
                                    "Error generating user storage credentials. " + e.getMessage());
                        }

                    } else
                        throw new PermissionDeniedException("The user does not exist.");
                }
            });

}

From source file:com.concursive.connect.web.modules.communications.utils.EmailUpdatesUtils.java

public static void manageQueue(Connection db, TeamMember teamMember) throws SQLException {
    //Determine if the member is part of any other projects and has a matching email updates preference
    PreparedStatement pst = db.prepareStatement("SELECT count(*) AS record_count " + "FROM project_team pt "
            + "WHERE pt.user_id = ? " + "AND pt.email_updates_schedule = ? ");
    int i = 0;//from w ww. j  av  a 2s .  co m
    pst.setInt(++i, teamMember.getUserId());
    pst.setInt(++i, teamMember.getEmailUpdatesSchedule());
    ResultSet rs = pst.executeQuery();
    int records = 0;
    if (rs.next()) {
        records = rs.getInt("record_count");
    }
    rs.close();
    pst.close();

    if (records == 0) {
        //Delete the queue since it is no longer needed.
        String field = "";
        int emailUpdatesSchedule = teamMember.getEmailUpdatesSchedule();
        if (emailUpdatesSchedule > 0) {
            if (emailUpdatesSchedule == TeamMember.EMAIL_OFTEN) {
                field = "schedule_often";
            } else if (emailUpdatesSchedule == TeamMember.EMAIL_DAILY) {
                field = "schedule_daily";
            } else if (emailUpdatesSchedule == TeamMember.EMAIL_WEEKLY) {
                field = "schedule_weekly";
            } else if (emailUpdatesSchedule == TeamMember.EMAIL_MONTHLY) {
                field = "schedule_monthly";
            }
            i = 0;
            pst = db.prepareStatement(
                    "DELETE FROM email_updates_queue " + "WHERE enteredby = ? AND " + field + " = ? ");
            pst.setInt(++i, teamMember.getUserId());
            pst.setBoolean(++i, true);
            pst.executeUpdate();
            pst.close();
        }
    }
}

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

/**
 * returns system by id/*from   w ww  .j av a  2 s.  c o  m*/
 *
 * @param con DB connection
 * @param id  system id
 * @return system
 */
public static HostSystem getSystem(Connection con, Long id) {

    HostSystem hostSystem = null;

    try {

        PreparedStatement stmt = con.prepareStatement("select * from  system where id=?");
        stmt.setLong(1, id);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            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"));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    return hostSystem;
}