Maven环境下Dbunit开发入门实例

菜鸟开发，在最近的项目中发项目测试和功能开发都是共用一套数据库，经常获取奇怪的结果，经过半天的排错，最后是数据中有重复的脏数据项导致结果不一致，这样感觉很浪费时间，这个完全是可以避免的，就是在测试开发的功能是先吧数据库备份，操作完后，还原即可，但是如果这个过程全部有自己手动实现，这个也是有点头疼，还好不用重复造轮子，我们能想到的有人都实现了，只是咋菜鸟不知道而已，其中一个框架就是Dbunit。

先期准备

1. 首先来看下数据库的数据
   
2. 看下项目的树形目录图
   
   其中t_user的内容为：
   
3. 接着我们看下pom中的具体jar包

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>org.tianjun.dbunit</groupId>
  <artifactId>dabunit-test</artifactId>
  <packaging>war</packaging>
  <version>0.0.1-SNAPSHOT</version>
  <name>dabunit-test Maven Webapp</name>
  <url>http://maven.apache.org</url>
  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.10</version>
      <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.dbunit</groupId>
        <artifactId>dbunit</artifactId>
        <version>2.5.1</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.38</version>
    </dependency>
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-api</artifactId>
        <version>1.7.13</version>
    </dependency>
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-log4j12</artifactId>
        <version>1.7.13</version>
    </dependency>
    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.17</version>
    </dependency>
  </dependencies>
  <build>
    <finalName>dabunit-test</finalName>
  </build>
</project>

实现过程

功能实现类

• IUserDao.java

package dao;

import model.User;

public interface IUserDao {
    public void add(User user);
    public void delete(String username);
    public User load(String username);
}

• UserDao.java

package dao.impl;

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

import uitls.DbUtil;
import model.User;
import dao.IUserDao;

public class UserDao implements IUserDao {

    @Override
    public void add(User user) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = DbUtil.getConnection();
            String sql = "insert into t_user(username,nickname,password) value (?,?,?)";
            ps = con.prepareStatement(sql);
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getNickname());
            ps.setString(3, user.getPassword());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DbUtil.close(ps);
            DbUtil.close(con);
        }

    }

    @Override
    public void delete(String username) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = DbUtil.getConnection();
            String sql = "delete from t_user where username=?";
            ps = con.prepareStatement(sql);
            ps.setString(1, username);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DbUtil.close(ps);
            DbUtil.close(con);
        }
    }

    @Override
    public User load(String username) {
        User u = null;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = DbUtil.getConnection();
            String sql = "select * from t_user where username=?";
            ps = con.prepareStatement(sql);
            ps.setString(1, username);
            rs = ps.executeQuery();
            while (rs.next()) {
                if(u==null) u =new User();
                u.setId(rs.getInt("id"));
                u.setNickname(rs.getString("nickname"));
                u.setPassword(rs.getString("password"));
                u.setUsername(rs.getString("username"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DbUtil.close(rs);
            DbUtil.close(ps);
            DbUtil.close(con);
        }

        return u;
    }

}

• User.java

package model;

public class User {
    private int id;
    private String username;
    private String password;
    private String nickname;

    public User() {

    }
    public User(String username, String password, String nickname) {
        super();
        this.username = username;
        this.password = password;
        this.nickname = nickname;
    }

    public User(int id, String username, String password, String nickname) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.nickname = nickname;
    }
    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;
    }
    public String getNickname() {
        return nickname;
    }
    public void setNickname(String nickname) {
        this.nickname = nickname;
    }

}

• IUserService.java

package service;

import model.User;


public interface IUserService {
    public void add(User user);

    public void delete(String username);

    public User load(String username);

    public User login(String username,String password);
}

• UserService.java

package service.impl;

import model.User;
import dao.IUserDao;
import dao.impl.UserDao;
import exceptions.UserException;
import service.IUserService;


public class UserService implements IUserService {
    private IUserDao userDao;

    public UserService(IUserDao userDao) {
        this.userDao = userDao;
    }

    public UserService() {
        userDao = new UserDao();
    }

    @Override
    public void add(User user) {
        if(load(user.getUsername())!=null)
            throw new UserException("用户名已经存在");
        userDao.add(user);
    }

    @Override
    public void delete(String username) {
        userDao.delete(username);
    }

    @Override
    public User load(String username) {
        return userDao.load(username);
    }

    @Override
    public User login(String username, String password) {
        User u = load(username);
        if(u==null) throw new UserException("用户名不存在");
        if(!u.getPassword().equals(password)) throw new UserException("用户密码不存在");
        return u;
    }

}

• UserException.java

package exceptions;

@SuppressWarnings("serial")
public class UserException extends RuntimeException {

    public UserException() {
        super();
    }

    public UserException(String message, Throwable cause) {
        super(message, cause);
    }

