How to create Composite Primary Key – Spring JPA + Spring Boot + MySQL

In the tutorial, JavaSampleApproach will introduce step-by-step to create Composite Primary Key with @Embeddable annotation.

Related articles:
How to configure Spring JPA One to Many Relationship – SpringBoot

I. Technologies

– Java 1.8
– Maven 3.3.9
– Spring Tool Suite – Version 3.8.1.RELEASE
– Spring Boot: 1.5.3.RELEASE
– MySql database

II. Overview

The tutorial will create a sample project with 2 entities: Customer entity (mapping with customer table) and OrderDetail entity (mapping with order_detail table). And they have One-to-Many relationship.
customer table has a composite primary key: {customer_id, brandcode}.
order_detail table has a primary key: order_id and a foreign key: {customer_id, brandcode}.

composite primary key - one-to-many relationship

To define a Composite Primary Key, we use javax.persistence.Embeddable annotation. @Embeddable is used to indicate a class whose instances are stored as intrinsic part of the owning entity.

Customer class:

@Entity
public class Customer {
	
	@EmbeddedId
	private CustomerId customerId;
	
	private String company;
	private String name;
	
	@OneToMany(mappedBy = "customer", cascade = CascadeType.ALL)
    private Set orderDetails;

    ...
}

CustomerId class:

@Embeddable
public class CustomerId implements Serializable{
	
	private static final long serialVersionUID = 1L;

	@Column(name = "customer_id")
	private int customerId;
	
	@Column(name = "brandcode")
	private String brandcode;

    ...
}

OrderDetail class:

@Entity
@Table(name="order_detail")
public class OrderDetail {
	@Id
	@Column(name="order_id")
	private String orderId;
	
    @ManyToOne
    @JoinColumns({
        @JoinColumn(name = "brandcode", referencedColumnName = "brandcode"),
        @JoinColumn(name = "customer_id", referencedColumnName = "customer_id")
    })
    private Customer customer;

    ...
}

III. Practice

Step to do:
– Create Spring Boot project
– Create entities class
– Create JPA Repositories & Services
– Create a test client
– Configure JPA and datasource
– Run & check results

1. Create Spring Boot project

– Using SpringToolSuite, create a SpringBoot project. Then add needed dependencies:


	org.springframework.boot
	spring-boot-starter-data-jpa



	mysql
	mysql-connector-java
	runtime



	org.json
	json

2. Create entities class

– Create Customer entity:

package com.grokonez.compositeprimarykey.model;

import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.OneToMany;

import org.json.JSONArray;
import org.json.JSONObject;

@Entity
public class Customer {
	
	@EmbeddedId
	private CustomerId customerId;
	
	private String company;
	private String name;
	
	@OneToMany(mappedBy = "customer", cascade = CascadeType.ALL)
    private Set orderDetails;
	
	public Customer(){}
	
	public Customer(CustomerId customerId, String company, String name){
		this.customerId = customerId;
		this.company = company;
		this.name = name;
	}
	
	public Customer(CustomerId customerId, String company, String name, Set orderDetails){
		this.customerId = customerId;
		this.company = company;
		this.name = name;
		this.orderDetails = orderDetails;
	}
	
	public void setCustomerId(CustomerId customerId){
		this.customerId = customerId;
	}
	
	public CustomerId getCustomerId(){
		return this.customerId;
	}
	
	public void setCompany(String company){
		this.company = company;
	}
	
	public String getCompany(){
		return this.company;
	}
	
	public void setName(String name){
		this.name = name;
	}
	
	public String getName(){
		return this.name;
	}
	
	public void setOrderDetails(Set orderDetails){
		this.orderDetails = orderDetails;
	}
	
	public Set getOrderDetails(){
		return this.orderDetails;
	}
	
	public String toString(){
        String info = "";
        JSONObject jsonInfo = new JSONObject();
        jsonInfo.put("customerId",this.customerId.getCustomerId());
        jsonInfo.put("brandcode", this.customerId.getBrandcode());
        jsonInfo.put("company", this.getCompany());
        jsonInfo.put("name", this.getName());
        
        JSONArray orders = new JSONArray();
        if(this.orderDetails != null){
            this.orderDetails.forEach(order->{
                JSONObject orderInfo = new JSONObject();
                orderInfo.put("orderId-" + order.getOrderId() , order);
                orders.put(orderInfo);
            });
        }
        jsonInfo.put("products", orders);
        info = jsonInfo.toString();
        return info;

	}
}

– Create CustomerId class:

