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 a few of the fields in Id.

This tutorial doesn’t cover basic project setups or dependencies. If you are after a basic tutorial for Spring Data JPA with Spring Boot, please visit Spring Boot 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 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 a 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. The user doesn’t remember the album and wants to find Songs with names and artists’ names. In the following section, we will see how to do that.

Entity Id (SongId)

Below is our CompositeId class, which represents three columns Composite key.

package com.amitph.spring.songs.repo;

import javax.persistence.Embeddable;
import java.io.Serializable;

@Embeddable
public class SongId implements Serializable {
  private String name;
  private String album;
  private String artist;

  // Getter, Setter, and Constructors
}Code language: Java (java)

Entity Object (Song)

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

package com.amitph.spring.songs.repo;

import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import java.time.LocalDateTime;

@Entity
public class Song {
  @EmbeddedId 
  private SongId id;
    
  private int duration;
  private String genre;
  private LocalDateTime releaseDate;
  private int rating;
  private String downloadUrl;

  // Getter, Setter, and Constructors
}Code language: Java (java)

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.

package com.amitph.spring.songs.repo;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface SongsRepository extends 
    CrudRepository<Song, SongId> {
  List<Song> findByIdNameAndIdArtist(String name, String artist);
}Code language: Java (java)

The method findByIdNameAndIdArtist is written in standard JPA format. The method name is resolved into an 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: the name and the artist. 

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

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

package com.amitph.spring.songs.repo;

import javax.persistence.Embeddable;

@Embeddable
public class LongKeySongId implements Serializable{
  private String name;
  private String album;
  private String artist;
  private String coArtist;
  private String soundEngineer;
  private String recordingArtist;
  private String composer;
  private String producer;
  private String country;

  // Getter, Setter, and Constructors

}Code language: Java (java)

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

The answer is simple to write a query method, like the one we saw above. Here is what our LongKeySongRepository looks like.

package com.amitph.spring.songs.repo;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface LongKeySongRepository 
    extends CrudRepository<LongKeySong, LongKeySongId> {
    
  // Method filters by 8 out of 9 Id keys
  List<LongKeySong> findByIdNameAndIdArtistAndIdAlbumAndIdCoArtistAndIdComposerAndIdSoundEngineerAndIdProducerAndIdRecordingArtist(String name, String artist, String album, String coArtist, String composer, String soundEngineer, String producer, String recordingArtist);
}Code language: Java (java)

Look at that method. It’s almost 270 characters long. This method works perfectly fine, but its signature is too lengthy. Indeed, any static code review tool will shout an error for this. The problem worsens if you have even more fields or longer names.

Using Spring JPA  filter by `Example`

Spring data provides a search by  Example mechanism, which is helpful here. We can write a findAll method that takes an Example of a Song to match. It’s 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. 

package com.amitph.spring.songs.repo;

import org.springframework.data.domain.Example;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface LongKeySongRepository 
    extends CrudRepository<LongKeySong, LongKeySongId> {
    
  List<LongKeySong> findAll(Example<LongKeySong> song);
}Code language: Java (java)

Now, let’s see how to create an Example instance and call the above repository method. 

public List<LongKeySong> findByIdPartiallyWithExample(
    String name, String artist, String album, String coArtist, 
    String composer, String soundEngineer, String producer, 
    String recordingArtist) {
    
  LongKeySong longKeySong = new LongKeySong();
  LongKeySongId longKeySongId = new LongKeySongId();
  longKeySong.setId(longKeySongId);

  longKeySongId.setName(name);
  longKeySongId.setAlbum(album);
  longKeySongId.setArtist(artist);
  longKeySongId.setCoArtist(coArtist);
  longKeySongId.setComposer(composer);
  longKeySongId.setSoundEngineer(soundEngineer);
  longKeySongId.setProducer(producer);
  longKeySongId.setRecordingArtist(recordingArtist);

  Example<LongKeySong> songExample = Example.of(longKeySong);
  return longKeySongRepository.findAll(songExample);
}Code language: Java (java)

Summary

We learnt 

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

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

For the full source code of the examples used here, please visit our GitHub Repository.

---