com.databasesandlife.util.jdbc

Class DbTransaction

java.lang.Object

com.databasesandlife.util.jdbc.DbTransaction

All Implemented Interfaces:

DbQueryable, AutoCloseable

public class DbTransaction
extends Object
implements DbQueryable, AutoCloseable

Represents a transaction against a database.

MySQL, PostgreSQL and SQL Server are supported.

An object is created with the JDBC URL to the database. There is no factory for this type of object, simply store the string JDBC URL as opposed to a DbConnectionFactory.

The following facilities are provided (i.e. these are the reasons why objects of this class might be preferred over simply using JDBC directly):

• SQLExceptions are not thrown. This allows checked Exceptions to be used in client code to represent things that might actually happen. If a SQLException occurs e.g. due to database connectivity, it is assumed there is nothing the program can do apart from display an error to the user.
• execute(java.lang.String, java.lang.Object...) method prepares statements and does parameter substitution in one line.
• query(java.lang.String, java.lang.Object...) method acts like execute, but returns an Iterable of objects representing rows. This is more convenient for the java "for" statement than the JDBC ResultSet object.
• Various extra data types are supported such as "points in time" stored as GMT date/times using java.util.Date, LocalDate, etc.
• insert(java.lang.String, java.util.Map<java.lang.String, ?>) and update(java.lang.String, java.util.Map<java.lang.String, ?>, java.lang.String, java.lang.Object...) take Maps of columns as arguments (easier than maintaining SQL strings)
• insertAndFetchNewId(java.lang.String, java.util.Map<java.lang.String, ?>) performs an insert and returns the new "auto-increment ID".
• insertIgnoringUniqueConstraintViolations(java.lang.String, java.util.Map<java.lang.String, ?>) and updateIgnoringUniqueConstraintViolations(java.lang.String, java.util.Map<java.lang.String, ?>, java.lang.String, java.lang.Object...) perform inserts and updates, but ignore any unique constraint violations. For example using the "insert then update" pattern, for "just-in-time" creating records, can use these methods.
• attempt(Runnable) establishes a savepoint before the runnable and rolls back to it on failure, necessary for any operation that may fail when using PostgreSQL.
• The transaction isolation level is set to REPEATABLE READ. (This is the default in MySQL but not other databases.)
• You can register DbTransaction.RollbackListener objects with addRollbackListener(RollbackListener). When the transaction rolls back, this listener will get called. This is so that any primary keys which have been assigned and stored in Java objects, which are now no longer valid due to the rollback, may be removed from the Java objects.

Upon creating an object, a connection is made to the database, and a transaction is started. Upon executing commit() or rollback() the connection is closed. Although opening a connection each time is not as efficient as using a connection pool, this class is extremely simple, which has advantages both in terms or reliability, maintainability and also speed. (For example, C3P0 has > 50 KLOC). Opening a connection to MySQL is fast.

DbTransaction objects are not thread safe; do not use them from multiple threads simultaneously.

Example usage:

   String jdbc = "jdbc:mysql://hostname/dbName?user=x&password=x&useUnicode=true&characterEncoding=UTF-8";
   try (DbTransaction db = new DbTransaction(jdbc)) {
      db.execute("DELETE FROM x WHERE id=?", 9);
      db.commit();
   }
 

Author:

This source is copyright Adrian Smith and licensed under the LGPL 3.

See Also:

Project on GitHub

Nested Class Summary

Nested Classes

Modifier and Type	Class and Description
static class	DbTransaction.CannotConnectToDatabaseException
static class	DbTransaction.DbQueryResultRow
static class	DbTransaction.DbQueryResultRowIterator
static class	DbTransaction.DbQueryResultSet
static class	DbTransaction.DbServerProduct
static interface	DbTransaction.DbTransactionFactory
static class	DbTransaction.ForeignKeyConstraintViolation
static interface	DbTransaction.RollbackListener
static class	DbTransaction.SqlException
static class	DbTransaction.UniqueConstraintViolation

Field Summary

Fields

Modifier and Type	Field and Description
protected Connection	connection
protected Map<Class<? extends Enum<?>>,String>	postgresTypeForEnum
protected Map<String,PreparedStatement>	preparedStatements
DbTransaction.DbServerProduct	product
protected List<DbTransaction.RollbackListener>	rollbackListeners

Constructor Summary

Constructors

Constructor and Description
DbTransaction(String jdbcUrl)

Method Summary

