Re: Is it thread-safe to pool the PreparedStatement?

I would say the answer is no.  There's nothing in the spec that requires
that a JDBC connection be thread-safe.  Doing what you suggest would
require the JDBC connection to be thread-safe.

You can cache prepared statements, but since statements are tied to a
connection, your key must be (connection, sqlString) rather than just
sqlString.  Many JDBC drivers do this for you already.

More importantly, do you have evidence that JDBC calls (as opposed to
time waiting for the database) are actually the source of any
performance problem, or is this just optimizing for its own sake?

Linus Kamb wrote:
> Is it legitimate and/or appropriate to share a JDBC Connection between
> threads and have each thread call connection.prepareStatement() ?
>
> It seems like that is the way one should do it, but I have read
> conflicting posts.  I recall (though I may be confused) reading that
> connections may cache prepared statements. (Not sure I want to say that
> lest I'm incorrect and add to the confusion...)
>
> Are there limits or scalability issues to such an approach?
>
> Linus
>
>
> Brian Maso wrote:
>> At first I thought this would violate JTA requirements -- but looking
>> at the JTA spec it seems that multithreaded access to a resource
>> manager is supported, so there's no problem there.
>>
>> The JDBC spec says that a PreparedStatement can only be involved in a
>> single database interaction at a time. This means that, while reading
>> the ResultSet response to a SELECT query put forth through a
>> particular PreparedStatement, you can't send another query through the
>> same PreparedStatement because the existing ResultSet will be closed
>> automatically. So your PreparedStatement pool needs to ensure
>> exclusive access to a single thread until the thread is done with the
>> statement, before returning the statement back to the pool. Sounds
>> like that kind of assurance might not be there.
>>
>> Brian Maso
>>
>> At 09:46 AM 8/20/2007, you wrote:
>>> I just join a project, this existing team member of this project
>>> concern performance a lot so their pool all PreparedStatements into a
>>> map (probably not a nice optimization, but I have no say about that)
>>> when the application start. And then reuse those preparedstatement
>>> again and again.
>>>
>>> I haven't get time to check all method call using those
>>> preparedstatement are synchronized properly. If that is not the case,
>>> is it thread-safe to pool the PreparedStatement?
>>>
>>> ===================================
>>> This list is hosted by DevelopMentor®  http://www.develop.com
>>>
>>> View archives and manage your subscription(s) at
>>> http://discuss.develop.com
>>
>> ===================================
>> This list is hosted by DevelopMentor®  http://www.develop.com
>>
>> View archives and manage your subscription(s) at
>> http://discuss.develop.com
>>
>
> ===================================
> This list is hosted by DevelopMentor®  http://www.develop.com
>
> View archives and manage your subscription(s) at http://discuss.develop.com

===================================
This list is hosted by DevelopMentor®  http://www.develop.com

View archives and manage your subscription(s) at http://discuss.develop.com