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 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 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.

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;

    public SongId(String name, String album, String artist) {
        this.name = name;
        this.album = album;
        this.artist = artist;
    }

    public SongId() {
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAlbum() {
        return album;
    }

    public void setAlbum(String album) {
        this.album = album;
    }

    public String getArtist() {
        return artist;
    }

    public void setArtist(String artist) {
        this.artist = artist;
    }
}

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

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;
    int rating;
    private String downloadUrl;

    public Song(SongId id, int duration, String genre, LocalDateTime releaseDate, int rating, String downloadUrl) {
        this.id = id;
        this.duration = duration;
        this.genre = genre;
        this.releaseDate = releaseDate;
        this.rating = rating;
        this.downloadUrl = downloadUrl;
    }

    public Song() {
    }

    public SongId getId() {
        return id;
    }

    public void setId(SongId id) {
        this.id = id;
    }

    public int getDuration() {
        return duration;
    }

    public void setDuration(int duration) {
        this.duration = duration;
    }

    public String getGenre() {
        return genre;
    }

    public void setGenre(String genre) {
        this.genre = genre;
    }

    public LocalDateTime getReleaseDate() {
        return releaseDate;
    }

    public void setReleaseDate(LocalDateTime releaseDate) {
        this.releaseDate = releaseDate;
    }

    public int getRating() {
        return rating;
    }

    public void setRating(int rating) {
        this.rating = rating;
    }

    public String getDownloadUrl() {
        return downloadUrl;
    }

    public void setDownloadUrl(String downloadUrl) {
        this.downloadUrl = downloadUrl;
    }
}

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.

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);
}

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.

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;

    public String getCoArtist() { return coArtist; }

    public void setCoArtist(String coArtist) { this.coArtist = coArtist; }

    public String getSoundEngineer() { return soundEngineer; }

    public void setSoundEngineer(String soundEngineer) { this.soundEngineer = soundEngineer; }

    public String getRecordingArtist() { return recordingArtist; }

    public void setRecordingArtist(String recordingArtist) { this.recordingArtist = recordingArtist; }

    public String getComposer() { return composer; }

    public void setComposer(String composer) { this.composer = composer; }

    public String getProducer() { return producer; }

    public void setProducer(String producer) { this.producer = producer; }

    public String getCountry() { return country; }

    public void setCountry(String country) { this.country = country; }

    public String getName() { return name; }

    public void setName(String name) { this.name = name;}

    public String getAlbum() { return album;}

    public void setAlbum(String album) { this.album = album;}

    public String getArtist() { return artist;}

    public void setArtist(String artist) { this.artist = artist;}

}

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.

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);
}

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. 

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);
}

Now, let see how to create an Example instance and call 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);
    }

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.