/** * * @param querySql * @param resultHandler * @param params * @param <T> * @return * @throws SQLException */ public <T> T query(final String querySql, final ResultSetHandler<T> resultHandler, final Object... params) throws SQLException { try { return this.queryRunner.query(this.conn, querySql, resultHandler, params); } catch (final SQLException ex) { //RETRY Logic should be implemented here if needed. throw ex; } finally { // Note: CAN NOT CLOSE CONNECTION HERE. } }
public <T> T query(String sql,ScalarHandler<T> scalarHandler,Object ... params){ try { return queryRunner.query(sql,scalarHandler,params); } catch (SQLException e) { log.error("query error",e); return null; } }
public <T> T query(String sql, ResultSetHandler<T> rsh,Object ... params){ try { return queryRunner.query(sql, rsh,params); } catch (SQLException e) { log.error("query error",e); return null; } }
public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) { try { return queryRunner.query(sql, rsh, params); } catch (SQLException e) { log.error("query error", e); return null; } }
public <T> T query(String sql, ScalarHandler<T> scalarHandler, Object... params) { try { return queryRunner.query(sql, scalarHandler, params); } catch (SQLException e) { log.error("query error", e); return null; } }
/** * returns the last id from a previous insert statement. Note that last insert and this operation * should use the same connection. * * @return the last inserted id in mysql per connection. */ public long getLastInsertId() throws SQLException { // A default connection: autocommit = true. long num = -1; try { num = ((Number) this.queryRunner .query(this.conn, "SELECT LAST_INSERT_ID();", new ScalarHandler<>(1))) .longValue(); } catch (final SQLException ex) { logger.error("can not get last insertion ID"); throw ex; } return num; }
/** * 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"); } }
private void loadTableVersion() throws SQLException { logger.info("Searching for table versions in the properties table"); if (this.tables.containsKey("properties")) { // Load version from settings final QueryRunner runner = new QueryRunner(this.dataSource); final Map<String, String> map = runner.query(FETCH_PROPERTY_BY_TYPE, new PropertiesHandler(), PropertyType.DB.getNumVal()); for (final String key : map.keySet()) { final String value = map.get(key); if (key.endsWith(".version")) { final String tableName = key.substring(0, key.length() - ".version".length()); this.installedVersions.put(tableName, value); if (this.tables.containsKey(tableName)) { this.tables.put(tableName, value); } } } } else { logger.info("Properties table doesn't exist."); } }
@Override public List<ModifiedRecord> deleteSqlPreviousData(Connection connection, DeleteImageParams deleteImageParams) throws SQLException { String beforeSql = SqlUtils.SELECT + String.join(SqlUtils.SQL_COMMA_SEPARATOR, deleteImageParams.getColumns()) + SqlUtils.FROM + String.join(SqlUtils.SQL_COMMA_SEPARATOR, deleteImageParams.getTables()) + SqlUtils.WHERE + deleteImageParams.getSqlWhere(); return queryRunner.query(connection, beforeSql, new UpdateSqlPreDataHandler( deleteImageParams.getPrimaryKeys(), deleteImageParams.getColumns())); }
@Override public List<ModifiedRecord> updateSqlPreviousData(Connection connection, UpdateImageParams updateImageParams) throws SQLException { // 前置镜像sql String beforeSql = SqlUtils.SELECT + String.join(SqlUtils.SQL_COMMA_SEPARATOR, updateImageParams.getColumns()) + SqlUtils.SQL_COMMA_SEPARATOR + String.join(SqlUtils.SQL_COMMA_SEPARATOR, updateImageParams.getPrimaryKeys()) + SqlUtils.FROM + String.join(SqlUtils.SQL_COMMA_SEPARATOR, updateImageParams.getTables()) + SqlUtils.WHERE + updateImageParams.getWhereSql(); return queryRunner.query(connection, beforeSql, new UpdateSqlPreDataHandler(updateImageParams.getPrimaryKeys(), updateImageParams.getColumns())); }
@Override public List<ModifiedRecord> selectSqlPreviousPrimaryKeys(Connection connection, SelectImageParams selectImageParams) throws SQLException { return queryRunner.query(connection, selectImageParams.getSql(), new UpdateSqlPreDataHandler( selectImageParams.getPrimaryKeys(), selectImageParams.getPrimaryKeys())); }
@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); }
private void performTestForScriptedSchema(String jdbcUrl) throws SQLException { try (HikariDataSource dataSource = getDataSource(jdbcUrl, 1)) { boolean result = new QueryRunner(dataSource).query("SELECT foo FROM bar WHERE foo LIKE '%world'", rs -> { rs.next(); String resultSetString = rs.getString(1); assertEquals("A basic SELECT query succeeds where the schema has been applied from a script", "hello world", resultSetString); return true; }); } }
private void performSimpleTest(String jdbcUrl) throws SQLException { try (HikariDataSource dataSource = getDataSource(jdbcUrl, 1)) { boolean result = new QueryRunner(dataSource, options.contains(Options.PmdKnownBroken)).query("SELECT 1", rs -> { rs.next(); int resultSetInt = rs.getInt(1); assertEquals("A basic SELECT query succeeds", 1, resultSetInt); return true; }); assertTrue("The database returned a record as expected", result); } }
@Test public void testValidQuery() throws Exception { final int res = this.dbOperator.query("select * from blah where ? = ?", this.handler, "id", 2); Assert.assertEquals(15, res); verify(this.queryRunner).query("select * from blah where ? = ?", this.handler, "id", 2); }
private HikariDataSource verifyCharacterSet(String jdbcUrl) throws SQLException { HikariDataSource dataSource = getDataSource(jdbcUrl, 1); boolean result = new QueryRunner(dataSource).query("SHOW VARIABLES LIKE 'character\\_set\\_connection'", rs -> { rs.next(); String resultSetInt = rs.getString(2); assertEquals("Passing query parameters to set DB connection encoding is successful", "utf8", resultSetInt); return true; }); assertTrue("The database returned a record as expected", result); return dataSource; }
private void performTestForCharacterEncodingForInitialScriptConnection(String jdbcUrl) throws SQLException { try (HikariDataSource dataSource = getDataSource(jdbcUrl, 1)) { boolean result = new QueryRunner(dataSource).query("SELECT foo FROM bar WHERE foo LIKE '%мир'", rs -> { rs.next(); String resultSetString = rs.getString(1); assertEquals("A SELECT query succeed and the correct charset has been applied for the init script", "привет мир", resultSetString); return true; }); assertTrue("The database returned a record as expected", result); } }
@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); }
private void performTestForJDBCParamUsage(String jdbcUrl) throws SQLException { try (HikariDataSource dataSource = getDataSource(jdbcUrl, 1)) { boolean result = new QueryRunner(dataSource).query("select CURRENT_USER()", rs -> { rs.next(); String resultUser = rs.getString(1); assertEquals("User from query param is created.", "someuser@%", resultUser); return true; }); assertTrue("The database returned a record as expected", result); result = new QueryRunner(dataSource).query("SELECT DATABASE()", rs -> { rs.next(); String resultDB = rs.getString(1); assertEquals("Database name from URL String is used.", "databasename", resultDB); return true; }); assertTrue("The database returned a record as expected", result); } }
private void performSimpleTest(String jdbcUrl) throws SQLException { HikariDataSource dataSource = getDataSource(jdbcUrl, 1); new QueryRunner(dataSource).query("SELECT 1 FROM dual", new ResultSetHandler<Object>() { @Override public Object handle(ResultSet rs) throws SQLException { rs.next(); int resultSetInt = rs.getInt(1); assertEquals("A basic SELECT query succeeds", 1, resultSetInt); return true; } }); dataSource.close(); }