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.
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.
Only if you use database-specific session scoped variables, as I explained in this article.
However, is it worth complicating your mapping like that? I doubt it. Just use a query and you are done. It’s much simpler and more flexible too. Or, you can use an Entity Listener, as explained in this article.
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)?
There’s nothing related to session variables in this article. But there is an example for using database-specific variables in this one.
That’s needed if you want to run a native SQL query, otherwise, the JOIN clause is specified during mapping, not query time.
Of course, the JOIN FETCH works even if you provided a custom ON clause. Check out this example on my book GitHub repository.
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.
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
ResultTransformerto build the parent-child tree graph as explained in this article.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.
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:
This way, you get all comments belonging to the current user, along with their associated Post.
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.
If you liked this article, I bet you are going to love my book as well. Just check out the testimonials I got from my readers.
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.
You can also make it
@OneToOne. In the end, it doesn’t make any difference from a SQL perspective.