DBUnit最佳实践之增删改查 。
目录结构
- 项目结构图
- 源代码
- 数据库工具类
-
学生数据访问对象
- 测试辅助类
- 测试对象比对辅助类
- 数据库测试辅助类
- 测试类
- 数据文件
- Maven工程文件
- 数据库配置属性文件
- 数据库脚本
- 日志配置文件
- 参考文档
- 完整项目源代码
项目结构图
源代码
数据库工具类
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）；
参考文档
完整源代码
545
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
