Reading from database using SQL prepared statement

/**
 * {@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();
  }
 }
}

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;
}

ProjectMeasuresIndexerIterator.selectMeasures(...)

private Measures selectMeasures(String projectUuid) {
 Measures measures = new Measures();
 ResultSet rs = null;
 try {
  AtomicInteger index = new AtomicInteger(1);
  measuresStatement.setString(index.getAndIncrement(), projectUuid);
  METRIC_KEYS.forEach(DatabaseUtils.setStrings(measuresStatement, index::getAndIncrement));
  measuresStatement.setBoolean(index.getAndIncrement(), ENABLED);
  rs = measuresStatement.executeQuery();
  while (rs.next()) {
   readMeasure(rs, measures);
  }
  return measures;
 } catch (Exception e) {
  throw new IllegalStateException(String.format("Fail to execute request to select measures of project %s", projectUuid), e);
 } finally {
  DatabaseUtils.closeQuietly(rs);
 }
}

ProjectMeasuresIndexerIterator.selectProjects(...)

private static List<Project> selectProjects(DbSession session, @Nullable String projectUuid) {
 List<Project> projects = new ArrayList<>();
 try (PreparedStatement stmt = createProjectsStatement(session, projectUuid);
  ResultSet rs = stmt.executeQuery()) {
  while (rs.next()) {
   String orgUuid = rs.getString(1);
   String uuid = rs.getString(2);
   String key = rs.getString(3);
   String name = rs.getString(4);
   Long analysisDate = DatabaseUtils.getLong(rs, 5);
   List<String> tags = readDbTags(DatabaseUtils.getString(rs, 6));
   Project project = new Project(orgUuid, uuid, key, name, tags, analysisDate);
   projects.add(project);
  }
  return projects;
 } catch (SQLException e) {
  throw new IllegalStateException("Fail to execute request to select all projects", e);
 }
}

public static List<String> getTableDDL(Connection conn, List<String> tables) throws SQLException {
  List<String> ddlList = new ArrayList<String>();
    String sql = "select DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME) FROM user_tables";
    if (tables.size() > 0) {
      sql += "IN (";
      for (int i = 0; i < tables.size(); ++i) {
        if (i != 0) {
          sql += ", ?";
    pstmt = conn.prepareStatement(sql);
    for (int i = 0; i < tables.size(); ++i) {
      pstmt.setString(i + 1, tables.get(i));
    rs = pstmt.executeQuery();
    while (rs.next()) {
      String ddl = rs.getString(1);
      ddlList.add(ddl);
    JdbcUtils.close(rs);
    JdbcUtils.close(pstmt);

JdbcUtil.selectByParams(...)

public List<Map> selectByParams(String sql, List params) throws SQLException {
  List<Map> list = new ArrayList<> ();
  int index = 1;
  pstmt = conn.prepareStatement(sql);
  if (null != params && !params.isEmpty()) {
    for (int i = 0; i < params.size(); i ++) {
      pstmt.setObject(index++, params.get(i));
    }
  }
  rs = pstmt.executeQuery();
  ResultSetMetaData metaData = rs.getMetaData();
  int colsLen = metaData.getColumnCount();
  while (rs.next()) {
    Map map = new HashMap(colsLen);
    for (int i = 0; i < colsLen; i ++) {
      String columnName = metaData.getColumnName(i + 1);
      Object columnValue = rs.getObject(columnName);
      if (null == columnValue) {
        columnValue = "";
      }
      map.put(columnName, columnValue);
    }
    list.add(map);
  }
  return list;
}

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;
}

SqlExecutor.select(...)

public <T> List<T> select(Connection connection, String sql, RowConverter<T> rowConverter) throws SQLException {
 PreparedStatement stmt = null;
 ResultSet rs = null;
 try {
  stmt = connection.prepareStatement(sql);
  rs = stmt.executeQuery();
  List<T> result = new ArrayList<>();
  while (rs.next()) {
   result.add(rowConverter.convert(rs));
  }
  return result;
 } finally {
  DatabaseUtils.closeQuietly(rs);
  DatabaseUtils.closeQuietly(stmt);
 }
}

CeScannerContextDao.selectScannerContext(...)

/**
 * The scanner context is very likely to contain lines, which are forcefully separated by {@code \n} characters,
 * whichever the platform SQ is running on ({@see LogsIteratorInputStream}).
 */
