For one of our Java projects, I wanted to help a colleague getting
started with unit testing. Due to high time pressure, there was a real
risk that – and we know it should not happen that way – unit tests
might be pushed backwards in time. The project is using the Spring
Framework in different places: Spring Core (IoC Container), Spring
JDBC/Spring DAO, Spring MVC in the WebTier and Spring with Acegi for
the security of the application. The unit testing initially has to deal
with the DAOs. These Data Access Objects are developed using Spring
JDBC though we consider – if we have another resource available –
moving to Spring Hibernate at some later point in time. To allow a
smooth transition, all DAO implementation classes are hidden behind DAO
interfaces; the Business Service that presents data services to the web
application is itself injected with the DAOs by the Spring Container.
We can replace the current JDBC based DAO Implementation Classes later
on with Spring Hibernate based implementations that implement the same
interface, leaving the Business Service none the wiser about this
switch.

So how to unit test these Spring JDBC driven DAO implementations?

I have set up a small demonstration. It consists of:

a single database table, ALS_PUBLISHERS
a
class Publisher – the Object representation mapped to the table- and
its companion DAO interface PublisherDao as well a an implementation
PublisherDaoJdbcImpl
a class TestPublisherDao that extends from JUnit TestCase and performs the unit tests for the PublisherDao under scrutiny
a
Spring Bean Configuration File that sets up a JDBC DataSource, the Dao
Implementation that we will test and injects both in the
TestPublisherDao class

I make use of Eclipse 3.1 for the demonstration, although any IDE or even no IDE would have worked.

In configuring the project, I add the following jars to the Build Path:

spring-1.2.5.jar (the jar containing most of the Spring Framework)
commons-logging-1.0.4.jar (used by Spring)
spring-mock-1.2.1.jar
(this library contains the class
AbstractDependencyInjectionSpringContextTests that extends JUnit’s
TestCase)
junit-3.8.1.jar (in order to set up and run Unit Tests)

The latter two libraries are required for the unit tests, the first two are part of the application.

The ALS_PUBLISHERS table is defined like this:

CREATE TABLE "ALS_PUBLISHERS"
(
"ID" NUMBER (10,0) NOT NULL,
"WEBSITE" VARCHAR2 (50),
"NAME" VARCHAR2 (50),
"COUNTRY" VARCHAR2 (2),
"LOCATION_IN_COUNTRY" VARCHAR2 (50),
"LOGO" BLOB,
  CONSTRAINT PBR_PK
  PRIMARY KEY ( ID ) 
)
;

The Publisher POJO that reflects this table looks like:

package nl.amis.als.domain;

public class Publisher {

	private Long id;
	private String name;
	private String website;

	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	
	public String getWebsite() {
		return website;
	}
	public void setWebsite(String website) {
		this.website = website;
	}
	
}

The Data Access Object interface that is defined for the Publisher domain class has been defined here:

package nl.amis.als.dao;

import java.util.List;

import nl.amis.als.domain.Publisher;

public interface PublisherDao {

    public List selectAllPublishers();
    
    public Publisher findPublisher(Long id);

    public void insertPublisher(Publisher pub);
    
    public void deletePublisher(Publisher pub);
    public void deletePublisher(Long id);
}

Granted,
this interface is relatively poor. We could define many more query
methods as well as an update operation. For this introduction into DAO
Unit testing with the Spring framework it does not really matter.

We
could have implemented the DAO interface in several ways, using
frameworks such as iBatis, TopLink or HIbernate or using Spring JDBC.
In this case, we have opted for the latter: plain JDBC or rather JDBC
using the Spring Templates that take care of much plumbing code like
TCTCF (try-catch-try-catch-finally). The class PublisherDaoJdbcImpl
extends the Spring JdbcDaoSupport class and implements the PublisherDao
interface. We see the interface methods implemented with relatively few
lines of code: most of the JDBC plumbing is found in the Spring
JdbcDaoSupport class.

package nl.amis.als.dao.jdbc;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import nl.amis.als.dao.PublisherDao;
import nl.amis.als.domain.Publisher;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultReader;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

