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.sql.CaseParty.java

public static List<CasePartyModel> getCasePartyList(String caseYear, String caseType, String caseMonth,
        String caseNumber) {/*  www.j ava 2  s.  c  o m*/
    List<CasePartyModel> list = new ArrayList();
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = DBConnection.connectToDB();

        String sql = "SELECT" + " caseRelation," + " LastName," + " FirstName," + " MiddleInitial,"
                + " jobTitle," + " companyName," + " Address1," + " Address2," + " City," + " stateCode,"
                + " zipCode," + " phone1 FROM " + " caseParty" + " WHERE" + " caseyear = ?"
                + " AND casetype = ?" + " AND casemonth = ?" + " AND caseNumber = ?";
        ps = conn.prepareStatement(sql);
        ps.setString(1, caseYear);
        ps.setString(2, caseType);
        ps.setString(3, caseMonth);
        ps.setString(4, caseNumber);
        rs = ps.executeQuery();

        while (rs.next()) {
            CasePartyModel item = new CasePartyModel();
            item.setCaseRelation(rs.getString("caseRelation") == null ? "" : rs.getString("caseRelation"));
            item.setLastName(rs.getString("LastName") == null ? "" : rs.getString("LastName"));
            item.setFirstName(rs.getString("FirstName") == null ? "" : rs.getString("FirstName"));
            item.setMiddleInitial(rs.getString("MiddleInitial") == null ? "" : rs.getString("MiddleInitial"));
            item.setJobTitle(rs.getString("jobTitle") == null ? "" : rs.getString("jobTitle"));
            item.setCompanyName(rs.getString("companyName") == null ? "" : rs.getString("companyName"));
            item.setAddress1(rs.getString("Address1") == null ? "" : rs.getString("Address1"));
            item.setAddress2(rs.getString("Address2") == null ? "" : rs.getString("Address2"));
            item.setCity(rs.getString("City") == null ? "" : rs.getString("City"));
            item.setStateCode(rs.getString("stateCode") == null ? "" : rs.getString("stateCode"));
            item.setZipcode(rs.getString("zipCode") == null ? "" : rs.getString("zipCode"));
            item.setPhone1(rs.getString("phone1") == null ? "" : rs.getString("phone1"));
            list.add(item);
        }
    } catch (SQLException ex) {
        if (ex.getCause() instanceof SQLServerException) {
            getCasePartyList(caseYear, caseType, caseMonth, caseNumber);
        }
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
    return list;
}

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

/**
 * select all unique public keys for user
 *
 * @param userId user id//  w  w  w. j  a  v a  2s  .  c o m
 * @return public  key list for user
 */
