In this post, I am going to explain how to join two tables using spring JPA specification.
To demonstrate the application, we are going to use two tables.
a. my_employee
b. my_hobbies
‘my_employee’ table has a one-to-many relationship with ‘my_hobbies’ table, where one employee has zero or more hobbies.
‘my_employee’ table entity is defined as below.
@Entity
@Table(name = "my_employee")
@EntityListeners(AuditingEntityListener.class)
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;
@Column(name = "first_name")
private String firstName;
@Column(name = "last_name")
private String lastName;
@Column(name = "age")
private Integer age;
@OneToMany(cascade = CascadeType.ALL)
@JoinColumn(name = "MY_EMPLOYEE_ID")
@JsonManagedReference
private Set<Hobby> hobbies;
.......
.......
}
‘my_hobbies’ table entity is defined as below.
@Entity
@Table(name = "my_hobbies")
public class Hobby {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID")
private int id;
@Column(name = "HOBBY")
private String hobby;
@JsonBackReference
@ManyToOne
@JoinColumn(name = "MY_EMPLOYEE_ID")
private Employee employee;
......
......
}
As you see JoinColoumn is "MY_EMPLOYEE_ID".
To perform ‘join’ operation using specification,
Step 1: Create an inner join to the specified attribute.
Join<Object, Object> joinParent = root.join("hobbies");
Step 2: Create a path corresponding to the referenced attribute.
Path expression = joinParent.get("hobby");
Step 3: Add the criteria to the predicates. Below predicate check for the hobby "Cricket".
predicates.add(criterailBuilder.equal(expression, "Cricket"));
That’s it you are done.
Find the below working application.
Step 1: Create new maven project ‘jpaSpecToJoinTables’.
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.smaple.app</groupId>
<artifactId>jpaSpecToJoinTables</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>
Step 3: Create package 'com.sample.app.model' and define model classes.
Employee.java
package com.sample.app.model;
import java.util.Set;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EntityListeners;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;
import com.fasterxml.jackson.annotation.JsonManagedReference;
@Entity
@Table(name = "my_employee")
@EntityListeners(AuditingEntityListener.class)
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;
@Column(name = "first_name")
private String firstName;
@Column(name = "last_name")
private String lastName;
@Column(name = "age")
private Integer age;
@OneToMany(cascade = CascadeType.ALL)
@JoinColumn(name = "MY_EMPLOYEE_ID")
@JsonManagedReference
private Set<Hobby> hobbies;
public Employee() {
}
public Employee(String firstName, String lastName, Integer age, Set<Hobby> hobbies) {
this.firstName = firstName;
this.lastName = lastName;
this.age = age;
this.hobbies = hobbies;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Set<Hobby> getHobbies() {
return hobbies;
}
public void setHobbies(Set<Hobby> hobbies) {
this.hobbies = hobbies;
}
}
Hobby.java
package com.sample.app.model;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import com.fasterxml.jackson.annotation.JsonBackReference;
@Entity
@Table(name = "my_hobbies")
public class Hobby {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID")
private int id;
@Column(name = "HOBBY")
private String hobby;
@JsonBackReference
@ManyToOne
@JoinColumn(name = "MY_EMPLOYEE_ID")
private Employee employee;
public Hobby() {
}
public Hobby(String hobby) {
this.hobby = hobby;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getHobby() {
return hobby;
}
public void setHobby(String hobby) {
this.hobby = hobby;
}
public Employee getEmployee() {
return employee;
}
public void setEmployee(Employee employee) {
this.employee = employee;
}
}
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;
}
}
JoinColumnProps.java
package com.sample.app.model;
public class JoinColumnProps {
private String joinColumnName;
private SearchFilter searchFilter;
public String getJoinColumnName() {
return joinColumnName;
}
public void setJoinColumnName(String joinColumnName) {
this.joinColumnName = joinColumnName;
}
public SearchFilter getSearchFilter() {
return searchFilter;
}
public void setSearchFilter(SearchFilter searchFilter) {
this.searchFilter = searchFilter;
}
}
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;
}
}
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;
private List<JoinColumnProps> joinColumnProps;
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;
}
public List<JoinColumnProps> getJoinColumnProps() {
return joinColumnProps;
}
public void setJoinColumnProps(List<JoinColumnProps> joinColumnProps) {
this.joinColumnProps = joinColumnProps;
}
}
Step 4: Create new package ‘com.sample.app.repository’ and define EmployeeRepository interface.
EmployeeRepository.java
package com.sample.app.repository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.repository.PagingAndSortingRepository;
import com.sample.app.model.Employee;
public interface EmployeeRepository extends JpaSpecificationExecutor<Employee>, PagingAndSortingRepository<Employee, Integer>{
}
Step 5: Create package ‘com.sample.app.service’ and define EmployeeService.
EmployeeService.java
package com.sample.app.service;
import java.util.ArrayList;
import java.util.List;
import javax.transaction.Transactional;
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.Component;
import com.sample.app.model.Employee;
import com.sample.app.model.SearchQuery;
import com.sample.app.repository.EmployeeRepository;
import com.smaple.app.jpa.specification.SpecificationUtil;
@Component
public class EmployeeService {
@Autowired
private EmployeeRepository empRepo;
public List<Employee> findAll(SearchQuery searchQuery) {
Specification<Employee> spec = SpecificationUtil.bySearchQuery(searchQuery, Employee.class);
PageRequest pageRequest = getPageRequest(searchQuery);
Page<Employee> page = empRepo.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<>();
List<String> ascProps = searchQuery.getSortOrder().getAscendingOrder();
if (ascProps != null && !ascProps.isEmpty()) {
for (String prop : ascProps) {
orders.add(Sort.Order.asc(prop));
}
}
List<String> descProps = searchQuery.getSortOrder().getDescendingOrder();
if (descProps != null && !descProps.isEmpty()) {
for (String prop : descProps) {
orders.add(Sort.Order.desc(prop));
}
}
Sort sort = Sort.by(orders);
return PageRequest.of(pageNumber, pageSize, sort);
}
@Transactional
public void save(Employee emp) {
empRepo.save(emp);
}
}
Step 6: Create package ‘com.smaple.app.jpa.specification’ and define SpecificationUtil.
SpecificationUtil.java
package com.smaple.app.jpa.specification;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Join;
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.JoinColumnProps;
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<>();
// Add Predicates for tables to be joined
List<JoinColumnProps> joinColumnProps = searchQuery.getJoinColumnProps();
if (joinColumnProps != null && !joinColumnProps.isEmpty()) {
for (JoinColumnProps joinColumnProp : joinColumnProps) {
addJoinColumnProps(predicates, joinColumnProp, criterailBuilder, root);
}
}
List<SearchFilter> searchFilters = searchQuery.getSearchFitler();
if (searchFilters != null && !searchFilters.isEmpty()) {
for (final SearchFilter searchFilter : searchFilters) {
addPredicates(predicates, searchFilter, criterailBuilder, root);
}
}
if (predicates.isEmpty()) {
return criterailBuilder.conjunction();
}
return criterailBuilder.and(predicates.toArray(new Predicate[0]));
};
}
private static <T> void addJoinColumnProps(List<Predicate> predicates, JoinColumnProps joinColumnProp,
CriteriaBuilder criterailBuilder, Root<T> root) {
SearchFilter searchFilter = joinColumnProp.getSearchFilter();
Join<Object, Object> joinParent = root.join(joinColumnProp.getJoinColumnName());
String property = searchFilter.getProperty();
Path expression = joinParent.get(property);
addPredicate(predicates, searchFilter, criterailBuilder, expression);
}
private static <T> void addPredicates(List<Predicate> predicates, SearchFilter searchFilter,
CriteriaBuilder criterailBuilder, Root<T> root) {
String property = searchFilter.getProperty();
Path expression = root.get(property);
addPredicate(predicates, searchFilter, criterailBuilder, expression);
}
private static void addPredicate(List<Predicate> predicates, SearchFilter searchFilter,
CriteriaBuilder criterailBuilder, Path expression) {
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");
}
}
}
Step 7: Create package ‘com.sample.app.controller’ and define EmployeeController.
EmployeeController.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.model.Employee;
import com.sample.app.model.SearchQuery;
import com.sample.app.service.EmployeeService;
import io.swagger.annotations.ApiOperation;
@RestController
public class EmployeeController {
@Autowired
private EmployeeService empService;
@ApiOperation(value = "Employees Filter", notes = "Get employees by search criteria")
@PostMapping(value = "/employees", produces = MediaType.APPLICATION_JSON_VALUE)
public ResponseEntity<List<Employee>> getEmployees(@RequestBody SearchQuery searchQuery) {
List<Employee> emps = empService.findAll(searchQuery);
return new ResponseEntity<>(emps, HttpStatus.OK);
}
}
Step 8: Create package ‘com.sample.app.config’ and define SwaggerConfig.
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();
}
}
Step 9: Define App.java, in com.sample.app package.
App.java
package com.sample.app;
import java.util.HashSet;
import java.util.Set;
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.model.Employee;
import com.sample.app.model.Hobby;
import com.sample.app.repository.EmployeeRepository;
import com.sample.app.service.EmployeeService;
@SpringBootApplication
public class App {
public static void main(String args[]) {
SpringApplication.run(App.class, args);
}
@Bean
public CommandLineRunner demo(EmployeeRepository employeeRepository, EmployeeService empService) {
return (args) -> {
Hobby hobby1 = new Hobby("Cricket");
Hobby hobby2 = new Hobby("Blogging");
Hobby hobby3 = new Hobby("Football");
Hobby hobby4 = new Hobby("Tennis");
Hobby hobby5 = new Hobby("Blogging");
Hobby hobby6 = new Hobby("Cricket");
Hobby hobby7 = new Hobby("Blogging");
Hobby hobby8 = new Hobby("Cricket");
Set<Hobby> hobbies1 = new HashSet<>();
hobbies1.add(hobby1);
hobbies1.add(hobby2);
Set<Hobby> hobbies2 = new HashSet<>();
hobbies2.add(hobby3);
hobbies2.add(hobby4);
Set<Hobby> hobbies3 = new HashSet<>();
hobbies3.add(hobby5);
hobbies3.add(hobby6);
Set<Hobby> hobbies4 = new HashSet<>();
hobbies4.add(hobby7);
hobbies4.add(hobby8);
Employee emp1 = new Employee("Ram", "Gurram", 32, hobbies1);
Employee emp2 = new Employee("Gopi", "Battu", 30, hobbies2);
Employee emp3 = new Employee("Surendra", "Sami", 32, hobbies3);
Employee emp4 = new Employee("Sai", "Praneet", 30, hobbies4);
Employee emp5 = new Employee("Sailu", "PTR", 31, null);
empService.save(emp1);
empService.save(emp2);
empService.save(emp3);
empService.save(emp4);
empService.save(emp5);
for (Employee emp : employeeRepository.findAll()) {
System.out.println(emp);
}
};
}
}
Total project structure looks like below.
Run App.java.
Open swagger url ‘http://localhost:8080/swagger-ui.html’ in browser.
Now you can experiment with the API ‘POST /employees’ in swagger.
Example 1: Get all the employees whose hobby is ‘Football’.
Input Payload
{
"pageNumber": 0,
"pageSize": 10,
"searchFitler": [
],
"sortOrder": {
"ascendingOrder": [
"firstName"
],
"descendingOrder": [
"lastName"
]
},
"joinColumnProps" : [
{
"joinColumnName" : "hobbies",
"searchFilter" : {
"operator": "=",
"property": "hobby",
"value": "Football"
}
}
]
}
Output
[
{
"id": 2,
"firstName": "Gopi",
"lastName": "Battu",
"age": 30,
"hobbies": [
{
"id": 4,
"hobby": "Football"
},
{
"id": 3,
"hobby": "Tennis"
}
]
}
]
Example 2: Get employees whose hobby is "Football" or "Blogging".
{
"pageNumber": 0,
"pageSize": 10,
"searchFitler": [
],
"sortOrder": {
"ascendingOrder": [
"firstName"
],
"descendingOrder": [
"lastName"
]
},
"joinColumnProps" : [
{
"joinColumnName" : "hobbies",
"searchFilter" : {
"operator": "IN",
"property": "hobby",
"value": ["Football", "Blogging"]
}
}
]
}
Output
[
{
"id": 2,
"firstName": "Gopi",
"lastName": "Battu",
"age": 30,
"hobbies": [
{
"id": 4,
"hobby": "Football"
},
{
"id": 3,
"hobby": "Tennis"
}
]
},
{
"id": 1,
"firstName": "Ram",
"lastName": "Gurram",
"age": 32,
"hobbies": [
{
"id": 1,
"hobby": "Cricket"
},
{
"id": 2,
"hobby": "Blogging"
}
]
},
{
"id": 4,
"firstName": "Sai",
"lastName": "Praneet",
"age": 30,
"hobbies": [
{
"id": 7,
"hobby": "Cricket"
},
{
"id": 8,
"hobby": "Blogging"
}
]
},
{
"id": 3,
"firstName": "Surendra",
"lastName": "Sami",
"age": 32,
"hobbies": [
{
"id": 6,
"hobby": "Blogging"
},
{
"id": 5,
"hobby": "Cricket"
}
]
}
]
Example 3: Get all the employees whose age is 32 and hobby is Cricket.
Input
{
"pageNumber": 0,
"pageSize": 10,
"searchFitler": [
{
"operator": "=",
"property": "age",
"value": 32
}
],
"sortOrder": {
"ascendingOrder": [
"firstName"
],
"descendingOrder": [
"lastName"
]
},
"joinColumnProps" : [
{
"joinColumnName" : "hobbies",
"searchFilter" : {
"operator": "=",
"property": "hobby",
"value": "Cricket"
}
}
]
}
Output
[
{
"id": 1,
"firstName": "Ram",
"lastName": "Gurram",
"age": 32,
"hobbies": [
{
"id": 2,
"hobby": "Blogging"
},
{
"id": 1,
"hobby": "Cricket"
}
]
},
{
"id": 3,
"firstName": "Surendra",
"lastName": "Sami",
"age": 32,
"hobbies": [
{
"id": 6,
"hobby": "Blogging"
},
{
"id": 5,
"hobby": "Cricket"
}
]
}
]
You can download the complete working application from the below link.
https://github.com/harikrishna553/springboot/tree/master/jpa/jpaSpecToJoinTables
gr8 awesome dude i never see such easy and simple explanation gr8 work (Y)
ReplyDeleteUse below snippet to add to support not in query.
ReplyDeletecase "NotIn":
predicates.add(criterailBuilder.in(expression).value(searchFilter.getValue()).not());
break;