'root.get({propertyName}).get({subPropertyName})' is used to query on embeddedId using specification.
For example, Project entity has ProjectId as embeddedId.
@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;
......
......
}
To get the path for embedded fields projectName, organization we should use below logic.
Path expression = null;
if ("projectName".equals(property)) {
expression = root.get("projectId").get("projectName");
} else if ("organization".equals(property)) {
expression = root.get("projectId").get("organization");
} else {
expression = root.get(property);
}
Similarly to sort on embedded id fields, we should access the nested fields using '.' notation like below.
Sort.Order.asc("projectId.projectName")
Sort.Order.asc("projectId.organization")
Sort.Order.desc("projectId.projectName")
Sort.Order.desc("projectId.organization")
Find the below working application.
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 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));
};
}
}
SwaggerConfig.java
package com.sample.app.config;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.context.annotation.Bean;
import org.springframework.stereotype.Component;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;
@Component
@EnableAutoConfiguration
@EnableSwagger2
public class SwaggerConfig {
@Bean
public Docket userApi() {
return new Docket(DocumentationType.SWAGGER_2).apiInfo(apiInfo()).select().paths(PathSelectors.any())
.apis(RequestHandlerSelectors.basePackage("com.sample.app.controller")).build();
}
private ApiInfo apiInfo() {
return new ApiInfoBuilder().title("Query builder").description("Query builder using spring specification")
.version("2.0").build();
}
}
ProjectController.java
package com.sample.app.controller;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;
import com.sample.app.entity.Project;
import com.sample.app.model.SearchQuery;
import com.sample.app.service.ProjectService;
import io.swagger.annotations.ApiOperation;
@RestController
public class ProjectController {
@Autowired
private ProjectService projectService;
@ApiOperation(value = "Projects Filter", notes = "Get projects by search criteria")
@PostMapping(value = "/projects", produces = MediaType.APPLICATION_JSON_VALUE)
public ResponseEntity<List<Project>> getEmployees(@RequestBody SearchQuery searchQuery) {
List<Project> emps = projectService.findAll(searchQuery);
return new ResponseEntity<>(emps, HttpStatus.OK);
}
}
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;
}
}
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;
}
}
SearchFilter.java
package com.sample.app.model;
public class SearchFilter {
private String property;
private String operator;
private Object value;
public String getProperty() {
return property;
}
public void setProperty(String property) {
this.property = property;
}
public String getOperator() {
return operator;
}
public void setOperator(String operator) {
this.operator = operator;
}
public Object getValue() {
return value;
}
public void setValue(Object value) {
this.value = value;
}
}
SearchQuery.java
package com.sample.app.model;
import java.util.List;
public class SearchQuery {
private List<SearchFilter> searchFitler;
private int pageNumber;
private int pageSize;
private SortOrder sortOrder;
public List<SearchFilter> getSearchFitler() {
return searchFitler;
}
public void setSearchFitler(List<SearchFilter> searchFitler) {
this.searchFitler = searchFitler;
}
public int getPageNumber() {
return pageNumber;
}
public void setPageNumber(int pageNumber) {
this.pageNumber = pageNumber;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public SortOrder getSortOrder() {
return sortOrder;
}
public void setSortOrder(SortOrder sortOrder) {
this.sortOrder = sortOrder;
}
}
SortOrder.java
package com.sample.app.model;
import java.util.List;
public class SortOrder {
private List<String> ascendingOrder;
private List<String> descendingOrder;
public List<String> getAscendingOrder() {
return ascendingOrder;
}
public void setAscendingOrder(List<String> ascendingOrder) {
this.ascendingOrder = ascendingOrder;
}
public List<String> getDescendingOrder() {
return descendingOrder;
}
public void setDescendingOrder(List<String> descendingOrder) {
this.descendingOrder = descendingOrder;
}
}
ProjectRepository.java
package com.sample.app.repository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.repository.PagingAndSortingRepository;
import com.sample.app.entity.Project;
import com.sample.app.entity.ProjectId;
public interface ProjectRepository extends JpaSpecificationExecutor<Project>, PagingAndSortingRepository<Project, ProjectId>{
}
ProjectService.java
package com.sample.app.service;
import java.util.List;
import com.sample.app.entity.Project;
import com.sample.app.model.SearchQuery;
public interface ProjectService {
public List<Project> findAll(SearchQuery searchQuery);
}
ProjectServiceImpl.java
package com.sample.app.service.impl;
import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import com.sample.app.entity.Project;
import com.sample.app.model.SearchQuery;
import com.sample.app.model.SortOrder;
import com.sample.app.repository.ProjectRepository;
import com.sample.app.service.ProjectService;
import com.sample.app.specification.SpecificationUtil;
@Service
public class ProjectServiceImpl implements ProjectService {
@Autowired
private ProjectRepository projectRepository;
@Override
public List<Project> findAll(SearchQuery searchQuery) {
Specification<Project> spec = SpecificationUtil.bySearchQuery(searchQuery, Project.class);
PageRequest pageRequest = getPageRequest(searchQuery);
Page<Project> page = projectRepository.findAll(spec, pageRequest);
return page.getContent();
}
private PageRequest getPageRequest(SearchQuery searchQuery) {
int pageNumber = searchQuery.getPageNumber();
int pageSize = searchQuery.getPageSize();
List<Sort.Order> orders = new ArrayList<>();
SortOrder sortOrder = searchQuery.getSortOrder();
if (sortOrder == null) {
return PageRequest.of(pageNumber, pageSize, null);
}
List<String> ascProps = searchQuery.getSortOrder().getAscendingOrder();
if (ascProps != null && !ascProps.isEmpty()) {
for (String property : ascProps) {
if ("projectName".equals(property)) {
orders.add(Sort.Order.asc("projectId.projectName"));
} else if ("organization".equals(property)) {
orders.add(Sort.Order.asc("projectId.organization"));
} else {
orders.add(Sort.Order.asc(property));
}
}
}
List<String> descProps = searchQuery.getSortOrder().getDescendingOrder();
if (descProps != null && !descProps.isEmpty()) {
for (String property : descProps) {
if ("projectName".equals(property)) {
orders.add(Sort.Order.desc("projectId.projectName"));
} else if ("organization".equals(property)) {
orders.add(Sort.Order.desc("projectId.organization"));
} else {
orders.add(Sort.Order.desc(property));
}
}
}
Sort sort = Sort.by(orders);
return PageRequest.of(pageNumber, pageSize, sort);
}
}
SpecificationUtil.java
package com.sample.app.specification;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Path;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import org.springframework.data.jpa.domain.Specification;
import com.sample.app.model.SearchFilter;
import com.sample.app.model.SearchQuery;
public class SpecificationUtil {
public static <T> Specification<T> bySearchQuery(SearchQuery searchQuery, Class<T> clazz) {
return (Root<T> root, CriteriaQuery<?> query, CriteriaBuilder criterailBuilder) -> {
List<Predicate> predicates = new ArrayList<>();
List<SearchFilter> searchFilters = searchQuery.getSearchFitler();
if (searchFilters == null || searchFilters.isEmpty()) {
return null;
}
for (final SearchFilter searchFilter : searchFilters) {
String property = searchFilter.getProperty();
Path expression = null;
if ("projectName".equals(property)) {
expression = root.get("projectId").get("projectName");
} else if ("organization".equals(property)) {
expression = root.get("projectId").get("organization");
} else {
expression = root.get(property);
}
switch (searchFilter.getOperator()) {
case "=":
predicates.add(criterailBuilder.equal(expression, searchFilter.getValue()));
break;
case "LIKE":
predicates.add(criterailBuilder.like(expression, "%" + searchFilter.getValue() + "%"));
break;
case "IN":
predicates.add(criterailBuilder.in(expression).value(searchFilter.getValue()));
break;
case ">":
predicates.add(criterailBuilder.greaterThan(expression, (Comparable) searchFilter.getValue()));
break;
case "<":
predicates.add(criterailBuilder.lessThan(expression, (Comparable) searchFilter.getValue()));
break;
case ">=":
predicates.add(
criterailBuilder.greaterThanOrEqualTo(expression, (Comparable) searchFilter.getValue()));
break;
case "<=":
predicates
.add(criterailBuilder.lessThanOrEqualTo(expression, (Comparable) searchFilter.getValue()));
break;
case "!":
predicates.add(criterailBuilder.notEqual(expression, searchFilter.getValue()));
break;
case "IsNull":
predicates.add(criterailBuilder.isNull(expression));
break;
case "NotNull":
predicates.add(criterailBuilder.isNotNull(expression));
break;
default:
System.out.println("Predicate is not matched");
throw new IllegalArgumentException(searchFilter.getOperator() + " is not a valid predicate");
}
}
if (predicates.isEmpty()) {
return criterailBuilder.conjunction();
}
return criterailBuilder.and(predicates.toArray(new Predicate[0]));
};
}
}
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>embeddedIdQueryUsingSpecification</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>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.9.2</version>
</dependency>
</dependencies>
</project>
Total project structure looks like below.
Run App.java.
Open the url ‘http://localhost:8080/swagger-ui.html’ in browse to get swagger UI.
Hit /projects API with below payload.
{
"pageNumber": 0,
"pageSize": 10,
"searchFitler": [
{
"operator": "LIKE",
"property": "projectName",
"value": "Engine"
}
],
"sortOrder": {
"ascendingOrder": [
"projectName"
],
"descendingOrder" : [
"organization"
]
}
}
You will receive below response.
[
{
"projectId": {
"projectName": "Advanced Search Engine",
"organization": "Research Labs"
},
"startedTime": "2020-01-09T08:50:27.457+0000",
"active": "y",
"description": "Search based on user profile"
},
{
"projectId": {
"projectName": "Cabin Control Engine",
"organization": "IT Services"
},
"startedTime": "2020-01-09T08:50:27.457+0000",
"active": "y",
"description": "To main cabin temperature and pressure"
}
]
You can download complete working application from this link.
No comments:
Post a Comment