Create better Criteria queries with Blaze persistence


Get access to all my video courses, 2 monthly Q&A calls, monthly coding challenges, a community of like-minded developers, and regular expert sessions.


The Blaze Persistence project provides an interesting alternative to JPA’s Criteria API. Both APIs enable you to define queries dynamically at runtime. Most developers use it to create query statements based on user input or the result of a business operation. Unfortunately, JPA’s Criteria API isn’t very popular because it’s hard to read and write. As I will show you in this tutorial, Blaze Persistence’s Criteria API provides an easier-to-use solution. It integrates with various JPA implementations and provides a huge set of query features.

In this tutorial, I will give you a quick introduction to the Blaze Persistence Criteria API. In future articles, we will take a closer look at more advanced query features, like CTE and window functions, the extensions to JPA’s Criteria API, and the entity view feature.

Adding Blaze Persistence to your project

Blaze Persistence consists of multiple modules. You always need to add the core API and implementation to your project. And you combine it with an integration module for your specific JPA implementation. You can find a full list of all modules in the documentation.

For this article, I’m using Blaze Persistence with Hibernate 6. So, I need to add dependencies to Hibernate and Blaze Persistence’s core API, core implementation, and Hibernate 6 integration to my pom.xml file.

<dependency>
	<groupId>org.hibernate.orm</groupId>
	<artifactId>hibernate-core</artifactId>
	<version>${version.hibernate}</version>
</dependency>
<dependency>
	<groupId>com.blazebit</groupId>
	<artifactId>blaze-persistence-core-api-jakarta</artifactId>
	<version>${version.blaze}</version>
	<scope>compile</scope>
</dependency>
<dependency>
	<groupId>com.blazebit</groupId>
	<artifactId>blaze-persistence-core-impl-jakarta</artifactId>
	<version>${version.blaze}</version>
	<scope>runtime</scope>
</dependency>
<dependency>
	<groupId>com.blazebit</groupId>
	<artifactId>blaze-persistence-integration-hibernate-6.0</artifactId>
	<version>${version.blaze}</version>
	<scope>runtime</scope>
</dependency>

In the next step, you need to instantiate a CriteriaBuilderFactory instance. You will use that object in your persistence code to create new queries.

CriteriaBuilderConfiguration config = Criteria.getDefault();
CriteriaBuilderFactory cbf = config.createCriteriaBuilderFactory(this.emf);

You should instantiate the CriteriaBuilderFactory at application startup and only create 1 instance. Depending on your environment, you might want to do that in a CDI producer or define a singleton bean in Spring. The official documentation provides several detailed examples of this.

Let’s use the CriteriaBuilderFactory cbf to define some queries.

Creating a basic query

Using Blaze Persistence, it only takes 2 method calls to define a simple query and execute it. The following code snippet shows you the creation and execution of a query that selects all ChessPlayer entities from the database.

EntityManager em = emf.createEntityManager();
em.getTransaction().begin();

List<ChessPlayer> players = cbf.create(em, ChessPlayer.class).getResultList();

players.forEach(p -> log.info(p.getFirstName() + " " + p.getLastName()));

em.getTransaction().commit();
em.close();

A call of the create method defines a query that will be executed using the provided EntityManager instance. The 2nd method parameter specifies the return type of the query. And it also acts as the default definition of the SELECT and FROM clauses. If you want to, you can, of course, overwrite these clauses.

You can see the executed SQL statement in your log file when you execute this simple test case and use my recommended logging configuration for Hibernate. As expected, the query selects all records from the ChessPlayer table and returns all columns mapped by the ChessPlayer entity class.

17:53:39,968 DEBUG [org.hibernate.SQL] - 
    select
        c1_0.id,
        c1_0.birthDate,
        c1_0.firstName,
        c1_0.lastName,
        c1_0.version 
    from
        ChessPlayer c1_0
17:53:40,037 INFO  [com.thorben.janssen.TestBlazeCriteria] - Magnus Carlsen
17:53:40,037 INFO  [com.thorben.janssen.TestBlazeCriteria] - Jorden van Foreest
17:53:40,037 INFO  [com.thorben.janssen.TestBlazeCriteria] - Anish Giri
17:53:40,037 INFO  [com.thorben.janssen.TestBlazeCriteria] - Fabiano Caruana

Hibernate then maps the query result to managed ChessPlayer entity objects. You can use these objects in the same way as any entity object you fetched using a JPQL query or any of Hibernate’s APIs.

This is obviously a very basic query that you will not use in your application. You will need to add a WHERE clause, join other tables, order the result and use pagination. So, let’s do that next.

Defining a WHERE clause

You can define the WHERE clause of your query by calling the where method on the CriteriaBuilder object returned by the create method.

EntityManager em = emf.createEntityManager();
em.getTransaction().begin();

List<ChessPlayer> players = cbf.create(em, ChessPlayer.class)
							   .where("firstName").eq("Fabiano")
							   .where("lastName").eq("Caruana")
							   .getResultList();

players.forEach(p -> log.info(p.getFirstName() + " " + p.getLastName()));

em.getTransaction().commit();
em.close();

As you can see in the code snippet, Blaze Persistence provides a fluent API, and you can call the where method multiple times. Each call returns a PredicateBuilder. The builder provides a set of methods to define predicates, like equals, greater than, less, or equal than, and is empty. You can also check if an entity is a member of an association, define subselects, and much more.

In this example, I keep it simple. I call the eq method to define equal comparisons of the firstName and lastName attributes of my ChessPlayer entity.

The parameters that I provide to the eq method make the definition of this WHERE clause interesting. Instead of defining a bind parameter, I provide the parameter value. With other query languages and APIs, e.g., JPQL, that’s something you shouldn’t do because it causes SQL injection vulnerabilities. But you don’t have to worry about that when using Blaze Persistence. It automatically adds a bind parameter to the query statement and sets the provided value as the bind parameter value.

19:03:52,772 DEBUG [org.hibernate.SQL] - 
    select
        c1_0.id,
        c1_0.birthDate,
        c1_0.firstName,
        c1_0.lastName,
        c1_0.version 
    from
        ChessPlayer c1_0 
    where
        c1_0.firstName=? 
        and c1_0.lastName=?
19:03:52,824 INFO  [com.thorben.janssen.TestBlazeCriteria] - Fabiano Caruana

In the test case, I called the where method twice. As you can see in the log output, Blaze Persistence generated a compound predicate for it and connected the 2 predicates with AND.

Creating nested compound predicates

You can define your own compound predicates by calling the whereAnd or whereOr method. These methods return a WhereAndBuilder or WhereOrBuilder instance, which you can use to define a nested compound predicate by calling the where method one or more times. And after you’ve added all predicates to your compound predicate, you need to call the endAnd or endOr method to close your compound predicate.

Don’t worry; it’s much easier than it might sound. I use these methods in the following example to select the chess players Fabiano Caruana and Magnus Carlsen.

EntityManager em = emf.createEntityManager();
em.getTransaction().begin();

List<ChessPlayer> players = cbf.create(em, ChessPlayer.class)
							   .whereOr()
									.whereAnd().where("firstName").eq("Fabiano")
										.where("lastName").eq("Caruana")
									.endAnd()
									.whereAnd().where("firstName").eq("Magnus")
										.where("lastName").eq("Carlsen")
									.endAnd()
							   .endOr()
							   .getResultList();

players.forEach(p -> log.info(p.getFirstName() + " " + p.getLastName()));

em.getTransaction().commit();
em.close();

When I execute this test case, you can see in the log output that Blaze Persistence generated the expected query. The WHERE clause checks the firstName and lastName of the player against 2 sets of bind parameter values. And it connects these 2 groups of predicates with OR.

19:13:15,557 DEBUG [org.hibernate.SQL] - 
    select
        c1_0.id,
        c1_0.birthDate,
        c1_0.firstName,
        c1_0.lastName,
        c1_0.version 
    from
        ChessPlayer c1_0 
    where
        (
            c1_0.firstName=? 
            and c1_0.lastName=?
        ) 
        or (
            c1_0.firstName=? 
            and c1_0.lastName=?
        )
19:13:15,598 INFO  [com.thorben.janssen.TestBlazeCriteria] - Magnus Carlsen
19:13:15,598 INFO  [com.thorben.janssen.TestBlazeCriteria] - Fabiano Caruana

As you can see, when you combine the where, whereAnd, and whereOr methods with the previously mentioned methods to define predicates, you can define complex WHERE clauses. Blaze Persistence even supports more advanced predicates than the JPA specification does. But those are a topic for another tutorial.

Defining a FROM clause

When I showed you how to define a basic query, I mentioned that Blaze Persistence generates a default FROM clause. It uses the entity class you referenced when calling the create method.

EntityManager em = emf.createEntityManager();
em.getTransaction().begin();

List<ChessPlayer> players = cbf.create(em, ChessPlayer.class).getResultList();

players.forEach(p -> log.info(p.getFirstName() + " " + p.getLastName()));

em.getTransaction().commit();
em.close();

