[ https://issues.apache.org/jira/browse/DDLUTILS-271?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13136629#comment-13136629 ]
Sean Xiong commented on DDLUTILS-271: ------------------------------------- My Solution: 1) Create a sql savepoint before call statement.executeUpdate() or statement.executeBatch(). 2) Throw sql exception if the execute in step 1 is rejected by DB serve. 3) Catch the sql exception and call connection.rollback to roll the status of connection back to the savepoint we created in step 1. Affected files: PlatformImplBase.java of DDLUtils modifications: 1) Method public void insert(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException; 2) Method public void update(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException; 3) Method private void executeBatch(PreparedStatement statement, int numRows, Table table) throws DatabaseOperationException; All modified block is putted between //<Modification done By Sean Xiong> //</Modification done By Sean Xiong> Method (1) public void insert(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException { SqlDynaClass dynaClass = model.getDynaClassFor(dynaBean); SqlDynaProperty[] properties = getPropertiesForInsertion(model, dynaClass, dynaBean); Column[] autoIncrColumns = getRelevantIdentityColumns(model, dynaClass, dynaBean); if ((properties.length == 0) && (autoIncrColumns.length == 0)) { _log.warn("Cannot insert instances of type " + dynaClass + " because it has no usable properties"); return; } String insertSql = createInsertSql(model, dynaClass, properties, null); String queryIdentitySql = null; if (_log.isDebugEnabled()) { _log.debug("About to execute SQL: " + insertSql); } if (autoIncrColumns.length > 0) { if (!getPlatformInfo().isLastIdentityValueReadable()) { _log.warn("The database does not support querying for auto-generated column values"); } else { queryIdentitySql = createSelectLastInsertIdSql(model, dynaClass); } } boolean autoCommitMode = false; PreparedStatement statement = null; try { if (!getPlatformInfo().isAutoCommitModeForLastIdentityValueReading()) { autoCommitMode = connection.getAutoCommit(); connection.setAutoCommit(false); } beforeInsert(connection, dynaClass.getTable()); statement = connection.prepareStatement(insertSql); //System.out.println("insetSql " + insertSql); for (int idx = 0; idx < properties.length; idx++ ) { setObject(statement, idx + 1, dynaBean, properties[idx]); } //System.out.println("statement" + statement.toString()); //<Modification done By Sean Xiong> int count = -1; try { count = statement.executeUpdate(); connection.commit(); } catch (SQLException sqle) { connection.rollback(); // reset the autoCommit of connection to true connection.setAutoCommit(autoCommitMode); throw sqle; } //</Modification done By Sean Xiong> afterInsert(connection, dynaClass.getTable()); if (count != 1) { _log.warn("Attempted to insert a single row " + dynaBean + " in table " + dynaClass.getTableName() + " but changed " + count + " row(s)"); } } catch (SQLException ex) { throw new DatabaseOperationException("Error while inserting into the database: " + ex.getMessage(), ex); } finally { closeStatement(statement); } if (queryIdentitySql != null) { Statement queryStmt = null; ResultSet lastInsertedIds = null; try { if (getPlatformInfo().isAutoCommitModeForLastIdentityValueReading()) { // we'll commit the statement(s) if no auto-commit is enabled because // otherwise it is possible that the auto increment hasn't happened yet // (the db didn't actually perform the insert yet so no triggering of // sequences did occur) if (!connection.getAutoCommit()) { connection.commit(); } } queryStmt = connection.createStatement(); lastInsertedIds = queryStmt.executeQuery(queryIdentitySql); lastInsertedIds.next(); for (int idx = 0; idx < autoIncrColumns.length; idx++) { // we're using the index rather than the name because we cannot know how // the SQL statement looks like; rather we assume that we get the values // back in the same order as the auto increment columns Object value = getObjectFromResultSet(lastInsertedIds, autoIncrColumns[idx], idx + 1); PropertyUtils.setProperty(dynaBean, autoIncrColumns[idx].getName(), value); } } catch (NoSuchMethodException ex) { // Can't happen because we're using dyna beans } catch (IllegalAccessException ex) { // Can't happen because we're using dyna beans } catch (InvocationTargetException ex) { // Can't happen because we're using dyna beans } catch (SQLException ex) { throw new DatabaseOperationException("Error while retrieving the identity column value(s) from the database", ex); } finally { if (lastInsertedIds != null) { try { lastInsertedIds.close(); } catch (SQLException ex) { // we ignore this one } } closeStatement(statement); } } if (!getPlatformInfo().isAutoCommitModeForLastIdentityValueReading()) { try { // we need to do a manual commit now connection.commit(); connection.setAutoCommit(autoCommitMode); } catch (SQLException ex) { throw new DatabaseOperationException(ex); } } } Method (2) private void executeBatch(PreparedStatement statement, int numRows, Table table) throws DatabaseOperationException { if (statement != null) { try { Connection connection = statement.getConnection(); beforeInsert(connection, table); //<Modification done By Sean Xiong> boolean autoCommitMode = connection.getAutoCommit(); connection.setAutoCommit(false); int[] results = null; try { results = statement.executeBatch(); connection.commit(); } catch (SQLException sqle) { connection.rollback(); throw sqle; } finally { // reset the autoCommit of connection to its original value connection.setAutoCommit(autoCommitMode); } //</Modification done By Sean Xiong> closeStatement(statement); afterInsert(connection, table); boolean hasSum = true; int sum = 0; for (int idx = 0; (results != null) && (idx < results.length); idx++) { if (results[idx] < 0) { hasSum = false; if (Jdbc3Utils.supportsJava14BatchResultCodes()) { String msg = Jdbc3Utils.getBatchResultMessage(table.getName(), idx, results[idx]); if (msg != null) { _log.warn(msg); } } } else { sum += results[idx]; } } if (hasSum && (sum != numRows)) { _log.warn("Attempted to insert " + numRows + " rows into table " + table.getName() + " but changed " + sum + " rows"); } } catch (SQLException ex) { throw new DatabaseOperationException("Error while inserting into the database", ex); } } } Method (3) private void executeBatch(PreparedStatement statement, int numRows, Table table) throws DatabaseOperationException { if (statement != null) { try { Connection connection = statement.getConnection(); beforeInsert(connection, table); //<Modification done By Sean Xiong> boolean autoCommitMode = connection.getAutoCommit(); connection.setAutoCommit(false); int[] results = null; try { results = statement.executeBatch(); connection.commit(); } catch (SQLException sqle) { connection.rollback(); throw sqle; } finally { // reset the autoCommit of connection to its original value connection.setAutoCommit(autoCommitMode); } //</Modification done By Sean Xiong> closeStatement(statement); afterInsert(connection, table); boolean hasSum = true; int sum = 0; for (int idx = 0; (results != null) && (idx < results.length); idx++) { if (results[idx] < 0) { hasSum = false; if (Jdbc3Utils.supportsJava14BatchResultCodes()) { String msg = Jdbc3Utils.getBatchResultMessage(table.getName(), idx, results[idx]); if (msg != null) { _log.warn(msg); } } } else { sum += results[idx]; } } if (hasSum && (sum != numRows)) { _log.warn("Attempted to insert " + numRows + " rows into table " + table.getName() + " but changed " + sum + " rows"); } } catch (SQLException ex) { throw new DatabaseOperationException("Error while inserting into the database", ex); } } } > ERROR: current transaction is aborted, commands ignored until end of > transaction block > -------------------------------------------------------------------------------------- > > Key: DDLUTILS-271 > URL: https://issues.apache.org/jira/browse/DDLUTILS-271 > Project: DdlUtils > Issue Type: Bug > Components: Core (No specific database) > Affects Versions: 1.0 > Reporter: Sean Xiong > Assignee: Thomas Dudziak > Labels: violation,unique,index > > After a violation of unique index inside the database an Exception is thrown > and DDLUTILS keeps throwing "ERROR: current transaction is aborted, commands > ignored until end of transaction block" stopping the application to do any > database activity through it. > User case: trying to insert two data rows with the same primary key to table. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira