private static String getSessionProperty(Connection connection, String key, String valueType) throws SQLException { try (Statement statement = connection.createStatement()) { ResultSet rs = statement.executeQuery("SHOW SESSION"); while (rs.next()) { if (rs.getString("Name").equals(key)) { return rs.getString(valueType); } } } return null; }
Statement stmt = conn.createStatement(); try { ResultSet rs = stmt.executeQuery( "SELECT FULL_NAME FROM EMP" ); try { while ( rs.next() ) { System.out.println( "Name: " + rs.getString("FULL_NAME") ); } } finally { try { rs.close(); } catch (Exception ignore) { } } } finally { try { stmt.close(); } catch (Exception ignore) { } }
List<JobStatusTraceEvent> getJobStatusTraceEvents(final String taskId) { String sql = String.format("SELECT * FROM %s WHERE task_id = '%s'", TABLE_JOB_STATUS_TRACE_LOG, taskId); List<JobStatusTraceEvent> result = new ArrayList<>(); try ( Connection conn = dataSource.getConnection(); PreparedStatement preparedStatement = conn.prepareStatement(sql); ResultSet resultSet = preparedStatement.executeQuery() ) { while (resultSet.next()) { JobStatusTraceEvent jobStatusTraceEvent = new JobStatusTraceEvent(resultSet.getString(1), resultSet.getString(2), resultSet.getString(3), resultSet.getString(4), resultSet.getString(5), Source.valueOf(resultSet.getString(6)), ExecutionType.valueOf(resultSet.getString(7)), resultSet.getString(8), State.valueOf(resultSet.getString(9)), resultSet.getString(10), new SimpleDateFormat("yyyy-mm-dd HH:MM:SS").parse(resultSet.getString(11))); result.add(jobStatusTraceEvent); } } catch (final SQLException | ParseException ex) { // TODO 记录失败直接输出日志,未来可考虑配置化 log.error(ex.getMessage()); } return result; } }
Class.forName("org.sqlite.JDBC"); Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db"); Statement stat = conn.createStatement(); 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(); conn.setAutoCommit(true); ResultSet rs = stat.executeQuery("select * from people;"); while (rs.next()) { System.out.println("name = " + rs.getString("name")); System.out.println("job = " + rs.getString("occupation"));
@Before public void setUp() throws SQLException { given(connection.createStatement()).willReturn(statement); given(connection.prepareStatement(anyString())).willReturn(preparedStatement); given(statement.executeQuery(anyString())).willReturn(resultSet); given(preparedStatement.executeQuery()).willReturn(resultSet); given(resultSet.next()).willReturn(true, true, false); given(resultSet.getString(1)).willReturn("tb1", "tb2"); given(resultSet.getInt(2)).willReturn(1, 2); template.setDataSource(new SingleConnectionDataSource(connection, false)); template.setExceptionTranslator(new SQLStateSQLExceptionTranslator()); template.afterPropertiesSet(); }
private List<Column> getColumnsForTable(Connection connection, String catalog, String schema, String table) throws SQLException { ResultSet columns = connection.getMetaData().getColumns(catalog, escapeLikeExpression(connection, schema), escapeLikeExpression(connection, table), null); ImmutableList.Builder<Column> columnBuilder = new ImmutableList.Builder<>(); while (columns.next()) { String name = columns.getString("COLUMN_NAME"); int type = columns.getInt("DATA_TYPE"); columnBuilder.add(new Column(name, APPROXIMATE_TYPES.contains(type))); } return columnBuilder.build(); }
@Test public void testStringQueryWithResults() throws Exception { String[] dbResults = new String[] { "alpha", "beta", "charlie" }; given(resultSet.next()).willReturn(true, true, true, false); given(resultSet.getString(1)).willReturn(dbResults[0], dbResults[1], dbResults[2]); StringQuery query = new StringQuery(dataSource, SELECT_FORENAME); query.setRowsExpected(3); String[] results = query.run(); assertThat(results, is(equalTo(dbResults))); verify(connection).prepareStatement(SELECT_FORENAME); verify(resultSet).close(); verify(preparedStatement).close(); verify(connection).close(); }
private void performTestForCustomIniFile(final String jdbcUrl) throws SQLException { assumeFalse(SystemUtils.IS_OS_WINDOWS); try (HikariDataSource ds = getDataSource(jdbcUrl, 1)) { Statement statement = ds.getConnection().createStatement(); statement.execute("SELECT @@GLOBAL.innodb_file_format"); ResultSet resultSet = statement.getResultSet(); assertTrue("The query returns a result", resultSet.next()); String result = resultSet.getString(1); assertEquals("The InnoDB file format has been set by the ini file content", "Barracuda", result); } }
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; } }
@Test public void testQueryForObjectWithString() throws Exception { String sql = "SELECT AGE FROM CUSTMR WHERE ID = 3"; given(this.resultSet.next()).willReturn(true, false); given(this.resultSet.getString(1)).willReturn("myvalue"); assertEquals("myvalue", this.template.queryForObject(sql, String.class)); verify(this.resultSet).close(); verify(this.statement).close(); }
/** * 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; }
/** * Get the name of the current schema. * This method is called by the database. * * @param conn the connection * @return the schema name */ public static String getCurrentSchema(Connection conn) throws SQLException { ResultSet rs = conn.createStatement().executeQuery("call schema()"); rs.next(); return rs.getString(1); }
private static void assertConnectionSource(Connection connection, String expectedSource) throws SQLException { String queryId; try (Statement statement = connection.createStatement(); ResultSet rs = statement.executeQuery("SELECT 123")) { queryId = rs.unwrap(PrestoResultSet.class).getQueryId(); } try (PreparedStatement statement = connection.prepareStatement( "SELECT source FROM system.runtime.queries WHERE query_id = ?")) { statement.setString(1, queryId); try (ResultSet rs = statement.executeQuery()) { assertTrue(rs.next()); assertThat(rs.getString("source")).isEqualTo(expectedSource); assertFalse(rs.next()); } } } }
tableColumns = databaseMetaData.getColumns( metaDataCatalogName, metaDataSchemaName, metaDataTableName, null); while (tableColumns.next()) { String columnName = tableColumns.getString("COLUMN_NAME"); int dataType = tableColumns.getInt("DATA_TYPE"); if (dataType == Types.DECIMAL) { String typeName = tableColumns.getString("TYPE_NAME"); int decimalDigits = tableColumns.getInt("DECIMAL_DIGITS");
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; }
public String getSchema() throws SQLException { if (isClosed) { throw new SQLException("Connection is closed"); } try (Statement stmt = createStatement(); ResultSet res = stmt.executeQuery("SELECT current_database()")) { if (!res.next()) { throw new SQLException("Failed to get schema information"); } return res.getString(1); } }
@Test public void testQueryForObjectThrowsIncorrectResultSizeForMoreThanOneRow() throws Exception { String sql = "select pass from t_account where first_name='Alef'"; given(this.resultSet.next()).willReturn(true, true, false); given(this.resultSet.getString(1)).willReturn("pass"); this.thrown.expect(IncorrectResultSizeDataAccessException.class); try { this.template.queryForObject(sql, String.class); } finally { verify(this.resultSet).close(); verify(this.statement).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; }
// assumes... // import java.sql.*; Connection conn=DriverManager.getConnection( "jdbc:ucanaccess://C:/__tmp/test/zzz.accdb"); Statement s = conn.createStatement(); ResultSet rs = s.executeQuery("SELECT [LastName] FROM [Clients]"); while (rs.next()) { System.out.println(rs.getString(1)); }
given(metaDataResultSet.next()).willReturn(true, false); given(metaDataResultSet.getString("TABLE_SCHEM")).willReturn(USER); given(metaDataResultSet.getString("TABLE_NAME")).willReturn(TABLE); given(metaDataResultSet.getString("TABLE_TYPE")).willReturn("TABLE"); given(columnsResultSet.next()).willReturn(true, false); given(columnsResultSet.getString("COLUMN_NAME")).willReturn("id"); given(columnsResultSet.getInt("DATA_TYPE")).willReturn(Types.INTEGER); given(columnsResultSet.getBoolean("NULLABLE")).willReturn(false); verify(metaDataResultSet, atLeastOnce()).next(); verify(columnsResultSet, atLeastOnce()).next(); verify(metaDataResultSet).close();