So, in this example, Blaze Persistence uses a default FROM clause based on the ChessPlayer entity class. You can explicitly define the root of your FROM clause by calling the from method on the CriteriaBuilder. In that case, the entity class you reference in the create method only defines the return type of your query. And the entity class referenced in the from method becomes your query root. That method also allows you to provide a 2nd method parameter that defines the alias of your query root.

EntityManager em = emf.createEntityManager();
em.getTransaction().begin();

List<ChessPlayer> players = cbf.create(em, ChessPlayer.class)
							   .from(ChessPlayer.class, "p")
							   .getResultList();

players.forEach(p -> log.info(p.getFirstName() + " " + p.getLastName()));

em.getTransaction().commit();
em.close();

If you call the from method multiple times, Blaze Persistence will add each referenced entity class to the FROM clause. This generates a cross join. In the following sections, I will show you how to define inner and outer joins.

EntityManager em = emf.createEntityManager();
em.getTransaction().begin();

List<Tuple> result = cbf.create(em, Tuple.class)
							   .from(ChessPlayer.class, "p")
							   .from(ChessTournament.class, "t")
							   .select("p", "person")
							   .select("t", "tournament")
							   .getResultList();

result.forEach(r -> log.info(((ChessPlayer)r.get("person")).getFirstName() + " " + ((ChessPlayer)r.get("person")).getLastName()));

em.getTransaction().commit();
em.close();

As soon as your query has more than 1 root, you also need to specify the SELECT clause. You can do that by calling the select method.

The 1st method parameter defines the element you want to select. This can be a reference to an entity, an entity attribute, a database function, an expression, or an ObjectBuilder. For the scope of this tutorial, I will only use entity and entity attribute references. I will explain Blaze Persistence’s ObjectBuilder in more detail in a future article.

When calling the select method, you can provide a 2nd method parameter to specify an alias. That makes it easier to reference the selected element when processing the returned Tuple instances.

Using an implicit join

Similar to JPQL, Blaze Persistence also supports implicit joins. You define them by referencing an entity attribute that models an association followed by the path operator “.” and the name of an attribute on the associated entity. Blaze Persistence then adds a LEFT JOIN clause for that association to the query statement.

I’m using that in the following example to define an implicit join from the ChessTournament to the ChessPlayer entity.

EntityManager em = emf.createEntityManager();
em.getTransaction().begin();

List<ChessTournament> tournaments = cbf.create(em, ChessTournament.class)
										.where("players.firstName").eq("Fabiano")
											.where("players.lastName").eq("Caruana")
										.getResultList();

tournaments.forEach(t -> log.info(t.getName()));

em.getTransaction().commit();
em.close();

The WHERE clause of my query contains 2 implicit joins using the same association. Blaze Persistence expects that these are identical. The generated SQL statement only contains 1 LEFT JOIN from the ChessPlayer table to the association table representing the many-to-many association.

07:20:34,964 DEBUG [org.hibernate.SQL] - 
    select
        c1_0.id,
        c1_0.endDate,
        c1_0.name,
        c1_0.startDate,
        c1_0.version 
    from
        ChessTournament c1_0 
    left join
        (ChessTournament_ChessPlayer p1_0 
    join
        ChessPlayer p1_1 
            on p1_1.id=p1_0.players_id) 
                on c1_0.id=p1_0.ChessTournament_id 
        where
            p1_1.firstName=? 
            and p1_1.lastName=?
07:20:34,994 INFO  [com.thorben.janssen.TestBlazeCriteria] - Tata Steel Chess Tournament 2021

Using an explicit join

In addition to the implicit joins, Blaze Persistence offers multiple versions of the innerJoin, leftJoin, and rightJoin methods to define inner, left, and right join clauses. You can even define lateral joins and joins to subselects. Both are features not supported by JPQL and are out of scope for this basic introduction to Blaze Persistence’s query features.

Here you can see a simple example that explicitly defines an inner join from the ChessTournament to the ChessPlayer entity class. It also defines p as the alias of the joined ChessPlayer, which makes it easier to reference the joined entity in the WHERE clause. By default, Blaze Persistence uses the lowercase version of the entity’s name as the alias.

EntityManager em = emf.createEntityManager();
em.getTransaction().begin();

List<ChessTournament> tournaments = cbf.create(em, ChessTournament.class)
										.innerJoin("players", "p")
										.where("p.firstName").eq("Fabiano")
											.where("p.lastName").eq("Caruana")
										.getResultList();

tournaments.forEach(t -> log.info(t.getName()));

em.getTransaction().commit();
em.close();

When executing this query, Blaze Persistence generates an SQL statement with the corresponding joins and a WHERE clause that limits the query result to all tournaments played by Fabiano Caruana.

