Re: Spring JDBC and the PostgreSQL JDBC driver

[Matthew Chambers <mchambers@xxxxxxxxxxxx>]

On 04/04/14 05:39, John R Pierce wrote:

On 4/3/2014 9:10 AM, Miller, Michael W wrote:

The issue I’m running into is the interaction between the Spring Framework JDBC functionality and the PostgreSQL JDBC driver. The specific issue is that I’m using SimpleJdbcCall.execute() to call the database and getting back a Jdbc4Array. When I then try to do something like Jdbc4Array.getArray() I get a SQL error that can be tracked down to the Jdbc driver trying to use a connection object which has already been closed by the Spring Framework.

 

One of the benefits of the Spring Framework is that I don’t have to bother with the drudge work like creating\closing connections. The drawback seems to be that the JDBC driver is making assumptions like the connection is still open.

if that framework is opening/closing connections on every query, its broken from a performance perspective.   and if it has methods that return objects, that are then broken, THATS also broken behavior.

but yeah, this belongs on the jdbc list.

The framework itself (spring) has nothing to do with connections being open/closed, that would be whatever JDBC connection pool your using under the hood.  Spring doesn't ship with a production quality connection pool as far as I know.

I think what the OP meant to say was that with Spring your freed from the need to always check your open connection back into the pool.  Spring is using aspect oriented magic to wrap your entry point functions with some code that checks out a connection, starts a transaction (sets these values as thread local objects that the rest of the framework uses), and then has finally block that cleans it all up for you.  So all you do is mark functions that you want to open a transaction, and the type of transaction you want. (read only, serializable, etc)

This removes all the boilerplate associated with old style JDBC.  It also means you get great reuse of your SQL code since the transaction starts at the entry point.  Most of my SQL related code is just 1 liners using Springs JdbcTemplate class.  I've written some massive applications with this style.  My current postgres project sees about 4gbps of traffic during peak times and there is not an explicit begin/commit in the entire code base.

-Matt