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

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.