public class PublisherDaoJdbcImpl extends JdbcDaoSupport implements
		PublisherDao {

	 public List selectAllPublishers() {
	        JdbcTemplate jt = getJdbcTemplate();
	        return jt.query ( "select id, name, website from als_publishers"
	                        , new RowMapperResultReader(new PublisherRowMapper())
	                        );
	    }//getAllPublishers
	        
	    // this inner class is invoked for each Row om the ResultSet. It implements
	    // the Spring RowMapper interface, that is used to convert ResultSet records
	    // to Domain Objects. RowMappers are used when the results are not simple
	    class PublisherRowMapper implements RowMapper {
	      public Object mapRow(ResultSet rs, int index) throws SQLException {
	        Publisher pub = new Publisher();
	        pub.setId(new Long(rs.getLong(1)));
	        pub.setName(rs.getString(2));
	        pub.setWebsite(rs.getString(3));
	        return pub;
	      }      
	    }//class PublisherRowMapper
	

	public Publisher findPublisher(Long id) {
		JdbcTemplate jt = new JdbcTemplate(super.getDataSource());
		Publisher pub = new Publisher();
		Object[] parameters = new Object[] { id };
		List l = jt.queryForList(
				"select id, name, website from als_publishers where id = ?",
				parameters);

		Iterator iter = l.iterator();
		while (iter.hasNext()) {
			Map m = (Map) iter.next();
			pub.setId(new Long(m.get("ID").toString()));
			pub.setName((String) m.get("NAME"));
			pub.setWebsite((String) m.get("WEBSITE"));
		}
		return pub;
	}

	public void insertPublisher(Publisher pub) {
		JdbcTemplate jt = new JdbcTemplate(super.getDataSource());
		Object[] parameters = new Object[] { pub.getId(), pub.getName(),
				pub.getWebsite() };
		int rows = jt.update("insert into als_publishers (id, name, website)"
				+ " values ( ?, ?, ?)", parameters);
	}
	public void deletePublisher(Publisher pub) {
		deletePublisher(pub.getId());
	}

	public void deletePublisher(Long id) {
		JdbcTemplate jt = new JdbcTemplate(super.getDataSource());
		Object[] parameters = new Object[] { id};
		int rows = jt.update("delete from als_publishers where id = ?", parameters);
	}

}

Notice
that while we cannot see it in this class, it still requires a
DataSource to be injected. The setDataSource() method is in the super
class JdbcDaoSupport.

With the DAO Interface in hand, we can
already start writing the Unit Test for the DAO. This test class
extends from the class AbstractDependencyInjectionSpringContextTests
from the Spring Mock library. This class extends JUnit TestCase. It
implements the SetUp() method – as a final method. However, it provides
a hook into this SetUp() through the onSetUp() method that we can
override in our own class; this is another example of the Template
Pattern. The AbstractDependencyInjectionSpringContextTests class
contains an abstract method protected String[] getConfigLocations()
that we must implement. We have this method return a String array with
the names of all Bean Configuration files that should be for injecting
dependencies. The second action performed by this class is so-called
auto-wiring: when our TestCase, extending from
AbstractDependencyInjectionSpringContextTests, is run as JUnit Test,
the class will be injected with dependencies- all setter methods in the
test class that correspond with the bean names in the specified Bean
Configuraton files are injected with the beans as produced by the IoC
Container.

We make use of this feature to have the Publisher DAO Implementation injected.

Our
test class is very simple, again I am mainly trying to demonstrate the
principles. It tests the selectAllPublishers() implementation, assuming
that it must return at least one record. It also attempts to test the
insertPublisher(), findPublisher() and deletePublisher() methods.

package nl.amis.als.dao.test;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.List;

import javax.sql.DataSource;

import nl.amis.als.dao.PublisherDao;
import nl.amis.als.domain.Publisher;

import org.springframework.test.AbstractDependencyInjectionSpringContextTests;

public class PublisherDaoTest extends AbstractDependencyInjectionSpringContextTests {

	
	public void testFindAllPublishers() {
          List l = publisherDao.selectAllPublishers();
          // there should be more than one Publisher returned
          assertTrue(l.size()>0);
	}

	public void testCreateAndFindPublisher() {
        Publisher pub = new Publisher();
        pub.setId(new Long(133));
        pub.setName("My New Publisher");
        pub.setWebsite("my.web.com");
        publisherDao.insertPublisher(pub);
        Publisher pub2 = publisherDao.findPublisher(new Long(133));
        assertEquals(pub2.getName(),pub.getName());
	publisherDao.deletePublisher(pub2);
        Publisher pub3 = publisherDao.findPublisher(pub2.getId());
        assertNull(pub3.getId() );
		
	}

	
    // specify the BeanConfigurationFiles to use for auto-wiring the properties of this class
	protected String[] getConfigLocations() {
	        return new String[]{"AlsTestContext.xml"};
	    }

