使用DBUnit集成Spring简化测试

今天药忘吃喽~ 2022-09-18 11:49 132阅读 0赞

在上一篇博文[使用DBUnit做单元测试][DBUnit]中，谈到了使用DBUnit进行数据准备做单测试，可以方便我们的数据准备以及后面的执行结果的验证，简化了我们做单元测试的准备工作，不过其中有一个不足就是，单元测试的代码过多的和DBUnit进行了耦合，并且其中还使用到了SQL，这个感觉非常不好，对于我们使用习惯了Spring进行解耦的人来说，总想使它简化一点，下面这个例子就是使用Spring集成DBUnit，进一步简化我们使用DBUnit做数据准备的工作。

我所做的就是将数据准备写成Annotation的方式，这样可以方便进行数据准备，写了两个Annotation，一个是用于Method的，一个是用于Class，用于Method的数据准备可以被事务管理，即数据在跑完测试后，就会被rollback，可是用于Class的却不能够在跑完测试后被回滚，于是就在找这里的问题。可是我却在一篇BLOG里面发现已经有现成的集成于spring test的dbunit annotation了，[https://github.com/springtestdbunit/spring-test-dbunit/][https_github.com_springtestdbunit_spring-test-dbunit]，测试了一下，我实现的功能它都已经实现了，并且把expected result也有一个实现了的Annotation ExpectedDatabase，那我的代码就直接扔掉了。只是这个ExpectedDatabase Annotation有一点不足的就是，不能够对结果数据进行排序，因为有的数据在插入到数据库中后，顺序就和Expected的结果集就会不一样了，这点我会在后面有说明，如何弥补这样的情况。以下是一个详细的实例，测试JAVA是放在名为com.dbunit.test的package中：

1、一些关键性的依赖

<dependency>
    			<artifactId>spring-jdbc</artifactId>
    			<groupId>org.springframework</groupId>
    			<scope>runtime</scope>
    		</dependency>
    		<dependency>
    			<artifactId>spring-core</artifactId>
    			<groupId>org.springframework</groupId>
    			<scope>compile</scope>
    		</dependency>
    		<dependency>
    			<artifactId>spring-beans</artifactId>
    			<groupId>org.springframework</groupId>
    			<scope>compile</scope>
    		</dependency>
    		<dependency>
    			<artifactId>spring-context</artifactId>
    			<groupId>org.springframework</groupId>
    			<scope>compile</scope>
    		</dependency>
    		<dependency>
    			<artifactId>spring-context-support</artifactId>
    			<groupId>org.springframework</groupId>
    			<scope>compile</scope>
    		</dependency>
    		<dependency>
    			<artifactId>spring-test</artifactId>
    			<groupId>org.springframework</groupId>
    			<scope>test</scope>
    		</dependency>
    		<dependency>
    			<artifactId>dbunit</artifactId>
    			<groupId>org.dbunit</groupId>
    			<scope>test</scope>
    		</dependency>
    		<dependency>
    			<groupId>jotm</groupId>
    			<artifactId>jotm</artifactId>
    			<scope>test</scope>
    		</dependency>
    		<dependency>
    			<groupId>com.experlog</groupId>
    			<artifactId>xapool</artifactId>
    			<scope>test</scope>
    		</dependency>
    		<dependency>
    			<groupId>junit</groupId>
    			<artifactId>junit</artifactId>
    			<scope>test</scope>
    		</dependency>

注：这里没有写spring-test-dbunit的依赖，那是因为在我环境我使用的是spring-test-dbunit的源码。

2、建表语句

create table YouTableName_1(
           filed_1 int,
           filed_2 varchar2(50),
           filed_3 varchar2(50)
    )

3、准备数据的DBUnit数据文件：MyTest.xml

<?xml version='1.0' encoding='UTF-8'?>
    <dataset>
      <YouTableName_1 Filed_1="1" Filed_2="f2" Filed_3="f3"/>
      <YouTableName_1 Filed_1="2" Filed_2="f2_1" Filed_3="f3_1"/>
    </dataset>

4、期待的结果DBUnit数据文件：MyTest\_Result.xml

<?xml version='1.0' encoding='UTF-8'?>
    <dataset>
      <YouTableName_1 Filed_1="1" Filed_2="a" Filed_3="a1"/>
      <YouTableName_1 Filed_1="2" Filed_2="b" Filed_3="b1"/>
    </dataset>

5、Spring的配置文件：spring.xml

<?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:jdbc="http://www.springframework.org/schema/jdbc"
    	xmlns:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop"
    	xmlns:p="http://www.springframework.org/schema/p" xmlns:context="http://www.springframework.org/schema/context"
    	xsi:schemaLocation="	
    	    http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd
    	    http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.1.xsd
    		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
    		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.1.xsd
    		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd">
    
    	<bean id="jotm" class="org.objectweb.jotm.Current" />
    
    	<bean id="dataSource" class="org.enhydra.jdbc.pool.StandardXAPoolDataSource"	destroy-method="shutdown">
    		<property name="dataSource">
    			<bean class="org.enhydra.jdbc.standard.StandardXADataSource" destroy-method="shutdown">
    				<property name="transactionManager" ref="jotm" />
    				<property name="driverName" value="oracle.jdbc.driver.OracleDriver" />
    				
    				<property name="url" value="jdbc:oracle:thin:@1.1.1.1:1521:dbschema" />
    				<property name="user" value="username" />
    				<property name="password" value="password" />
    			</bean>
    		</property>
    		<property name="user" value="username" />
    		<property name="password" value="password" />
    	</bean>
    	
    	<bean id="transactionManager" class="org.springframework.transaction.jta.JtaTransactionManager">
    		<property name="userTransaction" ref="jotm" />
    	</bean>
    
    	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    		<property name="dataSource" ref="dataSource">
    		</property>
    	</bean>
    
    	
    	<context:component-scan base-package="com.dbunit.test" />
    </beans>

6、用于测试的JAVA代码

**UpdateTest.java**

package com.dbunit.test;
    
    import java.sql.SQLException;
    
    import javax.inject.Inject;
    import javax.inject.Named;
    
    import org.springframework.jdbc.core.JdbcTemplate;
    
    @Named("UpdateTest")
    public class UpdateTest {
    
    	@Inject
    	JdbcTemplate jdbcTemplate;
    
    	public void updateFiled() throws SQLException {
    		jdbcTemplate.execute("update YouTableName_1 set filed_2='a',filed_3='a1' where filed_1=1");
    		jdbcTemplate.execute("update YouTableName_1 set filed_2='b',filed_3='b1' where filed_1=2");
    	}
    }

**MyTest.java**

package com.dbunit.test;
    
    import java.io.IOException;
    import java.sql.SQLException;
    
    import javax.inject.Inject;
    
    import junit.framework.Assert;
    
    import org.junit.Test;
    
    import com.github.springtestdbunit.annotation.DatabaseSetup;
    import com.github.springtestdbunit.annotation.ExpectedDatabase;
    import com.github.springtestdbunit.assertion.DatabaseAssertionMode;
    
    public class MyTest extends BasedTestCase {
    
    	@Inject
    	UpdateTest updateTest;
    
    	@Test
    	@DatabaseSetup({ "classpath:/MyTest.xml" })
    	@ExpectedDatabase(assertionMode=DatabaseAssertionMode.NON_STRICT,value="classpath:/MyTest_Result.xml")
    	public void testSend() throws IOException, SQLException {
    		try {
    			updateTest.updateFiled();
    		} catch (Exception e) {
    			e.printStackTrace();
    			Assert.assertTrue(false);
    		}
    
    	}
    
    }

**BasedTestCase.java**

package com.dbunit.test;
    
    import org.junit.runner.RunWith;
    import org.springframework.test.context.ContextConfiguration;
    import org.springframework.test.context.TestExecutionListeners;
    import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
    import org.springframework.test.context.support.DependencyInjectionTestExecutionListener;
    import org.springframework.test.context.transaction.TransactionalTestExecutionListener;
    import org.springframework.transaction.annotation.Transactional;
    
    import com.github.springtestdbunit.TransactionDbUnitTestExecutionListener;
    
    @RunWith(SpringJUnit4ClassRunner.class)
    @ContextConfiguration(locations = { "classpath:/spring.xml" })
    @TestExecutionListeners({ DependencyInjectionTestExecutionListener.class, TransactionalTestExecutionListener.class, TransactionDbUnitTestExecutionListener.class })
    @Transactional
    public abstract class BasedTestCase {
    
    }

这就是这个简单的测试示例所需要的全部文件，也可以运行成功了。

不过如果其它的测试，虽然执行结果正确，但是JUnit的执行结果却是错误的，这是因为我们准备的数据在被插入到数据库中后，记录的顺序可能就被打乱了，此时的执行结果的记录顺序就会和我们期待的结果就会不一样。这个时候的解决方法就是将查询执行结果的时候，加上某个字段的order by，就可以得到我们期望的结果。

此时我们将M**yTest.java** 和 **BaseTestCase.java**修改成如下这样的：

**MyTest.java**

package com.dbunit.test;
    
    import java.io.IOException;
    import java.sql.SQLException;
    
    import javax.inject.Inject;
    
    import junit.framework.Assert;
    
    import org.dbunit.dataset.ReplacementDataSet;
    import org.junit.Test;
    
    import com.github.springtestdbunit.annotation.DatabaseSetup;
    import com.github.springtestdbunit.annotation.ExpectedDatabase;
    import com.github.springtestdbunit.assertion.DatabaseAssertionMode;
    
    public class MyTest extends BasedTestCase {
    
    	@Inject
    	UpdateTest updateTest;
    
    	@Test
    	@DatabaseSetup({ "classpath:/MyTest.xml" })
    	//The comparation can be kept, it's maybe correct
    	@ExpectedDatabase(assertionMode=DatabaseAssertionMode.NON_STRICT,value="classpath:/MyTest_Result.xml")
    	public void testSend() throws IOException, SQLException {
    		try {
    			updateTest.updateFiled();
    			// get result data set by result xml file
    			ReplacementDataSet dataload_result = createDataSet(Thread.currentThread().getContextClassLoader().getResourceAsStream("MyTest_Result.xml"));
    			// compare the data which get from database and the expected result file
    			assertDataSet("YouTableName_1", "select filed_1,filed_2,filed_3 from YouTableName_1 order by filed_1", dataload_result);
    		} catch (Exception e) {
    			e.printStackTrace();
    			Assert.assertTrue(false);
    		}
    
    	}
    
    }

**BaseTestCase.java**

package com.dbunit.test;
    
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    import java.util.TreeMap;
    
    import javax.inject.Inject;
    import javax.sql.DataSource;
    
    import junit.framework.Assert;
    
    import org.dbunit.Assertion;
    import org.dbunit.database.DatabaseConnection;
    import org.dbunit.database.IDatabaseConnection;
    import org.dbunit.database.QueryDataSet;
    import org.dbunit.dataset.Column;
    import org.dbunit.dataset.IDataSet;
    import org.dbunit.dataset.ITable;
    import org.dbunit.dataset.ReplacementDataSet;
    import org.dbunit.dataset.filter.DefaultColumnFilter;
    import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
    import org.junit.runner.RunWith;
    import org.springframework.test.context.ContextConfiguration;
    import org.springframework.test.context.TestExecutionListeners;
    import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
    import org.springframework.test.context.support.DependencyInjectionTestExecutionListener;
    import org.springframework.test.context.transaction.TransactionalTestExecutionListener;
    import org.springframework.transaction.annotation.Transactional;
    
    import com.github.springtestdbunit.TransactionDbUnitTestExecutionListener;
    
    @RunWith(SpringJUnit4ClassRunner.class)
    @ContextConfiguration(locations = { "classpath:/spring.xml" })
    @TestExecutionListeners({ DependencyInjectionTestExecutionListener.class, TransactionalTestExecutionListener.class, TransactionDbUnitTestExecutionListener.class })
    @Transactional
    public abstract class BasedTestCase {
    	@Inject
    	DataSource dataSource;
    
    	/**
    	 * This is used to assert the data from table and the expected data set. If all of the them has the same records, then the assert is true.
    	 * 
    	 * @param tableName
    	 * @param sql
    	 * @param expectedDataSet
    	 * @throws Exception
    	 */
    	protected void assertDataSet(String tableName, String sql, IDataSet expectedDataSet) throws Exception {
    		if (dataSource == null) {
    			throw new RuntimeException("There must be dataSource in the spring config file");
    		}
    		IDatabaseConnection iconn = new DatabaseConnection(dataSource.getConnection());
    		printDataAsXml(iconn, tableName, sql);
    		QueryDataSet loadedDataSet = new QueryDataSet(iconn);
    		loadedDataSet.addTable(tableName, sql);
    		ITable table1 = loadedDataSet.getTable(tableName);
    		ITable table2 = expectedDataSet.getTable(tableName);
    		Assert.assertEquals(table2.getRowCount(), table1.getRowCount());
    
    		DefaultColumnFilter.includedColumnsTable(table1, table2.getTableMetaData().getColumns());
    		Assertion.assertEquals(table2, table1);
    
    	}
    
    	/**
    	 * Create the data set by input stream which read from the dbunit xml data file.
    	 * 
    	 * @param is
    	 * @return
    	 * @throws Exception
    	 */
    	protected ReplacementDataSet createDataSet(InputStream is) throws Exception {
    		return new ReplacementDataSet(new FlatXmlDataSetBuilder().build(is));
    	}
    
    	/**
    	 * Convert the data in the ITable to List
    	 * 
    	 * @param table
    	 * @return
    	 * @throws Exception
    	 */
    	private List<Map<?, ?>> getDataFromTable(ITable table) throws Exception {
    		List<Map<?, ?>> ret = new ArrayList<Map<?, ?>>();
    		int count_table = table.getRowCount();
    		if (count_table > 0) {
    			Column[] columns = table.getTableMetaData().getColumns();
    			for (int i = 0; i < count_table; i++) {
    				Map<String, Object> map = new TreeMap<String, Object>();
    				for (Column column : columns) {
    					map.put(column.getColumnName().toUpperCase(), table.getValue(i, column.getColumnName()));
    				}
    				ret.add(map);
    			}
    		}
    		return ret;
    	}
    
    	/**
    	 * Get data by the SQL and table name, then convert the data in the ITable to List
    	 * 
    	 * @param iconn
    	 * @param tableName
    	 * @param sql
    	 * @return
    	 * @throws Exception
    	 */
    	private List<Map<?, ?>> getTableDataFromSql(IDatabaseConnection iconn, String tableName, String sql) throws Exception {
    		ITable table = iconn.createQueryTable(tableName, sql);
    		return getDataFromTable(table);
    	}
    
    	/**
    	 * Get data by the SQL and table name, then convert the data in the ITable to List. And the print the data as xml data format.
    	 * 
    	 * @param iconn
    	 * @param tableName
    	 * @param sql
    	 * @throws Exception
    	 */
    	private void printDataAsXml(IDatabaseConnection iconn, String tableName, String sql) throws Exception {
    		List<Map<?, ?>> datas = getTableDataFromSql(iconn, tableName, sql);
    		StringBuffer sb;
    		for (Map<?, ?> data : datas) {
    			sb = new StringBuffer();
    			sb.append("<" + tableName.toUpperCase() + " ");
    			for (Object o : data.keySet()) {
    				sb.append(o + "=\"" + data.get(o) + "\" ");
    			}
    			sb.append("/>");
    			System.out.println(sb.toString());
    		}
    	}
    }

这里我们验证了两次结果，通常执行结果是正确的，如果执行结果失败了，可以把ExpectedDatabase Annotation给注释掉，再试试，当然你的expected结果文件一定要和你期的执行结果相同了。

[DBUnit]: http://blog.csdn.net/fenglibing/article/details/16844473
[https_github.com_springtestdbunit_spring-test-dbunit]: https://github.com/springtestdbunit/spring-test-dbunit/