Is your query too complex for JPA and Hibernate?

---

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Join the Persistence Hub!

---

A while ago, I wrote a post explaining how you can check if Hibernate is a good fit for your new project. One of the key questions you have to ask yourself is if you need to perform a lot of complex queries because JPQL supports only a subset of SQL. SQL is, therefore, often a better fit for complex queries.

This statement caused some confusion and people started to ask which queries I consider as complex.

That is not a question I can answer in a few sentences, so I decided to write a post that shows some things you can do with JPQL and some that you can’t. I hope it helps you to determine if your queries might be too complex for JPQL. And keep in mind, you can still use native queries to perform plain SQL statements and use managed entities, POJOs or scalar values as projections.

What you can do with JPQL

JPQL looks similar to SQL, but it supports only a small subset of its features. For most use cases, that’s not a problem. JPQL is powerful enough to implement the most common queries, and if you need more, you can still use a native SQL query.

1. Define the attributes you want to select

Most developers use JPQL to select entities. But that’s not the only projection you can use. You can define a list of entity attributes which you want to select as scalar values.

List<Object[]> authorNames = em.createQuery(“SELECT a.firstName, a.lastName FROM Author a”).getResultList();

You can also use constructor expressions to define a constructor call. The constructor expression has no effect on the generated SQL query. It is the same as if you would select the entity attributes as scalar values. Hibernate just calls the constructor for each row in the result set.

List<BookPublisherValue> bookPublisherValues = em.createQuery(“SELECT new org.thoughts.on.java.model.BookPublisherValue(b.title, b.publisher.name) FROM Book b”,BookPublisherValue.class).getResultList();

2. Join related entities in the FROM clause

You can, of course, also join related entities in JPQL queries. The good thing about it is that you don’t need to remember how to join the particular table on a database level. You can simply use the path operator “.” to navigate the defined relationship. Your JPA implementation uses the entity mapping to get the required information when it generates the SQL statement.

You can use that in 2 different ways. You can either create an implicit join by using the path operator in your SELECT, WHERE, GROUP BY, HAVING or ORDER clause:

em.createQuery(“SELECT b.title, b.publisher.name FROM Book b”).getResultList();

or you define an explicit join in the FROM clause:

em.createQuery(“SELECT b.title, p.name FROM Book b JOIN b.publisher p”).getResultList();

I always recommend to define an explicit join in the FROM clause and not to mix the 2 approaches. Some older Hibernate versions generated 2 joins for the same relationship if you used implicit and explicit joins in the same JPQL statement. So better be careful.

3. Join unrelated entities in the FROM clause

Joining of unrelated entities is a Hibernate specific feature that I’m missing in JPA. The JPA specification provides you with 2 options: you either use a cross join to join the entities, or you define the relationship, that you want to use in a JPQL query, on an entity level.

Most often, this is not an issue because you want to define these relationships anyways. But if your domain model contains a relationship that links one entity to hundreds or thousands of other entities, you shouldn’t model it on an entity level. You will not be able to use it without huge performance issues.

But that doesn’t mean that you shouldn’t use this relationship in a query. Databases can handle these relationships very efficiently.

Since Hibernate 5.1, you can also join unrelated entities in a JPQL query. The syntax is very similar to SQL and I explained it in more detail in a previous post.

em.createQuery(“SELECT p.firstName, p.lastName, n.phoneNumber FROM Person p JOIN PhoneBookEntry n ON p.firstName = n.firstName AND p.lastName = n.lastName“).getResultList();

4. Use conditional expressions in the WHERE and HAVING clause

OK, everyone knows that you can do that. So I keep it short: JPQL supports a standard set of conditional expressions in the WHERE and HAVING clauses. You can use them to limit the result set to all Authors with an id equal to the given bind parameter value.

Query q = em.createQuery(“SELECT a FROM Author a WHERE a.id = :id”);

5. Use subqueries in the WHERE and HAVING clause

For some reason, JPQL’s support for subqueries seems to be a lesser known feature. It’s not as powerful as in SQL because it’s limited to the WHERE and HAVING clause, but you can use it at least there.

Query q = em.createQuery(“SELECT a FROM Author a WHERE a.id IN (SELECT s.authorId FROM SpecialAuthors s)”);

6. Group your query results with GROUP BY and apply additional conditional expressions with HAVING

GROUP BY and HAVING are standard clauses in SQL, and that’s the same for JPQL. You can use them to group similar records in your result set and to apply additional conditional expressions on these groups.

em.createQuery(“SELECT a, count(b) FROM Author a JOIN a.books b GROUP BY a”).getResultList();

7. Order the query results with ORDER BY

ORDER BY is another JPQL clause that you know from SQL. You can use it to order the result of a query, and you should, of course, use it instead of ordering the result set in your Java code.

em.createQuery(“SELECT a FROM Author a ORDER BY a.lastName”).getResultList();

8. Limit the number of records in your result set

