Spring Data JPA Composite Key with @EmbeddedId

This post we will learn about dealing with Composite Keys with @EmbeddedId in Spring Data & JPA. We will see how we can define a table with composite key, specify the keys and see an example of putting an entry into the table and finding with the help of Composite key. 

If you want to learn how to use Spring Data JPA with a Spring Boot application please read our Spring Data and JPA Tutorial. 

Primary Key and Composite Primary Key

Although Database tables and their primary or index keys is too vast and sort of off scope of this post, we will quickly have a look what is Primary Key and Composite Primary Key. For your own interest and knowledge consider specific material to understand the concepts thoroughly. 

Primary Key

As a practice each row in a database table should be uniquely identified by a key column. This column can be a data column which has unique values or a specifically created column of sequential numbers or random ids like UUID. We call such a key column as a Primary Key of the table.

Having such a sequential Id or UUID sometimes, doesn’t help search operations. Consider an Employee table where a sequentially generated employee_id is a Primary key. Most of the cases, no one would search an employee with employee_id. More possible search keys would be name, designation, department or a combination. To help faster searches we may need to create additional indexes on search columns. 

Composite Primary Key

As an alternative, we can create a Primary Key of multiple columns which is called as Composite Primary Key. The Spring Data & JPA does support composite primary keys and today we will see how.

For this tutorial we will consider a table that stores Songs details such as name, genre, artist, rating, download link etc. We will create a Composite Primary Key of name, genre, and artist. For now we will assume that the song name, genre, and artist can uniquely identify a Song. 

Setup

We will build this tutorial, on top of a Spring Boot application. Considering you already have a Spring Boot project ready, we’ll add required dependencies and configurations in this section.

Make sure, you have next set of dependencies in your pom.xml file.

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-test</artifactId>
  <scope>test</scope>
</dependency>
<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
  <scope>test</scope>
</dependency>Code language: HTML, XML (xml)

Or, if yours is a Gradle project, add the next dependencies to your build.gradle file.

dependencies {
    implementation('org.springframework.boot:spring-boot-starter-web')
    compile("org.springframework.boot:spring-boot-starter-data-jpa")
    compile('mysql:mysql-connector-java')

    testImplementation('org.springframework.boot:spring-boot-starter-test')
    testCompile("com.h2database:h2")
}Code language: Gradle (gradle)

Note that we are using MySQL as our database, and H2 as a database for unit testing.

Now, have a look at application.yml. It has standard Spring Boot specific datasource configuration along with hibernate configs.

spring:
  datasource:
    url: jdbc:mysql://host:port/db_name
    password: password
    username: username
    driver-class-name: "com.mysql.jdbc.Driver"
  jpa:
    database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
    hibernate:
      ddl-auto: updateCode language: YAML (yaml)

The ddl-auto: update tells hibernate to create the tables on application startup, if they do not present already. 

Entity Bean and Repository

Let’s write our Entity Bean and Repository interface now. 

Id class (SongId)

At first, let’s create our ID class. As discussed before, our Song table has a composite key of name, genre, and artist. Instead of having these fields in Song entity, we will more them to a separate SongId class and refer the SongId class in Song.

The annotation @Embeddable is to tell that this is a Composite Id class and can be embedded into an entity bean.

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;
    }
}Code language: Java (java)

Song Entity Bean

Now, lets write the @Entity bean and add the SongId as a field marked with @Embedded.

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;
    }
}Code language: Java (java)

Song Repository Interface

Now, we have our Entity bean and Embedded Id in place. This time we will write the SongsRepository which extends from Spring Data‘s CrudRepository.

package com.amitph.spring.songs.repo;

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

@Repository
public interface SongsRepository extends CrudRepository<Song, SongId> {}Code language: Java (java)

Tests

package com.amitph.spring.songs.repo;

import com.amitph.spring.songs.web.SongNotFoundException;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.boot.test.autoconfigure.orm.jpa.TestEntityManager;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.ArrayList;
import java.util.List;

import static org.junit.Assert.assertTrue;

@DataJpaTest
@RunWith(SpringRunner.class)
public class SongsRepositoryTest {
    @Autowired SongsRepository repository;
    @Autowired TestEntityManager testEntityManager;

    @Test
    public void repoCorrectlySavesGivenSong() {
        SongId songId = new SongId("test_name", "test_album", "test_artist");
        Song song = new Song(songId, 23, null, null, 4, "http://download.this.song");

        repository.save(song);

        Song result = testEntityManager.find(Song.class, songId);
        assertTrue(result.getDownloadUrl().equals(song.getDownloadUrl()));
        assertTrue(result.getId().getAlbum().equals(song.getId().getAlbum()));
        assertTrue(result.getId().getName().equals(song.getId().getName()));
        assertTrue(result.getId().getArtist().equals(song.getId().getArtist()));
    }


    @Test
    public void repoCorrectlyFindsSongById() {
        List<Song> songs = insertFourSongsInDataBase();
        int testSongIndex = 3;

        SongId idToRetrieve = songs.get(testSongIndex).getId();

        Song result = repository.findById(idToRetrieve).orElseThrow(SongNotFoundException::new);

        assertTrue(result.getId().getAlbum().equals(songs.get(testSongIndex).getId().getAlbum()));
        assertTrue(result.getId().getName().equals(songs.get(testSongIndex).getId().getName()));
        assertTrue(result.getId().getArtist().equals(songs.get(testSongIndex).getId().getArtist()));

        assertTrue(result.getDuration() == songs.get(testSongIndex).getDuration());
        assertTrue(result.getRating() == songs.get(testSongIndex).getRating());
        assertTrue(result.getDownloadUrl().equals(songs.get(testSongIndex).getDownloadUrl()));
    }

    private List<Song> insertFourSongsInDataBase() {
        SongId songId1 = new SongId("test_name1", "test_album1", "test_artist1");
        Song song1 = new Song(songId1, 23, null, null, 3, "http://download.this.song1");

        SongId songId2 = new SongId("test_name2", "test_album2", "test_artist2");
        Song song2 = new Song(songId2, 21, null, null, 2, "http://download.this.song2");

        SongId songId3 = new SongId("test_name3", "test_album3", "test_artist3");
        Song song3 = new Song(songId3, 20, null, null, 4, "http://download.this.song3");

        SongId songId4 = new SongId("test_name4", "test_album4", "test_artist4");
        Song song4 = new Song(songId4, 26, null, null, 2, "http://download.this.song4");

        List<Song> songs = new ArrayList<>();
        songs.add(song1);
        songs.add(song2);
        songs.add(song3);
        songs.add(song4);

        testEntityManager.persistAndFlush(song1);
        testEntityManager.persistAndFlush(song2);
        testEntityManager.persistAndFlush(song3);
        testEntityManager.persistAndFlush(song4);
        return songs;
    }
}Code language: Java (java)

Here is simple test for our Repository. We use H2 a in-memory database for our test. The @DataJpaTest of Spring Boot does the auto-configuration of H2 Database and provides @TestEntityManager which we have autowired.

repoCorrectlySavesGivenSong

In this test we prepare a test Song along with a SongId and use our Repository to save it to the H2 database. We then, use TestEntityManager to retrieve the Song from database passing in the SongId, and then verify all the things we stored as retrieved as they were. 

repoCorrectlyFindsSongById

In this case we have created total 4 Songs along with different SongId. We used TestEntityManager to persist each of them to the database. We then, used our Responsitory under test and asked it to find a Song by id by passing in one of those 4 ids we created. Once retrieved we verified the exact same song was returned. 

Summary

In this Spring Data JPA Composite Key with @EmbeddedId tutorial we first saw what is Primary Key and why it is important. We also saw why some tables can’t have a unique column and something, why having a auto-generated id won’t help in searches. 
We then learned in brief about the concept of Composite Primary Key and we saw how Spring Data and JPA support it with the help of @EmbeddedId annotation.

We have also seen how to use Spring Boot‘s TestEntityManager to verify our repository and the entity beans work correctly.

We have drafted a separate article for how to perform search by using some of many columns in a primary key. If you are interested knowing about it, please visit Spring Data JPA find by @EmbeddedId Partially.

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

---