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

(Last Updated On: January 4, 2018)

Introduction

Today, I stumbled upon a StackOverflow answer that I gave some time ago and realized that it deserves a post of its own.

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:

postandlatestpostcomment

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 time

Considering we have the following entities in our database:

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

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

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

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

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());

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

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.

Subscribe to our Newsletter

* indicates required
10 000 readers have found this blog worth following!

If you subscribe to my newsletter, you'll get:
  • A free sample of my Video Course about running Integration tests at warp-speed using Docker and tmpfs
  • 3 chapters from my book, High-Performance Java Persistence, 
  • a 10% discount coupon for my book. 
Get the most out of your persistence layer!

Advertisements

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

  1. Hi Vlad, could you explain how can I implement this solution when I need the last comment of the currently logged user (eg by extracting the ID from SecurityContex and appending it to the WHERE clause)? Can the query in the formula by dynamic somehow? Thanks in advance.

      1. Correct me if I’m wrong but I cannot find anything related to session scoped variables in the linked article.

        Well, if I write a query to fetch all Posts from the database, together with the latest comment by the currently logged user (or just the post if the user has not commented on it), then I would need to join the comment table on the

        comment.post_id = post.id and the comment.user_id = curent_user_id

        passed to the HQL query as a parameter. Unfortunately Hibernate does not support JOINT FETCH with ON or WITH. Is there any way to implement such a query apart from using a Dto projection and a simple JOIN ON (without FETCH)?

      2. Correct me if I’m wrong but I cannot find anything related to session scoped variables in the linked article.

        There’s nothing related to session variables in this article. But there is an example for using database-specific variables in this one.

        Well, if I write a query to fetch all Posts from the database, together with the latest comment by the currently logged user (or just the post if the user has not commented on it), then I would need to join the comment table on the comment.post_id = post.id and the comment.user_id = curent_user_id

        That’s needed if you want to run a native SQL query, otherwise, the JOIN clause is specified during mapping, not query time.

        Unfortunately, Hibernate does not support JOINT FETCH with ON or WITH. Is there any way to implement such a query apart from using a Dto projection and a simple JOIN ON (without FETCH)?

        Of course, the JOIN FETCH works even if you provided a custom ON clause. Check out this example on my book GitHub repository.

      3. The actual business problem is inversed, I actually need to fetch all posts, and for each post where the current user has commented on, I also need this latest comment of the current user (to display it to the user on the posts list, I still need all posts even those not commented on). In this case the WHERE clause filters do not solve the problem, I need to left join on the user id too to get the required result (the cross product of the joins will contain null in comment colums for those posts, which the user has not commented on). But it looks I cannot do it simply with HQL, at least not with a single query. The only solution I came up with which produces a single SQL query is the one using JOIN ON (or WITH) but no FETCH and with a Dto projection with explicit constructor call with all the post and comment columns passed as parameters.

      4. In that case, you need to write a custom SQL query because JPA collections can either be fetched fully or not fetched at all. You can use the ResultTransformer to build the parent-child tree graph as explained in this article.

    1. Sorry maybe I didn’t describe the issue clearly, I mean that if you write a HQL query like this one

      “select p from Post p left join fetch p.userComment c with (c.user_id = :currentUserId)”

      I get a “QuerySyntaxException: with-clause not allowed on fetched associations; use filters”.

      However the WITH clause works if you just use “left join” instead of “left join fetch”. (The underlying SQl is a JOIN ON with two conditions)

      In my case the I am joining the Comment table not only on the post_id but also on the user_id being equal to a currentUserId which is a dynamic HQL query parameter set at runtime, which cannot be expressed in the mapping.

      I checked out the github example, though cannot find a custom JOIN .. ON clause, only the @JoinFormula and join fetch.

      1. JOIN FETCH tells Hibernate that the entire collection should be fetched, that’s why you can’t use SQL-level ON clauses to filter it. Now, you can simply fetch what you want like this:

        select c
        from UserComment c
        join fetch c.post
        where c.user = :currentUser
        

        This way, you get all comments belonging to the current user, along with their associated Post.

  2. I have been looking for such code last week. Why oh why I didn’t find that page when 🙂 AFAIR it doesn’t work with @OneToOne annotation. Only @ManyToOne works for me. I explain it to me like “Many Posts to one JointFormula for PostComment” :-). Anyway it works like a charm.

  3. Hi Vlad, there is one thing I don’t understand. Why do we need a ManyToOne annotation in the latestComment attribute even knowing that there’s just always one “latestComment” for a post?
    Thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *