How to use JDBC and transactions in Tomcat with JOTM

Target audience

This howto is intended for Servlet/JSP developers using Tomcat wanting to take advantage of distributed transactions when using JDBC code in their servlets

This howto is based on the JNDI Datasource HOW-TO (4.1.x, 5.0.x and 5.5.x) provided by the Apache Tomcat Project.

Tomcat and JOTM versions used in this document:

Database used for this document

1. Introduction

JOTM (Java Open Transaction Manager) is a transaction manager written in Java and implementating JTA (Java Transaction API.) JOTM is an Open Source project released under a BSD-style license.

Tomcat is the servlet container used in the official Reference Implementation for the Java Servlet and JavaServer Pages (JSP) technologies. Tomcat is released under the Apache Software License

MySQL configuration

Ensure that you follow these instructions as variations can cause problems.

Create a new test user, a new database and a single test table. Your MySQL user must have a password assigned. The driver fails if you try to connect with an empty password.

mysql> GRANT ALL PRIVILEGES ON *.* TO mojo@localhost
    ->   IDENTIFIED BY 'jojo' WITH GRANT OPTION;
mysql> create database javatest;
mysql> use javatest;
mysql> create table testdata (
    ->   id int not null auto_increment primary key,
    ->   foo int)type=InnoDB;
mysql>

Note: the above user should be removed once testing is complete!

Next insert some test data into the testdata table.

mysql> insert into testdata values(null, 1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from testdata;
+----+-----+
| ID | FOO |
+----+-----+
|  1 |   1 |
+----+-----+
1 row in set (0.00 sec)

mysql>

PostgreSQL configuration and setup is similar.

Installing Required JARs

In order for a web application to use JOTM, the webapp and Tomcat need to have access to the JOTM jars, as well as the jars it requires

Below is a list of required jars. They are all included with JOTM binary distribution, available at http://www.objectweb.org/jotm

All these jar files need to be placed on $TOMCAT_HOME/common/lib/ so that both Tomcat and your web application will see them.

You also need to copy the jar of your JDBC driver to $TOMCAT_HOME/common/lib/. For example:

Where X = version

Configuring JOTM

Create a file named carol.properties with the following properties:

# JNDI (Protocol Invocation)
carol.protocols=jrmp

# Local RMI Invocation
carol.jvm.rmi.local.call=true

# do not use CAROL JNDI wrapper
carol.start.jndi=false

# do not start a name server
carol.start.ns=false

# Naming Factory
carol.jndi.java.naming.factory.url.pkgs=org.apache.naming

This file resides in $TOMCAT_HOME/common/classes/.

Note: In Tomcat 5.5.x, you can include all required JARs in WEB-INF/lib. With this configuration carol.properties goes in WEB-INF/classes. For an example WAR, see http://static.raibledesigns.com/downloads/dbtest.war.

Configuring Tomcat

server.xml configuration

Now that you've installed JOTM, configure Tomcat to access JDBC and transaction resources. This is done in the dbtest.xml file used by Tomcat to initialize resources used by the web application. NOTE: This configuration has changed for Tomcat 5.5. 

<Context path="/dbtest" docBase="dbtest.war" debug="0"
  reloadable="true" crossContext="true">
  <!-- Resource configuration for JDBC datasource
       use XAPool
  -->
  <Resource name="jdbc/myDB" auth="Container"
    type="javax.sql.DataSource"/>
  <ResourceParams name="jdbc/myDB">
    <parameter>
      <name>factory</name>
      <value>org.objectweb.jndi.DataSourceFactory</value>
    </parameter>
    <!-- configured by default for PostgreSQL, just change the values
         to set it for your database
    -->
    <parameter><name>username</name><value>mojo</value></parameter>
    <parameter><name>password</name><value>jojo</value></parameter>
    <parameter><name>driverClassName</name>
      <value>org.postgresql.Driver</value></parameter>
    <parameter><name>url</name>
      <value>jdbc:postgresql://localhost/javatest</value></parameter>
    <!--
       <parameter><name>driverClassName</name>
       <value>com.mysql.jdbc.Driver</value></parameter>
       <parameter><name>url</name>
       <value>jdbc:mysql://localhost/javatest</value></parameter>
    -->
  </ResourceParams>
  <!-- Resource configuration for UserTransaction
  use JOTM
  -->
  <Resource name="UserTransaction" auth="Container"
    type="javax.transaction.UserTransaction"/>
  <ResourceParams name="UserTransaction">
    <parameter>
      <name>factory</name>
      <value>org.objectweb.jotm.UserTransactionFactory</value>
    </parameter>
    <parameter>
      <name>jotm.timeout</name>
      <value>60</value>
    </parameter>
  </ResourceParams>
</Context>

For Tomcat 5.5.x, this configuration has been simplified and a new <Transaction> element has been added. In your dbtest.xml (or META-INF/context.xml), configure the <Resource> and <Transaction> elements as follows.

<Context reloadable="true">

    <Resource name="jdbc/myDB" auth="Container" type="javax.sql.DataSource"
        factory="org.objectweb.jndi.DataSourceFactory"
        driverClassName="org.postgresql.Driver"
        username="mojo" password="jojo" url="jdbc:postgresql://localhost/javatest"/>
    
    <Transaction factory="org.objectweb.jotm.UserTransactionFactory"
       jotm.timeout="60"/>

</Context>

Notes from the Tomcat mailing list thread: UserTransaction, JOTM and Tomcat 5.5.x

web.xml configuration

Configure the web.xml file for the web application

<?xml version="1.0" encoding="ISO-8859-1"?>
 <!DOCTYPE web-app PUBLIC
	"-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
	"http://java.sun.com/dtd/web-app_2_3.dtd">

<web-app>
  <resource-env-ref>
    <description>
      DB Connection
    </description>
    <resource-env-ref-name>
      jdbc/myDB
    </resource-env-ref-name>
    <resource-env-ref-type>
      javax.sql.DataSource
    </resource-env-ref-type>
  </resource-env-ref>
</web-app>

Test Code

Create a simple test.jsp for use.

<html>
  <head>
    <title>DB Test</title>
  </head>
  <body>

    <%
    String completion = request.getParameter("completion");
    foo.DBTest test = new foo.DBTest();
    test.init(completion);
    %>
    <h2>Transaction completion</h2>
    Transaction completion is :<strong><%= completion %></strong>

    <h2>Results</h2>
    Int stored in JDBC : <strong><%= test.getFoo() %></strong><br />

    <hr />

    <form action="test.jsp" method="get">
      <input type="radio" name="completion" value="commit" checked="true"> Commit<BR>
      <input type="radio" name="completion" value="rollback">Rollback<BR>
      <button type="submit">Completion</button>
    </form>
  </body>
</html>

Create a Java class to use the Datasource and transactions. Note: this code isn't anywhere near production ready - it's only supposed to be used as a simple test :-)

