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.
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.
No comments:
Post a Comment