Article:		Database Connection Pooling with Tomcat
Subject:		More pools for Tomcat
Date:		2006-04-25 10:42:50
From:		Kunal_Jaggi
Response to: More pools for Tomcat
Hello Mike, Connection pooling is a standard technique used for sharing resources among several requesting clients. In a typical Java EE environment,you can share both database and non-database resources. Other than JDBC Connection objects, you can share threads, sockets and JMS message queues (JMS API provides these two interfaces: javax.jms.QueueConnectionFactory and javax.jms.QueueConnection). Please note, Apache Tomcat is a reference implementation for servlets and JSP technologies, it's not an EJB container. So, if you want to work with EJBs and JMS, you need an app server like BEA WebLogic or an open source alternative like JBoss or Apache Geronimo. Regards, Kunal

Showing messages 1 through 4 of 4.

• Easier said than done: connection pooling
  2006-10-08 16:53:44  jpower [View]
  
  
  I specified maxActive=100 and maxIdle=20 for my webapp. But as the app runs, the connections just ramped up. After a while, even if no one was using the app, I saw ~ 30 idle connections there, much bigger than the value of maxIdle. Unless I shutdown Tomcat, the number doesn't get smaller. This is giving me huge headache since I know the app is going to blow up as user keeps using it.
  I take special care in closing connections in finally block:
  fnally {
  
  }
• Easier said than done: connection pooling
  2008-05-21 03:42:49  lekanb [View]
  
  
  When i run the servlet now i get this error bcos i have started using the connection pooling:
  
  org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class ' ' for connect URL 'null'
  
  and this is my code 4 d servlet.
  
  package com.lekan.dbcp;
  
  import java.io.*;
  import javax.servlet.*;
  import javax.servlet.http.*;
  import java.sql.*;
  import javax.sql.*;
  import javax.naming.*;
  
  public class MobileServlet extends HttpServlet
  {
  private DataSource datasource = null;
  
  public void init() throws ServletException {
  try {
  //Create a datasource for pooled connections.
  datasource = (DataSource) getServletContext().getAttribute("DBCPool");
  
  //Register the driver for non-pooled connections.
  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
  }
  catch (Exception e) {
  throw new ServletException(e.getMessage());
  }
  }
  
  private synchronized Connection getConnection() throws SQLException {
  //if (pooledConnection) {
  //pooledCount++;
  return datasource.getConnection(); // Allocate and use a connection from the pool
  //}else {
  //nonPooledCount++;
  //Connection con = DriverManager.getConnection("jdbc:odbc:Addbook");
  //return con; //retun a newly created object
  //}
  }
  
  public void doPost ( HttpServletRequest req, HttpServletResponse res )
  throws IOException, ServletException
  {
  doGet( req, res );
  }
  
  public void doGet ( HttpServletRequest req, HttpServletResponse res )
  throws IOException, ServletException
  {
  res.setContentType("text/plain");
  PrintWriter out = res.getWriter();
  //BufferedReader br = req.getReader( );
  //String buf = br.readLine( );
  
  Connection conn = null;
  Statement stmt = null;
  ResultSet rest = null;
  
  try{
  //String uid = buf.substring(buf.indexOf('*')+1,buf.indexOf(','));
  //String pas = buf.substring(buf.indexOf(',')+1,buf.indexOf('>'));
  
  String sql = "SELECT * FROM tblOwner WHERE OwnerID = 'lekan' AND Password = 'golden'";
  
  conn = getConnection();
  stmt = conn.createStatement();
  rest = stmt.executeQuery(sql);
  
  if(rest.next()){
  out.print("User name and password valid!!");
  System.out.print("User name and password valid!!");
  }
  else{
  out.print("User name and password invalid!!");
  System.out.print("User name and password valid!!");
  }
  conn.close();
  }catch(SQLException se){
  out.print(se);
  se.printStackTrace();
  }
  finally {
  try {if (rest != null) rest.close();} catch (SQLException e) {}
  try {if (stmt != null) stmt.close();} catch (SQLException e) {}
  try {if (conn != null) conn.close();} catch (SQLException e) {}
  }
  out.close();
  }
  }
  
  
  and this is the listener
  
  package com.lekan.dbcp;
  
  import javax.servlet.*;
  import javax.servlet.http.*;
  
  import java.sql.*;
  import javax.sql.*;
  import javax.naming.*;
  
  public class DBCPoolingListener implements ServletContextListener{
  
  public void contextInitialized(ServletContextEvent sce){
  
  try {
  // Obtain our environment naming context
  Context envCtx = (Context) new InitialContext().lookup("java:comp/env");
  
  // Look up our data source
  DataSource ds = (DataSource) envCtx.lookup("jdbc/Addbook");
  
  sce.getServletContext().setAttribute("DBCPool", ds);
  }catch(NamingException e){
  e.printStackTrace();
  }
  }
  public void contextDestroyed(ServletContextEvent sce){}
  }
  
  
  
  This is the context in the server.xml
  
  <Context path="/dbcp" docBase="dbcp" debug="5" reloadable="true" crossContext="true">
  
  + <Resource name="jdbc/Addbook" auth="Container"+
  + type="javax.sql.DataSource" removeAbandoned="true"+
  + removeAbandonedTimeout="30" maxActive="0"+
  + maxIdle="-1" maxWait="30000" username=""+
  + password=""+
  + driverClassName="sun.jdbc.odbc.JdbcOdbcDriver"+
  + url="jdbc:odbc:Addbook"/>+
  
  + </Context>+
  
  This is my web.xml
  
  <!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.2//EN" "http://java.sun.com/j2ee/dtds/web-app_2_2.dtd">
  
  <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>
  Servlet for mobile apps
  </display-name>
  
  +<description> +
  + Servlet that handles mobile request+
  </description>
  
  <listener>
  <listener-class>com.lekan.dbcp.DBCPoolingListener</listener-class>
  </listener>
  
  <!-- This component has a dependency on an external resource-->
  <resource-ref>
  <description>DB Connection Pooling</description>
  <res-ref-name>jdbc/Addbook</res-ref-name>
  <res-type>javax.sql.DataSource</res-type>
  <res-auth>Container</res-auth>
  </resource-ref>
  
  <servlet>
  + <servlet-name>myServlet</servlet-name>+
  + <servlet-class>com.lekan.dbcp.MobileServlet</servlet-class>+
  <load-on-startup>0</load-on-startup>
  </servlet>
  
  <servlet-mapping>
  + <servlet-name>myServlet</servlet-name>+
  + <url-pattern>/mobile1</url-pattern>+
  </servlet-mapping>
  
  </web-app>
  
  Please need help,
  Thank you
• Easier said than done: connection pooling
  2009-06-09 03:53:33  Gerhard Greyvenstein [View]
  
  
  The problem is not with your java code, but rather the context of the datasource.
  
  This is the resource I found that has the best example: http://wiki.metawerx.net/wiki/Context.xml
• Easier said than done: connection pooling
  2006-10-08 16:53:35  jpower [View]
  
  
  I specified maxActive=100 and maxIdle=20 for my webapp. But as the app runs, the connections just ramped up. After a while, even if no one was using the app, I saw ~ 30 idle connections there, much bigger than the value of maxIdle. Unless I shutdown Tomcat, the number doesn't get smaller. This is giving me huge headache since I know the app is going to blow up as user keeps using it.
  I take special care in closing connections in the finally block:
  fnally {
  if (con != null) con.close;
  }
  However, I didn't try to close PreparedStatement in finally block because I assume if I close the connection, PreparedStatement will be automaticall closed.
  Thanks.