/** * * @param updateClause * @param params * @return * @throws SQLException */ public int update(final String updateClause, final Object... params) throws SQLException { try { return this.queryRunner.update(this.conn, updateClause, params); } catch (final SQLException ex) { //RETRY Logic should be implemented here if needed. throw ex; } finally { // Note: CAN NOT CLOSE CONNECTION HERE. } }
public int update(String sql, Object... params) { try { return queryRunner.update(sql, params); } catch (SQLException e) { log.error("update error",e); return 0; } }
public int update(String sql, Object... params) { try { return queryRunner.update(sql, params); } catch (SQLException e) { log.error("update error", e); return 0; } }
@Override public void clearLock(String groupId, String unitId) throws SQLException { log.debug("txc > sql > executor > clear lock. groupId: {}, unitId: {}", groupId, unitId); String cleanLockSql = "DELETE FROM `" + txcSql.lockTableName() + "` where group_id = ? and unit_id = ?"; queryRunner.update(cleanLockSql, groupId, unitId); }
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); } }
/** * Executes the given AZ related INSERT, UPDATE, or DELETE SQL statement. * it will call {@link AzkabanDataSource#getConnection()} inside * queryrunner.update. * * @param updateClause sql statements to execute * @param params Initialize the PreparedStatement's IN parameters * @return The number of rows updated. */ public int update(final String updateClause, final Object... params) throws SQLException { try { return this.queryRunner.update(updateClause, params); } catch (final SQLException ex) { // todo kunkun-tang: Retry logics should be implemented here. logger.error("update failed", ex); if (this.dbMetrics != null) { this.dbMetrics.markDBFailUpdate(); } throw ex; } }
@Override public void clearUndoLog(String groupId, String unitId) throws SQLException { log.debug("txc > clear undo log. groupId: {}, unitId: {}", groupId, unitId); txLogger.trace(groupId, unitId, "txc", "clear undo log"); String cleanUndoLogSql = "DELETE FROM `" + txcSql.undoLogTableName() + "` WHERE group_id = ? and unit_id = ?"; queryRunner.update(cleanUndoLogSql, groupId, unitId); } }
/** * 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"); } }
@Override public void beforeTransform() throws SQLException { dd.dropUnnamedFk(EVENT_CLASS_TABLE_NAME, EVENT_CLASS_FAMILY_TABLE_NAME); dd.dropUnnamedFk(APPLICATION_EVENT_MAP_TABLE_NAME, EVENT_CLASS_TABLE_NAME); runner.update( connection, "ALTER TABLE " + BASE_SCHEMA_TABLE_NAME + " CHANGE application_id application_id bigint(20)"); }
runner.update(conn, query); runner.update(conn, INSERT_DB_PROPERTY, propertyName, DataSourceUtils.PropertyType.DB.getNumVal(), version, System.currentTimeMillis()); } else { runner.update(conn, UPDATE_DB_PROPERTY, version, System.currentTimeMillis(), propertyName, DataSourceUtils.PropertyType.DB.getNumVal());
private void updateFamilyVersionId(EventClass ec, List<EventSchemaVersion> versions, QueryRunner runner) throws SQLException { for (EventSchemaVersion esv : versions) { if (ecBelongToThisFamilyVersion(ec, esv)) { int updateCount = runner.update(this.connection, "UPDATE " + EVENT_CLASS_TABLE_NAME + " SET events_class_family_versions_id=? WHERE id=?", esv.getId(), ec.getId()); if (updateCount != 1) { System.err.println("Error: failed to update event class's reference to ECFV: " + ec); } break; } } }
@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); } }
/** * 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; }
runner.update(connection, sql.toString());
@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 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 testValidUpdate() throws Exception { final int res = this.dbOperator.update("update blah set ? = ?", "1", 26); // 1 row is affected Assert.assertEquals(1, res); Assert.assertEquals(26, index_1); verify(this.queryRunner).update("update blah set ? = ?", "1", 26); }
private static void clearMySQLTestDB() throws SQLException { final Props props = new Props(); props.put("database.type", "mysql"); props.put("mysql.host", "localhost"); props.put("mysql.port", "3306"); props.put("mysql.database", ""); props.put("mysql.user", "root"); props.put("mysql.password", ""); props.put("mysql.numconnections", 10); final DataSource datasource = DataSourceUtils.getDataSource(props); final QueryRunner runner = new QueryRunner(datasource); try { runner.update("drop database azkabanunittest"); } catch (final SQLException e) { } runner.update("create database azkabanunittest"); }
private static void clearMySQLTestDb() throws SQLException { final Props props = new Props(); props.put("database.type", "mysql"); props.put("mysql.host", "localhost"); props.put("mysql.port", "3306"); props.put("mysql.database", ""); props.put("mysql.user", "root"); props.put("mysql.password", ""); props.put("mysql.numconnections", 10); final DataSource datasource = DataSourceUtils.getDataSource(props); final QueryRunner runner = new QueryRunner(datasource); try { runner.update("drop database azkabanunittest"); } catch (final SQLException e) { } runner.update("create database azkabanunittest"); }