How to use Vaadin SQLContainer when I already have a JDBC connection pool

Question

Vaadin 7 offers the SQLContainer implementation. The Book of Vaadin says to use either of its implementations of a JDBC connection pool. But I already am using the Tomcat JDBC Connection Pool implementation. Having one pool that draws from another pool seems like a bad thing.

To continue using the Tomcat pool, I implemented the com.vaadin.data.util.sqlcontainer.connection.JDBCConnectionPool interface. That interface requires three methods:

• reserveConnection
  I return a connection drawn from the Tomcat pool.
• releaseConnection
  I do nothing. I tried calling close on the connection, but that actually closed the connection rather than returning it to the Tomcat pool. Apparently the SQLContainer already called close once and my second call to close actually closes down the connection. I was getting runtime errors saying the connection was not open.
• destroy
  I do nothing. Supposedly this method is just some workaround for some issue with Postgres (which I'm using), but seems irrelevant given that I am actually using the Tomcat pool.

➜ Is implementing that interface the correct approach?

➜ If implementing that interface is the way to go, did I do so properly? Any other issues I should address?

My Tomcat pool is available via JNDI, so I'm not sure if I should be using the Vaadin class J2EEConnectionPool.

Answer 1

J2EEConnectionPool Is Not A Pool (a misnomer)

Having looked at the source code, it appears the J2EEConnectionPool class is misnamed. It is not an implementation of a pool. It merely draws on a javax.sql.DataSource object (obtained via JNDI if not provided to constructor) to get a java.sql.Connection object.

The "Pool" part of the name must come from an assumption that the DataSource is backed by a connection pool.

Yes, Use J2EEConnectionPool

So, if a real connection pool is in use such as the Tomcat JDBC Connection Pool available via DataSource or JNDI, pass that DataSource or JNDI info to an instance of J2EEConnectionPool for use with the Vaadin SQLContainer.

Forum Thread

See this discussion, “Any chance of bug fix for TableQuery+SQLContainer and Connection pools?”, on the Vaadin Forums.

Bug Ticket

See Ticket # 12370, “SQLContainer does not work with tomcat/BoneCP connection pool”, in the Vaadin issue tracker.

Answer 2

You should in fact be able to use J2EEConnectionPool, exactly as you described in your own answer above. I have used successfully used J2EEConnectionPool with FreeformQuery so I know this works. Unfortunately there is apparently a bug in Vaadin's TableQuery implementation which caused the "connection has been closed" error you saw. See: http://dev.vaadin.com/ticket/12370

The ticket proposes a code change, but in my case I simply replaced the offending TableQuery with a FreeformQuery.