The Coding Notes

TL;DR : JPA (2.*) does not support the "UNION" sql operation. JPA does not require a persistence provider to support Right outer joins or Right outer fetch joins, to be compliant with its API. Hibernate (5.*) does not support Right outer joins or Right outer fetch joins. Last week I bumped heads with JPA (2.2) and Hibernate (5.4). I had to write a search query on some tangled datastructures.  I tend to write queries out on paper first, for clarity. Here are some takeaways, while I wasted some scrap paper on the iterations. State of the UNION in JPA I ended up with a union of 3 queries. The moment that I wanted to get dirty with JPA to create a CriteriaQuery I hit the first snag. JPA 2.0 does not support the sql "UNION" operation. One does not simply join with JPA and Hibernate The next step was to rewrite the set of unions to a set of joins. 5 (+ 2 shared) Left joins and 4 right outer joins on I had one single unified query. I quickly glanced in my ID...

Flyway is a great tool to manage the consistency of your database in Java-land.  For each change in the database schema that is needed to run your application consistently with the db, you place a sql file in the db.migration folder. This allows flyway to manage the migration. Flyway checks if the sql files are already executed (using a hash table in the target database), else it will run the neccessary sql script(s). There is however a little setup required, even within a Spring Boot application. You can use the maven flyway plugin to run flyway migrations with mvn over CLI. This would mean decoupling a major part of your database's consistency management from your application into a build script... Though the only thing a Spring Boot application actually needs, is the flyway-core dependency ( which is recommended by Spring ). Keeping the management within the application. Probably the right choice since in this case the db only exists by the authority of that application....