Mailing-List: contact commons-user-help@jakarta.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Jakarta Commons Users List" <commons-user@jakarta.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=yahoo.com;
  h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
  b=x1dLZrorUUddSJ6g63GUbBNm28G5p2QQMWV0i4N76xxtjJmE0ThoWdZV+bczHq/di0uUl/yEjmiFQGo9VRtZN5GiEWvJrrUUrbpVaJG40QJsfyk/791eG3v0y7zAut1melg7DHjqMaPl1r4WQ37+Zit6LNCsG/IqmW4rYb+zMjc=
  ;
Message-ID: <20051105152702.16185.qmail@web50404.mail.yahoo.com>
Date: Sat, 5 Nov 2005 07:27:02 -0800 (PST)
From: David Graham <grahamdavid1980@yahoo.com>
Subject: Re: DBCP - not closing connections?
To: commons-user@jakarta.apache.org
In-Reply-To: <436B5797.5040403@arts.ac.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

MIke,
You are really doing this the hard way.  Since you are using JSP that
means you're app is running in a web container.  All containers support
configuring a DataSource in JNDI and looking it up from there.  Tomcat is
especially easy to setup.

Here are the instructions for Tomcat 5.5 (other versions are similar but
different):
http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html

Once you're using a DataSource from the container's JNDI directory you can
cleanup the JDBC code.  Querying the database from a JSP is bad form.  The
JSP should *only* be handling the display logic, not the query logic. 
Queries generally belong in a Java bean.

However, if you still want to query the database from your JSP, please use
the standard sql tags in the JSTL.  The Tomcat link above has an example
usage.  Also, here's a couple links to get you started.

Sun's JSTL Homepage:
http://java.sun.com/products/jsp/jstl/

Apache Taglib implementation of JSTL:
http://jakarta.apache.org/taglibs/


David


--- Mike Kelly <m.f.kelly@arts.ac.uk> wrote:

> Hello,
> I am using Commons DBCP to pool my database connections. Sometimes there
> 
> is just an hourglass cursor when trying to access my application - looks
> 
> like there are no free connections available.
> 
> Am I doing anything wrong in my code? Should I be closing the 
> datasource, for example? Please see below.
> 
> Class for retrieving datasource:
> 
> /////////////////////////////////
> package mypackage.name.here
> 
> import javax.sql.*;
> import java.io.File;
> import java.io.FileInputStream;
> import java.io.IOException;
> import java.util.*;
> import org.apache.commons.dbcp.*;
> import org.apache.commons.pool.*;
> import org.apache.commons.pool.impl.*;
> 
> public class DatabaseUtil {
> 
>      static PoolingDataSource dataSource = null;
>      public static final String DB_PROPERTIES =
> "myextension.properties";
> 
> 
>      //  Returns a DataSource that implements connection pooling
>      public static synchronized DataSource getDataSource(String 
> configDir) throws Exception {
> 		
> 	    if (dataSource == null){
> 	
> 	        Properties p = new Properties();
> 	
> 			try{
> 			File cfg = new File(configDir,DB_PROPERTIES);
> 			    p.load(new FileInputStream(cfg));
> 			} catch (IOException e) {	
> 				System.out.println(e);
> 			}
> 		    	  	
> 		    String type = p.getProperty("dbType");
> 		    String url = p.getProperty("url");
> 		    String ip = p.getProperty("dbIP");
> 		    String name = p.getProperty("dbName");
> 		    String port = p.getProperty("dbPort");
> 		    String sid = p.getProperty("dbSid");
> 		    String fullUrl = null;
> 		    if (type.trim().equals("ms")){
> 		    	// MSSQL
> 		    fullUrl = url+ip+"/"+name;
> 		    } else {
> 		    	// ORACLE
> 		    fullUrl = url+ip+":"+port+":"+sid;
> 		    }
> 		
> 		    BasicDataSource ds = new BasicDataSource();
> 		    ds.setUrl(fullUrl);
> 		    ds.setDriverClassName(p.getProperty("driver"));
> 		    ds.setUsername(p.getProperty("usr"));
> 		    ds.setPassword(EncDoc.decode(p.getProperty("pass")));
> 	
> 		    ds.setMaxActive(100);
> 		
> 		    // Create a PoolableDataSource
> 		    ObjectPool connectionPool = new GenericObjectPool(null);
> 		    ConnectionFactory connectionFactory = new 
> DataSourceConnectionFactory(ds);
> 		    PoolableObjectFactory poolableConnectionFactory = new 
> PoolableConnectionFactory(connectionFactory, connectionPool, null, null,
> 
> false, true);
> 		    dataSource = new PoolingDataSource(connectionPool);
> 	    }
> 
>      return dataSource;
>      }
> }
> //////////////////////////////
> 
> 
> Sample JSP page:
> 
> //////////////////////////////
> ...
> DataSource dataSource = DatabaseUtil.getDataSource(configDir);
> Connection myConnect = null;
> Statement myStatement = null;
> ResultSet myResults = null;
> 
> try{
> myConnect = dataSource.getConnection();
> myStatement = myConnect.createStatement();
> 
> ...
> 
> myQuery = "select count (*) as 'total' from admin where courseID = 
> '"+courseID+"'";
> 
> myStatement.executeQuery(myQuery);
> myResults = myStatement.getResultSet();
> while (myResults.next()) count = myResults.getInt("total");
> 
> ...
> 
> }catch (SQLException e) {
>      out.println("SQL Error: <br><br>"+e);
> } finally {
>      try { myResults.close(); } catch(Exception e) {}
>      try { myStatement.close(); } catch(Exception e) { }
>      try { myConnect.close(); } catch(Exception e) { }
> }
> ////////////////////////////////
> 
> 
> Thanks all.
> -- 
> 
> 
> Mike Kelly
> Multimedia Developer
> IT Research and Development Unit
> University of the Arts London
> 020 7514 6206
> 


Get Firefox!
http://www.mozilla.org/firefox/


__________________________________ 
Start your day with Yahoo! - Make it your home page! 
http://www.yahoo.com/r/hs

---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org