Spring, Databases and Tomcat5.5

This paper provides a brief checklist for defining and using a database using Tomcat5.5 and the Spring Framework.

This paper was written by bgiles at coyotesong dot com. It is incomplete at this time.

Overview

There are seven steps to using a database in a J2EE enviroment using the Spring framework.

• Define connection information
• Make information available to web application
• Create a DataSource from information
• Pass DataSource to implementations via dependency injection
• Use Spring Framework JdbcTemplate classes to perform actual access
• Improve performace with caching
• Stored Procedures

Defining the Connection Information

J2EE web servers almost always also function as JNDI servers for their applications. This is a convenient way for the site administrator to pass site-specific information, e.g. database connection information, to the application.

Tomcat5.5 can use a configuration file passed as metainformation in the web application's WAR file. This is a tremendous convenience in the development environment.

context.xml (WAR metainf file)

<Context path="/app" reloadable="true" 
    antiJARLocking="true" antiResourceLocking="false">


    <!-- database resource -->
    <Resource name="jdbc/webapp"
              auth="Container"
              type="javax.sql.DataSource"/>

    <!-- many of these parameters are used by Apache DBCP connection pooling -->
    <ResourceParam name="jdbc/webapp">
        <parameter>
            <name>factory</name>
            <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
        </parameter>
        <parameter>
            <name>driverClassName</name>
            <value>org.postgresql.Driver</value>
        </parameter>
        <parameter>
            <name>username</name>
            <value>username<value>
        </parameter>
        <parameter>
            <name>password</name>
            <value>password<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>defaultAutoCommit</name>
            <value>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>
    </ResourceParam>
</Context>

(Notes: you should use your own connection information. The standard PostgreSQL port is 5432, but at this time the Debian PostgreSQL 8.x packages use 5433 to avoid conflict with the PostgreSQL 7.x packages.)

build.xml fragment

<target name="package" depends="compile">
    <war destfile="${webapp.dist}/webapp.war"
        webxml="${webapp.staging}/WEB-INF/web.xml" compress="true">
        <metainf dir="${webapp.dist}" includes="context.xml"/>
        ...
    </war>
</target>

(Note: we use the property "webapp.staging" to identify the web application staging directory and "webapp.dist" to identify the distribution directory.)

Making Information Available to Web Application

Web applications must be told how to access to server-based JNDI information in the web.xml file.

web.xml fragment

<resource-ref>
    <description>database connection</description>
    <res-ref-name>jdbc/webapp</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>

Creating a DataSource from Connection Information

Spring can create a SQL DataSource for us using the connection information provided by the site administrator. We can use dependency injection to pass this information to our application code -- see next section.

dataAccessContext.xml fragment

<beans>
    <!-- JNDI DataSource in J2EE environments -->
    <!-- we need to copy database (and DBCP?) jar files to tomcat5.5/commons/lib. -->
    <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
        <property name="jndiName" value="java:/comp/env/jdbc/webapp"/>
    </bean>
</beans>

(Note: we are following the convention of splitting Spring's applicationContext.xml file into multiple files. dataAccessContext.xml contains information on the persistence layer, action-servlet.xml contains information on the web/servlet layer.)

Passing the DataSource to Implementations via Dependency Injection

Finally, each DAO implementation class must have the new DataSource passed to it. Remember: DAO implementation classes only need to provide a setter.

dataAccessContext.xml fragment

<beans>
    <bean id="objDao" class="com.example.dao.jdbc.ObjJdbc">
        <property name="dataSource">ref bean="dataSource"/></property>
    </bean>
</beans>

ObjJdbc.java fragment

public class ObjJdbc {
    private DataSource ds = null;

    public void setDataSource(ds) {
        this.ds = ds;
    }
}

Using Spring JdbcTemplate classes

(To be added)

SimpleJdbcTemplate

NamedParameterJdbcTemplate(?)

Improving performace with caching

(To be added)

risks, benefits

Stored Procedures

(To be added)