wh870712
- 浏览: 39345 次
- 性别:
- 来自: 深圳
-
文章分类
最新评论
-
baeqiuzhu：
我使用了一下 Truncate table t_cms_s ...
表记录删除之后，表空间清理释放过程
Java代码
package com.test.dbunit.dao;
import javax.sql.DataSource;
import org.dbunit.Assertion;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.database.QueryDataSet;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.operation.DatabaseOperation;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.AbstractTransactionalJUnit4SpringContextTests;
import org.springframework.test.context.transaction.TransactionConfiguration;
import com.test.dbunit.entity.User;
@ContextConfiguration(locations = { "classpath:testApplicationContext.xml" })
@TransactionConfiguration(defaultRollback = true)
public class UserDaoTest extends AbstractTransactionalJUnit4SpringContextTests {
@Autowired
private UserDao userDao;
@Autowired
private DataSource dataSource;
private IDatabaseConnection conn;
@Before
public void initDbunit() throws Exception {
conn = new DatabaseConnection(DataSourceUtils.getConnection(dataSource));
}
@Test
public void saveUser() throws Exception {
User user = new User();
user.setNick("user001");
user.setPassword("password001");
userDao.save(user);
QueryDataSet actual = new QueryDataSet(conn);
actual.addTable("user",
"select * from user where user.nick = 'user001'");
IDataSet expected = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001.xml").getFile());
Assertion.assertEquals(expected, actual);
}
@Test
public void updateUser() throws Exception {
IDataSet origen = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001.xml").getFile());
DatabaseOperation.INSERT.execute(conn, origen);
User user = new User();
user.setNick("user001");
user.setPassword("password002");
userDao.update(user);
QueryDataSet actual = new QueryDataSet(conn);
actual.addTable("user",
"select * from user where user.nick = 'user001'");
IDataSet expected = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001_updated.xml").getFile());
Assertion.assertEquals(expected, actual);
}
@Test
public void removeUser() throws Exception {
IDataSet origen = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001.xml").getFile());
DatabaseOperation.INSERT.execute(conn, origen);
userDao.remove("user001");
QueryDataSet actual = new QueryDataSet(conn);
actual.addTable("user", "select * from user where nick = 'user001'");
Assert.assertEquals(0, actual.getTable("user").getRowCount());
}
@Test
public void findUser() throws Exception {
IDataSet data = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001.xml").getFile());
DatabaseOperation.INSERT.execute(conn, data);
User user = userDao.getUserByNick("user001");
Assert.assertEquals("password001", user.getPassword());
}
}
package com.test.dbunit.dao;
import javax.sql.DataSource;
import org.dbunit.Assertion;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.database.QueryDataSet;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.operation.DatabaseOperation;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.AbstractTransactionalJUnit4SpringContextTests;
import org.springframework.test.context.transaction.TransactionConfiguration;
import com.test.dbunit.entity.User;
@ContextConfiguration(locations = { "classpath:testApplicationContext.xml" })
@TransactionConfiguration(defaultRollback = true)
public class UserDaoTest extends AbstractTransactionalJUnit4SpringContextTests {
@Autowired
private UserDao userDao;
@Autowired
private DataSource dataSource;
private IDatabaseConnection conn;
@Before
public void initDbunit() throws Exception {
conn = new DatabaseConnection(DataSourceUtils.getConnection(dataSource));
}
@Test
public void saveUser() throws Exception {
User user = new User();
user.setNick("user001");
user.setPassword("password001");
userDao.save(user);
QueryDataSet actual = new QueryDataSet(conn);
actual.addTable("user",
"select * from user where user.nick = 'user001'");
IDataSet expected = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001.xml").getFile());
Assertion.assertEquals(expected, actual);
}
@Test
public void updateUser() throws Exception {
IDataSet origen = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001.xml").getFile());
DatabaseOperation.INSERT.execute(conn, origen);
User user = new User();
user.setNick("user001");
user.setPassword("password002");
userDao.update(user);
QueryDataSet actual = new QueryDataSet(conn);
actual.addTable("user",
"select * from user where user.nick = 'user001'");
IDataSet expected = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001_updated.xml").getFile());
Assertion.assertEquals(expected, actual);
}
@Test
public void removeUser() throws Exception {
IDataSet origen = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001.xml").getFile());
DatabaseOperation.INSERT.execute(conn, origen);
userDao.remove("user001");
QueryDataSet actual = new QueryDataSet(conn);
actual.addTable("user", "select * from user where nick = 'user001'");
Assert.assertEquals(0, actual.getTable("user").getRowCount());
}
@Test
public void findUser() throws Exception {
IDataSet data = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001.xml").getFile());
DatabaseOperation.INSERT.execute(conn, data);
User user = userDao.getUserByNick("user001");
Assert.assertEquals("password001", user.getPassword());
}
}
对Dao进行单元测试，一般有两种思路。一是Mock，对使用的底层API进行Mock，比如Hibernate和JDBC接口，判断接口有没有正确调用，另一种是实际访问数据库，判断数据库有没有正确读写。更多的情况下，我更倾向于后者，因为在使用ORM工具或者jdbcTemplate的情况下，dao一般只有简单的几行代码，没有复杂的逻辑，Mock测试一般没有什么意义，我们更关心的是，Hibernate mapping是否正确，ibatis sql是否正确等，所以实际读写数据库才能真正判断一个dao是否正确，这也是我们关心的测试内容。
好的单元测试应该是原子性的，独立的，不应依赖其他测试和上下文，但是要测试数据读写是否正确，就必须涉及初始数据的加载，数据修改的还原等操作。对于初始数据的加载，手动输入很麻烦，一个解决方案就是使用Dbunit，从Xml文件甚至Excel中加载初始数据到数据库，是数据库的值达到一个已知状态。同时还可以使用Dbunit，对数据库的结果状态进行判断，保证和期望的一致。数据修改的还原，可以依赖Spring TransactionalTests，在测试完成后回滚数据库。
Dbunit还可以对数据的现有数据进行备份，还原，清空现有数据，一个好的测试实践是每一个开发人员一个测试数据库，进而对数据库的数据状态有更好的控制，但现实可能会是共享同一个测试库，所以这种情况下，测试的编写必须多做一些考虑。
待测试的类：
Java代码
package com.test.dbunit.dao.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
import com.test.dbunit.dao.UserDao;
import com.test.dbunit.entity.User;
public class DefaultUserDao extends BaseDao implements UserDao {
private static String QUERY_BY_NICK = "select * from user where user.nick = ?";
private static String REMOVE_USER = "delete from user where user.nick = ?";
private static String INSERT_USER = "insert into user(nick,password) values(?, ?)";
private static String UPDATE_USER = "update user set user.password = ? where user.nick = ?";
@Override
public User getUserByNick(String nick) {
return (User) getJdbcTemplate().queryForObject(QUERY_BY_NICK,new Object[]{nick}, new RowMapper(){
@Override
public Object mapRow(ResultSet rs, int index) throws SQLException {
User user = new User();
user.setNick(rs.getString("nick"));
user.setPassword(rs.getString("password"));
return user;
}
});
}
@Override
public void remove(String nick) {
getJdbcTemplate().update(REMOVE_USER, new Object[]{nick});
}
@Override
public void save(User user) {
getJdbcTemplate().update(INSERT_USER, new Object[]{user.getNick(), user.getPassword()});
}
@Override
public void update(User user) {
getJdbcTemplate().update(UPDATE_USER, new Object[]{user.getPassword(), user.getNick()});
}
}
package com.test.dbunit.dao.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
import com.test.dbunit.dao.UserDao;
import com.test.dbunit.entity.User;
public class DefaultUserDao extends BaseDao implements UserDao {
private static String QUERY_BY_NICK = "select * from user where user.nick = ?";
private static String REMOVE_USER = "delete from user where user.nick = ?";
private static String INSERT_USER = "insert into user(nick,password) values(?, ?)";
private static String UPDATE_USER = "update user set user.password = ? where user.nick = ?";
@Override
public User getUserByNick(String nick) {
return (User) getJdbcTemplate().queryForObject(QUERY_BY_NICK,new Object[]{nick}, new RowMapper(){
@Override
public Object mapRow(ResultSet rs, int index) throws SQLException {
User user = new User();
user.setNick(rs.getString("nick"));
user.setPassword(rs.getString("password"));
return user;
}
});
}
@Override
public void remove(String nick) {
getJdbcTemplate().update(REMOVE_USER, new Object[]{nick});
}
@Override
public void save(User user) {
getJdbcTemplate().update(INSERT_USER, new Object[]{user.getNick(), user.getPassword()});
}
@Override
public void update(User user) {
getJdbcTemplate().update(UPDATE_USER, new Object[]{user.getPassword(), user.getNick()});
}
}
单元测试：
需要注意的地方就是，DataSourceUtils.getConnection(datasource) ， 通过这种方式获得数据库连接初始化Dbunit，能够保证Dbunit使用的数据连接和当前事务的数据库连接相同，保证能够在参与到事务中。Spring的TransactionManager会在开始事务时把当前连接保存到ThreadLocal中，DataSourceUtils.getConnection方法，首先从ThreadLocal中获取连接。
user001.xml
Xml代码
<?xml version="1.0" encoding="UTF-8"?>
<dataset>
<user nick="user001" password="password001" />
</dataset>
<?xml version="1.0" encoding="UTF-8"?>
<dataset>
<user nick="user001" password="password001" />
</dataset> 使用dbunit，可以通过xml文件定义数据集，也可以使用其他方式定义，比如Excel，编程方式。
Dbunit的主要构件
IDatabaseConnection
数据库链接。实现类有DatabaseConnection 和DatabaseDataSourceConnection ，执行数据库操作时需要一个连接。
IDataSet
数据集，数据集可以从Xml文件Excel等外部文件获取，也可以从数据库查询，或者编程方式构件，数据集可以作为初始数据插入到数据库，也可以作为断言的依据。另外还有IDatatable等辅助类。
比如在updateUser测试中，使用了QueryDataSet，从数据库中构建一个Dataset，再通过FlatXmlDataSet从Xml文件中构建一个Dataset，断言这两个Dataset相同。
Java代码
QueryDataSet actual = new QueryDataSet(conn);
actual.addTable("user", "select * from user where user.nick = 'user001'");
IDataSet expected = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001_updated.xml").getFile());
Assertion.assertEquals(expected, actual);
QueryDataSet actual = new QueryDataSet(conn);
actual.addTable("user", "select * from user where user.nick = 'user001'");
IDataSet expected = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001_updated.xml").getFile());
Assertion.assertEquals(expected, actual);
DatabaseOperation
通过定义的静态字段可以获取一组代表一个数据操作的子类对象，比如DatabaseOperation .INSERT，返回 InsertOperation，通过执行execute方法把数据集插入到数据库。例如：
Java代码
IDataSet origen = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001.xml").getFile());
DatabaseOperation.INSERT.execute(conn, origen);
IDataSet origen = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001.xml").getFile());
DatabaseOperation.INSERT.execute(conn, origen); 从Xml文件中构建DataSet，使用Insert插入到数据库，初始化测试数据。
Assertion
唯一的方法，assertEqual，断言两个数据集或数据表相同。
更多关于Dbunit的组件的介绍：http://www.dbunit.org/components.html
PS:使用Oracle的时候，初始化DatabaseConnection需要传入scheme。new DatabaseConnection(conn,SCHEMA_NAME ) ，SCHMEA_NAME需要大写。
附件提供所有代码下载
一个DAO测试基类
Java代码
package com.taobao.dbunit.dao;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.dbunit.Assertion;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.DataSetException;
import org.dbunit.dataset.DefaultDataSet;
import org.dbunit.dataset.DefaultTable;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.operation.DatabaseOperation;
import org.junit.Assert;
import org.junit.Before;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.AbstractTransactionalJUnit4SpringContextTests;
import org.springframework.test.context.transaction.TransactionConfiguration;
@ContextConfiguration(locations = { "classpath:testApplicationContext.xml" })
@TransactionConfiguration(defaultRollback = true)
public class BaseDaoTest extends AbstractTransactionalJUnit4SpringContextTests {
@Autowired
private DataSource dataSource;
private IDatabaseConnection conn;
@Before
public void initDbunit() throws Exception {
conn = new DatabaseConnection(DataSourceUtils.getConnection(dataSource));
}
/**
* 清空file中包含的表中的数据，并插入file中指定的数据
*
* @param file
* @throws Exception
*/
protected void setUpDataSet(String file) throws Exception {
IDataSet dataset = new FlatXmlDataSet(new ClassPathResource(file)
.getFile());
DatabaseOperation.CLEAN_INSERT.execute(conn, dataset);
}
/**
* 验证file中包含的表中的数据和数据库中的相应表的数据是否一致
*
* @param file
* @throws Exception
*/
protected void verifyDataSet(String file) throws Exception {
IDataSet expected = new FlatXmlDataSet(new ClassPathResource(file)
.getFile());
IDataSet dataset = conn.createDataSet();
for (String tableName : expected.getTableNames()) {
Assertion.assertEquals(expected.getTable(tableName), dataset
.getTable(tableName));
}
}
/**
* 清空指定的表中的数据
*
* @param tableName
* @throws Exception
*/
protected void clearTable(String tableName) throws Exception {
DefaultDataSet dataset = new DefaultDataSet();
dataset.addTable(new DefaultTable(tableName));
DatabaseOperation.DELETE_ALL.execute(conn, dataset);
}
/**
* 验证指定的表为空
*
* @param tableName
* @throws DataSetException
* @throws SQLException
*/
protected void verifyEmpty(String tableName) throws DataSetException,
SQLException {
Assert.assertEquals(0, conn.createDataSet().getTable(tableName)
.getRowCount());
}
}
package com.taobao.dbunit.dao;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.dbunit.Assertion;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.DataSetException;
import org.dbunit.dataset.DefaultDataSet;
import org.dbunit.dataset.DefaultTable;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.operation.DatabaseOperation;
import org.junit.Assert;
import org.junit.Before;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.AbstractTransactionalJUnit4SpringContextTests;
import org.springframework.test.context.transaction.TransactionConfiguration;
@ContextConfiguration(locations = { "classpath:testApplicationContext.xml" })
@TransactionConfiguration(defaultRollback = true)
public class BaseDaoTest extends AbstractTransactionalJUnit4SpringContextTests {
@Autowired
private DataSource dataSource;
private IDatabaseConnection conn;
@Before
public void initDbunit() throws Exception {
conn = new DatabaseConnection(DataSourceUtils.getConnection(dataSource));
}
/**
* 清空file中包含的表中的数据，并插入file中指定的数据
*
* @param file
* @throws Exception
*/
protected void setUpDataSet(String file) throws Exception {
IDataSet dataset = new FlatXmlDataSet(new ClassPathResource(file)
.getFile());
DatabaseOperation.CLEAN_INSERT.execute(conn, dataset);
}
/**
* 验证file中包含的表中的数据和数据库中的相应表的数据是否一致
*
* @param file
* @throws Exception
*/
protected void verifyDataSet(String file) throws Exception {
IDataSet expected = new FlatXmlDataSet(new ClassPathResource(file)
.getFile());
IDataSet dataset = conn.createDataSet();
for (String tableName : expected.getTableNames()) {
Assertion.assertEquals(expected.getTable(tableName), dataset
.getTable(tableName));
}
}
/**
* 清空指定的表中的数据
*
* @param tableName
* @throws Exception
*/
protected void clearTable(String tableName) throws Exception {
DefaultDataSet dataset = new DefaultDataSet();
dataset.addTable(new DefaultTable(tableName));
DatabaseOperation.DELETE_ALL.execute(conn, dataset);
}
/**
* 验证指定的表为空
*
* @param tableName
* @throws DataSetException
* @throws SQLException
*/
protected void verifyEmpty(String tableName) throws DataSetException,
SQLException {
Assert.assertEquals(0, conn.createDataSet().getTable(tableName)
.getRowCount());
}
}
使用：
Java代码
@Test
public void updateUser() throws Exception {
setUpDataSet("com/taobao/dbunit/dao/user001.xml");
User user = new User();
user.setNick("user001");
user.setPassword("password002");
userDao.update(user);
verifyDataSet("com/taobao/dbunit/dao/user001_updated.xml");
}
@Test
public void updateUser() throws Exception {
setUpDataSet("com/taobao/dbunit/dao/user001.xml");
User user = new User();
user.setNick("user001");
user.setPassword("password002");
userDao.update(user);
verifyDataSet("com/taobao/dbunit/dao/user001_updated.xml");
}
package com.test.dbunit.dao;
import javax.sql.DataSource;
import org.dbunit.Assertion;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.database.QueryDataSet;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.operation.DatabaseOperation;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.AbstractTransactionalJUnit4SpringContextTests;
import org.springframework.test.context.transaction.TransactionConfiguration;
import com.test.dbunit.entity.User;
@ContextConfiguration(locations = { "classpath:testApplicationContext.xml" })
@TransactionConfiguration(defaultRollback = true)
public class UserDaoTest extends AbstractTransactionalJUnit4SpringContextTests {
@Autowired
private UserDao userDao;
@Autowired
private DataSource dataSource;
private IDatabaseConnection conn;
@Before
public void initDbunit() throws Exception {
conn = new DatabaseConnection(DataSourceUtils.getConnection(dataSource));
}
@Test
public void saveUser() throws Exception {
User user = new User();
user.setNick("user001");
user.setPassword("password001");
userDao.save(user);
QueryDataSet actual = new QueryDataSet(conn);
actual.addTable("user",
"select * from user where user.nick = 'user001'");
IDataSet expected = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001.xml").getFile());
Assertion.assertEquals(expected, actual);
}
@Test
public void updateUser() throws Exception {
IDataSet origen = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001.xml").getFile());
DatabaseOperation.INSERT.execute(conn, origen);
User user = new User();
user.setNick("user001");
user.setPassword("password002");
userDao.update(user);
QueryDataSet actual = new QueryDataSet(conn);
actual.addTable("user",
"select * from user where user.nick = 'user001'");
IDataSet expected = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001_updated.xml").getFile());
Assertion.assertEquals(expected, actual);
}
@Test
public void removeUser() throws Exception {
IDataSet origen = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001.xml").getFile());
DatabaseOperation.INSERT.execute(conn, origen);
userDao.remove("user001");
QueryDataSet actual = new QueryDataSet(conn);
actual.addTable("user", "select * from user where nick = 'user001'");
Assert.assertEquals(0, actual.getTable("user").getRowCount());
}
@Test
public void findUser() throws Exception {
IDataSet data = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001.xml").getFile());
DatabaseOperation.INSERT.execute(conn, data);
User user = userDao.getUserByNick("user001");
Assert.assertEquals("password001", user.getPassword());
}
}
package com.test.dbunit.dao;
import javax.sql.DataSource;
import org.dbunit.Assertion;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.database.QueryDataSet;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.operation.DatabaseOperation;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.AbstractTransactionalJUnit4SpringContextTests;
import org.springframework.test.context.transaction.TransactionConfiguration;
import com.test.dbunit.entity.User;
@ContextConfiguration(locations = { "classpath:testApplicationContext.xml" })
@TransactionConfiguration(defaultRollback = true)
public class UserDaoTest extends AbstractTransactionalJUnit4SpringContextTests {
@Autowired
private UserDao userDao;
@Autowired
private DataSource dataSource;
private IDatabaseConnection conn;
@Before
public void initDbunit() throws Exception {
conn = new DatabaseConnection(DataSourceUtils.getConnection(dataSource));
}
@Test
public void saveUser() throws Exception {
User user = new User();
user.setNick("user001");
user.setPassword("password001");
userDao.save(user);
QueryDataSet actual = new QueryDataSet(conn);
actual.addTable("user",
"select * from user where user.nick = 'user001'");
IDataSet expected = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001.xml").getFile());
Assertion.assertEquals(expected, actual);
}
@Test
public void updateUser() throws Exception {
IDataSet origen = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001.xml").getFile());
DatabaseOperation.INSERT.execute(conn, origen);
User user = new User();
user.setNick("user001");
user.setPassword("password002");
userDao.update(user);
QueryDataSet actual = new QueryDataSet(conn);
actual.addTable("user",
"select * from user where user.nick = 'user001'");
IDataSet expected = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001_updated.xml").getFile());
Assertion.assertEquals(expected, actual);
}
@Test
public void removeUser() throws Exception {
IDataSet origen = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001.xml").getFile());
DatabaseOperation.INSERT.execute(conn, origen);
userDao.remove("user001");
QueryDataSet actual = new QueryDataSet(conn);
actual.addTable("user", "select * from user where nick = 'user001'");
Assert.assertEquals(0, actual.getTable("user").getRowCount());
}
@Test
public void findUser() throws Exception {
IDataSet data = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001.xml").getFile());
DatabaseOperation.INSERT.execute(conn, data);
User user = userDao.getUserByNick("user001");
Assert.assertEquals("password001", user.getPassword());
}
}
对Dao进行单元测试，一般有两种思路。一是Mock，对使用的底层API进行Mock，比如Hibernate和JDBC接口，判断接口有没有正确调用，另一种是实际访问数据库，判断数据库有没有正确读写。更多的情况下，我更倾向于后者，因为在使用ORM工具或者jdbcTemplate的情况下，dao一般只有简单的几行代码，没有复杂的逻辑，Mock测试一般没有什么意义，我们更关心的是，Hibernate mapping是否正确，ibatis sql是否正确等，所以实际读写数据库才能真正判断一个dao是否正确，这也是我们关心的测试内容。
好的单元测试应该是原子性的，独立的，不应依赖其他测试和上下文，但是要测试数据读写是否正确，就必须涉及初始数据的加载，数据修改的还原等操作。对于初始数据的加载，手动输入很麻烦，一个解决方案就是使用Dbunit，从Xml文件甚至Excel中加载初始数据到数据库，是数据库的值达到一个已知状态。同时还可以使用Dbunit，对数据库的结果状态进行判断，保证和期望的一致。数据修改的还原，可以依赖Spring TransactionalTests，在测试完成后回滚数据库。
Dbunit还可以对数据的现有数据进行备份，还原，清空现有数据，一个好的测试实践是每一个开发人员一个测试数据库，进而对数据库的数据状态有更好的控制，但现实可能会是共享同一个测试库，所以这种情况下，测试的编写必须多做一些考虑。
待测试的类：
Java代码
package com.test.dbunit.dao.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
import com.test.dbunit.dao.UserDao;
import com.test.dbunit.entity.User;
public class DefaultUserDao extends BaseDao implements UserDao {
private static String QUERY_BY_NICK = "select * from user where user.nick = ?";
private static String REMOVE_USER = "delete from user where user.nick = ?";
private static String INSERT_USER = "insert into user(nick,password) values(?, ?)";
private static String UPDATE_USER = "update user set user.password = ? where user.nick = ?";
@Override
public User getUserByNick(String nick) {
return (User) getJdbcTemplate().queryForObject(QUERY_BY_NICK,new Object[]{nick}, new RowMapper(){
@Override
public Object mapRow(ResultSet rs, int index) throws SQLException {
User user = new User();
user.setNick(rs.getString("nick"));
user.setPassword(rs.getString("password"));
return user;
}
});
}
@Override
public void remove(String nick) {
getJdbcTemplate().update(REMOVE_USER, new Object[]{nick});
}
@Override
public void save(User user) {
getJdbcTemplate().update(INSERT_USER, new Object[]{user.getNick(), user.getPassword()});
}
@Override
public void update(User user) {
getJdbcTemplate().update(UPDATE_USER, new Object[]{user.getPassword(), user.getNick()});
}
}
package com.test.dbunit.dao.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
import com.test.dbunit.dao.UserDao;
import com.test.dbunit.entity.User;
public class DefaultUserDao extends BaseDao implements UserDao {
private static String QUERY_BY_NICK = "select * from user where user.nick = ?";
private static String REMOVE_USER = "delete from user where user.nick = ?";
private static String INSERT_USER = "insert into user(nick,password) values(?, ?)";
private static String UPDATE_USER = "update user set user.password = ? where user.nick = ?";
@Override
public User getUserByNick(String nick) {
return (User) getJdbcTemplate().queryForObject(QUERY_BY_NICK,new Object[]{nick}, new RowMapper(){
@Override
public Object mapRow(ResultSet rs, int index) throws SQLException {
User user = new User();
user.setNick(rs.getString("nick"));
user.setPassword(rs.getString("password"));
return user;
}
});
}
@Override
public void remove(String nick) {
getJdbcTemplate().update(REMOVE_USER, new Object[]{nick});
}
@Override
public void save(User user) {
getJdbcTemplate().update(INSERT_USER, new Object[]{user.getNick(), user.getPassword()});
}
@Override
public void update(User user) {
getJdbcTemplate().update(UPDATE_USER, new Object[]{user.getPassword(), user.getNick()});
}
}
单元测试：
需要注意的地方就是，DataSourceUtils.getConnection(datasource) ， 通过这种方式获得数据库连接初始化Dbunit，能够保证Dbunit使用的数据连接和当前事务的数据库连接相同，保证能够在参与到事务中。Spring的TransactionManager会在开始事务时把当前连接保存到ThreadLocal中，DataSourceUtils.getConnection方法，首先从ThreadLocal中获取连接。
user001.xml
Xml代码
<?xml version="1.0" encoding="UTF-8"?>
<dataset>
<user nick="user001" password="password001" />
</dataset>
<?xml version="1.0" encoding="UTF-8"?>
<dataset>
<user nick="user001" password="password001" />
</dataset> 使用dbunit，可以通过xml文件定义数据集，也可以使用其他方式定义，比如Excel，编程方式。
Dbunit的主要构件
IDatabaseConnection
数据库链接。实现类有DatabaseConnection 和DatabaseDataSourceConnection ，执行数据库操作时需要一个连接。
IDataSet
数据集，数据集可以从Xml文件Excel等外部文件获取，也可以从数据库查询，或者编程方式构件，数据集可以作为初始数据插入到数据库，也可以作为断言的依据。另外还有IDatatable等辅助类。
比如在updateUser测试中，使用了QueryDataSet，从数据库中构建一个Dataset，再通过FlatXmlDataSet从Xml文件中构建一个Dataset，断言这两个Dataset相同。
Java代码
QueryDataSet actual = new QueryDataSet(conn);
actual.addTable("user", "select * from user where user.nick = 'user001'");
IDataSet expected = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001_updated.xml").getFile());
Assertion.assertEquals(expected, actual);
QueryDataSet actual = new QueryDataSet(conn);
actual.addTable("user", "select * from user where user.nick = 'user001'");
IDataSet expected = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001_updated.xml").getFile());
Assertion.assertEquals(expected, actual);
DatabaseOperation
通过定义的静态字段可以获取一组代表一个数据操作的子类对象，比如DatabaseOperation .INSERT，返回 InsertOperation，通过执行execute方法把数据集插入到数据库。例如：
Java代码
IDataSet origen = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001.xml").getFile());
DatabaseOperation.INSERT.execute(conn, origen);
IDataSet origen = new FlatXmlDataSet(new ClassPathResource(
"com/taobao/dbunit/dao/user001.xml").getFile());
DatabaseOperation.INSERT.execute(conn, origen); 从Xml文件中构建DataSet，使用Insert插入到数据库，初始化测试数据。
Assertion
唯一的方法，assertEqual，断言两个数据集或数据表相同。
更多关于Dbunit的组件的介绍：http://www.dbunit.org/components.html
PS:使用Oracle的时候，初始化DatabaseConnection需要传入scheme。new DatabaseConnection(conn,SCHEMA_NAME ) ，SCHMEA_NAME需要大写。
附件提供所有代码下载
一个DAO测试基类
Java代码
package com.taobao.dbunit.dao;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.dbunit.Assertion;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.DataSetException;
import org.dbunit.dataset.DefaultDataSet;
import org.dbunit.dataset.DefaultTable;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.operation.DatabaseOperation;
import org.junit.Assert;
import org.junit.Before;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.AbstractTransactionalJUnit4SpringContextTests;
import org.springframework.test.context.transaction.TransactionConfiguration;
@ContextConfiguration(locations = { "classpath:testApplicationContext.xml" })
@TransactionConfiguration(defaultRollback = true)
public class BaseDaoTest extends AbstractTransactionalJUnit4SpringContextTests {
@Autowired
private DataSource dataSource;
private IDatabaseConnection conn;
@Before
public void initDbunit() throws Exception {
conn = new DatabaseConnection(DataSourceUtils.getConnection(dataSource));
}
/**
* 清空file中包含的表中的数据，并插入file中指定的数据
*
* @param file
* @throws Exception
*/
protected void setUpDataSet(String file) throws Exception {
IDataSet dataset = new FlatXmlDataSet(new ClassPathResource(file)
.getFile());
DatabaseOperation.CLEAN_INSERT.execute(conn, dataset);
}
/**
* 验证file中包含的表中的数据和数据库中的相应表的数据是否一致
*
* @param file
* @throws Exception
*/
protected void verifyDataSet(String file) throws Exception {
IDataSet expected = new FlatXmlDataSet(new ClassPathResource(file)
.getFile());
IDataSet dataset = conn.createDataSet();
for (String tableName : expected.getTableNames()) {
Assertion.assertEquals(expected.getTable(tableName), dataset
.getTable(tableName));
}
}
/**
* 清空指定的表中的数据
*
* @param tableName
* @throws Exception
*/
protected void clearTable(String tableName) throws Exception {
DefaultDataSet dataset = new DefaultDataSet();
dataset.addTable(new DefaultTable(tableName));
DatabaseOperation.DELETE_ALL.execute(conn, dataset);
}
/**
* 验证指定的表为空
*
* @param tableName
* @throws DataSetException
* @throws SQLException
*/
protected void verifyEmpty(String tableName) throws DataSetException,
SQLException {
Assert.assertEquals(0, conn.createDataSet().getTable(tableName)
.getRowCount());
}
}
package com.taobao.dbunit.dao;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.dbunit.Assertion;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.DataSetException;
import org.dbunit.dataset.DefaultDataSet;
import org.dbunit.dataset.DefaultTable;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.operation.DatabaseOperation;
import org.junit.Assert;
import org.junit.Before;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.AbstractTransactionalJUnit4SpringContextTests;
import org.springframework.test.context.transaction.TransactionConfiguration;
@ContextConfiguration(locations = { "classpath:testApplicationContext.xml" })
@TransactionConfiguration(defaultRollback = true)
public class BaseDaoTest extends AbstractTransactionalJUnit4SpringContextTests {
@Autowired
private DataSource dataSource;
private IDatabaseConnection conn;
@Before
public void initDbunit() throws Exception {
conn = new DatabaseConnection(DataSourceUtils.getConnection(dataSource));
}
/**
* 清空file中包含的表中的数据，并插入file中指定的数据
*
* @param file
* @throws Exception
*/
protected void setUpDataSet(String file) throws Exception {
IDataSet dataset = new FlatXmlDataSet(new ClassPathResource(file)
.getFile());
DatabaseOperation.CLEAN_INSERT.execute(conn, dataset);
}
/**
* 验证file中包含的表中的数据和数据库中的相应表的数据是否一致
*
* @param file
* @throws Exception
*/
protected void verifyDataSet(String file) throws Exception {
IDataSet expected = new FlatXmlDataSet(new ClassPathResource(file)
.getFile());
IDataSet dataset = conn.createDataSet();
for (String tableName : expected.getTableNames()) {
Assertion.assertEquals(expected.getTable(tableName), dataset
.getTable(tableName));
}
}
/**
* 清空指定的表中的数据
*
* @param tableName
* @throws Exception
*/
protected void clearTable(String tableName) throws Exception {
DefaultDataSet dataset = new DefaultDataSet();
dataset.addTable(new DefaultTable(tableName));
DatabaseOperation.DELETE_ALL.execute(conn, dataset);
}
/**
* 验证指定的表为空
*
* @param tableName
* @throws DataSetException
* @throws SQLException
*/
protected void verifyEmpty(String tableName) throws DataSetException,
SQLException {
Assert.assertEquals(0, conn.createDataSet().getTable(tableName)
.getRowCount());
}
}
使用：
Java代码
@Test
public void updateUser() throws Exception {
setUpDataSet("com/taobao/dbunit/dao/user001.xml");
User user = new User();
user.setNick("user001");
user.setPassword("password002");
userDao.update(user);
verifyDataSet("com/taobao/dbunit/dao/user001_updated.xml");
}
@Test
public void updateUser() throws Exception {
setUpDataSet("com/taobao/dbunit/dao/user001.xml");
User user = new User();
user.setNick("user001");
user.setPassword("password002");
userDao.update(user);
verifyDataSet("com/taobao/dbunit/dao/user001_updated.xml");
}
分享到：
发表评论
相关推荐
NULL 博文链接：https://xiangxingchina.iteye.com/blog/1752521
一个很好的DBUnit的例子 博文链接：https://virgoooos.iteye.com/blog/186859
unitils整合dbunit利用excel进行单元测试 包含mock以及整合spring进行测试
NULL 博文链接：https://xiongzhenhui.iteye.com/blog/802984
在最近的一个项目中，我尝试使用用DBUnit对Spring+iBatis的架构进行测试，下面记录了DBUnit的使用过程和遇到的一些问题。首先，我们建立一个测试环境（基于Maven2和Oracle数据库*）。数据表名Account。先建立一个...
用DbUnit进行SqlMap单元测试 摘自:http://www.uml.org.cn/Test/200811278.asp
下面小编就为大家带来一篇对dbunit进行mybatis DAO层Excel单元测试(必看篇)。小编觉得挺不错的，现在就分享给大家，也给大家做个参考。一起跟随小编过来看看吧
采用 Java 编写的数据库系统单元测试程序。
DbUnit是一款开源的数据库功能测试框架,使用它可以对数据库的基本操作进行白盒单元测试,对输入输出进行校验,从而保证数据的有效性。DbUnit使用XML文件提供测试数据,为测试人员增加了测试难度,降低了单元测试效率。...
单元测试入门学习，和dbunit结合开发
Junit,dbunit单元测试jar包
1. 测试环境的搭建（DBunit+HSQLDB） 1 1.1. DBunit的简介 1 1.1.1. DBunit简单介绍和原理 1 1.1.2. DBunit的三大核心组件 1 1.1.3. DBunit的安装使用 2 1.2. HSQLDB简介 3 1.2.2. 什么是HSQLDB 3 1.2.3. HSQLDB安装...
自述文件这个 repo 是我尝试使用 Spring Test DBUnit 实现的示例。 基本上我在 src/main/java/com/springtests/model 中有三个实体节目表演者每个都有自己的 ID 字段，并与具有三列的连接表 (prog_performer) 建立多...
dbunit2.2完全包 数据库单元测试
NULL 博文链接：https://yugouai.iteye.com/blog/1876827
DBUNIT 基类DBUNIT 基类DBUNIT 基类DBUNIT 基类DBUNIT 基类DBUNIT 基类DBUNIT 基类DBUNIT 基类DBUNIT 基类DBUNIT 基类DBUNIT 基类DBUNIT 基类DBUNIT 基类DBUNIT 基类DBUNIT 基类DBUNIT 基类DBUNIT 基类DBUNIT 基类...
2.3.4 在Spring中装配DAO 2.4 业务层 2.4.1 UserService 2.4.2 在Spring中装配Service 2.4.3 单元测试 2.5 展现层 2.5.1 配置Spring MVC框架 2.5.2 处理登录请求 2.5.3 JSP视图页面 2.6 运行Web应用 2.7 小结 第2篇...