Hibernate Tip: Best Way To Work with Scalar Projections

By Thorben Janssen

Criteria API, Query

Hibernate Tips is a series of posts in which I describe a quick and easy solution for common Hibernate questions. If you have a question for a future Hibernate Tip, please post a comment below.

Question:

On my latest article, Steve Ebersole reminded me of the Tuple interface. And to be honest, I don’t know how I could miss to mention it in that article. The Tuple interface is by far the most comfortable way to work with scalar projections, and I recommend them regularly.

So, in today’s Hibernate Tip, I want to show you the best way to work with a scalar value projection returned by your JPQL or CriteriaQuery.

Solution:

Scalar values can be a good projection if you need to read a few columns that you want to process immediately and you don’t have a matching DTO class for them.

The main problem with this projection is that it’s very uncomfortable to use. By default, a query that uses a scalar projection returns an Object[].

TypedQuery<Object[]> q = em.createQuery("SELECT b.title, b.publisher.name FROM Book b WHERE b.id = :id", Object[].class);
q.setParameter("id", 1L);
Object[] result = q.getSingleResult();

You then need to remember the order and types of the selected database columns and handle the Object[] accordingly.

log.info(result[0] + " was published by " + result[1]);

Tuple interface – The best scalar projection

The Tuple interface provides a better way to work with scalar projections. You still need to remember which entity attributes you selected. But in contrast to an Object[], you can access the fields using an alias or a TupleElement, and you can cast the value automatically.

That’s still not as comfortable as a DTO projection but much better than working with a basic Object[].

Let’s take a look at a JPQL and a CriteriaQuery that return one or more Tuple interfaces.

Retrieving a Tuple interface with JPQL

Creating a JPQL query that returns a Tuple interface is simple. You only need to select one or more entity attributes and add the Tuple.class as the second parameter to the createQuery method.

TypedQuery<Tuple> q = em.createQuery("SELECT b.title as title, b.publisher.name as name FROM Book b WHERE b.id = :id", Tuple.class);
q.setParameter("id", 1L);
Tuple result = q.getSingleResult();

In the next step, you can use the Tuple interface to access your query results.

The most basic way to access the selected fields is to reference them by their index. That’s basically the same approach as you would use if you retrieve the query result as an Object[].

log.info(result.get(0) + " was published by " + result.get(1));

But there is a better way to work with the Tuple interface. One that makes your code easier much easier to read. In the query, I defined an alias for each select entity attribute. You can use these attributes to retrieve the fields from the Tuple interface.

log.info(result.get("title") + " was published by " + result.get("name"));

Retrieving a Tuple interface with a CriteriaQuery

Here you can see a simple CriteriaQuery that selects the firstName and lastName attributes of the Author entity.

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

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Tuple> q = cb.createTupleQuery();
Root<Author> author = q.from(Author.class);
Path<String> firstName = author.get(Author_.firstName);
Path<String> lastName = author.get(Author_.lastName);
q.multiselect(firstName, lastName);

TypedQuery<Tuple> query = em.createQuery(q);
List<Tuple> authorNames = query.getResultList();

To create such a query, you first need to get a CriteriaBuilder instance and call the createTupleQuery() method. This gives you a CriteriaQuery that will return one or more Tuple interfaces.

In the next step, you define your FROM clause and your projection. In this example, I call the multiselect method on the CriteriaQuery with 2 Path objects. You could provide them directly to the multiselect method. But storing them in variables enables you to use them to retrieve the selected values from your Tuple interface.

for (Tuple authorName : authorNames) {
	log.info(authorName.get(firstName) + " " + authorName.get(lastName));
}

Learn more:

If you want to learn more about queries and projections, you should read the following articles:

 

Hibernate Tips Book







Get more recipes like this one in my new book Hibernate Tips: More than 70 solutions to common Hibernate problems.

It gives you more than 70 ready-to-use recipes for topics like basic and advanced mappings, logging, Java 8 support, caching, and statically and dynamically defined queries.

Get it now!



Tags

Criteria API, Query


About the author

Thorben is an independent consultant, international speaker, and trainer specialized in solving Java persistence problems with JPA and Hibernate.
He is also the author of Amazon’s bestselling book Hibernate Tips - More than 70 solutions to common Hibernate problems.

Books and Courses

Coaching and Consulting

Leave a Reply

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

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

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}