Commit and Rollback with Dropwizard DB, JDBI, and Transactional mixin

[Luke Lappin]

Hello all,


I was trying to use Dropwizard Database to give me a few onDemand DAO
objects which extend Transactional and GetHandle. I was going to use
these objects to do multiple inserts and updates which I need to all
complete to different tables or rollback. This works fine for reads.
However when I try to call commit on my DAO without explicitly getting
the Handle and setting the connection autocommit to false I am getting
an error which says I can commit when autocommit is set to true. Any
ideas what I am doing wrong? Here is some working and non working
code:


WORKING:

Handle h = db.open();
try{
h.getConnection().setAutoCommit(false);
}catch(SQLException se){
LOG.error(se.getMessage());
}

final TransactionalMemmeeDAO memmeeDao =
h.attach(TransactionalMemmeeDAO.class);
final TransactionalAttachmentDAO attachmentDao =
h.attach(TransactionalAttachmentDAO.class);

memmeeDao.insert(1);
memmeeDao.commit();



NOT WORKING:

final TransactionalMemmeeDAO memmeeDao =
h.attach(TransactionalMemmeeDAO.class);
try{
memmeeDAO.getHandle().getConnection().setAutoCommit(false);
}catch(SQLException se){
LOG.error(se.getMessage());
}

memmeeDao.insert(1);
memmeeDao.commit();

The error I get is:
Caused by: ! java.sql.SQLException: Can't call commit when
autocommit=true
! at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:930)
! at com.mysql.jdbc.ConnectionImpl.commit(ConnectionImpl.java:1535)
! at
org.apache.tomcat.dbcp.dbcp.DelegatingConnection.commit(DelegatingConnection.java:
334)
! at org.apache.tomcat.dbcp.dbcp.PoolingDataSource
$PoolGuardConnectionWrapper.commit(PoolingDataSource.java:211)
! at
org.skife.jdbi.v2.tweak.transactions.LocalTransactionHandler.commit(LocalTransactionHandler.java:
62)

[Christopher Currie]

In the 'non working' example, you don't appear to have a call to 'begin' on your DAO, which is required to start a transaction and disable autocommit mode on MySQL.

I really strongly recommend using the 'withTransaction' API, to let JDBI handle the low level begin/commit details automatically for you, unless you have a use case that prevents it.

[Luke Lappin]

Do you mean the InTransaction example on the link you gave? It seems in that type of transaction I can only use one DAO and I need multiple. Do you have any sample code? I also did try to use the keyword begin and it did not seem to change this behavior. Thanks.

[Luke Lappin]

The link I meant was:

http://skife.org/jdbi/java/library/sql/2011/03/16/jdbi-sql-objects.html

I suppose I could put all my transaction based inserts and updates
into one DAO if necessary.

On Jun 8, 7:58 am, Luke Lappin <lukelap...@gmail.com> wrote:
> Do you mean the InTransaction example on the link you gave? It seems in that type of transaction I can only use one DAO and I need multiple. Do you have any sample code? I also did try to use the keyword begin and it did not seem to change this behavior. Thanks.
>

> On Jun 8, 2012, at 2:23 AM, Christopher Currie <christop...@currie.com> wrote:
>
>
>
>
>
>
>
> > In the 'non working' example, you don't appear to have a call to 'begin' on your DAO, which is required to start a transaction and disable autocommit mode on MySQL.
>
> > I really strongly recommend using the 'withTransaction' API, to let JDBI handle the low level begin/commit details automatically for you, unless you have a use case that prevents it.
>

[Christopher Currie]

On Fri, Jun 8, 2012 at 4:58 AM, Luke Lappin <lukel...@gmail.com> wrote:

Do you mean the InTransaction example on the link you gave?

Yes, sorry for the confusion.

 

It seems in that type of transaction I can only use one DAO and I need multiple.

Then you should use the inTransaction method on your DBI instance instead, and create your DAOs using Handle.attach from within your TransactionCallback's inTransaction method. You should still be able to avoid explicit begin/commit in many cases.

 

Do you have any sample code? I also did try to use the keyword begin and it did not seem to change this behavior. Thanks.

Sample code abounds. Look at the tests in the JDBI source, I've found them super useful for learning the best practices.

[Luke Lappin]

I got it working quite well.  Much cleaner than the commit and rollback.  Thanks for the help!