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();
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();
@Override public void execute(String sql) { try (Connection connection = DriverManager.getConnection(jdbcUrl, jdbcProperties); Statement statement = connection.createStatement()) { statement.execute(sql); } catch (SQLException e) { throw new RuntimeException("Error executing sql:\n" + sql, e); } } }
@Test public void testExecuteUpdateWithInsert() throws Exception { try (Connection connection = createConnection("blackhole", "blackhole")) { try (Statement statement = connection.createStatement()) { assertEquals(statement.executeUpdate("INSERT INTO test_table VALUES (1), (2)"), 2); assertNull(statement.getResultSet()); assertEquals(statement.getUpdateCount(), 2); assertEquals(statement.getLargeUpdateCount(), 2); } } }
@Test public void testResultSetClose() throws Exception { try (Connection connection = createConnection()) { try (Statement statement = connection.createStatement()) { assertTrue(statement.execute("SELECT 123 x, 'foo' y")); ResultSet result = statement.getResultSet(); assertFalse(result.isClosed()); result.close(); assertTrue(result.isClosed()); } } }
@Test public void testHanaSequenceMaxValueIncrementer() throws SQLException { given(dataSource.getConnection()).willReturn(connection); given(connection.createStatement()).willReturn(statement); given(statement.executeQuery("select myseq.nextval from dummy")).willReturn(resultSet); given(resultSet.next()).willReturn(true); given(resultSet.getLong(1)).willReturn(10L, 12L); HanaSequenceMaxValueIncrementer incrementer = new HanaSequenceMaxValueIncrementer(); 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)); }
@Before public void setUp() throws ClassNotFoundException, SQLException { sc = new JavaSparkContext("local", "JavaAPISuite"); Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); Connection connection = DriverManager.getConnection("jdbc:derby:target/JavaJdbcRDDSuiteDb;create=true"); try { Statement create = connection.createStatement(); create.execute( "CREATE TABLE FOO(" + "ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," + "DATA INTEGER)"); create.close(); PreparedStatement insert = connection.prepareStatement("INSERT INTO FOO(DATA) VALUES(?)"); for (int i = 1; i <= 100; i++) { insert.setInt(1, i * 2); insert.executeUpdate(); } insert.close(); } catch (SQLException e) { // If table doesn't exist... if (e.getSQLState().compareTo("X0Y32") != 0) { throw e; } } finally { connection.close(); } }
String sql_insert = "INSERT INTO mytable(content) VALUES (?)"; String sql_currval = "SELECT seq_mytable.CURRVAL FROM dual"; Connection connection = null; PreparedStatement statement = null; Statement currvalStatement = null; ResultSet currvalResultSet = null; try { connection = database.getConnection(); connection.setAutoCommit(false); statement = connection.prepareStatement(sql_insert); statement.setString(1, "test"); statement.executeUpdate(); currvalStatement = connection.createStatement(); currvalResultSet = currvalStatement.executeQuery(sql_currval); if (currvalResultSet.next()) { int id = currvalResultSet.getInt(1); } connection.commit(); // ...
public static void putFolderToDb(final File pDestinationFile, final File pFolderToPut) throws ClassNotFoundException, SQLException, FileNotFoundException, IOException { pDestinationFile.delete(); Class.forName("org.sqlite.JDBC"); final Connection conn = DriverManager.getConnection("jdbc:sqlite:" + pDestinationFile); final Statement stat = conn.createStatement(); stat.execute("CREATE TABLE tiles (key INTEGER PRIMARY KEY, provider TEXT, tile BLOB)"); stat.close(); final PreparedStatement prep = conn.prepareStatement("insert into tiles values (?, ?, ?);"); File[] listFiles = pFolderToPut.listFiles(); if (listFiles != null) { final long y = Long.parseLong(s[s.length - 1].split(".png")[0]); final long index = (((z << z) + x) << z) + y; prep.setLong(1, index); final String provider = s[s.length - 4]; prep.setString(2, provider); final byte[] image = new byte[(int) listFiles2[yf].length()]; final FileInputStream str = new FileInputStream(listFiles2[yf]); str.read(image); str.close(); prep.setBytes(3, image); prep.executeUpdate(); conn.setAutoCommit(false); prep.executeBatch(); conn.setAutoCommit(true);
/** * @throws Exception If failed. */ @Test public void testSchemaInUrl() throws Exception { try(Connection conn = DriverManager.getConnection(URL + "/\"cache1\"")) { Statement stmt = conn.createStatement(); stmt.execute("select t._key, t._val from Integer t"); ResultSet rs = stmt.getResultSet(); while (rs.next()) assertEquals(rs.getInt(2), rs.getInt(1) * 2); } try(Connection conn = DriverManager.getConnection(URL + "/\"cache2\"")) { Statement stmt = conn.createStatement(); stmt.execute("select t._key, t._val from Integer t"); ResultSet rs = stmt.getResultSet(); while (rs.next()) assertEquals(rs.getInt(2), rs.getInt(1) * 3); } }
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()); } } } }
@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(); }
@Test public void testJDBC() { doInHibernate( this::sessionFactory, session -> { session.doWork( connection -> { Time time = Time.valueOf( LocalTime.of( 12, 0, 0 ) ); try (PreparedStatement ps = connection.prepareStatement( "INSERT INTO Person (id, shiftStartTime) VALUES (?, ?)" )) { ps.setLong( 1, 1L ); ps.setTime( 2, time, new GregorianCalendar( TimeZone.getTimeZone( "UTC" ) ) ); ps.executeUpdate(); } try (Statement st = connection.createStatement()) { try (ResultSet rs = st.executeQuery( "SELECT shiftStartTime FROM Person WHERE id = 1" )) { while ( rs.next() ) { Time dbTime = rs.getTime( 1, new GregorianCalendar( TimeZone.getTimeZone( "UTC" ) ) ); assertEquals( time, dbTime ); } } } } ); } ); }
@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 Table loadTableFromDB() throws SQLException { Connection connection = DriverManager.getConnection("jdbc:h2:mem:myDb;DB_CLOSE_DELAY=-1"); String create = "CREATE TABLE country_sales (" + "countries VARCHAR(255)," + "sale_timestamp TIMESTAMP," + "price INTEGER" + ");"; Statement statement = connection.createStatement(); statement.executeUpdate(create); PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO country_sales (countries, sale_timestamp, price) VALUES (?, ?, ?);"); for (int i = 0; i < categories.length; i++) { preparedStatement.setString(1, categories[i]); preparedStatement.setObject(2, timestamps[i]); preparedStatement.setInt(3, observations[i]); preparedStatement.executeUpdate(); } ResultSet resultSet = connection.createStatement().executeQuery("SELECT * FROM country_sales"); return Table.read().db(resultSet, "sales_from_db"); }
private void testAvaticaQuery(String url) { LOG.info("URL: " + url); try { Properties connectionProperties = new Properties(); connectionProperties.put("user", "admin"); connectionProperties.put("password", "priest"); Connection connection = DriverManager.getConnection(url, connectionProperties); Statement statement = connection.createStatement(); statement.setMaxRows(450); String query = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS"; ResultSet resultSet = statement.executeQuery(query); Assert.assertTrue(resultSet.next()); statement.close(); connection.close(); } catch (Exception e) { throw new RuntimeException(e); } }
/** * Make sure that JDBC connection is possible at the given host and port. * * @param host Host. * @param port Port. * @throws Exception If failed. */ private void checkJdbc(@Nullable String host, int port) throws Exception { if (host == null) host = "127.0.0.1"; String connStr = "jdbc:ignite:thin://" + host + ":" + port; try (Connection conn = DriverManager.getConnection(connStr)) { conn.setSchema(CACHE_NAME); try (Statement stmt = conn.createStatement()) { ResultSet rs = stmt.executeQuery("SELECT 1"); assertTrue(rs.next()); Assert.assertEquals(1, rs.getInt(1)); } } }
@Test public void testSqlUpdateWithThreadConnection() throws Exception { final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 4"; int rowsAffected = 33; given(this.statement.executeUpdate(sql)).willReturn(rowsAffected); given(this.connection.createStatement()).willReturn(this.statement); int actualRowsAffected = this.template.update(sql); assertTrue("Actual rows affected is correct", actualRowsAffected == rowsAffected); verify(this.statement).close(); verify(this.connection).close(); }
private boolean isReadOnly(final Connection conn) throws SQLException { final Statement stmt = conn.createStatement(); final ResultSet rs = stmt.executeQuery("SELECT @@global.read_only"); if (rs.next()) { final int value = rs.getInt(1); return value != 0; } throw new SQLException("can not fetch read only value from DB"); }