Developing a Spring Framework MVC application step-by-step

Part 4 – Implementing Database Persistence

Thomas Risberg
August, 2003

(Revised April, 2005)


This is Part 4 of a step-by-step account of how to develop a web application from scratch using the Spring Framework. In Part 1 (Steps 1 – 12) we configured the environment and set up a basic application that we will build upon. Part 2 (Steps 13-19) improved the application in several ways. Part 3 (Steps 20-22) Added some unit tests to the application and we also added a Form for performing a price increase. In Part 4 it is time to deal with database persistence. We saw in the earlier parts how we loaded some business objects using bean definitions in a configuration file. It is obvious that this would never work in real life – whenever we re-start the server we are back to the original prices. We need to add code to actually persist these changes to a database.


Step 23 – Add Ant tasks to create and load test data

Before we can start developing the persistence code, we should create the database tables that we need for our development and testing. We also need a database. I am planning on using HSQL, which is a good open source database written in Java. This database is distributed with Spring, so we can just copy the jar file to the web apps lib directory. Copy  spring-framework-1.2/lib/hsqldb/hsqldb.jar to springapp/war/WEB-INF/lib/. I am planning on using HSQL in a standalone mode to begin with. That way we don't have to worry about starting up a separate database server every time we use it. The URL we specify for connecting to HSQL controls the mode that the database is running in. To be able to run some Ant tasks for the database we have to add some database properties to the build properties file.

springapp/build.properties

# Ant properties for building the springapp

appserver.home=${user.home}/jakarta-tomcat-5.0.28
deploy.path=${appserver.home}/webapps

tomcat.manager.url=http://localhost:8080/manager
tomcat.manager.username=admin
tomcat.manager.password=tomcat

db.driver=org.hsqldb.jdbcDriver db.url=jdbc:hsqldb:db/test db.user=sa db.pw=

Next I add the targets I need to the build script. There are targets to create and delete tables and to load and delete test data.

    <target name="createTables">
        <echo message="CREATE TABLES USING: ${db.driver} ${db.url}"/>
        <sql driver="${db.driver}"
             url="${db.url}"
             userid="${db.user}"
             password="${db.pw}"
             onerror="continue">  
            <classpath refid="master-classpath"/>

        CREATE TABLE products (
          id INTEGER NOT NULL PRIMARY KEY,
          description varchar(255),
          price decimal(15,2)
        );
        CREATE INDEX products_description ON products(description);

        </sql> 
    </target>

    <target name="dropTables">
        <echo message="DROP TABLES USING: ${db.driver} ${db.url}"/>
        <sql driver="${db.driver}"
             url="${db.url}"
             userid="${db.user}"
             password="${db.pw}"
             onerror="continue">  
            <classpath refid="master-classpath"/>

        DROP TABLE products;

        </sql> 
    </target>

    <target name="loadData">
        <echo message="LOAD DATA USING: ${db.driver} ${db.url}"/>
        <sql driver="${db.driver}"
             url="${db.url}"
             userid="${db.user}"
             password="${db.pw}"
             onerror="continue">  
            <classpath refid="master-classpath"/>

        INSERT INTO products (id, description, price) values(1, 'Lamp', 5.78);
        INSERT INTO products (id, description, price) values(2, 'Table', 75.29);
        INSERT INTO products (id, description, price) values(3, 'Chair', 22.81);
COMMIT;
SHUTDOWN

</sql> </target> <target name="printData"> <echo message="PRINT DATA USING: ${db.driver} ${db.url}"/> <sql driver="${db.driver}" url="${db.url}" userid="${db.user}" password="${db.pw}" onerror="continue" print="true"> <classpath refid="master-classpath"/> SELECT * FROM products; </sql> </target> <target name="clearData"> <echo message="CLEAR DATA USING: ${db.driver} ${db.url}"/> <sql driver="${db.driver}" url="${db.url}" userid="${db.user}" password="${db.pw}" onerror="continue"> <classpath refid="master-classpath"/> DELETE FROM products; </sql> </target>

Now I execute some of these tasks to set up the test database. This will create a db folder under the springapp directory. Run 'ant createTables loadData printData' – I have included my output below.

[trisberg@localhost springapp]$ ant createTables loadData printData
Buildfile: build.xml
 
createTables:
     [echo] CREATE TABLES USING: org.hsqldb.jdbcDriver jdbc:hsqldb:db/test
      [sql] Executing commands
      [sql] 2 of 2 SQL statements executed successfully
 
