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 } }
public static void main(String[] args) throws Exception { Class.forName("org.sqlite.JDBC"); Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db"); Statement stat = conn.createStatement(); stat.executeUpdate("drop table if exists people;"); stat.executeUpdate("create table people (name, occupation);"); PreparedStatement prep = conn.prepareStatement( "insert into people values (?, ?);"); prep.setString(1, "Gandhi"); prep.setString(2, "politics"); prep.addBatch(); prep.setString(1, "Turing"); prep.setString(2, "computers"); prep.addBatch(); conn.setAutoCommit(false); prep.executeBatch(); conn.setAutoCommit(true); ResultSet rs = stat.executeQuery("select * from people;"); while (rs.next()) { System.out.println("name = " + rs.getString("name")); System.out.println("job = " + rs.getString("occupation")); rs.close(); conn.close();
/** * Return whether JDBC 3.0 Savepoints are supported. * Caches the flag for the lifetime of this ConnectionHolder. * @throws SQLException if thrown by the JDBC driver */ public boolean supportsSavepoints() throws SQLException { if (this.savepointsSupported == null) { this.savepointsSupported = getConnection().getMetaData().supportsSavepoints(); } return this.savepointsSupported; }
public void commit() throws SQLException { getConn().commit(); if (logger.isTraceEnabled()) { logger.trace("Batch executor commit " + idx.get() + " rows"); } idx.set(0); }
@Override public PreparedStatement getPreparedStatement(Connection connection, String sql) throws SQLException { connection.setAutoCommit(false); PreparedStatement statement = connection.prepareStatement(sql); statement.setFetchSize(1000); return statement; }
/** * Prepare the given Connection before it is exposed. * <p>The default implementation applies the auto-commit flag, if necessary. * Can be overridden in subclasses. * @param con the Connection to prepare * @see #setAutoCommit */ protected void prepareConnection(Connection con) throws SQLException { Boolean autoCommit = getAutoCommitValue(); if (autoCommit != null && con.getAutoCommit() != autoCommit) { con.setAutoCommit(autoCommit); } }
public boolean isValidConnection(final Connection c, String validateQuery, int validationQueryTimeout) throws Exception { if (c.isClosed()) { return false; } Statement stmt = null; try { stmt = c.createStatement(); if (validationQueryTimeout > 0) { stmt.setQueryTimeout(validationQueryTimeout); } stmt.execute(validateQuery); return true; } catch (SQLException e) { throw e; } finally { JdbcUtils.close(stmt); } }
@Test public void testHanaSequenceMaxValueIncrementer() throws SQLException { given(dataSource.getConnection()).willReturn(connection); given(connection.createStatement()).willReturn(statement); given(statement.executeQuery("select myseq.nextval from dummy")).willReturn(resultSet); given(resultSet.next()).willReturn(true); given(resultSet.getLong(1)).willReturn(10L, 12L); HanaSequenceMaxValueIncrementer incrementer = new HanaSequenceMaxValueIncrementer(); incrementer.setDataSource(dataSource); incrementer.setIncrementerName("myseq"); incrementer.setPaddingLength(2); incrementer.afterPropertiesSet(); assertEquals(10, incrementer.nextLongValue()); assertEquals("12", incrementer.nextStringValue()); verify(resultSet, times(2)).close(); verify(statement, times(2)).close(); verify(connection, times(2)).close(); }
@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(); }
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()); } } } }
@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(); }
// assumes... // import java.sql.*; Connection conn=DriverManager.getConnection( "jdbc:ucanaccess://C:/__tmp/test/zzz.accdb"); Statement s = conn.createStatement(); ResultSet rs = s.executeQuery("SELECT [LastName] FROM [Clients]"); while (rs.next()) { System.out.println(rs.getString(1)); }
/** * @throws Exception If failed. */ @Test public void testPortRangeConnect() throws Exception { try (Connection conn = DriverManager.getConnection(URL_PORT_RANGE)) { try (Statement stmt = conn.createStatement()) { stmt.execute("SELECT 1"); ResultSet rs = stmt.getResultSet(); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); } } }
public static void selectAllEmployments(ResultSet[] resultSets) throws SQLException { Connection conn = DriverManager.getConnection( "jdbc:default:connection" ); PreparedStatement statement = conn.prepareStatement( "select EMPLOYEE, EMPLOYER, STARTDATE, ENDDATE," + " REGIONCODE, EMPID, 'VALUE', CURRENCY" + " FROM EMPLOYMENT" ); resultSets[0] = statement.executeQuery(); conn.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(); }
/** * {@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(); } } }
@ExpectWarning("ODR_OPEN_DATABASE_RESOURCE") public void isReported(String url, String username, String password) throws Exception { Connection connection = DriverManager.getConnection(url, username, password); PreparedStatement pstmt = connection.prepareStatement("SELECT count(1) from tab"); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { System.out.println(rs.getString(1)); } }
private void testAvaticaQuery(String url) { LOG.info("URL: " + url); try { Properties connectionProperties = new Properties(); connectionProperties.put("user", "admin"); connectionProperties.put("password", "priest"); Connection connection = DriverManager.getConnection(url, connectionProperties); Statement statement = connection.createStatement(); statement.setMaxRows(450); String query = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS"; ResultSet resultSet = statement.executeQuery(query); Assert.assertTrue(resultSet.next()); statement.close(); connection.close(); } catch (Exception e) { throw new RuntimeException(e); } }
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 testCouldNotClose() throws Exception { SQLException sqlException = new SQLException("bar"); given(this.connection.createStatement()).willReturn(this.statement); given(this.resultSet.next()).willReturn(false); willThrow(sqlException).given(this.resultSet).close(); willThrow(sqlException).given(this.statement).close(); willThrow(sqlException).given(this.connection).close(); RowCountCallbackHandler rcch = new RowCountCallbackHandler(); this.template.query("SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3", rcch); verify(this.connection).close(); }