Dev Learning Lab

The labs on this site need JavaScript to run. The same content is written out below.

Learn › JPA › Queries

JPQL and fetch joins

What is the difference between join and join fetch?

JPQL is a query language written against entities, not tables. When you load posts together with their comments, a plain join and a join fetch differ in which columns are read from the database and in which queries are sent afterward.

Checked against real execution · 12 combinations were run on Hibernate 7.4.5 and H2, and the result list, the number of rows returned, the position of the row limit in the SQL, the follow-up queries, and the warnings and exceptions were checked against the results (the behavior of older versions follows the documentation)

Concepts in this lab

JPQL
The JPA query language that you write against entities and their fields, not tables. Its syntax is similar to SQL, and when a query runs, the JPA provider translates it into SQL for the database in use. Its advantage is that it does not depend on the syntax of a specific database.
Fetch join
A JPQL feature that loads an entity and its associated entities together in one go with a SQL join. Even an association configured for lazy fetching is loaded immediately in this query, which solves the N+1 selects problem. Be careful: if you load several collections together, the number of result rows multiplies.
Collection fetch joins and pagination
The problem that arises when you use a fetch join over a collection together with pagination (setFirstResult and setMaxResults). If you fetch join a one-to-many collection, the result rows of the SQL multiply by the number of children, so a database row limit (limit) applied as is cannot cut by parent. So older versions of Hibernate did not put a row limit in the SQL in this case. They read all the matching rows, cut them in memory, and wrote the warning HHH90003004 to the log. If there is a lot of data, all of it is loaded into memory, which is dangerous. Hibernate 7.4.5 builds a subquery that cuts the parents first and handles this in the database, but there are still cases where it cuts in memory, such as when the sort key is on the collection side. Behavior differs by version, so check the SQL that is actually sent. Fetching the collection separately with lazy fetching and a batch size setting is the safer way.
MultipleBagFetchException
The exception that Hibernate throws when you try to fetch join two or more collections declared as List in one query. Hibernate calls a List without an order column a bag. If you join two collections with one SQL statement, the rows multiply to the product of the sizes of the two collections and you cannot tell which rows are duplicates, so Hibernate refuses before running. If you change one side to a Set, the exception goes away, but the problem of rows multiplying remains. The usual solution is to fetch join only one collection and fetch the rest separately with a batch size setting.
Cartesian product
The result of producing every combination of two sets. In SQL, if you join two collections that belong to one entity at the same time, the number of result rows grows to the product of the sizes of the two collections. Unnecessarily large amounts of data are transferred and take up memory, so avoid loading several collections in a single join.

Try these

Running with the default settings

  1. em.createQuery(…): JPQL is written against entities, not tables. Post in from Post p is the entity name (the class name unless you set another one), and p.comments is a field name of Post. Hibernate translates this into SQL that queries the posts and comments tables.
  2. getResultList(): The SQL is sent. It joins the comments table, but the select clause has only the post columns. The comments are used only in the join condition. Post 1 has 3 comments, so it takes 3 rows, and post 2 takes 2 rows, so the database returns 5 rows in all.
  3. 5 rows are converted to Post objects. The same post appears in several rows, but Hibernate holds it only once, so the result list has 2 posts. The comments list is still empty (not loaded) because the comment columns were not read.
  4. Loop, post 1: The moment p.getComments().size() is called, a separate comment query is sent to fill in the empty list.
  5. Loop, post 2: The moment p.getComments().size() is called, a separate comment query is sent to fill in the empty list.

Result of this run

Code

@Entity
@Table(name = "posts")
class Post {
    @Id Long id;
    String title;

    @OneToMany(mappedBy = "post")
    List<Comment> comments = new ArrayList<>();

    @OneToMany(mappedBy = "post")
    List<Attachment> files = new ArrayList<>();     // attachments
}

@Entity
@Table(name = "comments")
class Comment {
    @Id Long id;
    String text;

    @ManyToOne(fetch = FetchType.LAZY)
    Post post;
}

// DB: post 1 "Notice" has 3 comments, post 2 "Question" has 2 comments
List<Post> posts = em.createQuery(
        "select p from Post p join p.comments c order by p.id",
        Post.class)
    .getResultList();

for (Post p : posts) {
    p.getComments().size();        // use the comments of each post
}

Assumptions of this model

With 3 comments on post 1 and 2 comments on post 2, all 12 combinations (6 JPQL queries, each with and without pagination) were run on real Hibernate 7.4.5 and an H2 database, and the size and order of the result list, the number of rows the database returned, whether the SQL contains the comment columns and a row limit and where, the number of follow-up queries, and the warnings and exceptions were confirmed to match the screen. The SQL on the screen is shortened to be easy to read. The row limit is written as limit 1, but the statement actually sent on H2 was offset ? rows fetch first ? rows only, and it differs by database. The result for this topic differs by version. Cutting in the database with a subquery when you add pagination to a fetch join, and holding the same entity only once in the result of a plain join, are results of running 7.4.5. The statements "older versions always cut in memory" and "up to Hibernate 5, distinct was needed" follow the documentation, and they were not verified by execution because no older version is installed on the PC used to write this lab. The comparison was done with the application freshly started for each combination (a new SessionFactory was created). This is because, in the same environment, when the same fetch join JPQL was first run without pagination and then run with pagination in the same SessionFactory, the SQL without a row limit was reused, and the whole result came back. Also, when a where condition was placed on the fetch-joined comments and pagination was applied, an SQL syntax error occurred in this environment. Code that uses a collection fetch join together with pagination needs its actual SQL checked on the version you use. A plain join and a join fetch are inner joins, so posts without comments drop out of the result, but the data of this lab has no such post.

Previous lab: N+1 · Next lab: Bulk update and delete operations

Concepts in this lab

What this lab shows

Settings

Code


      
    


    

      
Diagram

      

      

    


    

      

      

      

    


  


  

    

      
Run comparison

      

      

    

    

      
Try these