Spring Boot Composite Primary Key Example
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, Spring Boot/Spring Data JPA, Maven 3.9.8, MySQL 8.1.0
Project Setup
You can use the following pom.xml file for your maven based project.
You can refer to the following pom.xml file for your 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.release>22</maven.compiler.release>
</properties>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>4.0.4</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>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<!-- Exclude vintage engine to avoid JUnit 4 conflicts -->
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>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.
package com.roytuts.springboot.datajpa.composite.primarykey.entity;
@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.
package com.roytuts.springboot.datajpa.composite.primarykey.entity;
@Entity
@Table // (name = "user")
public class User {
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.
package com.roytuts.springboot.datajpa.composite.primarykey.repository;
public interface UserRepository extends JpaRepository<User, UserPKey> {
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.
package com.roytuts.springboot.datajpa.composite.primarykey;
@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.
No comments
Leave a comment