commons-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Shlomo Swidler (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DBCP-247) ResultSet returned from Statement.getGeneratedKeys has non-delegating Connection
Date Mon, 19 Nov 2007 17:52:43 GMT

     [ https://issues.apache.org/jira/browse/DBCP-247?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Shlomo Swidler updated DBCP-247:
--------------------------------

    Affects Version/s: 1.2.1

I think, based on googling, that the affected version is 1.2.1. Whatever version of DBCP shipped
with Tomcat 5.5, that's the one.

> 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
>    Affects Versions: 1.2.1
>         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.


Mime
View raw message