$ 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 $
Entries tagged [dbunit]
Using DBUnit from Ant
TweetPosted 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
-
dbunit-2.5.1.jar (Not available on the download page on SourceForge. Obtain it from Maven Repository instead)
-
With dependencies: slf4j-api-1.7.12.jar, slf4j-jdk14-1.7.12.jar
-
-
postgresql-9.4-1203.jdbc42.jar
-
Apache Ant™ version 1.9.6
-
PostgreSQL 9.3.5
-
Oracle JDK8u40
Complete JAR file list in my ~/.ant/lib
:
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
References
Arquillian Persistence Extension examples
TweetPosted 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(); } } } }