Build Status

1. Overview

1.1. What Is This?

Arquillian Persistence Extension was created to help you write tests where persistence layer is involved. Inspired by great framework called Unitils, it brings a bunch of annotations to help you deal with the underlying data storage.

It comes with following features:

  • Wrapping each test in the separated transaction (with commit (default) or rollback at the end).

  • Seeding database using:

    • DBUnit with XML, XLS, YAML and JSON supported as data sets format.

    • Custom SQL scripts.

  • Comparing database state at the end of the test using given data sets (with column exclusion).

  • Eviction JPA second level cache between test method invocation, see @JpaCacheEviction.

1.2. Containers Used For Testing

  • Glassfish 3.1.2 Embedded

  • JBoss AS 7.0.2 Final (managed)

  • JBoss AS 7.1.1.Final (managed)

  • Wildfly 8.1

1.3. Verified With Following Databases

  • HSQL

  • MS SQL 2008 Express (with Microsoft JDBC Driver)

  • MySQL 5.5.24

  • PostgreSQL 9.1.4

  • Oracle 11g

  • Derby

Enough talking, let’s see it in action!

1.4. Code Example

@RunWith(Arquillian.class)
public class UserPersistenceTest
{

  @Deployment
  public static Archive<?> createDeploymentPackage()
  {
      return ShrinkWrap.create(JavaArchive.class, "test.jar")
                       .addPackage(UserAccount.class.getPackage())
                       .addPackages(true, "org.fest") // FEST Assert is not part of Arquillian
                       .addAsManifestResource(EmptyAsset.INSTANCE, "beans.xml")
                       .addAsManifestResource("test-persistence.xml", "persistence.xml");
  }

  @PersistenceContext
  EntityManager em;

  @Test
  @UsingDataSet("datasets/users.yml")
  @ShouldMatchDataSet("datasets/expected-users.yml")
  public void should_change_user_password() throws Exception
  {
      // given
      String expectedPassword = "LexLuthor";
      UserAccount user = em.find(UserAccount.class, 2L);

      // when
      user.setPassword("LexLuthor");
      em.merge(user);

      // then
      assertThat(user.getPassword()).isEqualTo(expectedPassword);
  }
}

There are just two things which are different from the standard Arquillian test - @UsingDataSet and @ShouldMatchDataSet annotations. Former seeds the database using file in YAML format, and latter verifies database state using given file.

This example is taken from integration tests written for this project, so feel free to have a closer look.

But it’s that easy! And there’s more to come!

If you have any questions or would like to file feature request or bug report (hope not!) please have a look at the ways how you can get in touch with us.

2. Guide

2.1. Getting Started

2.1.1. Maven Setup

You’ll first need to setup Arquillian in your project. You can find instructions in the Getting Started guide. You’ll also need to add an Arquillian container adapter for a container which provides JPA. Any Java EE 6 server will do, such as JBoss AS 7 or GlassFish 3.1. Adding the required container adapter to your project is also covered in the Getting Started guide.

The only extra dependency you need is the Arquillian Persistence Extension implementation, arquillian-persistence-dbunit (former arquillian-persistence-impl), as shown in the snippet below.

Maven dependency for Arquillian Persistence Extension

<dependency>
    <groupId>org.jboss.arquillian.extension</groupId>
    <artifactId>arquillian-persistence-dbunit</artifactId>
    <version>${version.arquillian.persistence}</version>
    <scope>test</scope>
</dependency>

2.1.2. See It In Action

Here is an Arquillian-powered test with Persistence Extension.

@RunWith(Arquillian.class)
public class UserPersistenceTest {

    @Deployment
    public static Archive<?> createDeploymentPackage() {
        return ShrinkWrap.create(JavaArchive.class, "test.jar")
                .addPackage(UserAccount.class.getPackage())
                .addAsManifestResource(EmptyAsset.INSTANCE, "beans.xml")
                .addAsManifestResource("test-persistence.xml", "persistence.xml");
    }

    @PersistenceContext
    EntityManager em;

