Article:
  Database Connection Pooling with Tomcat
Subject:   Easier said than done: connection pooling
Date:   2006-10-08 16:53:44
From:   jpower
Response to: More pools for Tomcat

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 {


}

Full Threads Newest First

Showing messages 1 through 3 of 3.

  • 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.
  • 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