Functional testing Java-EE applications with TestEE.fi – Part 3: JDBC and JPA

Many if not most Java-EE applications use a SQL database for persisting application state. The well established technologies for accessing a SQL based datastore from Java are JDBC and JPA, both of which are an integral part of the Java-EE standard today.

TestEE.fi helps you functional testing your Java-EE application including JDBC and JPA persistence in a very easy and convenient way that’s still highly extendable and customizable. And it integrates with state-of-the-art tooling like Flyway and Liquibase to help you set up your database for testing the same way you do in production.

Preparing the database

Before we dive into technical details and sample code it’s important to understand how TestEE.fi handles databases and transactions and how this integrates with the lifecycle of a test run executed with JUnit.

The central persistence testing concept in TestEE.fi can be expressed in two fundamental truths:

  • Databases are instantiated and initialized per test class and shared by all its test methods.
  • Each test method runs in its own transaction which is rolled back once it’s finished.

In detail, running a typical test class execution with TestEE.fi in JUnit 4 looks like this:

  1. Create the database
  2. Setup schema (with Flyway or Liquibase)
  3. Setup test data (in @TestData method)
  4. Commit
  5. Run test
  6. Rollback
  7. Repeat steps 5 and 6 until all test methods have run
  8. Delete database

This combination of committing schema and test data while rolling back each test case lets TestEE.fi share the database among the test methods while still running each test method in perfect isolation from the others – an important invariant you should keep up in all your tests. Since all changes to the database made by a test method are guaranteed to be rolled back, you can be sure every test method starts with the same prepared database setup. Having to setup and initialize database and test data only once per test class on the other side can make quite a difference in test execution performance if you have complex database schemas, sizable amounts of test data to set up or many test methods to run on the database setup.

Important: You should be aware that TestEE.fi does not obey EJB-style declarative transaction management (as in @TransactionAttribute) like an application container would do. Instead it follows the transactional behavior described above. Tests that require more complex transaction orchestration should be executed inside an application container with a heavyweight test runtime like Arquillian.

Setting  up data sources

Deterministically repeatable and isolated test runs are at the very heart of useful test automation.  For simple Java-based application components JUnit already provides a very suitable lifecycle that makes it very easy to write quality tests (and rather difficult to write bad ones).

To extend this concept to use-case tests that require a SQL database to run their business logic on top of, in-memory SQL databases implemented in Java (like the H2 Database Engine or Apache Derby) are the usual suspects to put underneath your application logic at runtime.

At the time of writing, TestEE.fi comes with out-of-the-box support for two databases: H2 and PostgreSQL. As you might notice, only H2 offers an in-memory-mode (which TestEE.fi uses per default) – and indeed there are some things to look into when using the PostgreSQL integration.

But first well’ take a look at how to setup and use a simple H2 datasource in TestEE.fi. The following example demonstrates the usage of the @TestDataSource annotation:

@TestDataSource(name = "jdbc/dataSource1", factory = H2PostgresConnectionFactory.class)
public class MyTestClass {
}
@TestDataSource annotation

As you can see, all you need to do in order to have a database available in your test cases is to add the @TestDataSource annotation. There you specify the JNDI name of your data source as you would in your application container’s configuration – this is how the application code you’re testing will acquire access to the database either via JDBC or JPA.

Using the second mandatory parameter “factory” you specify a class implementing the ConnectionFactory interface that you want to use for this datasource. In our case the DataSource identified by “jdbc/dataSource1” uses an in-memory H2 database configured to emulate the PostgreSQL SQL dialect.

Note: the ConnectionFactory interface is actually the extension point you can use yourself in order to implement your own database integrations. This however is beyond the scope of this article. Should you need to implement your own ConnectionFactory (which is not hard at all) it’s best to take a look  at the already existing ones like H2PostgresConnectionFactory or PostgresConnectionFactory for reference – and if circumstances allow for it you might even want to contribute you integration to the TestEE.fi open source project, so others can benefit from your efforts, too!

The second ConnectionFactory currently supported by TestEE.fi connects to a PostgreSQL instance and creates a new logical database for each test class (and drops it when all tests have run). Since it requires an actual PostgreSQL installation to connect to, there’s a second annotation required to tell the PostresConnectionFactory what connection-info to pass to the PostgreSQL JDBC driver. The following snippet demonstrates the usage of the PostgresConnection factory by adding a second @TestDataSource to the example provided above:

