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 } }
/** * 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 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; }
/** * {@inheritDoc} */ @Override public boolean delete(Customer customer) throws Exception { try (Connection connection = getConnection(); PreparedStatement statement = connection.prepareStatement("DELETE FROM CUSTOMERS WHERE ID = ?")) { statement.setInt(1, customer.getId()); return statement.executeUpdate() > 0; } catch (SQLException ex) { throw new CustomException(ex.getMessage(), ex); } } }
@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); }
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(); }
/** * {@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 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(); }
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."); } } } }
try { connection = getConnection(); PreparedStatement statement = connection.prepareStatement("SELECT * FROM CUSTOMERS"); // NOSONAR ResultSet resultSet = statement.executeQuery(); // NOSONAR return StreamSupport.stream(new Spliterators.AbstractSpliterator<Customer>(Long.MAX_VALUE, Spliterator.ORDERED) {
@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 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(); }
/** * {@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); } }
@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(); }
@Override public List<String> getIndexNames(Entity<?> en, Connection conn) throws SQLException { List<String> names = new ArrayList<String>(); String showIndexs = "SELECT * FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME = ?"; PreparedStatement preparedStatement = conn.prepareStatement(showIndexs); preparedStatement.setString(1, en.getTableName().toUpperCase()); ResultSet rest = preparedStatement.executeQuery(); while (rest.next()) { String index = rest.getString(5); names.add(index); } return names; }
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(); } }
@Test public void testQueryForMapWithParamMapAndSingleRowAndColumn() throws Exception { given(resultSet.getMetaData()).willReturn(resultSetMetaData); given(resultSet.next()).willReturn(true, false); given(resultSet.getObject(1)).willReturn(11); MapSqlParameterSource params = new MapSqlParameterSource(); params.addValue("id", 3); Map<String, Object> map = template.queryForMap("SELECT AGE FROM CUSTMR WHERE ID < :id", params); assertEquals("Row is Integer", 11, ((Integer) map.get("age")).intValue()); verify(connection).prepareStatement("SELECT AGE FROM CUSTMR WHERE ID < ?"); verify(preparedStatement).setObject(1, 3); }
public static void paramHandling(short j, short i, ResultSet[] resultSets) throws SQLException { Connection conn = DriverManager.getConnection( "jdbc:default:connection" ); PreparedStatement statement = conn.prepareStatement( "SELECT " + j + " as value, " + i + " as value2 from sysibm.sysdummy1" ); resultSets[0] = statement.executeQuery(); conn.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; }
@Test public void testUpdateWithTypedParameters() throws SQLException { given(preparedStatement.executeUpdate()).willReturn(1); params.put("perfId", new SqlParameterValue(Types.DECIMAL, 1)); params.put("priceId", new SqlParameterValue(Types.INTEGER, 1)); int rowsAffected = namedParameterTemplate.update(UPDATE_NAMED_PARAMETERS, params); assertEquals(1, rowsAffected); verify(connection).prepareStatement(UPDATE_NAMED_PARAMETERS_PARSED); verify(preparedStatement).setObject(1, 1, Types.DECIMAL); verify(preparedStatement).setObject(2, 1, Types.INTEGER); verify(preparedStatement).close(); verify(connection).close(); }