Database Integration Testing

Now it is getting interesting. In the previous tutorial we tested just our persistence logic layer. We tested everything down to the JDBC driver but we did not test connecting and interacting with a real database. Unless we test against a real database, we will have no idea whether or not our persistence code actually works as expected. That is what we will find out with integration testing.

What is integration Testing?

As defined in the wiki, Integration Testing is the phase in software testing in which individual software modules are combined and tested as a group. It occurs after unit testing and before validation testing. Integration testing takes as its input modules that have been unit tested, groups them in larger aggregates, applies tests defined in an integration test plan to those aggregates, and delivers as its output the integrated system ready for system testing.

Ok, that sounds complicated. I am not going to cover integration testing in depth here (sorry!) but just show you how to do one simple integration test: connecting our unit tests, which test persistence, to the database. A few questions should come to mind when setting this up. What database should we use, a dedicated test database or the production database? How do we set up the “state” of the database? How do we clean up the data when the tests are finished running? And here is a more obvious question. How do we connect to the database outside of our web application?

Connecting to the database for unit testing

I am including example code to help you get up and running with a special hibernate configuration that will make it possible to run your unit tests independently of your web application. We have managed to connect our web application to the database already, so we know something about configuring Hibernate. But now we will see that the Hibernate configuration is even easier for unit tests!

To answer the other questions, I have decided to create a dedicated test database to keep my production database safe and clean from test data. I would always recommend this. Don’t run tests on your production database unless you absolutely, absolutely have no other choice.

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        <!-- Database connection settings -->
        <property name="connection.driver_class">org.hsqldb.jdbcDriver</property>
        <property name="connection.url">jdbc:hsqldb:hsql:testdb</property> 
        <property name="connection.username">sa</property>
        <property name="connection.password"></property>
        <!-- JDBC connection pool (use the built-in) -->
        <property name="connection.pool_size">1</property>
        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.HSQLDialect</property>
        <!-- Enable Hibernate's automatic session context management -->
        <property name="current_session_context_class">thread</property>
        <!-- Disable the second-level cache  -->
        <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>
        <!-- Drop and re-create the database schema on startup -->
        <property name="">create</property>

Preparing the database for testing

Now how does this work, exactly? Well, assigning the “” property to “create” means that every time you run your tests, Hibernate will create new tables for you. In our case, we will only be dealing with one table called GREETINGS. So, this will be easy. The next time we run our tests, Hibernate will drop and recreate the table(s) for us. Therefore we do not need to worry about running our tests on a clean database. In this case, we already have one!

But this is a simple example. In reality, preparing the database for testing is never that simple. You might need to insert several records in several different tables just to prepare one test. Not to mention the challenge of an evolving application and database with code changes from each new release. These are all things to keep in mind when planning your database integration testing. But the good news is that there is a great framework out there called DbUnit, which makes all these issues, especially data preparation, a lot easier. This tutorial is just an introduction, so I won’t be covering DbUnit here just yet.

Database cleanup

One technique often used to clean up the database after unit tests are finished is by calling the rollback method. The easiest way to do that is by calling this in the method “tearDown()”. That guarantees that each test leaves behind no data and after each test is run, all transactions on the database are rolled back. There are other ways to ensure database cleanup of course, so again, have a look at the DbUnit framework.

What to test

Our application is quite simple so that means that the tests are simple as well. Let’s take a moment and think about how our persistence code should behave. What should it do? Remember, we are not testing methods. We are testing what our application should do.

Here is a list I have made of three things I could think of. Maybe you can think of some additional tests as well, or a way to improve these tests.

1. When there are no Greetings in the database, the application should not crash. The method call for the greetings list should return an empty list. Let’s make a test method called testNoGreetingsInDBShouldResultInEmptyList().

2. When we save one Greeting, it should be persisted to the database. When we read the Greeting, we should get the result that we expect. Let’s create a test method called testSaveOneGreetingAndReadShouldBeSuccessful().

3. We shouldn’t have any problems saving multiple Greetings. Let’s create a test method called testSaveThreeGreetingsAndReadShouldBeSuccessful().

package com.bitbybit.dao;
import java.util.Date;
import java.util.List;
import junit.framework.TestCase;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.cfg.Configuration;
import org.hibernate.classic.Session;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.bitbybit.domain.Greeting;
public class HibernateGreetingDaoIntegrationTest extends TestCase {
	private SessionFactory sessionFactory; 
	private Session session; 
	HibernateGreetingDao hibernateGreetingDao;
	private SessionFactory getSessionFactory() throws Exception { 
		return createConfiguration().buildSessionFactory();
	private Configuration createConfiguration() throws Exception { 
		Configuration cfg = loadConfiguration(); 
		return cfg;
	private Configuration loadConfiguration() { 
		return new AnnotationConfiguration()
			.configure(new File("/Users/altheaparker/Documents/workspace/SpringGreetings/src/test/resources/hibernatetest.cfg.xml"));
	protected void setUp() throws Exception {
		sessionFactory = getSessionFactory();
		session = sessionFactory.getCurrentSession();
		hibernateGreetingDao = new HibernateGreetingDao();
	protected void tearDown() throws Exception {
	public void testNoGreetingsInDBShouldResultInEmptyList() throws Exception {
		List<Greeting> actualGreetingsList = hibernateGreetingDao.getAllGreetings();
		assertEquals(actualGreetingsList.size(), 0);	
	public void testSaveOneGreetingAndReadShouldBeSuccessful() throws Exception {
		Greeting expectedGreeting = new Greeting("Hello! Inserting the 1st greeting...", new Date(), "altheaparker");
		List<Greeting> actualGreetingsList = hibernateGreetingDao.getAllGreetings();
		assertEquals(actualGreetingsList.size(), 1);	
	public void testSaveThreeGreetingsAndReadShouldBeSuccessful() throws Exception {
		Greeting expectedGreeting1 = new Greeting("Hello! Inserting the 1st greeting...", new Date(), "altheaparker");
		Greeting expectedGreeting2 = new Greeting("Hello! Inserting the 2nd greeting...", new Date(), "johnnyd");
		Greeting expectedGreeting3 = new Greeting("Hello! Inserting the 3rd greeting...", new Date(), "sonialawson");
		List<Greeting> actualGreetingsList = hibernateGreetingDao.getAllGreetings();
		assertEquals(3, actualGreetingsList.size());	
		//the list is sorted correctly (order by id desc)
		assertEquals(expectedGreeting3.getId(), actualGreetingsList.get(0).getId()); 

So there we are. I’m sure your unit tests will be much better than these, but I hope I at least got you started and on your way to understanding how all this stuff works. Testing can be even more challenging than writing actual application code, mostly because it is not so clear which tests are right and which tests are wrong. At least with application code, you can say it either works or it doesn’t work. With testing however, you might always have the doubt as to whether you tested everything that could possibly go wrong, and everything that must go right. So that might keep you up at night. But don’t despair, just use your head and always think about what needs to be tested. Your gut should tell you if you are writing a useless test. And, practice makes perfect. :)

Thanks for reading and stay tuned for more upcoming chapters….

1 Comment
manu wrote on 2012-11-26 at 18:10:12:
wow.....amwesome post!! I was searching for this kind of solution and i luckily found one...

Post a Comment