I'm using c3p0. I set up a pooled as follows,
cpds = new ComboPooledDataSource();
cpds.setJdbcUrl(...);
/* connection setup */
spds.setMaxStatements(200);
I have an object that prepares several prepared statements on initialization. In order to do that, I grab a connection (con = getConnection()) from the PooledDataSource and then prepare a statement (e.g., PreparedStatement stmt = con.preparedStatemet(/*sql*/)). The prepared statements are stored as private variables in the object and the current connection is closed at the end of initialization (con.close()). The prepared statements are used in methods of the object.
For prepared statements that update the database, this works just fine. However, when I call a method that uses a prepared statement (stmt.executeQuery()) to query the database, I get the following SQLException
java.sql.SQLException: You can't operate on a closed Statement!!!
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:127)
Did I get something wrong concerning the usage of c3p0?
Many thanks in advance!
Edit: Obviously, my question is partly based on my lack of understanding. As was pointed out in the definite answer, a PreparedStatement belongs to a connection and whenever the connection is closed, the associated statements should be closed, as well. But if that is the case, I don't understand what the use of c3p0's statement cache is.
you should get the same Exception calling executeUpdate(). JDBC Connection and Statement pooling is designed to be transparent: the same API that works for unpooled DataSources should be used for pooled versions too. There will be a dramatic difference in performance, but the code should be semantically interchangeable.
in an unpooled environment, it should be obvious why your approach fails: a Statement, prepared or otherwise, is a child of a Connection, without which it can't function. you are hoping that in the pooled environment, even though the Connection has been "closed", it should still exist in the pool, so hey, those Statements might be good. but that's a very bad idea (and if your attempts to do updates really are succeeding after the parent Connection has been close()ed, again, that'd be a bug, a bad one.) once a Connection has been "closed" it goes back in the pool, but not forever. other clients will check it out, and start performing transaction work that shouldn't be interrupted by your stale Statements. eventually Connections will be expired out of the pool. what should happened to your retained PreparedStatements then?
c3p0 pools Statements transparently, meaning you should use exactly the same API you would have used with no pooling. Call prepareStatement(...) on your Connection, every time. if you've enabled Statement pooling in c3p0 (as you have), then internally c3p0 will check to see whether the Statement has already been prepared, and if so it will quietly use the cached version rather than forwarding the request to the dbms.
i hope this helps!