在很多成功的软件项目中，测试自动化往往是关键的层面。DBUnit允许开发人员在测试之前给目标数据库植入测试数据，在测试完毕后，再将数据库恢复到测试前的状态。在最近的一个项目中，我尝试使用用DBUnit对Spring+iBatis的架构进行测试，下面记录了DBUnit的使用过程和遇到的一些问题。
测试环境
首先，我们建立一个测试环境（基于Maven 2和Oracle数据库*）。数据表名Account。
数据库
先建立一个测试数据表（数据库为Oracle*）
Account.sql CREATE TABLE Account ("ID" NUMBER, "USERNAME" VARCHAR2(256 BYTE) NOT NULL ENABLE, "PASSWORD" VARCHAR2(256 BYTE), CONSTRAINT "ACCOUNT_UK_ID" UNIQUE ("ID"), CONSTRAINT "ACCOUNT_PK" PRIMARY KEY ("USERNAME") ) |
这里我暂时不想涉及Sequence，所以主键**是username，而不是ID，并且ID允许为NULL。这是因为Sequence的递增是不可恢复的，如果项目对记录ID是否连续不是特别在意的话，可以在自己的项目中建立，只要稍微修改一下iBatis配置文件中的SQL语句就可以了。这里我们先屏蔽这个问题。
DBUnit测试Oracle数据库时，帐户最好不要拥有DBA权限，否则会出现org.dbunit.database.AmbiguousTableNameException: COUNTRIES 错误。如果帐户必须具备DBA权限，那么就需要在执行new DatabaseConnection时，明确给定SCHEMA（名称必须大写），详细说明参考下文多处代码注释和“org.dbunit.database.AmbiguousTableNameException异常”章节。
表必须存在主键，否则返回org.dbunit.dataset.NoPrimaryKeyException错误。
Spring配置文件
ApplicationContext.xml <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd"> <beans> <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="locations"> <list> <value>classpath:database.properties</value> </list> </property> </bean> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="${database.connection.driver_class}"/> <property name="url" value="${database.connection.url}"/> <property name="username" value="${database.connection.username}"/> <property name="password" value="${database.connection.password}"/> </bean> <bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean"> <property name="configLocation"> <value>SqlMapConfig.xml</value> </property> <property name="dataSource" ref="dataSource"/> </bean> <bean id="accountManager" class="com.wang.dbunit.AccountManager"> <property name="sqlMapClient" ref="sqlMapClient"/> </bean> </beans> database.properties database.connection.driver_class=oracle.jdbc.driver.OracleDriver database.connection.url=jdbc:oracle:thin:@xxx.xxx.xxx.xxx:1521:test database.connection.username=username database.connection.password=password |
iBatis配置文件
SqlMapConfig.xml <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sqlMapConfig PUBLIC"-//iBATIS.com//DTD SQL Map Config 2.0//EN" "http://www.ibatis.com/dtd/sql-map-config-2.dtd"> <sqlMapConfig> <settings useStatementNamespaces="false" cacheModelsEnabled="true" enhancementEnabled="true" lazyLoadingEnabled="true" maxRequests="32" maxSessions="10" maxTransactions="5" /> <sqlMap resource="Account.xml"/> </sqlMapConfig> Account.xml <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPEsqlMap PUBLIC"-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd"> <sqlMap namespace="Account"> <resultMap id="accountMap" class="com.wang.dbunit.Account"> <result property="id" column="id" jdbcType="NUMBER" nullValue="0"/> <result property="userName" column="username" jdbcType="VARCHAR2"/> <result property="password" column="password" jdbcType="VARCHAR2"/> </resultMap> <!--** preserve ************************************** --> <sql id="id-select"> <![CDATA[ SELECT id_sequence.nextval AS id FROM dual ]]> </sql> <!--*************************************************** --> <sql id="account-select"> <![CDATA[ SELECTid, username ]]> <dynamic prepend=","> <isEqual property="includePassword" compareValue="true"> password </isEqual> </dynamic> FROMaccount </sql> <sql id="account-where"> <![CDATA[ username=#userName:VARCHAR2# ]]> <dynamic> <isNotNull property="password" prepend="AND "> <![CDATA[ password=#password:VARCHAR2# ]]> </isNotNull> </dynamic> </sql> <select id="getAccount" parameterClass="com.wang.dbunit.Account" resultMap="accountMap"> <include refid="account-select"/> <dynamic prepend=" WHERE"> <isNotNull property="userName"> <include refid="account-where"/> </isNotNull> </dynamic> </select> <!--**************************************************** --> <sql id="account-insert"> <![CDATA[ INSERT INTO account(username, password ]]> <dynamic prepend=","> <isNotEqual property="id" compareValue="0"> <![CDATA[ id ]]> </isNotEqual> </dynamic> ) </sql> <sql id="account-insert-values"> <![CDATA[ VALUES(#userName:VARCHAR2#, #password:VARCHAR2# ]]> <dynamic prepend=","> <isNotEqual property="id" compareValue="0"> <![CDATA[ #id:NUMBER# ]]> </isNotEqual> </dynamic> ) </sql> <insert id="createAccount" parameterClass="com.wang.dbunit.Account"> <isEqual property="generateIdFromSequence" compareValue="true"> <include refid="id-select"/> </isEqual> <include refid="account-insert"/> <include refid="account-insert-values"/> </insert> </sqlMap> |
（这个配置文件中预留了未来使用 sequence 的可能）
DBUnit配置文件
我们通过一个xml种子文件(seedfile)为DBUnit提供测试数据，文件中的数据会被DBUnit在测试开始前自动植入数据表，这个文件结构很简单：
dataSet.xml <?xml version='1.0' encoding='UTF-8'?> <DATASET> <ACCOUNT id='1' username='Drew' password='Smith'/> <ACCOUNT id='2' username='Nick' password='Marquiss'/> <ACCOUNT id='3' username='Jose' password='Whitson'/> </DATASET> |
“ACCOUNT”就是表名称，它的属性就是字段内容。
代码
辅助类Accout.java
package com.wang.dbunit; public class Account { private boolean generateIdFromSequence=false; private boolean includePassword = false; private long id = 0; private String userName = null; private String password = null; public boolean getGenerateIdFromSequence() { return generateIdFromSequence; } public void setGenerateIdFromSequence(boolean generateIdFromSequence) { this.generateIdFromSequence =generateIdFromSequence; } public void setId(long id) { this.id =id; } public long getId() { return this.id; } public String getPassword() { return password; } public void setPassword(String password) { this.password =password; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName =userName; } public boolean isIncludePassword() { return includePassword; } public void setIncludePassword(boolean includePassword) { this.includePassword =includePassword; } } |
业务类AccountManager.java
package com.wang.dbunit; import com.ibatis.sqlmap.client.SqlMapClient; public class AccountManager { protected SqlMapClient sqlMap = null; public void setSqlMapClient(SqlMapClient sqlMapClient) { this.sqlMap =sqlMapClient; } public Account getAccount(String userName, String password, boolean includePassword) throws Exception { Account account = new Account(); account.setUserName(userName); account.setPassword(password); account.setIncludePassword(includePassword); Account ret = (Account)(sqlMap.queryForObject("getAccount", account)); return ret; } public void createAccount(String userName, String password) throws Exception { Account account = new Account(); account.setUserName(userName); account.setPassword(password); sqlMap.insert("createAccount",account); } } |
好了，我们完成了了全部测试环境，接下来我们要开始编写测试用例。
测试
DatabaseTestCase类
DBUnit提供了一个抽象类： DatabaseTestCase，它继承自 JUnit的 TestCase，这个类有两个方法需要重载：
protecte abstract IDatabaseConnection getConnection() throws Exception; protected abstract IDataSet getDataSet() throws Exception; |
getConnection用于告诉测试用例如何找到数据库连接；getDataSet用于告诉测试用例如何获取测试数据文件（dataSet.xml）。下面我们先继承这个抽象类编写测试用例：
package com.wang.dbunit; import java.io.FileInputStream; import java.sql.Connection; import java.util.Properties; import javax.sql.DataSource; import com.wang.dbunit.Account; import org.apache.log4j.Logger; import org.apache.log4j.LogManager; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.dbunit.DatabaseTestCase; 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; public class HelloDBUnit extends DatabaseTestCase { static Logger logger = LogManager.getLogger(HelloDBUnit.class.getName()); privatestatic ApplicationContext context; protected Properties props = new Properties(); public HelloDBUnit() throws IOException { super(); props.load(Resources.getResourceAsStream( "database.properties")); context = newClassPathXmlApplicationContext( "classpath:ApplicationContext.xml"); } //////////////////////////////////////////////// @Override protected IDatabaseConnection getConnection() throws Exception { DataSourcedataSource = (DataSource)context.getBean("dataSource"); Connectionconnection = dataSource.getConnection(); |
// 如果所用测试帐户是 DBA，为了避免出现 AmbiguousTableNameException
// 异常，下面必须改写为 newDatabaseConnection(connection, SCHEMA)
// 形式。注意SCHEMA 要大写**
return new DatabaseConnection(connection); } @Override protected IDataSet getDataSet()throws Exception { return new FlatXmlDataSet( new FileInputStream("bin/dataSet.xml")); } /////////////////////////////////////////////// @Override protected DatabaseOperation getSetUpOperation() throws Exception { return DatabaseOperation.REFRESH; } @Override protected DatabaseOperation getTearDownOperation() throws Exception { return DatabaseOperation.NONE; } //////////////////////////////////////////////////// public void testSelectAccount() { AccountManager manager = (AccountManager)context.getBean("accountManager"); try { Accountaccount = manager.getAccount("Nick", "Marquiss", true); assertNotNull(account); } catch (Exceptione) { logger.error(e.getMessage(),e); } } public void testCreateAccount() { AccountManager manager = (AccountManager)context.getBean("accountManager"); try { manager.createAccount("TEST", "test"); } catch(Exception e) { logger.error(e.getMessage(),e); } } } |
上文内容不用于商业目的，如涉及知识产权问题，请权利人联系博为峰小编(021-64471599-8017)，我们将立即处理。
