Setting c3p0 configuration for long running query

Question

I am using Hibernate version 3.3.1 and jtds 1.2.2 as JDBC driver and c3p0 version 0.9.1.2 for connection pooling to connect SQL Server.

My query takes about 12 seconds to run. When i run the query, I get the following exception

ERROR [main] (JDBCExceptionReporter.java:101) - Invalid state, the Connection object is closed. org.hibernate.exception.GenericJDBCException: could not inspect JDBC autocommit mode at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126) at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52) at org.hibernate.jdbc.JDBCContext.afterNontransactionalQuery(JDBCContext.java:275) at org.hibernate.impl.SessionImpl.afterOperation(SessionImpl.java:444) at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1728) at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165) at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)

If I modify the query to return a small set of data, I do not get exception. It seems there is some configuration problem.

In my hibernate.properties file, I have the following configuration values

hibernate.format_sql=true
hibernate.dialect=org.hibernate.dialect.SQLServerDialect
hibernate.optimistic-lock=true
hibernate.connection.autocommit=true
hibernate.show_sql=false
hibernate.generate_statistics=false
c3p0.acquire_increment=1
c3p0.idle_test_period=1000
c3p0.max_size=10     
c3p0.max_statements=0     
c3p0.min_size=5     
c3p0.timeout=800

Can you please suggest what parameters needs to be set to run a long running query?

Thank you

Steve Waldman Steve Waldman · Accepted Answer · 2014-03-21T00:11:56

c3p0 requires no special setup for long-running queries, although there are some settings (in particular unreturnedConnectionTimeout) that could interfere with long-running queries if set.

i'd verify that unreturnedConnectionTimeout is not set. (there are lots of places c3p0 configuration might be set outside of your hibernate.properties file.) c3p0 dumps its config to log at INFO on pool initialization. look for the value of unreturnedConnectionTimeout. it should be 0.

if it is 0, there is nothing at the c3p0 level that should interfere with your long-duration query.

Setting c3p0 configuration for long running query

1 Answers