Refine search
private void runTableScripts(final Connection conn, final String table) throws IOException, SQLException { logger.info("Creating new table " + table); final String dbSpecificScript = "create." + table + ".sql"; final File script = new File(this.scriptPath, dbSpecificScript); BufferedInputStream buff = null; try { buff = new BufferedInputStream(new FileInputStream(script)); final String queryStr = IOUtils.toString(buff); final String[] splitQuery = queryStr.split(";\\s*\n"); final QueryRunner runner = new QueryRunner(); for (final String query : splitQuery) { runner.update(conn, query); } conn.commit(); } finally { IOUtils.closeQuietly(buff); } }
@Before public void setUp() throws Exception { this.queryRunner = mock(QueryRunner.class); this.conn = this.datasource.getConnection(); final DataSource mockDataSource = mock(this.datasource.getClass()); when(this.queryRunner.getDataSource()).thenReturn(mockDataSource); when(mockDataSource.getConnection()).thenReturn(this.conn); this.dbOperator = new DatabaseOperator(this.queryRunner); list.add(index_1); list.add(index_2); // valid query returns correct value when(this.queryRunner.query("select * from blah where ? = ?", this.handler, "id", 2)) .thenReturn(index_2); // If select an non-existing entry, handler returns 0. when(this.queryRunner.query("select * from blah where ? = ?", this.handler, "id", 3)) .thenReturn(0); //If typos, throw Exceptions. doThrow(SQLException.class).when(this.queryRunner) .query("sele * from blah where ? = ?", this.handler, "id", 2); doAnswer(invocation -> { index_1 = 26; return 1; }).when(this.queryRunner).update("update blah set ? = ?", "1", 26); }
@Test public void testMySQLWithConnectionPoolUsingSameContainer() throws SQLException, InterruptedException { // Populate the database with some data in multiple threads, so that multiple connections from the pool will be used for (int i = 0; i < 100; i++) { executorService.submit(() -> { try { new QueryRunner(dataSource).insert("INSERT INTO my_counter (n) VALUES (5)", (ResultSetHandler<Object>) rs -> true); } catch (SQLException e) { e.printStackTrace(); } }); } // Complete population of the database executorService.shutdown(); executorService.awaitTermination(5, TimeUnit.MINUTES); // compare to expected results int count = new QueryRunner(dataSource).query("SELECT COUNT(1) FROM my_counter", rs -> { rs.next(); return rs.getInt(1); }); assertEquals("Reuse of a datasource points to the same DB container", 100, count); int sum = new QueryRunner(dataSource).query("SELECT SUM(n) FROM my_counter", rs -> { rs.next(); return rs.getInt(1); }); // 100 records * 5 = 500 expected assertEquals("Reuse of a datasource points to the same DB container", 500, sum); }
/** * Drop foreign key with autogenerated name based on the table where constrain declared and * referenced table name. */ public void dropUnnamedFk(String tableName, String referencedTableName) throws SQLException { QueryRunner runner = new QueryRunner(); String query = String.format(QUERY_FIND_FK_NAME, tableName, referencedTableName); String fkName = runner.query(connection, query, rs -> rs.next() ? rs.getString(1) : null); if (fkName != null) { runner.update(connection, "ALTER TABLE " + tableName + " DROP FOREIGN KEY " + fkName); } else { System.err.println("FK name not found"); } }
/** * Do main part of data migration from old tables to new ctl based ones. * * @throws SQLException the sql exception */ protected List<Schema> transform() throws SQLException { // fetch schemas of appropriate feature like configuration List<Schema> schemas = runner.query(connection, "select " + "f.id as id, created_time as createdTime, created_username as createdUsername, " + "description, name, schems, version, application_id as appId " + "from " + getPrefixTableName() + "_schems f join schems s on f.id = s.id", new BeanListHandler<>(Schema.class)); // delete the fetched ids from schema table String toDelete = schemas.stream().map(s -> s.getId().toString()).collect(joining(", ")); String notEmptyIdSet = "^[\\s]*([0-9]+(\\,\\s)?)+"; if (toDelete.matches(notEmptyIdSet)) { runner.update(connection, "delete from schems where id in (" + toDelete + ")"); } // shift ids in order to avoid PK constraint violation during adding record to base_schema Long shift = runner.query(connection, "select max(id) as max_id from " + getPrefixTableName() + "_schems", rs -> rs.next() ? rs.getLong("max_id") : null); idShift = BaseSchemaIdCounter.getInstance().getAndShift(shift); runner.update(connection, "update " + getPrefixTableName() + "_schems set id = id + " + idShift + " order by id desc"); schemas.forEach(s -> s.setId(s.getId() + idShift)); return schemas; }
private void callGoodQuery(Connection conn) throws Exception { when(meta.getParameterCount()).thenReturn(2); runner.query(conn, "select * from blah where ? = ?", handler, "unit", "test"); verify(stmt, times(1)).executeQuery(); verify(results, times(1)).close(); verify(stmt, times(1)).close(); // make sure we closed the statement verify(conn, times(0)).close(); // make sure we do not close the connection, since QueryRunner.query(Connection, String, ResultSetHandler<T>, Object...) does not close connections // call the other variation of query when(meta.getParameterCount()).thenReturn(0); runner.query(conn, "select * from blah", handler); verify(stmt, times(2)).executeQuery(); verify(results, times(2)).close(); verify(stmt, times(2)).close(); // make sure we closed the statement verify(conn, times(0)).close(); // make sure we do not close the connection, see above }
private void callUpdateWithException(Object... params) throws Exception { boolean caught = false; try { when(meta.getParameterCount()).thenReturn(2); runner.update("select * from blah where ? = ?", params); verify(stmt, times(1)).executeUpdate(); verify(stmt, times(1)).close(); // make sure we closed the statement verify(conn, times(1)).close(); // make sure we closed the connection } catch(SQLException e) { caught = true; } if(!caught) { fail("Exception never thrown, but expected"); } }
@Test public void testGoodInsert() throws Exception { results = mock(ResultSet.class); when(meta.getParameterCount()).thenReturn(2); when(conn.prepareStatement(any(String.class), eq(Statement.RETURN_GENERATED_KEYS))).thenReturn(stmt); when(stmt.getGeneratedKeys()).thenReturn(results); when(results.next()).thenReturn(true).thenReturn(false); when(results.getObject(1)).thenReturn(1L); Long generatedKey = runner.insert("INSERT INTO blah(col1, col2) VALUES(?,?)", new ScalarHandler<Long>(), "unit", "test"); verify(stmt, times(1)).executeUpdate(); verify(stmt, times(1)).close(); // make sure we closed the statement verify(conn, times(1)).close(); // make sure we closed the connection Assert.assertEquals(1L, generatedKey.longValue()); }
private void callGoodBatch(Connection conn, Object[][] params) throws Exception { when(meta.getParameterCount()).thenReturn(2); runner.batch(conn, "select * from blah where ? = ?", params); verify(stmt, times(2)).addBatch(); verify(stmt, times(1)).executeBatch(); verify(stmt, times(1)).close(); // make sure we closed the statement verify(conn, times(0)).close(); // make sure we do not close the connection, since QueryRunner.batch(Connection, String, Object[][]) does not close connections }
@Test(expected=SQLException.class) public void testNullConnectionQuery() throws Exception { when(meta.getParameterCount()).thenReturn(2); when(dataSource.getConnection()).thenReturn(null); runner.query("select * from blah where ? = ?", handler, "unit", "test"); }
@Before public void setUp() throws Exception { MockitoAnnotations.initMocks(this); when(dataSource.getConnection()).thenReturn(conn); when(conn.prepareStatement(any(String.class))).thenReturn(stmt); when(stmt.getParameterMetaData()).thenReturn(meta); when(stmt.getResultSet()).thenReturn(results); when(stmt.executeQuery()).thenReturn(results); when(results.next()).thenReturn(false); handler = new ArrayHandler(); runner = new AsyncQueryRunner(Executors.newFixedThreadPool(1), new QueryRunner(dataSource)); }
@Test(expected=SQLException.class) public void testNullConnectionBatch() throws Exception { String[][] params = new String[][] { { "unit", "unit" }, { "test", "test" } }; when(meta.getParameterCount()).thenReturn(2); when(dataSource.getConnection()).thenReturn(null); runner.batch("select * from blah where ? = ?", params); }
@Test(expected=SQLException.class) public void testNullSqlUpdate() throws Exception { when(meta.getParameterCount()).thenReturn(2); runner.update(null); }
private void callBatchWithException(String sql, Object[][] params) throws Exception { boolean caught = false; try { runner.batch(sql, params); verify(stmt, times(2)).addBatch(); verify(stmt, times(1)).executeBatch(); verify(stmt, times(1)).close(); // make sure the statement is closed verify(conn, times(1)).close(); // make sure the connection is closed } catch(SQLException e) { caught = true; } if(!caught) { fail("Exception never thrown, but expected"); } }
@Test public void testTransaction() throws Exception { when(this.queryRunner.update(this.conn, "update blah set ? = ?", "1", 26)).thenReturn(1); when(this.queryRunner.query(this.conn, "select * from blah where ? = ?", this.handler, "id", 1)) .thenReturn(26); final SQLTransaction<Integer> transaction = transOperator -> { transOperator.update("update blah set ? = ?", "1", 26); return transOperator.query("select * from blah where ? = ?", this.handler, "id", 1); }; final int res = this.dbOperator.transaction(transaction); Assert.assertEquals(26, res); }
@Test public void testInsertUsesGivenQueryRunner() throws Exception { QueryRunner mockQueryRunner = mock(QueryRunner.class , org.mockito.Mockito.withSettings().verboseLogging() // debug for Continuum ); runner = new AsyncQueryRunner(Executors.newSingleThreadExecutor(), mockQueryRunner); runner.insert("1", handler); runner.insert("2", handler, "param1"); runner.insert(conn, "3", handler); runner.insert(conn, "4", handler, "param1"); // give the Executor time to submit all insert statements. Otherwise the following verify statements will fail from time to time. TimeUnit.MILLISECONDS.sleep(50); verify(mockQueryRunner).insert("1", handler); verify(mockQueryRunner).insert("2", handler, "param1"); verify(mockQueryRunner).insert(conn, "3", handler); verify(mockQueryRunner).insert(conn, "4", handler, "param1"); }
@Override public void undoRollbackInfoSql(Connection connection, RollbackInfo rollbackInfo) throws SQLException { try { connection.setAutoCommit(false); for (StatementInfo statementInfo : rollbackInfo.getRollbackSqlList()) { log.debug("txc > Apply undo log. sql: {}, params: {}", statementInfo.getSql(), statementInfo.getParams()); queryRunner.update(connection, statementInfo.getSql(), statementInfo.getParams()); } connection.commit(); } catch (SQLException e) { DbUtils.rollback(connection); throw e; } finally { connection.setAutoCommit(true); } }
public void executeUpdateSql(String sql, Object... params) { try (Connection connection = getConnection()) { new QueryRunner().update(connection, sql, params); if (!connection.getAutoCommit()) { connection.commit(); } } catch (SQLException e) { SQLException nextException = e.getNextException(); if (nextException != null) { throw new IllegalStateException("Fail to execute sql: " + sql, new SQLException(e.getMessage(), nextException.getSQLState(), nextException.getErrorCode(), nextException)); } throw new IllegalStateException("Fail to execute sql: " + sql, e); } catch (Exception e) { throw new IllegalStateException("Fail to execute sql: " + sql, e); } }