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:ca.qc.adinfo.rouge.leaderboard.db.LeaderboardDb.java

public static Leaderboard getLeaderboard(DBManager dbManager, String key) {

    Connection connection = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;//  w ww . j a  va  2 s . c  o m

    String sql = "SELECT score, user_id FROM rouge_leaderboard_score "
            + "WHERE `leaderboard_key` = ? ORDER BY `score` DESC LIMIT 5;";

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

        stmt.setString(1, key);

        Leaderboard leaderboard = new Leaderboard(key);

        rs = stmt.executeQuery();

        while (rs.next()) {
            Score score = new Score(rs.getLong("user_id"), rs.getLong("score"));
            leaderboard.addScore(score);
        }

        return leaderboard;

    } 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.wso2telco.dep.reportingservice.dao.OperatorDAO.java

/**
 * Gets the SP list.//  ww  w  .j  ava2s .c o  m
 *
 * @param operator the operator
 * @return the SP list
 */
public static ArrayList<SPObject> getSPList(String operator) {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    String sql = "Select * from sub_approval_operators WHERE OPERATOR_LIST like '%" + operator + "%'";
    ArrayList<SPObject> spList = new ArrayList<SPObject>();
    try {
        conn = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()) {
            SPObject spObject = new SPObject();
            spObject.setAppId(rs.getInt("APP_ID"));
            spList.add(spObject);

        }

    } catch (Exception e) {
        log.error("Error occured while getting approved operators of application from the database" + e);
    }
    return spList;
}

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

/**
 * returns system by id//from w  w w. ja  v 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;
}

From source file:com.wso2telco.dep.subscriptionvalidator.util.ValidatorDBUtils.java

public static List<ValidatorClassDTO> getValidatorClassForSMSSubscription() throws ValidatorException {
    Connection conn = null;//from   w w w  .ja  v  a2  s  .co  m
    PreparedStatement ps = null;
    ResultSet results = null;
    String sql = "SELECT v.class as class,s.application_id as app,s.api_id as api FROM validator v, subscription_validator s WHERE v.id=s.validator_id";
    List<ValidatorClassDTO> validatorClass = new ArrayList<ValidatorClassDTO>();
    try {
        conn = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);
        ps = conn.prepareStatement(sql);
        results = ps.executeQuery();
        while (results.next()) {
            ValidatorClassDTO classDTO = new ValidatorClassDTO();
            classDTO.setClassName(results.getString("class"));
            classDTO.setApp(results.getInt("app"));
            classDTO.setApi(results.getInt("api"));
            validatorClass.add(classDTO);
        }
    } catch (Exception e) {
        handleException("Error occured while getting Validator Class  from the database", e);
    } finally {
        closeAllConnections(ps, conn, results);
    }
    return validatorClass;
}

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

/**
 * checks to see if user is an admin based on auth token
 *
 * @param userId    user id/*  ww  w .  j  a  v a2 s .c o m*/
 * @param authToken auth token string
 * @return user type if authorized, null if not authorized
 */