loadData:
     [echo] LOAD DATA USING: org.hsqldb.jdbcDriver jdbc:hsqldb:db/test
      [sql] Executing commands
      [sql] 3 of 3 SQL statements executed successfully
 
printData:
     [echo] PRINT DATA USING: org.hsqldb.jdbcDriver jdbc:hsqldb:db/test
      [sql] Executing commands
      [sql] ID,DESCRIPTION,PRICE
      [sql] 1,Lamp,5.78
      [sql] 2,Table,75.29
      [sql] 3,Chair,22.81
 
      [sql] 1 of 1 SQL statements executed successfully
 
BUILD SUCCESSFUL
Total time: 2 seconds


Step 24 – Create a Data Access Object (DAO) implementation for JDBC.

I begin with creating a new 'springapp/src/db' directory to contain any classes that are used for database access. In this directory I create a new interface called 'ProductManagerDao.java'. This will be the interface that defines the functionality that the DAO implementation classes will provide – we could choose to have more than one implementation.

springapp/src/db/ProductManagerDao.java

package db;

import bus.Product;
import java.util.List;

public interface ProductManagerDao {

    public List getProductList();

    public void increasePrice(Product prod, int pct);

}

I'll follow this with a class called 'ProductManagerDaoJdbc.java' that will be the JDBC implementation of this interface. Spring provides a JDBC abstraction framework that we will make use of. The biggest difference between using straight JDBC and Spring's JDBC framework is that you don't have to worry about opening and closing the connection or any statements. It is all handled for you. Another advantage is that you won't have to catch any exceptions, unless you want to. Spring wraps all SQLExceptions in it's own unchecked exception hierarchy inheriting from DataAccessException. If you want to you can catch this exception, but since most database exceptions are impossible to recover from anyway, you might as well just let the exception propagate up to a higher level.

springapp/src/db/ProductManagerDaoJdbc.java

package db;

import bus.Product;
import java.util.List;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import javax.sql.DataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.object.MappingSqlQuery;
import org.springframework.jdbc.object.SqlUpdate;
import org.springframework.jdbc.core.SqlParameter;

public class ProductManagerDaoJdbc implements ProductManagerDao {

    /** Logger for this class and subclasses */
    protected final Log logger = LogFactory.getLog(getClass());

    private DataSource ds;

    public List getProductList() {
        logger.info("Getting products!");
        ProductQuery pq = new ProductQuery(ds);
        return pq.execute();
    }

    public void increasePrice(Product prod, int pct) {
        logger.info("Increasing price by " + pct + "%");
        SqlUpdate su = 
            new SqlUpdate(ds, "update products set price = price * (100 + ?) / 100 where id = ?");
        su.declareParameter(new SqlParameter("increase", Types.INTEGER));
        su.declareParameter(new SqlParameter("ID", Types.INTEGER));
        su.compile();
        Object[] oa = new Object[2];
        oa[0] = new Integer(pct);
        oa[1] = new Integer(prod.getId());
        int count = su.update(oa);
        logger.info("Rows affected: " + count);
    }

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

    class ProductQuery extends MappingSqlQuery {

        ProductQuery(DataSource ds) {
            super(ds, "SELECT id, description, price from products");
            compile();
        }
 
        protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            Product prod = new Product();
            prod.setId(rs.getInt("id"));
            prod.setDescription(rs.getString("description"));
            prod.setPrice(new Double(rs.getDouble("price")));
            return prod;
        }

    }

}

Lets go over the two DAO methods in this class. First, getProductList() creates a query object that will retrieve all the products. This query is executed and the results are passed back as a list of Products. At the end of the class we can see the definition for this query class. I have implemented it as an inner class since we are not going to reference it anywhere else, but I could just as well have made it a separate class. This query class extends MappingSqlQuery, which is a central class in Spring's JDBC framework. The idea behind this class is that you have to specify a SQL query when this class is created, and you are also responsible for implementing the mapRow method to map the data from each row into a class that represents the entity you are retrieving in your query. That's it, that's all you have to provide. The rest is managed by the framework. In the constructor of the ProductQuery class I pass in the data source. This data source will be provided in a similar fashion to the way we wired up the business objects in Part 2, so we don't have to worry about retrieving a data source in our DAO class. In the constructor I also define the SQL query that we will use to retrieve the products. The mapRow method will be called once for each row returned by the query. It creates a new Product and populates it based on the data retrieved from the current row of the resultset. You should not call getNext on the resultset, it is all handled by the framework. This is another example of an Inversion of Control solution.

