Caching JDBC Statements

Thread view:

Christopher Dean - 12 Dec 2003 16:44 GMT

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...

Reply to this Message

Joe Weinstein - 12 Dec 2003 17:04 GMT

> 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

Reply to this Message

NOBODY - 15 Dec 2003 00:31 GMT

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?

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

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)?

>> Anybody ever run into any problems caching a statement along with a
>> connection. Since I use one connection per thread and one statement
[quoted text clipped - 16 lines]
>
> Joe Weinstein at BEA

Reply to this Message

Joe Weinstein - 15 Dec 2003 04:05 GMT

> Hi Joe,
>
[quoted text clipped - 3 lines]
> 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
costs.
Joe Weinstein at BEA

>>>Anybody ever run into any problems caching a statement along with a
>>>connection. Since I use one connection per thread and one statement
[quoted text clipped - 16 lines]
>>
>>Joe Weinstein at BEA

Reply to this Message

Caching JDBC Statements

Free Magazines