DBUnit的简单实践

 

我不知道什么是失败，我也不知道失败后会是怎么样的天和地，但已经做过了，就不要放弃，做过了，就应该努力

 

我文章的前头我想说，这篇文章的偷学的。参考：http://kxrs.iteye.com/blog/248831

 

个人因为作者还是比较明白的，但文章的排版实在不敢恭维。好像还是。。。不管了，自己会就行嘿嘿

 

下面我把原作者的东西在创造了一下：

 

第一：DBUnit需要的jar包

 

 

<dependency>

<groupId>org.dbunit</groupId>

<artifactId>dbunit</artifactId>

<version>2.4.9</version>

         </dependency>

         <dependency>

             <groupId>org.slf4j</groupId>

             <artifactId>slf4j-api</artifactId>

             <version>1.5.8</version>

         </dependency>

    也可以直接下载：

            DBUnit  http://sourceforge.net/projects/dbunit/files/

            SLF4j    http://www.slf4j.org/

第二：创建应用工具

 

 

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
>
------------------------------------------------------------------------------------------------------------------

 

 

测试实例：

 

 

package com.java;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import org.dbunit.DBTestCase;
import org.dbunit.PropertiesBasedJdbcDatabaseTester;
import org.dbunit.database.DatabaseConfig;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatDtdDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
import org.dbunit.operation.DatabaseOperation;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.junit.runners.JUnit4;

@RunWith(JUnit4.class)
public class DaoTest extends DBTestCase {

    protected DatabaseOperation getSetUpOperation() throws Exception {
        return DatabaseOperation.CLEAN_INSERT;
    }

    protected DatabaseOperation getTearDownOperation() throws Exception {
        return DatabaseOperation.DELETE_ALL;
    }

    protected void setUpDatabaseConfig(DatabaseConfig config) {
        config.setProperty(DatabaseConfig.PROPERTY_BATCH_SIZE, new Integer(97));
        config.setFeature(DatabaseConfig.FEATURE_BATCHED_STATEMENTS, true);
    }

    public DaoTest() {
        System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_DRIVER_CLASS, "com.mysql.jdbc.Driver ");
        System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_CONNECTION_URL, "jdbc:mysql://localhost:3306/test");
        System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_USERNAME, "root");
        System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_PASSWORD, "24226305");
        // System.setProperty( PropertiesBasedJdbcDatabaseTester.DBUNIT_SCHEMA, "" );
    }

    protected IDataSet getDataSet() throws Exception {
        return new FlatXmlDataSetBuilder().build(new FileInputStream("dataset.xml"));
    }

    @Test
    public void DBtest() throws Exception {

        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "24226305");
        IDatabaseConnection connection = new DatabaseConnection(conn);
        FlatDtdDataSet.write(connection.createDataSet(), new FileOutputStream("my-dataset.dtd"));
        //DatabaseOperation.INSERT.execute(connection, getDataSet());

        Statement state = conn.createStatement();
        ResultSet rs = state.executeQuery("select * from User");
        while (rs.next()) {
            System.out.println(rs.getString("userName"));
        }
    }
}

 

 

 

 

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);
}

 

      至于原作者在后面写的注意，我个人感觉有写不妥的地方

 

1. 我感觉创建数据的时候应该在@BeforeClass里面做

        2.删除数据的时候应该在@AfterClass里面做

 

    还有就是DBUnit我个人感觉非常想框架，而且比什么Dao好的非常多，但比不上Hibernate什么的持久层框架那么好。所以可以堪称为小Dao

   希望我的拙见，能让大伙学到东西