07:38:28,951 DEBUG [org.hibernate.SQL] - 
    select
        c1_0.id,
        c1_0.endDate,
        c1_0.name,
        c1_0.startDate,
        c1_0.version 
    from
        ChessTournament c1_0 
    join
        (ChessTournament_ChessPlayer p1_0 
    join
        ChessPlayer p1_1 
            on p1_1.id=p1_0.players_id) 
                on c1_0.id=p1_0.ChessTournament_id 
        where
            p1_1.firstName=? 
            and p1_1.lastName=?
07:38:28,992 INFO  [com.thorben.janssen.TestBlazeCriteria] - Tata Steel Chess Tournament 2021

Defining a JOIN FETCH

In addition to the inner and outer joins you know from SQL, the JPA specification supports a JOIN FETCH clause. It tells the persistence provider to fetch the referenced association when fetching the selected entity object. This is one of the most commonly used features to improve the performance of read operations, and Blaze Persistence supports it as well.

You can define a JOIN FETCH clause similarly to the previously described inner and outer joins. The only difference is that you now need to call the fetchinnerJoinFetchleftJoinFetch, or rightJoinFetch method.

EntityManager em = emf.createEntityManager();
em.getTransaction().begin();

List<ChessTournament> tournaments = cbf.create(em, ChessTournament.class)
										.fetch("players")
										.innerJoin("players", "p")
										.where("p.firstName").eq("Fabiano")
											.where("p.lastName").eq("Caruana")
										.getResultList();

tournaments.forEach(t -> log.info(t.getName()));

em.getTransaction().commit();
em.close();

When doing that, please keep in mind that you should use an additional join to define your WHERE clause. Otherwise, your query will only return the associated entities that match the predicates of your WHERE clause. This initializes the association incompletely.

I did that in the previous code sample. As you can see in the log output, the executed SQL statement joins the association twice. The 1st join fetches all associated ChessPlayers, and the 2nd one limits the query result to the tournaments in which Fabiano Caruana participated.

10:22:35,027 DEBUG [org.hibernate.SQL] - 
    select
        c1_0.id,
        c1_0.endDate,
        c1_0.name,
        p1_0.ChessTournament_id,
        p1_1.id,
        p1_1.birthDate,
        p1_1.firstName,
        p1_1.lastName,
        p1_1.version,
        c1_0.startDate,
        c1_0.version 
    from
        ChessTournament c1_0 
    left join
        (ChessTournament_ChessPlayer p1_0 
    join
        ChessPlayer p1_1 
            on p1_1.id=p1_0.players_id) 
                on c1_0.id=p1_0.ChessTournament_id 
        join
            (ChessTournament_ChessPlayer p2_0 
        join
            ChessPlayer p2_1 
                on p2_1.id=p2_0.players_id) 
                    on c1_0.id=p2_0.ChessTournament_id 
            where
                p2_1.firstName=? 
                and p2_1.lastName=?
10:22:35,070 INFO  [com.thorben.janssen.TestBlazeCriteria] - Tata Steel Chess Tournament 2021

The last thing I want to show you in this article is how to paginate your query result. You should then have all the knowledge you need to start writing your first queries using Blaze Persistence.

Paginating your query result

Using SQL, you can choose between offset pagination and keyset pagination. Blaze Persistence offers an easy-to-use API for both options.

Applying offset pagination

Most developers are familiar with offset pagination. You simply add a LIMIT and OFFSET clause or a FETCH and OFFSET clause to your query or call the setFirstResult and setMaxResult methods on JPA’s query interface. Blaze Persistence’s CriteriaBuilder offers the same methods.

EntityManager em = emf.createEntityManager();
em.getTransaction().begin();

List<ChessPlayer> players = cbf.create(em, ChessPlayer.class)
							   .setFirstResult(2)
							   .setMaxResults(3)
							   .getResultList();

players.forEach(p -> log.info(p.getFirstName() + " " + p.getLastName()));

em.getTransaction().commit();
em.close();

As you can see in the log output, calling the setFirstResult and setMaxResults methods adds an OFFSET and a FETCH clause to the SQL statement.

10:52:52,061 DEBUG [org.hibernate.SQL] - 
    select
        c1_0.id,
        c1_0.birthDate,
        c1_0.firstName,
        c1_0.lastName,
        c1_0.version 
    from
        ChessPlayer c1_0 offset ? rows fetch first ? rows only
10:52:52,112 INFO  [com.thorben.janssen.TestBlazeCriteria] - Anish Giri
10:52:52,113 INFO  [com.thorben.janssen.TestBlazeCriteria] - Fabiano Caruana

