JPA Specification, Dynamic queries

1. Simple Specification:

Student class:

package az.etibarli.jpa_specification.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import lombok.Data;
import lombok.RequiredArgsConstructor;
import lombok.experimental.FieldNameConstants;

import java.math.BigDecimal;
import java.time.LocalDate;

@Data
@Entity
@FieldNameConstants
@RequiredArgsConstructor
public class Student {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String surname;
private String pinCode;
private Double gpa;
private LocalDate birthdate;
private BigDecimal scholarship;

}


Student repository:

package az.etibarli.jpa_specification.repository;

import az.etibarli.jpa_specification.entity.Student;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

public interface StudentRepository extends JpaRepository<Student, Long>, JpaSpecificationExecutor<Student> {
}


Search for either name or surname:

package az.etibarli.jpa_specification;

import az.etibarli.jpa_specification.entity.Student;
import az.etibarli.jpa_specification.repository.StudentRepository;
import az.etibarli.jpa_specification.search.SearchSpecification;
import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.criteria.CriteriaQuery;
import jakarta.persistence.criteria.Predicate;
import jakarta.persistence.criteria.Root;
import lombok.RequiredArgsConstructor;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.domain.Specification;

import java.util.ArrayList;
import java.util.List;

@SpringBootApplication
@RequiredArgsConstructor
public class JpaSpecificationApplication implements CommandLineRunner {

private final StudentRepository repository;

public static void main(String[] args) {
SpringApplication.run(JpaSpecificationApplication.class, args);
}

@Override
public void run(String... args) throws Exception {
Specification<Student> etibarli = search("Parvin", "Etibarli");
List<Student> all1 = repository.findAll(etibarli);
all1.forEach(System.out::println);
}

public Specification<Student> search(String name, String surname) {
List<Predicate> list = new ArrayList<>();
return new Specification<Student>() {
@Override
public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
if (name != null) {
Predicate namePredicate = builder.equal(root.get(Student.Fields.name), name);
list.add(namePredicate);
}
if(surname != null) {
Predicate lastNamePredicate = builder.equal(root.get(Student.Fields.surname), surname);
list.add(lastNamePredicate);
}
return builder.or(list.toArray(new Predicate[0]));
// return builder.and(list.toArray(new Predicate[0]));
}
};
}
}


Search for one key:

package az.etibarli.jpa_specification;

import az.etibarli.jpa_specification.entity.Student;
import az.etibarli.jpa_specification.repository.StudentRepository;
import az.etibarli.jpa_specification.search.SearchSpecification;
import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.criteria.CriteriaQuery;
import jakarta.persistence.criteria.Predicate;
import jakarta.persistence.criteria.Root;
import lombok.RequiredArgsConstructor;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.domain.Specification;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

@SpringBootApplication
@RequiredArgsConstructor
public class JpaSpecificationApplication implements CommandLineRunner {

private final StudentRepository repository;

public static void main(String[] args) {
SpringApplication.run(JpaSpecificationApplication.class, args);
}

@Override
public void run(String... args) throws Exception {
Specification<Student> search = search("1");
repository.findAll(search).forEach(System.out::println);
}

public Specification<Student> search(String key) {
List<Predicate> list = new ArrayList<>();
return new Specification<Student>() {
@Override
public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
if (key != null) {
Predicate namePredicate = builder.equal(root.get(Student.Fields.name), key);
list.add(namePredicate);
Predicate lastNamePredicate = builder.equal(root.get(Student.Fields.surname), key);
list.add(lastNamePredicate);
Predicate pinCodePredicate = builder.equal(root.get(Student.Fields.pinCode), key);
list.add(pinCodePredicate);
Predicate jpaPredicate = builder.greaterThanOrEqualTo(root.get(Student.Fields.gpa), Double.valueOf(key));
list.add(jpaPredicate);
Predicate scholarshipPredicate = builder.lessThanOrEqualTo(root.get(Student.Fields.scholarship), new BigDecimal(key));
list.add(scholarshipPredicate);
}
return builder.or(list.toArray(new Predicate[0]));
// return builder.and(list.toArray(new Predicate[0]));
}
};
}
}


