Hello.
Maybe you can use DbUtils class from dbutils project to code less in the finally
clause; methods closeQuietly and close makes this tedious stuff easier.
DataSource ds = ...; // Acquire a reference from JNDI or wherever
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = ds.getConnection(); // Or whatever
stmt = conn.prepareStatement("...");
rs = stmt.executeQuery();
... process the results ...
} catch (SQLException e) {
... process the exception ...
} finally {
DbUtils.closeQuietly(rs);
DbUtils.closeQuietly(stmt);
DbUtils.closeQuietly(conn);
}
Even further, you can avoid ResultSet and Statement closing, using methods from
QueryRunner to execute or update a database. Look into documentation, it's
pretty useful.
I hope this help. Regards,
____________________________________________
Alfredo Ledezma Meléndez.
Gerencia de Sistemas CRM
Consultor Externo de Sistemas de Atención a Clientes
RadioMovil DIPSA, S. A. de C. V.
Ejército Nacional No. 488, Col. Anahuac, C.P. 11570
México D.F.
-----Original Message-----
From: Craig McClanahan [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 14, 2005 5:10 PM
To: Jakarta Commons Users List; [EMAIL PROTECTED]
Subject: Re: [DBCP] Anther lame "pool exhausted exception" question
The key to returning connections to the connection pool is to call
close() on the *connection* itself. The most common way people get
themselves in trouble is to skip this somehow (perhaps because an
exception is thrown).
To avoid that sort of problem, I tend to use an idiom like this for
JDBC access code:
DataSource ds = ...; // Acquire a reference from JNDI or wherever
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = ds.getConnection(); // Or whatever
stmt = conn.prepareStatement("...");
rs = stmt.executeQuery();
... process the results ...
} catch (SQLException e) {
... process the exception ...
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
... record or ignore ...
}
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
... record or ignore ...
}
rs = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
... record or ignore ...
}
conn = null;
}
}
Courtesy of the "finally" logic, I'm guaranteed to always execute my
cleanup code, no matter where an exception might get thrown.
You'll note that it can be pretty tedious to do all this stuff with
raw JDBC. You might also want to look at using one of the SQL
frameworks that encapsulate this sort of stuff, but still give you the
guarantee that the connection will ultimately be returned to the pool.
Craig
On 7/14/05, Brian Cook <[EMAIL PROTECTED]> wrote:
>
> I have several apps written that use org.apache.commons.dbcp. And I am
> getting the org.apache.commons.dbcp.SQLNestedException: Cannot get a
> connection, pool exhausted exception.
>
> When I run netstat sure enough it shows about 60 - 100 "ESTABLISHED"
> connections open at any given time. Looking deeper it looks like new
> connections are created most but not each time the Java Bean the uses
> the data base is called. So it seems pretty clear that I am failing to
> close all of the connections in my app. My problem is that I can not
> find where that is.
>
> My understanding is that as long as I close every ResultSet, and each
> Connection object I should have my bases covered. I have gone back
> though the app repeatedly and verified that all the ResultSets,
> Connections, and BasicDataSource objects are all being closed.
>
> Due to an internal political problem I am not using JNDI for the
> connections. Instead I have a class file that is called when a
> connection is need. After the object is used a close() method is called
> and closes the Statement, Connection, ResultSet, BasicDataSource objects.
>
> So my questions are ..
>
> 1. Is there something else I should be doing to return the
> connections to the pool?
> 2. Does not using JNDI with org.apache.commons.dbcp cause problems
> with closing the connection pool some how?
>
>
> I have been though about 6 months of the archives for this list and did
> not find any hints, I went to look at the "examples" lnik at on the
> Commons.DBCP page but the link to
> http://cvs.apache.org/downviewcvs.html/jakarta-commons/dbcp/doc/ seems
> to be broken. Googling has only just returned the usall advice of make
> sure you are closing every connection.
>
> I am open to any ideas or thoughts to what I am failing to due here.
> Details and the code are included below.
>
>
> OS: Linux Red Hat 9.0
> Container: Tomcat 4.127
> DBCP: commons-dbcp-1.2.1.jar, commons-pool-1.2.jar
> JDK: 1.5
> Database: MySQL 4.1
> JDBC Driver: mysql-connector-java-3.1.7-bin.jar
>
>
>
> public class DBConector {
>
>
> <Omited Code/>
>
>
> public DBConector(String dataBaseName) {
>
> try{
> String DataBaseURL = "jdbc:mysql://"+DataBaseHost+"/"+dataBaseName+
> "?user="+DataBaseUser+"&password="+DataBasePassword;
>
> mysqlCon = getPooledConection(DataBaseURL);
> sqlStatement = mysqlCon.createStatement();
>
> }
> catch(Exception s) {
> System.err.println(new java.util.Date()+" Error throw by" +
> " DBConector()\n"+s);
> }
> }
>
>
> <Omited Code/>
>
>
> public ResultSet read(String SQL) {
>
> try{
> rs = sqlStatement.executeQuery( SQL );
> } // Close try
> catch(Exception s) {
> System.err.println(new java.util.Date()+" Error throw by an
> SQL " +
> "call in DBConector.read() class : \n"+s+"\n"+s.getMessage()+
> "\nSQL Queary : "+SQL+"\n");
> } // Close catch
>
> return(DataFromDB);
> } // Close the read method
>
>
>
> <Omited Code/>
>
>
> protected static Connection getPooledConection(String connectURI) {
> ds = new BasicDataSource();
> ds.setDriverClassName(DBDriver);
> ds.setUsername(DataBaseUser);
> ds.setPassword(DataBasePassword);
> ds.setUrl(connectURI);
> ds.setInitialSize(3);
> ds.setMaxActive(32);
> ds.setMaxIdle(8);
> ds.setMinIdle(3);
> Connection con = null;
>
> try {
> con = ds.getConnection();
> }
> catch(Exception a){System.out.println(new java.util.Date() +
> "PrintTimeDataBase6.getPooledConection() has thrown an
> exception.\n"+a+
> "\n"+ a.getMessage() );
> }
> return con;
> } // Close method
>
>
> <Omited Code/>
>
>
> public void close() {
> try { sqlStatement.close(); } catch(Exception a) { }
> try { mysqlCon.close(); } catch(Exception a) { }
> try { rs.close(); } catch(Exception a) { }
> try { ds.close(); } catch(Exception a) { }
> } // Close method
> } // Close the PrintTimeWebServices Class
>
> --
> Brian Cook
> Digital Services Analyst
> Print Time Inc.
> [EMAIL PROTECTED]
>
>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Este mensaje es exclusivamente para el uso de la persona o entidad a quien esta
dirigido; contiene informacion estrictamente confidencial y legalmente
protegida, cuya divulgacion es sancionada por la ley. Si el lector de este
mensaje no es a quien esta dirigido, ni se trata del empleado o agente
responsable de esta informacion, se le notifica por medio del presente, que su
reproduccion y distribucion, esta estrictamente prohibida. Si Usted recibio
este comunicado por error, favor de notificarlo inmediatamente al remitente y
destruir el mensaje. Todas las opiniones contenidas en este mail son propias
del autor del mensaje y no necesariamente coinciden con las de Radiomovil
Dipsa, S.A. de C.V. o alguna de sus empresas controladas, controladoras,
afiliadas y subsidiarias. Este mensaje intencionalmente no contiene acentos.
This message is for the sole use of the person or entity to whom it is being
sent. Therefore, it contains strictly confidential and legally protected
material whose disclosure is subject to penalty by law. If the person reading
this message is not the one to whom it is being sent and/or is not an employee
or the responsible agent for this information, this person is herein notified
that any unauthorized dissemination, distribution or copying of the materials
included in this facsimile is strictly prohibited. If you received this
document by mistake please notify immediately to the subscriber and destroy
the message. Any opinions contained in this e-mail are those of the author of
the message and do not necessarily coincide with those of Radiomovil Dipsa,
S.A. de C.V. or any of its control, controlled, affiliates and subsidiaries
companies. No part of this message or attachments may be used or reproduced in
any manner whatsoever.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
