一、DbUnit设计理念
熟悉单元测试的开发人员都知道，在对数据库进行单元测试时候，通常采用的方案有运用模拟对象(mock objects)和stubs两种。通过隔离关联的数据库访问类，比如JDBC的相关操作类，来达到对数据库操作的模拟测试。然而某些特殊的系统，比如利 用了EJB的CMP(container-managed persistence)的系统，数据库的访问对象是在最底层而且很隐蔽的，那么这两种解决方案对这些系统就显得力不从心了。
DBUnit的设计理念就是在测试之前，备份数据库，然后给对象数据库植入我们需要的准备数据，最后，在测试完毕后，读入备份数据库，回溯到测试前的状态；而且又因为DBUnit是对JUnit的一种扩展，开发人员可以通过创建测试用例代码，在这些测试用例的生命周期内来对数据库的操作结果进行比较。
二、DbUnit测试基本概念和流程
基于DbUnit 的测试的主要接口是IDataSet。IDataSet代表一个或多个表的数据。
可以将数据库模式的全部内容表示为单个IDataSet 实例。这些表本身由Itable 实例来表示。
IDataSet 的实现有很多，每一个都对应一个不同的数据源或加载机制。最常用的几种 IDataSet实现为:
FlatXmlDataSet：数据的简单平面文件 XML 表示 QueryDataSet：用 SQL 查询获得的数据 DatabaseDataSet：数据库表本身内容的一种表示 XlsDataSet ：数据的excel表示
DBUnit支持的数据库包括，db2,h2,hsqldb,mckoi,mssql,mysql,netezza,oralce,postgresql.
三、DBUnit测试流程：
一般而言，使用DbUnit进行单元测试的流程如下：1 根据业务，做好测试用的准备数据和预想结果数据，通常准备成xml格式文件。2 在setUp()方法里边备份数据库中的关联表。3 在setUp()方法里边读入准备数据。4 对测试类的对应测试方法进行实装:执行对象方法，把数据库的实际执行结果和预想结果进行比较。5 在tearDown()方法里边,把数据库还原到测试前状态。
四、实例开发：
1、新建一个pom工程，加入相关依赖:
<dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.11</version><scope>test</scope></dependency><dependency><groupId>org.dbunit</groupId><artifactId>dbunit</artifactId><version>2.5.3</version></dependency><dependency><groupId>org.slf4j</groupId><artifactId>slf4j-nop</artifactId><version>1.7.24</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.38</version></dependency>
2、新建工程相关目录
BaseDao.java文件：
public class BaseDao {private static Connection CONNECTION_INSTANCE = null;protected Connection getConnection() throws Exception{if(null==CONNECTION_INSTANCE){Class.forName("com.mysql.jdbc.Driver");CONNECTION_INSTANCE=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test","root","root");}return CONNECTION_INSTANCE;}protected void closeConnection() throws Exception{if(null!=CONNECTION_INSTANCE){if(!CONNECTION_INSTANCE.isClosed()){CONNECTION_INSTANCE.close();}CONNECTION_INSTANCE=null;}}}
DBUnitUtils.java文件
public class DBUnitUtils {//产生数据集public static void generateDatasetDtd(String[] tables) throws Exception{QueryDataSet dataSet=new QueryDataSet(DButils.getDataBaseConnection());for(String _table:tables){dataSet.addTable(_table);}FlatDtdDataSet.write(dataSet, new FileOutputStream(new File("resource/tmp.dtd")));//FlatXmlDataSet.write(dataSet,new FileOutputStream(new File("resource/dbunit1.xml")));}//备份表数据public static void backupDatabase(String[] tables,File backupFile) throws Exception{QueryDataSet dataSet=new QueryDataSet(DButils.getDataBaseConnection());for(String _table:tables){dataSet.addTable(_table);}FlatXmlDataSet.write(dataSet, new FileOutputStream(backupFile));}//清空表数据，并导入测试数据public static void importTables(File dataFile) throws Exception{IDataSet dataSet=new FlatXmlDataSetBuilder().build(dataFile);DatabaseOperation.CLEAN_INSERT.execute(DButils.getDataBaseConnection(), dataSet);}//清空表数据，恢复备份数据public static void resumeDatabase(File backupFile) throws Exception{IDataSet dataSet=new FlatXmlDataSetBuilder().build(backupFile);DatabaseOperation.CLEAN_INSERT.execute(DButils.getDataBaseConnection(), dataSet); }}
DButils.java文件
public class DButils {private static IDatabaseConnection conn;//通过dbUnit创建数据库连接public static IDatabaseConnection getDataBaseConnection() throws ClassNotFoundException, SQLException, DatabaseUnitException{if(conn==null){Class.forName("com.mysql.jdbc.Driver");Connection dbConn=DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");conn =new MySqlConnection(dbConn,"test");return conn;}return conn;}//关闭数据库连接public static void closeConnection(){if(conn!=null){try {conn.close();} catch (SQLException e) {e.printStackTrace();}}conn=null;}}
User.java文件
public class User { private long id;private String username;private String password;private String name;public long getId() {return id;}public void setId(long id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}public String getName() {return name;}public void setName(String name) {this.name = name;} }
UserService.java文件
public interface UserService {public long create(User user) throws Exception;public void update(User user) throws Exception;public User get(Long id) throws Exception;public void delete(Long id) throws Exception;public List<User> list() throws Exception;}
UserServiceImpl.java文件
public class UserServiceImpl extends BaseDao implements UserService{//新增用户返回主键public long create(User user) throws Exception {Long id=null;PreparedStatement stm=getConnection().prepareStatement("insert into user(username, password, name) values(?, ?, ?)", Statement.RETURN_GENERATED_KEYS);stm.setString(1, user.getName());stm.setString(2, user.getPassword());stm.setString(3, user.getName());stm.executeUpdate();ResultSet rs=stm.getGeneratedKeys();if(rs.first()){id=rs.getLong(1);}return id;}//更新用户public void update(User user) throws Exception {PreparedStatement stm = getConnection().prepareStatement("update user set password = ?, name = ? where username = ?", Statement.RETURN_GENERATED_KEYS);stm.setString(1, user.getPassword());stm.setString(2, user.getName());stm.setString(3, user.getUsername());stm.executeUpdate();closeConnection();}//获取用户public User get(Long id) throws Exception {User user = null;PreparedStatement stm = getConnection().prepareStatement("select username, password, name from user where id=?");stm.setLong(1, id);ResultSet rs = stm.executeQuery();if (rs.first()) {user = new User();user.setUsername(rs.getString(1));user.setPassword(rs.getString(2));user.setName(rs.getString(3));user.setId(id);}closeConnection();return user;}//删除用户public void delete(Long id) throws Exception {PreparedStatement stm = getConnection().prepareStatement("delete from user where id=?");stm.setLong(1, id);stm.executeUpdate();closeConnection(); }//返回所有用户列表public List<User> list() throws Exception {List<User> users = new ArrayList<User>();PreparedStatement stm = getConnection().prepareStatement("select username, password, name, id from user");ResultSet rs = stm.executeQuery();while (rs.next()) {User user = new User();user.setUsername(rs.getString(1));user.setPassword(rs.getString(2));user.setName(rs.getString(3));user.setId(rs.getLong(4));users.add(user);}closeConnection();return users;}}
UserServiceDBTestCase.java文件
public class UserServiceDBTestCase extends DatabaseTestCase{private UserService userService;@Override//新建一个userDao对象protected void setUp() throws Exception {super.setUp();userService = new UserServiceImpl();}@Override//清空userDao对象protected void tearDown() throws Exception {super.tearDown();userService = null;}@Override//获取数据库连接protected IDatabaseConnection getConnection() throws Exception {Class.forName("com.mysql.jdbc.Driver");Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");return new MySqlConnection(conn, "test");}//获取输入数据流 @Overrideprotected IDataSet getDataSet() throws Exception {InputStream is = getClass().getResourceAsStream("/test-data.xml");return new FlatXmlDataSetBuilder().build(is);}//测试数据的创建public void testCreate() throws Exception{User user = new User();user.setName("testdata");user.setPassword("testPassword");user.setUsername("testAdmin");Long id = userService.create(user);assertNotNull(id);User dbUser =userService.get(id);assertNotNull(dbUser);assertEquals("testAdmin", dbUser.getUsername());assertEquals("testPassword", dbUser.getPassword());assertEquals("testAdministrator", dbUser.getName());}public void testGet() throws Exception{User user = userService.get(1L);assertNotNull(user);assertEquals("admin1", user.getUsername());assertEquals("123", user.getPassword());assertEquals("Administrator1", user.getName());User nullUser = userService.get(0L);assertNull(nullUser);}public void testDelete() throws Exception{User user = userService.get(1L);assertNotNull(user);userService.delete(1L);User nullUser = userService.get(1L);assertNull(nullUser);}public void testList() throws Exception{List<User> users = userService.list();assertNotNull(users);assertEquals(4, users.size());}}
UserServiceTestCase.java文件
public class UserServiceTestCase extends TestCase{private UserService userService; private File backupFile=new File("resource/backup-data.xml");@Overrideprotected void setUp() throws Exception {userService=new UserServiceImpl();}@Beforepublic void setUpData() throws Exception {//备份原来的数据DBUnitUtils.backupDatabase(new String []{"user"}, backupFile);//导入测试数据DBUnitUtils.importTables(new File("resource/test-data.xml"));}@Afterpublic void tearDownData() throws Exception{//恢复备份数据 DBUnitUtils.resumeDatabase(backupFile);//关闭数据库连接 DButils.closeConnection();}public void testCreate() throws Exception{DBUnitUtils.importTables(new File("resource/test-data.xml"));User user = new User();DBUnitUtils.resumeDatabase(backupFile);DButils.closeConnection();user.setName("testdata");user.setPassword("testPassword");user.setUsername("testAdmin");Long id = userService.create(user);assertNotNull(id);User dbUser =userService.get(id);assertNotNull(dbUser);assertEquals("testAdmin", dbUser.getUsername());assertEquals("testPassword", dbUser.getPassword());assertEquals("testAdministrator", dbUser.getName());}public void testGet() throws Exception{User user = userService.get(1L);assertNotNull(user);assertEquals("admin1", user.getUsername());assertEquals("123", user.getPassword());assertEquals("Administrator1", user.getName());User nullUser = userService.get(0L);assertNull(nullUser);}public void testDelete() throws Exception{User user = userService.get(1L);assertNotNull(user);userService.delete(1L);User nullUser = userService.get(1L);assertNull(nullUser);}public void testList() throws Exception{ List<User> users = userService.list();assertNotNull(users);assertEquals(4, users.size());}}
test-data.xml文件
<?xml version='1.0' encoding='UTF-8'?><dataset><user id="1" username="test-user1" password="test-user1" name="test-user1"/><user id="2" username="test-user2" password="test-user2" name="test-user2"/></dataset>
接下来测试结果：
demo代码下载： dbunitDemo
总结:
使用了DBUnit后可以实现了对数据库的隔离,成功弥补了JUnit单元测试不清理数据现场的缺憾,实际上DBUnit只是简单的在单元测试前把数据库的数据进行了备份然后插入xml中配置好的数据,在测试结束后再用备份好的原数据库数据填充回数据库.但当面对复杂的表关系和大数据量的时候,每次进行测试都进行数据的备份,也是一个很大的负担,而且把全部的测试数据都编写在xml当中也是很大的工作量
