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) { } }
final PreparedStatement statement = connection.prepareStatement( "SELECT my_column FROM my_table where search_column = ANY (?)" ); final String[] values = getValues(); statement.setArray(1, connection.createArrayOf("text", values)); final ResultSet rs = statement.executeQuery(); try { while(rs.next()) { // do some... } } finally { rs.close(); }
/** * {@inheritDoc} */ @Override public Optional<Customer> getById(int id) throws Exception { ResultSet resultSet = null; try (Connection connection = getConnection(); PreparedStatement statement = connection.prepareStatement("SELECT * FROM CUSTOMERS WHERE ID = ?")) { statement.setInt(1, id); resultSet = statement.executeQuery(); if (resultSet.next()) { return Optional.of(createCustomer(resultSet)); } else { return Optional.empty(); } } catch (SQLException ex) { throw new CustomException(ex.getMessage(), ex); } finally { if (resultSet != null) { resultSet.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(); }
private void doTestQueryForListWithArgs(String sql) throws Exception { 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, new Object[] {3}); 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.preparedStatement).setObject(1, 3); verify(this.resultSet).close(); verify(this.preparedStatement).close(); }
public List<Biler> list() throws SQLException { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; List<Biler> bilers = new ArrayList<Biler>(); try { connection = database.getConnection(); statement = connection.prepareStatement("SELECT id, name, value FROM Biler"); resultSet = statement.executeQuery(); while (resultSet.next()) { Biler biler = new Biler(); biler.setId(resultSet.getLong("id")); biler.setName(resultSet.getString("name")); biler.setValue(resultSet.getInt("value")); bilers.add(biler); } } finally { if (resultSet != null) try { resultSet.close(); } catch (SQLException ignore) {} if (statement != null) try { statement.close(); } catch (SQLException ignore) {} if (connection != null) try { connection.close(); } catch (SQLException ignore) {} } return bilers; }
public void testStoredProcedureWithUndeclaredResults() throws Exception { ResultSet resultSet1 = mock(ResultSet.class); given(resultSet1.next()).willReturn(true, true, false); given(resultSet1.getString(2)).willReturn("Foo", "Bar"); given(resultSet2.next()).willReturn(true, false); given(resultSet2.getObject(1)).willReturn("Spam"); given(resultSet2.getObject(2)).willReturn("Eggs"); verify(resultSet1).close(); verify(resultSet2).close();
@Test public void testUpdateAndGeneratedKeys() throws SQLException { given(resultSetMetaData.getColumnCount()).willReturn(1); given(resultSetMetaData.getColumnLabel(1)).willReturn("1"); given(resultSet.getMetaData()).willReturn(resultSetMetaData); given(resultSet.next()).willReturn(true, false); given(resultSet.getObject(1)).willReturn(11); given(preparedStatement.executeUpdate()).willReturn(1); given(preparedStatement.getGeneratedKeys()).willReturn(resultSet); given(connection.prepareStatement(INSERT_GENERATE_KEYS, PreparedStatement.RETURN_GENERATED_KEYS) ).willReturn(preparedStatement); GeneratedKeysUpdater pc = new GeneratedKeysUpdater(); KeyHolder generatedKeyHolder = new GeneratedKeyHolder(); int rowsAffected = pc.run("rod", generatedKeyHolder); assertEquals(1, rowsAffected); assertEquals(1, generatedKeyHolder.getKeyList().size()); assertEquals(11, generatedKeyHolder.getKey().intValue()); verify(preparedStatement).setString(1, "rod"); verify(resultSet).close(); }
/** * Same as {@code com.facebook.presto.jdbc.TestJdbcPreparedStatement#testDeallocate()}. This one is run for TeradataJdbcDriver as well. */ @Test(groups = JDBC) public void testDeallocate() throws Exception { try (Connection connection = connection()) { for (int i = 0; i < 200; i++) { try { try (PreparedStatement preparedStatement = connection.prepareStatement("SELECT '" + repeat("a", 300) + "'")) { preparedStatement.executeQuery().close(); // Let's not assume when PREPARE actually happens } } catch (Exception e) { throw new RuntimeException("Failed at " + i, e); } } } }
private HashMap<Long, HashMap<String, Object>> buildSchemaMap(Connection conn) throws SQLException { HashMap<Long, HashMap<String, Object>> schemas = new HashMap<>(); PreparedStatement p = conn.prepareStatement("SELECT * from `schemas`"); ResultSet rs = p.executeQuery(); ResultSetMetaData md = rs.getMetaData(); while ( rs.next() ) { HashMap<String, Object> row = new HashMap<>(); for ( int i = 1; i <= md.getColumnCount(); i++ ) row.put(md.getColumnName(i), rs.getObject(i)); schemas.put(rs.getLong("id"), row); } rs.close(); return schemas; }
@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 public void testQueryForLongWithArgs() throws Exception { String sql = "SELECT AGE FROM CUSTMR WHERE ID = ?"; given(this.resultSet.next()).willReturn(true, false); given(this.resultSet.getLong(1)).willReturn(87L); long l = this.template.queryForObject(sql, new Object[] {3}, Long.class).longValue(); assertEquals("Return of a long", 87, l); verify(this.preparedStatement).setObject(1, 3); verify(this.resultSet).close(); verify(this.preparedStatement).close(); }
@Test public void testQueryForObjectWithString() throws Exception { String sql = "SELECT AGE FROM CUSTMR WHERE ID = 3"; given(this.resultSet.next()).willReturn(true, false); given(this.resultSet.getString(1)).willReturn("myvalue"); assertEquals("myvalue", this.template.queryForObject(sql, String.class)); verify(this.resultSet).close(); verify(this.statement).close(); }
given(metaDataResultSet.next()).willReturn(true, false); given(metaDataResultSet.getString("TABLE_SCHEM")).willReturn(USER); given(metaDataResultSet.getString("TABLE_NAME")).willReturn(TABLE); given(metaDataResultSet.getString("TABLE_TYPE")).willReturn("TABLE"); given(columnsResultSet.next()).willReturn(true, false); given(columnsResultSet.getString("COLUMN_NAME")).willReturn("id"); given(columnsResultSet.getInt("DATA_TYPE")).willReturn(Types.INTEGER); verify(metaDataResultSet, atLeastOnce()).next(); verify(columnsResultSet, atLeastOnce()).next(); verify(metaDataResultSet).close(); verify(columnsResultSet).close();
final PreparedStatement statement = connection.prepareStatement( "SELECT my_column FROM my_table " + "where search_column IN (SELECT * FROM unnest(?))" ); final String[] values = getValues(); statement.setArray(1, connection.createArrayOf("text", values)); final ResultSet rs = statement.executeQuery(); try { while(rs.next()) { // do some... } } finally { rs.close(); }
public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException { DatabaseMetaData metaData = ps.getConnection().getMetaData(); // String sName = getIdentifierName(schemaName, metaData); String tName = getIdentifierName(tableName, metaData); ps.setString(1, tName); ResultSet rs = ps.executeQuery(); String log = null; if (rs.next()) { log = rs.getString("KEYS"); } rs.close(); return log; } });
@Test public void testQueryForObjectWithBigDecimal() throws Exception { String sql = "SELECT AGE FROM CUSTMR WHERE ID = 3"; given(this.resultSet.next()).willReturn(true, false); given(this.resultSet.getBigDecimal(1)).willReturn(new BigDecimal("22.5")); assertEquals(new BigDecimal("22.5"), this.template.queryForObject(sql, BigDecimal.class)); verify(this.resultSet).close(); verify(this.statement).close(); }
@Test public void testQueryForListWithArgsAndSingleRowAndColumn() throws Exception { String sql = "SELECT AGE FROM CUSTMR WHERE ID < ?"; given(this.resultSet.next()).willReturn(true, false); given(this.resultSet.getObject(1)).willReturn(11); List<Map<String, Object>> li = this.template.queryForList(sql, new Object[] {3}); assertEquals("All rows returned", 1, li.size()); assertEquals("First row is Integer", 11, ((Integer) li.get(0).get("age")).intValue()); verify(this.preparedStatement).setObject(1, 3); verify(this.resultSet).close(); verify(this.preparedStatement).close(); }
@Test public void testQueryForObjectThrowsIncorrectResultSizeForMoreThanOneRow() throws Exception { String sql = "select pass from t_account where first_name='Alef'"; given(this.resultSet.next()).willReturn(true, true, false); given(this.resultSet.getString(1)).willReturn("pass"); this.thrown.expect(IncorrectResultSizeDataAccessException.class); try { this.template.queryForObject(sql, String.class); } finally { verify(this.resultSet).close(); verify(this.statement).close(); } }
@Test @SuppressWarnings("unchecked") public void testStoredProcedureSkippingUndeclaredResults() throws Exception { ResultSet resultSet = mock(ResultSet.class); given(resultSet.next()).willReturn(true, true, false); given(resultSet.getString(2)).willReturn("Foo", "Bar"); given(callableStatement.execute()).willReturn(true); given(callableStatement.getUpdateCount()).willReturn(-1); given(callableStatement.getResultSet()).willReturn(resultSet); given(callableStatement.getMoreResults()).willReturn(true, false); given(callableStatement.getUpdateCount()).willReturn(-1, -1); given(connection.prepareCall("{call " + StoredProcedureWithResultSetMapped.SQL + "()}") ).willReturn(callableStatement); JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); jdbcTemplate.setSkipUndeclaredResults(true); StoredProcedureWithResultSetMapped sproc = new StoredProcedureWithResultSetMapped( jdbcTemplate); Map<String, Object> res = sproc.execute(); assertEquals("incorrect number of returns", 1, res.size()); List<String> rs1 = (List<String>) res.get("rs"); assertEquals(2, rs1.size()); assertEquals("Foo", rs1.get(0)); assertEquals("Bar", rs1.get(1)); verify(resultSet).close(); }