public static List<PublicKey> getUniquePublicKeysForUser(Long userId) {

    Connection con = null;
    Map<String, PublicKey> keyMap = new LinkedHashMap();
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(
                "select * from public_keys where user_id=? and enabled=true order by key_nm asc");
        stmt.setLong(1, userId);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {

            PublicKey publicKey = new PublicKey();
            publicKey.setId(rs.getLong("id"));
            publicKey.setKeyNm(rs.getString(KEY_NM));
            publicKey.setPublicKey(rs.getString(PUBLIC_KEY));
            publicKey.setProfile(ProfileDB.getProfile(con, rs.getLong(PROFILE_ID)));
            publicKey.setType(SSHUtil.getKeyType(publicKey.getPublicKey()));
            publicKey.setFingerprint(SSHUtil.getFingerprint(publicKey.getPublicKey()));
            publicKey.setCreateDt(rs.getTimestamp(CREATE_DT));
            keyMap.put(publicKey.getKeyNm() + " (" + publicKey.getFingerprint() + ")", publicKey);

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

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

    return new ArrayList(keyMap.values());

}

From source file:com.mirth.connect.server.controllers.tests.TestUtils.java

public static boolean channelExists(String channelId) throws Exception {
    boolean exists = false;
    Connection connection = getConnection();
    PreparedStatement statement = connection.prepareStatement("SELECT 1 FROM channels WHERE channel_id = ?");
    statement.setString(1, channelId);//from  w  w w . j a  va 2s  .  com
    ResultSet result = statement.executeQuery();

    if (result.next()) {
        exists = true;
    }

    result.close();
    connection.close();
    return exists;
}

From source file:com.asakusafw.bulkloader.testutil.UnitTestUtil.java

/**
 * ?????// w  ww. j  a  v  a2s. c  om
 * @param tableName
 * @return
 */
public static boolean isExistTable(String tableName) throws Exception {
    String url = ConfigurationLoader.getProperty(Constants.PROP_KEY_DB_URL);
    String schema = url.substring(url.lastIndexOf("/") + 1, url.length());
    String sql = "SELECT count(*) as count FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=? AND TABLE_SCHEMA=?";
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        printLog("executeQuery???SQL" + sql + "" + tableName + ","
                + schema, "isExistTable");
        conn = DBConnection.getConnection();
        stmt = conn.prepareStatement(sql);
        stmt.setString(1, tableName);
        stmt.setString(2, schema);
        rs = stmt.executeQuery();
        if (rs.next()) {
            int count = rs.getInt("count");
            if (count > 0) {
                printLog("??????" + tableName, "isExistTable");
                return true;
            } else {
                printLog("??????" + tableName, "isExistTable");
                return false;
            }
        } else {
            printLog("??????" + tableName, "isExistTable");
            return false;
        }
    } finally {
        DBConnection.closeRs(rs);
        DBConnection.closePs(stmt);
        DBConnection.closeConn(conn);
    }
}

From source file:com.wso2telco.dep.validator.handler.utils.ValidatorDBUtils.java

/**
 * Method to retrieve the validator class from the database.
 *
 * @param applicationId/*www. ja  v  a2  s . c o m*/
 * @param apiId
 * @return validator class name
 * @throws ValidatorException
 */
public static String getValidatorClassForSubscription(int applicationId, int apiId) throws ValidatorException {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet results = null;
    String sql = "SELECT class FROM validator, subscription_validator "
            + "WHERE subscription_validator.application_id=? AND subscription_validator.api_id=? AND "
            + "validator.id=subscription_validator.validator_id";
    String validatorClass = null;
    try {
        conn = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);
        ps = conn.prepareStatement(sql);
        log.debug("getValidatorClassForSubscription for applicationId---> " + applicationId + " apiId--> "
                + apiId);
        ps.setInt(1, applicationId);
        ps.setInt(2, apiId);
        results = ps.executeQuery();
        if (results.isBeforeFirst()) {
            while (results.next()) {
                validatorClass = results.getString("class");
            }
        } else {
            log.error("Result Set is empty");
        }
    } catch (Exception e) {
        handleException("Error occured while getting Validator Class for App: " + applicationId + " API: "
                + apiId + " from the database", e);
    } finally {
        APIMgtDBUtil.closeAllConnections(ps, conn, results);
    }
    return validatorClass;
}

From source file:com.att.pirates.controller.GlobalDataController.java

public static boolean isATTEmployeeITUPRoleEmpty(String UUID) {
    ResultSet rs = null;/*  w ww  . j a  v a  2 s  . c o  m*/
    Connection conn = null;
    PreparedStatement preparedStatement = null;
    boolean rc = false;

    try {
        conn = DBUtility.getDBConnection();
        // SQL query command
        String SQL = "  select * from ATTEmployeeArtifacts where uuid = ? ";

        preparedStatement = conn.prepareStatement(SQL);
        preparedStatement.setString(1, UUID);
        rs = preparedStatement.executeQuery();

        if (rs.next()) {
            rc = true;
        }
    } catch (SQLException e) {
        logger.error(e.getMessage());
    } catch (Exception e) {
        logger.error(e.getMessage());
    } finally {
        try {
            if (rs != null)
                rs.close();
        } catch (Exception e) {
        }
        ;
        try {
            if (preparedStatement != null)
                preparedStatement.close();
        } catch (Exception e) {
        }
        ;
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
        }
        ;
    }

    return rc;
}

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

