Blaze Persistence - Criteria API for JPA backends

You can view the Blaze Persistence core module as being a builder for query objects similar to the JPA Criteria API. The builder generally tries to check correctness as early as possible, but defers some checks to query generation time which allows to write query building code that looks almost like JPQL.

Behind the scenes Blaze Persistence core generates a JPQL query or a provider native query string. When advanced features like e.g. CTEs are used, the query string represents the logical query structure and looks very much like a possible future revision of JPQL.

The developers of Blaze Persistence see entity views as a better alternative to JPA 2.1 entity graphs which is why there is no special support for entity graphs. Nevertheless, using entity graph with queries produced by Blaze Persistence shouldn’t be a problem as long as no advanced features are used and can be applied as usual via query hints. Also note that entity graphs require a JPA 2.1 implementation whereas entity views also work with a provider that only implements JPA 2.0.

Every release comes with a distribution bundle named like blaze-persistence-dist-VERSION. This distribution contains the required artifacts for the Blaze Persistence core module as well as artifacts for integrations and other modules.

The required artifacts are always necessary. Every other module builds up on that. Based on the JPA provider that is used, one of the integrations should be used. Other modules are optional and normally don’t have dependencies on each other.

Blaze Persistence is usable in Java EE, Spring as well as in Java SE environments.

All projects are built for Java 7 except for the ones where dependencies already use Java 8 like e.g. Hibernate 5.2, Spring Data 2.0 etc. So you are going to need at least JDK 8 for building the project.

We also support building the project with JDK 9 and try to keep up with newer versions. Currently, we support building the project with Java 8 - 14. If you want to run your application on a Java 9+ JVM you need to handle the fact that JDK 9+ doesn’t export some APIs like the JAXB, JAF, javax.annotations and JTA anymore. In fact, JDK 11 removed these modules so command line flags that are sometimes advised to add modules to the classpath won’t work.

Since libraries like Hibernate and others require these APIs you need to make them available. The easiest way to get these APIs back on the classpath is to package them along with your application. This will also work when running on Java 8. We suggest you add the following dependencies.

Automatic module names for modules.

The bare minimum is JPA 2.0. If you want to use the JPA Criteria API module, you will also have to add the JPA 2 compatibility module. Generally, we support the usage in Java EE 6+ or Spring 4+ applications.

The following table outlines the supported library versions for the integrations.

This section is supposed to give you a first feeling of how to use the criteria builder. For more detailed information, please see the subsequent chapters.

Let’s start with the simplest query possible:

This query simply selects all Cat objects and is equivalent to following JPQL query:

Once the create() method is called the expression returns a CriteriaBuilder<T> where T is specified via the second parameter of the create() method and denotes the result type of the query. The default behavior of create() is that the result type is assumed to be the entity class from which to select. So if we would like to only select the cats' age we would have to write:

Here we can see that the criteria builder assigns a default alias (the simple lower-case name of the entity class) to the entity class from which we select (root entity) if we do not specify one. If we want to save some writing, both the create() and the from() method allow the specification of a custom alias for the root entity:

Next we want to build a more complicated query. Let’s select all cats with an age between 5 and 10 years and with at least two kittens. Additionally, we would like to order the results by name ascending and by id in case of equal names.

We have built a couple of queries so far but how can we retrieve the results? There are two possible ways:

If you want to go into more detail, you are now ready to discover the other chapters of the documentation or the API yourself.

A quick overview that presents the interfaces that are essential for users and how they are related.

Every query builder has several clause specific managers that it delegates to. These managers contain the state for a clause and might interact with other clauses. Depending on which query builder features are used, the query object that is produced by a query builder through getTypeQuery() or getQuery() is either the JPA provider’s native query or a custom query.

If no advanced features are used, nothing special happens. The query string is built and passed to EntityManager.createQuery() which is then returned. When advanced features are used, an example query is built which most of the time is very similar to the original query except for advanced features. This example query serves as a basis for execution of advanced SQL. It almost contains all the necessary parts, there is just some SQL that needs to be replaced.

If CTEs are involved, one query per CTE is built via the same mechanism and added to the participating queries list. This list is ordered and contains all query parts that are involved in an advanced query. The ordering is important because in the end, parameters are positionally set in SQL and the order within the list represents the order of the query parts in the SQL. All these query objects are then passed to a QuerySpecification which is capable of producing the SQL for the whole query from it’s query parts. It serves as component that can be composed into a bigger query but also provides a method for creating a SelectQueryPlan or ModificationQueryPlan. Such query plans represent the executable form of query specifications that are fixed. The reason for the separation between the two is that list parameters or calls to setFirstResult() and setMaxResults() could change the SQL.

The query specification is wrapped in an implementation of the JPA query interfaces javax.persistence.Query or javax.persistence.TypedQuery and a query plan is only created on demand just before executing. Parameters, lock modes and flush modes are propagated to all necessary participating queries.

Set operations on top level queries essentially are special query specifications that contain multiple other query specifications.

To really execute such advanced queries, query plans use the ExtendedQuerySupport. It offers methods to run an JPA query with an SQL replacement and a list of participating queries. The ExtendedQuerySupport is JPA provider specific and is responsible for proper query caching and giving access to SQL specifics of JPA query objects.

The integration of ObjectBuilder is done by introducing a query wrapper that takes results, passes them through the object builder and then returns the results.

The essential integration points with the JPA provider are encapsulated in EntityManagerFactoryIntegrator and ExtendedQuerySupport.

The EntityManagerFactoryIntegrator offers support for DBMS detection, function registration and the construction of a JpaProvider through a JpaProviderFactory. The JpaProvider is a contract that can be used to query JPA provider specifics. Some of those specifics are whether a feature like entity joins is supported but also metamodel specifics like whether an attribute has a join table.

The ExtendedQuerySupport is necessary for advanced SQL related functionality and might not be available for a JPA provider. It provides access to SQL related information like the column names of an entity attribute or simply the SQL query for a JPA query.

JPQL offers support for CROSS, INNER, LEFT and RIGHT JOIN which are all well supported by Blaze Persistence. In contrast to JPQL, Blaze Persistence also has a notion of implicit/default and explicit joins which makes it very convenient to write queries as can be seen a few sections later.

The ON clause is a filter predicate similar to the WHERE clause, but is evaluated while joining to restrict the joined elements. In case of INNER joins the ON clause has the same effect as when putting the predicate into the WHERE clause. However LEFT joins won’t filter out objects from the source even if the predicate doesn’t match any joinable object, but instead will produce a NULL value for the joined element.

The ON clause is used when using array joins to restrict the key of a join to the index expression.

The ON clause can be constructed by setting a JPQL predicate expression with setOnExpression() or by using the Predicate Builder API.

The resulting JPQL looks as expected

The VALUES clause is similar to the SQL VALUES clause in the sense that it allows to define a temporary set of objects for querying. There are 3 different types of values for which a VALUES clause can be created

For query caching reasons, a VALUES clause has a fixed number of elements. If you bind a collection that has a smaller size, behind the scenes the rest is filled up with NULL values which are filtered out by a WHERE clause automatically. Trying to bind a collection with a larger size will lead to an exception at bind time.

The VALUES clause is a feature that can be used for doing efficient batching. The number of elements can serve as batch size. Processing a collection iteratively and binding subsets to a query efficiently reuses query caches. For one-shot or rarely executed queries it might not be necessary to implement batching. In such cases use one of the overloads that use the collection size as number of elements.

The join alias that must be defined for a VALUES clause is reused as alias for the parameter to bind values.

When using DML in CTEs it depends on the DBMS what state a FROM element might give. Normally this is not problematic as it is rarely necessary to do DML and a SELECT for the same entity in one query. When it is necessary to do that, it is strongly advised to make use of fromOld() or fromNew() to use the state before or after side-effects happen.

For example usage and further information, take a look into the Updatable CTEs chapter

In SQL, a from clause item must be a relation which is usually a table name but can also be a subquery, yet most ORMs do not support that directly. Blaze Persistence implements support for subqueries in the FROM clause by requiring the return type of a subquery to be an entity or CTE type. This is similar to how inlined CTEs work and in fact, under the hood, CTE builders are used to make this feature work. For more information about CTEs, go to the CTE documentation section.

Before a subquery can be constructed, one has to think of an entity or CTE type that represents the result of the subquery. Consider the following CTE entity as an example.

This CTE entity can then be used as result type for a subquery in fromSubquery().

The example doesn’t really make sense, it just tries to show off the possibilities. The JPQL for such a query looks roughly like the following

Using a dedicated entity or CTE class for a subquery result and binding every attribute might make sense for some cases, but most of the time, it is sufficient to re-bind all entity attributes again i.e. the result type matches the query root type Cat. To help with writing such queries, the fromEntitySubquery() method can be used. Databases are pretty good at eliminating unnecessary/unused projections in such scenarios, so it’s no big deal to use this short-cut if applicable.

will result in something like the following

As can be seen, all owned entity attributes of the type Cat are bound again.

Apart from the fromXXX methods there is also support for joining such subqueries via the joinOnSubquery() and joinOnEntitySubquery() methods or the join type specific variants.

Blaze Persistence offers support for doing lateral joins via the methods joinLateralOnSubquery() and joinLateralOnEntitySubquery(). A lateral join, which might also be known as cross apply or outer apply, allows to refer to aliases on the left side of the subquery i.e. the alias of the join base. Such a join is like a correlated subquery for the FROM clause.

The example query shows a special feature of lateral joins, which is the possibility to correlate a collection for a lateral join. This could also have been written by correlating an entity type and defining the correlation for the collection in the WHERE clause manually.

The resulting JPQL might look like the following

Note that lateral joins only work for inner and left joins. Also, not all databases support lateral joins. H2 and HSQL do not support that feature. MySQL only supports this since version 8. Oracle supports this since version 12.

The restriction builder is used to build a predicate for an existing left hand side expression and chains to the right hand side expression. It supports all standard predicates from JPQL and expressions can be of the following types:

Available predicates

The binary predicates EQ, NOT EQ, LT, LE, GT & GE also allow to create case when expressions for the right hand side via a builder API.

Keyset pagination or scrolling/filtering is way to efficiently paginate or scroll through a large data set. The idea of a keyset is, that every tuple can be uniquely identified by that keyset. Pagination only makes sense when the tuples in a data set are ordered and keyset pagination in contrast to offset pagination makes efficient use of the ordering property of the data set. By remembering the highest and lowest keysets of a page, it is possible to query the previous and next pages efficiently.

Apart from the transparent keyset pagination support, it is also possible to implement keyset scrolling/filtering manually. A keyset consists of the values of the ORDER BY expressions of a tuple and the last expression must uniquely identify a tuple. The id of an entity is not only a good candidate in general for the last expression, but also currently the only possible expression to satisfy this constraint.

The following query will order cats by their birthday and second by their id.

When a GROUP BY clause is used, most DBMS require that every non-aggregate expression that appears in the following clauses must also appear in the GROUP BY clause

This is due to the fact that these clauses are logically executed after the GROUP BY clause. Some DBMS even go as far as not allowing expressions of a certain complexity in the GROUP BY clause. For such expressions, the property/column references have to be extracted and put into the GROUP BY clause instead, so that the composite expressions can be built after grouping.

By default, the use of complex expressions is allowed in groupBy(), but can be disabled by turning on the compatible mode.

The HAVING clause is similar to the WHERE clause and most of the inner workings are described in the [Predicate Builder] chapter. The only difference is that the HAVING clause in contrast to the WHERE clause can contain aggregate functions and is logically executed after the GROUP BY clause.

The API for using the HAVING clause is the same as for the WHERE clause, except that it uses having instead of the where prefix.

Distinct can be applied on any query by calling distinct().

In addition to that, all aggregate functions as defined per JPQL support aggregating distinct values.

When selecting multiple expressions, you can decide between the query return types Object[] and javax.persistence.Tuple.

You can define aliases for select items and access the tuple elements by name or by a 0-based index.

The builder API for CASE WHEN in the SELECT clause is the same as for predicate builders except for different entry points. All entry methods take an optional select alias too.

Although there is an API for constructing CASE WHEN expressions, you can also just make use of them in a normal expression of select()

Although the JPA spec does mandate support for subqueries in the SELECT clause, every major JPA provider supports it, which is why Blaze Persistence also allows it. A subquery is the only type of expression that has to be created through the builder API, all other expressions can be created by passing the expression as string.

Like already explained in the beginning, constructor expressions are supported differently in Blaze Persistence. Instead of having to use the fully qualified class name in the query, Blaze Persistence offers a concept called [Object builder]. On top of that API, it implements features similar to the constructor expression of JPQL.

The selectNew(Constructor) variant is probably the one that comes closest to the constructor expression. That method allows to pass in a constructor object which will be used to construct objects when building the result list. The argument types of the constructor must match the types of the select items.

See how the query type changed because of the call to selectNew()? The JPQL contains no information about the constructor, just the scalar selects.

Having to explicitly declare the parameter types for retrieving the constructor is not very convenient. That’s why there is the variant which accepts the Class instead. That way the constructor selection is deferred to the runtime and is done based on the tuple elements types. The selection is done once based on the types of the first tuple.

Not only looks like the one from before, but also does the same. It’s just less code that is required. The only difference is the point in time where errors can happen. By choosing an explicit constructor at query building time, errors like non-accessible or non-existing constructors can show up earlier. By using the Class approach, errors would only show up when processing the query results. This behavior might change in the future i.e. due to improvements we might be able to determine the constructor already at query building time. Such a behavior could then of course be disabled if required.

As mentioned before, the selectNew() approaches with Class and Constructor both build on top of the more general approach of ObjectBuilder. An ObjectBuilder instance can be provided to a CriteriaBuilder and PaginatedCriteriaBuilder via selectNew(ObjectBuilder).

It is responsible for

The following example should illustrate the functionality

Looks like boilerplate for this simple query?

You are right, but keep in mind, this isn’t an API that a simple user should directly implement. This API allows to keep the select item providing and consuming parts together, but decouple it from the actual query. You can have one ObjectBuilder for multiple queries of the same query root.

In real life applications it is often required to have some sort of view model i.e. a model specifically for the UI. Without an API that allows to decouple the projection from the rest, you would

The ObjectBuilder API helps you in all these regards

Although this simple example doesn’t do anything fancy in buildList(), you could do anything in there

The best example for a consumer of this API is the entity-view module which makes use of the ObjectBuilder interface to implement efficient projection.

Apart from Hibernate, all JPA providers have severe limitations regarding the TREAT operator support. The only way to reliably workaround these limitations is to introduce separate joins for the desired subtypes. Blaze Persistence currently does not implement a transparent translation to the described workaround, but may soon do. Also see #123 for more information.

Every join alias and property of an alias can be polymorphic and therefore the TREAT operator can be applied to the expression. Since every FROM element in JPA is polymorphic by default, the TREAT operator merely gives access to the subtype properties. When the operator is used in an expression context like in a select item, the expression will return NULL if the treated element is not of the desired subtype. Similarly the use of the operator in a conditional context like in a WHERE predicate, will make the parent predicate evaluate to false if the treated element is not of the desired subtype.

Consider the following simple model

For simplicity this uses single table inheritance strategy but applies to all strategies. Consider the following test data.

A query for animals and optionally selecting the kittyName would roughly look like this

The resulting query might look like the following, but might differ depending on the actual support of the JPA provider.

The result list will contain 2 tuples.

Querying for a specific name i.e. using the kittyName in the WHERE clause like

will actually filter the result set by adding a type restriction predicate to the parent predicate

The part about the parent predicate is very important. The JPA spec didn’t test for this which is why most JPA implementations got this wrong. When the TREAT operator is for example used within an OR predicate, Blaze Persistence will handle this correctly.

This will correctly render to

which will return as expected 2 tuples, the cat and the dog.

If Blaze Persistence were rendering the TREAT operator through to the JPA provider as is, most JPA implementations will behave as if the following query was written

This will filter out the dog thus resulting in only 1 tuple in the result list which is mostly undesired.

Apart from accessing the properties of subtypes, JPA also specifies the use of the TREAT operator in a join path which allows to restrict the join scope and cast to specific subtypes. A treat join is just like a normal join, except that it additionally uses a predicate like TYPE(alias) = Subtype in the ON clause condition and hints the runtime to restrict the joined tables.

Consider the following simple model

For simplicity this uses single table inheritance strategy but applies to all strategies. Consider the following test data.

A query for cat people would roughly look like this

The resulting query might look like the following, but might differ depending on the actual support of the JPA provider.

The result list will contain 1 tuple, that is the cat person’s name and the name of the cat.

When doing a left treat join, all people are retained.

The resulting query might look like the following, but again might differ depending on the actual support of the JPA provider.

The result list will contain 3 tuples. Note that only the tuple of the cat person P1 will have a non-null name for the favoritePet.

Currently there is no direct support for this type of querying, but this will change soon. Also see #204

Identification variables are aliases of FROM clause elements. Since FROM clause aliases and SELECT aliases have to be unique, you can use SELECT aliases just like FROM clause aliases. The only exception to this are ON clauses of FROM clause elements.

Path expressions use the navigation operator . to navigate to properties of an object. A path expression has the form of identificationVariable.attribute where attribute is the name of an attribute which is part of the type of identificationVariable. Path expressions can also use multiple navigation operators like identificationVariable.association.attribute where association is an object typed attribute. In general, the use of a navigation operator will result in a model aware join of the attributes. In some cases the join will be omitted

The array expression syntax is an extension to the JPQL grammar that allows to refer to a specific element of a collection. Currently this is limited to associations mapped as java.util.Map and indexed java.util.List i.e. lists that use @OrderColumn.

A normal path expression like identificationVariable.collection.name will create an unconditional join for the attribute collection i.e. it refers to all collection elements. An array expression like identificationVariable.collection[:someParam].name on the other hand joins the attribute collection with a ON clause condition KEY(collection) = :someParam if the collection is a java.util.Map and INDEX(collection) = :someParam if it is a java.util.List. So an array expression refers to a single collection element.

Since array expressions by default use the join type LEFT, the expression result is either the value for the specific element or NULL if no collection element for the key or index exists. The array expression syntax can be used anywhere within a path expression. Even multiple uses like this are ok identificationVariable.collection1[:param1].association.collection2[:param2].attribute

Every expression in JPQL has a static type that can be determined through the metamodel. Since associations can refer to polymorphic types, it might be necessary to downcast identification variables or path expressions. JPA 2.1 introduced the concept of a TREAT expression to actually downcast to a specific subtype. Blaze Persistence follows the strict rules of JPQL regarding static type resolving and thus requires the use of TREAT when accessing subtype properties. A TREAT expression can be used in any clause and the result of such an expression is either the casted object or NULL if the object is not an instance of the requested type. If TREAT is used as part of a predicate and an object is not of the requested type, the predicate will evaluate to FALSE.

JPQL has the concept of qualified expressions for collections which is also supported in Blaze Persistence. By default, a join for a collection or an expression using an attribute referring to a collection type, will have the collection value as type. For allowing access to the key of a java.util.Map or the index of an indexed java.util.List, JPQL has a notion of qualification expressions.

Blaze Persistence only supports named parameters i.e. the :parameterName notation. There are multiple reasons for not supporting positional parameters but the main one being, that positional parameters were never needed by the authors of Blaze Persistence in any of their projects. Values can used in a query either through a parameter expression or by rendering it as literal expression. The syntaxes for literals depend on the data type of the value and roughly align with the syntax of EclipseLink and Hibernate.

Next to the normal literals, Blaze Persistence also has support for a NULL literal. Behind the scenes it renders as NULLIF(1,1).

Arithmetic operators (+, -, *, /) are available on numeric types. The type rules follow the JPQL rules which roughly say that if any operand in an arithmetic expression is of type …​ * Double, then the result is of type Double * Float, then the result is of type Float * BigDecimal, then the result is of type BigDecimal * BigInteger, then the result is of type BigInteger * Long, then the result is of type Long

In all other cases, the result is of the type Integer. The only exception to all of these rules is the division operator / for which the result type is undefined.

The operators can’t be used for date arithmetic. Instead the date diff functions have to be used.

Blaze Persistence supports a direct function call syntax FUNCTION_NAME ( (args)* ) for all functions and translates that to the JPA provider specific syntax. Non-standard functions may also use the FUNCTION ( function_name (, args)* ) syntax that got introduced in JPA 2.1 and are handled equally.

Since subqueries aren’t supported to be written as a whole but only through a builder API, Blaze Persistence offers a special API to construct complex expressions that contain subqueries. The API was explained for predicates and select expressions already. The general idea is that you introduce aliases for subqueries in a complex expression that later get replaced with the actual subquery in the expression tree.

Within subqueries, Blaze Persistence supports a function called OUTER() which can be used to refer to attributes of the parent query’s root. By using OUTER you can avoid introducing the query root alias of the outer query into the subquery directly.

For further information on OUTER take a look into the JPQL functions chapter.

Although Blaze Persistence already supports building CASE WHEN expressions via a builder API, it also supports an expression form. The API was explained for predicates and select expressions already.

Blaze Persistence supports constructing predicates via a builder API as has been shown in the [Predicate Builder] chapter, but sometimes it is necessary to define predicates as strings. It is necessary for CASE WHEN expressions or when wanting to replace a whole predicate via e.g. setWhereExpression(String).