When processing that query, the database has to order the result set first. It then iterates through the result set and skips the number of records defined as the OFFSET. It then returns the following records until it reaches the defined number of rows. The downside of this approach is that the database always has to read and skip the number of records defined by the OFFSET. Due to that, the query’s performance degrades based on the configured OFFSET.

Applying keyset pagination

Keyset pagination provides better performance for huge OFFSET values. Instead of telling the database to skip a defined number of records, it excludes them from the query result. This requires a unique order of your result set and a WHERE clause that filters all records you would otherwise have to skip.

If you’re unfamiliar with keyset pagination, I recommend reading this article by Markus Wienand. He explains in great detail how keyset pagination works and why it’s better than offset pagination.

Using Blaze Persistence, you can call the page method to apply keyset pagination. It expects the previous keyset page and the firstResult and maxResult values you already know from offset pagination. If the previous keyset page is null, Blaze Persistence uses these values to apply offset pagination. Otherwise, it uses the lower or upper bound of the previous page and the maxResults value to get the next or previous page.

I use that in the following example to iterate through the list of ChessPlayer entities in pages of 2. As I mentioned earlier, keyset pagination requires a unique order of the query result. I, therefore, called the orderByAsc method to get the ChessPlayers in the ascending order of their id attribute.

EntityManager em = emf.createEntityManager();
em.getTransaction().begin();

PagedList<ChessPlayer> players1 = cbf.create(em, ChessPlayer.class)
									 .orderByAsc("id")
									 .page(null, 0, 2)
									 .getResultList();

players1.forEach(p -> log.info(p.getFirstName() + " " + p.getLastName()));

PagedList<ChessPlayer> players2 = cbf.create(em, ChessPlayer.class)
									 .orderByAsc("id")
									 .page(players1.getKeysetPage(), 2, 2)
									 .getResultList();

players2.forEach(p -> log.info(p.getFirstName() + " " + p.getLastName()));

em.getTransaction().commit();
em.close();

When you execute this test case, you can see in the log output that the 1st query used standard offset pagination. The 2nd one used the order criteria to exclude all records that we would otherwise have to skip and then used offset pagination with an offset of 0. So, it returns the first 2 and avoids skipping any records.

11:31:07,257 DEBUG [org.hibernate.SQL] - 
    select
        c1_0.id,
        c1_0.birthDate,
        c1_0.firstName,
        c1_0.lastName,
        c1_0.version,
        (select
            count(*) 
        from
            ChessPlayer c2_0) 
    from
        ChessPlayer c1_0 
    order by
        c1_0.id asc offset ? rows fetch first ? rows only
11:31:07,259 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [1] as [INTEGER] - [0]
11:31:07,260 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [2] as [INTEGER] - [2]
11:31:07,288 INFO  [com.thorben.janssen.TestBlazeCriteria] - Magnus Carlsen
11:31:07,288 INFO  [com.thorben.janssen.TestBlazeCriteria] - Jorden van Foreest
11:31:07,508 DEBUG [org.hibernate.SQL] - 
    select
        c1_0.id,
        c1_0.birthDate,
        c1_0.firstName,
        c1_0.lastName,
        c1_0.version,
        (select
            count(*) 
        from
            ChessPlayer c2_0) 
    from
        ChessPlayer c1_0 
    where
        (
            ?
        ) < (
            c1_0.id
        ) 
        and 0=0 
    order by
        c1_0.id asc offset ? rows fetch first ? rows only
11:31:07,509 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [1] as [BIGINT] - [2]
11:31:07,509 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [2] as [INTEGER] - [0]
11:31:07,509 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [3] as [INTEGER] - [2]
11:31:07,513 INFO  [com.thorben.janssen.TestBlazeCriteria] - Anish Giri
11:31:07,513 INFO  [com.thorben.janssen.TestBlazeCriteria] - Fabiano Caruana

Using this approach, it doesn’t matter which part of the query result you try to fetch. The database never has to skip any records, which can drastically improve the query’s performance.

Conclusion

Blaze Persistence provides a Criteria API that enables you to define your query dynamically at runtime and is easier to read and write than JPA’s Criteria API. It also extends the query capabilities provided by Hibernate and JPA. We will take a closer look at that in future articles.

Blaze Persistence uses many default values that simplify the definition of a query and reduce the required boilerplate code. Good examples are the standard FROM and SELECT clauses that use the entity reference you provided when creating the query. You can, of course, override these defaults if they don’t match the requirements of your use case. This gives you full flexibility to define the queries you need for your use case.

Related Articles

Responses

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.