    @Test
    @UsingDataSet("datasets/users.yml")
    @ShouldMatchDataSet("datasets/expected-users.yml")
    public void should_change_user_password() throws Exception {
        // given
        String expectedPassword = "LexLuthor";
        UserAccount user = em.find(UserAccount.class, 2L);

        // when
        user.setPassword("LexLuthor");
        user = em.merge(user);

        // then
        assertEquals(expectedPassword, user.getPassword());
    }
}
users.yml
useraccount:
  - id: 1
    firstname: John
    lastname: Smith
    username: doovde
    password: password
  - id: 2
    firstname: Clark
    lastname: Kent
    username: superman
    password: kryptonite
expected-users.yml
useraccount:
  - firstname: John
    lastname: Smith
    username: doovde
    password: password
  - firstname: Clark
    lastname: Kent
    username: superman
    password: LexLuthor

There are just two things which are different from the standard Arquillian test - @UsingDataSet and @ShouldMatchDataSet annotations. Former seeds the database using file in YAML format, and latter verifies database state using given file. Read on to learn more about the features offered by Arquillian Persistence Extension. If you want to see more core have a look at our showcase on Github.

2.2. Transactional Tests

Adding transaction support to your tests is pretty straightforward. If that’s only what you need simply put @Transactional annotation either on the test which you want be wrapped in transaction or on the test class which will result with all tests running in their transactions.

Following modes are supported:

  • COMMIT: Each test will be finished with commit operation. This is default behaviour.

  • ROLLBACK: At the end of the test execution rollback will be performed

  • DISABLED: If you have enabled transactional support on the test class level, marking given test with this mode will simply run it without the transaction.

2.3. Seeding Database

Creating ad-hoc object graphs in the test code is often too verbose and makes it harder to read the tests. Arquillian Persistence Extension provides alternatives to set database fixtures to be used for the given test as described below.

2.3.1. Data Sets

If you are familiar with DBUnit you already know the concept of data sets. In brief data sets are files containing rows to be inserted to the database. In order to seed your database using data set file put @UsingDataSet annotation either on the test itself or on the class level. If there is definition on both, test level annotation takes precedence. In addition you can specify files location in the annotation (default folder is "datasets/". Refer to DBUnit configuration section to see how can you customize it). If files are not specified explicitly, following strategy is applied:

  • It’s assumed that files are stored in datasets folder on classpath (or any other you have configured as default location).

  • If annotation is defined on method level, file name has following format: [fully qualified class name]#[test method name].[default format].

  • If annotation is defined on class level, file name has following format: [fully qualified class name].[default format].

Where default format is XML, but can be also customized.

Arquillian Persistence Extension handles following formats available out of the box in DBUnit:

Flat XML Data Set

Simple XML format where each element represents rows in the given table and attributes names corresponds to its columns. You can also explicitly set null value for the given column, by simply using [null] keyword, as illustrated below.

Flat XML Data Set

<dataset>
    <useraccount id="1" firstname="Clark" lastname="Kent" username="ckent" password="LexLuthor123" nickname="[null]" />
</dataset>
Excel Spreadsheets

Another format supported out of the box by DBUnit. In order to use it follow these conventions:

  • Each table should have dedicated sheet within the Excel file.

  • First row should contain column labels.

Have a look at the example.

YAML and JSON

In addition to the format supported out of the box by DBUnit, Arquillian Persistence Extension provides alternative way to describe your data sets either by using YAML or JSON. Those formats are a bit more readable for the human (and for machines too!). So instead of hand-crafting XML or Excel files you can do define your data sets in YAML:

Data set described in YAML

useraccount:
  - id: 1
    firstname: Clark
    lastname: Kent
    username: ckent
    password: LexLuthor123
    nickname: "[null]"

or in JSON:

Data set described in JSON

{
  "useraccount":
  [
    {
      "id": "1",
      "firstname" : "Clark",
      "lastname" : "Kent",
      "username" : "ckent",
      "password" : "LexLuthor123",
      "nickname" : "[NULL]"
    }
  ]
}

2.3.2. Schema Creation

If you want to use custom SQL scripts for schema creation you can decorate your test using @CreateSchema annotation providing list of scripts you want to execute before test class. They will be executed only once. By convention such scripts will be first looked in schema folder, but you can also provide full path if you wish.

