hibernate + c3p0 strange behaviour

Alex-scat · **Joined:** Wed Nov 25, 2009 11:35 am **Posts:** 17

I feel i'm missing something important so could anyone advice why this happening?

I'm getting error while using c3p0 with hibernate:

Code:

Feb 5, 2010 1:53:23 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 17010, SQLState: null
Feb 5, 2010 1:53:23 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: Closed Resultset: next
Exception in thread "main" org.hibernate.exception.GenericJDBCException: could not advance using next()
        at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
        at org.hibernate.impl.ScrollableResultsImpl.next(ScrollableResultsImpl.java:104)
        at scc.ar.test.Tester.main(Tester.java:159)
Caused by: java.sql.SQLException: Closed Resultset: next
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
        at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1130)
        at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:212)
        at com.mchange.v2.c3p0.impl.NewProxyResultSet.next(NewProxyResultSet.java:2859)
        at org.hibernate.impl.ScrollableResultsImpl.next(ScrollableResultsImpl.java:99)

Code is:

Code:

      String str = "jdbc:oracle:thin:are_rq73/********@isd201.ISD.DP.UA:1521:dab";
      cfg.setProperty("hibernate.connection.url", str);
      cfg.setProperty("hibernate.show_sql", "true");
      cfg.setProperty("hibernate.c3p0.max_size","15");
      cfg.setProperty("hibernate.c3p0.min_size","0");
      cfg.setProperty("hibernate.c3p0.timeout","5000");
      cfg.setProperty("hibernate.c3p0.max_statements","100");
      cfg.setProperty("hibernate.c3p0.idle_test_period","300");
      cfg.setProperty("hibernate.c3p0.acquire_increment","1");
      cfg.configure();
      SessionFactory sessionFactory = cfg.buildSessionFactory();
      Session session = sessionFactory.openSession();
      org.hibernate.ScrollableResults r = session.createCriteria(Payor.class).scroll(ScrollMode.FORWARD_ONLY) ;
      session.get(Batch.class, Integer.valueOf(31066));
      r.next();

Exception occurs on "r.next();" line and will not occur if call session.beginTransaction() before.

It is not reproduced with default connection pool.

Froestel · **Posted:** Fri Feb 05, 2010 12:14 pm

Hi Alex-scat,

Alex-scat wrote:

...
will not occur if call session.beginTransaction() before.
...

So, why don't you call session.beginTransaction() before? You should enclose your DB access in session.beginTransaction() and session.commit() surrounded by a proper exception handling.

CU
Froestel

Alex-scat · **Joined:** Wed Nov 25, 2009 11:35 am **Posts:** 17

Thank you for reply, Froestel

I'd like to know why this not happening with default pool ? Also, problem itself looks strange. Can it be kind of bug ?

pb00067 · **Joined:** Tue Jun 16, 2009 3:36 am **Posts:** 990

The behavior of c3p0 is the correct one,
the default pool behaviors not correctly and should not be used for production use.

Since you:

- are not in transaction
- have not configured hibernate.connection.release_mode explicitely
(so with JDBCTransactionFactory it defaults to ConnectionReleaseMode.AFTER_TRANSACTION )
- have hibernate.connection.autocommit set to true

as soon

Code:

session.get(Batch.class, Integer.valueOf(31066));

is executed, hibernate closes the current connection,

Quote:

ConnectionManager.closeConnection() line: 463
ConnectionManager.aggressiveRelease() line: 429
ConnectionManager.afterTransaction() line: 316
JDBCContext.afterNontransactionalQuery(boolean) line: 268
SessionImpl.afterOperation(boolean) line: 587
SessionImpl.get(String, Serializable) line: 1002
SessionImpl.get(Class, Serializable) line: 990

thus c3p0 closes all regarding resources and puts the connection back to the pool.
In this way also the open cursor is closed and for this reason in the next line

Code:

r.next();

you get java.sql.SQLException: Closed Resultset:

It's not happening with default pool because probably
the default pool against jdbc-spec don't closes all resources bound to the connection
(therefore there' is also a warning that the default pool should not be used for production use).

N.B.: If you set hibernate.connection.release_mode=on_close then your app. will work,
but same as Froeste I suggest you to use transaction boundaries begin and commit/rollback.

Alex-scat · **Joined:** Wed Nov 25, 2009 11:35 am **Posts:** 17

Thanks a lot for explanation. I decided to use transaction scope.