From 56e975461d9a041e91dfaa0e7c9642d659bd523b Mon Sep 17 00:00:00 2001 From: James Moger Date: Mon, 20 Aug 2012 09:59:00 -0400 Subject: [PATCH] Use savepoints for all bulk ops (insert all, update all, delete all) --- docs/05_releases.mkd | 4 + src/com/iciql/Db.java | 116 ++++++++++++++++++++++++++--- src/com/iciql/SQLStatement.java | 2 +- src/com/iciql/TableDefinition.java | 33 ++++++++ 4 files changed, 145 insertions(+), 10 deletions(-) diff --git a/docs/05_releases.mkd b/docs/05_releases.mkd index 79543aa..c31a8b8 100644 --- a/docs/05_releases.mkd +++ b/docs/05_releases.mkd @@ -4,6 +4,10 @@ **%VERSION%** ([zip](http://code.google.com/p/iciql/downloads/detail?name=%ZIP%)|[jar](http://code.google.com/p/iciql/downloads/detail?name=%JAR%))   *released %BUILDDATE%* +- All bulk operations (insert all, update all, delete all) now use JDBC savepoints to ensure atomicity of the transaction + +**1.0.0**   *released 2012-07-14* + - Issue CREATE TABLE and CREATE INDEX statements once per-db instance/table-mapping - Fixed bug in using 0L primitive values in where clauses. These were confused with the COUNT(*) function. (Github/kc5nra,issue 5) - Added support for single column subquery *select name, address from user_table where user_id in (select user_id from invoice table where paid = false)* diff --git a/src/com/iciql/Db.java b/src/com/iciql/Db.java index 6187aba..6e23a59 100644 --- a/src/com/iciql/Db.java +++ b/src/com/iciql/Db.java @@ -22,6 +22,8 @@ import java.sql.DatabaseMetaData; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; +import java.sql.SQLFeatureNotSupportedException; +import java.sql.Savepoint; import java.sql.Statement; import java.util.ArrayList; import java.util.Collections; @@ -58,7 +60,7 @@ public class Db { private static final Map TOKENS; private static final Map> DIALECTS; - + private final Connection conn; private final Map, TableDefinition> classMap = Collections .synchronizedMap(new HashMap, TableDefinition>()); @@ -431,28 +433,88 @@ public class Db { } public void insertAll(List list) { - for (T t : list) { - insert(t); + if (list.size() == 0) { + return; + } + Savepoint savepoint = null; + try { + Class clazz = list.get(0).getClass(); + TableDefinition def = define(clazz).createIfRequired(this); + savepoint = prepareSavepoint(); + for (T t : list) { + PreparedStatement ps = def.createInsertStatement(this, t, false); + int rc = ps.executeUpdate(); + if (rc == 0) { + throw new IciqlException("Failed to insert {0}. Affected rowcount == 0.", t); + } + } + commit(savepoint); + } catch (SQLException e) { + rollback(savepoint); + throw new IciqlException(e); + } catch (IciqlException e) { + rollback(savepoint); + throw e; } } public List insertAllAndGetKeys(List list) { List identities = new ArrayList(); - for (T t : list) { - identities.add(insertAndGetKey(t)); + if (list.size() == 0) { + return identities; + } + Savepoint savepoint = null; + try { + Class clazz = list.get(0).getClass(); + TableDefinition def = define(clazz).createIfRequired(this); + savepoint = prepareSavepoint(); + for (T t : list) { + long key = def.insert(this, t, true); + identities.add(key); + } + commit(savepoint); + } catch (IciqlException e) { + rollback(savepoint); + throw e; } return identities; } public void updateAll(List list) { - for (T t : list) { - update(t); + if (list.size() == 0) { + return; + } + Savepoint savepoint = null; + try { + Class clazz = list.get(0).getClass(); + TableDefinition def = define(clazz).createIfRequired(this); + savepoint = prepareSavepoint(); + for (T t : list) { + def.update(this, t); + } + commit(savepoint); + } catch (IciqlException e) { + rollback(savepoint); + throw e; } } public void deleteAll(List list) { - for (T t : list) { - delete(t); + if (list.size() == 0) { + return; + } + Savepoint savepoint = null; + try { + Class clazz = list.get(0).getClass(); + TableDefinition def = define(clazz).createIfRequired(this); + savepoint = prepareSavepoint(); + for (T t : list) { + def.delete(this, t); + } + commit(savepoint); + } catch (IciqlException e) { + rollback(savepoint); + throw e; } } @@ -467,6 +529,42 @@ public class Db { throw IciqlException.fromSQL(sql, e); } } + + Savepoint prepareSavepoint() { + // create a savepoint + Savepoint savepoint = null; + try { + conn.setAutoCommit(false); + savepoint = conn.setSavepoint(); + } catch (SQLFeatureNotSupportedException e) { + // jdbc driver does not support save points + } catch (SQLException e) { + throw new IciqlException(e, "Could not create save point"); + } + return savepoint; + } + + void commit(Savepoint savepoint) { + if (savepoint != null) { + try { + conn.commit(); + conn.setAutoCommit(true); + } catch (SQLException e) { + throw new IciqlException(e, "Failed to commit pending transactions"); + } + } + } + + void rollback(Savepoint savepoint) { + if (savepoint != null) { + try { + conn.rollback(savepoint); + conn.setAutoCommit(true); + } catch (SQLException s) { + throw new IciqlException(s, "Failed to rollback transactions"); + } + } + } @SuppressWarnings("unchecked") TableDefinition getTableDefinition(Class clazz) { diff --git a/src/com/iciql/SQLStatement.java b/src/com/iciql/SQLStatement.java index 69e26ed..2f97829 100644 --- a/src/com/iciql/SQLStatement.java +++ b/src/com/iciql/SQLStatement.java @@ -166,7 +166,7 @@ public class SQLStatement { } } - private PreparedStatement prepare(boolean returnGeneratedKeys) { + PreparedStatement prepare(boolean returnGeneratedKeys) { PreparedStatement prep = db.prepare(getSQL(), returnGeneratedKeys); for (int i = 0; i < params.size(); i++) { Object o = params.get(i); diff --git a/src/com/iciql/TableDefinition.java b/src/com/iciql/TableDefinition.java index b6a670b..bd61b16 100644 --- a/src/com/iciql/TableDefinition.java +++ b/src/com/iciql/TableDefinition.java @@ -18,6 +18,7 @@ package com.iciql; import java.lang.reflect.Field; +import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; @@ -489,6 +490,38 @@ public class TableDefinition { // return the value unchanged return value; } + + PreparedStatement createInsertStatement(Db db, Object obj, boolean returnKey) { + SQLStatement stat = new SQLStatement(db); + StatementBuilder buff = new StatementBuilder("INSERT INTO "); + buff.append(db.getDialect().prepareTableName(schemaName, tableName)).append('('); + for (FieldDefinition field : fields) { + if (skipInsertField(field, obj)) { + continue; + } + buff.appendExceptFirst(", "); + buff.append(db.getDialect().prepareColumnName(field.columnName)); + } + buff.append(") VALUES("); + buff.resetCount(); + for (FieldDefinition field : fields) { + if (skipInsertField(field, obj)) { + continue; + } + buff.appendExceptFirst(", "); + buff.append('?'); + Object value = getValue(obj, field); + if (value == null && !field.nullable) { + // try to interpret and instantiate a default value + value = ModelUtils.getDefaultValue(field, db.getDialect().getDateTimeClass()); + } + stat.addParameter(value); + } + buff.append(')'); + stat.setSQL(buff.toString()); + IciqlLogger.insert(stat.getSQL()); + return stat.prepare(returnKey); + } long insert(Db db, Object obj, boolean returnKey) { SQLStatement stat = new SQLStatement(db); -- 2.39.5