The second method increasePrice is utilizing an SqlUpdate class. This class is passed the data source and an SQL update statement with placeholders for parameters. Same syntax as a prepared statement in JDBC. In fact, that is what SqlUpdate creates behind the scenes. For the parameters, we have to give them a name and declare what type they are so that the framework can set them before the prepared statement is executed. After all parameters have been declared, we “compile´┐Ż? the statement in JDO fashion. This will signal that we are done declaring parameters, and the framework will check to make sure that we have a matching declaration for each placeholder in the SQL statement. Next we declare an Object array that will hold the parameter values that we are going to pass in to the prepared statement. This array gets passed into the update method of SqlUpdate. The update method does return the count of rows affected.

I need to store the value of the primary key for each product in the Product class. This key will be used when I persist any changes to the object back to the database. To hold this key I add a private field named 'id' complete with setters and getters to Product.java.

springapp/src/bus/Product.java

package bus;

import java.io.Serializable;

public class Product implements Serializable {

    private int id;
    private String description;
    private Double price;

    public void setId(int i) {
        id = i;
    }

    public int getId() {
        return id;
    }

    public void setDescription(String s) {
        description = s;
    }

    public String getDescription() {
        return description;
    }

    public void setPrice(Double d) {
        price = d;
    }

    public Double getPrice() {
        return price;
    }

}

Now it is time to test this whole DAO setup. Actually, we probably should have written the tests first, but since this is a tutorial style document I think it makes more sense to introduce the actual code before the tests. I decided to test with a live database, so I will have to add dependencies for clearData and loadData to the junit task in the build.xml. This will ensure that we will have a consistent starting point for our tests.

    <target name="junit" depends="build,clearData,loadData" description="Run JUnit Tests">
        <junit printsummary="on"
               fork="false"
               haltonfailure="false"
               failureproperty="tests.failed"
               showoutput="true">
            <classpath refid="master-classpath"/>
            <formatter type="brief" usefile="false"/>

            <batchtest>
                <fileset dir="${build.dir}">
                    <include name="**/Test*.*"/>
                </fileset>
            </batchtest>

        </junit>

        <fail if="tests.failed">
        ***********************************************************
        ***********************************************************
        ****  One or more tests failed!  Check the output ...  ****
        ***********************************************************
        ***********************************************************
        </fail>
    </target>

Next, I add a TestProductManagerDaoJdbc.java to our collection of unit tests. In the setup part I create a data source that will be used for testing.

springapp/src/test/TestProductManagerDaoJdbc.java

package tests;

import java.util.List;
import java.util.ArrayList;
import junit.framework.TestCase;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import db.ProductManagerDaoJdbc;
import bus.Product;

public class TestProductManagerDaoJdbc extends TestCase {

    private ProductManagerDaoJdbc pmdao;

    public void setUp() {
        pmdao = new ProductManagerDaoJdbc();
        DriverManagerDataSource ds = new DriverManagerDataSource();
        ds.setDriverClassName("org.hsqldb.jdbcDriver");
        ds.setUrl("jdbc:hsqldb:db/test");
        ds.setUsername("sa");
        ds.setPassword("");
        pmdao.setDataSource(ds);
    }

    public void testGetProductList() {
        List l = pmdao.getProductList();
        Product p1 = (Product) l.get(0);
        assertEquals("Lamp", p1.getDescription());
        Product p2 = (Product) l.get(1);
        assertEquals("Table", p2.getDescription());
    }

    public void testIncreasePrice() {
        List l1 = pmdao.getProductList();
        Product p1 = (Product) l1.get(0);
        assertEquals(new Double("5.78"), p1.getPrice());
        pmdao.increasePrice(p1, 10);
        List l2 = pmdao.getProductList();
        Product p2 = (Product) l2.get(0);
        assertEquals(new Double("6.36"), p2.getPrice());
    }

}

When we pass the unit tests, we can move on and modify the web application to make use of these new database persistence capabilities.


Step 25 – Modify web application to use database persistence.

If we structured our application properly, we should only have to change the business classes to take advantage of the database persistence. The view and controller classes should not have to be modified, since they should be unaware of any implementation details of the business classes. So let's add the persistence to the ProductManager class. I add a reference to a ProductManagerDao interface plus a setter method for this reference. Which implementation that we actually use should be irrelevant to the ProductManager class, and we will set this through a configuration option.

springapp/src/bus/ProductManager.java

package bus;

import java.io.Serializable;
import java.util.ListIterator;
import java.util.List;
import db.ProductManagerDao;

