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.
• 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:

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

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.DbConnectionException Cannot connect to database
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
DbTransaction.DbServerProduct	product

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 (?,?,?)"
void	close()
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<Object> args)
void	execute(String sql, Object... args)
String	getFromDual()
static String	getSqlForLog(String sql, Object[] args)
void	insert(String table, Map<String,?> cols)
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).
static boolean	isForeignKeyConstraintViolation(String msg)
org.jooq.DSLContext	jooq()
static String	parseUniqueConstraintViolationOrNull(String msg)
DbTransaction.DbQueryResultSet	query(CharSequence sql, List<Object> args)
DbTransaction.DbQueryResultSet	query(String sql, Object... args)
void	rollback()
void	rollbackIfConnectionStillOpen()
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

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

Field Detail

product

public final DbTransaction.DbServerProduct product

Constructor Detail

DbTransaction

public DbTransaction(String jdbcUrl)
              throws DbTransaction.DbConnectionException

Throws:

DbTransaction.DbConnectionException

Method Detail

close

public void close()

Specified by:

close in interface AutoCloseable

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)

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(() -gt; { 
    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<Object> 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)

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<Object> args)

insert

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

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()

appendIn

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

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