List of usage examples for java.sql ResultSet isFirst
boolean isFirst() throws SQLException;
ResultSet object. From source file:Main.java
public static void main(String[] argv) throws Exception { String driverName = "com.jnetdirect.jsql.JSQLDriver"; Class.forName(driverName);// w w w .ja v a 2 s . co m String serverName = "127.0.0.1"; String portNumber = "1433"; String mydatabase = serverName + ":" + portNumber; String url = "jdbc:JSQLConnect://" + mydatabase; String username = "username"; String password = "password"; Connection connection = DriverManager.getConnection(url, username, password); Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table"); // Get cursor position int pos = resultSet.getRow(); boolean b = resultSet.isBeforeFirst(); // Move cursor to the first row resultSet.next(); // Get cursor position pos = resultSet.getRow(); b = resultSet.isFirst(); // Move cursor to the last row resultSet.last(); // Get cursor position pos = resultSet.getRow(); b = resultSet.isLast(); // Move cursor past last row resultSet.afterLast(); // Get cursor position pos = resultSet.getRow(); b = resultSet.isAfterLast(); }
From source file:Main.java
public static void main(String[] args) throws Exception { Connection conn = getConnection(); Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); st.executeUpdate("create table survey (id int,name varchar(30));"); st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')"); st.executeUpdate("insert into survey (id,name ) values (2,null)"); st.executeUpdate("insert into survey (id,name ) values (3,'Tom')"); ResultSet rs = st.executeQuery("SELECT * FROM survey"); // Get cursor position int pos = rs.getRow(); // 0 System.out.println(pos);//from w w w .j a va2 s . c o m boolean b = rs.isBeforeFirst(); // true System.out.println(b); // Move cursor to the first row rs.next(); // Get cursor position pos = rs.getRow(); // 1 b = rs.isFirst(); // true System.out.println(pos); System.out.println(b); // Move cursor to the last row rs.last(); // Get cursor position pos = rs.getRow(); System.out.println(pos); b = rs.isLast(); // true // Move cursor past last row rs.afterLast(); // Get cursor position pos = rs.getRow(); b = rs.isAfterLast(); // true rs.close(); st.close(); conn.close(); }
From source file:Main.java
public static void main(String[] args) throws Exception { try {//from ww w . j a va 2 s . c om String url = "jdbc:odbc:yourdatabasename"; String driver = "sun.jdbc.odbc.JdbcOdbcDriver"; String user = "guest"; String password = "guest"; Class.forName(driver); Connection connection = DriverManager.getConnection(url, user, password); Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); String sqlQuery = "SELECT EMPNO, EName, Job, MGR, HIREDATE FROM EMP"; ResultSet rs = stmt.executeQuery(sqlQuery); int rowSize = 0; while (rs.next()) { rowSize++; } System.out.println("Number of Rows in ResultSet is: " + rowSize); if (rowSize == 0) { System.out.println("Since there are no rows, exiting..."); System.exit(0); } int cursorPosition = Math.round(rowSize / 2); System.out.println("Moving to position: " + cursorPosition); rs.absolute(cursorPosition); System.out.println("Name: " + rs.getString(2)); rs.relative(-1); cursorPosition = rs.getRow(); System.out.println("Moving to position: " + cursorPosition); System.out.println("Name: " + rs.getString(2)); System.out.println("Moving to the first row"); while (!rs.isFirst()) { rs.previous(); } System.out.println("Name: " + rs.getString(2)); connection.close(); } catch (Exception e) { System.err.println(e); } }
From source file:edu.ku.brc.af.auth.specify.SpecifySecurityMgr.java
@Override public boolean authenticateDB(final String user, final String pass, final String driverClass, final String url, final String dbUserName, final String dbPwd) throws Exception { Connection conn = null;/* ww w . j av a2s. c o m*/ Statement stmt = null; boolean passwordMatch = false; try { Class.forName(driverClass); conn = DriverManager.getConnection(url, dbUserName, dbPwd); String query = "SELECT * FROM specifyuser where name='" + user + "'"; //$NON-NLS-1$ //$NON-NLS-2$ stmt = conn.createStatement(); ResultSet result = stmt.executeQuery(query); String dbPassword = null; while (result.next()) { if (!result.isFirst()) { throw new LoginException("authenticateDB - Ambiguous user (located more than once): " + user); //$NON-NLS-1$ } dbPassword = result.getString(result.findColumn("Password")); //$NON-NLS-1$ if (StringUtils.isNotEmpty(dbPassword) && StringUtils.isAlphanumeric(dbPassword) && UIHelper.isAllCaps(dbPassword) && dbPassword.length() > 20) { dbPassword = Encryption.decrypt(dbPassword, pass); } break; } /*if (dbPassword == null) { throw new LoginException("authenticateDB - Password for User " + user + " undefined."); //$NON-NLS-1$ //$NON-NLS-2$ }*/ if (pass != null && dbPassword != null && pass.equals(dbPassword)) { passwordMatch = true; } // else: passwords do NOT match, user will not be authenticated } catch (java.lang.ClassNotFoundException e) { e.printStackTrace(); edu.ku.brc.af.core.UsageTracker.incrHandledUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(SpecifySecurityMgr.class, e); log.error("authenticateDB - Could not connect to database, driverclass - ClassNotFoundException: "); //$NON-NLS-1$ log.error(e.getMessage()); throw new LoginException("authenticateDB - Database driver class not found: " + driverClass); //$NON-NLS-1$ } catch (SQLException ex) { if (debug) log.error("authenticateDB - SQLException: " + ex.toString()); //$NON-NLS-1$ if (debug) log.error("authenticateDB - " + ex.getMessage()); //$NON-NLS-1$ throw new LoginException("authenticateDB - SQLException: " + ex.getMessage()); //$NON-NLS-1$ } finally { try { if (conn != null) conn.close(); if (stmt != null) stmt.close(); } catch (SQLException e) { edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(SpecifySecurityMgr.class, e); log.error("Exception caught: " + e.toString()); //$NON-NLS-1$ e.printStackTrace(); } } return passwordMatch; }
From source file:org.castor.cpa.persistence.sql.keygen.HighLowKeyGenerator.java
/** * {@inheritDoc}//w ww. j a va 2 s . c o m */ public synchronized Object generateKey(final Connection conn, final String tableName, final String primKeyName) throws PersistenceException { String intTableName = tableName; if (_global) { intTableName = _globalKey; } HighLowValueHandler<? extends Object> handler = _handlers.get(intTableName); if (handler == null) { if (_sqlType == Types.INTEGER) { KeyGeneratorTypeHandlerInteger typeHandler = new KeyGeneratorTypeHandlerInteger(false); handler = new HighLowValueHandler<Integer>(intTableName, _grabSize, typeHandler); } else if (_sqlType == Types.BIGINT) { KeyGeneratorTypeHandlerLong typeHandler = new KeyGeneratorTypeHandlerLong(false); handler = new HighLowValueHandler<Long>(intTableName, _grabSize, typeHandler); } else { KeyGeneratorTypeHandlerBigDecimal typeHandler = new KeyGeneratorTypeHandlerBigDecimal(false); handler = new HighLowValueHandler<BigDecimal>(intTableName, _grabSize, typeHandler); } _handlers.put(intTableName, handler); } if (!handler.hasNext()) { // Create "SELECT seq_val FROM seq_table WHERE seq_key='table'" // with database-dependent keyword for lock // Note: Some databases (InstantDB, HypersonicSQL) don't support such locks. QueryExpression query = _factory.getQueryExpression(); query.addColumn(_seqTable, _seqValue); query.addCondition(_seqTable, _seqKey, QueryExpression.OP_EQUALS, JDBCSyntax.PARAMETER); String lockSQL = query.getStatement(true); // For the case that "SELECT FOR UPDATE" is not supported, perform dirty checking String updateSQL = "UPDATE " + _seqTable + " SET " + _seqValue + "=" + JDBCSyntax.PARAMETER + JDBCSyntax.WHERE + _seqKey + QueryExpression.OP_EQUALS + JDBCSyntax.PARAMETER + JDBCSyntax.AND + _seqValue + QueryExpression.OP_EQUALS + JDBCSyntax.PARAMETER; String maxSQL = JDBCSyntax.SELECT + "MAX(" + primKeyName + ") " + "FROM " + intTableName; String insertSQL = "INSERT INTO " + _seqTable + " (" + _seqKey + "," + _seqValue + ") VALUES (?, ?)"; PreparedStatement stmt = null; try { // Separate connection should be committed/rolled back at this point if (!_sameConnection) { conn.rollback(); } // Retry 7 times (lucky number) boolean success = false; for (int i = 0; !success && (i < LOCK_TRIALS); i++) { stmt = conn.prepareStatement(lockSQL); handler.bindTable(stmt, 1); ResultSet rs = stmt.executeQuery(); handler.init(rs); boolean found = rs.isFirst(); stmt.close(); if (found) { stmt = conn.prepareStatement(updateSQL); handler.bindMax(stmt, UPDATE_PARAM_MAX); handler.bindTable(stmt, UPDATE_PARAM_TABLE); handler.bindLast(stmt, UPDATE_PARAM_LAST); success = (stmt.executeUpdate() == 1); stmt.close(); } else { if (!_global) { stmt = conn.prepareStatement(maxSQL); rs = stmt.executeQuery(); handler.init(rs); stmt.close(); } stmt = conn.prepareStatement(insertSQL); handler.bindTable(stmt, INSERT_PARAM_TABLE); handler.bindMax(stmt, INSERT_PARAM_MAX); success = (stmt.executeUpdate() == 1); stmt.close(); } } if (success) { if (!_sameConnection) { conn.commit(); } } else { if (!_sameConnection) { conn.rollback(); } throw new PersistenceException(Messages.format("persist.keyGenFailed", getClass().getName())); } } catch (SQLException ex) { try { if (!_sameConnection) { conn.rollback(); } } catch (SQLException ex2) { LOG.warn("Problem rolling back JDBC transaction.", ex2); } throw new PersistenceException( Messages.format("persist.keyGenSQL", getClass().getName(), ex.toString()), ex); } finally { try { if (stmt != null) { stmt.close(); } } catch (SQLException ex) { LOG.warn(Messages.message("persist.stClosingFailed"), ex); } } } return handler.next(); }
From source file:nl.b3p.viewer.ViewerIntegrationTest.java
/** * test if the database has the right metadata version. * * @throws SQLException if something goes wrong executing the query * @throws ClassNotFoundException if the postgres driver cannot be found. */// w w w.java 2s . c om @Test public void testMetadataVersion() throws SQLException, ClassNotFoundException { // get 'database_version' from table metadata and check that is has the value of 'n' Connection connection = DriverManager.getConnection(databaseprop.getProperty("testdb.url"), databaseprop.getProperty("testdb.username"), databaseprop.getProperty("testdb.password")); ResultSet rs = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY) .executeQuery("SELECT config_value FROM metadata WHERE config_key = 'database_version'"); String actual_config_value = "-1"; while (rs.next()) { actual_config_value = rs.getString("config_value"); } assertThat("There is only one 'database_version' record (first and last should be same record).", rs.isLast(), equalTo(rs.isFirst())); rs.close(); connection.close(); assertEquals("The database version should be the same.", DatabaseSynchronizer.getUpdateNumber(), actual_config_value); }
From source file:org.surfnet.cruncher.repository.StatisticsRepositoryImpl.java
/** * {@inheritDoc}//w w w . j a v a 2 s. c om */ @Override public List<LoginData> getLogins(final LocalDate start, final LocalDate end, final String idpEntityId, final String spEntityId) { final List<LoginData> result = new ArrayList<LoginData>(); NamedParameterJdbcTemplate namedJdbcTemplate = new NamedParameterJdbcTemplate(cruncherJdbcTemplate); String query = "select * from aggregated_log_logins " + "where " + "entryday >= :startDate AND " + "entryday <= :endDate AND " + "(:spEntityId IS NULL OR spentityid = :spEntityId) AND " + "(:idpEntityId IS NULL OR idpentityid = :idpEntityId) " + "order by idpentityid, spentityid, entryday "; Map<String, Object> parameterMap = getParameterMap(start, end, idpEntityId, spEntityId); namedJdbcTemplate.query(query, parameterMap, new RowMapper<Object>() { private Map<LocalDate, Integer> queryResult = new HashMap<LocalDate, Integer>(); private LoginData currentAggregate = null; @Override public Object mapRow(ResultSet rs, int row) throws SQLException { LoginData currentRow = getLoginDataFromRow(rs); /* * aggregate if sp/idp entityid differs from previous record * do not aggregate if on first record * if on last record, aggregate last entries */ if (!currentRow.equals(currentAggregate) && !rs.isFirst()) { //record is different, aggregate previous one and start fresh result.add(aggregateCurrentEntry(currentAggregate, start, end)); queryResult = new HashMap<LocalDate, Integer>(); } queryResult.put(new LocalDate(rs.getDate("entryday")), rs.getInt("entrycount")); currentAggregate = currentRow; if (rs.isLast()) { // aggregate last set result.add(aggregateCurrentEntry(currentAggregate, start, end)); } /* * This is kinda weird, but single row results are stored in * queryResult (hashmap) or aggregated in result (List<loginData) */ return null; } private LoginData aggregateCurrentEntry(final LoginData loginData, final LocalDate start, final LocalDate end) { LocalDate current = start; int total = 0; while (current.isBefore(end.plusDays(1))) { Integer count = queryResult.get(current); if (count == null) { loginData.getData().add(0); } else { loginData.getData().add(count); total += count; } current = current.plusDays(1); } loginData.setTotal(total); loginData.setPointStart(start.toDate().getTime()); loginData.setPointEnd(end.toDate().getTime()); loginData.setPointInterval(POINT_INTERVAL); return loginData; } private LoginData getLoginDataFromRow(ResultSet rs) throws SQLException { LoginData result = new LoginData(); result.setIdpEntityId(rs.getString("idpentityid")); result.setIdpname(rs.getString("idpentityname")); result.setSpEntityId(rs.getString("spentityid")); result.setSpName(rs.getString("spentityname")); return result; } }); return result; }
From source file:com.l2jfree.gameserver.model.entity.Auction.java
/** Load bidders **/ private void loadBid() { _highestBidderId = 0;/*from w ww. j a v a2 s .com*/ _highestBidderName = ""; _highestBidderMaxBid = 0; Connection con = null; try { PreparedStatement statement; ResultSet rs; con = L2DatabaseFactory.getInstance().getConnection(con); statement = con.prepareStatement( "SELECT bidderId, bidderName, maxBid, clan_name, time_bid FROM auction_bid WHERE auctionId = ? ORDER BY maxBid DESC"); statement.setInt(1, getId()); rs = statement.executeQuery(); while (rs.next()) { if (rs.isFirst()) { _highestBidderId = rs.getInt("bidderId"); _highestBidderName = rs.getString("bidderName"); _highestBidderMaxBid = rs.getInt("maxBid"); } _bidders.put(rs.getInt("bidderId"), new Bidder(rs.getString("bidderName"), rs.getString("clan_name"), rs.getInt("maxBid"), rs.getLong("time_bid"))); } statement.close(); } catch (Exception e) { _log.error("Exception: Auction.loadBid(): ", e); } finally { L2DatabaseFactory.close(con); } }
From source file:at.stefanproell.ResultSetVerification.ResultSetVerificationAPI.java
/** * Calculate hash on DB/*from w w w . j a v a2 s . c o m*/ * * @return */ public String retrieveFullResultSet(ResultSet rs) { this.logger.info("Resulset row count: " + this.getResultSetRowCount(rs)); String resultSetHash = ""; String currentHash = ""; String previousKey = ""; String compositeHash = ""; int hashCounter = 0; long startTime = System.currentTimeMillis(); //int hashCounter =0; try { ResultSetMetaData rsmd = rs.getMetaData(); int columnsNumber = rsmd.getColumnCount(); this.logger.info("There are " + columnsNumber + " columns in the result set"); String newResultSetHash = null; long meanTimeStart = System.currentTimeMillis(); rs.setFetchSize(1000); while (rs.next()) { hashCounter++; if (hashCounter % 1000 == 0) { long meanTimeStop = System.currentTimeMillis(); this.logger.warning("Calculated " + hashCounter + " hashes so far. This batch took " + (double) ((meanTimeStop - meanTimeStart) / 1000) + " seconds"); meanTimeStart = System.currentTimeMillis(); } for (int i = 1; i < columnsNumber; i++) { currentHash += rs.getString(i); } if (rs.isFirst()) { resultSetHash = this.calculateHashFromString(currentHash); } else { compositeHash = (resultSetHash + currentHash); // reset the variables in order to reduce overhead resultSetHash = null; currentHash = null; newResultSetHash = this.calculateHashFromString(compositeHash); //this.logger.info("[resultSetHash] "+resultSetHash + "[currentHash] " + currentHash +" -> // [newResultSetHash]" + newResultSetHash ); resultSetHash = newResultSetHash; } System.gc(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } long endTime = System.currentTimeMillis(); long totalTime = endTime - startTime; double elapsedTime = (double) (totalTime / 1000); this.logger.info("Calculated " + hashCounter + " hash values in " + elapsedTime + " sec"); this.logger.info("Hash is " + resultSetHash); return resultSetHash; }
From source file:ManagerQuery.java
public boolean checkConstantDataForTooMuchTime() { String metricData = ""; int oldDataInt = -1; int newDataInt = -1; double oldDataDouble = -1; double newDataDouble = -1; String oldDataString = ""; String newDataString = ""; Properties conf2 = new Properties(); FileInputStream in = null;/*from w w w. j a v a 2s. co m*/ try { in = new FileInputStream("./config.properties"); conf2.load(in); in.close(); } catch (FileNotFoundException ex) { Logger.getLogger(ManagerQuery.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ManagerQuery.class.getName()).log(Level.SEVERE, null, ex); } Connection conn; try { conn = DriverManager.getConnection(conf2.getProperty("url"), conf2.getProperty("user"), conf2.getProperty("psw")); Statement stm = conn.createStatement(); //SELEZIONA IL METRIC TYPE E, IN BASE AD ESSO, USA IL CAMPO GIUSTO DELLA TABELLA DEI DATI. switch (this.metricType) { case "Intero": metricData = "value_num"; break; case "Float": metricData = "value_num"; break; case "Testuale": metricData = "value_text"; break; case "Series": metricData = "series"; break; } if (this.metricType.contains("Percentuale")) { metricData = "value_perc1"; } String localQuery1 = "SELECT Descriptions.sameDataAlarmCount FROM Dashboard.Descriptions WHERE Descriptions.IdMetric = '" + this.idProc + "' AND sameDataAlarmCount IS NOT NULL"; ResultSet rs1; try { rs1 = stm.executeQuery(localQuery1); if (rs1 != null) { try { if (rs1.next()) { this.sameDataAlarmCount = rs1.getLong("sameDataAlarmCount"); String localQuery2 = "SELECT Data." + metricData + " " + "FROM Dashboard.Data " + "WHERE Data.IdMetric_data = '" + this.idProc + "' " + "ORDER BY Data.computationDate DESC " + "LIMIT " + this.sameDataAlarmCount; ResultSet rs2 = stm.executeQuery(localQuery2); if (rs2 != null) { try { while (rs2.next()) { if (rs2.isFirst()) { switch (this.metricType) { case "Intero": oldDataInt = rs2.getInt(metricData); newDataInt = rs2.getInt(metricData); break; case "Float": oldDataDouble = rs2.getDouble(metricData); newDataDouble = rs2.getDouble(metricData); break; case "Testuale": oldDataString = rs2.getString(metricData); newDataString = rs2.getString(metricData); break; case "Series": oldDataString = rs2.getString(metricData); newDataString = rs2.getString(metricData); break; } if (this.metricType.contains("Percentuale")) { oldDataDouble = rs2.getDouble(metricData); newDataDouble = rs2.getDouble(metricData); } } else { switch (this.metricType) { case "Intero": oldDataInt = newDataInt; newDataInt = rs2.getInt(metricData); if (oldDataInt != newDataInt) { return false; } break; case "Float": oldDataDouble = newDataDouble; newDataDouble = rs2.getDouble(metricData); if (oldDataDouble != newDataDouble) { return false; } break; case "Testuale": oldDataString = newDataString; newDataString = rs2.getString(metricData); if (!oldDataString.equals(newDataString)) { return false; } break; case "Series": oldDataString = newDataString; newDataString = rs2.getString(metricData); if (!oldDataString.equals(newDataString)) { return false; } break; } if (this.metricType.contains("Percentuale")) { oldDataDouble = newDataDouble; newDataDouble = rs2.getDouble(metricData); if (oldDataDouble != newDataDouble) { return false; } } } } //Se si esce dal ciclo senza aver mai ritornato, allora i dati sono costanti return true; } catch (SQLException ex) { Logger.getLogger(ManagerQuery.class.getName()).log(Level.SEVERE, null, ex); return false; } } else { return false; } } else { return false; } } catch (SQLException ex) { Logger.getLogger(ManagerQuery.class.getName()).log(Level.SEVERE, null, ex); return false; } } else { return false; } } catch (SQLException ex) { Logger.getLogger(ManagerQuery.class.getName()).log(Level.SEVERE, null, ex); return false; } } catch (SQLException ex) { Logger.getLogger(ManagerQuery.class.getName()).log(Level.SEVERE, null, ex); return false; } }