@Override @SuppressFBWarnings(value = {"OBL_UNSATISFIED_OBLIGATION_EXCEPTION_EDGE", "SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING"}, justification = "findbugs does not trust jdbctemplate, sql is constant in practice") public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement( "DELETE FROM group_capacity WHERE group_id = ?;"); ps.setString(1, group); return ps; } };
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 } } }
/** * Delete undo log. * * @param xid the xid * @param branchId the branch id * @param conn the conn * @throws SQLException the sql exception */ public static void deleteUndoLog(String xid, long branchId, Connection conn) throws SQLException { PreparedStatement deletePST = conn.prepareStatement(DELETE_UNDO_LOG_SQL); deletePST.setLong(1, branchId); deletePST.setString(2, xid); deletePST.executeUpdate(); } }
private static boolean existsProperty(Connection connection, String template, String id, String key) throws SQLException { Preconditions.checkArgument(!Strings.isNullOrEmpty(id)); Preconditions.checkArgument(!Strings.isNullOrEmpty(key)); try (PreparedStatement queryStatement = connection.prepareStatement(template)) { int index = 0; queryStatement.setString(++index, id); queryStatement.setString(++index, key); try (ResultSet resultSet = queryStatement.executeQuery()) { return resultSet.next(); } } }
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 testPreparedStatementSetterSucceeds() throws Exception { final String sql = "UPDATE FOO SET NAME=? WHERE ID = 1"; final String name = "Gary"; int expectedRowsUpdated = 1; given(this.preparedStatement.executeUpdate()).willReturn(expectedRowsUpdated); PreparedStatementSetter pss = ps -> ps.setString(1, name); int actualRowsUpdated = new JdbcTemplate(this.dataSource).update(sql, pss); assertEquals("updated correct # of rows", actualRowsUpdated, expectedRowsUpdated); verify(this.preparedStatement).setString(1, name); verify(this.preparedStatement).close(); verify(this.connection).close(); }
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(); prep.setString(1, "Wittgenstein"); prep.setString(2, "smartypants"); prep.addBatch(); while (rs.next()) { System.out.println("name = " + rs.getString("name")); System.out.println("job = " + rs.getString("occupation")); conn.close();
static void perform(Connection cxn, long id, String selections, boolean isBlacklist) throws SQLException { try (PreparedStatement ps = cxn.prepareStatement("UPDATE pipelineselections SET selections = NULL, version = ?, filters = ? WHERE id = ?")) { ps.setInt(1, SCHEMA); ps.setString(2, asJson(selections, isBlacklist)); ps.setLong(3, id); ps.executeUpdate(); } }
@ExpectWarning("OBL_UNSATISFIED_OBLIGATION_EXCEPTION_EDGE") void updateResetFailuresToZero(String ipAddr, Connection conn) throws SQLException { PreparedStatement ps = conn.prepareStatement("UPDATE resetpasswordfailures SET failureCount=0 WHERE IPAddress=?"); ps.setString(1, ipAddr); ps.executeUpdate(); conn.close(); ps.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(); }
@Test public void testPreparedStatementSetterFails() throws Exception { final String sql = "UPDATE FOO SET NAME=? WHERE ID = 1"; final String name = "Gary"; SQLException sqlException = new SQLException(); given(this.preparedStatement.executeUpdate()).willThrow(sqlException); PreparedStatementSetter pss = ps -> ps.setString(1, name); this.thrown.expect(DataAccessException.class); this.thrown.expect(exceptionCause(sameInstance(sqlException))); try { new JdbcTemplate(this.dataSource).update(sql, pss); } finally { verify(this.preparedStatement).setString(1, name); verify(this.preparedStatement).close(); verify(this.connection, atLeastOnce()).close(); } }
@Override @SuppressFBWarnings(value = {"OBL_UNSATISFIED_OBLIGATION_EXCEPTION_EDGE", "SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING"}, justification = "findbugs does not trust jdbctemplate, sql is constant in practice") public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement( "DELETE FROM tenant_capacity WHERE tenant_id = ?;"); ps.setString(1, tenant); return ps; } };
private Long findSchemaForPositionSHA(Connection c, String sha) throws SQLException { PreparedStatement p = c.prepareStatement("SELECT * from `schemas` where position_sha = ?"); p.setString(1, sha); ResultSet rs = p.executeQuery(); if ( rs.next() ) { Long id = rs.getLong("id"); LOGGER.debug("findSchemaForPositionSHA: found schema_id: " + id + " for sha: " + sha); return id; } else { return null; } }
static void perform(Connection cxn, long id, String filters) throws SQLException { try (PreparedStatement ps = cxn.prepareStatement("UPDATE pipelineselections SET version = ?, filters = ? WHERE id = ?")) { ps.setInt(1, SCHEMA); ps.setString(2, addStateIfNull(filters)); ps.setLong(3, id); ps.executeUpdate(); } }
@NoWarning("OBL") void updateResetFailuresToZeroOK2(String ipAddr, Connection conn) throws SQLException { PreparedStatement ps = conn.prepareStatement("UPDATE resetpasswordfailures SET failureCount=0 WHERE IPAddress=?"); try { ps.setString(1, ipAddr); ps.executeUpdate(); } finally { ps.close(); conn.close(); } }
/** * {@inheritDoc} */ @Override public boolean add(Customer customer) throws Exception { if (getById(customer.getId()).isPresent()) { return false; } try (Connection connection = getConnection(); PreparedStatement statement = connection.prepareStatement("INSERT INTO CUSTOMERS VALUES (?,?,?)")) { statement.setInt(1, customer.getId()); statement.setString(2, customer.getFirstName()); statement.setString(3, customer.getLastName()); statement.execute(); return true; } catch (SQLException ex) { throw new CustomException(ex.getMessage(), ex); } }
String getMode() throws SQLException { if (mode == null) { PreparedStatement prep = prepareStatement( "SELECT VALUE FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME=?"); prep.setString(1, "MODE"); ResultSet rs = prep.executeQuery(); rs.next(); mode = rs.getString(1); prep.close(); } return mode; }
/** * {@inheritDoc} */ @Override public boolean update(Customer customer) throws Exception { try (Connection connection = getConnection(); PreparedStatement statement = connection.prepareStatement("UPDATE CUSTOMERS SET FNAME = ?, LNAME = ? WHERE ID = ?")) { statement.setString(1, customer.getFirstName()); statement.setString(2, customer.getLastName()); statement.setInt(3, customer.getId()); return statement.executeUpdate() > 0; } catch (SQLException ex) { throw new CustomException(ex.getMessage(), ex); } }
@DesireNoWarning("OBL") void updateResetFailuresToZeroOK3(String ipAddr, Connection conn) throws SQLException { PreparedStatement ps = conn.prepareStatement("UPDATE resetpasswordfailures SET failureCount=0 WHERE IPAddress=?"); try { ps.setString(1, ipAddr); ps.executeUpdate(); } finally { conn.close(); } }
public MonitorInstance findInst(String domain, String app, String cluster, String host) throws SQLException { String sql = "select id, domain, app, cluster, host, ip, lastActiveTime, lastPID from druid_inst " // + " where domain = ? and app = ? and cluster = ? and host = ? " // + " limit 1"; Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { conn = dataSource.getConnection(); stmt = conn.prepareStatement(sql); stmt.setString(1, domain); stmt.setString(2, app); stmt.setString(3, cluster); stmt.setString(4, host); rs = stmt.executeQuery(); if (rs.next()) { return readInst(rs); } return null; } finally { JdbcUtils.close(rs); JdbcUtils.close(stmt); JdbcUtils.close(conn); } }