Sunday, 26 April 2020

Spring JPA: Composite key example

A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table

For example, take ‘projects’ table.
PROJECT_NAME
ORGANIZATION
STARTED_TIME
ACTIVE

DESCRIPTION
















In the above table two columns ‘PROJECT_NAME’ and ‘ORGANIZATION’ together form a composite key.

How to design composite key scenario?
Step 1: Create a class that has properties projectName and organization. Annotate this class with @Embeddable annotation.

@Embeddable
public class ProjectId implements Serializable {

  private static final long serialVersionUID = 1L;

  @Column(name = "PROJECT_NAME")
  private String projectName;

  @Column(name = "ORGANIZATION")
  private String organization;

  ......
  ......
}

Step 2: Create Project class that contain ProjectId as embedded id.

@Entity
@Table(name = "projects")
public class Project {

  @EmbeddedId
  private ProjectId projectId;

  @Column(name = "STARTED_TIME")
  private Timestamp startedTime;

  @Column(name = "ACTIVE")
  private String active;

  @Column(name = "DESCRIPTION")
  private String description;

  ......
  ......
}

Find the below working application.

ProjectId.java
package com.sample.app.entity;

import java.io.Serializable;

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

@Embeddable
public class ProjectId implements Serializable {

  private static final long serialVersionUID = 1L;

  @Column(name = "PROJECT_NAME")
  private String projectName;

  @Column(name = "ORGANIZATION")
  private String organization;

  public ProjectId() {

  }

  public ProjectId(String projectName, String organization) {
    this.projectName = projectName;
    this.organization = organization;
  }

  public String getProjectName() {
    return projectName;
  }

  public void setProjectName(String projectName) {
    this.projectName = projectName;
  }

  public String getOrganization() {
    return organization;
  }

  public void setOrganization(String organization) {
    this.organization = organization;
  }

  @Override
  public String toString() {
    StringBuilder builder = new StringBuilder();
    builder.append("ProjectId [projectName=");
    builder.append(projectName);
    builder.append(", organization=");
    builder.append(organization);
    builder.append("]");
    return builder.toString();
  }

}

Project.java
package com.sample.app.entity;

import java.sql.Timestamp;

import javax.persistence.Column;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.Table;

@Entity
@Table(name = "projects")
public class Project {

  @EmbeddedId
  private ProjectId projectId;

  @Column(name = "STARTED_TIME")
  private Timestamp startedTime;

  @Column(name = "ACTIVE")
  private String active;

  @Column(name = "DESCRIPTION")
  private String description;

  public Project() {

  }

  public Project(String projectName, String organization, Timestamp startedTime, String active, String description) {
    ProjectId projectId = new ProjectId(projectName, organization);
    this.projectId = projectId;
    this.startedTime = startedTime;
    this.active = active;
    this.description = description;
  }

  public ProjectId getProjectId() {
    return projectId;
  }

  public void setProjectId(ProjectId projectId) {
    this.projectId = projectId;
  }

  public Timestamp getStartedTime() {
    return startedTime;
  }

  public void setStartedTime(Timestamp startedTime) {
    this.startedTime = startedTime;
  }

  public String getActive() {
    return active;
  }

  public void setActive(String active) {
    this.active = active;
  }

  public String getDescription() {
    return description;
  }

  public void setDescription(String description) {
    this.description = description;
  }

  @Override
  public String toString() {
    StringBuilder builder = new StringBuilder();
    builder.append("Project [projectId=");
    builder.append(projectId.toString());
    builder.append(", startedTime=");
    builder.append(startedTime);
    builder.append(", active=");
    builder.append(active);
    builder.append(", description=");
    builder.append(description);
    builder.append("]");
    return builder.toString();
  }

}

ProjectRepository.java
package com.sample.app.repository;

import org.springframework.data.repository.PagingAndSortingRepository;

import com.sample.app.entity.Project;
import com.sample.app.entity.ProjectId;

public interface ProjectRepository extends PagingAndSortingRepository<Project, ProjectId> {

}

Create application.properties file under src/main/resources folder.

application.properties
logging.level.root=WARN
logging.level.org.hibernate=ERROR

## H2 specific properties
spring.h2.console.enabled=true
spring.h2.console.path=/h2

spring.datasource.url=jdbc:h2:file:~/db/myOrg.db;DB_CLOSE_ON_EXIT=FALSE;DB_CLOSE_DELAY=-1;

spring.datasource.username=krishna
spring.datasource.password=password123

spring.datasource.driverClassName=org.h2.Driver

## JPA specific properties
# Creates the schema, destroying previous data.
spring.jpa.hibernate.ddl-auto=create

spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

spring.jpa.show-sql=true
#spring.jpa.properties.hibernate.format_sql=true

## Database connection pooling properties
# Number of ms to wait before throwing an exception if no connection is available.
spring.datasource.max-wait=10000

# Maximum number of active connections that can be allocated from this pool at the same time.
spring.datasource.tomcat.max-active=10
spring.datasource.tomcat.max-idle=5
spring.datasource.tomcat.min-idle=3

pom.xml
<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.sample.app</groupId>
  <artifactId>compositekey-demo</artifactId>
  <version>1</version>


  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.1.6.RELEASE</version>
  </parent>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>

  <dependencies>

    <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-jpa -->
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>


    <!-- https://mvnrepository.com/artifact/com.h2database/h2 -->
    <dependency>
      <groupId>com.h2database</groupId>
      <artifactId>h2</artifactId>
    </dependency>

  </dependencies>
</project>

Total project structure looks like below.

Run App.java, you will see below messages in console.
Project [projectId=ProjectId [projectName=Chat Server, organization=IT], startedTime=2020-03-19 14:54:25.658, active=y, description=To chat with people]
Project [projectId=ProjectId [projectName=CRM, organization=IT Services], startedTime=2020-03-19 14:54:25.658, active=y, description=Customer Relationship management]
Project [projectId=ProjectId [projectName=Cabin Control Engine, organization=IT Services], startedTime=2020-03-19 14:54:25.658, active=y, description=To main cabin temperature and pressure]
Project [projectId=ProjectId [projectName=Advanced Search Engine, organization=Research Labs], startedTime=2020-03-19 14:54:25.658, active=y, description=Search based on user profile]

You can download complete working application from this link.


Previous                                                    Next                                                    Home

No comments:

Post a Comment