I have seen the best practices for the connection pooling as below.
Connection conn = null;
Statement stmt = null; // Or PreparedStatement if needed
ResultSet rs = null;
try {
conn = ... get connection from connection pool ...
stmt = conn.createStatement("select ...");
rs = stmt.executeQuery();
... iterate through the result set ...
rs.close();
rs = null;
stmt.close();
stmt = null;
conn.close(); // Return to connection pool
conn = null; // Make sure we don't close it twice
} catch (SQLException e) {
... deal with errors ...
} *finally {* // Always make sure result sets and statements are closed,
// and the connection is returned to the pool
if (rs != null) {
try { rs.close(); } catch (SQLException e) { ; }
rs = null;
}
if (stmt != null) {
try { stmt.close(); } catch (SQLException e) { ; }
stmt = null;
}
if (conn != null) {
try { conn.close(); } catch (SQLException e) { ; }
conn = null;
}
}
However, we are closing the connection only in *finally* block not before
that. Is that the reason to throw the DBCP errors?
--
Regards,
Dhaval
On Wed, May 11, 2011 at 12:44 PM, Dhaval Jaiswal <[email protected]>wrote:
>
> Yeah, we have some of the SQL statements which are running more than 1
> minutes. However, for small queries it's also throwing errors DBCP.
>
>
> --
> Regards,
> Dhaval
>
>
> On Fri, May 6, 2011 at 4:59 PM, Martin Gainty <[email protected]> wrote:
>
>> Id the execution delivers results in under a minute then adhere to Mr
>> Woods advice
>> If over a minute the other consideration is you might have a terribly
>> long-executing SQL statement that needs to be tuned.
>>
>> if you can display your SQL statement and the relevant schemata we could
>> suggest more expeditious means to acquire your data
>>
>> Martin
>> ______________________________________________
>> Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
>>
>> Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
>> Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte
>> Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht
>> dient lediglich dem Austausch von Informationen und entfaltet keine
>> rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
>> E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
>>
>> Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
>> destinataire prévu, nous te demandons avec bonté que pour satisfaire
>> informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie
>> de ceci est interdite. Ce message sert à l'information seulement et n'aura
>> pas n'importe quel effet légalement obligatoire. Étant donné que les email
>> peuvent facilement être sujets à la manipulation, nous ne pouvons accepter
>> aucune responsabilité pour le contenu fourni.
>>
>>
>>
>>
>>
>>
>> > Subject: Re: Fwd: DBCP error
>> > From: [email protected]
>> > To: [email protected]
>> > CC: [email protected]; [email protected]
>> > Date: Fri, 6 May 2011 11:35:30 +0100
>>
>> >
>> > On Fri, 2011-05-06 at 11:12 +0100, Dhaval Jaiswal wrote:
>> > > Caused by: java.net.SocketException: Socket closed
>> >
>> > I'd suggest you look at server side timeout and maximum connection
>> > settings in
>> > http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html I'd
>> > suspect wait_timeout is the setting you're interested in. You might also
>> > want to check the maximum TCP session/idle timeouts on firewalls,
>> > routers and other network devices between the failing system and its
>> > MySQL server.
>> >
>> > Hope that helps,
>> > Nigel
>> >
>> >
>> > --
>> > MySQL General Mailing List
>> > For list archives: http://lists.mysql.com/mysql
>> > To unsubscribe: http://lists.mysql.com/[email protected]
>> >
>>
>
>
>
> --
> Regards,
> Dhaval Jaiswal
>
>
>
--
Regards,
Dhaval Jaiswal
