Integrete unitils for database(dao) testing

[/code][quote]Database testing
Unit tests for the database layer can be extremely valuable when building enterprise applications, but are often abandoned because of their complexity. Unitils greatly reduces this complexity, making database testing easy and maintainable. The following sections describe the support that the DatabaseModule and DbUnitModule have to offer for your database tests.
[/quote]

[quote]We extend XLS dataset for this sample

Build Tool:maven
DB: Hsql
Spring jdbc

[/quote]

1. maven dependency

<dependency>
			<groupId>org.unitils</groupId>
			<artifactId>unitils-io</artifactId>
			<version>3.3</version>
		</dependency>

		<dependency>
			<groupId>org.unitils</groupId>
			<artifactId>unitils-dbunit</artifactId>
			<version>3.3</version>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>org.unitils</groupId>
			<artifactId>unitils-spring</artifactId>
			<version>3.3</version>
			<scope>test</scope>
		</dependency>


		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.2-FINAL</version>
			<type>jar</type>
			<scope>compile</scope>
		</dependency>

2. Create unitils.properties in classpath.

database.driverClassName=org.hsqldb.jdbcDriver
database.schemaNames=PUBLIC
database.url=jdbc:hsqldb:mem:poc
database.dialect=hsqldb
database.userName=sa
database.password=

DbUnitModule.DataSet.loadStrategy.default=org.unitils.dbunit.datasetloadstrategy.impl.CleanInsertLoadStrategy
DbUnitModule.DataSet.factory.default=com.wilson.unitils.MultiSchemaXlsDataSetFactory
DbUnitModule.ExpectedDataSet.factory.default=com.wilson.unitils.MultiSchemaXlsDataSetFactory


# The database maintainer is disabled by default.
updateDataBaseSchema.enabled=true
#This table is by default not created automatically
dbMaintainer.autoCreateExecutedScriptsTable=true
dbMaintainer.script.locations=src/test/resources/data

#dbMaintainer.script.fileExtensions=sql,ddl
#DbUnitModule.DataSet.loadStrategy.default=org.unitils.dbunit.datasetloadstrategy.impl.CleanInsertLoadStrategy
#DatabaseModule.Transactional.value.default=commit 

# XSD generator
dataSetStructureGenerator.xsd.dirName=resources/xsd

3. From the above configure, we can see MultiSchemaXlsDataSetReader and MultiSchemaXlsDataSetFactory are extended to support microsoft xls data set.

public class MultiSchemaXlsDataSetFactory implements DataSetFactory {

	protected String defaultSchemaName;

	public void init(Properties configuration, String defaultSchemaName) {
		this.defaultSchemaName = defaultSchemaName;
	}

	public MultiSchemaDataSet createDataSet(File... dataSetFiles) {
		try {
			MultiSchemaXlsDataSetReader xlsDataSetReader = new MultiSchemaXlsDataSetReader(
					defaultSchemaName);
			return xlsDataSetReader.readDataSetXls(dataSetFiles);
		} catch (Exception e) {
			throw new UnitilsException("创建数据集失败: "
					+ Arrays.toString(dataSetFiles), e);
		}
	}

	public String getDataSetFileExtension() {
		return "xls";
	}

}

public class MultiSchemaXlsDataSetReader {
	private String defaultSchemaName;

	public MultiSchemaXlsDataSetReader(String defaultSchemaName) {
		this.defaultSchemaName = defaultSchemaName;
	}	

	public MultiSchemaDataSet readDataSetXls(File... dataSetFiles) {
		try {
			Map<String, List<ITable>> tableMap = getTables(dataSetFiles);
			MultiSchemaDataSet dataSets = new MultiSchemaDataSet();
			for (Entry<String, List<ITable>> entry : tableMap.entrySet()) {
				List<ITable> tables = entry.getValue();
				try {
					DefaultDataSet ds = new DefaultDataSet(tables
							.toArray(new ITable[] {}));
					dataSets.setDataSetForSchema(entry.getKey(), ds);
				} catch (AmbiguousTableNameException e) {
					throw new UnitilsException("构造DataSet失败!",  e);
				}
			}
			return dataSets;
		} catch (Exception e) {
			throw new UnitilsException("解析EXCEL文件出错", e);
		}
	}

