DBUnit最佳实践之增删改查

DBUnit最佳实践之增删改查。

目录结构

项目结构图
源代码
1. 数据库工具类
2. 学生数据访问对象
3. 测试辅助类
4. 测试对象比对辅助类
5. 数据库测试辅助类
6. 测试类
7. 数据文件
8. Maven工程文件
9. 数据库配置属性文件
10. 数据库脚本
11. 日志配置文件
参考文档
完整项目源代码

项目结构图

源代码

数据库工具类

DBUtil.java

package com.coderdream;

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

/**
 * @author CoderDream
 * @date 2014年10月15日
 * 
 */
public class DBUtil {

 /**
  * 获得数据库连接
  * 
  * @return
  */
 public static Connection getConnection() {
  Connection conn = null;
  try {
   // 读取数据库配置文件
   ResourceBundle rs = ResourceBundle.getBundle("dbutil");
   // 加载驱动
   Class.forName(rs.getString("db.classname"));
   // 得到数据库连接
   conn = DriverManager.getConnection(rs.getString("db.url"), rs.getString("db.username"), rs.getString("db.password"));
  } catch (ClassNotFoundException e) {
   System.out.println("数据库驱动加载失败,堆栈轨迹如下");
   e.printStackTrace();
  } catch (SQLException e) {
   System.out.println("数据库连接创建失败,堆栈轨迹如下");
   e.printStackTrace();
  }
  return conn;
 }

 /**
  * 关闭所有对象
  * 
  * @param rs
  * @param pstmt
  * @param conn
  */
 public static void closeAll(ResultSet rs, PreparedStatement pstmt, Connection conn) {
  if (null != rs) {
   try {
    rs.close();
   } catch (SQLException e) {
    System.out.println("数据库操作的ResultSet关闭失败,堆栈轨迹如下");
    e.printStackTrace();
   }
  }
  if (null != pstmt) {
   try {
    pstmt.close();
   } catch (SQLException e) {
    System.out.println("数据库操作的PreparedStatement关闭失败,堆栈轨迹如下");
    e.printStackTrace();
   }
  }
  close(conn);
 }

 /**
  * 关闭数据库连接
  * 
  * @param conn
  */
 public static void close(Connection conn) {
  if (null != conn) {
   try {
    conn.close();
    if (conn.isClosed()) {
     System.out.println("此数据库连接已关闭-->" + conn);
    } else {
     System.out.println("此数据库连接关闭失败-->" + conn);
    }
   } catch (SQLException e) {
    System.out.println("数据库连接关闭失败,堆栈轨迹如下");
    e.printStackTrace();
   }
  }
 }

}

学生实体

Student.java

package com.coderdream;

/**
 * 实体类
 * 
 * @author CoderDream
 * @date 2014年10月15日
 * 
 */
public class Student {
 private String id;
 private String name;
 private String sex;
 private String birthday;

 public Student() {
 }

 public Student(String id, String name, String sex, String birthday) {
  super();
  this.id = id;
  this.name = name;
  this.sex = sex;
  this.birthday = birthday;
 }

 public String getId() {
  return id;
 }

 public void setId(String id) {
  this.id = id;
 }

 public String getName() {
  return name;
 }

 public void setName(String name) {
  this.name = name;
 }

 public String getSex() {
  return sex;
 }

 public void setSex(String sex) {
  this.sex = sex;
 }

 public String getBirthday() {
  return birthday;
 }

 public void setBirthday(String birthday) {
  this.birthday = birthday;
 }

}

学生数据访问对象

StudentDao.java

package com.coderdream;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author CoderDream
 * @date 2014年10月15日
 * 
 */
public class StudentDao {

 /**
  * 增加学生
  * 
  * @param student
  * @return
  */
 public int addStudent(Student student) {
  int result = 0;
  Connection conn = null;
  try {
   conn = DBUtil.getConnection();
   String sql = "insert into student(id,name,sex,birthday) values(?,?,?,?)";
   PreparedStatement ps = conn.prepareStatement(sql);
   ps.setString(1, student.getId());
   ps.setString(2, student.getName());
   ps.setString(3, student.getSex());
   ps.setString(4, student.getBirthday());
   result = ps.executeUpdate();
  } catch (Exception ex) {
   ex.printStackTrace();
  } finally {
   if (null != conn) {
    try {
     conn.close();
    } catch (SQLException e) {
     e.printStackTrace();
    }
   }
  }
  return result;
 }

 /**
  * 修改学生
  * 
  * @param student
  * @return
  */
 public int updateStudent(Student student) {
  int result = 0;
  Connection conn = null;
  try {
   conn = DBUtil.getConnection();
   String sql = "update student set name=?,sex=?,birthday=? where id=?";
   PreparedStatement ps = conn.prepareStatement(sql);
   ps.setString(1, student.getName());
   ps.setString(2, student.getSex());
   ps.setString(3, student.getBirthday());
   ps.setString(4, student.getId());
   result = ps.executeUpdate();
  } catch (Exception ex) {
   ex.printStackTrace();
  } finally {
   if (null != conn) {
    try {
     conn.close();
    } catch (SQLException e) {
     e.printStackTrace();
    }
   }
  }
  return result;
 }

