DbUnit – Test and populate database with DbUnit, Hibernate and Spring

In enterprise Java applications development we need to test applications with unit tests. Usually, running a test one time can change database data. This can block running the test a second time because of integrity constraints. There are many solutions to solve this problem like:

  • Running SQL script.
  • Restoring a database.
  • Creating and populating a database with test data.
   

Photo credit: Marta

I think there isn’t a better solution, but some of them are more appropriate for some kind of projects.

Creating a script for initializing database is good solution for project using a big database and which need a large range of data to run significant test, because creating or restoring a database can take a lot of time and machine resources. But it can be complicated to create the script because we need to respect data dependency and to have someone in a team who has good SQL knowledge.

Restoring a data base is a good solution for a project with a stable database schema. A frequent database changes costs a lot of time. At every change, we need to restore a database, to make changes and to create a new backup before running application tests

Creating and populating a database is used for testing applications with small database and for new application developments (Development from scratch). This can be done by creating SQL scripts and running them before executing tests. There is more elegant solution to create and populate database which are based on an ORM like Hibernate and a database testing framework like DbUnit.

DbUnit is a JUnit extension (also usable with Ant) targeted at 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. [DbUnit web site]

In this post I will present one way of creating, populating and testing a Java application by using Hibernate JPA, Spring and DbUnit.

1 – Creating a Hibernate JPA Project with Spring

In this article I use the same project created in the article Using Hibernate JPA in Spring. You can browse the project in this link or you can checkout the project code from Google code SVN repository from this URL https://project4example1.googlecode.com/svn/trunk. After you check out the project into your IDE, the project will be structured as shown in the picture.

To add DbUnit in your project, you need to add “dbUnit.jar” to the project library. If you are using Maven you need to add DbUnit dependency in your pom.xml as shown in the code source bellow.

<dependency>
	<groupId>org.dbunit</groupId>
	<artifactId>dbunit</artifactId>
	<version>2.4.7</version>
	<scope>test</scope>
</dependency>
   

2 – Populate the database with DbUnit

The code source bellow presents the content of the FlatXmlDataSet.xml used in this project. The structure of the file is easy to understand; Each XML element corresponds to a table row. Each XML element name corresponds to a table name. The XML attributes correspond to table columns.

<!DOCTYPE dataset SYSTEM "src/test/resources/my-dataset.dtd">
<dataset>
	<customer customer_id="1" tax_id="2" name="name1" adresse="adresse1"
		city="Paris" state="Paris" zip="75001" phone="0606060606" />
	<customer customer_id="2" tax_id="3" name="name2" adresse="adresse2"
		city="Paris" state="Paris" zip="75002" phone="0606060607" />
	<customer customer_id="3" tax_id="4" name="name3" adresse="adresse3"
		city="Paris" state="Paris" zip="75003" phone="0606060608" />

	<customer_order order_id="1" customer_id="1"
		date_placed="2008-01-01" date_promised="2008-02-02" terms="terms"
		status="done" />
	<customer_order order_id="2" customer_id="2"
		date_placed="2009-02-02" date_promised="2009-04-20" terms="terms"
		status="done" />
	<customer_order order_id="3" customer_id="2"
		date_placed="2010-03-03" date_promised="2010-05-24" terms="terms"
		status="done" />
	<customer_order order_id="4" customer_id="3"
		date_placed="2008-04-04" date_promised="2008-07-05" terms="terms"
		status="done" />
</dataset>

To control data format you can add DTD file. This file is optional if you don’t want to use a DTD file you have to replace the first line of your flat XML dataset by <?xml version='1.0' encoding='UTF-8'?>.

<!ELEMENT dataset (
    customer*,
    customer_order*)>

<!ELEMENT customer EMPTY>
<!ATTLIST customer
	customer_id  CDATA #REQUIRED
	tax_id  CDATA #REQUIRED
	name  CDATA #REQUIRED
	adresse  CDATA #REQUIRED
	city  CDATA #REQUIRED
	state  CDATA #REQUIRED
	zip  CDATA #REQUIRED
	phone  CDATA #REQUIRED   
>

<!ELEMENT customer_order EMPTY>
<!ATTLIST customer_order
	order_id CDATA #REQUIRED
	customer_id CDATA #REQUIRED
	date_placed CDATA #REQUIRED
	date_promised CDATA #REQUIRED
	terms CDATA #REQUIRED
	status CDATA #REQUIRED
>

The code source bellow presents a jUnit test used to test a DAO. Before test execution, the database will be created by Hibernate and it will be populated by DbUnit with XML dataset. In the “setup” method CLEAN_INSERT operation is executed; it deletes all rows in tables and insert data from the data set.

package com.mycompany.test.dao;

import java.io.InputStream;
import java.sql.Connection;
import java.util.Collection;

import junit.framework.TestCase;

import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.operation.DatabaseOperation;
import org.hibernate.SessionFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.orm.hibernate3.SessionFactoryUtils;

import com.mycompany.dao.ICustomerDao;
import com.mycompany.entity.Customer;

public class CustomerDaoTest extends TestCase {

	private static final String[] LOCATIONS = { "application-context.xml" };
	private static final String FLAT_XML_DATASET = "FlatXmlDataSet.xml";
	private ApplicationContext context;
	private ICustomerDao iCustomerDao;


	@Override
	protected void setUp() throws Exception {
		super.setUp();
		context = new ClassPathXmlApplicationContext(LOCATIONS);
		iCustomerDao = (ICustomerDao) context.getBean("customerDao");
		DatabaseOperation.CLEAN_INSERT.execute(getConnection(), getDataSet());

	}

	public void testGetAllCustomers() {
		Collection<Customer> listCustomers = iCustomerDao.getAll();
		assertFalse(listCustomers.isEmpty());
	}
	
	public void testSaveCustomer() {
		Collection<Customer> listCustomers1 = iCustomerDao.getAll();
		Customer customer = new Customer(1, "name","adresse", "city", "state", "123", "0606060606", null);
		iCustomerDao.save(customer);
		Collection<Customer> listCustomers2 = iCustomerDao.getAll();
		assertEquals(listCustomers2.size() - listCustomers1.size(), 1);
	}
	

	private IDataSet getDataSet() throws Exception {
		InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream(FLAT_XML_DATASET);
		IDataSet dataset = new FlatXmlDataSet(inputStream);
		return dataset;
	}

	private IDatabaseConnection getConnection() throws Exception {
		SessionFactory sessionFactory = (SessionFactory) context.getBean("sessionFactory");
		Connection jdbcConnection = SessionFactoryUtils.getDataSource(sessionFactory).getConnection();
		IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);
		return connection;
	}
}


To test java applications we need to know data at initial state to predict data evolution and to make JUnit assertion. DbUnit gives a smart solution to populate a database. DbUnit is dedicated for applications tests, but I think it could be useful in others contexts like application deployment when we need to fill reference data.

Advertisements

Using Hibernate JPA in Spring

JPA (Java Persistence API) provides a good ORM (Objet Relational Mapper) solution. It has a large success because it’sa standard and it’s implemented by different vendor.

Hibernate Implements JPA specification. Using this implementation has the benefit of avoiding hbm.xml mapping files and to use annotations metatdata to specify the mapping between objects and relational database tables.

This article will show by a project example how to persist data with Hibernate JPA implementation and Spring Framework.

   
Photo Credit : tanakawho

The picture bellow presents the project structure and the different files created. If you are using maven you need to add dependency as shown in the picture, otherwise you have to download jars from Springsource and Hibernate web sites and to manage dependencies.

   

1 – Hibernate JPA configuration
The JPA interface needs a configuration file named persistence.xml in the application’s META-INF directory. The sourcecode bellow present the persistence.xml file used in this project.

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence 
http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
    <persistence-unit name="acme" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <class>com.mycompany.entity.CustomerOrder</class>
        <class>com.mycompany.entity.Customer</class>
        <properties>
            <property name="hibernate.hbm2ddl.auto" value="create-drop"/>
        </properties>
    </persistence-unit>
</persistence>

JPA persist a simple POJO Classe with some metatdata wich are specified in annotations. For more information about JPA annottion, see Chapter 8 “Metadata Annotations” of the JPA Specification . Here is an example data class:

import java.io.Serializable;
import java.util.List;
import javax.persistence.*;
import org.hibernate.annotations.GenericGenerator;

