Re: [SPAM] - Re: [SPAM] - Re: JDBC HighLoad - Found word(s) - Mailing list pgsql-jdbc

If you want to pool prepared statements, this can only be done on a PER
CONNECTION basis. That is, you are going to have to make each connection
have a pool of prepared statements so that when you make a call to
prepareStatement it checks the pool OF THAT SPECIFIC CONNECTION to see
if it's already in there, and so that close (on the prepared statement)
returns it to the pool.

Apache DBCP has an implementation of this, see the Commons DBCP project
at jakarta.apache.org.

Greetings,
Sebastiaan van Erk

Stéphane RIFF wrote:
> But with this usage i cannot reuse a preparedStatement.
> Is it possible to create the preparedStatement in the constructor and
> reused them
> for each update. So that each thread has his own preparedStatements because
> each thread do an update every second, i also think that preparedStatements
> was  good to use in this configuration but in your usage i have to
> prepare the statement each time i get the connection.
>
> Dave Cramer wrote:
>
>> Stephane,
>>
>> No.
>>
>> Here is the basic usage of the connection ( regardless of the
>> existance of the pool)
>>
>> get the connection
>> prepare the statement
>> execute the statement
>> do something with result
>> close statement
>> close connection ( this allows the connection to be re-used by another
>> request )
>>
>>
>> Having a pool does not change this pattern.
>>
>> All the pool does is cache connections so that they can be re-used by
>> multiple threads without going through the overhead of opening the
>> connection.
>> It also minimizes the number of connections required for an
>> application. For example if you have 1000 requests you might only need
>> 100 connections since they will share
>> the connections in the pool.
>>
>> Dave
>>
>> Stéphane RIFF wrote:
>>
>>> I thought that preparedStatement know what connection to use even if
>>> i return it to the pool
>>>
>>> Dave Cramer wrote:
>>>
>>>> Stephane,
>>>>
>>>> Yes, that is true, but where do you get the connection from on the
>>>> next iteration of the loop ? The constructor of SQLoader.... so the
>>>> next loop it is gone.
>>>>
>>>> Dave
>>>>
>>>> Stéphane RIFF wrote:
>>>>
>>>>> I thought that the m_conn.close() released the connection to the
>>>>> pool doesn't it ?
>>>>> If i close the connection at the end of the loop byt a
>>>>> SQLoader.close() this will
>>>>> make one connection for each thread. I want each thread ask a
>>>>> connection to the pool
>>>>> and released it after each update.
>>>>>
>>>>> Thanks for your time
>>>>> Bye
>>>>>
>>>>> Kris Jurka wrote:
>>>>>
>>>>>> On Fri, 4 Feb 2005, [ISO-8859-1] St�phane RIFF wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>> Hello,
>>>>>>> I implement like you said in the last post but now i get some
>>>>>>> errors like this :
>>>>>>>
>>>>>>> 2005-02-04 09:03:26,234 : [WARN] SQLoader -
>>>>>>> java.sql.SQLException:
>>>>>>> org.apache.commons.dbcp.DelegatingPreparedStatement is closed.
>>>>>>> I attach the two class i you want to see
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> Your main loop is written:
>>>>>>
>>>>>> SQLoader sl = new SQLoader();
>>>>>> for(int i=0;i<100;i++) {
>>>>>>    sl.saveTrame( ... );
>>>>>> }
>>>>>>
>>>>>> but the end of the saveTrame method you have:
>>>>>>
>>>>>> finally {
>>>>>>    try {
>>>>>>        m_conn.commit();
>>>>>>        m_conn.close();
>>>>>>
>>>>>> This closes the connection on the first iteration of the loop.
>>>>>> I'd suggest something like adding SQLoader.close() which gets
>>>>>> called at the end of the for loop.
>>>>>>
>>>>>> Kris Jurka
>>>>>>
>>>>>> ---------------------------(end of
>>>>>> broadcast)---------------------------
>>>>>> TIP 8: explain analyze is your friend
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>>
>>
>
>
>