2.3.3. Custom SQL Scripts

If you want you can also use seed your database using plain SQL (or execute any other action directly on the database level). To achieve it simply put @ApplyScriptBefore or @ApplyScriptAfter annotation either directly on your test method or on the test class. Scripts will be executed before or after test method accordingly. If there is definition on both, test level annotation takes precedence.

By convention Arquillian Persistence Extension will look for these scripts in the scripts folder (in a Maven project, that’s typically src/test/resources/scripts in the source tree), but you can customize it as you wish - see configuration section how to do it. You can also specify files location in the annotation, the same way as you can do with DBUnit data sets. If files are not specified explicitly, following strategy is applied:

  • It’s assumed that files are stored in scripts folder (or any other you have configured as default location).

  • If annotation is defined on method level, file name has following format: [before|after]-[fully qualified class name]#[test method name].sql.

  • If annotation is defined on class level, file name has following format: [before|after]-[fully qualified class name].sql.

2.4. Verifying Database Content After The Test

Sometimes it might be a bit cumbersome to assert database state directly from your testing code. Especially when you change quite a lot in the database tables you might want to validate values directly in the database. By using DBUnit you can use the same data sets approach. Put @ShouldMatchDataSet annotation either on the test method or your test class and Arquillian Persistence Extension will use all these files together to check if database contains entries you are expecting after test execution. Again, by convention it first looks for the files in datasets folder. If you don’t provide file names in the annotation itself following rules are applied:

  • It’s assumed that files are stored in datasets folder (or any other you have configured as default location).

  • If annotation is defined on method level, file name has following format expected-[fully qualified class name]#[test method name].[default format]

  • If annotation is defined on class level, file name has following format expected-[fully qualified class name].[default format].

Where default format is XML, but can be also customized.

In addition you can also specify which columns should not be used for comparision. This might be handy if you export large datasets directly from the database using tools like Jailer and you don’t want to compare surrogate keys (database generated ids). You can specify these columns in the annotation:

@ShouldMatchDataSet(value = "expected-users.yml", excludeColumns = { "id", "creationDate"})

To determine order of data sets comparison you can use orderBy attribute:

@ShouldMatchDataSet(value = "expected-users.yml", orderBy = { "id"})

2.5. Data Insert Strategies

DBUnit, and hence Arquillian Persistence Extension, provides following strategies for inserting data:

  • INSERT
    Performs insert of the data defined in provided data sets. This is the default strategy.

  • CLEAN_INSERT
    Performs insert of the data defined in provided data sets, after removal of all data present in the tables (DELETE_ALL invoked by DBUnit before INSERT).

  • REFRESH
    During this operation existing rows are updated and new ones are inserted. Entries already existing in the database which are not defined in the provided data set are not affected.

  • UPDATE
    This strategy updates existing rows using data provided in the datasets. If dataset contain a row which is not present in the database (identified by its primary key) then exception is thrown.

Data seeding strategy can be specified globally in arquillian.xml. Please refer to the configuration section for the details.

2.6. Cleaning Your Data

2.6.1. DBUnit Cleanup Modes

Arquillian Persistence Extension comes with great degree of configuration. In combination with data sets which you use to seed the database you can also specify when and how you would like your database to be cleaned. By default your database is entirely erased before each test. If you want to control this behavior, use @Cleanup annotation for this purpose. You can define it on the test method level or globally by decorating test class. Following modes are currently supported:

  • STRICT
    Cleans the entire database. This strategy might require turning off database constraints (e.g. referential integrity).

  • USED_ROWS_ONLY
    Deletes only those entries which were defined in data sets used for seeding.

  • USED_TABLES_ONLY
    Deletes only those tables which were used in data sets.

You can also specify when you would like to invoke the cleanup procedure. For instance:

@Cleanup(phase = TestExecutionPhase.AFTER, strategy = CleanupStrategy.USED_ROWS_ONLY)

You can invoke it BEFORE or AFTER the test. You can also disable cleanup by using TestExecutionPhase.NONE.

