Re: Caching JDBC Statements

From: Joe Weinstein (
Date: 12/15/03

Date: Sun, 14 Dec 2003 20:05:26 -0800

NOBODY wrote:

> Hi Joe,
> I was wondering many little things...
> 1-Can I create many statement/preparedstatement all mixed, all opened and
> with different SQL, simultaneously, on the same unique connection (per
> thread of course) without closing any of these statement?

Yes, subject perhaps to DBMS limits. For instance, in Oracle an open
statement or result set requires a DBMS-side cursor, of which there is
a configurable limit allowed for a single connection. Some other DBMSes
have no such limit.

> 2-if so, for how long these statement are valid? as long as the
> connection is?

They are valid for as long as the connection is open, or as long as
the objects to which their query plan (prepared statements) remain
unaltered. A statement referring to a table or procedure which is
altered by another connection, typically become defunct and must be
closed and recreated. Some other DBMSes require special settings to
allow a statement to be valid across transactions....

> 3-what is the cost on serverside when one's program keeps let's say, 20
> (pooled) connection, each connection having, let's say, 30 (cached)
> [prepared]statements each (i.e. 600 [prepared]statement total)?

Some DBMSes will incur no special burden, and others may incur memory
Joe Weinstein at BEA

> Joe Weinstein <> wrote in
>>Christopher Dean wrote:
>>>Anybody ever run into any problems caching a statement along with a
>>>connection. Since I use one connection per thread and one statement
>>>at time this seemed like a logical thing to do...
>>Caching statements, especially prepared/callable statements is a good
>>thing. Depending on how complicated your code is, make sure that if
>>there are multiple levels of the stack in a thread using the same
>>connection, that no two levels try to use the same statement at a
>>time. You can cache multiple instances of a statement for the same
>>SQL. Depending on how complicated your code is, you may want code to
>>reset any non-default statement state when it is returned to the
>>cache, so if one caller sets maxRows(1) etc, the next user doesn't
>>magically start getting only one row from his queries too. Another
>>gotcha is if any cached statements refer to tables or procedures that
>>get altered in the DBMS while your application is still using open
>>connections and statements. Any cached prepared statements referring
>>to altered objects will fail and must be closed and replaced. Those
>>hints should do for a start...
>>Joe Weinstein at BEA