public static String isAuthorized(Long userId, String authToken) {

    String authorized = null;

    Connection con = null;
    if (authToken != null && !authToken.trim().equals("")) {

        try {
            con = DBUtils.getConn();
            PreparedStatement stmt = con
                    .prepareStatement("select * from users where enabled=true and id=? and auth_token=?");
            stmt.setLong(1, userId);
            stmt.setString(2, authToken);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                authorized = rs.getString("user_type");

            }
            DBUtils.closeRs(rs);

            DBUtils.closeStmt(stmt);

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

}

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

public static void raisePrice(String coffeeName, double maximumPercentage, BigDecimal[] newPrice)
        throws SQLException {

    Connection con = DriverManager.getConnection("jdbc:default:connection");
    PreparedStatement pstmt = null;
    ResultSet rs = null;/*  w w w  . jav a  2 s.  c  om*/
    BigDecimal oldPrice;

    String queryGetCurrentCoffeePrice = "select COFFEES.PRICE " + "from COFFEES "
            + "where COFFEES.COF_NAME = ?";

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

    if (rs.next()) {
        oldPrice = rs.getBigDecimal(1);
    } else {
        return;
    }

    BigDecimal maximumNewPrice = oldPrice.multiply(new BigDecimal(1 + maximumPercentage));

    // Test if newPrice[0] > maximumNewPrice

    if (newPrice[0].compareTo(maximumNewPrice) == 1) {
        newPrice[0] = maximumNewPrice;
    }

    // Test if newPrice[0] <= oldPrice

    if (newPrice[0].compareTo(oldPrice) < 1) {
        newPrice[0] = oldPrice;
        return;
    }

    String queryUpdatePrice = "update COFFEES " + "set COFFEES.PRICE = ? " + "where COFFEES.COF_NAME = ?";

    pstmt = con.prepareStatement(queryUpdatePrice);
    pstmt.setBigDecimal(1, newPrice[0]);
    pstmt.setString(2, coffeeName);
    pstmt.executeUpdate();
}

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

/**
 * returns a list of system ids for a given profile
 *
 * @param con       DB con/*  www .  j  a v  a2 s .c o m*/
 * @param profileId profile id
 * @param userId user id
 * @return list of host systems
 */
public static List<Long> getSystemIdsByProfile(Connection con, Long profileId, Long userId) {

    List<Long> systemIdList = new ArrayList<Long>();
    try {
        PreparedStatement stmt = con.prepareStatement(
                "select sm.system_id from  system_map sm, user_map um where um.profile_id=sm.profile_id and sm.profile_id=? and um.user_id=?");
        stmt.setLong(1, profileId);
        stmt.setLong(2, userId);
        ResultSet rs = stmt.executeQuery();

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

    } catch (Exception e) {
        e.printStackTrace();
    }
    return systemIdList;
}

From source file:com.wso2telco.dep.reportingservice.dao.ApiManagerDAO.java

/**
 * Gets the consumer key by application.
 *
 * @param applicationId the application id
 * @return the consumer key by application
 * @throws APIMgtUsageQueryServiceClientException the API mgt usage query service client exception
 * @throws SQLException the SQL exception
 */// www. j  a  v  a2 s.  c  o m
public static String getConsumerKeyByApplication(int applicationId)
        throws APIMgtUsageQueryServiceClientException, SQLException {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet results = null;
    String sql = "SELECT CONSUMER_KEY FROM " + ReportingTable.AM_APPLICATION_KEY_MAPPING
            + " WHERE KEY_TYPE = 'PRODUCTION' AND APPLICATION_ID=?";
    String consumerKey = null;

    try {
        conn = DbUtils.getDbConnection(DataSourceNames.WSO2AM_DB);
        ps = conn.prepareStatement(sql);
        ps.setInt(1, applicationId);
        log.debug("getConsumerKeyByApplication");
        results = ps.executeQuery();
        while (results.next()) {
            consumerKey = results.getString("CONSUMER_KEY");
        }
    } catch (Exception e) {
        log.error("Error occured while getting consumer key from the database" + e);
    } finally {
        DbUtils.closeAllConnections(ps, conn, results);
    }

    return consumerKey;
}

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

/**
 * checks to see if username is unique while ignoring current user
 *
 * @param userId user id//from   w w w . j a v  a 2s.co  m
 * @param username username
 * @return true false indicator
 */
public static boolean isUnique(Long userId, String username) {

    boolean isUnique = true;
    if (userId == null) {
        userId = -99L;
    }

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(
                "select * from users where enabled=true and lower(username) like lower(?) and id != ?");
        stmt.setString(1, username);
        stmt.setLong(2, userId);
        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            isUnique = false;
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);
    } catch (Exception ex) {
        log.error(ex.toString(), ex);
    }
    DBUtils.closeConn(con);

    return isUnique;

}

From source file:com.wso2telco.dep.reportingservice.dao.OperatorDAO.java

/**
 * Gets the all operators.//ww  w. ja  v  a2  s  .c  o m
 *
 * @return the all operators
 * @throws APIMgtUsageQueryServiceClientException the API mgt usage query service client exception
 * @throws SQLException the SQL exception
 */
public static List<String> getAllOperators() throws APIMgtUsageQueryServiceClientException, SQLException {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet results = null;
    String sql = "SELECT operatorname FROM " + ReportingTable.OPERATORS + "";
    List<String> op = new ArrayList<String>();
    try {
        conn = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);
        ps = conn.prepareStatement(sql);
        log.debug("getAllOperators for ID");
        results = ps.executeQuery();
        while (results.next()) {
            String temp = results.getString("operatorname");
            op.add(temp);
        }
    } catch (Exception e) {
        log.error("Error occured while getting All Operators from the database" + e);
    } finally {
        DbUtils.closeAllConnections(ps, conn, results);
    }
    return op;
}