package com.grokonez.compositeprimarykey.model;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Embeddable;

@Embeddable
public class CustomerId implements Serializable{
	
	private static final long serialVersionUID = 1L;

	@Column(name = "customer_id")
	private int customerId;
	
	@Column(name = "brandcode")
	private String brandcode;
	
	public CustomerId(){};
	
	public CustomerId(int customerId, String brandcode){
		this.customerId = customerId;
		this.brandcode = brandcode;
	}
	
	public void setCustomerId(int customerId){
		this.customerId = customerId;
	}
	
	public int getCustomerId(){
		return this.customerId;
	}
	
	public void setBrandcode(String brandcode){
		this.brandcode = brandcode;
	}
	
	public String getBrandcode(){
		return this.brandcode;
	}
}

– Create OrderDetail entity:

package com.grokonez.compositeprimarykey.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinColumns;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

@Entity
@Table(name="order_detail")
public class OrderDetail { 

	@Id
	@Column(name="order_id")
	private String orderId;
	
    @ManyToOne
    @JoinColumns({
        @JoinColumn(name = "brandcode", referencedColumnName = "brandcode"),
        @JoinColumn(name = "customer_id", referencedColumnName = "customer_id")
    })
    private Customer customer;
    
    private String product;
    
    public OrderDetail(){
    }
    
    public OrderDetail(String orderId, String product, Customer customer){
    	this.orderId = orderId;
    	this.product = product;
    	this.customer = customer;
    }
    
    public void setOrderId(String orderId){
    	this.orderId = orderId;
    }
    
    public String getOrderId(){
    	return this.orderId;
    }
    
    public void setCustomer(Customer customer){
    	this.customer = customer;
    }
    
    public Customer getCustomer(){
    	return this.customer;
    }
    
    public void setProductName(String product){
    	this.product = product;
    }
    
    public String getProduct(){
    	return this.product;
    }
    
    public String toString(){
    	return String.format("['product': %s]", this.product);
    }
}
3. Create JPA Repositories & Services
3.1 Create JPA repositories

– CustomerRepository:

package com.grokonez.compositeprimarykey.repository;

import org.springframework.data.jpa.repository.JpaRepository;

import com.grokonez.compositeprimarykey.model.Customer;
import com.grokonez.compositeprimarykey.model.CustomerId;

public interface CustomerRepository extends JpaRepository{
}

– OrderRepository:

package com.grokonez.compositeprimarykey.repository;

import org.springframework.data.jpa.repository.JpaRepository;

import com.grokonez.compositeprimarykey.model.OrderDetail;

public interface OrderRepository extends JpaRepository{
}
3.2 Create Services

– CustomerServices:

package com.grokonez.compositeprimarykey.service;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.grokonez.compositeprimarykey.model.Customer;
import com.grokonez.compositeprimarykey.repository.CustomerRepository;

@Service
public class CustomerServices {
	@Autowired
	CustomerRepository customerRepository;
	
	public void deleteAll(){
		customerRepository.deleteAll();
	}
	
	public void save(Customer customer){
		customerRepository.save(customer);
		customerRepository.flush();
	}
	
	@Transactional
	public void showAll(){
		List custs = customerRepository.findAll();
		custs.forEach(System.out::println);
	}
}

– OrderServices:

package com.grokonez.compositeprimarykey.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.grokonez.compositeprimarykey.model.OrderDetail;
import com.grokonez.compositeprimarykey.repository.OrderRepository;

@Service
public class OrderServices {
	
	@Autowired
	OrderRepository orderRepository;
	
	public void save(OrderDetail order){
		orderRepository.save(order);
	}
	
	public void deleteAll(){
		orderRepository.deleteAll();
	}
}
4. Create a test client

Use 2 services CustomerServices & OrderServices:

@Autowired
CustomerServices customerService;

@Autowired
OrderServices orderServices;

Implement 3 functions:
deleteAll()
saveData()
showAll()

Full Sourcecode:

package com.grokonez.compositeprimarykey;

import java.util.Arrays;
import java.util.HashSet;
import java.util.Set;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import com.grokonez.compositeprimarykey.model.Customer;
import com.grokonez.compositeprimarykey.model.CustomerId;
import com.grokonez.compositeprimarykey.model.OrderDetail;
import com.grokonez.compositeprimarykey.service.CustomerServices;
import com.grokonez.compositeprimarykey.service.OrderServices;

@SpringBootApplication
public class SpringDataCompositePrimaryKeyApplication implements CommandLineRunner{