public Optional<String> selectScannerContext(DbSession dbSession, String taskUuid) {
 try (PreparedStatement stmt = dbSession.getConnection().prepareStatement("select context_data from ce_scanner_context where task_uuid=?")) {
  stmt.setString(1, taskUuid);
  try (ResultSet rs = stmt.executeQuery()) {
   if (rs.next()) {
    return Optional.of(IOUtils.toString(rs.getBinaryStream(1), UTF_8));
   }
   return Optional.empty();
  }
 } catch (SQLException | IOException e) {
  throw new IllegalStateException("Fail to retrieve scanner context of task " + taskUuid, e);
 }
}

@Test
public void testPrepareMultiple()
    throws Exception
{
  try (Connection connection = createConnection();
      PreparedStatement statement1 = connection.prepareStatement("SELECT 123");
      PreparedStatement statement2 = connection.prepareStatement("SELECT 456")) {
    try (ResultSet rs = statement1.executeQuery()) {
      assertTrue(rs.next());
      assertEquals(rs.getLong(1), 123);
      assertFalse(rs.next());
    }
    try (ResultSet rs = statement2.executeQuery()) {
      assertTrue(rs.next());
      assertEquals(rs.getLong(1), 456);
      assertFalse(rs.next());
    }
  }
}

public List<MonitorApp> listApp(String domain) throws SQLException {
  List<MonitorApp> list = new ArrayList<MonitorApp>();
  String sql = "select id, domain, app from druid_app " //
         + " where domain = ?";
  Connection conn = null;
  PreparedStatement stmt = null;
  ResultSet rs = null;
  try {
    conn = dataSource.getConnection();
    stmt = conn.prepareStatement(sql);
    stmt.setString(1, domain);
    rs = stmt.executeQuery();
    if (rs.next()) {
      list.add(readApp(rs));
    }
    return list;
  } finally {
    JdbcUtils.close(rs);
    JdbcUtils.close(stmt);
    JdbcUtils.close(conn);
  }
}

private void assertSetNull(int sqlType, int expectedSqlType)
    throws SQLException
{
  try (Connection connection = createConnection();
      PreparedStatement statement = connection.prepareStatement("SELECT ?")) {
    statement.setNull(1, sqlType);
    try (ResultSet rs = statement.executeQuery()) {
      assertTrue(rs.next());
      assertNull(rs.getObject(1));
      assertTrue(rs.wasNull());
      assertFalse(rs.next());
      assertEquals(rs.getMetaData().getColumnType(1), expectedSqlType);
    }
  }
}

protected String getConstValueFromDb(String domain, String app, String type, Long hash) {
  String sql = "select value from druid_const where domain = ? AND app = ? and type = ? and hash = ?";
  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, type);
    stmt.setLong(4, hash);
    rs = stmt.executeQuery();
    if (rs.next()) {
      return rs.getString(1);
    }
  } catch (SQLException ex) {
    LOG.error("save const error error", ex);
  } finally {
    JdbcUtils.close(rs);
    JdbcUtils.close(stmt);
    JdbcUtils.close(conn);
  }
  return null;
}

private void assertParameter(Object expectedValue, int expectedSqlType, Binder binder)
    throws SQLException
{
  try (Connection connection = createConnection();
      PreparedStatement statement = connection.prepareStatement("SELECT ?")) {
    binder.bind(statement, 1);
    try (ResultSet rs = statement.executeQuery()) {
      assertTrue(rs.next());
      assertEquals(expectedValue, rs.getObject(1));
      assertFalse(rs.next());
      assertEquals(rs.getMetaData().getColumnType(1), expectedSqlType);
    }
  }
}

public MonitorCluster findCluster(String domain, String app, String cluster) throws SQLException {
  String sql = "select id, domain, app, cluster from druid_cluster " //
         + " where domain = ? and app = ? and cluster = ?";
  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);
    rs = stmt.executeQuery();
    if (rs.next()) {
      return readCluster(rs);
    }
    return null;
  } finally {
    JdbcUtils.close(rs);
    JdbcUtils.close(stmt);
    JdbcUtils.close(conn);
  }
}

public MonitorApp findApp(String domain, String app) throws SQLException {
  String sql = "select id, domain, app from druid_app " //
         + " where domain = ? and app = ?";
  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);
    rs = stmt.executeQuery();
    if (rs.next()) {
      return readApp(rs);
    }
    return null;
  } finally {
    JdbcUtils.close(rs);
    JdbcUtils.close(stmt);
    JdbcUtils.close(conn);
  }
}

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);
  }
}

public List<MonitorCluster> listCluster(String domain, String app) throws SQLException {
  List<MonitorCluster> list = new ArrayList<MonitorCluster>();
  String sql = "select id, domain, app, cluster from druid_cluster " //
         + " where domain = ?";
  if (app != null) {
    sql += " and app = ?";
  }
  Connection conn = null;
  PreparedStatement stmt = null;
  ResultSet rs = null;
  try {
    conn = dataSource.getConnection();
    stmt = conn.prepareStatement(sql);
    stmt.setString(1, domain);
    if (app != null) {
      stmt.setString(2, app);
    }
    rs = stmt.executeQuery();
    if (rs.next()) {
      list.add(readCluster(rs));
    }
    return list;
  } finally {
    JdbcUtils.close(rs);
    JdbcUtils.close(stmt);
    JdbcUtils.close(conn);
  }
}

AbstractJdbcTestCase$AbstractSelectSection.runSelect(...)

  protected static void runSelect(Connection connection, @Parameter SelectParameter parameter, NewThread tag)
      throws SQLException {
    try (PreparedStatement statement = connection.prepareStatement("SELECT * FROM OFFICE_FLOOR_JDBC_TEST")) {
      ResultSet resultSet = statement.executeQuery();
      assertTrue("Should have row from database", resultSet.next());
      parameter.name = resultSet.getString("NAME");
    }
  }
}

CloneProjectDBDao.selectClonedProjectByToken(...)

public String selectClonedProjectByToken(String token) throws Exception {
  try (Connection connection = ds.getConnection();
     PreparedStatement statement = connection.prepareStatement(selectClonedProjectByToken)) {
    statement.setString(1, token);
    try (ResultSet rs = statement.executeQuery()) {
      connection.commit();
      if (rs.next()) {
        return rs.getString("json");
      }
    }
  }
  return null;
}

Javadoc

Executes the SQL query in the PreparedStatement and returns the ResultSet generated by the query.

Popular methods of PreparedStatement

setString
Sets the value of a specified parameter to a supplied string.
executeUpdate
close
setInt
Sets the value of a specified parameter to a supplied int value.
setObject
execute
setLong
setNull
Sets the value of a specified parameter to SQL NULL. This version of setNull should be used for User
setTimestamp
Sets the value of a specified parameter to a supplied java.sql.Timestamp value, using the supplied C
addBatch
executeBatch
setBytes

Popular in Java

Reading from database using SQL prepared statement
getSupportFragmentManager (FragmentActivity)
onRequestPermissionsResult (Fragment)
setContentView (Activity)
HttpServer (com.sun.net.httpserver)
This class implements a simple HTTP server. A HttpServer is bound to an IP address and port number a
SQLException (java.sql)
An exception that indicates a failed JDBC operation. It provides the following information about pro
Random (java.util)
This class provides methods that return pseudo-random values.It is dangerous to seed Random with the
ExecutorService (java.util.concurrent)
An Executor that provides methods to manage termination and methods that can produce a Future for tr
JOptionPane (javax.swing)
JTextField (javax.swing)
CodeWhisperer alternatives

How to use executeQuerymethodin java.sql.PreparedStatement

Reading from database using SQL prepared statement

Refine search

How to use
executeQuery
method
in
java.sql.PreparedStatement