    // this private property is injected through the setter from the BeanConfigurationFile
	// specified in getConfigLocations()
	private PublisherDao publisherDao;
	public PublisherDao getPublisherDao() {
		return publisherDao;
	}

	public void setPublisherDao(PublisherDao publisherDao) {
		this.publisherDao = publisherDao;
	}

}

The one thing still lacking for running our Unit Test is the Bean Configuration file. Here it is:

<?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>
	<!-- datasource configuration:
		View context.xml and server.xml for more datasource configuration -->
	<bean id="dataSourceDBDirect"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource"
		destroy-method="close">
		<property name="driverClassName"
			value="oracle.jdbc.driver.OracleDriver" />
		<property name="url"
			value="jdbc:oracle:thin:@localhost:1521:orcl" />
		<property name="username" value="als" />
		<property name="password" value="als" />
	</bean>

	<!--  resource bundle location 
	-->
	<bean id="messageSource"
		class="org.springframework.context.support.ResourceBundleMessageSource"
		abstract="false" singleton="true" lazy-init="default"
		autowire="default" dependency-check="default">
		<property name="basename">
			<value>messages</value>
		</property>
	</bean>

	<!-- ********** JDBC DAOs ********** -->

	<bean id="publisherDao"
		class="nl.amis.als.dao.jdbc.PublisherDaoJdbcImpl">
		<property name="dataSource">
			<ref local="dataSourceDBDirect" />
		</property>
	</bean>

</beans>

Note that normally our application would be getting a DataSource
from the Web/Servlet Container in which our application runs. However,
since we test outside the container, we inject our own DataSource,
produced by the Spring class
org.springframework.jdbc.datasource.DriverManagerDataSource. Note how
the setPublisherDao method in our test class corresponds with the
publisherDao bean set up in this configuration file.

Controlling the Test Data Set using DBUnit

In
order to be able to write tests based on the actual data set in the
table, we need to control the data in the table. For this, we will use DBUnit.
DBUnit can be used to construct tests on the contents of database
tables. However, its importance to us right now lies in DBUnit’s
functionality for controlling the data in the tables: ” DbUnit is a JUnit extension (also usable with Ant) targeted for database-driven projects that,
among other things, puts your database into a known state between test runs. This is an excellent
way to avoid the myriad of problems that can occur when one test case corrupts the database and
causes subsequent tests to fail or exacerbate the damage.”

In
order to set up DBUnit in my project, I need to download dbunit-2.1.jar
as well well commons-io-1.0.jar. I add them both to the Build Path as
external archive.

DBUnit can load the test data set into the
target tables from for example an XML File. Later we will see how this
data is loaded by DBUnit in the setup stage of our Unit Test. We will
first see how we can use DBUnit to create the XML file from extracting
data from the table. The class

package nl.amis.als.dao.test;

import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;

import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.DefaultDataSet;
import org.dbunit.dataset.ITable;
import org.dbunit.dataset.filter.DefaultColumnFilter;
import org.dbunit.dataset.xml.FlatXmlDataSet;

public class ExtractAlsTestData {

	public static void main(String[] args) throws Exception {
		// database connection
		Class driverClass = Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection jdbcConnection = DriverManager.getConnection(
				"jdbc:oracle:thin:@localhost:1521:orcl", "als", "als");
		IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);

		// add the table ALS_PUBLISHERS for data extraction
		ITable tbl = connection.createQueryTable("als_publishers",
				"SELECT * FROM als_publishers ");
		// exclude column LOGO from the data extraction
		ITable filteredTable = DefaultColumnFilter.excludedColumnsTable(tbl,
				new String[] { "LOGO" });
		// create a dataset with table ALS_PUBLISHERS
		DefaultDataSet partialDataSet = new DefaultDataSet();
		partialDataSet.addTable(filteredTable);
		// have DBUnit write the table data to an XML file
		FlatXmlDataSet.write(partialDataSet, new FileOutputStream(
				"als-publishers-dataset.xml"));
	}
}

