Spring JPA @NamedQuery With a Secondary Table Select-In

We're in the process of adding Like buttons to NixMash Spring Posts, so we needed to first get our data schema in order. We created a user_likes table which is comprised of a primary key, user_id, item_id, and content_type_id fields. We have item_id and content_type_id (rather than simply a post_id field) so we can extend “likes” to other areas in the application.

You can see in the schema below that we have a foreign key to users, but because of the reusable logic of user_likes we couldn't apply a key from item_id to the posts table post_id field.

JPA Task At Hand: Retrieve Posts Liked By User From User_Likes Table

We have a shiny new Like Hibernate Entity, but no persistence relationship between our Post object to our Like object. We didn't create a @OneToMany relationship from User to Like either, as it didn't seem to add any benefit for the additional overhead.

We're retrieving all of the posts liked by User, stored as the item_id values in user_likes. We need to retrieve everything from our Post object, including its @ManyToMany Tag children, but we need a more inventive way of retrieving those Posts.

Enter the Spring JPA @NamedQuery

We recently covered Native SQL Queries in Spring JPA, or the @NamedNativeQuery. There we performed an actual SQL Query and populated a class. Today we query the class.

Much like our @NamedNativeQuery we add our @NamedQuery to our Post Entity. A couple things to mention here. 1) Notice the name property. We're adding a “Post.getByPostIds” query to our Post Entity. We'll see that query name in a bit. 2) We're performing a SQL “Select In” query, where :postIds are the item_ids in our user_likes table.

The Post Repository Query

Nothing to do here. Move along to the Service layer.

Gathering the Item_Ids from the User_Likes Table

I said we'd move on to the Service Layer and construct the query, but we need to collect the User's Liked Post Ids, or the item_ids from the user_likes table with the user's user_id. This is interesting because we perform a SQL “Select In” query in our Post @NamedQuery and so will add a custom @Query in our LikeRepository to retrieve only the items_id.

Service Layer, Constructing the Query

We're ready to put the pieces together in the Post Service class. We use our @PersistenceContext EntityManager em Bean to createNamedQuery() from “Post.getByPostIds”, setting our @NamedQuery :postIds parameter to our LikeRepository @Query list of Long item_ids. Our getResultList() contains our list of Post class objects that the user “liked”.

So what was that? 7 or 8 lines of code to perform a rather complicated SQL query and data retrieval? Spring JPA, my friends. Spring J-P-A.

Source Code Notes for this Post

All source code discussed in this post can be found in my NixMash Spring GitHub repo and viewed online here.

Posted July 20, 2016 08:47 PM EDT

More Like This Post