 /**
  * 查找学生
  * 
  * @param id
  *            学生ID
  * @return
  */
 public Student findStudent(String id) {
  Student rtnStudent = null;
  List<Student> studentList = null;
  Connection conn = null;
  ResultSet rs = null;
  try {
   conn = DBUtil.getConnection();
   String sql = "select id,name,sex,birthday from student where id=?";
   PreparedStatement ps = conn.prepareStatement(sql);
   ps.setString(1, id);
   rs = ps.executeQuery();
   // 调用记录集对象的next方法，移动指针，如果到达了EOF返回false
   studentList = new ArrayList<Student>();
   Student student = null;
   while (rs.next()) {
    // 学员类对象
    student = new Student();
    // 为学员对象属性赋值
    student.setId(rs.getString(1));
    student.setName(rs.getString(2));
    student.setSex(rs.getString(3));
    student.setBirthday(rs.getString(4));

    // 为集合类添加对象
    studentList.add(student);
   }
  } catch (Exception ex) {
   ex.printStackTrace();
  } finally {
   if (null != rs) {
    try {
     rs.close();
    } catch (SQLException e) {
     e.printStackTrace();
    }
   }
   if (null != conn) {
    try {
     conn.close();
    } catch (SQLException e) {
     e.printStackTrace();
    }
   }
  }

  if (null != studentList && 0 < studentList.size()) {
   rtnStudent = studentList.get(0);
  }
  return rtnStudent;
 }

 /**
  * 删除学生
  * 
  * @param id
  *            学生ID
  * @return
  */
 public int deleteStudent(String id) {
  int result = 0;
  Connection conn = null;
  try {
   conn = DBUtil.getConnection();
   String sql = "delete from student where id=?";
   PreparedStatement ps = conn.prepareStatement(sql);
   ps.setString(1, id);
   result = ps.executeUpdate();
  } catch (Exception ex) {
   ex.printStackTrace();
  } finally {
   if (null != conn) {
    try {
     conn.close();
    } catch (SQLException e) {
     e.printStackTrace();
    }
   }
  }
  return result;
 }

}

测试辅助类

AbstractDbUnitTestCase.java

package com.coderdream.util;

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

import junit.framework.Assert;

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.FlatXmlDataSetBuilder;
import org.dbunit.operation.DatabaseOperation;
import org.junit.AfterClass;
import org.junit.BeforeClass;

import com.coderdream.DBUtil;

/**
 * @author CoderDream
 * @date 2014年10月15日
 *
 */
public class AbstractDbUnitTestCase {
 public static IDatabaseConnection dbunitCon;
 private File tempFile;

 private static String classPath = AbstractDbUnitTestCase.class.getResource("/").getPath();

 @BeforeClass
 public static void init() throws DatabaseUnitException, SQLException {
  dbunitCon = new DatabaseConnection(DBUtil.getConnection());
 }

 /**
  * 实际结果取得
  * 
  * @return
  * @throws Exception
  */
 public static IDataSet createDataSet() throws Exception {
  IDataSet databaseDataSet = dbunitCon.createDataSet();
  return databaseDataSet;
 }

 /**
  * @param tname
  * @return
  * @throws DataSetException
  * @throws IOException
  */
 public IDataSet createDataSet(String tname) throws DataSetException, IOException {
  InputStream is = AbstractDbUnitTestCase.class.getClassLoader().getResourceAsStream(tname + ".xml");
  Assert.assertNotNull("dbunit的基本数据文件不存在", is);
  return new FlatXmlDataSetBuilder().build(is);
 }

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

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

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

 public void bakcupOneTable(String tname) throws DataSetException, IOException, SQLException {
  backupCustomTable(new String[] { tname });
 }

 public void resumeTable() throws Exception {
  IDataSet ds = new FlatXmlDataSetBuilder().build(tempFile);
  DatabaseOperation.CLEAN_INSERT.execute(dbunitCon, ds);
 }

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

}

测试对象比对辅助类

EntitiesHelper.java

package com.coderdream.util;

import junit.framework.Assert;

import com.coderdream.Student;

public class EntitiesHelper {
 private static Student baseStudent = new Student("0001", "翁仔", "m", "1979-12-31");

 public static void assertStudent(Student expected, Student actual) {
  Assert.assertNotNull(expected);
  Assert.assertEquals(expected.getId(), actual.getId());
  Assert.assertEquals(expected.getName(), actual.getName());
  Assert.assertEquals(expected.getSex(), actual.getSex());
  Assert.assertEquals(expected.getBirthday(), actual.getBirthday());
 }

 public static void assertStudent(Student expected) {
  assertStudent(expected, baseStudent);
 }
}

数据库测试辅助类

DbUnitUtil.java

package com.coderdream.util;

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

import junit.framework.Assert;

import org.dbunit.DatabaseUnitException;
import org.dbunit.database.DatabaseConnection;
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.FlatXmlDataSetBuilder;
import org.dbunit.operation.DatabaseOperation;

import com.coderdream.DBUtil;

public class DbUnitUtil {

 private static String classPath = DbUnitUtil.class.getResource("/").getPath();

 public static DatabaseConnection getConn() throws DatabaseUnitException, SQLException {
  DatabaseConnection dbunitCon = new DatabaseConnection(DBUtil.getConnection());
  return dbunitCon;
 }

 // 实际结果取得
 public static IDataSet createDataSet() throws Exception {
  IDataSet databaseDataSet = getConn().createDataSet();
  return databaseDataSet;
 }

