Use Groovy to assign test data with DbUnit

DbUnitWithGroovy.md

DbUnit with Groovy

This little extension to DbUnit allows you to write the test data using Groovy lists and maps rather than XML or Excel. That way you can keep your test data in the same file as your db integration test, which will be easier to grok and maintain.

GroovyDataset is the DbUnit extension that you need to put in your project. GroovyDatasetTest is the unit test for it. UserIntegrationTest is an example, where the "data" attribute is the test data that is inserted into the database. (In real life, you'd create a superclass and move the SessionFactory, the definition of the data field, the setup() method, etc. there).

This was all described in a blog post: http://www.jroller.com/kenwdelong/entry/groovy_based_dbdeploy_tests

For how to use it, see the UserIntegrationTest below. You can specify the data for the test in a List of Maps (see the "data" field).

You can generally pull the doData() method and it's friends out into a superclass; I've condensed it all here for ease of reading.

GroovyDataset

package com.kendelong.integration

import org.dbunit.dataset.Column;
import org.dbunit.dataset.DataSetException 
import org.dbunit.dataset.DefaultTable;
import org.dbunit.dataset.DefaultTableIterator;
import org.dbunit.dataset.DefaultTableMetaData;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ITable;
import org.dbunit.dataset.ITableIterator;
import org.dbunit.dataset.ITableMetaData;
import org.dbunit.dataset.datatype.DataType;

import com.lowagie.text.Row;

class GroovyDataset implements IDataSet
{
	private List data;
	
	public GroovyDataset(List data)
	{
		this.data = data;	
	}
	/**
	* Returns names of tables in this dataset in proper sequence. Multiple
	* occurrence of the same name may be returned if multiple tables having
	* the same name are present in the dataset.
	*/
   public String[] getTableNames() throws DataSetException
   {
	   List names = data.collect { it['table'] }
	   names.unique()
	   return names
   }

   public ITableMetaData getTableMetaData(String tableName)
		   throws DataSetException
   {
	   // Inspired by XlsTable
	   def columns = []
	   def meta = null
	   data.each
	   {
		   row ->
		   if(meta) return
		   if(row.table == tableName)
		   {
			   row.each
			   {
				   columnName, columnValue ->
				   if(columnName == 'table') return
				   columns << new Column(columnName, DataType.UNKNOWN)
			   }
			   meta = new DefaultTableMetaData(tableName, columns as Column[])
		   }
	   }
   	   return meta
   }

   /**
	* Returns the specified table.
	*
	* @throws AmbiguousTableNameException if dataset contains multiple tables
	*      having the specified name. Use {@link #iterator} to access
	*      to all tables.
	* @throws NoSuchTableException if dataset do not contains the specified
	*      table
	*/
   public ITable getTable(String tableName) throws DataSetException
   {
	   def table = new DefaultTable(getTableMetaData(tableName))
	   data.each
	   {
		   row ->
		   if(row.table == tableName)
		   {
			   def values = []
			   row.each
			   {
				   columnName, columnValue ->
				   if(columnName == 'table') return
				   values << columnValue
			   }
			   table.addRow(values as Object[])
		   }
	   }
	   return table
   }

   /**
	* Returns tables in this dataset in proper sequence. Multiple tables having
	* the same name but different data may be returned.
	*
	* @deprecated Use {@link #iterator} or {@link #reverseIterator} instead.
	*/
   public ITable[] getTables() throws DataSetException
   {
	   def tableList = getTableList()
	   return tableList as ITable[]
   }
	
	private List getTableList() 
	{
	    def tableList = []
		data.each 
		{
			row ->
			def tableName = row['table']
			tableList << getTable(tableName)
		}
		return tableList
	}

   /**
	* Returns an iterator over the tables in this dataset in proper sequence.
	*/
   public ITableIterator iterator() throws DataSetException
   {
	   return new DefaultTableIterator(getTables())
   }

   /**
	* Returns an iterator over the tables in this dataset in reverse sequence.
	*/
   public ITableIterator reverseIterator() throws DataSetException
   {
	   return new DefaultTableIterator(getTables().reverse())
   }
   
   /**
	* Whether or not this dataset handles table names in a case sensitive way or not.
	* @return <code>true</code> if the case sensitivity of table names is used in this dataset.
	* @since 2.4.2
	*/
   public boolean isCaseSensitiveTableNames()
   {
	   return true
   }
}

GroovyDatasetTest

package com.kendelong.integration

import groovy.util.GroovyTestCase;

import org.dbunit.dataset.ITable;
import org.dbunit.dataset.ITableMetaData;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.junit.runners.BlockJUnit4ClassRunner;

class GroovyDatasetTest extends GroovyTestCase
{
	private List data;
	private GroovyDataset gd;
	
	@Before
	public void setUp()
	{
		data = [
			[table: 'content_page_shared', id: 30, node_name: 'mynode'],
			[table: 'content_item', id:10, name: 'test', content_type: 'article'],
			[table: 'content_page', id:10, logical_id: 30],
			[table: 'article', id: 10],
			[table: 'content_page_shared', id: 31, node_name: 'mynode2']
			] 
		gd = new GroovyDataset(data);
	}
	
	@Test
	public void testGetTableNames()
	{
		assertArrayEquals(['content_page_shared', 'content_item', 'content_page', 'article'] as String[], gd.getTableNames())
	}
	
	@Test
	public void testGetTableMetadata()
	{
		ITableMetaData meta = gd.getTableMetaData("content_item")
		assertEquals 3, meta.columns.size()
		assertEquals "id", meta.columns[0].columnName
		assertEquals "name", meta.columns[1].columnName
	}
	
	@Test
	public void testGetTableMetadataMultipleRowsInDataset()
	{
		ITableMetaData meta = gd.getTableMetaData("content_page_shared")
		assertEquals 2, meta.columns.size()
		assertEquals "id", meta.columns[0].columnName
		assertEquals "node_name", meta.columns[1].columnName
	}
	
	@Test
	public void testGetTable()
	{
		ITable table = gd.getTable("content_page_shared")
		assertEquals "rows", 2, table.getRowCount()
		assertEquals "logicalid", 31, table.getValue(1, "id")
	}
	
	@Test
	public void testGetTableList()
	{
		assertEquals 5, gd.getTables().length
	}
}

UserIntegrationTest

package com.kendelong.integration.user;

import static junit.framework.Assert.assertEquals;
import static junit.framework.Assert.assertNotNull;
import static junit.framework.Assert.assertNotSame;
import static junit.framework.Assert.assertNull;
import static junit.framework.Assert.assertTrue;

import org.dbunit.DatabaseUnitException;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.operation.DatabaseOperation;
import org.hibernate.SessionFactory;
import org.hibernate.jdbc.Work;
import org.junit.Before;
import org.junit.runner.RunWith;

import java.util.Date;

import org.apache.solr.client.solrj.impl.XMLResponseParser.KnownType;
import org.junit.Ignore;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.test.context.transaction.TransactionConfiguration;


@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration({"classpath:database-test-context.xml"})
@TransactionConfiguration(defaultRollback=true)
@Transactional
public class UserIntegrationTest
{
	private final Integer KNOWN_USER_ID = 2001;

	@Autowired
	private SessionFactory sessionFactory;

	private IDatabaseConnection con;
	private IDataSet dataset;
	
	private List data;

	
	{
		data  = [
			[table: 'user', id: 2001, legacy_id: 2001, first_name: 'Sarah',last_name:'Connor', email: 'sarah@term.com', screen_name: 'AScreenName', create_date:'2010-11-02'],
			[table: 'baby', id: 5000, legacy_id: 5000, name: 'baby1', birth_date: '2011-05-12', user_id: 2001, gender: 'F', create_date:'2010-11-02'],
			[table: 'baby', id: 5001, legacy_id: 5001, name: 'baby2', birth_date: '2011-05-12', user_id: 2001, gender: 'F', create_date:'2010-11-02'],
		] ;
	}
	
	@Autowired
	private IProfileService profileService;
	
	@Before
	public void doData() throws DatabaseUnitException, SQLException
	{
		sessionFactory.getCurrentSession().doWork(new Work()
		{
			@Override
			public void execute(Connection connection) throws SQLException
			{
				try
				{
					con = new DatabaseConnection(connection);
					dataset = new GroovyDataset(data);
					DatabaseOperation.REFRESH.execute(con, dataset);
				}
				catch(DatabaseUnitException e)
				{
					throw new SQLException(e);
				}
			}
		});
	}

	@Test
	public void testLoadUser()
	{
		User user = this.profileDao.loadUserById(KNOWN_USER_ID, false, false);
		assertNotNull(user);
		assertEquals("Sarah", user.getFirstName());
		assertEquals("Connor", user.getLastName())
	}

	@Test
	public void testBabiesLoadedProperly()
	{
		User user = this.profileDao.loadUserById(KNOWN_USER_ID, true, false);		
		assertTrue(user.hasBabies());
		assertEquals(2, user.babies.size())
	}
	
}