2. Specification with one to one, one to many and many to many relationship:

package az.etibarli.jpa_specification.entity;

import jakarta.persistence.*;
import lombok.Data;
import lombok.RequiredArgsConstructor;
import lombok.experimental.FieldNameConstants;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.LinkedHashSet;
import java.util.Set;

@Data
@Entity
@FieldNameConstants
@RequiredArgsConstructor
public class Student {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String surname;
private String pinCode;
private Double gpa;
private LocalDate birthdate;
private BigDecimal scholarship;

@OneToOne(cascade = CascadeType.ALL, orphanRemoval = true)
@JoinColumn(name = "address_id")
private Address address;

@OneToMany(mappedBy = "student", cascade = CascadeType.ALL, orphanRemoval = true, fetch = FetchType.EAGER)
private Set<Book> books = new LinkedHashSet<>();

@ManyToMany(fetch = FetchType.EAGER)
@JoinTable(name = "Student_teachers",
joinColumns = @JoinColumn(name = "student_id"),
inverseJoinColumns = @JoinColumn(name = "teachers_id"))
private Set<Teacher> teachers = new LinkedHashSet<>();

}


package az.etibarli.jpa_specification.entity;

import com.fasterxml.jackson.annotation.JsonIgnore;
import jakarta.persistence.*;
import lombok.Getter;
import lombok.RequiredArgsConstructor;
import lombok.Setter;
import lombok.ToString;
import lombok.experimental.FieldNameConstants;

@Getter
@Setter
@Entity
@ToString
@FieldNameConstants
@RequiredArgsConstructor
public class Address {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String country;
private String city;
private String addressLine;

@JsonIgnore
@ToString.Exclude
@OneToOne(mappedBy = "address", orphanRemoval = true)
private Student student;

}


package az.etibarli.jpa_specification.entity;

import com.fasterxml.jackson.annotation.JsonIgnore;
import jakarta.persistence.*;
import lombok.Getter;
import lombok.RequiredArgsConstructor;
import lombok.Setter;
import lombok.ToString;
import lombok.experimental.FieldNameConstants;

@Getter
@Setter
@Entity
@ToString
@FieldNameConstants
@RequiredArgsConstructor
public class Book {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String author;
private String name;
private Integer year;

@ManyToOne
@JsonIgnore
@ToString.Exclude
@JoinColumn(name = "student_id")
private Student student;

}


package az.etibarli.jpa_specification.entity;

import com.fasterxml.jackson.annotation.JsonIgnore;
import jakarta.persistence.*;
import lombok.Getter;
import lombok.RequiredArgsConstructor;
import lombok.Setter;
import lombok.ToString;
import lombok.experimental.FieldNameConstants;

import java.util.LinkedHashSet;
import java.util.Set;

@Getter
@Setter
@Entity
@ToString
@FieldNameConstants
@RequiredArgsConstructor
public class Teacher {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String surname;
private String grade;
private String gender;

@JsonIgnore
@ToString.Exclude
@ManyToMany(mappedBy = "teachers")
private Set<Student> students = new LinkedHashSet<>();

}


package az.etibarli.jpa_specification;

import az.etibarli.jpa_specification.entity.Address;
import az.etibarli.jpa_specification.entity.Book;
import az.etibarli.jpa_specification.entity.Student;
import az.etibarli.jpa_specification.entity.Teacher;
import az.etibarli.jpa_specification.repository.StudentRepository;
import jakarta.persistence.criteria.*;
import lombok.RequiredArgsConstructor;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.domain.Specification;

import java.util.ArrayList;
import java.util.List;

