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;
......
......
}
How to query on embeddedId property using query method?
List<Project> findByProjectIdProjectName(String projectName);
Here Project has an ProjectId with a projectName. In that case, the method creates the x.projectId.projectName property traversal. The resolution algorithm starts by interpreting the entire part (ProjectIdProjectName) as the property and checks the domain class for a property with that name (uncapitalized). If the algorithm succeeds, it uses that property. If not, the algorithm splits up the source at the camel-case parts from the right side into a head and a tail and tries to find the corresponding property — in our example, ProjectIdProject and Code. If the algorithm finds a property with that head, it takes the tail and continues building the tree down from there, splitting the tail up in the way just described. If the first split does not match, the algorithm moves the split point to the left (ProjectId, ProjectName) and continues.
Problem with above approach
Although this should work for most cases, it is possible for the algorithm to select the wrong property. Suppose the Project class has an ProjectIdProject property as well. The algorithm would match in the first split round already, choose the wrong property, and fail.
How to resolve the ambiguity?
Use _ inside your method name to manually define traversal points.
Example
List<Project> findByProjectId_ProjectName(String projectName);
List<Project> findByProjectId_Organization(String organization);
List<Project> findByProjectId_ProjectNameAndProjectId_Organization(String projectName, String organization);
Find the below working application.
Step 1: Create new maven project ‘query-method-on-embeddedId-demo’.
Step 2: Update pom.xml with maven dependencies.
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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.sample.app</groupId>
<artifactId>query-method-on-embeddedId-demo</artifactId>
<version>1</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.4.0</version>
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
</dependency>
</dependencies>
</project>
Step 3: 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=false spring.jpa.properties.hibernate.format_sql=false ## 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 spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true
Step 4: Define Project and ProjectId entities.
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();
}
}
Step 5: Define ProjectRepository.
ProjectRepository.java
package com.sample.app.repository;
import java.util.List;
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> {
List<Project> findByProjectId_ProjectName(String projectName);
List<Project> findByProjectId_Organization(String organization);
List<Project> findByProjectId_ProjectNameAndProjectId_Organization(String projectName, String organization);
}
Step 6: Define main application class.
App.java
package com.sample.app;
import java.sql.Timestamp;
import java.util.Arrays;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import com.sample.app.entity.Project;
import com.sample.app.repository.ProjectRepository;
@SpringBootApplication
public class App {
public void printProjects(Iterable<Project> pjts, String msg) {
System.out.println(msg);
for (Project pjt : pjts) {
System.out.println(pjt.getProjectId().getProjectName() + ", " + pjt.getProjectId().getOrganization());
}
System.out.println();
}
public static void main(String args[]) {
SpringApplication.run(App.class, args);
}
@Bean
public CommandLineRunner demo(ProjectRepository projectRepository) {
return (args) -> {
Timestamp timeStamp = new Timestamp(System.currentTimeMillis());
Project pjt1 = new Project("Chat Server", "Aero", timeStamp, "y", "To chat with people");
Project pjt2 = new Project("Cabin Control Engine", "IT Services", timeStamp, "y",
"To main cabin temperature and pressure");
Project pjt3 = new Project("Chat Server", "Research Labs", timeStamp, "y", "Search based on user profile");
Project pjt4 = new Project("CRM", "IT Services", timeStamp, "y", "Customer Relationship management");
projectRepository.saveAll(Arrays.asList(pjt1, pjt2, pjt3, pjt4));
Iterable<Project> projects = projectRepository.findAll();
printProjects(projects, "All Projects");
projects = projectRepository.findByProjectId_Organization("IT Services");
printProjects(projects, "All Projects in the organization IT Services");
projects = projectRepository.findByProjectId_ProjectName("Chat Server");
printProjects(projects, "All Projects with name Chat Server");
projects = projectRepository.findByProjectId_ProjectNameAndProjectId_Organization("Chat Server", "Aero");
printProjects(projects, "All Projects with name Chat Server and organization Aero");
};
}
}
Total project structure looks like below.
Run App.java, you will see below messages in console.
All Projects Chat Server, Aero Cabin Control Engine, IT Services Chat Server, Research Labs CRM, IT Services All Projects in the organization IT Services CRM, IT Services Cabin Control Engine, IT Services All Projects with name Chat Server Chat Server, Aero Chat Server, Research Labs All Projects with name Chat Server and organization Aero Chat Server, Aero
You can download complete working application from below link.
https://github.com/harikrishna553/springboot/tree/master/jpa/query-method-on-embeddedId-demo
No comments:
Post a Comment