[
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
