Who should read this?

Any Spring application developer who wants to write integration tests involving a JPA entity bound to a database view.

Motivation

Broadleaf previously announced that we had joined TMForum as a member, and recently, we were awarded OpenAPI certifications for implementing various TMForum APIs (more on that here). 

We had to introduce a few new database views to support the translation from Broadleaf concepts to TMF concepts. We did this with a few steps:

• First, we wrote and added the view definitions to our Liquibase changelogs. These changelogs apply the necessary schema updates to the production database.
• Then, we defined new JPA entities to target and bind to each view. Vlad Mihalcea's article on the subject better explains how to achieve this. However, the key point to observe here is that the @Table annotation on each entity will now reference the name of the view rather than an actual table. This will be relevant to our testing purposes later.

This worked great in a real application stack - Liquibase applied the changelogs to my Postgres instance, and Spring successfully bound my JPA entity to the view.

The problem arose when I tried to write an integration test that involved my new view/entity. 

Our integration tests are mostly run against an embedded HSQL instance because it's lightweight and fast. Furthermore, in tests, we often disable Liquibase instead of relying on the 'Auto DDL' created by Hibernate.

So, where's the problem?

Recall that nothing demarcates a table-backed entity from a view-backed entity in the JPA entity definition. From the perspective of Hibernate, there is no way to know that the @Table annotation is for a view rather than a table. Thus, when the 'Auto DDL' kicks in, Hibernate will create a real table for the entity. 

This article will discuss how to supersede the Hibernate 'Auto DDL' and ensure your JPA entity remains backed by a view in integration tests.

Tutorial

Update Test Properties

1. First, we want to set spring.jpa.defer-datasource-initialization=true in our test properties for reasons explained in the Spring documentation. This will ensure that Spring first runs the 'Auto DDL' behavior from Hibernate and then runs the schema*.sql files we provide to customize the database further.
   • The essential advantage is that we can continue relying on 'Auto DDL' to set up the vast majority of the database schema and only be responsible for customizing the parts we know will be wrong.
2. Next, we want to ensure Spring knows where to find the schema*.sql file we will add with the DDL customizations. The Spring documentation discusses the default locations it looks, but in my case, I had to explicitly define spring.sql.init.schema-locations to have my SQL file (src/test/resources/schema-hsqldb.sql) recognized.

Here is the final state of my src/test/resources/application.yml file (I'm defining the properties here so they consistently apply to all tests in my project by default):

spring:
  liquibase:
    enabled: false
  sql:
    init:
      schema-locations: 'optional:classpath*:schema-hsqldb.sql'
  jpa:
    defer-datasource-initialization: true

Introduce the corrective SQL file

At this stage, we need to introduce the previously mentioned SQL file that will correct the schema to what we expect. 

As noted earlier, in my case, I defined my script at src/test/resources/schema-hsqldb.sql. 

The contents of this script will naturally vary based on what your view looks like, but the general pattern to follow is like this:

1. Execute a DROP TABLE command to eliminate the table Hibernate created for your view automatically. 
2. Execute a CREATE VIEW command that defines the view matching your expectations.

-- Drop the Hibernate auto-created table
DROP TABLE xyz;

-- Create a view instead
CREATE VIEW xyz as
(
-- SELECT ...
);

And that's all it takes! With this in place, the Hibernate Auto DDL will run and establish most of the database schema, and then your script will come in and replace the appropriate tables with views. In your tests, if you run a query against your JPA view entity, you will find it is now correctly sourcing data from your view rather than a new table.

Additional Notes

Ideally, the production Liquibase and this test schema*.sql file would reference the same view definition for guaranteed consistency. 

In some cases, the same view definition SQL works in both places, and you may try configuring your test to point to the SQL file you are using in production. For example, in production, you might use Liquibase's createView changeset with path set to the location of a SQL file with the full view definition (and fullDefinition set to true). Then, in your tests, you have two files specified under spring.sql.init.schema-locations - one to run the DROP TABLE command and the other pointing to the same view-creating file used in production.

In other cases, this may not be possible altogether because the query for your production platforms may not be compatible with your test database. For instance, in my case, the HSQL variant of my SELECT had to include particular column casts to function correctly, so I needed a bespoke HSQL definition of the query anyway. It's still pretty easy to keep the production and test SQL in parity since both are their own SQL files that can be quickly compared with a diff in my IDE.

Conclusion

By implementing this change, we can continue to leverage Hibernate Auto DDL and the benefits of a lightweight embedded database for our integration tests, all while preserving behavioral consistency with our production application.

I hope you found this helpful!