 /**
  * @param tname
  * @return
  * @throws DataSetException
  * @throws IOException
  */
 public static IDataSet createDataSet(String tname) throws Exception {
  InputStream is = DbUnitUtil.class.getClassLoader().getResourceAsStream(tname + ".xml");
  // InputStream is = AbstractDbUnitTestCase.class.getClassLoader().getResourceAsStream(classPath + tname + ".xml");
  Assert.assertNotNull("dbunit的基本数据文件不存在", is);
  return new FlatXmlDataSetBuilder().build(is);
 }

 public File backupAllTable() throws Exception {
  IDataSet ds = getConn().createDataSet();
  File tempFile = writeBackupFile(ds);
  return tempFile;
 }

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

 public File backupCustomTable(String[] tname) throws Exception {
  QueryDataSet ds = new QueryDataSet(getConn());
  for (String str : tname) {
   ds.addTable(str);
  }
  File tempFile = writeBackupFile(ds);
  return tempFile;
 }

 public File bakcupOneTable(String tname) throws Exception {
  File tempFile = backupCustomTable(new String[] { tname });
  return tempFile;
 }

 public void resumeTable(File tempFile) throws Exception {
  IDataSet ds = new FlatXmlDataSetBuilder().build(tempFile);
  DatabaseOperation.CLEAN_INSERT.execute(getConn(), ds);
 }

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

}

测试类

继承TestCase的测试类
StudentDaoTest.java

package com.coderdream;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.SQLException;

import junit.framework.TestCase;

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.ITable;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
import org.dbunit.operation.DatabaseOperation;
import org.junit.Assert;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * <pre>
 * 继承TestCase
 * 在setUp()和tearDown()分别执行数据备份及待测试数据初始化和数据恢复工作
 * </pre>
 * 
 * @author CoderDream
 * @date 2014年10月15日
 * 
 */
public class StudentDaoTest extends TestCase {
 private static final Logger logger = LoggerFactory.getLogger(StudentDaoTest.class);
 private static String classPath = StudentDaoTest.class.getResource("/").getPath();
 private static Connection conn;
 private static IDatabaseConnection dbUnitConn;
 private static String DATA_BACKUP_FILE = "student_back";

 private File tempFile;
 private StudentDao studentDao;

 @Override
 protected void setUp() {
  try {
   super.setUp();
   studentDao = new StudentDao();
   // 获得数据库连接
   conn = DBUtil.getConnection();
   // 获得DB 连接
   dbUnitConn = new DatabaseConnection(conn);
   // 对数据库中的操作对象表student 进行备份
   QueryDataSet backupDataSet = new QueryDataSet(dbUnitConn);
   backupDataSet.addTable("student");
   tempFile = File.createTempFile(DATA_BACKUP_FILE, ".xml", new File(classPath));// 备份文件
   FlatXmlDataSet.write(backupDataSet, new FileOutputStream(tempFile));
   // 准备数据的读入
   IDataSet dataSet = new FlatXmlDataSetBuilder().build(new File(classPath + "student_pre.xml"));
   DatabaseOperation.CLEAN_INSERT.execute(dbUnitConn, dataSet);
  } catch (Exception e) {
   logger.error(e.getMessage());
   e.printStackTrace();
  }
 }

 /**
  * 3 实装测试方法，详细见代码注释。 检索类方法，可以利用assertEquals() 方法，拿表的字段进行比较。
  */
 @Test
 public void testFindStudent() throws Exception {
  // 执行findStudent 方法
  Student result = studentDao.findStudent("0001");
  // 预想结果和实际结果的比较
  assertNotNull(result);
  assertEquals("翁仔", result.getName());
  assertEquals("m", result.getSex());
  assertEquals("1979-12-31", result.getBirthday());
 }

 /**
  * 更新，添加，删除等方法，可以利用Assertion.assertEquals() 方法，拿表的整体来比较。
  */
 @Test
 public void testAddStudent() throws Exception {
  // 执行addStudent 方法
  // 被追加的记录
  Student newStudent = new Student("0088", "王耳朵", "m", "1982-01-01");
  // 执行追加方法
  int result = studentDao.addStudent(newStudent);
  Assert.assertEquals(1, result);
  // 预想结果和实际结果的比较
  // 预期结果取得
  IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(new File(classPath + "student_exp.xml"));
  ITable expectedTable = expectedDataSet.getTable("student");
  // 实际结果取得
  IDataSet databaseDataSet = dbUnitConn.createDataSet();
  ITable actualTable = databaseDataSet.getTable("student");
  // 比较
  Assertion.assertEquals(expectedTable, actualTable);
 }

 /**
  * 更新，添加，删除等方法，可以利用Assertion.assertEquals() 方法，拿表的整体来比较。
  */
 @Test
 public void testUpdateStudent() throws Exception {
  // 被更新的记录
  Student student = new Student("0002", "王翠花", "f", "1981-08-09");
  // 执行追加更新方法
  int result = studentDao.updateStudent(student);
  Assert.assertEquals(1, result);

  // 预想结果和实际结果的比较
  // 预期结果取得
  IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(new File(classPath + "student_update.xml"));
  ITable expectedTable = expectedDataSet.getTable("student");
  // 实际结果取得
  IDataSet databaseDataSet = dbUnitConn.createDataSet();
  ITable actualTable = databaseDataSet.getTable("student");
  // 比较
  Assertion.assertEquals(expectedTable, actualTable);
 }

