Statement stmt = conn.createStatement(); try { ResultSet rs = stmt.executeQuery( "SELECT FULL_NAME FROM EMP" ); try { while ( rs.next() ) { System.out.println( "Name: " + rs.getString("FULL_NAME") ); } } finally { try { rs.close(); } catch (Exception ignore) { } } } finally { try { stmt.close(); } catch (Exception ignore) { } }
@NoWarning("OBL") ResultSet doQuery2(Connection conn, String query) throws SQLException { Statement statement = conn.createStatement(); try { return statement.executeQuery(query); } catch (SQLException e) { statement.close(); throw e; } }
/** * Prepare the transactional {@code Connection} right after transaction begin. * <p>The default implementation executes a "SET TRANSACTION READ ONLY" statement * if the {@link #setEnforceReadOnly "enforceReadOnly"} flag is set to {@code true} * and the transaction definition indicates a read-only transaction. * <p>The "SET TRANSACTION READ ONLY" is understood by Oracle, MySQL and Postgres * and may work with other databases as well. If you'd like to adapt this treatment, * override this method accordingly. * @param con the transactional JDBC Connection * @param definition the current transaction definition * @throws SQLException if thrown by JDBC API * @since 4.3.7 * @see #setEnforceReadOnly */ protected void prepareTransactionalConnection(Connection con, TransactionDefinition definition) throws SQLException { if (isEnforceReadOnly() && definition.isReadOnly()) { Statement stmt = con.createStatement(); try { stmt.executeUpdate("SET TRANSACTION READ ONLY"); } finally { stmt.close(); } } }
@Test public void testQueryForList() throws Exception { String sql = "SELECT AGE FROM CUSTMR WHERE ID < 3"; given(this.resultSet.next()).willReturn(true, true, false); given(this.resultSet.getObject(1)).willReturn(11, 12); List<Map<String, Object>> li = this.template.queryForList(sql); assertEquals("All rows returned", 2, li.size()); assertEquals("First row is Integer", 11, ((Integer) li.get(0).get("age")).intValue()); assertEquals("Second row is Integer", 12, ((Integer) li.get(1).get("age")).intValue()); verify(this.resultSet).close(); verify(this.statement).close(); }
@Test public void testSqlUpdateWithThreadConnection() throws Exception { final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 4"; int rowsAffected = 33; given(this.statement.executeUpdate(sql)).willReturn(rowsAffected); given(this.connection.createStatement()).willReturn(this.statement); int actualRowsAffected = this.template.update(sql); assertTrue("Actual rows affected is correct", actualRowsAffected == rowsAffected); verify(this.statement).close(); verify(this.connection).close(); }
public void close(Statement st, Connection conn) { if (st != null) {try {st.close();} catch (SQLException e) {LogKit.error(e.getMessage(), e);}} if (threadLocal.get() == null) { // in transaction if conn in threadlocal if (conn != null) {try {conn.close();} catch (SQLException e) {throw new ActiveRecordException(e);}} } }
/** * @throws Exception If failed. */ @Test public void testSchemaNameInQuery() throws Exception { Connection conn = DriverManager.getConnection(URL); Statement stmt = conn.createStatement(); assertNotNull(stmt); assertFalse(stmt.isClosed()); stmt.execute("select t._key, t._val from \"cache1\".Integer t"); ResultSet rs = stmt.getResultSet(); while(rs.next()) assertEquals(rs.getInt(2), rs.getInt(1) * 2); stmt.execute("select t._key, t._val from \"cache2\".Integer t"); rs = stmt.getResultSet(); while(rs.next()) assertEquals(rs.getInt(2), rs.getInt(1) * 3); stmt.execute("select t._key, t._val, v._val " + "from \"cache1\".Integer t join \"cache2\".Integer v on t._key = v._key"); rs = stmt.getResultSet(); while(rs.next()) { assertEquals(rs.getInt(2), rs.getInt(1) * 2); assertEquals(rs.getInt(3), rs.getInt(1) * 3); } stmt.close(); }
/** * @throws Exception If failed. */ @org.junit.Test public void testExecuteQuery1() throws Exception { final String sqlText = "select val from test"; try (ResultSet rs = stmt.executeQuery(sqlText)) { assertNotNull(rs); assertTrue(rs.next()); int val = rs.getInt(1); assertTrue("Invalid val: " + val, val >= 1 && val <= 10); } stmt.close(); // Call on a closed statement checkStatementClosed(new RunnableX() { @Override public void run() throws Exception { stmt.executeQuery(sqlText); } }); }
/** * Close ResultSet、Statement、Connection * ThreadLocal support declare transaction. */ public void close(ResultSet rs, Statement st, Connection conn) { if (rs != null) {try {rs.close();} catch (SQLException e) {LogKit.error(e.getMessage(), e);}} if (st != null) {try {st.close();} catch (SQLException e) {LogKit.error(e.getMessage(), e);}} if (threadLocal.get() == null) { // in transaction if conn in threadlocal if (conn != null) {try {conn.close();} catch (SQLException e) {throw new ActiveRecordException(e);}} } }
/** * @throws Exception If failed. */ @org.junit.Test public void testCloseResultSet0() throws Exception { ResultSet rs0 = stmt.executeQuery(SQL); ResultSet rs1 = stmt.executeQuery(SQL); ResultSet rs2 = stmt.executeQuery(SQL); assert rs0.isClosed() : "ResultSet must be implicitly closed after re-execute statement"; assert rs1.isClosed() : "ResultSet must be implicitly closed after re-execute statement"; assert !rs2.isClosed() : "Last result set must be available"; stmt.close(); assert rs2.isClosed() : "ResultSet must be explicitly closed after close statement"; }
Connection conn = dataSource.getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT ID FROM USERS"); ... rs.close(); stmt.close(); conn.close();
@Test public void testQueryForObjectWithBigInteger() throws Exception { String sql = "SELECT AGE FROM CUSTMR WHERE ID = 3"; given(this.resultSet.next()).willReturn(true, false); given(this.resultSet.getObject(1, BigInteger.class)).willReturn(new BigInteger("22")); assertEquals(new BigInteger("22"), this.template.queryForObject(sql, BigInteger.class)); verify(this.resultSet).close(); verify(this.statement).close(); }
/** * Test update with static SQL. */ @Test public void testSqlUpdate() throws Exception { final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 4"; int rowsAffected = 33; given(this.statement.executeUpdate(sql)).willReturn(rowsAffected); given(this.connection.createStatement()).willReturn(this.statement); int actualRowsAffected = this.template.update(sql); assertTrue("Actual rows affected is correct", actualRowsAffected == rowsAffected); verify(this.statement).close(); verify(this.connection).close(); }
/** * Writes the result set of a query to a file in the CSV format. * * @param conn the connection * @param outputFileName the file name * @param sql the query * @param charset the charset or null to use the system default charset * (see system property file.encoding) * @return the number of rows written */ public int write(Connection conn, String outputFileName, String sql, String charset) throws SQLException { Statement stat = conn.createStatement(); ResultSet rs = stat.executeQuery(sql); int rows = write(outputFileName, rs, charset); stat.close(); return rows; }
stmt = conn.createStatement(); while (rs.next()) assertEquals(rs.getInt(2), rs.getInt(1) * 2); while (rs.next()) assertEquals(rs.getInt(2), rs.getInt(1) * 3); while (rs.next()) { assertEquals(rs.getInt(2), rs.getInt(1) * 2); assertEquals(rs.getInt(3), rs.getInt(1) * 3); stmt.close();
/** * @throws Exception If failed. */ @org.junit.Test public void testClose() throws Exception { String sqlText = "select * from test"; ResultSet rs = stmt.executeQuery(sqlText); assertTrue(rs.next()); assertFalse(rs.isClosed()); assertFalse(stmt.isClosed()); stmt.close(); stmt.close(); // Closing closed is ok assertTrue(stmt.isClosed()); // Current result set must be closed assertTrue(rs.isClosed()); }
/** * * Override this to provide an alertnate method of getting * connections (such as caching). One method to fix this is to open * connections at the start of flushBuffer() and close them at the * end. I use a connection pool outside of JDBCAppender which is * accessed in an override of this method. * */ protected void execute(String sql) throws SQLException { Connection con = null; Statement stmt = null; try { con = getConnection(); stmt = con.createStatement(); stmt.executeUpdate(sql); } finally { if(stmt != null) { stmt.close(); } closeConnection(con); } //System.out.println("Execute: " + sql); }
public static int getRowsCount(Connection dbConnection, String tableName) { String sqlStatementString; Statement sqlStatement; ResultSet rs; int rowCount = 0; try { sqlStatement = dbConnection.createStatement(); sqlStatementString = "SELECT COUNT(*) FROM " + tableName; // System.out.println(sqlStatementString); rs = sqlStatement.executeQuery(sqlStatementString); rs.next(); rowCount = rs.getInt(1); rs.close(); sqlStatement.close(); return rowCount; } catch (SQLException e) { return rowCount; } }