package foo;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import javax.transaction.UserTransaction;

public class DBTest{

    int foo = -1;
    // value stored in DB

    public void init(String completion) {
        try{
            Context ctx = new InitialContext();

            // JDBC stuff
            DataSource ds =
                (DataSource)ctx.lookup("java:comp/env/jdbc/myDB");

            UserTransaction ut = (UserTransaction)ctx.lookup("java:comp/UserTransaction");

            java.sql.Connection conn = ds.getConnection();

            System.out.println("<<< beginning the transaction >>>");
            ut.begin();

             // JDBC statements
             Statement stmt = conn.createStatement();
             ResultSet rst =
                 stmt.executeQuery("select id, foo from testdata");
             if(rst.next()) {
                 foo=rst.getInt(2);
             }
             System.out.println("foo = "+ foo +" (before completion)");

             PreparedStatement pstmt = conn.prepareStatement("update testdata set foo=? where id=1");
             pstmt.setInt(1,++foo);
             pstmt.executeUpdate();

              if (completion != null && completion.equals("commit")) {
                  System.out.println("<<< committing the transaction >>>");
                  ut.commit();
              } else {
                  System.out.println("<<< rolling back the transaction >>>");
                  ut.rollback();
              }

             // we set foo to the value stored in the DB
             rst =
                 stmt.executeQuery("select id, foo from testdata");
             if(rst.next()) {
                 foo=rst.getInt(2);
             }
             System.out.println("foo = "+ foo +" (after completion)");

             conn.close();
             System.out.println("<<< done >>>");
        }catch(Exception e) {
            System.out.print("DBTest >> ");
            e.printStackTrace();
        }
    }

    public String getFoo() { return ""+foo; }
}

Lastly, deploy your web app into $CATALINA_HOME/webapps as a warfile called dbtest.war.

Once deployed, point a browser at http://localhost:8080/dbtest/test.jsp to view the fruits of your hard work.

You can also download dbtest.war from http://static.raibledesigns.com/downloads/dbtest.war.