public class ProductManager implements Serializable {

    private ProductManagerDao pmd;
    private List products;

    public void setProductManagerDao(ProductManagerDao pmd) {
        this.pmd = pmd;
    }

/*  
    public void setProducts(List p) {
        products = p;
    }
*/

    public List getProducts() {
        products = pmd.getProductList();
        return products;
    }

    public void increasePrice(int pct) {
        ListIterator li = products.listIterator();
        while (li.hasNext()) {
            Product p = (Product) li.next();
/*
            double newPrice = p.getPrice().doubleValue() * (100 + pct)/100;
            p.setPrice(new Double(newPrice));
*/
            pmd.increasePrice(p, pct);
        }
        
    }

}

We will no longer rely on the product list that we have in memory. Every time getProducts is called, we will go out and query the database. The increasePrice method will now delegate to the DAO to increase the price and the new price will be reflected the next time we call the getProducts method.

Next we need to modify the configuration file for the web application – springapp-servlet.xml.

springapp/war/WEB-INF/springapp-servlet.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
"http://www.springframework.org/dtd/spring-beans.dtd">

<!-- - Application context definition for "springapp" DispatcherServlet. --> <beans> <!-- Controller for the initial "Hello" page --> <bean id="springappController" class="web.SpringappController"> <property name="productManager"> <ref bean="prodMan"/> </property> </bean> <!-- Validator and Form Controller for the "Price Increase" page --> <bean id="priceIncreaseValidator" class="bus.PriceIncreaseValidator"/> <bean id="priceIncreaseForm" class="web.PriceIncreaseFormController"> <property name="sessionForm"><value>true</value></property> <property name="commandName"><value>priceIncrease</value></property> <property name="commandClass"><value>bus.PriceIncrease</value></property> <property name="validator"><ref bean="priceIncreaseValidator"/></property> <property name="formView"><value>priceincrease</value></property> <property name="successView"><value>hello.htm</value></property> <property name="productManager"> <ref bean="prodMan"/> </property> </bean> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName"><value>org.hsqldb.jdbcDriver</value></property> <property name="url"> <value>jdbc:hsqldb:/Users/trisberg/projects/springapp/db/test</value> </property> <property name="username"><value>sa</value></property> <property name="password"><value></value></property> </bean> <bean id="prodManDao" class="db.ProductManagerDaoJdbc"> <property name="dataSource"> <ref bean="dataSource"/> </property> </bean> <bean id="prodMan" class="bus.ProductManager"> <property name="productManagerDao"> <ref bean="prodManDao"/> </property> <!-- <property name="products"> <list> <ref bean="product1"/> <ref bean="product2"/> <ref bean="product3"/> </list> </property> --> </bean> <!-- <bean id="product1" class="bus.Product"> <property name="description"><value>Lamp</value></property> <property name="price"><value>5.75</value></property> </bean> <bean id="product2" class="bus.Product"> <property name="description"><value>Table</value></property> <property name="price"><value>75.25</value></property> </bean> <bean id="product3" class="bus.Product"> <property name="description"><value>Chair</value></property> <property name="price"><value>22.79</value></property> </bean> --> <bean id="messageSource" class="org.springframework.context.support.ResourceBundleMessageSource"> <property name="basename"><value>messages</value></property> </bean> <bean id="urlMapping" class="org.springframework.web.servlet.handler.SimpleUrlHandlerMapping"> <property name="mappings"> <props> <prop key="/hello.htm">springappController</prop> <prop key="/priceincrease.htm">priceIncreaseForm</prop> </props> </property> </bean> <bean id="viewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver"> <property name="viewClass"> <value>org.springframework.web.servlet.view.JstlView</value> </property> <property name="prefix"><value>/WEB-INF/jsp/</value></property> <property name="suffix"><value>.jsp</value></property> </bean> </beans>

I remove the population of a set of products that were passed in to the ProductManager. I replace this by adding beans for a DataSource and a ProductManagerDaoJdbc implementation. The URL provided for the datasource includes the full path for the database location (/Users/trisberg/projects/springapp/db/test). You will of course have to adjust this to match your setup.

Now we can build and deploy the modified application. Run 'ant undeploy deploy' to clean out any of the old classes and replace them with the new ones. Then fire up Tomcat and pull up the application. The only difference you should see is the decimals limited to two positions due to the column being declared that way in the database. Also, you should see any price increases remaining in effect even after you cycle the application server.


Step 26 – Fix the broken tests.

We have radically changed the ProductManager implementation – we pushed all the functionality down to the ProductManagerDao implementation, so now the old tests fail. To fix this I will create a mock implementation of the ProductManagerDao. This implementation will basically mimic the functionality we had in the original ProductManager.

springapp/src/tests/MockProductManagerDaoImpl.java

package tests;

import bus.Product;
import java.util.List;
import db.ProductManagerDao;
import bus.Product;

public class MockProductManagerDaoImpl implements ProductManagerDao {

    private List products;

    public void setProducts(List p) {
        products = p;
    }

    public List getProductList() {
        return products;
    }

    public void increasePrice(Product prod, int pct) {
        double newPrice = prod.getPrice().doubleValue() * (100 + pct)/100;
        prod.setPrice(new Double(newPrice));
    }

}

Now we have to make sure to configure the 'TestSpringappController' and 'TestProductManager' tests to use this mock implementation. For the 'TestSpringappController' I will modify the 'springapp-servlet.xml' file that I copied into the tests/WEB-INF directory (I knew it would be a good idea to make a copy of this file). Do not modify the one located in war/WEB-INF – we need that one to stay as is so we can run the application in Tomcat. We move the list of products from the ProductManager to the ProductManagerDao and give ProductManager a reference to this DAO.

springapp/src/tests/WEB-INF/springapp-servlet.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">

<!--
  - Application context definition for "springapp" DispatcherServlet.
  -->


<beans>
    <bean id="springappController" class="web.SpringappController">
        <property name="productManager">
            <ref bean="prodMan"/>
        </property>
    </bean>

    <bean id="prodManDao" class="tests.MockProductManagerDaoImpl">
        <property name="products">
            <list>
                <ref bean="product1"/>
                <ref bean="product2"/>
                <ref bean="product3"/>
            </list>
        </property>
    </bean>

    <bean id="prodMan" class="bus.ProductManager">
        <property name="productManagerDao">
            <ref bean="prodManDao"/>
        </property>
<!--
        <property name="products">
            <list>
                <ref bean="product1"/>
                <ref bean="product2"/>
                <ref bean="product3"/>
            </list>
        </property>
-->
    </bean>

    <bean id="product1" class="bus.Product">
        <property name="description"><value>Lamp</value></property>
        <property name="price"><value>5.75</value></property>
    </bean>
        
    <bean id="product2" class="bus.Product">
        <property name="description"><value>Table</value></property>
        <property name="price"><value>75.25</value></property>
    </bean>

    <bean id="product3" class="bus.Product">
        <property name="description"><value>Chair</value></property>
        <property name="price"><value>22.79</value></property>
    </bean>

</beans>        

For the TestProductManager test case we will make similar modifications to the setUp method.

springapp/src/tests/TestProductManager .java

package tests;

import java.util.List;
import java.util.ArrayList;
import junit.framework.TestCase;
import db.ProductManagerDao; import bus.ProductManager; import bus.Product; public class TestProductManager extends TestCase { private ProductManager pm; public void setUp() { pm = new ProductManager(); Product p = new Product(); p.setDescription("Chair"); p.setPrice(new Double("20.50")); ArrayList al = new ArrayList(); al.add(p); p = new Product(); p.setDescription("Table"); p.setPrice(new Double("150.10")); al.add(p); /* pm.setProducts(al); */ MockProductManagerDaoImpl pmdao = new MockProductManagerDaoImpl(); pmdao.setProducts(al); pm.setProductManagerDao(pmdao); pm.getProducts(); } public void testGetProducs() { List l = pm.getProducts(); Product p1 = (Product) l.get(0); assertEquals("Chair", p1.getDescription()); Product p2 = (Product) l.get(1); assertEquals("Table", p2.getDescription()); } public void testIncreasePrice() { pm.increasePrice(10); List l = pm.getProducts(); Product p = (Product) l.get(0); assertEquals(new Double("22.55"), p.getPrice()); p = (Product) l.get(1); assertEquals(new Double("165.11"), p.getPrice()); } }

Compile everything and run the tests. They should run successfully now.

There are still a few things I would like to improve upon. To begin with, we are not using a connection pool which is a must for any serious web application deployment. We are also using HSQL in a standalone mode, which is limiting us to one connection at a time. This of course will not scale. Another issue that we are not dealing with is concurrency. Two users could increase prices simultaneously and they would be a bit surprised by seeing the combined effect of their actions. I'll leave these improvements up to you for now – I might add them in an additional part to this document in the future.


Back


Copyright © 2003-2005, Thomas Risberg