JavaSpringTechnology

Spring Data JPA find by @EmbeddedId Partially

In this article we will see ways to query when we have a Composite Primary Key, represented by Spring Data and JPA @EmbeddedId and we want to filter results using only few of the fields in Id.

This tutorial doesn’t cover basic project setup or dependencies. If you are after basic tutorial for Spring Data JPA with Spring Boot, please visit Accessing Data with Spring Data JPA.

In the Spring Data JPA Composite Key with @EmbeddedId tutorial we saw how to represent Composite Key in JPA Entity object and how to query it by Id using Spring Data. When we search with Id, we actually don’t need to provide the repository method declaration. Spring Data’s Crud Repository has already declared it for you.

Sometimes, though, we may want to search with one or only few of the total columns in the Id. Let’s consider our Song table, where the song name, album, and artist are the three column composite key. User don’t remember the album and wants to find Songs with Name and Artist. In the following section we will see how to do that.

1 Entity Id (SongId)

Below is our CompositeId class. Which represents three columns Composite key.

2 Entity Object (Song)

The Song is a JPA based Entity object which has fields related to Songs along with a reference to the SongId instance. The SongId instance reference here is marked as @EmebeddedId

3 Repository class

As our SongRepository is extended from Spring Data CrudRepository it already has declared standard CRUD methods. However, our scenario is different so we will need to declare a query method for this scenario.

The method findByIdNameAndIdArtist is written in standard JPA format. The method name is resolved into object and field notion and converted to an actual SQL query in the background.

The findByIdNameAndIdArtist is converted to -> findBy + id.name + AND + id.artist

Also, notice that the method takes two arguments which are obviously the name and artist

4 What if we have large number of id fields to search by?

Consider a Hypothetical scenario where we have too many fields in the ID and we want to search by too many fields.

Here, the LongSongId has got 9 field Composite Primary Key. What if we want to search by first 8 fields using the JPA Repository?

Answer is simple write a query method, like the one we saw above.  Here is how our LongKeySongRepository looks like.

Looks at that method. It’s almost 270 characters long. It’s a full Sentence if not paragraph. This method works perfectly but its signature is too lengthy think about making a call to such a method and passing 8 arguments. Surely, any static code review tool is gonna shout an error for this. The problem become worse if you have even more fields or lengthier field names.

5 Using Spring JPA  filter by Example

Spring data provides a search by  Example mechanism which is absolutely helpful here. We can write a findAll method which takes and Example of Song to match. It’s actually preparing a Song instance with the fields we want to search by and give to Spring Date as an Example and Spring Data will use it to find objects in the database. 

Now, let see how to create an Example instance and call above repository method

6 Conclusion

We learnt 

  • How to use query method to search by various field on an Entity Object.
  • How Spring Data resolves the query method name into object field notion.
  • If we have large number of fields in Primary Key, searching with large number of fields makes method name really lengthy. 
  • How to use Spring Data Example to simply searching with large number of fields. 

Learn about Spring Data and JPA in our Spring Data and JPA Tutorial. Learn about creating a Spring Boot backed RESTfull Web Service in our Spring Boot REST Service tutorial.


For full source code of the examples used here, please visit https://github.com/amitrp/spring-date-jpa-embeddedid. 


Leave a Reply

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