@SpringBootApplication
@RequiredArgsConstructor
public class JpaSpecificationApplication implements CommandLineRunner {

private final StudentRepository repository;

public static void main(String[] args) {
SpringApplication.run(JpaSpecificationApplication.class, args);
}

@Override
public void run(String... args) throws Exception {
repository.findAll(search("Ayaz")).forEach(System.out::println);
}

public Specification<Student> search(String key) {
List<Predicate> list = new ArrayList<>();
return new Specification<Student>() {
@Override
public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
if (key != null) {
Predicate namePredicate = builder.equal(root.get(Student.Fields.name), key);
list.add(namePredicate);
Predicate lastNamePredicate = builder.equal(root.get(Student.Fields.surname), key);
list.add(lastNamePredicate);
Predicate pinCodePredicate = builder.equal(root.get(Student.Fields.pinCode), key);
list.add(pinCodePredicate);
// Predicate jpaPredicate = builder.greaterThanOrEqualTo(root.get(Student.Fields.gpa), Double.valueOf(key));
// list.add(jpaPredicate);
// Predicate scholarshipPredicate = builder.lessThanOrEqualTo(root.get(Student.Fields.scholarship), new BigDecimal(key));
// list.add(scholarshipPredicate);
// one to one
Join<Student, Address> studentAddressJoin = root.join(Student.Fields.address, JoinType.INNER);
Predicate countryPredicate = builder.equal(studentAddressJoin.get(Address.Fields.country), key);
list.add(countryPredicate);
Predicate cityPredicate = builder.equal(studentAddressJoin.get(Address.Fields.city), key);
list.add(cityPredicate);
Predicate addressLinePredicate = builder.equal(studentAddressJoin.get(Address.Fields.addressLine), key);
list.add(addressLinePredicate);

// one to many
Join<Student, Book> studentBookJoin = root.join(Student.Fields.books, JoinType.INNER);
Predicate authorPredicate = builder.like(builder.lower(studentBookJoin.get(Book.Fields.author)),
"%" + key + "%");
list.add(authorPredicate);
Predicate bookNamePredicate = builder.like(builder.lower(studentBookJoin.get(Book.Fields.name)), "%" + key + "%");
list.add(bookNamePredicate);

// many to many
Join<Student, Teacher> studentTeacherJoin = root.join(Student.Fields.teachers, JoinType.INNER);
Predicate teacherNamePredicate = builder.equal(studentTeacherJoin.get(Teacher.Fields.name), key);
list.add(teacherNamePredicate);
}
return builder.or(list.toArray(new Predicate[0]));
// return builder.and(list.toArray(new Predicate[0]));
}
};
}
}














3. Here a bit complex Specification:

package az.etibarli.jpa_specification.search;

import az.etibarli.jpa_specification.entity.Student;
import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.criteria.CriteriaQuery;
import jakarta.persistence.criteria.Predicate;
import jakarta.persistence.criteria.Root;
import org.springframework.data.jpa.domain.Specification;

import java.util.ArrayList;
import java.util.List;

public class SearchSpecification implements Specification<Student> {


@Override
public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
List<Predicate> predicates = new ArrayList<>();
predicates.add(builder.equal(root.get("name"), "Parvin"));
return builder.and(predicates.toArray(new Predicate[0]));
}

}


@Override
public void run(String... args) throws Exception {
SearchSpecification specification = new SearchSpecification();
repository.findAll(specification).forEach(System.out::println);
}























2. JpaSpecificationExecutor:

package com.example.cascade_types_and_n_plus_one_problem.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import lombok.*;
import org.hibernate.Hibernate;

import java.util.Objects;

@Getter
@Setter
@ToString
@RequiredArgsConstructor
@Entity
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String surname;
private Integer age;
private String email;

@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || Hibernate.getClass(this) != Hibernate.getClass(o)) return false;
Employee employee = (Employee) o;
return id != null && Objects.equals(id, employee.id);
}

@Override
public int hashCode() {
return getClass().hashCode();
}
}
package com.example.cascade_types_and_n_plus_one_problem.repository;

