Use savepoints for all bulk ops (insert all, update all, delete all)

3 files changed, 141 insertions, 10 deletions

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<Object, Token> TOKENS;

 

 	private static final Map<String, Class<? extends SQLDialect>> DIALECTS;

-

+	

 	private final Connection conn;

 	private final Map<Class<?>, TableDefinition<?>> classMap = Collections

 			.synchronizedMap(new HashMap<Class<?>, TableDefinition<?>>());

@@ -431,28 +433,88 @@ public class Db {
 	}

 

 	public <T> void insertAll(List<T> 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 <T> List<Long> insertAllAndGetKeys(List<T> list) {

 		List<Long> identities = new ArrayList<Long>();

-		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 <T> void updateAll(List<T> 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 <T> void deleteAll(List<T> 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")

 	<T> TableDefinition<T> getTableDefinition(Class<T> 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<T> {
 		// 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);