Search data across Multiple Columns using Spring Data JPA

Image by qimono from Pixbay

Technologies

  1. Spring Boot 2.4.x
  2. Spring Data JPA 2.4.x
  3. Angular 11.x
  4. MySql
  1. Using Example Matcher
  2. Using Specification
  3. Using Query
EmployeeProjectView

Example Matcher

According to Spring Docs

  1. Probe: The actual example of a domain object with populated fields.
findEmployeeProjectsExampleMatcher()
ExampleMatcher customExampleMatcher = ExampleMatcher.matchingAny().withMatcher("firstName",
ExampleMatcher.GenericPropertyMatchers.contains().ignoreCase())
....
Example<EmployeeProjectView> employeeExample= Example.of(employeeProjectView, customExampleMatcher);
/* Get employees based on search criteria*/
return employeeProjectViewRepository.findAll(employeeExample, PageRequest.of(employeeRequestDTO.getCurrentPageNumber(),
employeeRequestDTO.getPageSize(), Sort.by(employeeRequestDTO.getSortColumnName()).descending()));

Specification

According to Spring Docs

public interface EmployeeRepository extends CrudRepository<Customer, Long>, JpaSpecificationExecutor 
{ }
getSpecification() method
First Predicate
Second Predicate
/** Combine both predicates   */
return criteriaBuilder.and(predicateForEmployee, predicateForData);

Query

The last way of implementing multi column search is using the @Query annotation.

Search by Query

Run the Project

  1. Clone the repository
$ docker-compose  -f src/main/resources/docker-compose.yml up
$ cd src/webapp
$ npm install
$ ng serve --watch
Employee Projects
The user specified as a definer (‘’@’’) does not exist
drop view if exists employee_project_view;
create view employee_project_view as
select distinct employee_id,
first_name,
last_name,
project_id,
name as project_name,
location as project_location,
budget as project_budget
from employee,
employee_project,
project
where employee.id = employee_project.employee_id
and employee_project.project_id = project.id;
  1. For simple queries that can not be solved regular JPA methods, use Query annotation
  2. For any other requirement that does need mandatory matching and optional matching(meaning inputString needs to be matched with all columns including employeeId)
  3. For requirements that need mandatory matching and optional matching, like the one shown in this article, use Specification(preferred) or Query.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Pavan Jadda

Pavan Jadda

Full Stack Developer(Java, Angular and React)