Modifier and Type	Method and Description
void	addPostgresTypeForEnum(Class<? extends Enum<?>> enumClass, String postgresType)
void	addRollbackListener(DbTransaction.RollbackListener listener) For example, during insert in jOOQ:
<V> void	appendIn(Appendable sql, List<? super V> sqlParams, String field, Collection<? extends V> values) Writes "foo IN (?,?,?)"
protected void	appendInsertStatement(StringBuilder sql, List<Object> params, String table, Map<String,?> cols)
protected void	appendSetClauses(StringBuilder sql, List<Object> params, Map<String,?> cols)
void	attempt(Runnable r) Sets a savepoint as is necessary on PostgreSQL, runs the code, then rolls back to the savepoint on RuntimeException or discards the savepoint on success.
void	close()
protected void	closeConnection()
void	commit()
void	deleteOrThrowForeignKeyConstraintViolation(String table, String where, Object... args) For normal delete where you don't expect a possible foreign key constraint violation, use execute(String, Object...) instead
void	execute(CharSequence sql, List<?> args)
void	execute(String sql, Object... args)
protected long	fetchNewPkValue()
protected Connection	getConnection()
String	getFromDual()
protected PreparedStatement	getPreparedStatement(String sql)
protected String	getQuestionMarkForValue(Object value)
String	getSchemaQuote()
static String	getSqlForLog(String sql, Object[] args)
void	insert(String table, Map<String,?> cols)
void	insert(org.jooq.TableRecord<?> record)
long	insertAndFetchNewId(String table, Map<String,?> cols)
long	insertAndFetchNewIdOrThrowUniqueConstraintViolation(String table, Map<String,?> cols)
void	insertIgnoringUniqueConstraintViolations(String table, Map<String,?> cols)
void	insertOrThrowUniqueConstraintViolation(String table, Map<String,?> cols)
void	insertOrUpdate(String table, Map<String,?> colsToUpdate, Map<String,?> colsToInsert, String... primaryKeyColumns) Inserts (colsToInsert + colsToUpdate) and, if that fails because the row already exists, updates (colsToUpdate) where (primaryKeyColumns out of colsToInsert).
protected PreparedStatement	insertParamsToPreparedStatement(String sql, Object... args)
static boolean	isForeignKeyConstraintViolation(String msg)
org.jooq.DSLContext	jooq()
protected void	logNewTransaction()
static String	parseUniqueConstraintViolationOrNull(String msg)
DbTransaction.DbQueryResultSet	query(CharSequence sql, List<?> args)
DbTransaction.DbQueryResultSet	query(String sql, Object... args)
void	rollback()
void	rollbackIfConnectionStillOpen()
protected void	rollbackToSavepointAndThrowConstraintViolation(Savepoint initialState, RuntimeException exception) If "exception" represents a violation exception it is thrown and the connection is rolled back to "initialState", otherwise the original exception is re-thrown.
void	update(String table, Map<String,?> cols, String where, Object... whereParams)
void	updateIgnoringUniqueConstraintViolations(String table, Map<String,?> cols, String where, Object... whereParams)
void	updateOrThrowUniqueConstraintViolation(String table, Map<String,?> cols, String where, Object... whereParams)

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

product

public final DbTransaction.DbServerProduct product

connection

protected Connection connection

rollbackListeners

protected final List<DbTransaction.RollbackListener> rollbackListeners

preparedStatements

protected final Map<String,PreparedStatement> preparedStatements

postgresTypeForEnum

protected final Map<Class<? extends Enum<?>>,String> postgresTypeForEnum

Constructor Detail

DbTransaction

public DbTransaction(String jdbcUrl)
              throws DbTransaction.CannotConnectToDatabaseException

Throws:

DbTransaction.CannotConnectToDatabaseException

Method Detail

close

public void close()

Specified by:

close in interface AutoCloseable

logNewTransaction

protected void logNewTransaction()

getConnection

protected Connection getConnection()

getPreparedStatement

protected PreparedStatement getPreparedStatement(String sql)

insertParamsToPreparedStatement

protected PreparedStatement insertParamsToPreparedStatement(String sql,
                                                            Object... args)

fetchNewPkValue

protected long fetchNewPkValue()

getQuestionMarkForValue

protected String getQuestionMarkForValue(Object value)

parseUniqueConstraintViolationOrNull

public static String parseUniqueConstraintViolationOrNull(String msg)

Returns:

the name of the unique constraint that was violated, or null if the error was not about a unique constraint violation

isForeignKeyConstraintViolation

public static boolean isForeignKeyConstraintViolation(String msg)

rollbackToSavepointAndThrowConstraintViolation

protected void rollbackToSavepointAndThrowConstraintViolation(Savepoint initialState,
                                                              RuntimeException exception)
                                                       throws DbTransaction.UniqueConstraintViolation,
                                                              DbTransaction.ForeignKeyConstraintViolation