@TestDataSource(name = "jdbc/dataSource1", factory = H2PostgresConnectionFactory.class)
@TestDataSource(name = "jdbc/dataSource2", factory = PostgresConnectionFactory.class)
@PostgresConfiguration(hostname = "localhost", port = "5432", username = "postgres", password = "postgres")
@RunWith(TestEEfi.class)
public class MyTestClass {
}
@TestDataSource for PostgresSQL

As you can see the annotation @PostgresConfiguration is used to tell the PostgresConnectionFactory how it can reach the database to use for running tests in.

Note: When testing against an actual PostgreSQL instance don’t use a production database. As you can see the user you use to connect to your PostgreSQL instance requires quite extensive permissions in order to create and drop logical databases on demand. On top of that, while TestEE.fi does it’s best to try, it can not be guaranteed that the created databases will be dropped after the test run – should you for example simply terminate a running test case you debugged into, there is not chance for the PostgresConnectionFactory to clean up when your tests have run.

The example above uses hard-coded connectivity information in the @PostgresConfiguration annotation. It’s not uncommon to have different connectivity information for example on your development machines vs. your continuous integration environment. For this purpose, TestEE.fi optionally integrates the evaluation of Groovy expressions in the connectivity information strings. In order to enable the Groovy integration, all you have to do is to add Groovy as a test dependency as shown in the following Maven example (extending the example given in Part 1):

<dependencies>
	<dependency>
		<groupId>fi.testee</groupId>
		<artifactId>testeefi-junit4-all</artifactId>
		<version>0.6.1</version>
		<scope>test</scope>
	</dependency>
	
	<!-- TestEE.fi uses slf4j, so add logback as logging implementation -->
	<dependency>
		<groupId>ch.qos.logback</groupId>
		<artifactId>logback-classic</artifactId>
		<version>1.1.7</version>
		<scope>test</scope>
	</dependency>

	<!-- Enable TestEE.fi's Groovy integration -->
	<dependency>
		<groupId>org.codehaus.groovy</groupId>
		<artifactId>groovy-all</artifactId>
		<version>2.4.12</version>
	</dependency>
</dependencies>
Enable TestEE.fi's Groovy integration with Maven

If you’re using Gradle, the snippet looks like this:

dependencies {
	testCompile 'fi.testee:testeefi-junit4-all:0.6.1'
	testCompile 'ch.qos.logback:logback-classic:1.1.7'
	testCompile 'org.codehaus.groovy:groovy-all:2.4.12'
}
Enable TestEE.fi's Groovy integration with Gradle

Once Groovy is available at test runtime the Strings used in the @PostgresConfiguration annotation are treated and evaluated as Groovy Strings. The following example leverages this mechanism to read the configuration from environment variables (which usually are very easy to provide in CI environments) and even providing a fallback (for example for development machines where the tests run against local database instances):

@TestDataSource(name = "jdbc/dataSource1", factory = H2PostgresConnectionFactory.class)
@TestDataSource(name = "jdbc/dataSource2", factory = PostgresConnectionFactory.class)
@PostgresConfiguration(
        hostname = "${System.getenv('PSQL_HOSTNAME') ?: 'localhost'}",
        port = "${System.getenv('PSQL_PORT') ?: '5432'}",
        username = "${System.getenv('PSQL_USER') ?: 'postgres'}",
        password = "${System.getenv('PSQL_PASSWORD') ?: 'postgres'}"
)
@RunWith(TestEEfi.class)
public class MyTestClass {
}
Groovy integration for parameterizing PostgreSQL connectivity

Creating the database schema

In order to test your application against a SQL database you usually need to make sure that it contains the tables, views, sequences etc. your business logic builds upon. In order to setup your application’s schema in production environments it’s common practice to use database schema versioning tools like Flyway or Liquibase (if you don’t I strongly suggest you give it at least a shot).

TestEE.fi makes it trivial to leverage your production database setup created with Flyway or Liquibase and integrate it directly into your test setup – both are directly supported by TestEE.fi!

Let’s just re-use the example code we established so far. While it’s not very likely that you encounter something like this in an actual project, I’ll be using both Flyway and Liquibase in the same test project, just to demonstrate how easy it really is to have TestEE.fi setup your test schema.