Predicates can be connected with the logical operators AND and OR and form a compound predicate. Predicates can be grouped by using parenthesis and can be prefixed with the unary operator NOT for negating the predicate. All predicates except for null-aware predicates like e.g. IS NULL that compare against NULL, will result in UNKNOWN which is intuitively equal to FALSE.

The subquery_alias is replaced with the subquery defined via the builder API as explained in the predicates chapter and the builder section for the IN predicate.

Apart from support for binding parameters on the constructed JPA query, Blaze Persistence also allows to bind parameters on the query builder itself. All query builders(including subquery and other builders) support setting parameters through the same setParameter() API that is offered by the JPA query API. In addition to that, it is also possible to inspect the parameters and the bound values while building through getParameters().

A CriteriaBuilder allows to render and create a count query variant via getCountQuery() which can be used to count the results of a query. This count query is rather simple. Most of the time, it’s just the original query, except that it has just COUNT(*) in the SELECT clause and drops the ORDER BY clause. If a GROUP BY clause is involved, the count query will count the number of distinct groups instead.

Invoking getCountQuery() on a PaginatedCriteriaBuilder will return the count query used for the paginated query which is based on it’s identifier expressions.

On top of that, CriteriaBuilder also offers a getQueryRootCountQuery() which will count the number of query roots similar to what the count query of a PaginatedCriteriaBuilder does. The benefit of this count query is that it will omit joins that are only relevant for the SELECT and ORDER BY clauses.

When using getCountQuery(), this will count the result set size and produce the following JPQL

When using getQueryRootCountQuery(), this will count the number of cats instead, which allows to omit joins and will produce the following JPQL

Note that the use of the HAVING clause is currently unsupported when used with count queries. Also see #616

As mentioned in the configuration chapter, a query builder can be further configured via the setProperty(String, String) method. This allows for disabling optimizations when encountering bugs or fine tuning on a case by case basis.

If you encounter, that you make use of a configuration very often, you should consider configuring the property globally via the CriteriaBuilderConfiguration and only switch to a different value when needed.

Query result caching can be enabled by invoking the setCacheable(boolean) method on a query builder.

Note that additional configuration might be required for the caching to work properly. For details, refer to the documentation of your JPA provider.

Although we try to adopt or at least allow the use of JPA provider extension there are some limitations to the query API that are known and currently not addressed.

In Blaze Persistence we have followed a more involved approach for implementing pagination than plainly using JPA standard methods like javax.persistence.Query.setMaxResults() or javax.persistence.Query.setFirstResult() to steer the result set dimensions. This is due to deficiencies in some JPA providers when it comes to handling paginated queries containing join fetches for collections, but also to support custom pagination that is necessary for reporting queries or entity views.

The approach used in Blaze Persistence consists of up to three queries executed consecutively:

You can disable the count query by passing false to PaginatedCriteriaBuilder.withCountQuery(boolean).

By default, a required ID query is embedded into the object query as subquery if the JPA Provider and DBMS dialect supports that. The inlining can be forced or disabled by passing true or false to PaginatedCriteriaBuilder.withInlineIdQuery(boolean) or globally by configuring the [INLINE_ID_QUERY] property.

By default, the count query is embedded into the ID query or object query as select item if the JPA Provider and DBMS dialect supports that. The inlining can be forced or disabled by passing true or false to PaginatedCriteriaBuilder.withInlineCountQuery(boolean) or globally by configuring the [INLINE_COUNT_QUERY] property.

As already laid out in the introduction, pagination works on an ordered set of elements/rows. Offset pagination basically looks at the ordered set from left to right and counts elements until the count reaches firstResult. From that point on elements are collected until maxResults of elements have been collected or no more elements are available.

This basically means that the OFFSET i.e. firstResult part forces a DBMS to actually determine an element/row is visible for a transaction and then ignore/skip it. The bigger the firstResult value, the more resources the DBMS has to waste for skipping elements/rows. This essentially means that when employing offset pagination, accessing the latter pages will become more and more expensive. In order for this approach to be actually usable with larger data sets, a DBMS index that can be used for the ordering is required to avoid constantly loading and sorting data. In addition to that, the DBMS should have enough RAM to keep the index fully in-memory to avoid costly disk fetches.

Although offset pagination works in every case, it should be avoided if possible because of the performance implications. As you will see in the keyset pagination part there is a more efficient approach to pagination that almost uses the same API.

The following example illustrates the usage and what happens behind the scenes

Executes the following queries

Note that the ID query is necessary because of the join fetched collection kittens

Keyset pagination is a way to efficiently paginate or scroll through a large data set by querying for elements that come before or after a reference point. The idea of a keyset is, that every tuple can be uniquely identified by that keyset. So a keyset essentially is a reference point of a tuple in a data set ordered by keysets. Keyset pagination in contrast to offset pagination makes efficient use of the ordering property of the data set. By remembering the highest and lowest keysets of a page, it is possible to query the previous and next pages efficiently.

A keyset in terms of query results consists of the values of the ORDER BY expressions of a tuple. In order to satisfy the uniqueness constraint, it is generally a good idea to use an entity’s id as last expression in the ORDER BY clause.

Keyset pagination just like offset pagination requires index support on the DBMS side to work efficiently. A range-scan enabled index like provided by a b-tree index is required for keyset pagination to work best. In contrast to offset pagination, an index does not have to be traversed like a list in order to ignore/skip a certain amount of elements/rows. Instead, a DBMS can make use of the structure of the index and traverse it in O(log N) as compared to O(N) to get to the firstResult. This characteristic makes keyset pagination especially useful for accessing latter pages.