This
class will generate the file als-publishers-dataset.xml. We have taken
care not to include the LOGO column of type BLOB. Not that DBUnit
cannot handle it, it just sort of messes up our xml file and we will
not do any testing on the binary data in this column. It took some time
to find out how to use the Column Filters (see DBUnit – how to exclude some columns at runtime). Note that it would be easy to add additional tables from the ALS Schema to this extraction class. Also note that article DBUnit Made Easy
explains a way to write a Spring based utility class that can easily be
configured and run for extracting data. The resulting file –
als-publishers-dataset.xml – looks like this:

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
  <als_publishers ID="16" WEBSITE="www.evengreaterbooks-inc.com" NAME="My Publishing House"/>
  <als_publishers ID="2" WEBSITE="www.wrox.com" NAME="Wrox (label of Wiley Publishing)" LOCATION_IN_COUNTRY="Indianapolis (IN)"/>
  <als_publishers ID="3" WEBSITE="www.wiley.com2" NAME="Wiley and Sons and grandsons" LOCATION_IN_COUNTRY="New York (NY)"/>
  <als_publishers ID="4" WEBSITE="http://www.manning.com" NAME="Manning Publications Co." COUNTRY="us"/>
  <als_publishers ID="5" WEBSITE="http://java.sun.com" NAME="SUN Microsystems"/>
  <als_publishers ID="6" WEBSITE="http://books.mcgraw-hill.com" NAME="McGrawHill" COUNTRY="nl"/>
  <als_publishers ID="1" WEBSITE="www.oreilly.com" NAME="O&apos;Reilly" COUNTRY="uk" LOCATION_IN_COUNTRY="Sebastopol, CA"/>
  <als_publishers ID="15" WEBSITE="www.greatbooks-inc.com" NAME="My Publishing House"/>
</dataset>

Now
we are going to benefit from this dataset in our Test Class. We do so
by overriding the onSetUp() method that is defined in the
AbstractDependencyInjectionSpringContextTests superclass.

import org.dbunit.database.DatabaseDataSourceConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.operation.DatabaseOperation;

	 protected void onSetUp() throws Exception {
		super.onSetUp();
		// we will use DBUnit to prepare the database with a proper dataset

		// initialize your database connection here
		IDatabaseConnection connection = new DatabaseDataSourceConnection(
				dataSourceDBDirect, "ALS");
		// initialize your dataset here, from the file containing the test
		// dataset
		IDataSet dataSet = new FlatXmlDataSet(new FileInputStream(
				"als-publishers-dataset.xml"));

		try {
			DatabaseOperation.CLEAN_INSERT.execute(connection, dataSet);
		} finally {
			connection.close();
		}
	}

The
CLEAN_INSERT operation will first remove all data from the target
table(s) and then load the data from the specified dataset. This
dataset is created from the als-publishers-dataset.xml.

We also
need to inject the DataSource into the TestClass as DBUnit needs the
DataSource to load the data from the XML file into the database:

    private DataSource dataSourceDBDirect;
    private PublisherDao publisherDao;
    
    public void setDataSourceDBDirect(DataSource dataSourceDBDirect) {
    	this.dataSourceDBDirect = dataSourceDBDirect;
    }   
	   
	public PublisherDao getPublisherDao() {
		return publisherDao;
	}

	public void setPublisherDao(PublisherDao publisherDao) {
		this.publisherDao = publisherDao;
	}

The dataSourceDBDirect bean is already set up because the DataSource needs to be injected in the DAO Implementation.

At
this point we can start writing unit tests that make assumptions on the
data in the ALS_PUBLISHERS table – as we know how the data has been set
up. For example:

	public void testFindPublisher() {
        Publisher pub2 = publisherDao.findPublisher(new Long(6));
        assertEquals(pub2.getName(),"McGrawHill");
        assertEquals(pub2.getWebsite(),"http://books.mcgraw-hill.com");
		
	}

Resources

Introducing Spring JDBC – frequently the best introduction of Spring in an organization – AMIS Technology Weblog (September 2005)

DBUnit homepag

Effective Unit Testing with DbUnit by Andrew Glover (january 2004) – ONJava.com

DBUnit – how to exclude some columns at runtime

DBUnit Made Easy by Bill Siggelkow Oct. 13, 2005, O’Reilly Network Weblogs

Testing with the Spring Framework – Chapter 23 of the reference documentation

Control your test-environment with DbUnit and Anthill Philippe Girolami 13 Apr 2004, IBM Developer Works

Getting started with Unit Testing of JDBC based DAOs with the Spring Framework (introducing DBUnit)

Controlling the Test Data Set using DBUnit

Resources

Like this:

About The Author

Lucas Jellema

One Response

Controlling the Test Data Set using DBUnit

Resources

Share this:

Like this:

Related Posts

About The Author

Lucas Jellema

One Response