Thursday 26 March 2020

Spring boot jpa: Sort by an embeddedId field

Using '.' operator we can specify sorting functionality on embeddedId fields.
@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;

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

@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;

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

As you see above snippet ProjectId is a composite key for the  entity Project. I can specify sorting on organization field like below.

Sort sort = new Sort(Direction.ASC, Arrays.asList("projectId.organization"));

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


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 org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;

import com.sample.app.entity.Project;
import com.sample.app.repository.ProjectRepository;

@SpringBootApplication
public class App {

 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", "IT", 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("Advanced Search Engine", "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));

   Sort sort = new Sort(Direction.ASC, Arrays.asList("projectId.organization"));

   Iterable<Project> projects = projectRepository.findAll(sort);

   for (Project pjt : projects) {
    System.out.println(pjt);
   }

  };
 }
}

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>sortByEmbeddedId</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-01-10 09:47:30.215, active=y, description=To chat with people]
Project [projectId=ProjectId [projectName=CRM, organization=IT Services], startedTime=2020-01-10 09:47:30.215, active=y, description=Customer Relationship management]
Project [projectId=ProjectId [projectName=Cabin Control Engine, organization=IT Services], startedTime=2020-01-10 09:47:30.215, active=y, description=To main cabin temperature and pressure]
Project [projectId=ProjectId [projectName=Advanced Search Engine, organization=Research Labs], startedTime=2020-01-10 09:47:30.215, active=y, description=Search based on user profile]

As you see above snippet, you can observe projects are returned by the ascending order of organisation they belongs to.

You can download complete working application from this link.





Previous                                                    Next                                                    Home

No comments:

Post a Comment