How to use DBCP with JNDI ?

[Satyajeet Kadam]

Hi i am new to struts, I want to use JNDI with DBCP.Is it Possiable?
For DBCP i have use Struts-config
Q1) Where to configure for JNDI?
Q2) What are advantages and disadvantages of using DBCP?
Q3) Which is best approach either to write ConnectionPool classes
or use DBCP?( i have mentioned code below)

    
 
<data-sources>
<data-source type="org.apache.tomcat.dbcp.dbcp.BasicDataSource">
    <set-property
      property="driverClassName"
      value="com.mysql.jdbc.Driver" />
    <set-property
      property="url"
      value="jdbc:mysql://localhost:3306/test?autoReconnect=true" />
    <set-property
      property="username"
      value="root" />
    <set-property
      property="password"
      value="" />
    <set-property
      property="maxActive"
      value="10" />
    <set-property
      property="maxWait"
      value="5000" />
    <set-property
      property="defaultAutoCommit"
      value="false" />
    <set-property
      property="defaultReadOnly"
      value="false" />
    <set-property
      property="validationQuery"
      value="SELECT COUNT(*) FROM test" />
</data-source>
</data-sources>

  
//ConnectionPool.java
 
package connectionbean;
 
import java.sql.*;
import java.util.Vector;
 
public class ConnectionPool
{
/**
* Driver String which holds driver name which is to be used 
* for connection Database server
*/
private String driver=null;

/**
* url string which holds url of Database
*/
private String url=null;

/**
*  size no.of connection in Pool..
*/
private int size=0;

/**
* clinet is no.of  clients connected to Connection Pool...
*/
private static int client=0;

/**
* username is userid to log into Database server...
*/
private String username="";

/*
* password is to log into Database server...
*/
private String password="";
 
/**
* pool is vertor instance whcih holds all connection 
* free as well as busy conntion.
*/
private Vector pool=null;
 

/**
* ConnectionPool(String url,String driver,username,password,size) 
*  is constructor which calls loadDriver()method... 
*@param: url 
*@param: driver
*@param: username
*@param: password
*@param: size
*/
public ConnectionPool(String url,String driver,String username,String password,int size)
 {
this.url=url;
this.driver=driver;
this.username=username;
this.password=password;
this.size=size;
loadDriver();
initializePool(); 
 }
 
/**
* loadDrier() mehtod 
*/
private void loadDriver()
{
  try{
  Class.forName(driver);
 }catch(Exception er){System.out.println("Error in Driver Loading"+er.getMessage());}
}
 

/**
* ConnectionPool instnace which holds connections. 
*/
public static ConnectionPool ref;
 
/**
* This method calls creates Connection Pool 
* this method is called from jspinit() method of JSP pages....
*/
static synchronized public ConnectionPool getInstance()
  {   //order of args passed... url,driver,username,password,size
   //if(ref==null)
//ref=new ConnectionPool("jdbc:mysql://mysql.webappcabaret.com/silicon","org.gjt.mm.mysql.Driver","silicon","admin",5);
//if(ref==null)
//ref=new ConnectionPool("jdbc:microsoft:sqlserver://SII_PRAFUL:1433;Databasename=si_silicon","com.microsoft.jdbc.sqlserver.SQLServerDriver","sa","sa",5);
 
if(ref==null)
ref=new ConnectionPool("jdbc:mysql://mac3:3306/silicon","org.gjt.mm.mysql.Driver","root","",10);

client++;
return ref;
  }
 
/**
*  initializePool() method retrives connection from Connection pool.
*/
    public void initializePool() 
{
try
  {
   for(int x=0;x<size;x++)
{
  Connection con=createConnection();
  if(con != null)
   {
    PooledConnection pcon=new PooledConnection(con);
    addConnection(pcon);
   }
    }
  }catch(Exception e)
{System.out.println("Error while connecting"+e.getMessage());}
 }

/**
*
*  if the value of the pool is null than of the initial size  vector will be creTED
*/
private void addConnection(PooledConnection value)
{
System.out.println("Add connection called");
if(pool==null)
   pool=new Vector(size);
     pool.addElement(value);
}
 