@TestDataSource(name = "jdbc/dataSource1", factory = H2PostgresConnectionFactory.class)
@TestDataSource(name = "jdbc/dataSource2", factory = PostgresConnectionFactory.class)
@PostgresConfiguration(hostname = "localhost", port = "5432", username = "postgres", password = "postgres")
@Liquibase(dataSource = "jdbc/dataSource1", changeLogFile="liquibase/changelog.xml")
@Flyway(dataSource = "jdbc/dataSource2")
@RunWith(TestEEfi.class)
public class MyTestClass {
}
Using Flyway and Liquibase in TestEE.fi

As you can see, both Flyway and Liquibase are simply enabled by adding the corresponding annotation and specifying the datasource on which the schema setup is to be performed. Liquibase also requires you to specify where to find the changelog XML file whereas Flyway comes with a default value “db/migrations”. In this case we’re setting up the H2 database using Liquibase and the PostgreSQL database is migrated with Flyway.

Inserting test data

Setting up your schema is often a necessary step when writing tests that involve SQL databases. Once you have your schema up an ready to go, you often also need some kind of test data set up before your tests can run. This can be done using one (or a combination of) the following options:

  1. Use additional Liquibase changelog files or Flyway migrations
  2. Setup test data before each test in a @Before method
  3. Use a static @TestData method

The first option can be rather comfortable – especially if you’re sharing your test data among many test classes it comes rather naturally. Flyway’s Java-based migrations even allow you to programmatically create your test data – this is out of the scope of this article and already nicely described here.

The second option comes naturally when you already have some experience with JUnit and have data that needs to be different on a per-test-class basis: you simply insert what you need in a @Before method which is executed before each test method of a class. TestEE.fi will be rolling back the transaction to the state prior to the @Before method was invoked, so repeatability is not a concern as well.

Preparing your test data on a per-methods basis has a relevant drawback, though: If your test data setup takes some time (e.g. is you have lots of test data to insert) it can be rather costly to execute it on a per-test basis. A more performance-oriented way to insert the test data is to use a static method annotated with @TestData as the following example (again building upon our previous code) demonstrates:

@TestDataSource(name = "jdbc/dataSource1", factory = H2PostgresConnectionFactory.class)
@TestDataSource(name = "jdbc/dataSource2", factory = PostgresConnectionFactory.class)
@PostgresConfiguration(hostname = "localhost", port = "5432", username = "postgres", password = "postgres")
@Liquibase(dataSource = "jdbc/dataSource1", changeLogFile="liquibase/changelog.xml")
@Flyway(dataSource = "jdbc/dataSource2")
@RunWith(TestEEfi.class)
public class MyTestClass {
    @TestData
    public static void setupTestData(
            final TestDataSources dataSources,
            final TestPersistenceUnits persistenceUnits
    ) throws SQLException {
        try(final Connection c = dataSources.get("jdbc/dataSource1").getConnection()) {
			// Do something with the JDBC connection to setup your data
		}
		
		final EntityManager em = persistenceUnits.get("myPersistenceUnit");
		// Use entity manager to setup your test data
    }
}
Usage of @TestData

Since static test data setup happens on a per-class basis (which means there is no test class instance to operate on), access to the database cannot simply be injected into some member variable. An instance of the interface TestDataSources is provided as a method parameter instead, and you can simply invoke TestDataSources.get() in order to get a connection that you can use to insert your test data.

The second paramater of type TestPersistenceUnits provides the same functionality based on JPA: it returns an EntityManager that you can use to setup your test data using JPA. Of course for that you need to have a persistence unit defined in META-INF/persistence.xml that uses one of the data sources configured on your test case.

Note: Both parameters are optional, so you can simply use the one you need for your purposes and leave out the other one.

After the invocation of the @TestData methods (one per class in your class hierarchy is allowed), TestEE.fi commits the transaction on the database and starts running the test methods of your test class. After each method the database is rolled back to the state right after your test data has been set up.

Writing your test

Once you have your database schema and test data all set up it’s really easy to write test cases on top of that. Access to JDBC data sources and JPA entity managers works the same way it would in the application container: they will be injected into your EJBs and CDI beans. Additionally you can have them injected directly into your test class. That can be really handy when performing operations on your database (like asserting some database state) for which you have no business logic: simply write it in your test class.

