ResultSet returned from Statement.getGeneratedKeys has non-delegating Connection
--------------------------------------------------------------------------------
Key: DBCP-247
URL: https://issues.apache.org/jira/browse/DBCP-247
Project: Commons Dbcp
Issue Type: Bug
Environment: Ubuntu Linux 7.10, JDK 1.5, Tomcat 5.5, MySQL 5.0.45
Reporter: Shlomo Swidler
Priority: Minor
We have a library that wraps a ResultSet and, when the client code calls
ResultSet.close(), our library also closes the underlying Statement and
Connection. This is very useful because it allows us to abstract out from the
client code whether the Connection needs to be closed when you're done with the
ResultSet (as when using DBCP) or not (as when running standalone, with classic
MySQL JDBC driver connections).
Here's a snippet of the library code:
public class ResultSetWrapper {
private ResultSet rs;
public ResultSetWrapper(ResultSet rs) {
this.rs = rs;
}
public boolean next() throws SQLException {
return rs.next();
}
public int getInt(int fieldNum) throws SQLException {
return rs.getInt(fieldNum);
}
// ... etc methods delegating to the underlying ResultSet
public void close() throws SQLException {
Statement st = rs.getStatement();
Connection conn = st.getConnection();
try {
rs.close();
} finally {
try {
st.close();
} finally {
conn.close(); // Here is where the problem lies
}
}
}
}
Now, in order to test our code we are running with maxActive=1 and maxIdle=1,
so we never actually use more than one JDBC connection.
This works great when the ResultSetWrapper wraps a plain-old ResultSet returned
from Statement.executeQuery().
But when I wrap a ResultSet returned from Statement.getGeneratedKeys(), then
the underlying JDBC connection is closed (at the "Here is where the problem
lies" comment in ResultSetWrapper), not the DBCP DelegatingConnection. Here is
an example:
example database table:
CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50)
NOT NULL);
example test case:
public class Test {
private DataSource dataSource = null;
public Test() {
// initialize the DataSource
// ...
}
public ResultSetWrapper wrapGeneratedKeysQuery() throws SQLException {
Connection conn = dataSource.getConnection();
Statement st = conn.createStatement();
st.execute("INSERT INTO test (name) VALUES ('foo')");
ResultSet rs = st.getGeneratedKeys();
return new ResultSetWrapper(rs);
}
public ResultSetWrapper wrapPlainQuery() throws SQLException {
Connection conn = dataSource.getConnection(); // Here is where we run out
of connections in the pool
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT id FROM test LIMIT 1");
ResultSetWrapper rsw = new ResultSetWrapper(rs);
return rsw;
}
public static void test() throws Exception {
Test t = new Test();
ResultSetWrapper rsw = t.wrapGeneratedKeysQuery();
rsw.next();
rsw.getInt(1);
rsw.close(); // This closes the underlying JDBC connection
ResultSetWrapper rsw2 = t.wrapPlainQuery(); // throws an exception: DBCP
connection pool is exhausted
rsw2.next();
rsw2.getInt(1);
rsw2.close();
}
}
The result: the DBCP connection is never returned to the pool. We run out of
connections. wrapPlainQuery's getConnection fails because the pool (of 1
connection) has been exhausted. The statement rsw.close() closed the underlying
JDBC connection and didn't return the DBCP connection to the pool.
The workaround is to always carry around within the ResultSetWrapper the
"original" Connection upon which the query was executed. But this should not be
necessary.
Sorry, but I can't tell what version of DBCP I'm running. It's the one included
in the Tomcat 5.5 bundle I'm running.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
