Blaze Persistence – The Best Way to Write JPA Criteria Queries

Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?

Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.

So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!


In this article, I’m going to explain why the Blaze Persistence framework provides the best API to write JPA Criteria queries.

Blaze Persistence is a very advanced data access framework maintained by Christian Beikov, who’s also a Hibernate ORM project contributor.

I first heard of Blaze Persistence during an interview Christian gave us for the Hibernate forum, and if you are using JPA and Hibernate, you should definitely use Blaze Persistence as well.

Domain Model

Let’s assume we have a parent post table that has a one-to-many table relationship with the post_comment child table.

Blaze Persistence JPA Criteria tables

The post table contains a single record:

| id | title                             |
| 1  | High-Performance Java Persistence |

And the post_comment child table contains three rows that are associated with the single post record:

| id | review                                | post_id |
| 1  | Best book on JPA and Hibernate!       | 1       |
| 2  | A great reference book.               | 1       |
| 3  | A must-read for every Java developer! | 1       |

Getting the post with the latest comment

Now, we want a report that provides us the post info along with its latest post_comment, and, for this purpose, we can use the following SQL query:

SELECT AS post_id,
   p.title AS post_title, AS comment_review
   SELECT AS id, AS review,
      pc1.post_id AS post_id,
      MAX( OVER (PARTITION BY pc1.post_id) AS max_id
      post_comment pc1
) pc2
   post p ON = pc2.post_id
WHERE = pc2.max_id 

When running the SQL query above, we get the expected result set:

| post_id | post_title                        | comment_review                        |
| 1       | High-Performance Java Persistence | A must-read for every Java developer! |

Blaze Persistence JPA Criteria

Now, if we want to generate the aforementioned SQL query dynamically, we cannot use the JPA Criteria API since it doesn’t have support for Window Functions or Derived Tables.

Luckily, not only that Blaze Persistence can help us generate this query, but it can that for Oracle, PostgreSQL, MySQL, or SQL Server.

The first step, is to create a CriteriaBuilderFactory using the existing EntityManagerFactory object reference:

CriteriaBuilderFactory cbf = Criteria

And, the Criteria query is going to look as follows:

List<Tuple> tuples = cbf
    .create(entityManager, Tuple.class)
    .fromSubquery(PostCommentMaxIdCTE.class, "pc2")
        .from(PostComment.class, "pc1")
        .bind("maxId").select("MAX( OVER (PARTITION BY")
    .joinOn(Post.class, "p", JoinType.INNER)
        .onExpression(" = pc2.postId").end()
    .select("", "post_id")
    .select("p.title", "post_title")
    .select("", "comment_review")

Notice how similar is the Blaze Persistence Criteria API compared to the original SQL query.

When querying JPA entities, Blaze Persistence can use the JPA entity Metamodel to generate the SQL query, but for the Derived Table that builds the projection with the MAX Window Function call, we need to provide a CTE structure that defines the Metamodel for the underlying projection.

For this reason, we created the PostCommentMaxIdCTE class that holds the pc2 Dervied Table projection:

public class PostCommentMaxIdCTE {
    private Long id;
    private String review;
    private Long postId;
    private Long maxId;

The CTE structures will have to be registered with JPA in the same way as entities. So, if you are using Spring Boot, then you can use the @EntityScan annotation to reference the package where the Blaze Persistence CTE classes are located:


And, when running the Blaze Persistence Criteria query above on PostgreSQL, the following SQL query is executed:

SELECT AS col_0_0_,
    blazepersi1_.title AS col_1_0_, AS col_2_0_
    SELECT AS col_0_0_, AS col_1_0_,
        blazepersi0_.post_id AS col_2_0_,
        MAX( OVER (
            PARTITION BY blazepersi0_.post_id
        ) AS col_3_0_
            post_comment blazepersi0_
) blazepersi0_(id, review, postid, maxid)
INNER JOIN post blazepersi1_ 
    ON (
        (NULL IS NULL) AND = blazepersi0_.postid 
WHERE = blazepersi0_.maxid 

And, when running the same Blaze Persistence JPA Criteria query on MySQL, the following SQL query is generated:

SELECT AS col_0_0_,
    blazepersi1_.title AS col_1_0_, AS col_2_0_
        NULL id,
        NULL review,
        NULL postId,
        NULL maxId
    FROM   dual
    WHERE  1 = 0
   AS col_0_0_,
   AS col_1_0_,
            blazepersi0_.post_id AS col_2_0_,
            MAX( OVER (
                PARTITION BY blazepersi0_.post_id
            )                    AS col_3_0_
        FROM post_comment blazepersi0_
) blazepersi0_
INNER JOIN post blazepersi1_
    ON (
        ( NULL IS NULL ) AND = blazepersi0_.postid 
WHERE = blazepersi0_.maxid

Cool, right?

I'm running an online workshop on the 11th of October about High-Performance SQL.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.


Blaze Persistence is a very useful add-on for JPA and Hibernate because it allows you to generate dynamic queries that can use non-trivial SQL features, such as:

And, since the generated SQL queries are generated based on the underlying Hibernate Dialect, we don’t have to worry about query portability in case our application needs to support multiple relational database systems.

Transactions and Concurrency Control eBook

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.