One of the obvious requirements for keyset pagination to work, is the need for a reference point i.e. a keyset from which point on the next or previous elements should be queried.

The API in Blaze Persistence tries to allow making use of keyset pagination in a transparent and easy manner without compromises.

Since we are not fetching any collections, the ID query is avoided. For brevity, we skip the count query. So let’s look at the object queries generated

As you can see, nothing fancy, except for the additional select that is used for extracting the keyset.

This time the query made efficient use of the keyset by filtering out elements/rows that come before the reference point

Before the query filtered out elements/rows that came before the reference point, this time it does the opposite. It filters out elements/rows coming after the reference point. Another interesting thing to notice, the ordering was reversed too. This has the effect that the DBMS can traverse the index backwards and essentially is how keyset pagination works. The ordering is reversed again in-memory, so you don’t notice anything of these details.

Note that in the following situations, the implementation automatically falls back to offset pagination

To be able to make use of keyset pagination either via the PaginatedCriteriaBuilder API or the manual keyset filter API, the KeysetPage or the respective Keyset elements have to be preserved across page requests. Applications that can retain state between requests(i.e. via a session) can just preserve the KeysetPage object itself. Applications that try to avoid server side state have to serialize and deserialize the state somehow.

Since the keyset state is available through getter methods, it shouldn’t be too hard to do the serialization and deserialization. When implementing a custom Keyset, the equals() and hashCode() contracts have to make use of just the tuple. A custom KeysetPage implementation has to provide access to the lowest and highest keysets, as well as the firstResult and maxResults values used for querying that page.

Beware that keyset pagination isn’t perfect. If entries can be prepended relative to the current keyset/reference point, it might happen that the page number calculation becomes wrong over time. Most of the time this is negligible as it kind of gives the illusion that the user works on a snapshot of the data.

The navigation to the page on which an entity with a specific id is involves finding out the position of the entity. Blaze Persistence offers a custom function named PAGE_POSITION which determines the absolute position of an entity in an ordered set.

The count query contains the page position determination logic. It essentially passes an ID query as subquery to the PAGE_POSITION function. The concrete SQL implementation of that function depends on the DBMS, but they all follow the same main idea. Wrap the ID query and count the row numbers. In another wrapper around that, filter for the row with the matching id and return the row number as position. The element/row number of the first element on that page is calculated and used as firstResult. Apart from this speciality, the rest of the query is just like a normal offset pagination query.

By default, a query will be paginated by the query root’s id or group by keys, but that might not always be desirable. If the query should rather be paginated based on the identifier of a uniqueness preserving association or unique key rather than primary key or group by keys, the pageBy() variants that accept identifier expressions can be used.

This will paginate based on the identifier of the one-to-one association instead, which is considered uniqueness preserving.

Apart from paginating object graphs it is also possible to paginate aggregate results via this API. The use of groupBy() or an aggregate function will make it necessary to render a GROUP BY clause.

It doesn’t matter if grouping is done explicitly or implicitly, pagination will always be done based on the GROUP BY clause if available, unless a custom identifier expression is specified. This means that the count query, will count the number distinct groups. The id query will select the distinct groups and the object query will finally do the aggregation based on a filter on the groups.

Note that we didn’t specify a groupBy("c.name") because it can be implicitly determined. Also note that using just the name in the orderByAsc is not a violation of the uniqueness properties. This is because through implicit group by collection, we know that name is going to be part of the GROUP BY clause and when a tuple of expressions is contained in the group by, it is considered unique.

Since we don’t fetch collections, there is no need for an id query.

There are certain cases when the PaginatedCriteriaBuilder API is not a perfect fit. It might be desirable to reuse the ids of a page in multiple contexts or in a subquery or CTE. Blaze Persistence provides some createPageIdQuery() method variants for this purpose that are analogous to the various pageBy method variants. The method creates a new CriteriaBuilder that represents the id query that would normally be executed when using the PaginatedCriteriaBuilder API.

Imagine you take an existing query

and turn that into an id query

executing this query would produce the expected id query

and when embedding this query into e.g. a subquery like

this will execute like

Since the PaginatedCriteriaBuilder API pagination produces inherently distinct results the use of distinct() on a PaginatedCriteriaBuilder is disallowed and will result in an exception. Also note that there is currently no support for the HAVING clause along with a PaginatedCriteriaBuilder. Also see #616

If these limitations are not ok for your use case, you will have to implement a custom pagination strategy via setFirstResult() and setMaxResults().

The DELETE statement deletes entities that satisfy the WHERE clause of the statement. A delete builder can be created via CriteriaBuilderFactory.delete().

You can immediately execute the query by calling executeUpdate() or create a JPA Query by calling getQuery().

The DELETE collection statement deletes collection entries of entities that satisfy the WHERE clause of the statement. A collection delete builder can be created via CriteriaBuilderFactory.deleteCollection(). The statement only works on collections that have a join or collection table.

The alias cat, or in general a delete collection statement alias, only allows access to the join or collection table related attributes. Generally, these attributes are

You can immediately execute the query by calling executeUpdate() or create a JPA Query by calling getQuery().

The UPDATE statement updates attributes as specified in the SET clause on entities that satisfy the WHERE clause of the statement. An update builder can be created via CriteriaBuilderFactory.update().

There are multiple set() variants to be able to cover all possible expressions.

You can immediately execute the query by calling executeUpdate() or create a JPA Query by calling getQuery().

