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:

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.