Java – c3p0 prepared declarations are closed for no obvious reason

I'm using c3p0 I created a collection as follows,

cpds = new ComboPooledDataSource();
cpds.setJdbcUrl(...);
/* connection setup */
spds.setMaxStatements(200);

I have an object and prepare several prepared statements for initialization To do this, I get the connection from pooleddatasource (con = getconnection()), and then prepare a statement (for example, Preparedstatement stmt = con. Preparedstate (/ * SQL * /)) The prepared statement is stored in the object as a private variable, and the current connection is closed at the end of initialization (con. Close()) The prepared statement is used for the method of the object

This works well for pre - prepared statements that update the database 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)

Is there anything wrong with my use of c3p0?

Thank you in advance!

Editor: obviously, part of my problem is based on my lack of understanding As pointed out in the clear answer, Preparedstatement belongs to a connection. Whenever the connection is closed, the associated statement should also be closed But if this is the case, I don't understand what the statement cache using c3p0 is

Solution

You should get the same exception call executeupdate() JDBC connection and statement pooling are designed to be transparent: the same API used for non pooled data sources should also be used for pooled versions Performance will vary greatly, but the code should be semantically interchangeable

In a non pooled environment, the reason why your method fails should be obvious: a declaration, ready or otherwise, is a connected child that cannot run without it You want the connection to exist in the pool even if it is "closed" in the collection environment, so hey, these statements may be good But this is a very bad idea (and if your attempt to update after the parent connection has been closed () is really successful, then it will be an error, a bad one.) Once the connection is "closed", it goes back to the swimming pool, but not forever Other customers will check it and start trading that should not be interrupted by stale statements Eventually, connections will expire from the pool So what happens to the preparedstatements you keep?

The c3p0 pool statement is transparent, which means that you should use exactly the same API instead of a pool Call preparestatement (...) on the connection every time If you have enabled statement pooling in c3p0 (just like you), the internal c3p0 will check whether the statement is ready. If so, it will quietly use the cached version instead of forwarding the request to DBMS

I hope it helps!