Introduction

In this article, we are going to see how we can load multiple entities by id at once when using JPA and Hibernate.

Loading multiple entities by their identifier is a very common requirement when using JPA and Hibernate. Hence, we are going to see how we can optimize the underlying SQL query execution.

Domain Model

For the following examples, we are going to use the following Book entity:

Now, let’s consider we have added several Book entities into our database:

entityManager.persist(
    new Book()
    .setIsbn("978-9730228236")
    .setTitle("High-Performance Java Persistence")
    .setAuthor("Vlad Mihalcea")
);

entityManager.persist(
    new Book()
    .setIsbn("978-1934356555")
    .setTitle("SQL Antipatterns")
    .setAuthor("Bill Karwin")
);

entityManager.persist(
    new Book()
    .setIsbn("978-3950307825")
    .setTitle("SQL Performance Explained")
    .setAuthor("Markus Winand")
);

entityManager.persist(
    new Book()
    .setIsbn("978-1449373320")
    .setTitle("Designing Data-Intensive Applications")
    .setAuthor("Martin Kleppmann")
);

Notice how nicely we can build the JPA entity instances when the attributes follow the fluent-style API guidelines.

For more details about using the fluent-style API design pattern, check out this article.

Fetching multiple entities by id using JPA

Let’s assume that the user provides several ISBN book numbers, and the application needs to load the associated Book entities from the database.

When using JPA, we can load multiple entities at once using either JPQL or Criteria API.

Fetching multiple entities by id using JPQL

To fetch multiple entities with a JPQL query, we need to pass the entity identifiers to the IN clause, as illustrated by the following example:

List<Book> books = entityManager.createQuery("""
    select b
    from Book b
    where b.isbn in (:isbn)
    """, Book.class)
.setParameter(
    "isbn", 
    Arrays.asList(
        "978-9730228236",
        "978-1934356555",
        "978-3950307825"
    )
)
.getResultList();

Now, when executing the JPQL query above, Hibernate generates the following SQL query:

Query:["
    SELECT 
        b.isbn AS isbn1_0_,
        b.author AS author2_0_,
        b.title AS title3_0_
    FROM 
        book b
    WHERE 
        b.isbn IN (
            ?,
            ?,
            ?
        )
 "], 
Params:[(
    978-9730228236, 
    978-1934356555, 
    978-3950307825
)]

The best way to log SQL statements is to use a JDBC DataSource or Driver proxy, as the open-source datasource-proxy project.

For more details about this topic, check out this article.

Now, if you vary the IN clause parameter count, you will see that the SQL statement will have to match the number of bind parameters, and that can affect the effectiveness of the SQL statement caching mechanism. To cope with this issue, I added support for the hibernate.query.in_clause_parameter_padding configuration property.

So, when enabling the hibernate.query.in_clause_parameter_padding configuration property:

<property>
    name="hibernate.query.in_clause_parameter_padding"
    value="true"
</property>

Hibernate will use the closest power-of-two integer that can hold all the provided bind parameter values. Therefore, when rerunning the previous JPQL query, Hibernate will execute the following SQL statement:

Query:["
    SELECT 
        b.isbn AS isbn1_0_,
        b.author AS author2_0_,
        b.title AS title3_0_
    FROM 
        book b
    WHERE 
        b.isbn IN (
            ?,
            ?,
            ?,
            ?
        )
 "], 
Params:[(
    978-9730228236, 
    978-1934356555, 
    978-3950307825,
    978-3950307825
)]

Notice how the 4th parameter was duplicated in the bind parameter value set. This will allow us to reuse the same SQL statement execution plan even when we supply four arguments, as illustrated by the following JPQL query:

List<Book> books = entityManager.createQuery(
    select b
    from Book b
    where b.isbn in (:isbn)
    """, Book.class)
.setParameter(
    "isbn", 
    Arrays.asList(
        "978-9730228236",
        "978-1934356555",
        "978-3950307825",
        "978-1449373320"
    )
)
.getResultList();

The hibernate.query.in_clause_parameter_padding optimization is especially useful when using a database system that provides an SQL execution plan cache, like Oracle, SQL Server, or DB2.

For more details about the hibernate.query.in_clause_parameter_padding configuration parameter, check out this article.

Fetching multiple entities by id using Criteria API

The previous JPQL query can also be written using the JPA Criteria API, as illustrated by the following example:

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Book> query = builder.createQuery(Book.class);

ParameterExpression<List> isbn = builder.parameter(List.class);

Root<Book> root = query
.from(Book.class);

query
.where(
    root.get("isbn").in(
        isbn
    )
);

List<Book> books = entityManager
.createQuery(query)
.setParameter(
    isbn, 
    Arrays.asList(
        "978-9730228236",
        "978-1934356555",
        "978-3950307825"
    )
)
.getResultList();

Just like with JPQL queries, you can use the hibernate.query.in_clause_parameter_padding optimization to increase the effectiveness of the SQL statement caching mechanism.

Fetching multiple entities by id using Hibernate

Besides supporting all the previously mentioned JPA features, Hibernate provides a provider-specific way of loading multiple entities by their identifier.

List<Book> books = entityManager
.unwrap(Session.class)
.byMultipleIds(Book.class)
.multiLoad(
    "978-9730228236",
    "978-1934356555",
    "978-3950307825"
);

When executing the multiLoad example above, Hibernate will generate the following SQL query:

Query:["
    SELECT 
        b.isbn AS isbn1_0_,
        b.author AS author2_0_,
        b.title AS title3_0_
    FROM 
        book b
    WHERE 
        b.isbn IN (
            ?,
            ?,
            ?
        )
 "], 
Params:[(
    978-9730228236, 
    978-1934356555, 
    978-3950307825
)]

While much more concise than a JPQL or Criteria API query, the multiLoad method doesn’t currently benefit from the hibernate.query.in_clause_parameter_padding optimization. However, I opened the HHH-13692 Jira issue for this, and this should be fixed in a future version of Hibernate.

Another advantage of using the multiLoad method is that entities can be loaded from the first or second-level cache without needing an SQL query to be executed.

I'm running an online workshop on the 11th of October about High-Performance SQL.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Fetching multiple entities by their id is a typical requirement when using JPQ and Hibernate. While using a JPQL or Criteria API query might seem a little bit verbose compared to the Hibernate multiLoad alternative, if you’re using a database system offering an SQL execution plan cache, then it’s a better idea to stick to the JPA query approach while enabling the hibernate.query.in_clause_parameter_padding optimization.

Insert details about how the information is going to be processed

DOWNLOAD NOW