If "exception" represents a violation exception it is thrown and the connection is rolled back to "initialState", otherwise the original exception is re-thrown.

Throws:

DbTransaction.UniqueConstraintViolation

DbTransaction.ForeignKeyConstraintViolation

closeConnection

protected void closeConnection()

addPostgresTypeForEnum

public void addPostgresTypeForEnum(Class<? extends Enum<?>> enumClass,
                                   String postgresType)

addRollbackListener

public void addRollbackListener(DbTransaction.RollbackListener listener)

For example, during insert in jOOQ:

 
 db.addRollbackListener(() -> {
    venue.setVid(null); 
    venue.changed(VENUES.VID, false);
 } );
 

jooq

public org.jooq.DSLContext jooq()

getSqlForLog

public static String getSqlForLog(String sql,
                                  Object[] args)

query

public DbTransaction.DbQueryResultSet query(String sql,
                                            Object... args)

Specified by:

query in interface DbQueryable

Returns:

Never retuns null (but may return an empty iterable)

query

public DbTransaction.DbQueryResultSet query(CharSequence sql,
                                            List<?> args)

Specified by:

query in interface DbQueryable

Returns:

Never retuns null (but may return an empty iterable)

execute

public void execute(String sql,
                    Object... args)
             throws DbTransaction.SqlException

Throws:

DbTransaction.SqlException

attempt

public void attempt(Runnable r)

Sets a savepoint as is necessary on PostgreSQL, runs the code, then rolls back to the savepoint on RuntimeException or discards the savepoint on success.

deleteOrThrowForeignKeyConstraintViolation

public void deleteOrThrowForeignKeyConstraintViolation(String table,
                                                       String where,
                                                       Object... args)
                                                throws DbTransaction.ForeignKeyConstraintViolation

For normal delete where you don't expect a possible foreign key constraint violation, use execute(String, Object...) instead

Throws:

DbTransaction.ForeignKeyConstraintViolation

execute

public void execute(CharSequence sql,
                    List<?> args)

appendSetClauses

protected void appendSetClauses(StringBuilder sql,
                                List<Object> params,
                                Map<String,?> cols)

appendInsertStatement

protected void appendInsertStatement(StringBuilder sql,
                                     List<Object> params,
                                     String table,
                                     Map<String,?> cols)

insert

public void insert(String table,
                   Map<String,?> cols)

insert

public void insert(org.jooq.TableRecord<?> record)

insertOrThrowUniqueConstraintViolation

public void insertOrThrowUniqueConstraintViolation(String table,
                                                   Map<String,?> cols)
                                            throws DbTransaction.UniqueConstraintViolation

Throws:

DbTransaction.UniqueConstraintViolation

insertIgnoringUniqueConstraintViolations

public void insertIgnoringUniqueConstraintViolations(String table,
                                                     Map<String,?> cols)

insertAndFetchNewId

public long insertAndFetchNewId(String table,
                                Map<String,?> cols)

insertAndFetchNewIdOrThrowUniqueConstraintViolation

public long insertAndFetchNewIdOrThrowUniqueConstraintViolation(String table,
                                                                Map<String,?> cols)
                                                         throws DbTransaction.UniqueConstraintViolation

Throws:

DbTransaction.UniqueConstraintViolation

update

public void update(String table,
                   Map<String,?> cols,
                   String where,
                   Object... whereParams)

updateOrThrowUniqueConstraintViolation

public void updateOrThrowUniqueConstraintViolation(String table,
                                                   Map<String,?> cols,
                                                   String where,
                                                   Object... whereParams)
                                            throws DbTransaction.UniqueConstraintViolation

Throws:

DbTransaction.UniqueConstraintViolation

updateIgnoringUniqueConstraintViolations

public void updateIgnoringUniqueConstraintViolations(String table,
                                                     Map<String,?> cols,
                                                     String where,
                                                     Object... whereParams)

insertOrUpdate

public void insertOrUpdate(String table,
                           Map<String,?> colsToUpdate,
                           Map<String,?> colsToInsert,
                           String... primaryKeyColumns)

Inserts (colsToInsert + colsToUpdate) and, if that fails because the row already exists, updates (colsToUpdate) where (primaryKeyColumns out of colsToInsert).

See Also:

"Just-in-time" inserting rows into a database (Databases & Life)

rollback

public void rollback()

commit

public void commit()

rollbackIfConnectionStillOpen

public void rollbackIfConnectionStillOpen()

getFromDual

public String getFromDual()

getSchemaQuote

public String getSchemaQuote()

appendIn

public <V> void appendIn(Appendable sql,
                         List<? super V> sqlParams,
                         String field,
                         Collection<? extends V> values)

Writes "foo IN (?,?,?)"