DBUnit

DBUnit官方网站:www.dbunit.org


About DbUnit
DbUnit is a JUnit extension (also usable with Ant) targeted at database-driven projects that, among other things, puts your database into a known state between test runs. This is an excellent way to avoid the myriad of problems that can occur when one test case corrupts the database and causes subsequent tests to fail or exacerbate the damage.

DbUnit has the ability to export and import your database data to and from XML datasets. Since version 2.0, DbUnit can also work with very large datasets when used in streaming mode. DbUnit can also help you to verify that your database data match an expected set of values.

1.添加dbunit-2.3.0.jar


DBUnit依赖于Simple Logging Facade for Java (SLF4J)项目 官网:http://www.slf4j.org/

2.添加slf4j-api-1.5.3.jar

3.添加slf4j-jcl-1.5.3.jar

SLF4J依赖于Apache的 Commons-Logging.

4.添加commons-logging.jar

★ 导出数据库中的数据到xml文件

------------------------------------------------------------------------------------------------------------

package com.test.dbunit;

import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;

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.FlatDtdDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSet;

public class Test {
public static void main(String[] args) throws Exception {
 
  Class.forName("com.mysql.jdbc.Driver");
  Connection conn = DriverManager.getConnection(
    "jdbc:mysql://localhost/dbunit","root","admin");
  //使用DBunit的DatabaseConnection类封装jdbc的连接，它实现了接口IDatabaseConnection
  IDatabaseConnection connection =new DatabaseConnection(conn);
  //QueryDataSet和.net中的数据集的概念类似，它是数据库的一个映像
  QueryDataSet partial=new QueryDataSet(connection);
  //把task表中的数据导出到xml文件中
  partial.addTable("task");
 
  //partial.addTable("users","select * from users where id= 1 ");
  partial.addTable("users");
  //把数据内容导出到xml文件中
  FlatXmlDataSet.write(partial,new FileOutputStream("partial.xml"));
 
  //将数据库中所有的数据导出
  IDataSet full =connection.createDataSet();
  FlatXmlDataSet.write(full, new FileOutputStream("full.xml"));
  //导出Dtd文件
  FlatDtdDataSet.write(full, new FileOutputStream("full.dtd"));
}

}
------------------------------------------------------------------------------------------------------------

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE dataset SYSTEM "full.dtd">
<dataset>
  <task/>
  <users id="1" username="张三" password="123"/>
  <users id="2" username="李四" password="456"/>
  <users id="3" username="王五" password="789"/>
</dataset>
------------------------------------------------------------------------------------------------------------

<!ELEMENT dataset (
    task*,
    users*)>

<!ELEMENT task EMPTY>
<!ATTLIST task
    id CDATA #IMPLIED
    name CDATA #IMPLIED
    description CDATA #IMPLIED
>

<!ELEMENT users EMPTY>
<!ATTLIST users
    id CDATA #REQUIRED
    username CDATA #REQUIRED
    password CDATA #REQUIRED
>

------------------------------------------------------------------------------------------------------------------

//使用dbunti测试的简单例子

package com.test.dbunit;

import java.io.File;
import java.io.FileInputStream;

import org.dbunit.Assertion;
import org.dbunit.DBTestCase;
import org.dbunit.PropertiesBasedJdbcDatabaseTester;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ITable;
import org.dbunit.dataset.SortedTable;
import org.dbunit.dataset.filter.DefaultColumnFilter;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.operation.DatabaseOperation;