	private Map<String, List<ITable>> getTables(File... dataSetFiles) {
		Pattern pattern = Pattern.compile("\\.");
		Map<String, List<ITable>> tableMap = new HashMap<String, List<ITable>>();
		try {
			for (File file : dataSetFiles) {
				IDataSet dataSet = new XlsDataSet(new FileInputStream(file));
				String[] tableNames = dataSet.getTableNames();
				for (String each : tableNames) {
					String schema = null;
					String tableName;
					String[] temp = pattern.split(each);
					if (temp.length == 2) {
						schema = temp[0];
						tableName = temp[1];
					} else {
						schema = this.defaultSchemaName;
						tableName = each;
					}
					ITable table = dataSet.getTable(each);
					if (!tableMap.containsKey(schema)) {
						tableMap.put(schema, new ArrayList<ITable>());
					}
					tableMap.get(schema).add(new XlsTable(tableName, table));
				}
			}
		} catch (Exception e) {
			throw new UnitilsException("创建数据集失败: "
					+ Arrays.toString(dataSetFiles), e);
		}
		return tableMap;
	}

	class XlsTable extends AbstractTable {
		private ITable delegate;
		private String tableName;

		public XlsTable(String tableName, ITable table) {
			this.delegate = table;
			this.tableName = tableName;
		}

		public int getRowCount() {
			return delegate.getRowCount();
		}

		public ITableMetaData getTableMetaData() {
			ITableMetaData meta = delegate.getTableMetaData();
			try {
				return new DefaultTableMetaData(tableName, meta.getColumns(),
						meta.getPrimaryKeys());
			} catch (DataSetException e) {
				throw new UnitilsException("Don't get the meta info from  "
						+ meta, e);
			}
		}

		public Object getValue(int row, String column) throws DataSetException {
			Object delta = delegate.getValue(row, column);
			if (delta instanceof String) {
				if (StringUtils.isEmpty((String) delta)) {
					return null;
				}
			}
			return delta;
		}

	}
}

4. in the above confire, we can also see following configure, this mean when unitils starts, it use the ddl under dbMaintainer.script.locations to initial the hsql table structure

dbMaintainer.script.locations=src/test/resources/data

We just create one table for testing

CREATE TABLE T_USER (USER_ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY, USER_NAME VARCHAR(40) NOT NULL, PASSWORD VARCHAR(40) NOT NULL);

5. Our Unit test sample

package com.wilson.unitils;

import org.junit.Assert;
import org.junit.Test;
import org.unitils.UnitilsJUnit4;
import org.unitils.dbunit.annotation.DataSet;
import org.unitils.spring.annotation.SpringApplicationContext;
import org.unitils.spring.annotation.SpringBean;

import com.wilson.dbunit.User;
import com.wilson.dbunit.UserDao;

@SpringApplicationContext("com/wilson/unitils/applicationContext.xml")
public class UserDaoUnitilsTest extends UnitilsJUnit4{
	@SpringBean("userDao")
	protected UserDao userDao;

	@Test
	@DataSet("UserDaoUnitilsTest_findUser.xls")
	public void findUser() throws Exception {
		User user = userDao.findUserByUserName("admin");
		System.out.println(user);
		Assert.assertEquals("123456", user.getPassword());
	}
}

From the above code, we use import the data in UserDaoUnitilsTest_findUser.xls to hsql database, sheet name is table name, the first line is the column names
[img]http://dl.iteye.com/upload/attachment/0080/1956/a92ffbcb-3b70-386f-9404-d2e3576cd88c.jpg[/img]

Database DDL:

CREATE TABLE T_USER (USER_ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY, USER_NAME VARCHAR(40) NOT NULL, PASSWORD VARCHAR(40) NOT NULL);

another test case for validate db insert result. we need add annotion @ExpectedDataSet

	@Test
	@ExpectedDataSet("UserDaoUnitilsTest_registerUser.xls")
	public void testRegisterUser() throws Exception {
		User user = new User();
		user.setUserId(3);
		user.setUserName("user001");
		user.setPassword("123456");
		userDao.RegisterUser(user);
	}

The t_user table should has same content as the UserDaoUnitilsTest_registerUser.xls
[img]http://dl.iteye.com/upload/attachment/0080/3571/8355c2a6-8936-3db8-a775-3600640fa6f0.jpg[/img]

6. if we want spring use the same datasource with unitils, we can add following as datasource in spring configure file.[repository\src\test\java\com\wilson\unitils\applicationContext.xml]

<bean id="dataSource" class="org.unitils.database.UnitilsDataSourceFactoryBean" />

Other code is related to spring jdbc and it's really simple , you can refer to the attachment. Be sure you have maven installed before running.