DbUnit

DbUnit

是一個資料庫單元測試的元件，如測試 DAO 的 CRUD 操作等

//-- UserDAO.java --
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Date;

public class UserDAO {
 
 private Connection conn;
 
 public UserDAO(Connection conn) {
  super();
  this.conn = conn;
 }

 public void addUser(int id, String name, Date birthday) throws SQLException {
  PreparedStatement ps = conn.prepareStatement("insert into users (id, name, birthday) values (?,?,?)");
  try {
   ps.setInt(1, id);
   ps.setString(2, name);
   ps.setDate(3, birthday);
   ps.executeUpdate();
  } finally {
   ps.close();
  }
 }

}

//-- UserDAOTest.java --
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import org.dbunit.Assertion;
import org.dbunit.DBTestCase;
import org.dbunit.PropertiesBasedJdbcDatabaseTester;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ITable;
import org.dbunit.dataset.filter.DefaultColumnFilter;
import org.dbunit.dataset.xml.FlatXmlDataSet;

public class UserDAOTest extends DBTestCase {
 
 private UserDAO userDAO;
 private Connection conn;
 
 protected void setUp() throws Exception {
  System.setProperty(
    PropertiesBasedJdbcDatabaseTester.DBUNIT_DRIVER_CLASS,
    "org.apache.derby.jdbc.EmbeddedDriver");
  System.setProperty(
    PropertiesBasedJdbcDatabaseTester.DBUNIT_CONNECTION_URL,
    "jdbc:derby:testdb;create=true");
  System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_USERNAME,
    "");
  System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_PASSWORD,
    "");
  IDatabaseConnection dbConn = getConnection();
  conn = dbConn.getConnection();
  Statement statement = conn.createStatement();
  try {
   statement.execute("create table users (id integer not null, name varchar(255) not null, birthday date, createDate date, primary key (id))");
  } catch (SQLException e) {
   
  } finally {
   statement.close();
   
  }
  userDAO = new UserDAO(conn); 
  super.setUp();
 }

 protected void tearDown() throws Exception {
  conn.close();
  super.tearDown();
 }
 
 protected IDataSet getDataSet() throws Exception {
  return new FlatXmlDataSet(new FileInputStream("dataset.xml"));
 }

 public void testAddUser() throws Exception {
  // 測試
  userDAO.addUser(2, "John", java.sql.Date.valueOf("1977-11-01"));
  
    // 資料庫實際資料
    IDataSet ds = getConnection().createDataSet();
    ITable actual = ds.getTable("users");
    
    // 忽略欄位    
    actual = DefaultColumnFilter.excludedColumnsTable(actual, new String[]{"createDate"});
    
    // 預期資料
    IDataSet expected = new FlatXmlDataSet(new File("expectedDataSet.xml"));
    ITable expectedTable = expected.getTable("users");

    Assertion.assertEquals(expectedTable, actual);
 }

}

//-- dataset.xml --
<?xml version='1.0' encoding='UTF-8'?>
<dataset>
  <users id="1" name="May" birthday="1997-11-13"/>
</dataset>

//-- expectedDataSet.xml --
<?xml version='1.0' encoding='UTF-8'?>
<dataset>
  <users id="1" name="May" birthday="1997-11-13"/>
  <users id="2" name="John" birthday="1977-11-01"/>
</dataset>