@Entity
@Table(name = "Customer")
public class Customer implements Serializable {

    public Customer() {super();}

public Customer(Integer taxId, String name, String adresse, String city, 
String state, String zip, String phone, List<CustomerOrder> CustomerOrders) {
        this.taxId = taxId;
        this.name = name;
        this.adresse = adresse;
        this.city = city;
        this.state = state;
        this.zip = zip;
        this.phone = phone;
        this.CustomerOrders = CustomerOrders;
    }

    @Id
    @GenericGenerator(name = "generator", strategy = "increment")
    @GeneratedValue(generator = "generator")
    @Column(name = "customer_id", nullable = false)
    private Long customerId;

    @Column(name = "tax_id", nullable = false)
    private Integer taxId;

    @Column(name = "name", nullable = false)
    private String name;

    @Column(name = "adresse", nullable = false)
    private String adresse;

    @Column(name = "city", nullable = false)
    private String city;

    @Column(name = "state", nullable = false)
    private String state;

    @Column(name = "zip", nullable = false)
    private String zip;

    @Column(name = "phone", nullable = false)
    private String phone;

    @OneToMany
    private List<CustomerOrder> CustomerOrders;

    // Add getters and setters
}

2 – Using JPA in Spring
In this part we explain how to integrate Hibernate JPA implementation into Spring.
The source code bellow present the application context used to configure Hibernate JPA into Spring Framework.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:flow="http://www.springframework.org/schema/webflow-config"
       xmlns:lang="http://www.springframework.org/schema/lang"
       xmlns:osgi="http://www.springframework.org/schema/osgi"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:util="http://www.springframework.org/schema/util"
       xmlns:p="http://www.springframework.org/schema/p"

       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
          http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
          http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
          http://www.springframework.org/schema/webflow-config http://www.springframework.org/schema/webflow-config/spring-webflow-config-2.0.xsd
          http://www.springframework.org/schema/lang http://www.springframework.org/schema/lang/spring-lang-3.0.xsd
          http://www.springframework.org/schema/osgi http://www.springframework.org/schema/osgi/spring-osgi-3.0.xsd
          http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
          http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.0.xsd
">
    
	<context:annotation-config/>
    <context:component-scan base-package="com.mycompany"/>
    

    <bean id="entityManagerFactory"
     class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="persistenceXmlLocation" value="persistence.xml"  />
        <property name="jpaVendorAdapter">
            <bean
        class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
                <property name="showSql" value="true" />
                <property name="generateDdl" value="true" />
                <property name="databasePlatform" value="org.hibernate.dialect.HSQLDialect" />
            </bean>
        </property>
    </bean>

    <bean id="dataSource"
     class="org.apache.commons.dbcp.BasicDataSource"
     destroy-method="close">
        <property name="driverClassName" value="org.hsqldb.jdbcDriver" />
        <property name="url" value="jdbc:hsqldb:mem:test" />
        <property name="username" value="sa" />
        <property name="password" value="" />
    </bean>

	<tx:annotation-driven />
    <bean id="transactionManager"
     class="org.springframework.orm.jpa.JpaTransactionManager" >
        <property name="entityManagerFactory" ref="entityManagerFactory"  />
    </bean>
      

</beans>

The code source bellow present the customer DAO.

package com.mycompany.dao.hibernate;

import java.util.Collection;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.mycompany.dao.ICustomerDao;
import com.mycompany.entity.Customer;

@Repository
public class CustomerDao implements ICustomerDao{
	
	@PersistenceContext
	private EntityManager em;

    public Collection<Customer> getAll() {
    	Query query = em.createQuery("from Customer");
        return query.getResultList();
    }
    
    public Collection<Customer> findByName(String name) {
    	Query query = em.createQuery("from Customer as c where c.name like :name");
    	query.setParameter("name", name);
        return query.getResultList();
    }

    public Customer getById(Long id) {
        return em.find(Customer.class, id);
    }

    @Transactional
    public void save(Customer customer) {
        em.persist(customer);
    }

    @Transactional
    public void delete(Long id) {
    	Customer customer = em.find(Customer.class, id);  
    	em.remove(customer);
    }
}