import com.example.cascade_types_and_n_plus_one_problem.entity.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

public interface EmployeeRepository extends JpaRepository<Employee, Long>, JpaSpecificationExecutor<Employee> {
}
package com.example.cascade_types_and_n_plus_one_problem.criteria;

public enum SearchOperation {
GREATER_THAN,
LESS_THAN,
GREATER_THAN_EQUAL,
NOT_EQUAL,
EQUAL,
MATCH,
MATCH_START,
MATCH_END,
IN,
NOT_IN
}
package com.example.cascade_types_and_n_plus_one_problem.criteria;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class SearchCriteria {
private String key;
private Object value;
private SearchOperation operation;
}
package com.example.cascade_types_and_n_plus_one_problem.criteria;

import com.example.cascade_types_and_n_plus_one_problem.entity.Employee;
import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.criteria.CriteriaQuery;
import jakarta.persistence.criteria.Predicate;
import jakarta.persistence.criteria.Root;
import org.springframework.data.jpa.domain.Specification;

import java.util.ArrayList;
import java.util.List;

public class EmployeeSpecification implements Specification<Employee> {

private List<SearchCriteria> list = new ArrayList<>();

public void add(SearchCriteria criteria) {
list.add(criteria);
}

@Override
public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
// Create a new Predicate list
List<Predicate> predicates = new ArrayList<>();

// add criteria to predicates
for (SearchCriteria criteria : list) {
if (criteria.getOperation().equals(SearchOperation.GREATER_THAN)) {
predicates.add(builder.greaterThan(root.get(criteria.getKey()), criteria.getValue().toString()));
} else if (criteria.getOperation().equals(SearchOperation.LESS_THAN)) {
predicates.add(builder.lessThan(root.get(criteria.getKey()), criteria.getValue().toString()));
} else if (criteria.getOperation().equals(SearchOperation.GREATER_THAN_EQUAL)) {
predicates.add(builder.greaterThanOrEqualTo(root.get(criteria.getKey()), criteria.getValue().toString()));
} else if (criteria.getOperation().equals(SearchOperation.NOT_EQUAL)) {
predicates.add(builder.notEqual(root.get(criteria.getKey()), criteria.getValue().toString()));
} else if (criteria.getOperation().equals(SearchOperation.EQUAL)) {
predicates.add(builder.equal(root.get(criteria.getKey()), criteria.getValue().toString()));
} else if (criteria.getOperation().equals(SearchOperation.MATCH)) {
predicates.add(builder.like(builder.lower(root.get(criteria.getKey())),
"%" + criteria.getValue().toString().toLowerCase() + "%"));
} else if (criteria.getOperation().equals(SearchOperation.MATCH_START)) {
predicates.add(builder.like(builder.lower(root.get(criteria.getKey())),
criteria.getValue().toString().toLowerCase() + "%"));
} else if (criteria.getOperation().equals(SearchOperation.MATCH_END)) {
predicates.add(builder.like(builder.lower(root.get(criteria.getKey())),
"%" + criteria.getValue().toString().toLowerCase()));
} else if (criteria.getOperation().equals(SearchOperation.IN)) {
predicates.add(builder.in(root.get(criteria.getKey())).value(criteria.getValue()));
} else if (criteria.getOperation().equals(SearchOperation.NOT_IN)) {
predicates.add(builder.not(root.get(criteria.getKey())).in(criteria.getValue()));
}
}
return builder.and(predicates.toArray(new Predicate[0]));
}
}
package com.example.cascade_types_and_n_plus_one_problem;

import com.example.cascade_types_and_n_plus_one_problem.criteria.EmployeeSpecification;
import com.example.cascade_types_and_n_plus_one_problem.criteria.SearchCriteria;
import com.example.cascade_types_and_n_plus_one_problem.criteria.SearchOperation;
import com.example.cascade_types_and_n_plus_one_problem.entity.Employee;
import com.example.cascade_types_and_n_plus_one_problem.repository.EmployeeRepository;
import lombok.RequiredArgsConstructor;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import java.util.List;

