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:fll.db.NonNumericNominees.java

/**
 * Get all nominees in the specified category.
 * //w ww.ja v a 2s .c om
 * @throws SQLException
 */
public static Set<Integer> getNominees(final Connection connection, final int tournamentId,
        final String category) throws SQLException {
    final Set<Integer> result = new HashSet<>();
    PreparedStatement get = null;
    ResultSet rs = null;
    try {
        get = connection.prepareStatement(
                "SELECT DISTINCT team_number FROM non_numeric_nominees" + " WHERE tournament = ?" //
                        + " AND category = ?");
        get.setInt(1, tournamentId);
        get.setString(2, category);
        rs = get.executeQuery();
        while (rs.next()) {
            final int team = rs.getInt(1);
            result.add(team);
        }
    } finally {
        SQLFunctions.close(rs);
        SQLFunctions.close(get);
    }

    return result;
}

From source file:ca.qc.adinfo.rouge.achievement.db.AchievementDb.java

public static HashMap<String, Achievement> getAchievementList(DBManager dbManager) {

    Connection connection = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;/*from  w  ww .  j  a  v  a2s  .c  om*/
    HashMap<String, Achievement> returnValue = new HashMap<String, Achievement>();

    String sql = "SELECT `key`, `name`, `point_value`, `total` FROM rouge_achievements";
    try {
        connection = dbManager.getConnection();
        stmt = connection.prepareStatement(sql);

        rs = stmt.executeQuery();

        while (rs.next()) {

            String key = rs.getString("key");

            Achievement achievement = new Achievement(key, rs.getString("name"), rs.getInt("point_value"),
                    rs.getDouble("total"), 0);

            returnValue.put(key, achievement);
        }

        return returnValue;

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

    } finally {

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

From source file:com.silverpeas.notation.model.RatingDAO.java

public static boolean existRaterRating(Connection con, RaterRatingPK pk) throws SQLException {
    PreparedStatement prepStmt = con.prepareStatement(QUERY_EXISTS_RATER_RATING);
    prepStmt.setString(1, pk.getInstanceId());
    prepStmt.setString(2, pk.getContributionId());
    prepStmt.setString(3, pk.getContributionType());
    prepStmt.setString(4, pk.getRater().getId());
    ResultSet rs = null;//ww w .  j  a v  a 2  s  . co m

    try {
        rs = prepStmt.executeQuery();
        return (rs.next());
    } finally {
        DBUtil.close(rs, prepStmt);
    }
}

From source file:com.nabla.dc.server.handler.fixed_asset.Asset.java

static public void dispose(final Connection conn, final Integer assetId, final IDisposal disposal)
        throws SQLException, DispatchException {
    final PreparedStatement redo = conn
            .prepareStatement("INSERT INTO fa_transaction_redo (fa_asset_id, command) VALUES(?,?);");
    try {//from w ww  .j  av  a2s. c  om
        redo.setInt(1, assetId);
        // backup transaction after disposal if any
        if (log.isDebugEnabled())
            log.debug("backing up transactions after disposal date");
        // charge monthly depreciation in disposal month if disposal is after 15
        final Calendar dt = Util.dateToCalendar(disposal.getDate());
        if (dt.get(GregorianCalendar.DAY_OF_MONTH) >= dt.getActualMaximum(GregorianCalendar.DAY_OF_MONTH) / 2)
            dt.add(GregorianCalendar.MONTH, 1);
        dt.set(GregorianCalendar.DAY_OF_MONTH, 1);
        final Date from = Util.calendarToSqlDate(dt);
        // get list of transactions to backup before we delete them
        final IntegerSet transIds = new IntegerSet();
        final PreparedStatement stmt = StatementFormat.prepare(conn,
                "SELECT t.*" + " FROM fa_transaction AS t INNER JOIN period_end AS p ON t.period_end_id=p.id"
                        + " WHERE t.fa_asset_id=? AND p.end_date>?;",
                assetId, from);
        try {
            final ResultSet rs = stmt.executeQuery();
            try {
                while (rs.next()) {
                    transIds.add(rs.getInt("id"));
                    final String command = MessageFormat.format("INSERT INTO fa_transaction"
                            + " (id,fa_asset_id,period_end_id,amount,class,type,depreciation_period)"
                            + " VALUES({0,number,0},{1,number,0},{2,number,0},{3,number,0},''{4}'',''{5}'',{6,number,0});",
                            rs.getInt("id"), rs.getInt("fa_asset_id"), rs.getInt("period_end_id"),
                            rs.getInt("amount"), rs.getString("class"), rs.getString("type"),
                            Database.getInteger(rs, "depreciation_period"));
                    if (log.isTraceEnabled())
                        log.trace("redo = " + command);
                    redo.setString(2, command);
                    redo.addBatch();
                }
            } finally {
                rs.close();
            }
        } finally {
            stmt.close();
        }
        // remove any transaction after disposal date
        if (log.isDebugEnabled())
            log.debug("removing transactions after disposal date");
        Database.executeUpdate(conn, "DELETE FROM fa_transaction WHERE id IN (?);", transIds);
        // add disposal transactions
        if (log.isDebugEnabled())
            log.debug("adding transactions for disposal");
        final TransactionList transactions = new TransactionList(assetId);
        // closing cost
        transactions.add(new Transaction(TransactionClasses.COST, TransactionTypes.CLOSING, disposal.getDate(),
                -1 * getAssetCostBeforeDisposal(conn, assetId)));
        // closing accumulated depreciation
        transactions.add(new Transaction(TransactionClasses.DEP, TransactionTypes.CLOSING, disposal.getDate(),
                -1 * getAssetDepreciationBeforeDisposal(conn, assetId)));
        for (Integer newTransId : transactions.save(conn, true)) {
            redo.setString(2,
                    MessageFormat.format("DELETE FROM fa_transaction WHERE id={0,number,0};", newTransId));
            redo.addBatch();
        }
        if (!Database.isBatchCompleted(redo.executeBatch()))
            throw new InternalErrorException("failed to save disposal transactions");
    } finally {
        redo.close();
    }
}

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

/**
 * get salt by user name//w w  w . j a  va2 s  . co  m
 *
 * @param con      DB connection
 * @param username username
 * @return salt
 */
private static String getSaltByUsername(Connection con, String username) {

    String salt = "";
    try {
        PreparedStatement stmt = con
                .prepareStatement("select salt from users where enabled=true and username=?");
        stmt.setString(1, username);
        ResultSet rs = stmt.executeQuery();
        if (rs.next() && rs.getString("salt") != null) {
            salt = rs.getString("salt");
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

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

/**
 * get salt by authentication token//  ww w  .ja  v a  2 s .  c  o  m
 *
 * @param con       DB connection
 * @param authToken auth token
 * @return salt
 */
private static String getSaltByAuthToken(Connection con, String authToken) {

    String salt = "";
    try {
        PreparedStatement stmt = con
                .prepareStatement("select salt from users where enabled=true and auth_token=?");
        stmt.setString(1, authToken);
        ResultSet rs = stmt.executeQuery();
        if (rs.next() && rs.getString("salt") != null) {
            salt = rs.getString("salt");
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

From source file:com.wso2telco.mnc.resolver.mncrange.McnRangeDbUtil.java

public static String getMncBrand(String mcc, String mnc) throws MobileNtException {
    Connection conn = null;/*w  w w .  j a va2  s  .c o m*/
    PreparedStatement ps = null;
    ResultSet rs = null;
    String sql = "SELECT operatorname " + "FROM operators " + "WHERE mcc = ? AND mnc = ?";

    String mncBrand = null;

    try {
        conn = getAxiataDBConnection();
        ps = conn.prepareStatement(sql);
        ps.setString(1, mcc);
        ps.setString(2, mnc);
        rs = ps.executeQuery();
        if (rs.next()) {
            mncBrand = rs.getString("operatorname");
        }
    } catch (SQLException e) {
        handleException("Error occured while getting Brand for for mcc: and mnc: " + mcc + ":" + mnc
                + " from the database", e);
    } finally {
        McnRangeDbUtil.closeAllConnections(ps, conn, rs);
    }
    return mncBrand;
}

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

/**
 * returns user id based on auth token/* ww w.  j  av  a  2  s  . c om*/
 *
 * @param authToken auth token
 * @param con       DB connection
 * @return user
 */
public static User getUserByAuthToken(Connection con, String authToken) {

    User user = null;
    try {
        PreparedStatement stmt = con
                .prepareStatement("select * from users where enabled=true and auth_token like ?");
        stmt.setString(1, authToken);
        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            Long userId = rs.getLong("id");

            user = UserDB.getUser(con, userId);
        }
        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 system ids//from w  w  w .ja  va2s . c om
 *
 * @param con DB connection
 * @return system
 */
public static List<Long> getAllSystemIds(Connection con) {

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

    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.wso2telco.mnc.resolver.mncrange.McnRangeDbUtil.java

/**
 * Gets the mcc number ranges.//w  ww . j a v a2 s  . c  o  m
 *
 * @param mcc the mcc
 * @return the mcc number ranges
 * @throws MobileNtException the mobile nt exception
 */
public static List<NumberRange> getMccNumberRanges(String mcc) throws MobileNtException {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    String sql = "SELECT mnccode,brand,rangefrom,rangeto " + "FROM mcc_number_ranges " + "WHERE mcccode = ?";

    List<NumberRange> lstranges = new ArrayList();

    try {
        conn = getAxiataDBConnection();
        ps = conn.prepareStatement(sql);
        ps.setString(1, mcc);
        rs = ps.executeQuery();
        while (rs.next()) {
            lstranges.add(new NumberRange(rs.getLong("rangefrom"), rs.getLong("rangeto"),
                    rs.getString("mnccode"), rs.getString("brand")));
        }
    } catch (SQLException e) {
        handleException("Error occured while getting Number ranges for mcc: " + mcc + " from the database", e);
    } finally {
        McnRangeDbUtil.closeAllConnections(ps, conn, rs);
    }
    return lstranges;
}