Introduction

On the Hibernate forum, I noticed the following question which is about using the Hibernate Query Cache for storing DTO projections, not entities.

While caching JPQL queries which select entities is rather typical, caching DTO projections is a lesser-known feature of the Hibernate second-level Query Cache.

Domain Model

Let’s assume we have two Post and PostComment entities that look as follows:

Now, for the front page of our website we just need to display a summary of the latest Post entries and mention the number of associated PostComment child entities.

However, we don’t want to fetch all Post along with their associated PostComments for two reasons:

1. We might bump into the HHH000104: firstResult/maxResults specified with collection fetch; applying in memory! issue
2. We don’t want to fetch more columns than necessary because that’s not very efficient.

So, for our front page, we will just select a summary that can be represented by the following DTO:

public class PostSummary {

    private Long id;

    private String title;

    private Date createdOn;

    private int commentCount;

    public PostSummary(
            Long id, 
            String title, 
            Date createdOn, 
            Number commentCount) {
        this.id = id;
        this.title = title;
        this.createdOn = createdOn;
        this.commentCount = commentCount.intValue();
    }

    public Long getId() {
        return id;
    }

    public String getTitle() {
        return title;
    }

    public Date getCreatedOn() {
        return createdOn;
    }

    public int getCommentCount() {
        return commentCount;
    }

    @Override
    public String toString() {
        return "PostSummary{" +
                "id=" + id +
                ", title='" + title + '\'' +
                ", createdOn=" + createdOn +
                ", commentCount=" + commentCount +
                '}';
    }
}

Fetching the PostSummary DTO

To fetch the latest PostSummary DTOs, we are going to use the following JPQL projection query:

List<PostSummary> getLatestPostSummaries(
            EntityManager entityManager,
            int maxResults,
            boolean cacheable) {
    List<PostSummary> latestPosts = entityManager.createQuery(
        "select new " +
        "   com.vladmihalcea.book.hpjp.hibernate.cache.query.PostSummary(" +
        "       p.id, " +
        "       p.title, " +
        "       p.createdOn, " +
        "       count(pc.id) " +
        "   ) " +
        "from PostComment pc " +
        "left join pc.post p " +
        "group by p.id, p.title " +
        "order by p.createdOn desc ", PostSummary.class)
    .setMaxResults(maxResults)
    .setHint(QueryHints.HINT_CACHEABLE, cacheable)
    .getResultList();

    LOGGER.debug("Latest posts: {}", latestPosts);

    return latestPosts;
}

So, this query uses several constructs that are worth mentioning:

• the SELECT clause uses a Constructor result DTO projection so the query returns a List of PostSummary objects.
• the setMaxResults method is used for limiting the size of the underlying SQL ResultSet
• the HINT_CACHEABLE JPA hint is for caching the ResultSet

Now, if we call this method without caching:

doInJPA(entityManager -> {
    List<PostSummary> latestPosts = getLatestPostSummaries(
        entityManager,
        5,
        false
    );

    assertEquals(5, latestPosts.size());
});

We can see that Hibernate generates the proper result:

SELECT 
    p.id AS col_0_0_,
    p.title AS col_1_0_,
    p.created_on AS col_2_0_,
    count(pc.id) AS col_3_0_
FROM 
    post_comment pc
LEFT OUTER JOIN 
    post p 
    ON pc.post_id=p.id
GROUP BY 
    p.id,
    p.title
ORDER BY 
    p.created_on DESC
LIMIT 
    5

-- Latest posts: [
    PostSummary{
        id=42, 
        title='High-Performance Java Persistence, Chapter 10', 
        createdOn=2018-02-07 12:09:53.691, 
        commentCount=6
    }, 
    PostSummary{
        id=40, 
        title='High-Performance Java Persistence, Chapter 9', 
        createdOn=2018-02-07 12:09:53.69, 
        commentCount=1
    }, 
    PostSummary{
        id=35, 
        title='High-Performance Java Persistence, Chapter 8', 
        createdOn=2018-02-07 12:09:53.686, 
        commentCount=4
    }, 
    PostSummary{
        id=30, 
        title='High-Performance Java Persistence, Chapter 7', 
        createdOn=2018-02-07 12:09:53.68, commentCount=4
    }, 
    PostSummary{
        id=19, title='High-Performance Java Persistence, Chapter 6', 
        createdOn=2018-02-07 12:09:53.67, 
        commentCount=9
    }
]

