Introduction

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.

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
   p.id AS post_id,
   p.title AS post_title,
   pc2.review AS comment_review
FROM (
   SELECT
      pc1.id AS id,
      pc1.review AS review,
      pc1.post_id AS post_id,
      MAX(pc1.id) OVER (PARTITION BY pc1.post_id) AS max_id
   FROM 
      post_comment pc1
) pc2
JOIN 
   post p ON p.id = pc2.post_id
WHERE 
   pc2.id = 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
    .getDefault()
    .createCriteriaBuilderFactory(
        entityManagerFactory
    );

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("id").select("pc1.id")
        .bind("review").select("pc1.review")
        .bind("postId").select("pc1.post.id")
        .bind("maxId").select("MAX(pc1.id) OVER (PARTITION BY pc1.post.id)")
    .end()
    .joinOn(Post.class, "p", JoinType.INNER)
        .onExpression("p.id = pc2.postId").end()
    .where("pc2.id").eqExpression("pc2.maxId")
    .select("p.id", "post_id")
    .select("p.title", "post_title")
    .select("pc2.review", "comment_review")
    .getResultList();

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:

@CTE
@Entity
public class PostCommentMaxIdCTE {
    @Id
    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:

@EntityScan("com.vladmihalcea.hpjp.jpa.repository.cte")

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

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

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

SELECT 
    blazepersi1_.id AS col_0_0_,
    blazepersi1_.title AS col_1_0_,
    blazepersi0_.review AS col_2_0_
FROM (
    SELECT 
        NULL id,
        NULL review,
        NULL postId,
        NULL maxId
    FROM   dual
    WHERE  1 = 0
    UNION ALL (
        SELECT 
            blazepersi0_.id AS col_0_0_,
            blazepersi0_.review AS col_1_0_,
            blazepersi0_.post_id AS col_2_0_,
            MAX(blazepersi0_.id) OVER (
                PARTITION BY blazepersi0_.post_id
            )                    AS col_3_0_
        FROM post_comment blazepersi0_
    )
) blazepersi0_
INNER JOIN post blazepersi1_
    ON (
        ( NULL IS NULL ) AND 
        blazepersi1_.id = blazepersi0_.postid 
    )
WHERE 
    blazepersi0_.id = 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.

Conclusion

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:

• CTE
• Recursive CTE
• Window Functions
• Lateral Joins

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.

Insert details about how the information is going to be processed

DOWNLOAD NOW