Kohei Nozaki's blog 

Entries tagged [dbunit]

Using DBUnit from Ant


Posted on Wednesday Sep 23, 2015 at 12:02PM in Technology


It’s a common usecase that using DBUnit from JUnit testcases but sometimes I need to use DBUnit in a standalone application manner to achive tasks such as exporting the data from a database to a XML file for creating test data from an existing table. In such case, I feel using DBUnit Ant task is better rather than launching it from Maven plugin or write a standalone Java application uses DBUnit API. in this entry, I introduce you a complete working example of an Ant script that uses DBUnit Ant task.

Environment

Complete JAR file list in my ~/.ant/lib:

$ ls -l ~/.ant/lib
total 1236
-rw-rw-r--. 1 kyle kyle 560755 Sep 23 10:56 dbunit-2.5.1.jar
-rw-rw-r--. 1 kyle kyle 660126 Sep 23 10:54 postgresql-9.4-1203.jdbc42.jar
-rw-rw-r--. 1 kyle kyle  32127 Sep 23 11:15 slf4j-api-1.7.12.jar
-rw-rw-r--. 1 kyle kyle   7892 Sep 23 11:16 slf4j-jdk14-1.7.12.jar
$

Exporting example

build.xml

This script exports the result of a SQL to a flat XML file. The format can be specified in dbunit.format property that has set to flat as default. You can add additional query or table elements inside the dbunit element to include multiple dataset in single XML file. JDBC connection information such as credentials or URL is expected to be stored as a separate file named dbunit.properties that is placed under the same directory to this file.

<project name="dbunit-export" basedir=".">
    <taskdef name="dbunit" classname="org.dbunit.ant.DbUnitTask"/>

    <loadproperties srcFile="dbunit.properties"/>

    <fail unless="dbunit.driver"/>
    <fail unless="dbunit.url"/>
    <fail unless="dbunit.userid"/>
    <fail unless="dbunit.password"/>
    <fail unless="dbunit.datatypeFactory"/>

    <property name="dbunit.format" value="flat"/> <!-- Possible values are "flat", "xml", "csv", "dtd", "xls". Defaults to "flat" -->

    <target name="export">
        <fail unless="dbunit.sql"/>
        <fail unless="dbunit.dest"/>
        <fail unless="dbunit.format"/>
        <fail unless="dbunit.query"/>

        <dbunit driver="${dbunit.driver}" url="${dbunit.url}" userid="${dbunit.userid}" password="${dbunit.password}">
            <dbconfig>
                <property name="datatypeFactory" value="${dbunit.datatypeFactory}" />
            </dbconfig>
            <export dest="${dbunit.dest}">
                <query name="${dbunit.query}" sql="${dbunit.sql}"/>
            </export>
        </dbunit>
    </target>
</project>

dbunit.properties

Set each properties to suit your environment. See this page to check what datatypeFactory are available.

dbunit.driver=org.postgresql.Driver
dbunit.url=jdbc:postgresql://localhost:5432/mydb
dbunit.userid=someone
dbunit.password=somepass
dbunit.datatypeFactory=org.dbunit.ext.postgresql.PostgresqlDataTypeFactory

How to run

The following command exports a XML file from a SQL.

$ ant export "-Ddbunit.sql=select * from job_instance where jobinstanceid >= 399341 order by jobinstanceid" -Ddbunit.query=job_instance -Ddbunit.dest=job_instance.xml
Buildfile: /home/kyle/dbunit-ant-example/build.xml

export:
   [dbunit] Executing export:
   [dbunit]       in format: flat to datafile: /home/kyle/dbunit-ant-example/job_instance.xml
Successfully wrote file '/home/kyle/dbunit-ant-example/job_instance.xml'

BUILD SUCCESSFUL
Total time: 0 seconds

This produces a flat XML dataset named job_instance.xml something like:

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
  <job_instance jobinstanceid="399341" jobname="somejob1" applicationname="someapp"/>
  <job_instance jobinstanceid="399342" jobname="somejob2" applicationname="someapp"/>
  <job_instance jobinstanceid="399343" jobname="somejob3" applicationname="someapp"/>
...
  <job_instance jobinstanceid="400004" jobname="somejob4" applicationname="someapp"/>
</dataset>

Exporting / importing an entire database

<project name="mydbunit" basedir=".">
    <taskdef name="dbunit" classname="org.dbunit.ant.DbUnitTask"/>

    <loadproperties srcFile="dbunit.properties"/>

    <fail unless="dbunit.driver"/>
    <fail unless="dbunit.url"/>
    <fail unless="dbunit.userid"/>
    <fail unless="dbunit.password"/>
    <fail unless="dbunit.datatypeFactory"/>

    <property name="dbunit.format" value="flat"/> <!-- Possible values are "flat", "xml", "csv", "dtd", "xls". Defaults to "flat" -->

    <target name="export">
        <fail unless="dbunit.dest"/>
        <fail unless="dbunit.format"/>

        <dbunit driver="${dbunit.driver}" url="${dbunit.url}" userid="${dbunit.userid}" password="${dbunit.password}">
            <dbconfig>
                <property name="datatypeFactory" value="${dbunit.datatypeFactory}" />
                <!-- <property name="escapePattern" value="`?`" /> -->
            </dbconfig>
            <export dest="${dbunit.dest}" ordered="true"/>
        </dbunit>
    </target>

    <target name="import">
        <fail unless="dbunit.src"/>
        <fail unless="dbunit.format"/>

        <dbunit driver="${dbunit.driver}" url="${dbunit.url}" userid="${dbunit.userid}" password="${dbunit.password}">
            <dbconfig>
                <property name="datatypeFactory" value="${dbunit.datatypeFactory}" />
                <!-- <property name="escapePattern" value="`?`" /> -->
            </dbconfig>
            <operation type="CLEAN_INSERT" src="${dbunit.src}" ordered="true"/>
        </dbunit>
    </target>

</project>

To export:

$ ant export -Ddbunit.dest=mydb.xml

To import:

$ ant import -Ddbunit.src=mydb.xml

Note that you need commons-collections3 jar file in your classpath.

Other tasks

There are more operations that can be achieved through Ant tasks available such as:

  • Data comparison

  • INSERT or UPDATE based on the file


Arquillian Persistence Extension examples


Posted on Wednesday Mar 18, 2015 at 05:47PM in Arquillian


The whole project can be obtained from GitHub. tested with WildFly 8.2.0.Final as remote container.

Implementation (test target)

Assume we have very simple 2 entities as follows:

@Entity
public class Dept implements Serializable {
    @Id
    private Integer id;
    @Column(nullable = false)
    private String name;
    @OneToMany(mappedBy = "dept")
    private Collection<Employee> employees;
...

@Entity
public class Employee implements Serializable {
    @Id
    private Integer id;
    @Column(nullable = false)
    private String name;
    @JoinColumn(nullable = false)
    @ManyToOne
    private Dept dept;
...

Test target EJB:

@Stateless
@LocalBean
public class HumanResourcesBean {

    @PersistenceContext
    private EntityManager em;

    public void addEmployee(Employee employee, Integer deptId) {
        final Dept dept = em.find(Dept.class, deptId);
        dept.getEmployees().add(employee);
        employee.setDept(dept);
        em.persist(employee);
    }

