@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); } } }
private static void execute(Connection connection, String sql) throws SQLException { try (Statement stmt = connection.createStatement()) { stmt.execute(sql); connection.commit(); } }
@Override public void shutdown(DataSource dataSource, String databaseName) { Connection con = null; try { con = dataSource.getConnection(); if (con != null) { con.createStatement().execute("SHUTDOWN"); } } catch (SQLException ex) { logger.info("Could not shut down embedded database", ex); } finally { if (con != null) { try { con.close(); } catch (Throwable ex) { logger.debug("Could not close JDBC Connection on shutdown", ex); } } } }
@SuppressFBWarnings("DMI_EMPTY_DB_PASSWORD") public void init() throws Exception { Class.forName("org.h2.Driver"); connection = DriverManager.getConnection("jdbc:h2:~/ehcache-demo-peeper", "sa", ""); try (Statement statement = connection.createStatement()) { statement.execute("CREATE TABLE IF NOT EXISTS PEEPS (" + "id bigint auto_increment primary key," + "PEEP_TEXT VARCHAR(142) NOT NULL" + ")"); connection.commit(); } }
@Test public void testStrConcat() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); String tableName = generateUniqueName(); conn.createStatement().execute("create table " + tableName + " (PK1 integer, F1 varchar, F2 varchar, F3 varchar, F4 varchar, constraint PK primary key (PK1))"); conn.createStatement().execute("upsert into " + tableName + "(PK1, F1,F3) values(0, 'tortilla', 'chip')"); conn.commit(); ResultSet rs = conn.createStatement().executeQuery("select * from " + tableName + " where (F1||F2||F3||F4)='tortillachip'"); assertTrue(rs.next()); assertEquals(0, rs.getInt(1)); assertFalse(rs.next()); }
@Test public void testBatchUpdateWithNoBatchSupport() throws Exception { final String[] sql = {"UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 1", "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 2"}; given(this.statement.execute(sql[0])).willReturn(false); given(this.statement.getUpdateCount()).willReturn(1, 1); given(this.statement.execute(sql[1])).willReturn(false); mockDatabaseMetaData(false); given(this.connection.createStatement()).willReturn(this.statement); JdbcTemplate template = new JdbcTemplate(this.dataSource, false); int[] actualRowsAffected = template.batchUpdate(sql); assertTrue("executed 2 updates", actualRowsAffected.length == 2); verify(this.statement, never()).addBatch(anyString()); verify(this.statement).close(); verify(this.connection, atLeastOnce()).close(); }
@Test void waits_until_postgres_accepts_jdbc_connections() throws Exception { HikariConfig hikariConfig = new HikariConfig(); hikariConfig.setJdbcUrl(POSTGRE_SQL_CONTAINER.getJdbcUrl()); hikariConfig.setUsername("foo"); hikariConfig.setPassword("secret"); try (HikariDataSource ds = new HikariDataSource(hikariConfig)) { Statement statement = ds.getConnection().createStatement(); statement.execute("SELECT 1"); ResultSet resultSet = statement.getResultSet(); resultSet.next(); int resultSetInt = resultSet.getInt(1); assertEquals(1, resultSetInt); } }
@Test public void testExecuteWithQuery() throws Exception { try (Connection connection = createConnection()) { try (Statement statement = connection.createStatement()) { assertTrue(statement.execute("SELECT 123 x, 'foo' y, CAST(NULL AS bigint) z")); ResultSet rs = statement.getResultSet(); assertEquals(statement.getUpdateCount(), -1); assertEquals(statement.getLargeUpdateCount(), -1); assertTrue(rs.next()); assertEquals(rs.getLong(1), 123); assertFalse(rs.wasNull()); assertEquals(rs.getLong("x"), 123); assertFalse(rs.wasNull()); assertEquals(rs.getLong(3), 0); assertTrue(rs.wasNull()); assertEquals(rs.getLong("z"), 0); assertTrue(rs.wasNull()); assertNull(rs.getObject("z")); assertTrue(rs.wasNull()); assertEquals(rs.getString(2), "foo"); assertFalse(rs.wasNull()); assertEquals(rs.getString("y"), "foo"); assertFalse(rs.wasNull()); assertFalse(rs.next()); } } }
/** * @param batched Batch mode flag. * @throws SQLException if failed. */ private void doTestAutoCommit(boolean batched) throws SQLException { IgniteCache<Integer, ?> cache = grid(0).cache("ints"); try (Connection c = c(false, NestedTxMode.ERROR)) { try (Statement s = c.createStatement()) { assertFalse(s.executeQuery("SELECT * from INTS").next()); if (batched) { s.addBatch("INSERT INTO INTS(k, v) values(1, 1)"); s.executeBatch(); } else s.execute("INSERT INTO INTS(k, v) values(1, 1)"); // We haven't committed anything yet - this check shows that autoCommit flag is in effect. assertTrue(cache.query(new SqlFieldsQuery("SELECT * from INTS")).getAll().isEmpty()); // We should see own updates. assertTrue(s.executeQuery("SELECT * from INTS").next()); c.commit(); c.setAutoCommit(true); assertEquals(1, cache.get(1)); assertTrue(s.executeQuery("SELECT * from INTS").next()); } } }
private static void executeQuery(Statement stmt, String sql, boolean resultSet) throws SQLException{ System.out.println("Running: " + sql); long startTime = System.currentTimeMillis(); if (resultSet){ ResultSet res = stmt.executeQuery(sql); while (res.next()){ res.getString(1); } } else { stmt.execute(sql); } long endTime = System.currentTimeMillis(); long msElapsedTime = endTime - startTime; System.out.printf("Time taken for query = %d ms \n", msElapsedTime); }
@SuppressFBWarnings("DMI_EMPTY_DB_PASSWORD") public void init() throws Exception { dataCache.setupCache(); Class.forName("org.h2.Driver"); connection = DriverManager.getConnection("jdbc:h2:~/ehcache-demo-peeper", "sa", ""); try (Statement statement = connection.createStatement()) { statement.execute("CREATE TABLE IF NOT EXISTS PEEPS (" + "id bigint auto_increment primary key," + "PEEP_TEXT VARCHAR(142) NOT NULL" + ")"); connection.commit(); } }
@Test public void testToCharWithCloneMethod() throws SQLException { Connection conn = DriverManager.getConnection(getUrl()); String tableName = generateUniqueName(); String ddl = "create table " + tableName + " (k varchar primary key, v integer[])"; conn.createStatement().execute(ddl); conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('x',ARRAY[1234])"); conn.commit(); ResultSet rs = conn.createStatement().executeQuery("select to_char(v[1],'000') from " + tableName); assertTrue(rs.next()); assertEquals("Unexpected value for date ", String.valueOf(1234), rs.getString(1)); assertFalse(rs.next()); }
/** * @throws SQLException If failed. */ @Test public void testExecute() throws SQLException { conn.createStatement().execute("delete from Person where cast(substring(_key, 2, 1) as int) % 2 = 0"); assertFalse(jcache(0).containsKey("p2")); assertTrue(jcache(0).containsKeys(new HashSet<Object>(Arrays.asList("p1", "p3")))); }
@Test public void testSuccessDefaultKey() throws Exception { String accessToken = Jwts.builder() .setSubject("test") .signWith(SignatureAlgorithm.HS512, defaultKey) .compact(); try (Connection connection = createConnection(ImmutableMap.of("accessToken", accessToken))) { try (Statement statement = connection.createStatement()) { assertTrue(statement.execute("SELECT 123")); ResultSet rs = statement.getResultSet(); assertTrue(rs.next()); assertEquals(rs.getLong(1), 123); assertFalse(rs.next()); } } }
private static void createSchema(String url, String schema) throws SQLException { try (Connection connection = DriverManager.getConnection(url); Statement statement = connection.createStatement()) { statement.execute("CREATE SCHEMA " + schema); } }
private void truncateAnalysisTables(Connection connection) throws SQLException { try (Statement statement = connection.createStatement()) { // Clear inspection tables for (String table : ANALYSIS_TABLES) { statement.execute(createTruncateSql(table.toLowerCase(Locale.ENGLISH))); // commit is useless on some databases connection.commit(); } // Clear resource related tables for (String table : RESOURCE_RELATED_TABLES) { statement.execute("DELETE FROM " + table + " WHERE resource_id IS NOT NULL"); connection.commit(); } } }
/** * @param batched Batch mode flag. * @throws SQLException if failed. */ private void doTestAutoCommit(boolean batched) throws SQLException { IgniteCache<Integer, ?> cache = grid(0).cache("ints"); try (Connection c = c(false, NestedTxMode.ERROR)) { try (Statement s = c.createStatement()) { assertFalse(s.executeQuery("SELECT * from INTS").next()); if (batched) { s.addBatch("INSERT INTO INTS(k, v) values(1, 1)"); s.executeBatch(); } else s.execute("INSERT INTO INTS(k, v) values(1, 1)"); // We haven't committed anything yet - this check shows that autoCommit flag is in effect. assertTrue(cache.query(new SqlFieldsQuery("SELECT * from INTS")).getAll().isEmpty()); // We should see own updates. assertTrue(s.executeQuery("SELECT * from INTS").next()); c.commit(); c.setAutoCommit(true); assertEquals(1, cache.get(1)); assertTrue(s.executeQuery("SELECT * from INTS").next()); } } }
protected void execute(Connection connection, String query) throws SQLException { try (Statement statement = connection.createStatement()) { log.debug("Execute: %s", query); statement.execute(query); } }