public insertUser(String name, String email) { Connection conn = null; PreparedStatement stmt = null; try { conn = setupTheDatabaseConnectionSomehow(); stmt = conn.prepareStatement("INSERT INTO person (name, email) values (?, ?)"); stmt.setString(1, name); stmt.setString(2, email); stmt.executeUpdate(); } finally { try { if (stmt != null) { stmt.close(); } } catch (Exception e) { // log this error } try { if (conn != null) { conn.close(); } } catch (Exception e) { // log this error } } }
private static void createDatabase(File dbHome, String user, String password) throws SQLException { String url = format("jdbc:h2:%s/sonar;USER=%s;PASSWORD=%s", dbHome.getAbsolutePath(), user, password); DriverManager.registerDriver(new Driver()); DriverManager.getConnection(url).close(); } }
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(); while (rs.next()) { System.out.println("name = " + rs.getString("name")); System.out.println("job = " + rs.getString("occupation")); conn.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; }
@Test public void testUpdateCount() throws Exception { final String sql = "UPDATE INVOICE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?"; int idParam = 11111; given(this.preparedStatement.executeUpdate()).willReturn(1); Dispatcher d = new Dispatcher(idParam, sql); int rowsAffected = this.template.update(d); assertTrue("1 update affected 1 row", rowsAffected == 1); verify(this.preparedStatement).setInt(1, idParam); verify(this.preparedStatement).close(); verify(this.connection).close(); }
/** Load driver and make a connection. */ @Test public void testConnect() throws Exception { Class.forName("org.apache.drill.jdbc.Driver"); final Connection connection = DriverManager.getConnection("jdbc:drill:zk=local"); connection.close(); }
/** * @throws Exception If failed. */ @Test public void testCache1() throws Exception { Properties cfg = new Properties(); cfg.setProperty(PROP_NODE_ID, grid(0).localNode().id().toString()); Connection conn = null; try { conn = DriverManager.getConnection(URL, cfg); ResultSet rs = conn.createStatement().executeQuery("select _val from Integer order by _val"); int cnt = 0; while (rs.next()) assertEquals(++cnt, rs.getInt(1)); assertEquals(2, cnt); } finally { if (conn != null) conn.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(); }
@Test public void testIntAddExpression() throws Exception { String query = "SELECT entity_id FROM " + tableName + " where A_INTEGER + 2 = 4"; Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); try { PreparedStatement statement = conn.prepareStatement(query); ResultSet rs = statement.executeQuery(); assertTrue (rs.next()); assertEquals(ROW2, rs.getString(1)); assertFalse(rs.next()); } finally { conn.close(); } }
@Test public void testUpdate() throws SQLException { given(preparedStatement.executeUpdate()).willReturn(1); params.put("perfId", 1); params.put("priceId", 1); int rowsAffected = namedParameterTemplate.update(UPDATE_NAMED_PARAMETERS, params); assertEquals(1, rowsAffected); verify(connection).prepareStatement(UPDATE_NAMED_PARAMETERS_PARSED); verify(preparedStatement).setObject(1, 1); verify(preparedStatement).setObject(2, 1); verify(preparedStatement).close(); verify(connection).close(); }
@Test public void testExecuteNoParameters() throws SQLException { given(preparedStatement.executeUpdate()).willReturn(1); Object result = namedParameterTemplate.execute(SELECT_NO_PARAMETERS, (PreparedStatementCallback<Object>) ps -> { assertEquals(preparedStatement, ps); ps.executeQuery(); return "result"; }); assertEquals("result", result); verify(connection).prepareStatement(SELECT_NO_PARAMETERS); verify(preparedStatement).close(); verify(connection).close(); }
@Test public void testBatchUpdateWithSqlParameterSource() throws Exception { SqlParameterSource[] ids = new SqlParameterSource[2]; ids[0] = new MapSqlParameterSource("id", 100); ids[1] = new MapSqlParameterSource("id", 200); final int[] rowsAffected = new int[] {1, 2}; given(preparedStatement.executeBatch()).willReturn(rowsAffected); given(connection.getMetaData()).willReturn(databaseMetaData); namedParameterTemplate = new NamedParameterJdbcTemplate(new JdbcTemplate(dataSource, false)); int[] actualRowsAffected = namedParameterTemplate.batchUpdate( "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = :id", ids); assertTrue("executed 2 updates", actualRowsAffected.length == 2); assertEquals(rowsAffected[0], actualRowsAffected[0]); assertEquals(rowsAffected[1], actualRowsAffected[1]); verify(connection).prepareStatement("UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?"); verify(preparedStatement).setObject(1, 100); verify(preparedStatement).setObject(1, 200); verify(preparedStatement, times(2)).addBatch(); verify(preparedStatement, atLeastOnce()).close(); verify(connection, atLeastOnce()).close(); }
@Test public void testNegativeAgainstUnsignedNone() throws Exception { String tableName = initTableValues(); String query = "SELECT uint_key, ulong_key, string_key FROM " + tableName + " WHERE ulong_key < -1"; Connection conn = DriverManager.getConnection(getUrl()); try { PreparedStatement statement = conn.prepareStatement(query); ResultSet rs = statement.executeQuery(); assertFalse(rs.next()); } finally { conn.close(); } }
/** * @throws Exception If failed. */ @Test public void testPrepareStatement() throws Exception { try (Connection conn = DriverManager.getConnection(URL)) { // null query text GridTestUtils.assertThrows(log, new Callable<Object>() { @Override public Object call() throws Exception { return conn.prepareStatement(null); } }, SQLException.class, "SQL string cannot be null" ); final String sqlText = "select * from test where param = ?"; try (PreparedStatement prepared = conn.prepareStatement(sqlText)) { assertNotNull(prepared); } conn.close(); // Exception when called on closed connection checkConnectionClosed(new RunnableX() { @Override public void run() throws Exception { conn.prepareStatement(sqlText); } }); } }
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(); }
@NoWarning("ODR_OPEN_DATABASE_RESOURCE") public void init2(String mCmd, String connString) throws Exception { Connection dbCon = null; PreparedStatement stmt = null; try { dbCon = DriverManager.getConnection(connString); stmt = dbCon.prepareStatement(mCmd); // some additional code not related to blocker } catch (SQLException e) { throw new Exception(e); } finally { try { if (null != stmt) stmt.close(); } catch (Throwable e) {/* do nothing */ } try { if (null != dbCon) dbCon.close(); } catch (Throwable e) {/* do nothing */ } } }
public Collection<Permission> executePermissionsQuery() throws SQLException, ClassNotFoundException { List<Permission> result = new LinkedList<>(); if (query != null) { try { try (ResultSet resultSet = statement.executeQuery()) { ResultSetMetaData resultMetaData = resultSet.getMetaData(); while (resultSet.next()) { LinkedHashMap<String, Long> map = new LinkedHashMap<>(); for (int i = 1; i <= resultMetaData.getColumnCount(); i++) { String label = resultMetaData.getColumnLabel(i); map.put(label, resultSet.getLong(label)); } result.add(new Permission(map)); } } } finally { statement.close(); connection.close(); } } return result; }
String sql = String.format(SPLIT_QUERY, escapedQuery, numSplits); try { Connection conn = DriverManager.getConnection(url,user,pwd); try ( Statement stmt = conn.createStatement(); while (res.next()) { } catch (Exception e) { LOG.error("Closing connection due to error", e); conn.close(); throw e;
conn = ds.getConnection(); ps = conn.prepareStatement(this.config.getProperty(ID_QUERY)); ps.setString(1, email); rs = ps.executeQuery(); if (rs.next()) { displayName = rs.getString(1); conn.close(); } catch (Exception ex) {
@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(); }