public static HashMap<String, Achievement> getAchievements(DBManager dbManager, long userId) {

    Connection connection = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;//from  w w w  .  ja  va2s .  com
    HashMap<String, Achievement> returnValue = new HashMap<String, Achievement>();

    String sql = "SELECT ach.`key` as `key`, ach.`name` as name, " + "ach.point_value as point_value, "
            + "prg.progress as progress, ach.total as total "
            + "FROM rouge_achievement_progress as prg, rouge_achievements as ach "
            + "WHERE ach.key = prg.achievement_key and prg.user_id = ?; ";

    try {
        connection = dbManager.getConnection();
        stmt = connection.prepareStatement(sql);
        stmt.setLong(1, userId);

        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"), rs.getDouble("progress"));

            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.l2jfree.gameserver.model.entity.events.DM.java

public static void loadData() {
    _eventName = "";
    _eventDesc = "";
    _joiningLocationName = "";
    _savePlayers = new CopyOnWriteArrayList<String>();
    _players = new CopyOnWriteArrayList<L2Player>();
    _topPlayer = null;//from   w  w  w  .j a  va 2  s. c o m
    _npcSpawn = null;
    _joining = false;
    _teleport = false;
    _started = false;
    _sitForced = false;
    _npcId = 0;
    _npcX = 0;
    _npcY = 0;
    _npcZ = 0;
    _rewardId = 0;
    _rewardAmount = 0;
    _topKills = 0;
    _minlvl = 0;
    _maxlvl = 0;
    _playerColors = 0;
    _playerX = 0;
    _playerY = 0;
    _playerZ = 0;

    Connection con = null;
    try {
        PreparedStatement statement;
        ResultSet rs;

        con = L2DatabaseFactory.getInstance().getConnection(con);

        statement = con.prepareStatement("Select * from dm");
        rs = statement.executeQuery();

        while (rs.next()) {
            _eventName = rs.getString("eventName");
            _eventDesc = rs.getString("eventDesc");
            _joiningLocationName = rs.getString("joiningLocation");
            _minlvl = rs.getInt("minlvl");
            _maxlvl = rs.getInt("maxlvl");
            _npcId = rs.getInt("npcId");
            _npcX = rs.getInt("npcX");
            _npcY = rs.getInt("npcY");
            _npcZ = rs.getInt("npcZ");
            _rewardId = rs.getInt("rewardId");
            _rewardAmount = rs.getInt("rewardAmount");
            _playerColors = rs.getInt("color");
            _playerX = rs.getInt("playerX");
            _playerY = rs.getInt("playerY");
            _playerZ = rs.getInt("playerZ");

        }
        statement.close();
    } catch (Exception e) {
        _log.error("Exception: DM.loadData(): ", e);
    } finally {
        L2DatabaseFactory.close(con);
    }
}

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

public static List<String> getPublicKeysForSystem(Connection con, Long systemId) {
    List<String> publicKeyList = new ArrayList<String>();

    if (systemId == null) {
        systemId = -99L;// w w  w.  ja  va 2 s  .c  o m
    }
    try {
        PreparedStatement stmt = con.prepareStatement(
                "select * from public_keys where (profile_id is null or profile_id in (select profile_id from system_map where system_id=?)) and enabled=true");
        stmt.setLong(1, systemId);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            publicKeyList.add(rs.getString("public_key"));
        }
        DBUtils.closeStmt(stmt);

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

    return publicKeyList;

}

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

public static List<String> getPublicKeysForSystem(Connection con, Long systemId) {
    List<String> publicKeyList = new ArrayList<>();

    if (systemId == null) {
        systemId = -99L;/*from   w  w w.  j ava 2 s. c  o m*/
    }
    try {
        PreparedStatement stmt = con.prepareStatement(
                "select * from public_keys where (profile_id is null or profile_id in (select profile_id from system_map where system_id=?)) and enabled=true");
        stmt.setLong(1, systemId);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            publicKeyList.add(rs.getString(PUBLIC_KEY));
        }
        DBUtils.closeStmt(stmt);

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

    return publicKeyList;

}