The UPDATE collection statement updates attributes as specified in the SET clause on entries of a collection of entities that satisfy the WHERE clause of the statement. A collection update builder can be created via CriteriaBuilderFactory.updateCollection(). The statement only works on collections that have a join or collection table.

You can only set attributes of the join or collection table, which generally are

The logical SQL for this re-parenting query looks like

This will cause all kittens that previously were associated with the cat with id 1 now to be the kittens of the cat with id 2.

The INSERT-SELECT statement allows to create new entities based on result of a SELECT query. An insert builder can be created via CriteriaBuilderFactory.insert().

Let’s consider a simple entity class for INSERT statement examples

The bind() method allows to bind any select expression to an attribute of the Pet entity. You can also bind values directly with bind(String, Object) if you want.

As you can see, we didn’t specify the id attribute. This is because it’s value is going to be generated by the database.

The INSERT-SELECT collection statement inserts new collection entries. A collection insert builder can be created via CriteriaBuilderFactory.insertCollection(). The statement only works on collections that have a join or collection table.

You can only bind attributes of the join or collection table, which generally are

The logical SQL for this kittens copying query looks like

This will copy all kittens that are associated with the cat with id 1 to the kittens of the cat with id 2.

If the underlying DBMS does not support the use of CTEs on modification statements, the CTE’s are inlined into the query. You can make use of those by defining them via with(). For further information on this, check out the CTE chapter.

The RETURNING clause allows to return values to the client based on the modified entities of a DML statement. Every DML statement can return any attributes of the entities that the statement modified.

All query builders for DML statements provide getWithReturningQuery() variants for creating a JPA TypedQuery from specifiable attributes which should be returned for modified entities.

The TypedQuery instance always returns a single ReturningResult. Calling getResultList() will just wrap the result of getSingleResult() in a list.

A ReturningResult basically gives you access to the update count via getUpdateCount() and a result list, representing the attributes of modified entities that were requested to be returned, via getResultList(). In addition to that, it also offers access to the last returned result via getLastResult(), but this might get removed in the future.

The following examples will show how the different executeWithReturning() variants can be used.

Although it might be possible to retrieve other columns based on identifiers for DBMS that don’t support RETURNING all columns natively, there is no emulation implemented yet.

Currently the CTE support is only implemented for Hibernate because of the deep integration that is needed for it to work. The integration with the persistence provider happens in the implementation of com.blazebit.persistence.spi.ExtendedQuerySupport which tries to focus on the minimal necessary methods needed for the integration to work.

In case of Hibernate, a CTE entity class is treated as if org.hibernate.annotations.Subselect was annotated. Hibernate generally generates from clause elements of the form ( select * from MyCTE ) for Subselect entities which we replace simply by the name of the CTE.

The current implementation extracts the SQL from various template JPQL queries that are created behind the scenes. After applying different transformations on the SQL and merging it together to a single SQL query, the new SQL replaces is used in a special JPQL query object. The special JPQL query object is what you can finally execute. It will make use of the SQL query that was created before.

Every CTE will result in a separate JPQL query behind the scenes from which the SQL is extracted. The SQL from the main query, together with the SQLs from the CTEs are put together to form the full SQL query. It is that SQL, the special JPQL query receives for later execution.

CTEs not only provide a way to extract subqueries or use subqueries in the FROM clause, but also to implement recursive queries.

A recursive CTE is normally composed of two parts, a base query(non-recursive query) and a recursive query connected with the SET operator UNION or UNION ALL. The recursive part is allowed to refer to the CTE itself.

A recursive CTE is normally evaluated in iterations

The following illustrates how the ancestor hierarchy of an entity can be retrieved.

This will return all the ancestors of the Cat with an id equal to someCatId.

An updatable CTE is like a normal CTE, but the data comes from returned attributes of a DML statement.

You can start an updatable CTE with the withReturning() method and subsequently decide the DML statement type. The query builder for the DML statement provides a returning() method for binding attributes of the DML statement to a CTE attribute.

The query deletes cats with a NULL name. For every deleted cat, a temporary MyCte entity with the cat’s id bound is created. Finally the deleted cats are queried through MyCte.

As you can imagine, this can be used to define very efficient data pipelines.

One problem with updatable CTEs that might come up is, that you might want to query an entity in one CTE, while also wanting to do a modification in a different CTE. Since the visibility of changes that are done in updatable CTEs might differ from one to another DBMS, Blaze Persistence offers a way to resolve this special case.

Let’s consider the following example:

Although the CTE MyCte is never used, it is still executed. Depending on the DBMS you are on, the SELECT statement will return the state before or after the DELETE statement happened.

In order to make the SELECT statement portable, Blaze Persistence provides a way to qualify a FROM clause element to use the old or new state i.e. before or after modifications happened.

The fromOld() method qualifies the FROM element in the query as old. In the same way does fromNew() qualify the FROM element as new.

In general, we advise you to rethink how you do the querying when having a need for this feature. It should only be used as a last resort.

If a DBMS does not support CTEs natively, the queries are inlined as subqueries in the FROM clause. Note that recursive CTEs can’t be emulated. CTEs are well tested with PostgreSQL, DB2, Oracle and Microsoft SQL Server. Many of the basic features work with H2, but beware that H2 support for CTEs is still experimental.

CTEs in DML are uses of a CTE where the top level statement is a DML. In contrast, Updatable CTEs are CTEs that contain DML and get their values from a RETURNING clause of the DML.

A set operation ends the source query builder and starts a new query builder. This new builder then has to be explicitly ended.