 /**
  * 更新，添加，删除等方法，可以利用Assertion.assertEquals() 方法，拿表的整体来比较。
  */
 @Test
 public void testDeleteStudent() throws Exception {
  // 被删除的记录
  String id = "0001";
  // 执行删除方法
  int result = studentDao.deleteStudent(id);
  Assert.assertEquals(1, result);

  // 预想结果和实际结果的比较
  // 预期结果取得
  IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(new File(classPath + "student_delete.xml"));
  ITable expectedTable = expectedDataSet.getTable("student");
  // 实际结果取得
  IDataSet databaseDataSet = dbUnitConn.createDataSet();
  ITable actualTable = databaseDataSet.getTable("student");
  // 比较
  Assertion.assertEquals(expectedTable, actualTable);
 }

 @Override
 protected void tearDown() throws Exception {
  super.tearDown();

  IDataSet dataSet = new FlatXmlDataSetBuilder().build(tempFile);
  DatabaseOperation.CLEAN_INSERT.execute(dbUnitConn, dataSet);

  // close dbUnitConn
  try {
   if (dbUnitConn != null) {
    dbUnitConn.close();
   }
  } catch (SQLException e) {
   logger.error(e.getMessage());
   e.printStackTrace();
  }

  // close conn
  try {
   if (conn != null) {
    conn.close();
   }
  } catch (SQLException e) {
   logger.error(e.getMessage());
   e.printStackTrace();
  }
 }
}

继承AbstractDbUnitTestCase的测试类
StudentDaoTest2.java

package com.coderdream;

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

import org.dbunit.Assertion;
import org.dbunit.dataset.DataSetException;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ITable;
import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
import org.dbunit.operation.DatabaseOperation;
import org.junit.After;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;

import com.coderdream.util.AbstractDbUnitTestCase;
import com.coderdream.util.EntitiesHelper;

/**
 * <pre>
 * 
 * 在AbstractDbUnitTestCase中有很多方法用于备份和恢复数据，
 * 本类继承AbstractDbUnitTestCase类，使用注解Before和After，
 * 在setUp()和tearDown()分别执行数据备份及待测试数据初始化和数据恢复工作。
 * </pre>
 * 
 * @author CoderDream
 * @date 2014年10月15日
 * 
 */
public class StudentDaoTest2 extends AbstractDbUnitTestCase {

 private static String classPath = StudentDaoTest.class.getResource("/").getPath();

 private StudentDao studentDao;

 @Before
 public void setUp() throws DataSetException, IOException, SQLException {
  studentDao = new StudentDao();
  bakcupOneTable("student");
 }

 @After
 public void tearDown() throws Exception {
  resumeTable();
 }

 /**
  * 3 实装测试方法，详细见代码注释。 检索类方法，可以利用assertEquals() 方法，拿表的字段进行比较。
  */
 @Test
 public void testFindStudent() throws Exception {
  IDataSet ds = createDataSet("student");
  DatabaseOperation.CLEAN_INSERT.execute(dbunitCon, ds);
  // 执行findStudent 方法
  Student result = studentDao.findStudent("0001");
  // 预想结果和实际结果的比较
  Assert.assertNotNull(result);
  Assert.assertEquals("翁仔", result.getName());
  Assert.assertEquals("m", result.getSex());
  Assert.assertEquals("1979-12-31", result.getBirthday());

  //
  Student student = new Student("0001", "翁仔", "m", "1979-12-31");
  EntitiesHelper.assertStudent(result, student);
 }

 /**
  * 更新，添加，删除等方法，可以利用Assertion.assertEquals() 方法，拿表的整体来比较。
  */
 @Test
 public void testAddStudent() throws Exception {
  IDataSet ds = createDataSet("student");
  DatabaseOperation.CLEAN_INSERT.execute(dbunitCon, ds);

  // 被追加的记录
  Student newStudent = new Student("0088", "王耳朵", "m", "1982-01-01");
  // 执行追加 addStudent 方法
  int result = studentDao.addStudent(newStudent);
  Assert.assertEquals(1, result);

  // 预期结果取得
  IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(new File(classPath + "student_add.xml"));
  ITable expectedTable = expectedDataSet.getTable("student");

  // 实际结果取得(取此时数据库中的数据)
  // Creates a dataset corresponding to the entire database
  IDataSet databaseDataSet = createDataSet();
  ITable actualTable = databaseDataSet.getTable("student");

  // 预想结果和实际结果的比较
  Assertion.assertEquals(expectedTable, actualTable);
 }

 /**
  * 更新，添加，删除等方法，可以利用Assertion.assertEquals() 方法，拿表的整体来比较。
  */
 @Test
 public void testUpdateStudent() throws Exception {
  IDataSet ds = createDataSet("student");
  DatabaseOperation.CLEAN_INSERT.execute(dbunitCon, ds);

  // 被追加的记录
  Student student = new Student("0002", "王翠花", "f", "1981-08-09");
  // 执行追加 addStudent 方法
  int result = studentDao.updateStudent(student);
  Assert.assertEquals(1, result);

  // 预期结果取得
  IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(new File(classPath + "student_update.xml"));
  ITable expectedTable = expectedDataSet.getTable("student");

  // 实际结果取得(取此时数据库中的数据)
  // Creates a dataset corresponding to the entire database
  IDataSet databaseDataSet = createDataSet();
  ITable actualTable = databaseDataSet.getTable("student");

  // 预想结果和实际结果的比较
  Assertion.assertEquals(expectedTable, actualTable);
 }