  /** 
  *getConnection(0 method returns the connection which is available in Pool.
  * searching for an existing connection
  *@return: Connection instance which is free in Connection Pool...
  */
  public synchronized Connection getConnection() throws Exception
 {
 PooledConnection pcon= null;
     //System.out.println("get connection is now called size= "+pool.size());
 
 //if(!pool.isEmpty())
  // {
 for(int x=0;x<pool.size();x++)
   {  pcon=(PooledConnection)pool.elementAt(x);
  if(pcon.inUse()==false)
    {
 pcon.setInUse(true);
 System.out.println("Connection assigned: "+(x+1));
 return pcon.getConnection();
 }
   }
       //this will create a new connection above the limit
   try{  Connection con=createConnection();
    pcon=new PooledConnection(con);
    pcon.setInUse(true);
   pool.addElement(pcon);
   }catch(Exception e)
   {System.out.println("Error while connecting"+e.getMessage());}
  return pcon.getConnection();
  // }
  //else
//{ 
//  initializePool();
//  return pcon.getConnection();
//}
  }

/**
* createConnection() method creates connection which 
* is called from getConnection() method ...
*/
private Connection createConnection() throws Exception
{Connection con=null;
con=DriverManager.getConnection(url,username,password);
//System.out.println("create Connection called");
return con;
}
 

/**
* releaseConnection() method make Connection free
*/
public synchronized void releaseConnection(Connection con)
{
for(int x=0; x<pool.size();x++)
  { //System.out.println("Connection releasing: Loop  "+(x+1));
PooledConnection pcon=(PooledConnection)pool.elementAt(x);
if(pcon.getConnection()==con)
  { //System.out.println("Connection releasing: "+(x+1));
   pcon.setInUse(false);
   break;
  }
  }
}
 
/**
* emptyPool() method removes connection 
*/
public synchronized void emptyPool()
   {
//System.out.println("now opool is getting emptied");
for(int x=0;x<pool.size();x++)
 {
  //System.err.println("Closing JDBC Connev=ctiuon  : :"+x);
  PooledConnection pcon= (PooledConnection)pool.elementAt(x);
  if(pcon.inUse()==false)
  pcon.close();
  else
{
 try{
  java.lang.Thread.sleep(3000);
  pcon.close();
}catch(InterruptedException ie)
{System.out.println(ie.getMessage());}
}
}
  }//emptyPool() closed...
 

/**
*Setter method for Driver, Url , Size username,password ....
*Getter method for Driver, Url , Size username,password ....
*/
public void setDriver(String value)
{
if (value !=null)
{
driver=value;
}
}

public String getDriver()
{
return driver;
}
 
public void setURL(String value)
{
if (value !=null)
{
url=value;
}
}

public String getURL()
{
return url;
}

public void setSize(int value)
{
if (value >1)
size=value;
}

public int getSize()
{
return size;
}
 

public void setUsername(String value)
{
if (value !=null)
{
username=value;
}
}

public String getUsername()
{
return username;
}

public void setPassword(String value)
{
if (value !=null)
{
password=value;
}
}

public String getPassword()
{
return password;
}
 
}// end of Class ConnectionPool.....
 
  

   // PooledConnection.java
 
package connectionbean;
 
import java.sql.*;
//sql package is imported..
 
/**
*PoooledCoonection class use for tolding Connection instance which 
*is stored in Connection Pool, This class if free then from Pool it 
*is given to requested use to establish connection to database.
*/
public class PooledConnection
{
/**
* connection is instance of Connection inerface of SQL package..
* initially is null..
*/
private Connection connection=null;
 
/**
* inuse is boolean field which stores status of 
* connection availability Free or not.
*/
private boolean inuse=false;
 
/**
* PooledConnection(Connection value) 
*@param value is instance of Connection interfaces of SQL Package..
* ...
*/
public PooledConnection(Connection value)
{
System.out.println("inside pooled pooled connection");
if(value!=null)
 connection=value;
}
//constructor closed....
 
/**
* getConnection() method return Connection instance 
* PooledConnection holding...
* @return:Connection instance
*/
public Connection getConnection()
 {
return connection;
   }
 
/**
* setInUse(boolean) method sets valus true/false 
* availability of PooledConnection instance.
* PooledConnection holding...
* @param: value is boolean true/false
*/
public void setInUse(boolean value)
{
inuse=value;
}
 
/**
* inUse() method return Availability
* availability of PooledConnection instance.
* PooledConnection holding...
* @return: value is boolean true/false
*/
public boolean inUse()
{
return inuse;
}
 
/**
  * close() method use for closing connection instace 
  *  hold by PooledConnetion...
*/
public void close()
{
try{
//System.out.println("closing the pooled connection");
connection.close();
}catch(SQLException e)
{
System.err.println(e.getMessage());
}
}
}// PooledConnection Closed...
 

[Merrill Higginson]

Q3) Which is best approach either to write ConnectionPool classes
or use DBCP?

Answer: Neither.

The Struts DBCP datasource configuration utility has been deprecated. It's no longer supported in Version 1.3.5 and above. I would strongly recommend that you not use it.

There's no need to "roll your own" connection pooling utility either. Any Application Server worth using has its own connection pooling configuration utility. They have put a lot of time and effort into making it work efficiently with that specific App server, so why not use it?

The best practice for JDBC DataSources in a J2EE application is to use your App server's configuration utility to set up a DataSource and store a reference to it in JNDI. In your application code you simply look up the reference in JNDI and use the retrieved DataSource to get a JDBC Connection.

[Milan Jagatiya]

Hi,
I agree with Merrill. you should use app server based connection pooling. because it works efficiently than our code. for more details check out

http://tomcat.apache.org/tomcat-5.0-doc/jndi-datasource-examples-howto.html

milan.