public class SampleTest extends DBTestCase{
    //重写构造方法
public SampleTest(String name){
  super(name);
  //在系统属性中添加数据库练接用到的属性
  System.setProperty(
    PropertiesBasedJdbcDatabaseTester.DBUNIT_DRIVER_CLASS,
    "com.mysql.jdbc.Driver");
  System.setProperty(
    PropertiesBasedJdbcDatabaseTester.DBUNIT_CONNECTION_URL,
    "jdbc:mysql://localhost/dbunit");
  System.setProperty(
    PropertiesBasedJdbcDatabaseTester.DBUNIT_USERNAME,
    "root");
  System.setProperty(
    PropertiesBasedJdbcDatabaseTester.DBUNIT_PASSWORD,
    "admin");
}
/**
  * 在每次测试执行之前都先执行getSetUpOperation()操作
  */
public DatabaseOperation getSetUpOperation() throws Exception{
  //默认在setUPOperation中就是执行CLEAN_INSERT ，
  //CLEAN_INSERT是DELETE_ALL和INSERT的组合，数据库会恢复到xml文件中的数据。
  return DatabaseOperation.CLEAN_INSERT;
  //刷新会更新xml内容到数据库中，数据存，xml中都存在的updata,数据库不存在insert,
  //数据库中有xml中没有的保持不变
  //return DatabaseOperation.REFRESH;
}
/**
  * 每次测试执行之后会执行该操作。
  */
public DatabaseOperation getTearDownOperation() throws Exception{
  //什么都不做--默认
  //return DatabaseOperation.NONE;
  //清空数据库
  return DatabaseOperation.DELETE_ALL;
}

/**
  * 将数据文件转换成数据集,这个方法是在dbunit启动的时候自动启动
  */
@Override
protected IDataSet getDataSet() throws Exception {
  return new FlatXmlDataSet(new FileInputStream("full.xml"));
}

public void test1() throws Exception{
  IDataSet dataSet = getConnection().createDataSet();
  //数据库中实际的表
  ITable actualTable = dataSet.getTable("users");
  //期望值
  IDataSet dataSet2 = new FlatXmlDataSet(new File("full.xml"));
  ITable expectedTable = dataSet2.getTable("users");
  //DBUnit的Assertion类
  Assertion.assertEquals(expectedTable,actualTable);
}

//比较过滤二种方法之一,包含哪些列
public void test2() throws Exception{
     IDataSet dataSet = getConnection().createDataSet();
     ITable  actualTable = dataSet.getTable("users");
 
     //期望值-没有id列
     IDataSet dataSet2 = new FlatXmlDataSet(new File("Full2.xml"));
     ITable expectedTable = dataSet2.getTable("users");
     //用过滤器过滤掉actualtable的中的id列
     actualTable = DefaultColumnFilter.includedColumnsTable(actualTable,
       expectedTable.getTableMetaData().getColumns());
     Assertion.assertEquals(expectedTable, actualTable);
}
//方法2，比较用户名相同的。排除掉不需要比较的各个字段，
public void test3() throws Exception{
  IDataSet dataSet =getConnection().createDataSet();
  ITable actualTable = dataSet.getTable("users");
 
  IDataSet dataSet2 = new FlatXmlDataSet(new File("full2.xml"));
  ITable expectedTable = dataSet2.getTable("users");
 
  ITable filterActualTable =DefaultColumnFilter.excludedColumnsTable(
    actualTable, new String[]{"id","password"});
 
        ITable filterExpectedTable =DefaultColumnFilter.excludedColumnsTable(
          expectedTable,new String[]{"password"});
       
        Assertion.assertEquals(filterExpectedTable,filterActualTable);
}
//排序表格
public void test4() throws Exception{
  IDataSet dataSet = getConnection().createDataSet();
  //数据库中实际的表
  ITable actualTable = dataSet.getTable("users");
  //期望值
  IDataSet dataSet2 = new FlatXmlDataSet(new File("expected.xml"));
  ITable expectedTable = dataSet2.getTable("users");
  //把表按照字段排序，默认是按照字符串排序
  SortedTable sortedTable1 = new SortedTable(actualTable,new String[]{"id"});
  //按照数据库中字段排序
  sortedTable1.setUseComparable(true);
  SortedTable sortedTable2 = new SortedTable(expectedTable,new String[]{"id"});
  //按照数据库中字段排序
  sortedTable2.setUseComparable(true);
  //DBUnit的Assertion类
  Assertion.assertEquals(sortedTable2,sortedTable1);
}
// 表操作测试的实例：

public void testSave() throws Exception{
  UsersDB db =new UsersDB();
  Users users =new Users();
  users.setId(9);
  users.setUserName("langsin");
  users.setPassword("helloworld");
 
  db.save(users);
  IDataSet dataSet = getConnection().createDataSet();
  ITable actualTable = dataSet.getTable("users");
  IDataSet dataSet2 = new FlatXmlDataSet(new File("expected2.xml"));
  ITable expectedTable =dataSet.getTable("users");
  Assertion.assertEquals(expectedTable, actualTable);
}



------------------------------------------------------------------------

package com.test.bean;

public class Users {
private int id;
private String userName;
private String password;
public int getId() {
  return id;
}
public void setId(int 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;
}


}

------------------------------------------------------------------------

package com.test.dbunit;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import com.test.bean.Users;

public class UsersDB {
public void save(Users users){
  Connection conn=null;
  try{
   Class.forName("com.mysql.jdbc.Driver");
   conn=DriverManager.getConnection("jdbc:mysql://localhost/dbunit",
     "root", "admin");
   String sql="insert into users(id,username,password) values(?,?,?)";
   PreparedStatement ps =conn.prepareStatement(sql);
   ps.setInt(1, users.getId());
   ps.setString(2,users.getUserName());
   ps.setString(3,users.getPassword());
   ps.executeUpdate();
  }catch(Exception ex){
   ex.printStackTrace();
  }finally
  {
   if(null!= conn)
   {
    try
    {
     conn.close();
    }catch(SQLException e){
     e.printStackTrace();
    }
   
   }
  }
}

}
---------------------------expected2.xml------------------------------

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE dataset SYSTEM "full.dtd">
<dataset>
  <task/>
  <users id="1" username="张三" password="123"/>
  <users id="2" username="李四" password="456"/>
  <users id="3" username="王五" password="789"/>
  <users id="9" username="langsin" password="helloworld"/>
</dataset>

***********************************************************************

使用DBUnit进行单元测试的基本流程

1.根据业务，做好测试用的准备数据和预想结果数据，通常为XML格式文件。

2.在setUp()方法里面边备份数据库中的关联表。

3.在setUP()方法里面边读入准备数据。

4.对测试类的对应测试方法进行调用:执行对象方法，把数据库中的实际执行

结果和预想结果进行比较。

5.在tearDown()方法里面，把数据库还原到测试前状态。

***********************************************************************