How to map the latest child of a parent entity using Hibernate JoinFormula

Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

Introduction

In this article, I’m going to explain how the Hibernate JoinFormula annotation works and how you can use it to map the latest child of a parent entity.

As previously explained, the @JoinFormula is a very awesome annotation which allows you to customize the way you join entities beyond JPA @JoinColumn capabilities.

Domain Model

For the upcoming test cases, we are going to use the following entities:

Post using Hibernate JoinFormulat to map the latest PostComment =

The PostComment entity is mapped as follows:

@Entity(name = "PostComment")
@Table(name = "post_comment")
public class PostComment {

    @Id
    private Long id;

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

    private String review;

    @Column(name = "created_on")
    @Temporal(TemporalType.TIMESTAMP)
    private Date createdOn;

    //Getters and setters omitted for brevity
}

Not only the PostComment has a @ManyToOne association to a Post, but the Post is also associated with the latest PostComment as follows:

@Entity(name = "Post")
@Table(name = "post")
public class Post {

    @Id
    private Long id;

    private String title;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinFormula("""
        (SELECT pc.id
        FROM post_comment pc
        WHERE pc.post_id = id
        ORDER BY pc.created_on DESC
        LIMIT 1)
        """
    )
    private PostComment latestComment;

    //Getters and setters omitted for brevity
}

The latestComment attribute associates the parent Post entity with the latest PostComment child entity. The @JoinFormula annotation allows us to define any SQL select query to provide the relationship between two entities.

Testing the Hibernate JoinFormula annotation

Considering we have the following entities in our database:

Post post = new Post()
    .setId(1L)
    .setTitle("High-Performance Java Persistence");

entityManager.persist(post);

assertNull(post.getLatestComment());

entityManager.persist(
    new PostComment()
    .setId(1L)
    .setPost(post)
    .setCreatedOn(
        Timestamp.valueOf(
            LocalDateTime.of(2016, 11, 2, 12, 33, 14)
        )
    )
    .setReview("Woohoo!")
);

entityManager.persist(
    new PostComment()
    .setId(2L)
    .setPost(post)
    .setCreatedOn(
        Timestamp.valueOf(
            LocalDateTime.of(2016, 11, 2, 15, 45, 58)
        )
    )
    .setReview("Finally!")
);

entityManager.persist(
    new PostComment()
    .setId(3L)
    .setPost(post)
    .setCreatedOn(
        Timestamp.valueOf(
            LocalDateTime.of(2017, 2, 16, 16, 10, 21)
        )
    )
    .setReview("Awesome!")
);

When we fetch the Post entity, we can see that the latestComment attribute works as expected:

Post post = entityManager.find(Post.class, 1L);
PostComment latestComment = post.getLatestComment();

assertEquals("Awesome!", latestComment.getReview());

Online Workshops

If you enjoyed this article, I bet you are going to love my upcoming Online Workshops!

Conclusion

As I explained in my book, High-Performance Java Persistence, if you don’t take advantage of the underlying JPA provider or relational database capabilities, you are going to lose lots of features.

Transactions and Concurrency Control eBook

12 Comments on “How to map the latest child of a parent entity using Hibernate JoinFormula

  1. The LIMIT 1 works for H2. I get syntax errors when running against Oracle or SQL Server. Is there a portable way, or do I have to switch for another solution?

    • Theoretically, you could use the SQL Standard FETCH FIRST 1 ROWS ONLY syntax. However, I tested it and it looks like Hibernate adds some aliases to the keyword as it doesn’t recognize them. Most likely they need to be added to the base Dialect. You should open a Hibernate Jira issue for it.

  2. Hi Vlad, unfortunately any parent entity won’t be returned if it has no ‘latest’ child record (or any record matching the condition in the JoinFormula). I’ve just tested that in your test: I added another Post without any PostComments and at the bottom of the test this post is not returned.
    What is the best way to return it and have the ‘latest’ record simply null? Thanks.

      • If you explicitly ask for Post with id 2, then yes. If you ask for all posts, you only get the one that has the ‘latest’ record. I’ve created a PR with a simple test to demonstrate it.

      • That’s the expected behaviour when you use JOIN which means INNER JOIN. Try with LEFT JOIN if you want to get all Posts, no matter whether they have comments.

  3. There is a @ManyToOne association in the Post entity. I wonder why @JoinFormula can’t use with @OneToOne?

    • There’s a bug in Hibernate annotation processing preventing you from using a @OneToOne association with @JoinFormula.

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.