Spring JPA - Make Dynamic “Where” Using Predicate and Criteria
On this article, I assume you have understand about Spring Data JPA and always using it. Based on my experience, sometimes we need to get data specify by column. Let Says we have Student table on our DB.
maybe, we need get List by address only, and on repository we write
List<Student> findByAddress(String address)
and, maybe we need to get List By Age,
List<Student> findByAge(int age),
and maybe we need to combine condition to get List, I think this traditional method not efficient if table have more column and you need dynamic where condition.
Therefore, I suggest you to using Predicate and Criteria for it to make dynamic where condition.
Make Spring Boot Project
Go to spring initializr and create new project with dependencies:
- Spring Data Jpa
- Spring Data Web
- MySQL Connector
Create Entity
@Entity
public class Student {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private int age;
private String address;
public Student() {
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", address='" + address + '\'' +
'}';
}
}Create Repository
dont forget to add JpaSpecificationExecutor<Student>
public interface StudentRepository extends JpaRepository<Student, Long>, JpaSpecificationExecutor<Student> {
}Create Search Spesication
create enum SearchOperation
public enum SearchOperation {
GREATER_THAN,
LESS_THAN,
GREATER_THAN_EQUAL,
LESS_THAN_EQUAL,
NOT_EQUAL,
EQUAL,
MATCH,
MATCH_END,
}and then create SearchCriteria
public class SearchCriteria {
private String key;
private Object value;
private SearchOperation operation;
public SearchCriteria(String key, Object value, SearchOperation operation) {
this.key = key;
this.value = value;
this.operation = operation;
}
public String getKey() {
return key;
}
public void setKey(String key) {
this.key = key;
}
public Object getValue() {
return value;
}
public void setValue(Object value) {
this.value = value;
}
public SearchOperation getOperation() {
return operation;
}
public void setOperation(SearchOperation operation) {
this.operation = operation;
}
}then, create GenericSpesification, I make it as generic and usefull if you need other tables using dynamic where.
public class GenericSpesification<T> implements Specification<T> {
private static final long serialVersionUID = 1900581010229669687L;
private List<SearchCriteria> list;
public GenericSpesification() {
this.list = new ArrayList<>();
}
public void add(SearchCriteria criteria) {
list.add(criteria);
}
@Override
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
//create a new predicate list
List<Predicate> predicates = new ArrayList<>();
//add 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.LESS_THAN_EQUAL)) {
predicates.add(builder.lessThanOrEqualTo(
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()));
} else if (criteria.getOperation().equals(SearchOperation.EQUAL)) {
predicates.add(builder.equal(
root.get(criteria.getKey()), criteria.getValue()));
} 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_END)) {
predicates.add(builder.like(
builder.lower(root.get(criteria.getKey())),
criteria.getValue().toString().toLowerCase() + "%"));
}
}
return builder.and(predicates.toArray(new Predicate[0]));
}
}Create Service and Implementation
public interface StudentService {
List<Student> findAll();
List<Student> findStudentsWithPredicate();
}and create implementation
@Service
public class StudentServiceImpl implements StudentService {
private static final String ADDRESS = "address";
private static final String ADDRESS_VALUE = "BANDUNG";
private static final String AGE = "age";
private static final int AGE_VALUE = 25;
@Autowired
private StudentRepository studentRepository;
@Override
public List<Student> findAll() {
return studentRepository.findAll();
}
@Override
public List<Student> findStudentsWithPredicate() {
/*
* find Student which stay in BANDUNG and age greather than 25 years old
*/
GenericSpesification genericSpesification = new GenericSpesification<Student>();
genericSpesification.add(new SearchCriteria(ADDRESS, ADDRESS_VALUE, SearchOperation.EQUAL));
genericSpesification.add(new SearchCriteria(AGE, AGE_VALUE, SearchOperation.GREATER_THAN));
return studentRepository.findAll(genericSpesification);
}
}on findStudentsWithPredicate we create GenericSpesictaion object and create dynamic where passing to studentRepository.
StudentController
@RestController
@RequestMapping("/student")
public class StudentController {
@Autowired
private StudentService studentService;
@GetMapping("/all")
public List<Student> findAll() {
return studentService.findAll();
}
@GetMapping("/withDynamicSearch")
public List<Student> withDynamicSearch() {
return studentService.findStudentsWithPredicate();
}
}Dont forget, to write properties
# MySQL
spring.datasource.url=jdbc:mysql://localhost:3306/demo_dynamic_where?createDatabaseIfNotExist=true
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
server.port=7789Time For Test
Go to browser and access http://localhost:7789/student/all, and the result be like this
and access also this url http://localhost:7789/student/withDynamicSearch
the result suitable with criteria that we write on student service implementation.
you can find on my github repo https://github.com/teten777/spring-data-jpa-dynamic-where
If it was interesting or helpful to you, please do press the 👏 clap button and help others find this story too or if you wanna talk internally with me , reach me in https://linktr.ee/teten_nugraha.
