List of usage examples for java.sql ResultSet first
boolean first() throws SQLException;
ResultSet object. From source file:com.l2jfree.gameserver.model.quest.QuestState.java
/** * Read from the database a previously saved variable for this quest. * Due to performance considerations, this function should best be used only when the quest is first loaded. * Subclasses of this class can define structures into which these loaded values can be saved. * However, on-demand usage of this function throughout the script is not prohibited, only not recommended. * Values read from this function were entered by calls to "saveGlobalQuestVar" * @param var : String designating the name of the variable for the quest * @return String : String representing the loaded value for the passed var, or an empty string if the var was invalid *//*from ww w .j av a 2 s . co m*/ public final String getGlobalQuestVar(String var) { String result = ""; Connection con = null; try { con = L2DatabaseFactory.getInstance().getConnection(con); PreparedStatement statement; statement = con .prepareStatement("SELECT value FROM character_quest_global_data WHERE charId = ? AND var = ?"); statement.setInt(1, _player.getObjectId()); statement.setString(2, var); ResultSet rs = statement.executeQuery(); if (rs.first()) result = rs.getString(1); rs.close(); statement.close(); } catch (Exception e) { _log.error("could not load player's global quest variable:", e); } finally { L2DatabaseFactory.close(con); } return result; }
From source file:org.accada.epcis.repository.query.QueryOperationsBackendSQL.java
/** * {@inheritDoc}//from ww w. jav a 2s . c o m */ public boolean fetchExistsSubscriptionId(final QueryOperationsSession session, final String subscriptionID) throws SQLException { PreparedStatement stmt = session.getPreparedStatement(SQL_EXISTS_SUBSCRIPTION); stmt.setString(1, subscriptionID); if (LOG.isDebugEnabled()) { LOG.debug("SQL: " + SQL_EXISTS_SUBSCRIPTION); LOG.debug(" param1 = " + subscriptionID); } ResultSet rs = stmt.executeQuery(); rs.first(); return rs.getBoolean(1); }
From source file:org.wso2.carbon.ml.dataset.internal.DatabaseHandler.java
/** * Retrieve and returns the Summary statistics for a given feature of a * given data-set, from the database.//from ww w. ja v a 2 s. c om * * @param datasetID Unique identifier of the data-set * @param featureName Name of the feature of which summary statistics are needed * @return JSON string containing the summary statistics * @throws DatabaseHandlerException */ protected String getSummaryStats(String datasetID, String featureName) throws DatabaseHandlerException { Connection connection = null; PreparedStatement getSummaryStatement = null; ResultSet result = null; try { connection = dataSource.getConnection(); connection.setAutoCommit(true); getSummaryStatement = connection.prepareStatement(SQLQueries.GET_SUMMARY_STATS); getSummaryStatement.setString(1, featureName); getSummaryStatement.setString(2, datasetID); result = getSummaryStatement.executeQuery(); result.first(); return result.getString(1); } catch (SQLException e) { throw new DatabaseHandlerException( "An error occured while retireving summary " + "statistics for the feature \"" + featureName + "\" of the data set " + datasetID + ": " + e.getMessage(), e); } finally { // Close the database resources MLDatabaseUtils.closeDatabaseResources(connection, getSummaryStatement, result); } }
From source file:org.cerberus.crud.dao.impl.TestBatteryDAO.java
@Override public TestBattery findTestBatteryByKey(Integer testBatteryID) throws CerberusException { boolean throwEx = false; final String query = "SELECT * FROM testbattery t WHERE t.testbatteryID = ?"; TestBattery testBattery = null;//www. j a v a 2s .co m Connection connection = this.databaseSpring.connect(); try { PreparedStatement preStat = connection.prepareStatement(query); preStat.setInt(1, testBatteryID); try { ResultSet resultSet = preStat.executeQuery(); try { if (resultSet.first()) { testBattery = this.loadFromResultSet(resultSet); } } catch (SQLException exception) { MyLogger.log(TestBatteryDAO.class.getName(), Level.ERROR, "Unable to execute query : " + exception.toString()); } finally { resultSet.close(); } } catch (SQLException exception) { MyLogger.log(TestBatteryDAO.class.getName(), Level.ERROR, "Unable to execute query : " + exception.toString()); } finally { preStat.close(); } } catch (SQLException exception) { MyLogger.log(TestBatteryDAO.class.getName(), Level.ERROR, "Unable to execute query : " + exception.toString()); } finally { try { if (connection != null) { connection.close(); } } catch (SQLException e) { MyLogger.log(TestBatteryDAO.class.getName(), Level.WARN, e.toString()); } } if (throwEx) { throw new CerberusException(new MessageGeneral(MessageGeneralEnum.NO_DATA_FOUND)); } return testBattery; }
From source file:org.cerberus.crud.dao.impl.TestBatteryDAO.java
@Override public TestBattery findTestBatteryByTestBatteryName(String testBattery) throws CerberusException { boolean throwEx = false; final String query = "SELECT * FROM testbattery t WHERE t.testbattery = ?"; TestBattery testBatteryResult = null; Connection connection = this.databaseSpring.connect(); try {//from ww w . j a v a2s .c o m PreparedStatement preStat = connection.prepareStatement(query); preStat.setString(1, testBattery); try { ResultSet resultSet = preStat.executeQuery(); try { if (resultSet.first()) { testBatteryResult = this.loadFromResultSet(resultSet); } } catch (SQLException exception) { MyLogger.log(TestBatteryDAO.class.getName(), Level.ERROR, "Unable to execute query : " + exception.toString()); } finally { resultSet.close(); } } catch (SQLException exception) { MyLogger.log(TestBatteryDAO.class.getName(), Level.ERROR, "Unable to execute query : " + exception.toString()); } finally { preStat.close(); } } catch (SQLException exception) { MyLogger.log(TestBatteryDAO.class.getName(), Level.ERROR, "Unable to execute query : " + exception.toString()); } finally { try { if (connection != null) { connection.close(); } } catch (SQLException e) { MyLogger.log(TestBatteryDAO.class.getName(), Level.WARN, e.toString()); } } if (throwEx) { throw new CerberusException(new MessageGeneral(MessageGeneralEnum.NO_DATA_FOUND)); } return testBatteryResult; }
From source file:org.wso2.carbon.ml.dataset.internal.DatabaseHandler.java
/** * Returns the number of features of a given data-set. * * @param datasetID Unique identifier of the data-set * @return Number of features in the data-set * @throws DatabaseHandlerException *//*ww w. j av a 2s. c om*/ protected int getFeatureCount(String datasetID) throws DatabaseHandlerException { Connection connection = null; PreparedStatement getFeatues = null; ResultSet result = null; int featureCount = 0; try { connection = dataSource.getConnection(); connection.setAutoCommit(true); // Create a prepared statement and extract data-set configurations. getFeatues = connection.prepareStatement(SQLQueries.GET_FEATURE_COUNT); getFeatues.setString(1, datasetID); result = getFeatues.executeQuery(); if (result.first()) { featureCount = result.getInt(1); } return featureCount; } catch (SQLException e) { throw new DatabaseHandlerException("An error occurred while retrieving feature count of the dataset " + datasetID + ": " + e.getMessage(), e); } finally { // Close the database resources MLDatabaseUtils.closeDatabaseResources(connection, getFeatues, result); } }
From source file:org.cerberus.crud.dao.impl.TestCaseCountryPropertiesDAO.java
@Override public TestCaseCountryProperties findTestCaseCountryPropertiesByKey(String test, String testcase, String country, String property) throws CerberusException { TestCaseCountryProperties result = null; boolean throwException = false; final String query = "SELECT * FROM testcasecountryproperties WHERE test = ? AND testcase = ? AND country = ? AND hex(`property`) = hex(?)"; Connection connection = this.databaseSpring.connect(); try {/*from ww w .j a v a 2 s .co m*/ PreparedStatement preStat = connection.prepareStatement(query); try { preStat.setString(1, test); preStat.setString(2, testcase); preStat.setString(3, country); preStat.setBytes(4, property.getBytes("UTF-8")); ResultSet resultSet = preStat.executeQuery(); try { if (resultSet.first()) { String description = resultSet.getString("description"); String type = resultSet.getString("type"); String database = resultSet.getString("database"); String value1 = resultSet.getString("value1"); String value2 = resultSet.getString("value2"); int length = resultSet.getInt("length"); int rowLimit = resultSet.getInt("rowLimit"); String nature = resultSet.getString("nature"); int retryNb = resultSet.getInt("RetryNb"); int retryPeriod = resultSet.getInt("RetryPeriod"); result = factoryTestCaseCountryProperties.create(test, testcase, country, property, description, type, database, value1, value2, length, rowLimit, nature, retryNb, retryPeriod); } else { throwException = true; } } catch (SQLException exception) { LOG.error("Unable to execute query : " + exception.toString()); } finally { resultSet.close(); } } catch (SQLException exception) { LOG.error("Unable to execute query : " + exception.toString()); } catch (UnsupportedEncodingException ex) { LOG.error(ex.toString()); } finally { preStat.close(); } } catch (SQLException exception) { LOG.error("Unable to execute query : " + exception.toString()); } finally { try { if (connection != null) { connection.close(); } } catch (SQLException e) { LOG.warn(e.toString()); } } if (throwException) { throw new CerberusException(new MessageGeneral(MessageGeneralEnum.NO_DATA_FOUND)); } return result; }
From source file:org.geowebcache.storage.jdbc.jobstore.JDBCJobWrapper.java
public long getJobCount() throws SQLException { long result = 0; String query = "SELECT COUNT(*) FROM JOBS"; final Connection conn = getConnection(); PreparedStatement prep = null; try {//from w w w . ja v a 2 s .c om prep = conn.prepareStatement(query); ResultSet rs = prep.executeQuery(); try { if (rs.first()) { result = rs.getLong(1); } } finally { close(rs); } } finally { close(prep); close(conn); } return result; }
From source file:org.geowebcache.storage.jdbc.metastore.JDBCMBWrapper.java
/** * Checks / creates the "variables" table and verifies that the db_version variable is. * /*from w w w . j ava 2 s . com*/ * @param conn * @throws SQLException * @throws StorageException * if the database is newer than the software */ protected int getDbVersion(Connection conn) throws SQLException, ConfigurationException { condCreate(conn, "VARIABLES", "KEY VARCHAR(32), VALUE VARCHAR(128)", "KEY", null); Statement st = null; ResultSet rs = null; try { st = conn.createStatement(); rs = st.executeQuery("SELECT VALUE FROM VARIABLES WHERE KEY LIKE 'db_version'"); if (rs.first()) { // Check what version of the database this is String db_versionStr = rs.getString("value"); int cur_db_version = Integer.parseInt(db_versionStr); return cur_db_version; } else { // This is a new database, insert current value st.execute("INSERT INTO VARIABLES (KEY,VALUE) " + " VALUES ('db_version'," + JDBCMBWrapper.DB_VERSION + ")"); return JDBCMBWrapper.DB_VERSION; } } finally { close(rs); close(st); } }
From source file:servlets.Install_servlets.java
/** * * @param request// w ww .j a v a2 s . c o m * @param response * @throws ServletException * @throws IOException */ private HttpServletResponse install(JsonObject settings, HttpServletResponse response) throws ServletException, IOException { PrintWriter logWriter = null; DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); Calendar cal = Calendar.getInstance(); try { /** * ******************************************************* * STEP 1 Check if the user exists in the DB. IF ERROR --> throws * MySQL exception, GO TO STEP 2b throws NoSuchAlgorithmException, * GO TO STEP 2b ELSE --> GO TO STEP 2 * ******************************************************* */ String installation_type = settings.get("installation_type").getAsString(); String data_location = settings.get("data_location").getAsString(); DBConnectionManager.setDataLocation(data_location); String emsadminpass = settings.get("EMS_ADMIN_PASSWORD").getAsString(); String emsadminuser = settings.get("EMS_ADMIN_USER").getAsString().toLowerCase(); /* ******************************************************* * INSTALL OPTION * *******************************************************/ if ("install".equals(installation_type)) { String mysql_emsusername = settings.get("MYSQL_EMS_USER").getAsString(); String mysql_emsuserpass = settings.get("MYSQL_EMS_PASS").getAsString(); String mysql_adminUser = settings.get("MYSQL_ROOT_USER").getAsString(); String mysql_adminPass = settings.get("MYSQL_ROOT_PASSWORD").getAsString(); String dbname = settings.get("MYSQL_DATABASE_NAME").getAsString(); String dbhost = settings.get("MYSQL_HOST").getAsString(); String userHost = ("localhost".equals(dbhost) ? dbhost : "%"); this.testWritableDirectory(data_location); this.testDatabaseConnection(dbhost, mysql_adminUser, mysql_adminPass); BufferedReader bufferedReader; String path; String line; String commandOutput = ""; PrintWriter printWriter; Properties properties; logWriter = new PrintWriter(new FileWriter(data_location + "/install.log")); logWriter.println(dateFormat.format(cal.getTime()) + '\t' + "Data location is " + data_location); //1. Open the SQL Database install script, Replace the database name and the EMS user name path = Install_servlets.class.getResource("/sql_scripts/install_databases.sql").getPath(); bufferedReader = new BufferedReader(new FileReader(path)); printWriter = new PrintWriter(new FileWriter(data_location + "/install_databases.sql")); logWriter.println(dateFormat.format(cal.getTime()) + '\t' + "Adapting SQL script"); while ((line = bufferedReader.readLine()) != null) { line = line.replace("STATegraDB", dbname); line = line.replace("emsuser#123", mysql_emsuserpass); line = line.replace("emsuser", mysql_emsusername); line = line.replace("adminpassword", emsadminpass); line = line.replace("localhost", userHost); printWriter.println(line); } bufferedReader.close(); printWriter.close(); //2.Execute the SQL script logWriter.println(dateFormat.format(cal.getTime()) + '\t' + "Executing SQL script"); String[] mysqlCommand = { "mysql", "-u", mysql_adminUser, "--password=" + mysql_adminPass, "-h", dbhost, "-e", "source " + data_location + "/install_databases.sql" }; Process dumpProcess = Runtime.getRuntime().exec(mysqlCommand); bufferedReader = new BufferedReader(new InputStreamReader(dumpProcess.getInputStream())); line = bufferedReader.readLine(); // Mientras se haya leido alguna linea while (line != null) { commandOutput += "\n" + line; line = bufferedReader.readLine(); } int exitCode = dumpProcess.waitFor(); logWriter.println( "/************************************************************/\n*** STDOUT\n/************************************************************/"); logWriter.println(commandOutput); //GET THE ERROR OUTPUT commandOutput = ""; bufferedReader = new BufferedReader(new InputStreamReader(dumpProcess.getErrorStream())); line = bufferedReader.readLine(); while (line != null) { commandOutput += "\n" + line; line = bufferedReader.readLine(); } logWriter.println( "/************************************************************/\n*** STDERR\n/************************************************************/"); logWriter.println(commandOutput); if (exitCode != 0) { throw new SQLException("Failed while executing the mysql database installation. Error: " + commandOutput.replace("\n", "</br>").replace("\r", "</br>")); } //3. Update the data location property logWriter.println(dateFormat.format(cal.getTime()) + '\t' + "Updating the application settings..."); path = Install_servlets.class.getResource("/conf/data_location.properties").getPath(); properties = new Properties(); properties.load(new FileReader(path)); FileOutputStream fileOutputStream = new FileOutputStream(path); properties.setProperty("data_location", data_location); properties.store(fileOutputStream, null); fileOutputStream.close(); //4. COPY AND UPDATE the db_config.properties file logWriter.println( dateFormat.format(cal.getTime()) + '\t' + "Updating the database connection settings..."); path = Install_servlets.class.getResource("/sql_scripts/db_config.properties").getPath(); bufferedReader = new BufferedReader(new FileReader(path)); printWriter = new PrintWriter(new FileWriter(data_location + "/db_config.properties")); while ((line = bufferedReader.readLine()) != null) { line = line.replace("STATegraDB", dbname); line = line.replace("emsuser#123", new String(Base64.encodeBase64(mysql_emsuserpass.getBytes()))); line = line.replace("emsuser", mysql_emsusername); line = line.replace("localhost", dbhost); printWriter.println(line); } bufferedReader.close(); printWriter.close(); logWriter.println(dateFormat.format(cal.getTime()) + '\t' + "Creating directories..."); //5. Create all the necessary directories new File(data_location + "/SOP_documents").mkdir(); new File(data_location + "/treatment_documents").mkdir(); // new File(data_location + "/extensions").mkdir(); // logWriter.println(dateFormat.format(cal.getTime()) + '\t' + "Copying extensions..."); // path = Install_servlets.class.getResource("/resources/extensions").getPath(); // File folder = new File(path); // File[] listOfFiles = folder.listFiles(); // // for (File listOfFile : listOfFiles) { // if (listOfFile.isFile() && listOfFile.getName().contains(".jar")) { // FileUtils.copyFile(listOfFile, new File(data_location + "/extensions/" + listOfFile.getName())); // } // } //6. Remove the temporal files logWriter.println(dateFormat.format(cal.getTime()) + '\t' + "Cleaning temporal files..."); new File(data_location + "/install_databases.sql").delete(); logWriter.println(dateFormat.format(cal.getTime()) + '\t' + "Removing installation page..."); } try { /** * *********************************************************************************************************************** */ /* UPDATE OPTION */ /** * *********************************************************************************************************************** */ this.testDatabaseConnection( DBConnectionManager.getConnectionManager().getProperties().getProperty("host"), DBConnectionManager.getConnectionManager().getProperties().getProperty("username"), DBConnectionManager.getConnectionManager().getProperties().getProperty("password")); this.testValidAdminUser(emsadminpass); this.testWritableDirectory(data_location); //GET PREVIOUS VERSION FROM DATABASE PreparedStatement ps = (PreparedStatement) DBConnectionManager.getConnectionManager() .prepareStatement("SELECT version FROM appVersion;"); ResultSet rs = (ResultSet) DBConnectionManager.getConnectionManager().execute(ps, true); double currentAppVersion = 0.6 * 10; if (rs.first()) { currentAppVersion = Double.parseDouble(rs.getString("version")) * 10; } //GET LAST UPDATE SCRIPT String path = Install_servlets.class.getResource("/sql_scripts/update_scripts/").getPath(); double lastAppVersion = currentAppVersion; boolean exists = true; File f; while (exists) { lastAppVersion = lastAppVersion + 1; f = new File(path + lastAppVersion / 10 + ".sql"); exists = f.exists(); } BufferedReader bufferedReader; String line; String commandOutput = ""; PrintWriter printWriter; Properties properties = new Properties(); logWriter = new PrintWriter(new FileWriter(data_location + "/update.log")); logWriter.println(dateFormat.format(cal.getTime()) + '\t' + "Data location is " + data_location); properties.load(new FileReader(data_location + "/db_config.properties")); logWriter.println(dateFormat.format(cal.getTime()) + '\t' + "Upgrading from version " + currentAppVersion + " to " + (lastAppVersion - 0.1)); //FOR EACH AVAILABLE NEW VERSION for (double i = currentAppVersion + 1; i < lastAppVersion; i = i + 1) { String dbuser = DBConnectionManager.getConnectionManager().getProperties() .getProperty("username"); String dbpass = DBConnectionManager.getConnectionManager().getProperties() .getProperty("password"); String dbname = DBConnectionManager.getConnectionManager().getProperties() .getProperty("databasename"); String dbhost = DBConnectionManager.getConnectionManager().getProperties().getProperty("url"); dbhost = dbhost.replace("jdbc:mysql://", "").replace("/" + dbname, ""); //1. Open the SQL Database update script, Replace the database name logWriter.println(dateFormat.format(cal.getTime()) + '\t' + "Upgrading to version " + i / 10); path = Install_servlets.class.getResource("/sql_scripts/update_scripts/" + i / 10 + ".sql") .getPath(); logWriter.println(dateFormat.format(cal.getTime()) + '\t' + "Adapting SQL script..."); bufferedReader = new BufferedReader(new FileReader(path)); printWriter = new PrintWriter(new FileWriter(data_location + "/" + i / 10 + ".sql")); while ((line = bufferedReader.readLine()) != null) { line = line.replace("STATegraDB", dbname); line = line.replace("emsadminuser@email.com", emsadminuser); printWriter.println(line); } bufferedReader.close(); printWriter.close(); //2.Execute the SQL script logWriter.println(dateFormat.format(cal.getTime()) + '\t' + "Executing SQL script..."); String[] mysqlCommand = { "mysql", "-u", dbuser, "--password=" + dbpass, "-h", dbhost, "-e", "source " + data_location + "/" + i / 10 + ".sql" }; Process dumpProcess = Runtime.getRuntime().exec(mysqlCommand); bufferedReader = new BufferedReader(new InputStreamReader(dumpProcess.getInputStream())); line = bufferedReader.readLine(); // Mientras se haya leido alguna linea while (line != null) { commandOutput += "\n" + line; line = bufferedReader.readLine(); } int exitCode = dumpProcess.waitFor(); //SAVE THE STDOUT logWriter.println( "/************************************************************/\n*** STDOUT\n/************************************************************/"); logWriter.println(commandOutput); //GET THE ERROR OUTPUT commandOutput = ""; bufferedReader = new BufferedReader(new InputStreamReader(dumpProcess.getErrorStream())); line = bufferedReader.readLine(); while (line != null) { commandOutput += "\n" + line; line = bufferedReader.readLine(); } logWriter.println( "/************************************************************/\n*** STERR\n/************************************************************/"); logWriter.println(commandOutput); if (exitCode != 0) { throw new SQLException( "Failed while executing the mysql database update script. Error: " + commandOutput); } logWriter.println(dateFormat.format(cal.getTime()) + '\t' + "Executing SQL script... DONE"); logWriter.println(dateFormat.format(cal.getTime()) + '\t' + "Cleaning temporal files..."); new File(data_location + "/" + i / 10 + ".sql").delete(); } //3. Update the data location property logWriter.println(dateFormat.format(cal.getTime()) + '\t' + "Updating the application settings..."); path = Install_servlets.class.getResource("/conf/data_location.properties").getPath(); properties = new Properties(); properties.load(new FileReader(path)); FileOutputStream fileOutputStream = new FileOutputStream(path); properties.setProperty("data_location", data_location); properties.store(fileOutputStream, null); fileOutputStream.close(); // logWriter.println(dateFormat.format(cal.getTime()) + '\t' + "Copying extensions..."); // path = Install_servlets.class.getResource("/resources/extensions").getPath(); // File folder = new File(path); // File[] listOfFiles = folder.listFiles(); // // for (File file : listOfFiles) { // if (file.isFile() && file.getName().contains(".jar")) { // File _file = new File(data_location + "/extensions/" + file.getName()); // if(_file.exists()){ // _file.delete(); // } // FileUtils.copyFile(file, _file); // } // } //6. Remove the temporal redirection // logWriter.println(dateFormat.format(cal.getTime()) + '\t' + "Disabling installation page..."); // path = Install_servlets.class.getResource("/").getPath(); // new File(path + "/../../index.html").delete(); // new File(path + "/../../install.html").delete(); // new File(path + "/../../_index.html").renameTo(new File(path + "/../../index.html")); } catch (IOException e) { throw new IOException("Unable to read database configuration file at " + data_location + "</br>Please check if directory exists and Tomcat user has read/write permissions on this directory."); } } catch (Exception e) { if (logWriter != null) { logWriter.println(dateFormat.format(cal.getTime()) + "\t INSTALLATION FAILED " + e.getMessage()); } ServerErrorManager.handleException(e, Install_servlets.class.getName(), "installPostHandler", e.getMessage()); } finally { if (logWriter != null) { logWriter.close(); } /** * ******************************************************* * STEP 3b CATCH ERROR. GO TO STEP 4 * ******************************************************* */ if (ServerErrorManager.errorStatus()) { response.setStatus(400); response.getWriter().print(ServerErrorManager.getErrorResponse()); } else { /** * ******************************************************* * STEP 3A WRITE RESPONSE ERROR. GO TO STEP 4 * ******************************************************* */ JsonObject obj = new JsonObject(); obj.add("success", new JsonPrimitive(true)); response.getWriter().print(obj.toString()); } /** * ******************************************************* * STEP 4 FORCE DBConnection Reloaded * ******************************************************** */ // Properties properties = new Properties(); // properties.load(DBConnectionManager.class.getResourceAsStream("/conf/data_location.properties")); // String data_location = properties.getProperty("data_location"); // this.DATA_LOCATION = data_location; // DBConnectionManager.setDataLocation(data_location); // DBConnectionManager.reloadConnectionManager(); } //CATCH IF THE ERROR OCCURRED IN ROLL BACK OR CONNECTION CLOSE return response; }