Joins, projections and bulk updates
A query does not have to return whole entities. Often you need one field, a number or a short summary for a report. In this lesson JPQL goes through relationships, groups rows and fills a record directly from the query. At the end — queries that change many rows at once.
Joining through a relationship
In JPQL you join not by columns, but by the relationship field:
// src/main/java/com/example/blog/Main.java
List<String> titles = em.createQuery(
"select p.title from Post p join p.tags t where t.name = :tag order by p.id",
String.class)
.setParameter("tag", "hibernate")
.getResultList();
IO.println("Tagged hibernate: " + titles);Hibernate: select p1_0.title from Post p1_0 join post_tag t1_0 on p1_0.id=t1_0.post_id join Tag t1_1 on t1_1.id=t1_0.tag_id where t1_1.name=? order by p1_0.id
Tagged hibernate: [Hello, Hibernate, Lazy loading]join p.tags t is enough: Hibernate knows about the post_tag table from the mapping. Selecting p.title instead of p returns strings, so the result type is String.class. A plain join does not load the relationship into the entity — for that there is join fetch from the N+1 lesson.
A summary into a record
Create a record for the result:
// src/main/java/com/example/blog/AuthorStats.java
package com.example.blog;
public record AuthorStats(String name, long posts, long views) {
}The select new construction calls its constructor for every row:
// src/main/java/com/example/blog/Main.java
List<AuthorStats> stats = em.createQuery("""
select new com.example.blog.AuthorStats(a.name, count(p), sum(p.views))
from Post p join p.author a
group by a.name
order by sum(p.views) desc
""", AuthorStats.class)
.getResultList();
stats.forEach(IO::println);Hibernate: select a1_0.name,count(p1_0.id),sum(p1_0.views) from Post p1_0 join Author a1_0 on a1_0.id=p1_0.author_id group by a1_0.name order by sum(p1_0.views) desc
AuthorStats[name=Anna, posts=1, views=120]
AuthorStats[name=Oleg, posts=1, views=80]
AuthorStats[name=Maria, posts=1, views=45]After new comes the full class name with the package. count and sum of an integer field return Long, so the record components are long. Such objects are not entities: Hibernate does not track them, and they cost less than loading whole posts.
| Function | Result |
|---|---|
count(p) | Number of rows |
sum, avg, min, max | As in SQL |
size(p.comments) | Size of a collection |
upper, lower, concat, length | String functions |
Changing many rows
update and delete queries change rows directly in the database, without loading entities:
// src/main/java/com/example/blog/Bulk.java
emf.runInTransaction(em -> {
Post post = em.find(Post.class, 1L);
int updated = em.createQuery("update Post p set p.views = 0 where p.views < :limit")
.setParameter("limit", 1000)
.executeUpdate();
IO.println("Updated rows: " + updated);
IO.println("Views in memory: " + post.getViews());
em.refresh(post);
IO.println("Views after refresh: " + post.getViews());
});Hibernate: select p1_0.id,p1_0.author_id,p1_0.title,p1_0.views from Post p1_0 where p1_0.id=?
Hibernate: update Post p1_0 set views=0 where p1_0.views<?
Updated rows: 3
Views in memory: 120
Hibernate: select p1_0.id,p1_0.author_id,p1_0.title,p1_0.views,c1_0.post_id,c1_0.id,c1_0.text from Post p1_0 left join Comment c1_0 on p1_0.id=c1_0.post_id where p1_0.id=?
Views after refresh: 0executeUpdate returns the number of changed rows. Such a query needs an active transaction.
Common mistake
Look at the output again: after the bulk update the post loaded earlier still shows 120 views. Bulk queries bypass the persistence context, so objects already in memory keep old values. Run bulk queries before loading entities, or refresh the affected objects with em.refresh, or clear the context with em.clear().