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 } }
public static void main(String[] args) throws Exception { 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(false); prep.executeBatch(); 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")); rs.close(); conn.close();
public List<User> getUser(int userId) { String sql = "SELECT id, username FROM users WHERE id = ?"; List<User> users = new ArrayList<>(); try (Connection con = DriverManager.getConnection(myConnectionURL); PreparedStatement ps = con.prepareStatement(sql);) { ps.setInt(1, userId); try (ResultSet rs = ps.executeQuery();) { while(rs.next()) { users.add(new User(rs.getInt("id"), rs.getString("name"))); } } } catch (SQLException e) { e.printStackTrace(); } return users; }
Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USERNAME, JDBC_PASSWORD); PreparedStatement statement = connection.prepareStatement(JDBC_SELECT); ResultSet rs = statement.executeQuery(); PrintStream out = System.out; if (rs != null) { while (rs.next()) { ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 1; i <= rsmd.getColumnCount(); i++) { if (i > 1) { out.print(","); } int type = rsmd.getColumnType(i); if (type == Types.VARCHAR || type == Types.CHAR) { out.print(rs.getString(i)); } else { out.print(rs.getLong(i)); } } out.println(); } }
@Override @Nullable public String getClobAsString(ResultSet rs, int columnIndex) throws SQLException { logger.debug("Returning CLOB as string"); if (this.wrapAsLob) { Clob clob = rs.getClob(columnIndex); return clob.getSubString(1, (int) clob.length()); } else { return rs.getString(columnIndex); } }
@Override public Object getCalendarValue(final String columnLabel, final Class<?> type, final Calendar calendar) throws SQLException { if (Date.class == type) { return resultSet.getDate(columnLabel, calendar); } if (Time.class == type) { return resultSet.getTime(columnLabel, calendar); } if (Timestamp.class == type) { return resultSet.getTimestamp(columnLabel, calendar); } throw new SQLException(String.format("Unsupported type: %s", type)); }
@Test public void testCloseConnectionOnRequest() throws Exception { String sql = "SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3"; given(this.resultSet.next()).willReturn(false); given(this.connection.createStatement()).willReturn(this.preparedStatement); RowCountCallbackHandler rcch = new RowCountCallbackHandler(); this.template.query(sql, rcch); verify(this.resultSet).close(); verify(this.preparedStatement).close(); verify(this.connection).close(); }
@Test public void testOracleSequenceMaxValueIncrementer() throws SQLException { given(dataSource.getConnection()).willReturn(connection); given(connection.createStatement()).willReturn(statement); given(statement.executeQuery("select myseq.nextval from dual")).willReturn(resultSet); given(resultSet.next()).willReturn(true); given(resultSet.getLong(1)).willReturn(10L, 12L); OracleSequenceMaxValueIncrementer incrementer = new OracleSequenceMaxValueIncrementer(); incrementer.setDataSource(dataSource); incrementer.setIncrementerName("myseq"); incrementer.setPaddingLength(2); incrementer.afterPropertiesSet(); assertEquals(10, incrementer.nextLongValue()); assertEquals("12", incrementer.nextStringValue()); verify(resultSet, times(2)).close(); verify(statement, times(2)).close(); verify(connection, times(2)).close(); }
// 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)); }
@ExpectWarning("ODR_OPEN_DATABASE_RESOURCE") public void isReported(String url, String username, String password) throws Exception { Connection connection = DriverManager.getConnection(url, username, password); PreparedStatement pstmt = connection.prepareStatement("SELECT count(1) from tab"); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { System.out.println(rs.getString(1)); } }
@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); }
/** * @throws Exception If failed. */ @Test public void testSchemaInUrlAndInQuery() throws Exception { try(Connection conn = DriverManager.getConnection(URL + "/\"cache2\"")) { Statement stmt = conn.createStatement(); stmt.execute("select t._key, t._val, v._val " + "from \"cache1\".Integer t join Integer v on t._key = v._key"); ResultSet rs = stmt.getResultSet(); while (rs.next()) { assertEquals(rs.getInt(2), rs.getInt(1) * 2); assertEquals(rs.getInt(3), rs.getInt(1) * 3); } } }
@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(); }
DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); final Connection c = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "manager"); String plsql = "" + " declare " + " end;"; CallableStatement cs = c.prepareCall(plsql); cs.setString(1, "12345"); cs.registerOutParameter(2, Types.VARCHAR); cs.registerOutParameter(3, OracleTypes.CURSOR); cs.execute(); while (cursorResultSet.next ()) System.out.println (cursorResultSet.getInt(1) + " " + cursorResultSet.getString(2)); cs.close(); c.close();
@Test public void testQueryForObjectWithInteger() throws Exception { String sql = "SELECT AGE FROM CUSTMR WHERE ID = 3"; given(this.resultSet.next()).willReturn(true, false); given(this.resultSet.getInt(1)).willReturn(22); assertEquals(Integer.valueOf(22), this.template.queryForObject(sql, Integer.class)); verify(this.resultSet).close(); verify(this.statement).close(); }
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()); } } } }
@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(); }
try (Connection con = DriverManager.getConnection(mHiveURL, mHiveUserName, mHiveUserPassword)) { try (PreparedStatement dropTablePS = con.prepareStatement(sql)) { dropTablePS.execute(); try (PreparedStatement loadTablePS = con.prepareStatement(sql)) { loadTablePS.executeUpdate(); try (PreparedStatement describeTablePS = con.prepareStatement(sql)) { describeTablePS.execute(); reportWriter.println("Result should be \"You passed Hive test!\" "); reportWriter.println("Checker result is: "); while (resultSet.next()) { reportWriter.println(resultSet.getString(1) + resultSet.getString(2));
String str = null; try { conn = DriverManager.getConnection("", "", ""); stmt = conn.prepareStatement(str); stmt.setString(1, ""); rs = stmt.executeQuery(); se.printStackTrace(); } finally { try { if (rs != null) { rs.close(); rs = null; se.printStackTrace(); stmt.close(); stmt = null; se.printStackTrace(); conn.close(); conn = null;