	@Autowired
	CustomerServices customerService;
	
	@Autowired
	OrderServices orderServices;
	
	public static void main(String[] args) {
		SpringApplication.run(SpringDataCompositePrimaryKeyApplication.class, args);
	}

	@Override
	public void run(String... args) throws Exception {
		deleteAll();
		saveData();
		showAll();
	}
	
	public void deleteAll(){
		System.out.println("===============Delete All Customers===============");
		
		orderServices.deleteAll();
		customerService.deleteAll();
	}
	
	public void saveData(){
		System.out.println("===============Storing Customers===============");
		
		// ===============Create customers===============
		// 1. Jack
		CustomerId jackId = new CustomerId(1000, "azc");
		Customer jack = new Customer(jackId, "A & Z", "Jack");
		
		OrderDetail jackIphoneOrder = new OrderDetail("001", "IPhone 7", jack);
		OrderDetail jackIPadMiniOrder = new OrderDetail("002", "IPad Mini 2", jack);
		
		Set jackOrderDetails = new HashSet(Arrays.asList(jackIphoneOrder, jackIPadMiniOrder));
		
		jack.setOrderDetails(jackOrderDetails);
		
		// 2. Mary
		CustomerId maryId = new CustomerId(2000, "mkl");
		Customer mary = new Customer(maryId, "Fashion Company", "Mary");
		
		OrderDetail maryNote7Order = new OrderDetail("003", "Samsung Galaxy Note 7", mary);
		
		Set maryOrderDetails = new HashSet(Arrays.asList(maryNote7Order));
		
		mary.setOrderDetails(maryOrderDetails);
		
		// ===============Saving to DB===============
		
		customerService.save(jack);
		customerService.save(mary);
		
	}
	
	public void showAll(){
		System.out.println("===============Show All Customers' Info===============");
		customerService.showAll();
	}
}

5. Configure JPA and Datasource

– Open application.properties file, configure spring.datasource & spring.jpa:

spring.datasource.url=jdbc:mysql://localhost:3306/testdb
spring.datasource.username=root
spring.datasource.password=12345
 
spring.jpa.generate-ddl=true
spring.jpa.show-sql=false
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

– If set spring.jpa.generate-ddl is true, 2 tables: customer & order_detail will be created automatically by Hibernate.
Otherwise, if set spring.jpa.generate-ddl is false, we can use below SQL scripts to create the tables:

drop table if exists testdb.customer;
create table customer(
	customer_id INT NOT NULL AUTO_INCREMENT,
	brandcode VARCHAR(25) NOT NULL,
	company VARCHAR(25) NOT NULL,
	name VARCHAR(25) NOT NULL,
	PRIMARY KEY (customer_id, brandcode)
)

drop talbe if exists testdb.order_detail;
create table order_detail(
	order_id VARCHAR(25) NOT NULL AUTO_INCREMENT,
	customer_id INT NOT NULL,
	brandcode VARCHAR(25) NOT NULL,
	product VARCHAR(100) NOT NULL,
	PRIMARY KEY (order_id),
	CONSTRAINT FK_order_detail FOREIGN KEY (customer_id,brandcode)
    REFERENCES customer(customer_id,brandcode)
)
6. Run & check results

Build & Run the project with SpringBoot App mode.
-> Logs:

===============Delete All Customers===============
2017-05-03 21:38:04.716  INFO 4388 --- [           main] o.h.h.i.QueryTranslatorFactoryInitiator  : HHH000397: Using ASTQueryTranslatorFactory
===============Storing Customers===============
===============Show All Customers' Info===============
{"customerId":1000,"name":"Jack","company":"A & Z","brandcode":"azc","products":[{"orderId-002":"['product': IPad Mini 2]"},{"orderId-001":"['product': IPhone 7]"}]}
{"customerId":2000,"name":"Mary","company":"Fashion Company","brandcode":"mkl","products":[{"orderId-003":"['product': Samsung Galaxy Note 7]"}]}

Database tables:

customer table data

composite primary key - embeddable class - customer table

order_detail table data

composite primary key - embeddable class - orderdetails table

III. SourceCode

SpringDataCompositePrimaryKey



By grokonez | May 3, 2017.


Related Posts


2 thoughts on “How to create Composite Primary Key – Spring JPA + Spring Boot + MySQL”

  1. Thank you so much, I was trying a lot of ways and none was right. Finally I could find it here and now everything are fine in my project with CustomerId configuration, it was the rignt way.

Got Something To Say:

Your email address will not be published. Required fields are marked *

*