2.6.2. Using Custom SQL Scripts For Cleanup

You can also use custom SQL scripts to clean your database before or after the test. For this purpose use @CleanupUsingScript annotation and specifies SQL files which have to be executed. If you don’t provide file names in the annotation itself following rules are applied:

  • It’s assumed that files are stored in scripts folder (or any other you have configured as default location).

  • If annotation is defined on method level, file name has following format cleanup-[fully qualified class name]#[test method name].sql

  • If annotation is defined on class level, file name has following format cleanup-[fully qualified class name].sql

The same way as with DBUnit cleanup you can also define test phase when it should take place.

2.7. Additional Configuration

2.7.1. Other Goodies

  • You can use scriptsToExecuteBeforeTest and scriptsToExecuteAfterTest scripts to turn off and bring back referential integrity checks for each tests using single, global definition externalized from the test class. This way your tests are portable and you can use different profiles with database specific arquillian.xml configurations transparently.

  • You can dump database content for each test execution and use it later for debugging purposes.

See following section for details on how to configure those features.

2.7.2. Additional Configuration

Arquillian Persistence Extension can be customized in the similar way as any other components from Arquillian ecosystem - through <extension> elements defined in arquillian.xml. Following sections describe all possible settings which you can use to tweak the extension for your environment.

Transaction management is delegated to Transaction Extension as of 1.0.0.Alpha6 version. Therefore if you want to specify JNDI for the UserTransaction, please use following snippet in the arquillian.xml. 
<extension qualifier="transaction">
    <property name="manager">java:jboss/UserTransaction</property>
</extension>
General Settings

You can customize Arquillian Persistence Extension configuration in arquillian.xml by adding following element:

<extension qualifier="persistence">
    <property name="defaultDataSource">java:app/datasources/mssql_ds</property>
</extension>

List of all available properties

Property Name Default Value Description

defaultDataSource

none

Name of the default data source used to interact with the database (seeding, comparing etc). Required if not specified by using @DataSource annotation.

defaultTransactionMode

COMMIT

Transaction mode for running the tests if not specified explicitly by using @Transactional. Possible values: COMMIT, ROLLBACK or DISABLED.

dumpData

false

Enables database state dumping in following phases BEFORE_SEED, AFTER_SEED, BEFORE_CLEAN, AFTER_CLEAN. Might be handy for debugging. (Boolean.)

dumpDirectory

OS-specific tmp directory defined in java.io.tmpdir

Folder where all database dumps will be stored.

defaultCleanupPhase

AFTER

Defines default cleanup phase. Possible values: BEFORE, AFTER, NONE. If not defined on the test method or class level this setting is used.

defaultCleanupStrategy

STRICT

Defines strategy of cleaning database content for the test. Possible values: STRICT, USED_ROWS_ONLY or USED_TABLES_ONLY. If not defined on the test method or class level this setting is used.

defaultDataSeedStrategy

INSERT

Defines strategy of inserting data to the database. Possible values: INSERT, CLEAN_INSERT, UPDATE or REFRESH.
DBUnit Specific Settings

Arquillian Persistence Extension provides a way to customize underlying behaviour of DBUnit (exposed as properties and features) and some other customizations.

<extension qualifier="persistence-dbunit">
    <property name="datatypeFactory">org.dbunit.ext.mssql.MsSqlDataTypeFactory</property>
    <property name="useIdentityInsert">true</property>
    <property name="excludePoi">true</property>
</extension>

List of all available properties

Property Name Default Value Description

defaultDataSetLocation

"datasets/"

Folder where all datasets are located.

defaultDataSetFormat

XML

Default format of data sets when file name is inferred from test method name, when file is not specified in @UsingDataSet or @ShouldMatchDataSet. Other supported formats are EXCEL, YAML and JSON.

excludePoi

false

Excludes Apache POI from packaging process, which results in slimier deployment. If you are not using Excel datasets you can safely turn it off.

batchedStatements

false

Enable or disable usage of JDBC batched statement by DBUnit.

caseSensitiveTableNames

false

Enable or disable case sensitive table names. If enabled, DBUnit handles all table names in a case sensitive way.