    public UserException(String message) {
        super(message);
    }

    public UserException(Throwable cause) {
        super(cause);
    }

}

• Dbutil.java

package uitls;

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

public class DbUtil {
    public static Connection getConnection() throws SQLException {
        Connection con = null;
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/tianjun_dbunit?useUnicode=true&characterEncoding=utf-8", 
                "root", "123");
        return con;
    }

    public static void close(Connection con) {
        try {
            if(con!=null) con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(PreparedStatement ps) {
        try {
            if(ps!=null) ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(ResultSet rs) {
        try {
            if(rs!=null) rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

测试类的实现

• TestDbunit.java

package org.tianjun.dbunit;
import static junit.framework.Assert.*;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.SQLException;

import junit.framework.Assert;
import model.User;

import org.dbunit.DatabaseUnitException;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.database.QueryDataSet;
import org.dbunit.dataset.DataSetException;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.dataset.xml.FlatXmlProducer;
import org.dbunit.operation.DatabaseOperation;
import org.junit.Test;
import org.xml.sax.InputSource;

import dao.IUserDao;
import dao.impl.UserDao;
import uitls.DbUtil;

public class TestDbunit {
    @Test
    public void testLoad(){
        try {
            testBackuTable();
            IDatabaseConnection con = new DatabaseConnection(DbUtil.getConnection());
            /**
             * 1、FlatXmlDataSet用来获取基于属性存储的属性值
             * 2、XMLDataSet用来获取基于节点类型存储的属性值
             */
            IDataSet ds = new FlatXmlDataSet(
                    new FlatXmlProducer(
                            new InputSource(TestDbunit.class.getClassLoader()
                                    .getResourceAsStream("t_user.xml"))));
            /**
             * 将数据库中的数据清空，并且把测试数据插入
             */
            DatabaseOperation.CLEAN_INSERT.execute(con, ds);

            IUserDao ud = new UserDao();
            User tu = ud.load("admin");
            assertEquals(tu.getId(), 1);
            assertEquals(tu.getUsername(), "admin");
            assertEquals(tu.getPassword(), "123");
            assertEquals(tu.getNickname(), "超级管理员");
        } catch (DatabaseUnitException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        testResume();
    }

    /**
     * 备份数据库所有的表
     */
    @Test
    public void testBackupAllTable(){
        try {
            IDatabaseConnection con = new DatabaseConnection(DbUtil.getConnection());
            IDataSet ds = con.createDataSet();

            FlatXmlDataSet.write(ds, new FileWriter("d:/test.xml"));


        } catch (DatabaseUnitException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    /**
     * 备份某张表
     * @return
     */
    @Test
    public void testBackuTable(){
        try {
            IDatabaseConnection con = new DatabaseConnection(DbUtil.getConnection());
            /*
             * 通过QueryDataSet可以有效的选择要处理的表来作为数据集
             */
            QueryDataSet backup = new QueryDataSet(con);
            /*
             * 添加t_user这张表作为备份表
             */
            backup.addTable("t_user");
            FlatXmlDataSet.write(backup, new FileWriter("d:/test1.xml"));

        } catch (DatabaseUnitException | SQLException | IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 还原数据库表
     */
    @Test
    public void testResume(){
        try {
            IDatabaseConnection con = new DatabaseConnection(DbUtil.getConnection());
            IDataSet ds = new FlatXmlDataSet(new FlatXmlProducer(
                    new InputSource(new FileInputStream("d:/test1.xml"))));
            DatabaseOperation.CLEAN_INSERT.execute(con, ds);
        } catch (DataSetException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (DatabaseUnitException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

• TestUserService.java

package service;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.SQLException;

import junit.framework.Assert;
import model.User;

import org.dbunit.DatabaseUnitException;
import org.dbunit.dataset.DataSetException;
import org.dbunit.dataset.IDataSet;
import org.dbunit.operation.DatabaseOperation;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import dao.impl.UserDao;
import exceptions.UserException;
import service.impl.UserService;
import uitls.AbstractDbUnitTestCase;
import uitls.EntitiesHelper;

public class TestUserService extends AbstractDbUnitTestCase {
    private IUserService us;
    private IDataSet ds;

    @Before
    public void setUp() throws DataSetException, IOException{
        us = new UserService(new UserDao());
        backupOneTable("t_user");
        ds = createDateSet("t_user");
    }

    @Test
    public void testLoad() throws DatabaseUnitException, SQLException{
            DatabaseOperation.CLEAN_INSERT.execute(dbunitCon, ds);
            User u = us.load("admin");
            EntitiesHelper.assertUser(u);
    }

    @Test
    public void testAddNotExist() throws DatabaseUnitException, SQLException{
        DatabaseOperation.TRUNCATE_TABLE.execute(dbunitCon, ds);
        User u = new User("admin", "123", "管理员");
        us.add(u);
        User tu = us.load("admin");
        EntitiesHelper.assertUser(tu, u);
    }

    @Test(expected=UserException.class)
    public void testAddExists() throws DatabaseUnitException, SQLException{
        DatabaseOperation.CLEAN_INSERT.execute(dbunitCon, ds);
        User u = new User(1,"admin", "123", "管理员");
        us.add(u);
    }

    @Test
    public void testDelete() throws DatabaseUnitException, SQLException{
        DatabaseOperation.CLEAN_INSERT.execute(dbunitCon, ds);
        us.delete("admin");
        User tu = us.load("admin");
        Assert.assertNull(tu);
    }

    @Test
    public void testLogin() throws DatabaseUnitException, SQLException{
        DatabaseOperation.CLEAN_INSERT.execute(dbunitCon, ds);
        String username="admin";
        String password ="123";
        User tu  = us.login(username, password);
        EntitiesHelper.assertUser(tu);
    }

    @Test(expected=UserException.class)
    public void testNotExistsUserLogin() throws DatabaseUnitException, SQLException{
        DatabaseOperation.CLEAN_INSERT.execute(dbunitCon, ds);
        String username = "admin1";
        String password = "123";
        us.login(username, password);

    }

    @Test(expected=UserException.class)
    public void testPasswordErrorUserLogin() throws DatabaseUnitException, SQLException{
        DatabaseOperation.CLEAN_INSERT.execute(dbunitCon, ds);
        String username = "admin";
        String password = "1234";
        us.login(username, password);
    }

    @After
    public void tearDown() throws FileNotFoundException, DatabaseUnitException, SQLException{
        resumeTable();
    }

}

• AbstractDbUnitTestCase.java

package uitls;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;

import org.dbunit.DatabaseUnitException;
import org.dbunit.database.AmbiguousTableNameException;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.database.QueryDataSet;
import org.dbunit.dataset.DataSetException;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.dataset.xml.FlatXmlProducer;
import org.dbunit.operation.DatabaseOperation;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.xml.sax.InputSource;

import static org.junit.Assert.*;

public class AbstractDbUnitTestCase {
    public static IDatabaseConnection dbunitCon;
    private File tempFile ;

    @BeforeClass  //只执行一次（@before是每一个@test前都要执行）同理@afterClass
    public static void init() throws DatabaseUnitException, SQLException{
        dbunitCon = new DatabaseConnection(DbUtil.getConnection());
    }


    protected IDataSet createDateSet(String tname) throws DataSetException{
        InputStream is = AbstractDbUnitTestCase.class.getClassLoader()
                .getResourceAsStream(tname+".xml");
        assertNotNull("dbunit的基础数据不存在",is);
        return new FlatXmlDataSet(new FlatXmlProducer(
                new InputSource(is)));
    }

    protected void backupAllTalbe() throws IOException, SQLException, DataSetException{
        IDataSet ds = dbunitCon.createDataSet();
        writeBackupFile(ds);
    }

    private void writeBackupFile(IDataSet ds) throws IOException, DataSetException{
        tempFile = File.createTempFile("back", "xml");
        FlatXmlDataSet.write(ds, new FileWriter(tempFile));
    }

    protected void backupCustomTable(String[] tname) throws DataSetException, IOException{
        QueryDataSet ds = new QueryDataSet(dbunitCon);
        for(String str:tname){
            ds.addTable(str);
        }
        writeBackupFile(ds);
    }

    protected void backupOneTable(String tname) throws DataSetException, IOException{
        backupCustomTable(new String[]{tname});
    }

    protected void resumeTable() throws FileNotFoundException, DatabaseUnitException, SQLException{
        IDataSet ds = new FlatXmlDataSet(new FlatXmlProducer(
                new InputSource(new FileInputStream(tempFile))));
        DatabaseOperation.CLEAN_INSERT.execute(dbunitCon, ds);
    }

    @AfterClass
    public static void destory(){
        if(dbunitCon!=null){
            try {
                dbunitCon.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

• EntitiesHelper.java

package uitls;

import model.User;
import junit.framework.Assert;

public class EntitiesHelper {
    private static User baseUser = new User(1,"admin","123","管理员");

    public static void assertUser(User expected,User actual) {
        Assert.assertNotNull(expected);
        Assert.assertEquals(expected.getId(), actual.getId());
        Assert.assertEquals(expected.getUsername(), actual.getUsername());
        Assert.assertEquals(expected.getPassword(), actual.getPassword());
        Assert.assertEquals(expected.getNickname(), actual.getNickname());
    }

    public static void assertUser(User expected) {
        assertUser(expected, baseUser);
    }
}

分析总结

如下图所示

可以看到在我们之后的开发中可以直接使用
- AbstractDbunitCase.java
- EntitiesHelper.java
这两个类，这也是我们这个实例的精华之所在！！！