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 ResultSet getLobStream(Connection conn, String column, int id) throws SQLException { PreparedStatement prep = conn.prepareStatement("SELECT " + column + " FROM SYSTEM_LOB_STREAM WHERE ID=? ORDER BY PART"); prep.setInt(1, id); return prep.executeQuery(); }
final PreparedStatement statement = connection.prepareStatement( "SELECT my_column FROM my_table where search_column = ANY (?)" ); final String[] values = getValues(); statement.setArray(1, connection.createArrayOf("text", values)); final ResultSet rs = statement.executeQuery(); try { while(rs.next()) { // do some... } } finally { rs.close(); }
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 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(); } } }
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(); }
private Map<FileHistoryId, FileVersion> getSingleVersionInHistory(PreparedStatement preparedStatement) throws SQLException { try (ResultSet resultSet = preparedStatement.executeQuery()) { Map<FileHistoryId, FileVersion> mostRecentPurgeFileVersions = new HashMap<FileHistoryId, FileVersion>(); while (resultSet.next()) { FileHistoryId fileHistoryId = FileHistoryId.parseFileId(resultSet.getString("filehistory_id")); FileVersion fileVersion = createFileVersionFromRow(resultSet); mostRecentPurgeFileVersions.put(fileHistoryId, fileVersion); } return mostRecentPurgeFileVersions; } }
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; }
/** * 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; }
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; }
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(); }
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); } }
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) {
protected VectorClock getVectorClockByDatabaseVersionId(int databaseVersionId) throws SQLException { PreparedStatement preparedStatement = getStatement("databaseversion.select.all.getVectorClockByDatabaseVersionId.sql"); preparedStatement.setInt(1, databaseVersionId); ResultSet resultSet = preparedStatement.executeQuery(); VectorClock vectorClock = new VectorClock(); while (resultSet.next()) { vectorClock.setClock(resultSet.getString("client"), resultSet.getLong("logicaltime")); } resultSet.close(); preparedStatement.close(); return vectorClock; }
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; }
@Override public void run(Connection conn) throws Exception { try (PreparedStatement stmt = conn.prepareStatement("SELECT 'zzz'")) { ResultSet rs = stmt.executeQuery(); rs.next(); rs.getBoolean(1); } } }, "0700B", "Cannot convert to boolean");
public static void simpleScalar(short i, ResultSet[] resultSets) throws SQLException { Connection conn = DriverManager.getConnection( "jdbc:default:connection" ); PreparedStatement statement = conn.prepareStatement( "SELECT " + i + " as value, 'getAll' as name from sysibm.sysdummy1" ); resultSets[0] = statement.executeQuery(); conn.close(); } }
private byte[] fetch() { try { prep.setInt(2, seq++); ResultSet rs = prep.executeQuery(); if (rs.next()) { return rs.getBytes(1); } return null; } catch (SQLException e) { throw DbException.convert(e); } }
@Test public void testSelectCountAlternateStyle() throws Exception { final ResultSet resultSet = client.prepareStatement("SELECT COUNT(*) AS cnt FROM druid.foo").executeQuery(); final List<Map<String, Object>> rows = getRows(resultSet); Assert.assertEquals( ImmutableList.of( ImmutableMap.of("cnt", 6L) ), rows ); }
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; } }