spring data jpa find by composite primary key


In this blog, we are going to learn composite primary key in spring data JPA with two annotations @IdClass and @EmbeddedId. 

What is Composite Primary Key?

A Composite Primary Key or simply called a composite key is primary key with two or more than two columns. 

We have two ways of defining composite keys(A multi column primary key). 

  1. @IdClass Annotation
  2. @EmbeddedId Annotation

However, there are some requirements in order to use these annotations. 

  • The class for this composite key must be public
  • It must implement Serializable interface
  • It must contain equals() and hashcode() methods.
  • It must also contains a no arguments contructor.

Lombok Annotations


We can Initialize a Spring boot project just like shown in this blog and include lombok maven dependency. 
In case you are unfamiliar with lombok, it is a java library used to populate boiler plate code with the help of annotations. 

@Data annotation populates Getters, Setters, HashCode and Equals methods. 

We also use @NoArgsConstructor annotation too.


Defining Entities

Let us assume we have User Entity and Post Entity. I don't want to do any joins. 
So, we create a new Entity UserPost which contains both userId and postId. 

import lombok.Data;
import lombok.ToString;
import javax.persistence.*;
@Data
@Entity
@ToString
@Table(name = "post")
public class Post {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "post_id")
private Integer postId;
@Column(name = "post_name")
private String postName;
@Column(name = "post_description")
private String postDescription;
@Column(name = "created_on")
private Long createdOn;
@Column(name = "created_by")
private String createdBy;
@Column(name = "modified_on")
private Long modifiedOn;
@Column(name = "modified_by")
private String modifiedBy;
}
view raw Post.java hosted with ❀ by GitHub
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.*;
@Data
@Entity
@Table(name = "user_details")
@NoArgsConstructor
@AllArgsConstructor
public class UserDetails {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "user_id")
private Integer userId;
@Column(name = "first_name")
private String firstName;
@Column(name = "middle_name")
private String middleName;
@Column(name = "last_name")
private String lastName;
@Column(name = "email")
private String email;
@Column(name = "password_hash")
private String passwordHash;
@Column(name = "created_on")
private Long createdOn;
@Column(name = "created_by")
private String createdBy;
@Column(name = "modified_on")
private Long modifiedOn;
@Column(name = "modified_by")
private String modifiedBy;
}
view raw User.java hosted with ❀ by GitHub

Using @IdClass Annotation

Let us create our UserPost Entity and our composite key class UserPostId. 

On the top of our UserPost Entity, we use @IdClass annotation and pass UserPostId class as argument. We need to have our composite key columns annotated with @Id annotation in UserPost Entity class. 

We need to have our composite columns in the Composite Key class. 


import com.pranay.blog.entities.compositekey.UserPostId;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
import javax.persistence.*;
import java.io.Serializable;
@Data
@Entity
@Table(name = "user_post")
@AllArgsConstructor
@NoArgsConstructor
@IdClass(UserPostId.class)
public class UserPost implements Serializable {
private static final long serialVersionUID = -909206262878526790L;
@Id
@Column(name = "user_id")
private Integer userId;
@Id
@Column(name = "post_id")
private Integer postId;
}
view raw UserPost.java hosted with ❀ by GitHub
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;
import java.io.Serializable;
@Data
@NoArgsConstructor
@EqualsAndHashCode
public class UserPostId implements Serializable {
private static final long serialVersionUID = 2702030623316532366L;
private Integer userId;
private Integer postId;
}
view raw UserPostId.java hosted with ❀ by GitHub



Using @EmbeddedId Annotation

Same as above, we need an Entity and composite key class.

Let us create our UserPost Entity and our composite key class UserPostId. 

We use @Embedabble annotation on the top of our composite key class and in our entity class, there is no need to declare these fields again, instead we define a variable with UserPostId userPostId with @EmbeddedId annotation.


import com.pranay.blog.entities.compositekey.UserPostId;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.*;
import java.io.Serializable;
@Data
@Entity
@Table(name = "user_post")
@AllArgsConstructor
@NoArgsConstructor
public class UserPost implements Serializable {
private static final long serialVersionUID = -9092062626878526790L;
@EmbeddedId
private UserPostId userPostId;
}
view raw UserPost.java hosted with ❀ by GitHub
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;
import javax.persistence.Embeddable;
import java.io.Serializable;
@Data
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
@Embeddable
public class UserPostId implements Serializable {
private static final long serialVersionUID = 2702030623316532366L;
private Integer userId;
private Integer postId;
}
view raw UserPostId.java hosted with ❀ by GitHub


Custom query using @Query on our Jpa Repository

The difference while using custom query using @Query Annotation is we need to mention the field name of our composite class additionally wherein while using Id, we can mention the field name inside our composite key class directly. Below are find by composite key queries look like.


@Repository
public interface UserPostRepository extends JpaRepository<UserPost, UserPostId> {
//@EmbeddableId key class query
@Query("SELECT up.userPostId.postId FROM UserPost up where up.userPostId.userId=:userId")
Set<Integer> getUserPostFromUserId(Integer userId);
}
@Repository
public interface UserPostRepository extends JpaRepository<UserPost, UserPostId> {
//@IdClass Composite Key class query
@Query("SELECT up.postId FROM UserPost up where up.userId=:userId")
Set<Integer> getUserPostFromUserId(Integer userId);
}

Post a Comment