permaculture playing cards*
The moose likes JDBC and the fly likes connection pooling Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "connection pooling" Watch "connection pooling" New topic
Author

connection pooling

raghuram bikkani
Greenhorn

Joined: Jan 18, 2006
Posts: 16
how will we create connection pooling manually?
Manoj Kumkumath
Ranch Hand

Joined: Dec 01, 2005
Posts: 71
Raghuram,
As the name indicates, Connection pooling is just a way to efficiently handle your database connection(JDBC connection) by sharing it for different clients. So when your server starts or when you make the first call to a db, you can make a pool of prefixed number of database connections and whenever you need a connection instead of creating a connection it picks the connection from the collection where you have stored your connection. Once you are done with your connection, you return it back to the pool.

What I said is the minimum requirement. May be the best starting point is understand how the various existing connection pools behave. Then it's just matter of programming.
-Manoj
Jean Mathew
Greenhorn

Joined: Feb 07, 2005
Posts: 4
Some steps/points to get a web application up and running in tomcat
5.0 that uses DBCP Connection pooling and JNDI lookup for locating
the data source that is configured in the pool.

Requirements:
=============
Tomcat 5.0 (DBCP JAR included)
MySQL Database (I have used for this example)
MySQL Driver (e.g. mysql-connector-java-3.2.0-bin.jar)


Source Code (I used servlet in this example)
===========

web.xml
-------
<?xml version="1.0" encoding="ISO-8859-1"?>
<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
version="2.4">
<display-name>DBCP Test</display-name>
<description>Testing Connection Pooling and JNDI</description>
<servlet>
<servlet-name>TestServlet</servlet-name>
<servlet-class>jean.TestServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>TestServlet</servlet-name>
<url-pattern>/TestDBCPServlet</url-pattern>
</servlet-mapping>
<!-- I dont find any relevance for this code...
thats why i commented out this.
<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/mysql</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
-->
</web-app>


TestServlet.java
----------------
package jean;

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpServletRequest;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import java.io.PrintWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.PreparedStatement;

public class TestServlet extends HttpServlet {

public void doGet(HttpServletRequest request,
HttpServletResponse response) {
Connection con = null;

response.setContentType("text/html");
try {
PrintWriter out = response.getWriter();
out.print("<BR><BR><b>DBCP with JNDI based
Connection</b><BR>Sample Rows...") ;
Context init = new InitialContext();
Context c = (Context) init.lookup("java:comp/env");
DataSource ds = (DataSource) c.lookup
("jdbc/MySQLDBPool");
synchronized (ds) {
conn = ds.getConnection();
}
ps = conn.prepareStatement("Select * from a");
ResultSet rs=null;
rs = ps.executeQuery();
while (rs.next()) {
out.println("<BR>" + rs.getString(2));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}

There are more that one way we can configure the web application
context in the tomcat. In this example I just make a jeantest.xml
and copied to the '<TOMCAT_HOME>/conf/Catalina/localhost' folder.

In this file you can see that I am using empty string in the parth
attribute of the 'Context' tag. This will make this web application
which resides in the folder 'D:/jeandbcptest' to be mounted as the
root context with the tomcat. If we do like this we can access the
application using the url 'http://localhost:8080/' (Default ROOT
will not be seen in the root context). If you want to change it to
any sub context you can give any name in that path attribute (say
<Context path="TestDBCP" ...). Then you can use access your
application using the URL 'http://localhost:8080/TestDBCP/'.

In this file I have created a connection pool with the JNDI
name 'jdbc/MySQLDBPool' using 'Resource' tag and set the parameters
with the 'ResourceParams' tag.

jeantest.xml
------------
<Context path="" docBase="D:/jeandbcptest" debug="0"
privileged="true">
<Logger className="org.apache.catalina.logger.FileLogger"
prefix="localhost_DBTest_log." suffix=".txt"
timestamp="true"/>
<Resource name="jdbc/MySQLDBPool"
auth="Container"
type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/MySQLDBPool">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>100</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>30</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>10000</value>
</parameter>
<parameter>
<name>username</name>
<value>user_name_goes_here</value>
</parameter>
<parameter>
<name>password</name>
<value>password_goes_here</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.mysql.jdbc.Driver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:mysql://localhost:3306/test?
autoReconnect=true</value>
</parameter>
</ResourceParams>
</Context>

That's all... Start the tomcat and Give the URL
http://localhost:8080/TestDBCPServlet

If you want to add this context definition in the server.xml place
it between <HOST> and </HOST> tag.

Note that JNDI pool is binded to the context name that we are giving
in the 'path' attribute. For example we have put this application in
the '<TOMCAT_HOME>/webapp/testinwebapp' folder, and we just update
the 'docBase' attribute of 'Context' with this path... We can access
this same application with these two URL's. But we cannot use the
JNDI name with the 2nd URL.
1. http://localhost:8080/TestDBCPServlet
2. http://localhost:8080/testinwebapp/TestDBCPServlet

Jean


Jean Mathew<br />Kochi, Kerala, India
Hemant Agarwal
Ranch Hand

Joined: Nov 21, 2005
Posts: 138
Nice Explanation
Babji Reddy
Ranch Hand

Joined: Jan 24, 2006
Posts: 106
In addition to obtain and return connections from/to a pool, a connexion pool should have capabilities like 'Monitoring Timed out connections'.
This is an 'Event driven' paradim, where a pool thread will notify the timedout connection and there by returning that connection.
Other features are monitoring stale connections, where a pool thread takes the unused conenctions in the pool and fire some sample SQLs in regular intervals, there by making sure that the connection is a live connection before giving it to a request.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: connection pooling
 
Similar Threads
Connection Pooling
Questions on EJB
pooling in tomcat 6.x
servlet destroy method and session
Driver Loading