Composite Primary Key
Composite primary key is a combination of two or more columns in a table. So instead of a single column to be a primary key, more than one column or field is made to be a primary key to uniquely identify the rows in a given table.
Let’s say you have created a table called user in your database and the user table has three columns – id, first name and last name. So you don’t want to make the id column as a primary key but first name and last name together will be a unique and primary key. Here first and last name fields make together a composite primary key.
Prerequisites
Java 11 (1.8+), Spring Boot/Spring Data JPA 2.7.6, Maven 3.8.5, MySQL 8.0.26
Project Setup
You can use the following pom.xml file for your maven based project.
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.roytuts</groupId>
<artifactId>springboot-data-jpa-composite-primary-key</artifactId>
<version>0.0.1-SNAPSHOT</version>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>11</maven.compiler.source>
<maven.compiler.target>11</maven.compiler.target>
</properties>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.6</version>
</parent>
<dependencies>
<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>
<!--required only if jdk 9 or higher version is used -->
<dependency>
<groupId>javax.xml.bind</groupId>
<artifactId>jaxb-api</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>The jaxb-api dependency is needed when your application requires JDK 9 or above because, by default, JDK 9 or above version does not include jaxb-api.
MySQL Table
The following table can be used for composite primary key example. The first_name and last_name columns make together a composite primary key.
CREATE TABLE user (
id int unsigned COLLATE utf8mb4_unicode_ci NOT NULL,
first_name varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
last_name varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (last_name,first_name)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Insert few sample data to test the application once it is built:
INSERT INTO `user` (`id`, `first_name`, `last_name`) VALUES
(3, 'Arup', 'Roy'),
(1, 'Soumitra', 'Roy'),
(2, 'Liton', 'Sarkar');Composite Primary Key Class
To build a composite primary key class in Java using Spring Boot Data JPA, you need to have the no-argument constructor and you need to override equals() and hashCode() methods for building the logic to check equality.
The composite primary key class can be marked with annotation @Embeddable to use this class as a key in the entity class. Here you need to mention the table’s column names if they are different from Java field name using @Column annotation.
@Embeddable
public class UserPKey implements Serializable {
private static final long serialVersionUID = 1L;
@Column(name = "first_name")
private String firstName;
@Column(name = "last_name")
private String lastName;
public UserPKey() {
}
public UserPKey(String firstName, String lastName) {
this.firstName = firstName;
this.lastName = lastName;
}
public String getFirstName() {
return firstName;
}
public String getLastName() {
return lastName;
}
@Override
public int hashCode() {
return Objects.hash(getFirstName(), getLastName());
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
UserPKey other = (UserPKey) obj;
return Objects.equals(getFirstName(), other.getFirstName())
&& Objects.equals(getLastName(), other.getLastName());
}
@Override
public String toString() {
return "CompositePKey [firstName=" + firstName + ", lastName=" + lastName + "]";
}
}Your composite primary key class must implement Serializable interface otherwise your application will throw the following exception:
Caused by: org.hibernate.MappingException: Composite-id class must implement Serializable: com.roytuts.springboot.datajpa.composite.primarykey.entity.UserPKey
at org.hibernate.mapping.RootClass.checkCompositeIdentifierEntity Class
The Java entity class is mapped to the database table (user). Notice in the following entity class I have used @EmbeddedId on the composite primary key class to indicate it as a primary key.
@Entity
@Table // (name = "user")
public class User {
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
@EmbeddedId
private UserPKey userPKey;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
@Override
public String toString() {
return "User [id=" + id + ", userPKey=" + userPKey + "]";
}
public UserPKey getUserPKey() {
return userPKey;
}
public void setUserPKey(UserPKey userPKey) {
this.userPKey = userPKey;
}
}If your Java class name and table name are same then you don’t need to specify the table name using @Table annotation on the entity class.
Repository Interface
Spring Data JPA provides interface which can be used to perform basic CRUD (Create Read Update Delete) operations without writing a single SQL statement.
I am writing a method to find a user using composite primary key value.
public interface UserRepository extends JpaRepository<User, Integer> {
User findByUserPKey(UserPKey userPKey);
}Datasource Configuration
The following standard datasource configuration is written into the file application.properties under src/main/resources folder.
spring.datasource.url=jdbc:mysql://localhost/roytuts
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driverClassName=com.mysql.cj.jdbc.DriverSpring Boot Main Class
The spring boot main class is enough to deploy the application into embedded Tomcat server. Here I am using CLI (Command Line Interface) version of the main class to run the application in standalone mode.
@SpringBootApplication
@EntityScan(basePackages = "com.roytuts.springboot.datajpa.composite.primarykey.entity")
@EnableJpaRepositories(basePackages = "com.roytuts.springboot.datajpa.composite.primarykey.repository")
public class App implements CommandLineRunner {
@Autowired
private UserRepository userRepository;
public static void main(String[] args) {
SpringApplication.run(App.class, args).close();
}
@Override
public void run(String... args) throws Exception {
System.out.println("=====================================================================");
List<User> users = userRepository.findAll();
users.stream().forEach(u -> System.out.println(u));
System.out.println("---------------------------------------------------------------------");
System.out.println();
User user = userRepository.findByUserPKey(new UserPKey("Soumitra", "Roy"));
System.out.println(user);
System.out.println("---------------------------------------------------------------------");
System.out.println();
User newUser = new User();
newUser.setId(4);
newUser.setUserPKey(new UserPKey("First Name", "Last Name"));
userRepository.save(newUser);
users = userRepository.findAll();
users.stream().forEach(u -> System.out.println(u));
System.out.println("---------------------------------------------------------------------");
System.out.println();
newUser.setUserPKey(new UserPKey("First Name", "Last"));
userRepository.save(newUser);
users = userRepository.findAll();
users.stream().forEach(u -> System.out.println(u));
System.out.println("---------------------------------------------------------------------");
System.out.println();
userRepository.delete(newUser);
users = userRepository.findAll();
users.stream().forEach(u -> System.out.println(u));
//System.out.println("---------------------------------------------------------------------");
//System.out.println();
System.out.println("=====================================================================");
}
}Testing Spring Boot Composite Primary Key
By executing the above main class I am testing the example app, where I am fetching existing data, creating a new user data, deleting data.
=====================================================================
User [id=4, userPKey=CompositePKey [firstName=First Name, lastName=Last Name]]
User [id=3, userPKey=CompositePKey [firstName=Arup, lastName=Roy]]
User [id=1, userPKey=CompositePKey [firstName=Soumitra, lastName=Roy]]
User [id=2, userPKey=CompositePKey [firstName=Liton, lastName=Sarkar]]
---------------------------------------------------------------------
User [id=1, userPKey=CompositePKey [firstName=Soumitra, lastName=Roy]]
---------------------------------------------------------------------
User [id=4, userPKey=CompositePKey [firstName=First Name, lastName=Last Name]]
User [id=3, userPKey=CompositePKey [firstName=Arup, lastName=Roy]]
User [id=1, userPKey=CompositePKey [firstName=Soumitra, lastName=Roy]]
User [id=2, userPKey=CompositePKey [firstName=Liton, lastName=Sarkar]]
---------------------------------------------------------------------
User [id=4, userPKey=CompositePKey [firstName=First Name, lastName=Last]]
User [id=4, userPKey=CompositePKey [firstName=First Name, lastName=Last Name]]
User [id=3, userPKey=CompositePKey [firstName=Arup, lastName=Roy]]
User [id=1, userPKey=CompositePKey [firstName=Soumitra, lastName=Roy]]
User [id=2, userPKey=CompositePKey [firstName=Liton, lastName=Sarkar]]
---------------------------------------------------------------------
User [id=4, userPKey=CompositePKey [firstName=First Name, lastName=Last Name]]
User [id=3, userPKey=CompositePKey [firstName=Arup, lastName=Roy]]
User [id=1, userPKey=CompositePKey [firstName=Soumitra, lastName=Roy]]
User [id=2, userPKey=CompositePKey [firstName=Liton, lastName=Sarkar]]
=====================================================================Hope you got an idea how to create composite primary key in Spring Boot application.