The implementation of this feature feels a little bit strange if you’re used to the SQL syntax. In SQL, you simply add the limit statement to your query.

SELECT * FROM author LIMIT 10

JPQL doesn’t know the LIMIT keyword. You have to define the maximum number of returned rows on the Query interface and not in the JPQL statement. That has the benefit that you can do that in the same way for JPQL and Criteria API queries.

em.createQuery(“SELECT a FROM Author a”).setMaxResults(10).getResultList();

9. Use standard functions

JPQL also supports a small set of standard functions that you can use in your queries. You can use them to perform simple operations in the database instead of your Java code.

em.createQuery(“SELECT a, count(b) FROM Author a JOIN a.books b GROUP BY a”).getResultList();

10. Use non-standard and database specific functions

SQL supports more functions than JPQL and in addition to that, most databases provide a huge set of proprietary functions. Hibernate’s database-specific dialects offer proprietary support for some of these functions and since JPA 2.1 you can call all functions supported by your database with a call of the function function.

em.createQuery(“SELECT a FROM Author a WHERE a.id = function(‘calculate’, 1, 2)“, Author.class).getSingleResult();

11. Call stored procedures

JPA 2.1 also introduced the @NamedStoredProcedureQuery and the dynamic StoredProcedureQuery to provide basic support for stored procedure calls.

The following code snippet shows the annotation based definition of a stored procedure call. The name of the named query is getReviews and it calls the stored procedure get_reviews with a REF_CURSOR and an input parameter.

@NamedStoredProcedureQuery(
name = “getReviews”,
procedureName = “get_reviews”,
resultClasses = Review.class,
parameters = {
	@StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, type = void.class),
	@StoredProcedureParameter(mode = ParameterMode.IN, type = Long.class)
}
)

You can use the @NamedStoredProcedureQuery like any other named query, as soon as you’ve defined it.

StoredProcedureQuery q = this.em.createNamedStoredProcedureQuery(“getReviews”);
q.setParameter(2, b.getId());
List<Review> reviews = q.getResultList();

The StoredProcedureQuery uses a Java API to define the stored procedure call. This allows you to adapt the stored procedure call at runtime.

StoredProcedureQuery query = this.em.createStoredProcedureQuery(“calculate”);
query.registerStoredProcedureParameter(“x”, Double.class, ParameterMode.IN);
query.registerStoredProcedureParameter(“y”, Double.class, ParameterMode.IN);
query.registerStoredProcedureParameter(“sum”, Double.class, ParameterMode.OUT);

You can then use it in the same way as other JPQL queries.

// set input parameter
query.setParameter(“x”, 1.23d);
query.setParameter(“y”, 4.56d);

// call the stored procedure and get the result
query.execute();
Double sum = (Double) query.getOutputParameterValue(“sum”);

What you can’t do with JPQL

As you’ve seen, JPQL supports a set of features that allows you to create queries up to a certain complexity. In my experience, these queries are good enough for most use cases. But if you want to implement reporting queries or have to rely on database-specific features, you will miss a lot of advanced SQL features. Here are a few of them that I miss on a regular basis, and that require me to use native queries.

I don’t explain all the advanced SQL features in detail, but I link to some experts who know SQL a lot better than I do.

1. Use subqueries outside of WHERE and HAVING clauses

That’s the only features I often miss in JPQL and something that’s annoying me for quite a while. With JPQL, you can use subqueries only in the WHERE and HAVING clauses but not in the SELECT and FROM clause.

SQL, of course, allows you to use subqueries also in the SELECT and FROM clause. In my experience, this is nothing you need on a daily basis, but I think I use it a few times in all of my projects.

2. Perform set operations

UNION, INTERSECT, and EXCEPT allow you to perform standard set operations on the result sets of independent queries. Lukas Eder explains them in detail in his blog post: You Probably don’t Use SQL INTERSECT or EXCEPT Often Enough.

3. Use database specific hints

Most databases support proprietary query hints that allow you to provide additional information about your query. For some queries, the right set of hints can have a huge performance impact. You can learn more about hints in Markus Winand’s post: About Optimizer Hints.

4. Write recursive queries

Recursive queries are another nice SQL feature that allows you to traverse a graph of related database records.

5. Use window functions

If you don’t know about window functions in SQL, you have to watch one of Lukas Eder’s SQL talks or read some of his posts on the jOOQ blog. As soon as you understand this nifty SQL feature, you can do amazing things, like running total calculations or analyzing data series, with a relatively simple SQL statement.

Conclusion

As you’ve seen, SQL supports more features than JPQL and allows you to perform more advanced operations within your query. You definitely need these kinds of features, if you want to implement reporting or data mining use cases.

JPQL, on the other hand, provides a smaller set of features that allow you to define which entity attributes you want to select, to join multiple entities, to group them and to apply different kinds of conditional expressions. In my experience, these features are good enough for most applications that focus on managing a set of data instead of analyzing it.