 /**
  * 更新，添加，删除等方法，可以利用Assertion.assertEquals() 方法，拿表的整体来比较。
  */
 @Test
 public void testDeleteStudent() throws Exception {
  IDataSet ds = createDataSet("student");
  DatabaseOperation.CLEAN_INSERT.execute(dbunitCon, ds);

  // 被追加的记录
  String id = "0001";
  // 执行删除方法
  int result = studentDao.deleteStudent(id);
  Assert.assertEquals(1, result);

  // 预期结果取得
  IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(new File(classPath + "student_delete.xml"));
  ITable expectedTable = expectedDataSet.getTable("student");

  // 实际结果取得(取此时数据库中的数据)
  // Creates a dataset corresponding to the entire database
  IDataSet databaseDataSet = createDataSet();
  ITable actualTable = databaseDataSet.getTable("student");

  // 预想结果和实际结果的比较
  Assertion.assertEquals(expectedTable, actualTable);
 }
}

继承DBTestCase的测试类
StudentDaoTest3.java

package com.coderdream;

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

import org.dbunit.Assertion;
import org.dbunit.DBTestCase;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ITable;
import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
import org.dbunit.operation.DatabaseOperation;
import org.junit.After;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.coderdream.util.DbUnitUtil;
import com.coderdream.util.EntitiesHelper;

/**
 * <pre>
 * 继承DBTestCase
 * 每次测试执行之前都先执行getSetUpOperation()操作
 * 每次测试执行之后都会执行getTearDownOperation()操作
 * </pre>
 * 
 * @author CoderDream
 * @date 2014年10月15日
 * 
 */
public class StudentDaoTest3 extends DBTestCase {

 private static final Logger logger = LoggerFactory.getLogger(StudentDaoTest3.class);

 private static String classPath = StudentDaoTest3.class.getResource("/").getPath();

 private StudentDao studentDao;

 private DbUnitUtil dbUnitUtil;

 private File tempFile;

 @Before
 public void setUp() throws Exception {
  studentDao = new StudentDao();
  dbUnitUtil = new DbUnitUtil();
  tempFile = dbUnitUtil.bakcupOneTable("student");
 }

 @After
 public void tearDown() throws Exception {
  dbUnitUtil.resumeTable(tempFile);
 }

 /**
  * <pre>
  * 在每次测试执行之前都先执行getSetUpOperation()操作
  * 
  * 在setUpOperation中的默认操作是执行CLEAN_INSERT
  * CLEAN_INSERT是DELETE_ALL和INSERT的组合，将xml文件中的数据恢复插入到数据库中。
  * 
  * REFRESH 刷新会更新xml内容到数据库中：
  *  数据库和xml中都存在的updata
  *  数据库不存在insert,
  *  数据库中有xml中没有的保持不变
  * 
  * </pre>
  */
 public DatabaseOperation getSetUpOperation() throws Exception {
  logger.debug("### getSetUpOperation");
  return DatabaseOperation.CLEAN_INSERT;
  // return DatabaseOperation.REFRESH;
 }

 /**
  * 每次测试执行之后会执行getTearDownOperation操作。
  * 
  * <pre>
  * DatabaseOperation.NONE
  *  什么都不做--默认
  * DatabaseOperation.DELETE_ALL
  *  清空数据库
  * </pre>
  * 
  */
 public DatabaseOperation getTearDownOperation() throws Exception {
  logger.debug("### getTearDownOperation");
  // 什么都不做--默认
  // return DatabaseOperation.NONE;
  // 清空数据库
  return DatabaseOperation.DELETE_ALL;
 }

 /**
  * 将数据文件转换成数据集,这个方法是在dbunit启动的时候自动启动
  */
 @Override
 protected IDataSet getDataSet() throws Exception {
  logger.debug("### getDataSet");
  // 放在 src/test/resoures 里面的文件会编译到上面的路径下
  FlatXmlDataSetBuilder fdb = new FlatXmlDataSetBuilder();
  return fdb.build(new FileInputStream(classPath + "student_pre.xml"));
 }

 /**
  * 3 实装测试方法，详细见代码注释。 检索类方法，可以利用assertEquals() 方法，拿表的字段进行比较。
  */
 @Test
 public void testFindStudent() throws Exception {
  IDataSet ds = DbUnitUtil.createDataSet("student");
  DatabaseOperation.CLEAN_INSERT.execute(DbUnitUtil.getConn(), ds);
  // 执行findStudent 方法
  Student result = studentDao.findStudent("0001");
  // 预想结果和实际结果的比较
  Assert.assertNotNull(result);
  Assert.assertEquals("翁仔", result.getName());
  Assert.assertEquals("m", result.getSex());
  Assert.assertEquals("1979-12-31", result.getBirthday());

  //
  Student student = new Student("0001", "翁仔", "m", "1979-12-31");
  EntitiesHelper.assertStudent(result, student);
 }

