• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

connection pooling

 
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
how will we create connection pooling manually?
 
Ranch Hand
Posts: 71
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 138
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Nice Explanation
 
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic