dbunit java_junit+dbunit

目录结构：

D:\code\kongee\junit_01>tree /f

卷 软件 的文件夹 PATH 列表

卷序列号为 000D-CD8E

D:.

│ .classpath

│ .project

│ student-init.sql

│

├─.settings

│ org.eclipse.jdt.core.prefs

│

├─bin

│ │ student.xml

│ │

│ ├─com

│ │ └─laolang

│ │ ├─dao

│ │ │ StudentDao.class

│ │ │ StudentDaoImpl.class

│ │ │ TestDbUnit.class

│ │ │ TestDbUnit2.class

│ │ │

│ │ ├─modle

│ │ │ Cal.class

│ │ │ Student.class

│ │ │ TestA.class

│ │ │ TestCal.class

│ │ │ TestSuite.class

│ │ │

│ │ └─util

│ │ AbstractTestDbunitCase.class

│ │ DButil.class

│ │ EntitiesHelper.class

│ │ student-sql.properties

│ │

│ └─dbunit-xml

│ student.xml

│

├─lib

│ dbunit-2.4.9.jar

│ hamcrest-all-1.3.jar

│ hamcrest-core-1.3.jar

│ junit-4.11.jar

│ mysql-connector-java-5.1.28-bin.jar

│ slf4j-api-1.6.1.jar

│ slf4j-nop-1.7.12.jar

│

├─src

│ └─com

│ └─laolang

│ ├─dao

│ │ StudentDao.java

│ │ StudentDaoImpl.java

│ │

│ ├─modle

│ │ Cal.java

│ │ Student.java

│ │

│ └─util

│ AbstractTestDbunitCase.java

│ DButil.java

│ EntitiesHelper.java

│ student-sql.properties

│

└─test

│ student.xml

│

├─com

│ └─laolang

│ ├─dao

│ │ TestDbUnit.java

│ │ TestDbUnit2.java

│ │

│ └─modle

│ TestA.java

│ TestCal.java

│ TestSuite.java

│

└─dbunit-xml

student.xml

D:\code\kongee\junit_01>

student-init.sql

create table student (

stuid int primary key auto_increment,

stuname varchar(20),

stuage int,

stusex varchar(2)

)engine=innodb;

insert into student values

(1001,'小代码',24,'男'),

(1002,'小叶子',25,'女'),

(1003,'老狼',34,'男'),

(1004,'龙女',43,'女'),

(1005,'天涯',18,'男');

src

com.laolang.dao.StudentDao

package com.laolang.dao;

import com.laolang.modle.Student;

public interface StudentDao {

public void add( Student stu ) ;

public void delete ( int stuid );

public Student selectById( int stuid );

public Student selectByName( String stuname );

}

com.laolang.dao.StudentDaoImpl

package com.laolang.dao;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import com.laolang.modle.Student;

import com.laolang.util.DButil;

public class StudentDaoImpl implements StudentDao{

@Override

public void add(Student stu) {

Connection conn = null;

PreparedStatement ps = null;

try {

conn = DButil.getConnection();

String sql = "insert into student (stuname,stuage,stusex) values (?,?,?)";

//System.out.println("sql:"+sql);

ps = conn.prepareStatement(sql);

//ps.setInt(1, stu.getStuId());

ps.setString(1, stu.getStuName());

ps.setInt(2, stu.getStuAge());

ps.setString(3, stu.getStuSex());

ps.executeUpdate();

} catch (SQLException e) {

e.printStackTrace();

}finally{

DButil.close(ps);

DButil.close(conn);

}

}

@Override

public void delete(int stuid) {

Connection conn = null;

PreparedStatement ps = null;

try {

conn = DButil.getConnection();

String sql = "delete from student where stuid = ?";

ps = conn.prepareStatement(sql);

ps.setInt(1, stuid);

ps.executeUpdate();

} catch (SQLException e) {

e.printStackTrace();

}finally{

DButil.close(ps);

DButil.close(conn);

}

}

@Override

public Student selectById(int stuid) {

Student stu = null;

Connection conn = null;

PreparedStatement ps = null;

ResultSet rs = null;

try {

conn = DButil.getConnection();

String sql = "select stuid,stuname,stuage,stusex from student where stuid = ?";

ps = conn.prepareStatement(sql);

ps.setInt(1, stuid);

rs = ps.executeQuery();

while(rs.next()){

if( null == stu ){

stu = new Student();

}

stu.setStuId(stuid);

stu.setStuName(rs.getString("stuname"));

stu.setStuAge(rs.getInt("stuage"));

stu.setStuSex(rs.getString("stusex"));

}

} catch (SQLException e) {

e.printStackTrace();

}finally{

DButil.close(rs);

DButil.close(ps);

DButil.close(conn);

}

return stu;

}

@Override

public Student selectByName(String stuname) {

Student stu = null;

Connection conn = null;

PreparedStatement ps = null;

ResultSet rs = null;

try {

conn = DButil.getConnection();

String sql = "select stuid,stuname,stuage,stusex from student where stuname = ?";

ps = conn.prepareStatement(sql);

//ps.setInt(1, stuid);

ps.setString(1, stuname);

rs = ps.executeQuery();

while(rs.next()){

if( null == stu ){

stu = new Student();

}

stu.setStuId(rs.getInt("stuid"));

stu.setStuName(rs.getString("stuname"));

stu.setStuAge(rs.getInt("stuage"));

stu.setStuSex(rs.getString("stusex"));

}

//System.out.println(stu.toString());

} catch (SQLException e) {

e.printStackTrace();

}finally{

DButil.close(rs);

DButil.close(ps);

DButil.close(conn);

}

return stu;

}

}

com.laolang.modle.Student

package com.laolang.modle;

public class Student {

public Student() {

super();

}

public Student(String stuName, int stuAge, String stuSex) {

super();

this.stuName = stuName;

this.stuAge = stuAge;

this.stuSex = stuSex;

}

public Student(int stuId, String stuName, int stuAge, String stuSex) {

super();

this.stuId = stuId;

this.stuName = stuName;

this.stuAge = stuAge;

this.stuSex = stuSex;

}

@Override

public String toString() {

return "Student [stuId=" + stuId + ", stuName=" + stuName + ", stuAge="

+ stuAge + ", stuSex=" + stuSex + "]";

}

public int getStuId() {

return stuId;

}

public void setStuId(int stuId) {

this.stuId = stuId;

}

public String getStuName() {

return stuName;

}

public void setStuName(String stuName) {

this.stuName = stuName;

}

public int getStuAge() {

return stuAge;

}

public void setStuAge(int stuAge) {

this.stuAge = stuAge;

}

public String getStuSex() {

return stuSex;

}

public void setStuSex(String stuSex) {

this.stuSex = stuSex;

}

int stuId;

String stuName;

int stuAge;

String stuSex;

}

com.laolang.util.AbstractTestDbunitCase

package com.laolang.util;

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 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.FlatXmlProducer;

import org.dbunit.operation.DatabaseOperation;

import org.junit.AfterClass;

import org.junit.BeforeClass;

import org.xml.sax.InputSource;

@SuppressWarnings("deprecation")

public abstract class AbstractTestDbunitCase {

@BeforeClass

public static void init() throws DatabaseUnitException, SQLException {

dconn = new DatabaseConnection(DButil.getConnection());

}

protected IDataSet createDataset(String tablename ) throws DataSetException{

InputStream is = AbstractTestDbunitCase.class.getClassLoader().getResourceAsStream("dbunit-xml/"+tablename+".xml");

Assert.assertNotNull("dbunit xml数据文件未找到",is);

return new FlatXmlDataSet( new FlatXmlProducer(new InputSource(is)));

}

protected void backupAllTable() throws SQLException, IOException, DataSetException{

IDataSet dataset = dconn.createDataSet();

writeBackupFile(dataset);

}

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(dconn);

for(String str:tname) {

ds.addTable(str);

}

writeBackupFile(ds);

}

protected void bakcupOneTable(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(dconn, ds);

}

@AfterClass

public static void destory() {

try {

if (null != dconn)

dconn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

public static IDatabaseConnection dconn;

private File tempfile;

}

com.laolang.util.DBUtil

package com.laolang.util;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ResourceBundle;

public class DButil {

/** 数据库连接地址 */

private static String URL;

/** 数据库用户名 */

private static String USERNAME;

/** 数据库密码 */

private static String USERPASSWORD;

/** mysql 驱动 */

private static String DRIVER;

/** The rb. */

private static ResourceBundle rb = ResourceBundle

.getBundle("com.laolang.util.student-sql");

/**

* 使用静态代码块加载驱动

*/

static {

URL = rb.getString("jdbc.url");

USERNAME = rb.getString("jdbc.username");

USERPASSWORD = rb.getString("jdbc.userpassword");

DRIVER = rb.getString("jdbc.driver");

try {

Class.forName(DRIVER);

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

}

public static Connection getConnection() throws SQLException {

Connection con = null;

con = DriverManager.getConnection(

URL, USERNAME, USERPASSWORD);

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

}

}

}

student-sql.properties

jdbc.url=jdbc:mysql://localhost:3306/student

jdbc.username=root

jdbc.userpassword=root

jdbc.driver=com.mysql.jdbc.Driver

EntitiesHelper

package com.laolang.util;

import org.junit.Assert;

import com.laolang.modle.Student;

public class EntitiesHelper {

public static void assertUser(Student expected,Student actual) {

Assert.assertNotNull(expected);

Assert.assertEquals(expected.getStuId(), actual.getStuId());

Assert.assertEquals(expected.getStuName(), actual.getStuName());

Assert.assertEquals(expected.getStuAge(), actual.getStuAge());

Assert.assertEquals(expected.getStuSex(), actual.getStuSex());

}

}

test

com.laolang.dao.TestDbUnit

package com.laolang.dao;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileWriter;

import java.io.IOException;

import java.sql.SQLException;

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.xml.FlatXmlDataSet;

import org.dbunit.dataset.xml.FlatXmlProducer;

import org.dbunit.operation.DatabaseOperation;

import org.junit.Test;

import org.xml.sax.InputSource;

import com.laolang.modle.Student;

import com.laolang.util.DButil;

import static org.junit.Assert.*;

public class TestDbUnit {

@Test

public void testselectbyid() {

try {

testbackupAll();

IDatabaseConnection dconn = new DatabaseConnection(

DButil.getConnection());

IDataSet dataset = new FlatXmlDataSet(new FlatXmlProducer(

new InputSource(TestDbUnit.class.getClassLoader()

.getResourceAsStream("student.xml"))));

DatabaseOperation.CLEAN_INSERT.execute(dconn, dataset);

StudentDao dao = new StudentDaoImpl();

Student stu = dao.selectById(1009);

if( null == stu ){

System.out.println("stu == null");

}

System.out.println(stu.toString());

assertEquals(stu.getStuId(), 1009);

assertEquals(stu.getStuName(), "程序员");

assertEquals(stu.getStuAge(), 99);

assertEquals(stu.getStuSex(), "男");

} catch (DatabaseUnitException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

testresume();

}

//@Test

public void testbackupAll(){

try {

IDatabaseConnection dconn = new DatabaseConnection(DButil.getConnection());

IDataSet dataset = dconn.createDataSet();

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

} catch (DatabaseUnitException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}

}

//@Test

public void testbackupTable(){

try {

IDatabaseConnection dconn = new DatabaseConnection(DButil.getConnection());

QueryDataSet qds = new QueryDataSet(dconn);

qds.addTable("student");

FlatXmlDataSet.write(qds, new FileWriter("d:/teststudent.xml"));

} catch (DatabaseUnitException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}

}

//@Test

public void testresume(){

try {

IDatabaseConnection dconn = new DatabaseConnection(DButil.getConnection());

IDataSet dataset = new FlatXmlDataSet( new FlatXmlProducer(new InputSource(new FileInputStream("d:/test.xml"))));

DatabaseOperation.CLEAN_INSERT.execute(dconn, dataset);

} catch (DatabaseUnitException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

} catch (FileNotFoundException e) {

e.printStackTrace();

}

}

}

com.laolang.dao.TestDbUtils2

package com.laolang.dao;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.sql.SQLException;

import org.dbunit.DatabaseUnitException;

import org.dbunit.database.DatabaseConnection;

import org.dbunit.database.IDatabaseConnection;

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 static org.junit.Assert.*;

import com.laolang.modle.Student;

import com.laolang.util.AbstractTestDbunitCase;

import com.laolang.util.DButil;

import com.laolang.util.EntitiesHelper;

public class TestDbUnit2 extends AbstractTestDbunitCase{

private IDataSet ds;

private StudentDao studao;

private IDatabaseConnection dconn;

@Before

public void setUp() throws DataSetException, IOException {

//初始化

studao = new StudentDaoImpl();

bakcupOneTable("student");

ds = createDataset("student");

try {

dconn = new DatabaseConnection(DButil.getConnection());

} catch (DatabaseUnitException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

}

@Test

public void testSelectById(){

try {

DatabaseOperation.CLEAN_INSERT.execute(dconn, ds);

Student stu = studao.selectById(1009);

assertEquals(stu.getStuId(), 1009);

assertEquals(stu.getStuName(), "程序员");

assertEquals(stu.getStuAge(), 99);

assertEquals(stu.getStuSex(), "男");

} catch (DatabaseUnitException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

}

@Test

public void testAdd(){

//DatabaseOperation.TRUNCATE_TABLE.execute(dbunitCon, ds);

//User u = new User(1,"admin","123","管理员");

//us.add(u);

//User tu = us.load("admin");

//EntitiesHelper.assertUser(tu, u);

try {

DatabaseOperation.TRUNCATE_TABLE.execute(dconn, ds);

//DatabaseOperation

Student stu = new Student(1,"1010",100,"男");

studao.add(stu);

//Student tstu = studao.selectById(1010);

Student tstu = studao.selectById(1);

assertNotNull(tstu);

EntitiesHelper.assertUser(stu, tstu);

//assertEquals(stu.getStuId(), tstu.getStuId());

//System.out.println("stu.stuid:"+stu.getStuId());

//System.out.println("tstu.stuid:"+tstu.getStuId());

//assertEquals(stu.getStuName(), tstu.getStuName());

//assertEquals(stu.getStuAge(), tstu.getStuAge());

//assertEquals(stu.getStuSex(), tstu.getStuSex());

} catch (DatabaseUnitException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

}

@After

public void testResume(){

try {

resumeTable();

} catch (FileNotFoundException e) {

e.printStackTrace();

} catch (DatabaseUnitException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

}

//@Test

//public void testselectbyid() {

//

//try {

//testbackupAll();

//IDatabaseConnection dconn = new DatabaseConnection(

//DButil.getConnection());

//IDataSet dataset = new FlatXmlDataSet(new FlatXmlProducer(

//new InputSource(TestDbUnit2.class.getClassLoader()

//.getResourceAsStream("student-dbunit.xml"))));

//

//DatabaseOperation.CLEAN_INSERT.execute(dconn, dataset);

//

//StudentDao dao = new StudentDaoImpl();

//Student stu = dao.selectById(1009);

//if( null == stu ){

//System.out.println("stu == null");

//}

//System.out.println(stu.toString());

//assertEquals(stu.getStuId(), 1009);

//assertEquals(stu.getStuName(), "程序员");

//assertEquals(stu.getStuAge(), 99);

//assertEquals(stu.getStuSex(), "男");

//

//} catch (DatabaseUnitException e) {

//e.printStackTrace();

//} catch (SQLException e) {

//e.printStackTrace();

//}

//testresume();

//}

//

@Test

//public void testbackupAll(){

//try {

//IDatabaseConnection dconn = new DatabaseConnection(DButil.getConnection());

//IDataSet dataset = dconn.createDataSet();

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

//} catch (DatabaseUnitException e) {

//e.printStackTrace();

//} catch (SQLException e) {

//e.printStackTrace();

//} catch (IOException e) {

//e.printStackTrace();

//}

//}

//

@Test

//public void testbackupTable(){

//try {

//IDatabaseConnection dconn = new DatabaseConnection(DButil.getConnection());

//QueryDataSet qds = new QueryDataSet(dconn);

//qds.addTable("student");

//FlatXmlDataSet.write(qds, new FileWriter("d:/teststudent.xml"));

//} catch (DatabaseUnitException e) {

//e.printStackTrace();

//} catch (SQLException e) {

//e.printStackTrace();

//} catch (IOException e) {

//e.printStackTrace();

//}

//}

//

@Test

//public void testresume(){

//try {

//IDatabaseConnection dconn = new DatabaseConnection(DButil.getConnection());

//IDataSet dataset = new FlatXmlDataSet( new FlatXmlProducer(new InputSource(new FileInputStream("d:/test.xml"))));

//DatabaseOperation.CLEAN_INSERT.execute(dconn, dataset);

//

//} catch (DatabaseUnitException e) {

//e.printStackTrace();

//} catch (SQLException e) {

//e.printStackTrace();

//} catch (FileNotFoundException e) {

//e.printStackTrace();

//}

//}

}

xml

test/dbunit-xml/student.xml

test/student.xml

基本流程

1、根据数据库中的表结构写xml文件

2、写备份和恢复函数

protected void backupAllTable() throws SQLException, IOException, DataSetException{

IDataSet dataset = dconn.createDataSet();

writeBackupFile(dataset);

}

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(dconn);

for(String str:tname) {

ds.addTable(str);

}

writeBackupFile(ds);

}

protected void bakcupOneTable(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(dconn, ds);

} 3、在测试用例中，@Before调用备份

4、测试代码中，使用DatabaseOperation将测试数据插入数据库中

5、@After调用恢复