 /**
  * 更新，添加，删除等方法，可以利用Assertion.assertEquals() 方法，拿表的整体来比较。
  */
 @Test
 public void testAddStudent() throws Exception {
  IDataSet ds = DbUnitUtil.createDataSet("student");
  DatabaseOperation.CLEAN_INSERT.execute(DbUnitUtil.getConn(), ds);

  // 被追加的记录
  Student newStudent = new Student("0088", "王耳朵", "m", "1982-01-01");
  // 执行追加 addStudent 方法
  int result = studentDao.addStudent(newStudent);
  Assert.assertEquals(1, result);

  // 预期结果取得
  IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(new File(classPath + "student_add.xml"));
  ITable expectedTable = expectedDataSet.getTable("student");

  // 实际结果取得(取此时数据库中的数据)
  // Creates a dataset corresponding to the entire database
  IDataSet databaseDataSet = DbUnitUtil.createDataSet();
  ITable actualTable = databaseDataSet.getTable("student");

  // 预想结果和实际结果的比较
  Assertion.assertEquals(expectedTable, actualTable);
 }

 /**
  * 更新，添加，删除等方法，可以利用Assertion.assertEquals() 方法，拿表的整体来比较。
  */
 @Test
 public void testUpdateStudent() throws Exception {
  IDataSet ds = DbUnitUtil.createDataSet("student");
  DatabaseOperation.CLEAN_INSERT.execute(DbUnitUtil.getConn(), ds);

  // 被追加的记录
  Student student = new Student("0002", "王翠花", "f", "1981-08-09");
  // 执行追加更新方法
  int result = studentDao.updateStudent(student);
  Assert.assertEquals(1, result);

  // 预期结果取得
  IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(new File(classPath + "student_update.xml"));
  ITable expectedTable = expectedDataSet.getTable("student");

  // 实际结果取得(取此时数据库中的数据)
  // Creates a dataset corresponding to the entire database
  IDataSet databaseDataSet = DbUnitUtil.createDataSet();
  ITable actualTable = databaseDataSet.getTable("student");

  // 预想结果和实际结果的比较
  Assertion.assertEquals(expectedTable, actualTable);
 }

 /**
  * 更新，添加，删除等方法，可以利用Assertion.assertEquals() 方法，拿表的整体来比较。
  */
 @Test
 public void testDeleteStudent() throws Exception {
  IDataSet ds = DbUnitUtil.createDataSet("student");
  DatabaseOperation.CLEAN_INSERT.execute(DbUnitUtil.getConn(), ds);

  // 被删除的记录
  String id = "0001";
  // 执行删除方法
  int result = studentDao.deleteStudent(id);
  Assert.assertEquals(1, result);

  // 预期结果取得
  IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(new File(classPath + "student_delete.xml"));
  ITable expectedTable = expectedDataSet.getTable("student");

  // 实际结果取得(取此时数据库中的数据)
  // Creates a dataset corresponding to the entire database
  IDataSet databaseDataSet = DbUnitUtil.createDataSet();
  ITable actualTable = databaseDataSet.getTable("student");

  // 预想结果和实际结果的比较
  Assertion.assertEquals(expectedTable, actualTable);
 }
}

不继承任何类的测试类
StudentDaoTest4.java

package com.coderdream;

import java.io.FileInputStream;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.SQLException;

import org.dbunit.Assertion;
import org.dbunit.DatabaseUnitException;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.database.QueryDataSet;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ITable;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.dataset.xml.FlatXmlProducer;
import org.dbunit.operation.DatabaseOperation;
import org.junit.After;
import org.junit.AfterClass;
import org.junit.Assert;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xml.sax.InputSource;

/**
 * <pre>
 * DBUnit使用步骤
 *  1)下载地址为http://sourceforge.net/projects/dbunit/files/ 
 *  2)导入DBUnit所需两个jar文件(dbunit.jar和slf4j-api.jar)
 *  3)创建DBUnit用到的xml格式的测试数据,xml文件名建议与表名相同 
 *  4)创建DBUnit的Connection和DataSet,然后开始进行各项测试工作
 * 
 * 使用注解@BeforeClass，在globalInit()执行打开数据库操作；
 * 使用注解@AfterClass，在globalDestroy()执行数据库关闭操作；
 * 
 * 使用注解@Before，每次测试执行之前都先执行init()操作；
 * 使用注解@After，每次测试执行之后都会执行destroy()操作；
 * 
 * DBUtil提供数据库操作方法。
 * </pre>
 * @author CoderDream
 * @date 2014年10月15日
 * 
 */
public class StudentDaoTest4 {
 private static final Logger logger = LoggerFactory.getLogger(StudentDaoTest4.class);
 private static Connection conn;
 private static IDatabaseConnection dbUnitConn;
 private static String DATA_BACKUP_FILE = "dataBackup_student.xml";

 @BeforeClass
 public static void globalInit() {
  conn = DBUtil.getConnection();
  System.out.println("DB-Unit时获取到数据库连接-->" + conn);
  try {
   // DBUnit中用来操作数据文件的Connection需依赖于数据库连接的Connection
   dbUnitConn = new DatabaseConnection(conn);
  } catch (DatabaseUnitException e) {
   e.printStackTrace();
  }
 }