    public void addDept(Dept dept, Employee employee) {
        Collection<Employee> employees = new ArrayList<>();
        dept.setEmployees(employees);
        employees.add(employee);
        employee.setDept(dept);
        em.persist(dept);
        em.persist(employee);
    }
}

addEmployee() testing

Test method of addEmployee():

@Test
@UsingDataSet("input.xml")
@ShouldMatchDataSet(value = "addEmployee-expected.xml", orderBy = "id")
public void addEmployeeTest() throws Exception {
    Employee emp = new Employee();
    emp.setId(2002);
    emp.setName("Todd");
    humanResourcesBean.addEmployee(emp, 200);
}

Initial entry data (input.xml):

<dataset>
    <Dept id="100" name="Sales"/>
    <Dept id="200" name="Finance"/>
    <Employee id="1000" name="Scott"  dept_id="100"/>
    <Employee id="1001" name="Martin" dept_id="100"/>
    <Employee id="1002" name="Nick"   dept_id="100"/>
    <Employee id="2000" name="Jordan" dept_id="200"/>
    <Employee id="2001" name="David"  dept_id="200"/>
</dataset>

Expected data (addEmployee-expected.xml):

<dataset>
    <Employee id="1000" name="Scott"  dept_id="100"/>
    <Employee id="1001" name="Martin" dept_id="100"/>
    <Employee id="1002" name="Nick"   dept_id="100"/>
    <Employee id="2000" name="Jordan" dept_id="200"/>
    <Employee id="2001" name="David"  dept_id="200"/>
    <Employee id="2002" name="Todd"   dept_id="200"/> <!-- Newly added -->
</dataset>

addDept() testing

Test method of addDept():

@Test
@UsingDataSet("input.xml")
@ShouldMatchDataSet(value = "addDept-expected.xml", orderBy = "id")
public void addDeptTest() throws Exception {
    Dept dept = new Dept();
    dept.setId(300);
    dept.setName("Engineering");
    Employee emp = new Employee();
    emp.setId(3000);
    emp.setName("Carl");
    humanResourcesBean.addDept(dept, emp);
}

Initial entry data (input.xml) is the same to previous testing.

Expected data (addDept-expected.xml):

<dataset>
    <Dept id="100" name="Sales"/>
    <Dept id="200" name="Finance"/>
    <Dept id="300" name="Engineering"/> <!-- Newly added -->
    <Employee id="1000" name="Scott"  dept_id="100"/>
    <Employee id="1001" name="Martin" dept_id="100"/>
    <Employee id="1002" name="Nick"   dept_id="100"/>
    <Employee id="2000" name="Jordan" dept_id="200"/>
    <Employee id="2001" name="David"  dept_id="200"/>
    <Employee id="3000" name="Carl"   dept_id="300"/> <!-- Newly added -->
</dataset>

It works well with multiple tables.

addDept() testing with DBUnit

Sometimes use of DBUnit directly is useful for complex assertion. in such case you need to care following conditions:

  • If you use JPA, force EntityManager to execute DMLs via invoking em.flush() before assertion

  • Include test data to the Arquillian’s application archive so that DBUnit can load these data on the server side

The XML can be included via addAsResource() method as follows:

@Deployment
public static Archive<?> createDeploymentPackage() {
    final WebArchive webArchive = ShrinkWrap.create(WebArchive.class, "test.war")
            .addPackage(Dept.class.getPackage())
            .addClass(HumanResourcesBean.class)
            .addAsResource("datasets/addDept-expected.xml") // to be loaded by DBUnit on the server side
            .addAsResource("test-persistence.xml", "META-INF/persistence.xml");
//        System.out.println(webArchive.toString(true));
    return webArchive;
}

The test method of addDept() and related convenient methods:

@Test
@UsingDataSet("input.xml")
public void addDeptTestWithDbUnit() throws Exception {
    Dept dept = new Dept();
    dept.setId(300);
    dept.setName("Engineering");
    Employee emp = new Employee();
    emp.setId(3000);
    emp.setName("Carl");

    humanResourcesBean.addDept(dept, emp);
    em.flush(); // force JPA to execute DMLs before assertion

    final IDataSet expectedDataSet = getDataSet("/datasets/addDept-expected.xml");
    assertTable(expectedDataSet.getTable("Dept"), "select * from dept order by id");
    assertTable(expectedDataSet.getTable("Employee"), "select * from employee order by id");
}

private static IDataSet getDataSet(String path) throws DataSetException {
    return new FlatXmlDataSetBuilder().build(HumanResourcesBeanIT.class.getResource(path));
}

private void assertTable(ITable expectedTable, String sql) throws SQLException, DatabaseUnitException {
    try (Connection cn = ds.getConnection()) {
        IDatabaseConnection icn = null;
        try {
            icn = new DatabaseConnection(cn);
            final ITable queryTable = icn.createQueryTable(expectedTable.getTableMetaData().getTableName(), sql);
            Assertion.assertEquals(expectedTable, queryTable);
        } finally {
            if (icn != null) {
                icn.close();
            }
        }
    }
}