The following simple example demonstrates direct access to your database from your test methods. Access via EJBs is not shown here but works exactly the same way.

@TestDataSource(name = "jdbc/dataSource1", factory = H2PostgresConnectionFactory.class)
@TestDataSource(name = "jdbc/dataSource2", factory = PostgresConnectionFactory.class)
@PostgresConfiguration(hostname = "localhost", port = "5432", username = "postgres", password = "postgres")
@Liquibase(dataSource = "jdbc/dataSource1", changeLogFile="liquibase/changelog.xml")
@Flyway(dataSource = "jdbc/dataSource2")
@RunWith(TestEEfi.class)
public class MyTestClass {
    @TestData
    public static void setupTestData(final TestDataSources dataSources, final TestPersistenceUnits persistenceUnits) throws SQLException {
		// Test data setup
    }
	
    @PersistenceContext(unitName = "myPersistenceUnit")
    private EntityManager entityManager;
    @Resource(mappedName = "jdbc/dataSource1")
    private DataSource dataSource;
	
	 @Test
    public void can_test_with_jpa() {
		// Insert entity with ID 1
        entityManager.persist(new MyEntity(1L));
		
		// Ensure it's available in the database
		assertNotNull(entityManager.find(MyEntity.class, 1l));
    }
	
	@Test
    public void can_test_with_jdbc() throws SQLException {
		try(final Connection c = dataSource.getConnection()) {
			// write some test with JDBC
		}
    }
}
Using JDBC and JPA in test methods

As you can see we use exactly the same annotations @PersistenceContext and @Resource we would be using to have those resources available in EJBs or CDI managed beans.

Conclusion

At first glance this looks like a lot of annotations. But remember that we built quite a sizable example here with many different functionalities:

  • Usage of both an in-memory H2 database and an external PostgreSQL database
  • Usage of both Flyway and Liquibase to setup the schemas in our two databases
  • Usage of a @TestData method to setup test data both via JDBC and JPA
  • Test cases for both JDBC and JPA testing

In my experience you rarely need that kind of complexity in your tests. Most applications you come across work perfectly fine with simpler setups which in turn are also way easier to configure in TestEE.fi – and it’s good to know that, should you ever encounter a more complex setup, TestEE.fi will be up to  the task!

The source code of the sample application on which the examples above are based can be found here.

Note: The example on github uses a second H2 in-memory database instead of a PostgreSQL database so it can be run without any prior installation of PostgreSQL.

If you want to know more about advanced testing with TestEE.fi, be sure to check out the other posts of this series to get you up to speed quickly:

4 thoughts on “Functional testing Java-EE applications with TestEE.fi – Part 3: JDBC and JPA

  1. michael

    Hi Alex, TestEE.fi looks very promising to me! 🙂 I assume it is also possible to combine JDBC/JPA and JAX-RS/REST testing in order to create a “real” end2end test for a REST service with database layer?!?

    Reply
    1. Alex Stockinger Post author

      Hi Michael,
      thank you for your feedback. Of course you can combine JDBC/JPA tests with the JAX-RS integration for end2end testing.
      Each test will run in a separate transaction and REST requests are serialized by TestEE.fi since it does not support multiple parallel transactions in one test case.
      That is actually one of the most useful scenarios and TestEE.fi was specifically designed with such use cases in mind.

      Should you have further questions just let me known 🙂

      Regards,
      Alex

      Reply
  2. michael

    Hi Alex, again another query regarding “…. Of course for that you need to have a persistence unit defined in META-INF/persistence.xml that uses one of the data sources configured on your test case.” What is the recommended way to define a second “test” persistence.xml (e.g. /persistence/src/test/resources/META-INF/persistence.xml)? I want to use one for test and another one for the productive usage. Or do you recommend to use one for both and just define a additional persistence-unit inside my prod persistence.xml? I am looking forward to your suggestion….

    Reply
    1. Alex Stockinger Post author

      It is possible to define a test-only persistence unit in secret/test/resources/META-INF/persistence.xml but that would mean you’re testing something that will never make it to production I think? May I ask what your use cases for that scenario is?

      Cheers,
      Alex

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *