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