qualifiedTableNames

false

Enable or disable multiple schemas support. If enabled, DBUnit access tables with names fully qualified by schema using this format: SCHEMA.TABLE.

datatypeWarning

true

Enable or disable the warning message displayed when DBUnit encounter an unsupported data type.

skipOracleRecycleBinTables

false

Enable or disable the processing of oracle recycle bin tables (tables starting with BIN$). Oracle 10g recycle bin tables may break DBUnit’s assumption of tables name uniqueness within a schema since these table are case sensitive. Enable this feature for Oracle 10g databases until the bug in the oracle driver is fixed, which incorrectly reports this system tables to DVUnit.

escapePattern

none

Allows schema, table and column names escaping. The property value is an escape pattern where the ? is replaced by the name. For example, the pattern " [?] " is expanded as " [MY_TABLE] " for a table named "MY_TABLE". The most common escape pattern is "\"?\"" which surrounds the table name with quotes (for the above example it would result in "\"MY_TABLE\""). As a fallback if no questionmark is in the given string and its length is one it is used to surround the table name on the left and right side. For example the escape pattern "\"" will have the same effect as the escape pattern "\"?\"".

tableType

none

Used to configure the list of table types recognized by DBUnit.

datatypeFactory

org.dbunit.dataset. datatype. DefaultDataTypeFactory

Used to configure the DataType factory. You can replace the default factory to add support for non-standard database vendor data types. Provided class must implement org.dbunit.dataset.datatype.IDataTypeFactory.

statementFactory

org.dbunit.database. statement. PreparedStatementFactory

Used to configure the statement factory. Provided class must implement org.dbunit.database.statement.IStatementFactory.

resultSetTableFactory

org.dbunit.database. IResultSetTableFactory

Used to configure the ResultSet table factory. Provided class must implement org.dbunit.database.CachedResultSetTableFactory.

primaryKeyFilter

none

Use to override primary keys detection. Provided class must implement org.dbunit.dataset.filter.IColumnFilter.

identityColumnFilter

none

Use to override IDENTITY column detection (MS SQL specific solution). Provided class must implement org.dbunit.dataset.filter.IColumnFilter.

batchSize

100

Size of the batch updates.

fetchSize

100

The statement fetch size for loading data into a result set table.

metadataHandler

org.dbunit.database. DefaultMetadataHandler

Used to configure the handler used to control database metadata related methods. Provided class must implement org.dbunit.database.IMetadataHandler

For MySQL users…​

If you are using MySQL, you must use org.dbunit.ext.mysql.MySqlMetadataHandler or you will get the "No columns found" error. See http://sourceforge.net/p/dbunit/bugs/226/.

useIdentityInsert

false

Disables MS SQL Server automatic identifier generation for the execution of inserts. For usage with Microsoft driver you should append your JDBC connection with "SelectMethod=cursor".

excludeTablesFromCleanup

empty

List of tables to be excluded from cleanup procedure.
    * Especially handy for sequence tables which are most likely to be cleared
    * when using STRICT cleanup strategy.
Comma separated list of table names to be excluded from cleanup procedure. Especially handy for sequence tables which are otherwise cleared when using STRICT cleanup strategy.

schema

empty

Database schema name to be used by DBUnit
SQL Scripts Customization

Arquillian Persistence Extension allows you to customize the way how SQL scripts are handled.

<extension qualifier="persistence-script">
    <property name="sqlStatementDelimiter">GO</property>
</extension>

List of all available properties

Property name Default value Description

defaultSqlScriptLocation

"scripts/"

Folder where all custom SQL scripts are located.

scriptsToExecuteBeforeTest

none

Ad-hoc scripts or file locations to be used before every test. Might be handy for turning off integrity checks.

scriptsToExecuteAfterTest

none

Ad-hoc scripts or file locations to be used after every test. Could be used to revert operations applied by scriptsToExecuteBeforeTest.

defaultCleanupUsingScriptPhase

AFTER

Defines default cleanup phase for custom SQL scripts.

sqlStatementDelimiter

;

Defines char sequence indicating end of SQL statement