connection pooling coding

I want to make my program to achieve connection pooling?

what are the steps i have to do that?
LVL 9
Manikandan ThiagarajanSenior consultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

fargoCommented:
i use commons dbcp and it is also available by default under tomcat
http://jakarta.apache.org/commons/dbcp/

here is a good article about the same
http://www.onjava.com/pub/a/onjava/2006/04/19/database-connection-pooling-with-tomcat.html

if u wish to see a simple connection pool, better have a look at core servlets chapter here
http://archive.coreservlets.com/Chapter18.html

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fargoCommented:
here is one link that may be helpful
take a look at the last post in this link
http://forum.java.sun.com/thread.jspa?threadID=614996&messageID=3420584
Manikandan ThiagarajanSenior consultantAuthor Commented:
mport java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

//
// Here are the dbcp-specific classes.
// Note that they are only used in the setupDriver
// method. In normal use, your classes interact
// only with the standard JDBC API
//
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;

//
// Here's a simple example of how to use the PoolingDriver.
// In this example, we'll construct the PoolingDriver manually,
// just to show how the pieces fit together, but you could also
// configure it using an external conifguration file in
// JOCL format (and eventually Digester).
//

//
// To compile this example, you'll want:
// * commons-pool.jar
// * commons-dbcp.jar
// in your classpath.
//
// To run this example, you'll want:
// * commons-collections.jar
// * commons-pool.jar
// * commons-dbcp.jar
// * the classes for your (underlying) JDBC driver
// in your classpath.
//
// Invoke the class using two arguments:
// * the connect string for your underlying JDBC driver
// * the query you'd like to execute
// You'll also want to ensure your underlying JDBC driver
// is registered. You can use the "jdbc.drivers"
// property to do this.
//
// For example:
// java -Djdbc.drivers=oracle.jdbc.driver.OracleDriver \
// -classpath commons-collections.jar:commons-pool.jar:commons-dbcp.jar:oracle-jdbc.jar:. \
// ManualPoolingDriverExample
// "jdbc:oracle:thin:scott/tiger@myhost:1521:mysid"
// "SELECT * FROM DUAL"
//
public class ManualPoolingDriverExample {

public static void main(String[] args) {
//
// First we load the underlying JDBC driver.
// You need this if you don't use the jdbc.drivers
// system property.
//
System.out.println("Loading underlying JDBC driver.");
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
System.out.println("Done.");

//
// Then we set up and register the PoolingDriver.
// Normally this would be handled auto-magically by
// an external configuration, but in this example we'll
// do it manually.
//
System.out.println("Setting up driver.");
try {
setupDriver(args[0]);
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("Done.");

//
// Now, we can use JDBC as we normally would.
// Using the connect string
// jdbc:apache:commons:dbcp:example
// The general form being:
// jdbc:apache:commons:dbcp:<name-of-pool>
//

Connection conn = null;
Statement stmt = null;
ResultSet rset = null;

try {
System.out.println("Creating connection.");
conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:example");
System.out.println("Creating statement.");
stmt = conn.createStatement();
System.out.println("Executing statement.");
rset = stmt.executeQuery(args[1]);
System.out.println("Results:");
int numcols = rset.getMetaData().getColumnCount();
while(rset.next()) {
for(int i=1;i<=numcols;i++) {
System.out.print("\t" + rset.getString(i));
}
System.out.println("");
}
} catch(SQLException e) {
e.printStackTrace();
} finally {
try { rset.close(); } catch(Exception e) { }
try { stmt.close(); } catch(Exception e) { }
try { conn.close(); } catch(Exception e) { }
}

// Display some pool statistics
try {
printDriverStats();
} catch (Exception e) {
e.printStackTrace();
}

// closes the pool
try {
shutdownDriver();
} catch (Exception e) {
e.printStackTrace();
}
}

public static void setupDriver(String connectURI) throws Exception {
//
// First, we'll need a ObjectPool that serves as the
// actual pool of connections.
//
// We'll use a GenericObjectPool instance, although
// any ObjectPool implementation will suffice.
//
ObjectPool connectionPool = new GenericObjectPool(null);

//
// Next, we'll create a ConnectionFactory that the
// pool will use to create Connections.
// We'll use the DriverManagerConnectionFactory,
// using the connect string passed in the command line
// arguments.
//
ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(connectURI,null);

//
// Now we'll create the PoolableConnectionFactory, which wraps
// the "real" Connections created by the ConnectionFactory with
// the classes that implement the pooling functionality.
//
PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory,connectionPool,null,null,false,true);

//
// Finally, we create the PoolingDriver itself...
//
Class.forName("org.apache.commons.dbcp.PoolingDriver");
PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");

//
// ...and register our pool with it.
//
driver.registerPool("example",connectionPool);

//
// Now we can just use the connect string "jdbc:apache:commons:dbcp:example"
// to access our pool of Connections.
//
}

public static void printDriverStats() throws Exception {
PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
ObjectPool connectionPool = driver.getConnectionPool("example");

System.out.println("NumActive: " + connectionPool.getNumActive());
System.out.println("NumIdle: " + connectionPool.getNumIdle());
}

public static void shutdownDriver() throws Exception {
PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
driver.closePool("example");
}
}

/*****************************************************************************/
fargo,could you explain this program
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Manikandan ThiagarajanSenior consultantAuthor Commented:
fargo, this could be applicalble for weblogic server
fargoCommented:
everything is explained in the code with the comments. What exactly u r not getting??
Manikandan ThiagarajanSenior consultantAuthor Commented:
public static void setupDriver(String connectURI) throws Exception {}

what purpose for this method and code inside
fargoCommented:
in the setupDriver method in the code, is basically the manual initialisation of the connection pool for the dbcp. But u can simply avoid it if u do the following. Read the examples in the commons-dbcp site.

Use the org.apache.commons.dbcp.BasicDataSource class and initialiase it with required connection parameter. Then have a class to handle the request of the connection and closing the connection for the initialised datasource.
Manikandan ThiagarajanSenior consultantAuthor Commented:
Class.forName ( driverClassName ) ;
Connection con = DriverManager.getConnection () ;
Statement s = con.createStatement () ;
ResultSet rs = s.executeQuery ( "your SQL query" ) ;

while ( rs.next () )
{
  // get the data using rs.getString ( "column name" ) ;


}

is that  statements are enough to achieve connection pool
fargoCommented:
No. These are enough to make a db connection BUT NOT connection pooling.

here is one good article.
http://www.onjava.com/pub/a/onjava/2006/04/19/database-connection-pooling-with-tomcat.html
OR
here
http://www.javaranch.com/journal/200601/JDBCConnectionPooling.html
Manikandan ThiagarajanSenior consultantAuthor Commented:
 Context envCtx = (Context) new InitialContext().lookup("java:comp/env");
      datasource = (DataSource) envCtx.lookup("jdbc/MyDataSource");

could you explain this code
fargoCommented:
the mentioned code is using JNDI (Java naming and directory interface) to lookup for the configuration parameter for the datasource. Generally one define this as a Resource in tomcat for the datasource configuration using JNDI and commons-dbcp. Commons-DBCP is default connection pooling provided by jakarta tomcat. You can even get more details about the JNDI Configuration with tomcat in the jakart tomcat website.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JSP

From novice to tech pro — start learning today.