@SpringBootApplication
@RequiredArgsConstructor
public class CascadeTypesAndNPlusOneProblemApplication implements CommandLineRunner {

private final EmployeeRepository employeeRepository;

public static void main(String[] args) {
SpringApplication.run(CascadeTypesAndNPlusOneProblemApplication.class, args);
}

@Override
public void run(String... args) throws Exception {
System.out.println("Start-------------------------------------------");

// Course course = new Course();
// course.setName("ms9");
//
// Set<Student> set = new HashSet<>();
// for (int i = 0; i < 10; i++) {
// Student student = new Student();
// student.setName("Parvin " + i);
// student.setCourse(course);
// set.add(student);
// }
// course.setStudents(set);
// courseRepository.save(course);

// Course course = courseRepository.findById(1L).get();
// System.out.println(course);

EmployeeSpecification specification = new EmployeeSpecification();
specification.add(SearchCriteria.builder()
.key("age")
.value(20)
.operation(SearchOperation.GREATER_THAN)
.build());
specification.add(SearchCriteria.builder()
.key("age")
.value(30)
.operation(SearchOperation.LESS_THAN)
.build());
specification.add(SearchCriteria.builder()
.key("name")
.value("in")
.operation(SearchOperation.MATCH_END)
.build());

List<Employee> all = employeeRepository.findAll(specification);
all.stream().forEach(System.out::println);
System.out.println("Finish------------------------------------------");
}
}


2. Jpa Specification with one to one, one to many and many to many relationship:

This is almost the best example:

https://gitlab.com/etibarliparvin/specification

JPA Specification



----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


package az.azerconnect.sales.numberdiscovery.search;

import az.azerconnect.sales.numberdiscovery.entity.MsisdnBkcEntity;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.util.ObjectUtils;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.util.ArrayList;
import java.util.List;

public class BkcSpecification implements Specification<MsisdnBkcEntity> {

private List<SearchCriteria> criteriaList = new ArrayList<>();

public void search(List<String> levelIds, String prefix, String msisdn) {
if (!ObjectUtils.isEmpty(levelIds))
criteriaList.add(new SearchCriteria(MsisdnBkcEntity.Fields.levelId, levelIds, SearchOperation.IN));
if (!ObjectUtils.isEmpty(prefix))
criteriaList.add(new SearchCriteria(MsisdnBkcEntity.Fields.prefix, prefix, SearchOperation.EQUAL));
if (!ObjectUtils.isEmpty(msisdn))
criteriaList.add(new SearchCriteria(MsisdnBkcEntity.Fields.msisdn, msisdn, SearchOperation.MATCH));
}

@Override
public Predicate toPredicate(Root<MsisdnBkcEntity> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
List<Predicate> predicates = new ArrayList<>();
for (SearchCriteria criteria : criteriaList) {
if (criteria.getOperation1().equals(SearchOperation.EQUAL)) {
predicates.add(builder.equal(root.get(criteria.getKey()), criteria.getValue1()));
} else if (criteria.getOperation1().equals(SearchOperation.MATCH)) {
predicates.add(builder.like(builder.lower(root.get(criteria.getKey())),
"%" + criteria.getValue1().toString().toLowerCase() + "%"));
} else if (criteria.getOperation1().equals(SearchOperation.IN)) {
CriteriaBuilder.In<String> inClause = builder.in(root.get(criteria.getKey()));
List<String> value = (List<String>) criteria.getValue1();
for (String level : value) {
inClause.value(level);
}
predicates.add(inClause);
}
}
return builder.and(predicates.toArray(new Predicate[0]));
}
}










Комментарии

Популярные сообщения из этого блога

Lesson1: JDK, JVM, JRE

SE_21_Lesson_11: Inheritance, Polymorphism

SE_21_Lesson_9: Initialization Blocks, Wrapper types, String class