canonical example by Tabnine
public void runQuery(String url, String sql) { try (Connection connection = DriverManager.getConnection(url); PreparedStatement preparedStatement = connection.prepareStatement(sql)) { // ... add parameters to the SQL query using PreparedStatement methods: // setInt, setString, etc. try (ResultSet resultSet = preparedStatement.executeQuery()) { while (resultSet.next()) { // ... do something with result set } } } catch (SQLException e) { // ... handle SQL exception } }
private static String getSessionProperty(Connection connection, String key, String valueType) throws SQLException { try (Statement statement = connection.createStatement()) { ResultSet rs = statement.executeQuery("SHOW SESSION"); while (rs.next()) { if (rs.getString("Name").equals(key)) { return rs.getString(valueType); } } } return null; }
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) { } }
/** * Get the user name for this id. * This method is called by the database. * * @param conn the connection * @param id the user id * @return the user name */ public static String getUserById(Connection conn, int id) throws SQLException { PreparedStatement prep = conn.prepareStatement( "SELECT NAME FROM INFORMATION_SCHEMA.USERS WHERE ID=?"); prep.setInt(1, id); ResultSet rs = prep.executeQuery(); if (rs.next()) { return rs.getString(1); } return null; }
private static void assertConnectionSource(Connection connection, String expectedSource) throws SQLException { String queryId; try (Statement statement = connection.createStatement(); ResultSet rs = statement.executeQuery("SELECT 123")) { queryId = rs.unwrap(PrestoResultSet.class).getQueryId(); } try (PreparedStatement statement = connection.prepareStatement( "SELECT source FROM system.runtime.queries WHERE query_id = ?")) { statement.setString(1, queryId); try (ResultSet rs = statement.executeQuery()) { assertTrue(rs.next()); assertThat(rs.getString("source")).isEqualTo(expectedSource); assertFalse(rs.next()); } } } }
@Test public void testStringQueryWithoutResults() throws SQLException { given(resultSet.next()).willReturn(false); StringQuery query = new StringQuery(dataSource, SELECT_FORENAME_EMPTY); String[] results = query.run(); assertThat(results, is(equalTo(new String[0]))); verify(connection).prepareStatement(SELECT_FORENAME_EMPTY); verify(resultSet).close(); verify(preparedStatement).close(); verify(connection).close(); }
@Test public void testStringQueryWithResults() throws Exception { String[] dbResults = new String[] { "alpha", "beta", "charlie" }; given(resultSet.next()).willReturn(true, true, true, false); given(resultSet.getString(1)).willReturn(dbResults[0], dbResults[1], dbResults[2]); StringQuery query = new StringQuery(dataSource, SELECT_FORENAME); query.setRowsExpected(3); String[] results = query.run(); assertThat(results, is(equalTo(dbResults))); verify(connection).prepareStatement(SELECT_FORENAME); verify(resultSet).close(); verify(preparedStatement).close(); verify(connection).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(timeOut = 10_000) public void testPartialCancel() throws Exception { try (Connection connection = createConnection(); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SELECT count(*) FROM blackhole.test.slow")) { statement.unwrap(PrestoStatement.class).partialCancel(); assertTrue(resultSet.next()); assertEquals(resultSet.getLong(1), 0); } }
@Test public void testCloseConnectionOnRequest() throws Exception { String sql = "SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3"; given(this.resultSet.next()).willReturn(false); given(this.connection.createStatement()).willReturn(this.preparedStatement); RowCountCallbackHandler rcch = new RowCountCallbackHandler(); this.template.query(sql, rcch); verify(this.resultSet).close(); verify(this.preparedStatement).close(); verify(this.connection).close(); }
public void create(User user) throws SQLException { try ( Connection connection = dataSource.getConnection(); PreparedStatement statement = connection.prepareStatement(SQL_INSERT, Statement.RETURN_GENERATED_KEYS); ) { statement.setString(1, user.getName()); statement.setString(2, user.getPassword()); statement.setString(3, user.getEmail()); // ... int affectedRows = statement.executeUpdate(); if (affectedRows == 0) { throw new SQLException("Creating user failed, no rows affected."); } try (ResultSet generatedKeys = statement.getGeneratedKeys()) { if (generatedKeys.next()) { user.setId(generatedKeys.getLong(1)); } else { throw new SQLException("Creating user failed, no ID obtained."); } } } }
@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(); }
private static Long executeScalar(PreparedStatement ps, Long defaultValue) throws SQLException { ResultSet rs = null; try { rs = ps.executeQuery(); if (rs.next()) { return rs.getLong(1); } return defaultValue; } finally { closeQuietly(rs); } }
private void checkRepresentation(String expression, int expectedSqlType, ResultAssertion assertion) throws Exception { try (ResultSet rs = statement.executeQuery("SELECT " + expression)) { ResultSetMetaData metadata = rs.getMetaData(); assertEquals(metadata.getColumnCount(), 1); assertEquals(metadata.getColumnType(1), expectedSqlType); assertTrue(rs.next()); assertion.accept(rs, 1); assertFalse(rs.next()); } }
private String getOriginalTaskId(final String taskId) { String sql = String.format("SELECT original_task_id FROM %s WHERE task_id = '%s' and state='%s' LIMIT 1", TABLE_JOB_STATUS_TRACE_LOG, taskId, State.TASK_STAGING); String result = ""; try ( Connection conn = dataSource.getConnection(); PreparedStatement preparedStatement = conn.prepareStatement(sql); ResultSet resultSet = preparedStatement.executeQuery() ) { if (resultSet.next()) { return resultSet.getString("original_task_id"); } } catch (final SQLException ex) { // TODO 记录失败直接输出日志,未来可考虑配置化 log.error(ex.getMessage()); } return result; }
/** * Get the name of the current schema. * This method is called by the database. * * @param conn the connection * @return the schema name */ public static String getCurrentSchema(Connection conn) throws SQLException { ResultSet rs = conn.createStatement().executeQuery("call schema()"); rs.next(); return rs.getString(1); }
@Before public void setUp() throws SQLException { given(connection.createStatement()).willReturn(statement); given(connection.prepareStatement(anyString())).willReturn(preparedStatement); given(statement.executeQuery(anyString())).willReturn(resultSet); given(preparedStatement.executeQuery()).willReturn(resultSet); given(resultSet.next()).willReturn(true, true, false); given(resultSet.getString(1)).willReturn("tb1", "tb2"); given(resultSet.getInt(2)).willReturn(1, 2); template.setDataSource(new SingleConnectionDataSource(connection, false)); template.setExceptionTranslator(new SQLStateSQLExceptionTranslator()); template.afterPropertiesSet(); }
@Test public void testQueryForObjectWithMapAndInteger() throws Exception { given(resultSet.getMetaData()).willReturn(resultSetMetaData); given(resultSet.next()).willReturn(true, false); given(resultSet.getInt(1)).willReturn(22); Map<String, Object> params = new HashMap<>(); params.put("id", 3); Object o = template.queryForObject("SELECT AGE FROM CUSTMR WHERE ID = :id", params, Integer.class); assertTrue("Correct result type", o instanceof Integer); verify(connection).prepareStatement("SELECT AGE FROM CUSTMR WHERE ID = ?"); verify(preparedStatement).setObject(1, 3); }
@Test public void testQueryWithRowCallbackHandlerNoParameters() throws SQLException { given(resultSet.next()).willReturn(true, false); given(resultSet.getInt("id")).willReturn(1); given(resultSet.getString("forename")).willReturn("rod"); final List<Customer> customers = new LinkedList<>(); namedParameterTemplate.query(SELECT_NO_PARAMETERS, rs -> { Customer cust = new Customer(); cust.setId(rs.getInt(COLUMN_NAMES[0])); cust.setForename(rs.getString(COLUMN_NAMES[1])); customers.add(cust); }); assertEquals(1, customers.size()); assertTrue("Customer id was assigned correctly", customers.get(0).getId() == 1); assertTrue("Customer forename was assigned correctly", customers.get(0).getForename().equals("rod")); verify(connection).prepareStatement(SELECT_NO_PARAMETERS); verify(preparedStatement).close(); verify(connection).close(); }
public static List<Map<String, Object>> executeQuery(Connection conn, String sql, List<Object> parameters) throws SQLException { List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>(); PreparedStatement stmt = null; ResultSet rs = null; try { stmt = conn.prepareStatement(sql); setParameters(stmt, parameters); rs = stmt.executeQuery(); ResultSetMetaData rsMeta = rs.getMetaData(); while (rs.next()) { Map<String, Object> row = new LinkedHashMap<String, Object>(); for (int i = 0, size = rsMeta.getColumnCount(); i < size; ++i) { String columName = rsMeta.getColumnLabel(i + 1); Object value = rs.getObject(i + 1); row.put(columName, value); } rows.add(row); } } finally { JdbcUtils.close(rs); JdbcUtils.close(stmt); } return rows; }