Now, if we call this method one more time, Hibernate executes the same SQL query:

SELECT 
    p.id AS col_0_0_,
    p.title AS col_1_0_,
    p.created_on AS col_2_0_,
    count(pc.id) AS col_3_0_
FROM 
    post_comment pc
LEFT OUTER JOIN 
    post p 
    ON pc.post_id=p.id
GROUP BY 
    p.id,
    p.title
ORDER BY 
    p.created_on DESC
LIMIT 
    5

But, we don’t want that since the front page is accessed very often, and we have more reads than writes into our system.

Caching the result set

So, if we pass the cacheable parameter set to true, Hibernate can cache the query, and we can see that in the query cache region :

So, when executing this test case:

doInJPA(entityManager -> {
    List<PostSummary> latestPosts = getLatestPostSummaries(
        entityManager,
        5,
        true
    );

    printQueryCacheRegionStatistics();
    assertEquals(5, latestPosts.size());
});

Hibernate is going to generate the following output:

-- Checking cached query results in region: org.hibernate.cache.internal.StandardQueryCache
-- key: sql: select querycache1_.id as col_0_0_, querycache1_.title as col_1_0_, querycache1_.created_on as col_2_0_, count(querycache0_.id) as col_3_0_ from post_comment querycache0_ left outer join post querycache1_ on querycache0_.post_id=querycache1_.id group by querycache1_.id , querycache1_.title order by querycache1_.created_on desc; parameters: ; named parameters: {}; max rows: 5; transformer: org.hibernate.transform.CacheableResultTransformer@47bbf55f
-- Element for key sql: select querycache1_.id as col_0_0_, querycache1_.title as col_1_0_, querycache1_.created_on as col_2_0_, count(querycache0_.id) as col_3_0_ from post_comment querycache0_ left outer join post querycache1_ on querycache0_.post_id=querycache1_.id group by querycache1_.id , querycache1_.title order by querycache1_.created_on desc; parameters: ; named parameters: {}; max rows: 5; transformer: org.hibernate.transform.CacheableResultTransformer@47bbf55f is null
-- Query results were not found in cache

SELECT 
    p.id AS col_0_0_,
    p.title AS col_1_0_,
    p.created_on AS col_2_0_,
    count(pc.id) AS col_3_0_
FROM 
    post_comment pc
LEFT OUTER JOIN 
    post p 
    ON pc.post_id=p.id
GROUP BY 
    p.id,
    p.title
ORDER BY 
    p.created_on DESC
LIMIT 
    5

-- Caching query results in region: org.hibernate.cache.internal.StandardQueryCache; timestamp=6217724081721344
-- key: sql: select querycache1_.id as col_0_0_, querycache1_.title as col_1_0_, querycache1_.created_on as col_2_0_, count(querycache0_.id) as col_3_0_ from post_comment querycache0_ left outer join post querycache1_ on querycache0_.post_id=querycache1_.id group by querycache1_.id , querycache1_.title order by querycache1_.created_on desc; parameters: ; named parameters: {}; max rows: 5; transformer: org.hibernate.transform.CacheableResultTransformer@47bbf55f value: [6217724081721344, [Ljava.io.Serializable;@621f23ac, [Ljava.io.Serializable;@7761e342, [Ljava.io.Serializable;@51f68849, [Ljava.io.Serializable;@4eb9ae4d, [Ljava.io.Serializable;@5520f675]

So, the result was cached, and if we try to load it once more, Hibernate is going to skip the SQL query execution:

-- Checking cached query results in region: org.hibernate.cache.internal.StandardQueryCache
-- key: sql: select querycache1_.id as col_0_0_, querycache1_.title as col_1_0_, querycache1_.created_on as col_2_0_, count(querycache0_.id) as col_3_0_ from post_comment querycache0_ left outer join post querycache1_ on querycache0_.post_id=querycache1_.id group by querycache1_.id , querycache1_.title order by querycache1_.created_on desc; parameters: ; named parameters: {}; max rows: 5; transformer: org.hibernate.transform.CacheableResultTransformer@47bbf55f
-- Checking query spaces are up-to-date: [post, post_comment]
-- Returning cached query results

Cool, right?

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

While using the Hibernate Query Cache for entities is not uncommon, the second-level Query Cache works also for DTO projections, and it can be suitable if we have SQL queries that are executed often, but the underlying tables don’t change frequently.

Insert details about how the information is going to be processed

DOWNLOAD NOW