Using Tomcat's DBCP within a servlet

sicruise

I am writing an ajax application that is very demanding on mysql... So I am looking at the option of using a DBCP. I have set up the connection pool successfully but my problem is I can't find any example java to use this...

The only examples I can find are using the jstl tag libary withing jsp's.

Anyone have any example code?

and dont send me something like this...

    public void LoadDriver() {
        try {
            // The newInstance() call is a work around for some
            // broken Java implementations

            Class.forName("com.mysql.jdbc.Driver").newInstance();
            
        } catch (Exception ex) {
            System.out.println("Error Loading Database Driver");
        }
    }
    
    public Connection CreateConnection() {
        
        Connection conn = null;
        
        try {
            
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db?user=user&password=password");
            
        } catch (SQLException ex) {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        
        return conn;
    }

information

sicruise wrote:

I am writing an ajax application that is very demanding on mysql... So I am looking at the option of using a DBCP. I have set up the connection pool successfully but my problem is I can't find any example java to use this...

http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html

smcelhinney

Im so tempted to post that code back to you..

Whats the problem with JSTL anyhow? Or custom taglibs for that matter?

In any case the above post is the best starting point. Use the Tomcat documentation on JNDI. Google "jndi servlet OR jsp implementation", its all tomcat baby..

Here's a sample DB connection using JNDI datasources.

Context initContext = new InitialContext();
Context envContext  = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");
Connection conn = ds.getConnection();
//etc.

Disadvantage of jndi servlet/jsp implementations is that recordsets AND connections always have to be programmatically closed. Always.. forever.. (Did I stress that enough?)

With JSTL, no need to worry about this kinda stuff, all taken care. Is it servlets you're using with Ajax? Or pure JSP?

sicruise

Thanks for your help on this,

basically I am structuring my app that any database calls and methods are all done in servlets and the front is served by jsp's.

I'll try implement this over the next 2 days, seeing the app is already basically built it may take a while to convert fully. They way I have it at the moment closing the connections shouldn't cause any issues... that's what i'm doing with my standard jdbc connection anyway.

Have you implemented this before?

information

sicruise wrote:

basically I am structuring my app that any database calls and methods are all done in servlets

Its bad design to put DB code in servlets, you should use DAOs
http://java.sun.com/blueprints/corej2eepatterns/Patterns/DataAccessObject.html

sicruise wrote:

I'll try implement this over the next 2 days, seeing the app is already basically built it may take a while to convert fully.

If you used DAOs you would only have to change one method, the getCon() method. here you would simply replace the jdbc code with the jndi code

sicruise wrote:

They way I have it at the moment closing the connections shouldn't cause any issues... that's what i'm doing with my standard jdbc connection anyway.

You will see a hugh performance improvement when you use connection pooling, because you are creating a new connection each time which is where the most overhead is in database access.

You must close the connections in order to return them to the pool.
See the last section of sample code in th elink I posted as this shows you how to do it correctly.

smcelhinney

Im actually just getting the hang of DBCP myself, but you have everything you need in 'information's link.

thats what I use anyway. Start by figuring out how to create JNDI Datasources.

Are you using Netbeans for development, or something a little more powerful? With the Java Blueprints (current ver, not sure) implementation, its easy to create these on a context level, by editing the context.xml.

Like I have one that I use for a change management tool Im building..

In the context xml

<?xml version="1.0" encoding="UTF-8"?>
<Context crossContext="true" debug="5" docbase="ChangeManager" path="/cm" reloadable="true">
  <Logger className="org.apache.catalina.logger.FileLogger" prefix="localhost_changeman_log." suffix=".txt" timestamp="true"/>
  <Resource auth="Container" name="jdbc/ChangeMan" type="javax.sql.DataSource"/>
  <ResourceParams name="jdbc/ChangeMan">
    <parameter>
      <name>factory</name>
      <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
    </parameter>
    <!-- Maximum number of dB connections in pool. Make sure you
        configure your mysqld max_connections large enough to handle
        all of your db connections. Set to 0 for no limit.
        -->
    <parameter>
      <name>maxActive</name>
      <value>100</value>
    </parameter>
    <!-- Maximum number of idle dB connections to retain in pool.
        Set to -1 for no limit.  See also the DBCP documentation on this
        and the minEvictableIdleTimeMillis configuration parameter.
        -->
    <parameter>
      <name>maxIdle</name>
      <value>30</value>
    </parameter>
    <!-- Maximum time to wait for a dB connection to become available
        in ms, in this example 10 seconds. An Exception is thrown if
        this timeout is exceeded.  Set to -1 to wait indefinitely.
        -->
    <parameter>
      <name>maxWait</name>
      <value>10000</value>
    </parameter>
    <!-- MySQL dB username and password for dB connections  -->
    <parameter>
      <name>username</name>
      <value>user</value>
    </parameter>
    <parameter>
      <name>password</name>
      <value>pass</value>
    </parameter>
    <!-- Class name for the old mm.mysql JDBC driver - uncomment this entry and comment next
        if you want to use this driver - we recommend using Connector/J though
        <parameter>
        <name>driverClassName</name>
        <value>org.gjt.mm.mysql.Driver</value>
        </parameter>
        -->
    <!-- Class name for the official MySQL Connector/J driver -->
    <parameter>
      <name>driverClassName</name>
      <value>com.mysql.jdbc.Driver</value>
    </parameter>
    <!-- The JDBC connection url for connecting to your MySQL dB.
        The autoReconnect=true argument to the url makes sure that the
        mm.mysql JDBC Driver will automatically reconnect if mysqld closed the
        connection.  mysqld by default closes idle connections after 8 hours.
        -->
    <parameter>
      <name>url</name>
      <value>jdbc:mysql://localhost:3306/changeman?autoReconnect=true</value>
    </parameter>
    <parameter>
      <name>removeAbandoned</name>
      <value>true</value>
    </parameter>
    <parameter>
      <name>removeAbandonedTimeout</name>
      <value>60</value>
    </parameter>
    <parameter>
      <name>logAbandoned</name>
      <value>true</value>
    </parameter>
  </ResourceParams>
</Context>

and then in the web.xml

    <resource-ref>
        <description>DB Connection</description>
        <res-ref-name>jdbc/ChangeMan</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>

nice..

the last few params help the DBCP class to close and log inactive or abandoned connections, which helps you improve your code.

Any questions, pm me or post again..

information

smcelhinney wrote:

In the context xml

the link i gave says put code in server.xml, but you should put it in context.xml.
I haven't checked in a while but I think it now only works if its in context.xml.
Having it in context.xml makes your war file more portable

smcelhinney wrote:

<?xml version="1.0" encoding="UTF-8"?>
<Context crossContext="true" debug="5" docbase="ChangeManager" path="/cm" reloadable="true">
  <Logger className="org.apache.catalina.logger.FileLogger" prefix="localhost_changeman_log." suffix=".txt" timestamp="true"/>
  <Resource auth="Container" name="jdbc/ChangeMan" type="javax.sql.DataSource"/>
  <ResourceParams name="jdbc/ChangeMan">
    <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</value>
    </parameter>
    <parameter>
      <name>password</name>
      <value>pass</value>
    </parameter>
    <parameter>
      <name>driverClassName</name>
      <value>com.mysql.jdbc.Driver</value>
    </parameter>
    <parameter>
      <name>url</name>
      <value>jdbc:mysql://localhost:3306/changeman?autoReconnect=true</value>
    </parameter>
    <parameter>
      <name>removeAbandoned</name>
      <value>true</value>
    </parameter>
    <parameter>
      <name>removeAbandonedTimeout</name>
      <value>60</value>
    </parameter>
    <parameter>
      <name>logAbandoned</name>
      <value>true</value>
    </parameter>
  </ResourceParams>
</Context>

Thats the old way to do it, see the link I provided for the new layout