 @AfterClass
 public static void globalDestroy() {
  DBUtil.close(conn);
  if (null != dbUnitConn) {
   try {
    dbUnitConn.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }

 /**
  * 备份数据库中所有表的数据，同时将student.xml的数据插入到数据库中
  */
 // @Before
 // public void initAll() throws Exception {
 // logger.debug("Before #### initAll");
 // // 此时所创建的DataSet包含了数据库中所有表的数据
 // IDataSet backupDataSet = dbUnitConn.createDataSet();
 // // 备份数据库中所有表的数据
 // FlatXmlDataSet.write(backupDataSet, new FileWriter(DATA_BACKUP_FILE));
 //
 // // FlatXmlDataSet用来获取基于属性存储的属性值,XmlDataSet用来获取基于节点类型存储的属性值
 // IDataSet dataSet = new FlatXmlDataSet(new FlatXmlProducer(new InputSource(StudentDaoTest4.class.getClassLoader()
 // .getResourceAsStream("student.xml"))));
 // DatabaseOperation.CLEAN_INSERT.execute(dbUnitConn, dataSet);
 // }

 /**
  * 备份数据库中某一张或某几张表的数据，同时将xml文件中的数据插入到数据库中
  */
 @Before
 public void init() throws Exception {
  logger.debug("Before #### init");
  // 通过QueryDataSet可以有效的选择要处理的表来作为DataSet
  QueryDataSet dataSet = new QueryDataSet(dbUnitConn);
  // 这里指定只备份t_student表中的数据,如果想备份多个表,那就再addTable(tableName)即可
  dataSet.addTable("student");
  FlatXmlDataSet.write(dataSet, new FileWriter(DATA_BACKUP_FILE));
 }

 /**
  * 还原表数据
  */
 @After
 public void destroy() throws Exception {
  IDataSet dataSet = new FlatXmlDataSet(new FlatXmlProducer(new InputSource(new FileInputStream(DATA_BACKUP_FILE))));
  DatabaseOperation.CLEAN_INSERT.execute(dbUnitConn, dataSet);
 }

 /**
  * <pre>
  * 测试查询方法
  *    DatabaseOperation类的几个常量值
  *    CLEAN_INSERT----先删除数据库中的所有数据,然后将student.xml中的数据插入数据库
  *    DELETE----------如果数据库存在与student.xml记录的相同的数据,则删除数据库中的该条数据
  *    DELETE_ALL------删除数据库中的所有数据
  *    INSERT----------将t_student.xml中的数据插入数据库
  *    NONE------------nothing to do
  *    REFRESH---------刷新数据库中的数据
  *    TRUNCATE_TABLE--清空表中的数据
  *    UPDATE----------将数据库中的那条数据更新为student.xml中的数据
  * </pre>
  */
 @Test
 public void testFindStudent() throws Exception {
  // 下面开始数据测试
  StudentDao studentDao = new StudentDao();
  Student student = studentDao.findStudent("0002");
  Assert.assertEquals(student.getId(), "0002");
  Assert.assertEquals(student.getName(), "王翠花");
  Assert.assertEquals(student.getSex(), "f");
  Assert.assertEquals(student.getBirthday(), "1982-08-09");
 }

 /**
  * 更新，添加，删除等方法，可以利用Assertion.assertEquals() 方法，拿表的整体来比较。
  */
 @Test
 public void testAddStudent() throws Exception {
  // 被追加的记录
  Student newStudent = new Student("0088", "王耳朵", "m", "1982-01-01");
  // 执行追加 addStudent 方法
  StudentDao studentDao = new StudentDao();
  int result = studentDao.addStudent(newStudent);
  Assert.assertEquals(1, result);

  // 预期结果取得
  IDataSet expectedDataSet = new FlatXmlDataSet(new FlatXmlProducer(new InputSource(StudentDaoTest4.class.getClassLoader()
    .getResourceAsStream("student_add.xml"))));
  ITable expectedTable = expectedDataSet.getTable("student");

  // 实际结果取得(取此时数据库中的数据)
  // Creates a dataset corresponding to the entire database
  IDataSet databaseDataSet = dbUnitConn.createDataSet();
  ITable actualTable = databaseDataSet.getTable("student");

  // 预想结果和实际结果的比较
  Assertion.assertEquals(expectedTable, actualTable);
 }

 /**
  * 更新，添加，删除等方法，可以利用Assertion.assertEquals() 方法，拿表的整体来比较。
  */
 @Test
 public void testUpdateStudent() throws Exception {
  // 被更新的记录
  Student student = new Student("0002", "王翠花", "f", "1981-08-09");
  // 执行追加 addStudent 方法
  StudentDao studentDao = new StudentDao();
  int result = studentDao.updateStudent(student);
  Assert.assertEquals(1, result);

  // 预期结果取得
  IDataSet expectedDataSet = new FlatXmlDataSet(new FlatXmlProducer(new InputSource(StudentDaoTest4.class.getClassLoader()
    .getResourceAsStream("student_update.xml"))));
  ITable expectedTable = expectedDataSet.getTable("student");

  // 实际结果取得(取此时数据库中的数据)
  // Creates a dataset corresponding to the entire database
  IDataSet databaseDataSet = dbUnitConn.createDataSet();
  ITable actualTable = databaseDataSet.getTable("student");

  // 预想结果和实际结果的比较
  Assertion.assertEquals(expectedTable, actualTable);
 }

 /**
  * 更新，添加，删除等方法，可以利用Assertion.assertEquals() 方法，拿表的整体来比较。
  */
 @Test
 public void testDeleteStudent() throws Exception {
  // 被删除记录的id
  String id = "0001";
  // 执行删除方法
  StudentDao studentDao = new StudentDao();
  int result = studentDao.deleteStudent(id);
  Assert.assertEquals(1, result);

  // 预期结果取得
  IDataSet expectedDataSet = new FlatXmlDataSet(new FlatXmlProducer(new InputSource(StudentDaoTest4.class.getClassLoader()
    .getResourceAsStream("student_delete.xml"))));
  ITable expectedTable = expectedDataSet.getTable("student");

  // 实际结果取得(取此时数据库中的数据)
  // Creates a dataset corresponding to the entire database
  IDataSet databaseDataSet = dbUnitConn.createDataSet();
  ITable actualTable = databaseDataSet.getTable("student");

  // 预想结果和实际结果的比较
  Assertion.assertEquals(expectedTable, actualTable);
 }
}

数据文件

student_pre.xml

<?xml version="1.0" encoding="UTF-8"?>
<dataset>
 <student id="0001" name="翁仔" sex="m" birthday="1979-12-31" />
 <student id="0002" name="王翠花" sex="f" birthday="1982-08-09" />
</dataset>

student_add.xml

<?xml version="1.0" encoding="UTF-8"?>
<dataset>
 <student id="0001" name="翁仔" sex="m" birthday="1979-12-31" />
 <student id="0002" name="王翠花" sex="f" birthday="1982-08-09" />
 <student id="0088" name="王耳朵" sex="m" birthday="1982-01-01" />
</dataset>

student_update.xml

<?xml version="1.0" encoding="UTF-8"?>
<dataset>
 <student id="0001" name="翁仔" sex="m" birthday="1979-12-31" />
 <student id="0002" name="王翠花" sex="f" birthday="1981-08-09" />
</dataset>

student_delete.xml

<?xml version="1.0" encoding="UTF-8"?>
<dataset>
 <student id="0002" name="王翠花" sex="f" birthday="1982-08-09" />
</dataset>

Maven工程文件

pom.xml

<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/xsd/maven-4.0.0.xsd">
 <modelVersion>4.0.0</modelVersion>
 <groupId>com.coderdream</groupId>
 <artifactId>dbunit-export-import</artifactId>
 <version>0.0.1-SNAPSHOT</version>
 <packaging>jar</packaging>

 <name>DBUnitSample</name>
 <url>http://maven.apache.org</url>

 <properties>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
 </properties>

 <dependencies>
  <dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.1.24</version>
  </dependency>

  <dependency>
   <groupId>org.slf4j</groupId>
   <artifactId>slf4j-log4j12</artifactId>
   <version>1.7.5</version>
  </dependency>

  <dependency>
   <groupId>org.dbunit</groupId>
   <artifactId>dbunit</artifactId>
   <version>2.4.8</version>
  </dependency>

 </dependencies>

</project>

数据库配置属性文件

dbutil.properties

db.classname=com.mysql.jdbc.Driver
db.url=jdbc:mysql://127.0.0.1:3306/dbup?characterEncoding=UTF-8
db.username=root
db.password=1234

数据库脚本

dbup.sql

/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50525
Source Host           : localhost:3306
Source Database       : dbup

Target Server Type    : MYSQL
Target Server Version : 50525
File Encoding         : 65001

Date: 2014-10-11 14:27:06
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `role`
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `id` varchar(20) NOT NULL DEFAULT '',
  `roleName` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES ('1', '管理员');
INSERT INTO `role` VALUES ('2', '普通用户');

-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` varchar(20) NOT NULL DEFAULT '',
  `name` varchar(20) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL,
  `birthday` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '2', '3', '4');

-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` varchar(20) NOT NULL DEFAULT '',
  `name` varchar(20) DEFAULT NULL,
  `role_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ref_id` (`role_id`),
  CONSTRAINT `ref_id` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '张三', '1');
INSERT INTO `user` VALUES ('2', '李四', '2');

日志配置文件

log4j.properties

# Set root logger level to DEBUG and its only appender to A1.
log4j.rootLogger=DEBUG, A1

# A1 is set to be a ConsoleAppender.
log4j.appender.A1=org.apache.log4j.ConsoleAppender

# A1 uses PatternLayout.
log4j.appender.A1.layout=org.apache.log4j.PatternLayout
log4j.appender.A1.layout.ConversionPattern=%-4r [%t] %-5p %c %x - %m%n

运行前准备
1、在MySQL的客户端（如Navicat Premium）中执行sql文件夹中的dbup.sql文件，创建数据库和表；
2、选择pom.xml文件，右键， Run As -> Maven install。
运行前准运行与结果
1、运行测试类（DBUnitPractise\src\test\java\com\coderdream\StudentDaoTest.java）；
2、运行测试类（DBUnitPractise\src\test\java\com\coderdream\StudentDaoTest2.java）；
3、运行测试类（DBUnitPractise\src\test\java\com\coderdream\StudentDaoTest3.java）；
4、运行测试类（DBUnitPractise\src\test\java\com\coderdream\StudentDaoTest4.java）；

参考文档

完整源代码