The call to unionAll() ends the previous query builder making any operations on it fail with an exception. Finally endSet() ends the last query builder.

You can chain as many queries with set operations with the following methods

All operations have the same precedence i.e. they are executed from left to right. The only way to order the operations is by grouping them with parenthesis as shown in the next chapter.

You can also make use of set operations in CTEs like the following example shows.

The API is the same, and produces the expected query.

Finally, there is also support for set operations within subqueries.

As you can see, this is rendered differently. It makes use of custom JPQL functions that could even be directly executed by the JPA provider. This is possible because Blaze Persistence registers the JPQL functions for the entire persistence unit. These functions produce the necessary SQL in-place which is more efficient than a complete SQL replacement. The following set operation functions are registered by default:

In order to support grouping of set operations, Blaze Persistence has a special API for grouping the left and right hand sides of set operations. Normally in SQL, the grouping can be achieved by using parenthesis which you can see in the logical query. Unfortunately it is not so easy to provide support for such a grouping in a builder API which is why there are special methods for starting and connecting such a group with set operations. Applying a set operation on such a parenthesis is possible with one of the startXXX() methods:

You can imagine any startXXX() being the opening parenthesis that must be ended with a endSet() representing the closing parenthesis. Since you could apply other set operations on that group, you are required to signal that you are done with the builder by calling endSet().

Similarly you can also have a left nested group for set operations.

The left nesting is started by startSet() which more or less represents the open parenthesis. The parenthesis is then closed by calling endSet().

At the beginning of every nesting group, you can start as many left nestings as you want by calling startSet() and doing so intuitively always results in an open parenthesis that has to be closed by a endSet().

As a convenience, Blaze Persistence allows to have empty nested set operation groups like the following.

Contrary to what you might think, this is allowed and results in the following query.

This is done to make it possible to pass the result of startSet() to consumers which may or may not add set operands.

Since set operations might change the order of elements in the overall result, they also allow to define an ORDER BY clause for the result of a set operation group.

The order by elements are resolved against the first set operand. This means that you can only order by select aliases of the first query in the set operation. If the order by element does not refer to a select alias, it is implicitly resolved against the query root like in the following example.

Apart from the ordering by name, this query will also skip the first element and limit the elements to be returned to one.

Ordering and limiting is also possible for nested set operation groups and can be realized by invoking the endSetWith() operation. Calling endSetWith() is necessary to end the current query builder i.e. switch the context to the whole set operation group. After applying ordering and limiting the set operation group has to be closed with endSet().

Currently there is no emulation implemented for databases that do not support set operations natively. One type of emulation that is implemented however is for the non-distinct variants INTERSECT ALL and EXCEPT ALL in case the distinct variant is supported. The emulation for the non-distinct variants is implemented by adding the ROW_NUMBER to an operand which is removed afterwards.

The DBMS support for set operations is quite good.

Except for H2 the operations can also be used in almost any context.

These functions have a deeper integration with the query building process and do not directly generate SQL.

These functions are provided by Blaze Persistence and are registered by default in every CriteriaBuilderConfiguration. They can be overridden at configuration time if desired.

Every of the following functions has to be invoked with the JPA 2.1 function syntax.

Apart from providing many useful functions out of the box, Blaze Persistence also allows to implement custom JPQL functions that can be called just like any other non-standard function, via the JPA 2.1 function syntax. In addition to that, you can even override existing implementations. So if you need to workaround bugs or want to improve something, you don’t have to wait for a release.

Custom functions are registered via CriteriaBuilderConfiguration.registerFunction() and expect an instance of a JpqlFunctionGroup. A JpqlFunctionGroup is a container for a custom function that defines the function name, whether it is an aggregate function and the DBMS specific implementations of it as instances of JpqlFunction.

When a function is marked as being an aggregate function, it is treated like any other aggregate function regarding implicit group by generation. This means that the occurrence of the function invocation in a query builder, will trigger implicit group by generation.

During the building of a CriteriaBuilderFactory the DBMS specific implementations of the registered JpqlFunctionGroup instances are selected. If there is no DBMS specific one available, it will fallback to the default. If there is no default implementation available, a warning message is emitted, saying that no applicable function was found.

A JpqlMacro is a special kind of function that is evaluated at expression parse time and produces a JPQL expression. Contrary to a JpqlFunction, a macro only needs to provide a render() method and does not suffer of the problems regarding parameters since it produces a JPQL expression rather than SQL.

One of the possible use cases for macros is to have user defined expression expansions to avoid boilerplate. Let’s implement a macro called ITEM_TOTAL.

When passing in an expression like alias, the macro will produce the expression (1 + alias.taxClass.taxValue / 100) * alias.quantity * alias.price. The macro can be either globally registered in the configuration or on a case by case basis directly on the CriteriaBuilder. See the environment section for how to get access to the CriteriaBuilderConfiguration.

Since macros aren’t actually functions and are pretty unique to Blaze Persistence, we decided to allow invoking them directly instead of having to use the JPA 2.1 function syntax.

Let’s see how the macro can be used to easily calculate the total amount of an order.

To wrap it up, macros help to reuse expressions and avoid boilerplate!

Currently there are no built-in JPQL macros available in the core part of Blaze Persistence but only in the entity view module.

Sometimes it might be necessary to adapt the Java type to SQL type mappings for your DBMS. In order to do so, introduce a new method getSqlTypes and extend the dialect like

There are many other customizations possible. Take a look at the DbmsDialect API for more information.