DEV Community

Pavan K Jadda
Pavan K Jadda

Posted on

Search data across Multiple Columns using Spring Data JPA

This blog post explains the process to search for data across multiple columns in a table using Spring Data JPA.

https://miro.medium.com/max/2000/1*ojOjEV1SwR2G8NE8hD14ig.jpeg

Technologies

  1. Spring Boot 2.4.x
  2. Spring Data JPA 2.4.x
  3. Angular 11.x
  4. MySql

There are 3 different ways to search for data across multiple columns in a table using Spring Data JPA

  1. Using Example Matcher
  2. Using Specification
  3. Using Query Let’s take an example of Employee and Project tables. Create view EmployeeProjectView (mapped in database as employee_project_view) that joins employee and project tables and returns them in a single view
package com.pj.multicolumnsearch.domain;

import lombok.Data;
import org.springframework.data.annotation.Immutable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;

/**
 * @author Pavan Jadda
 */
@Data
@Entity
@Immutable
@Table(name = "employee_project_view")
public class EmployeeProjectView implements Serializable
{
    private static final long serialVersionUID = 1916548443504880237L;

    @Id
    @Column(name = "employee_id")
    private Long employeeId;

    @Column(name = "last_name")
    private String lastName;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "project_id")
    private Long projectId;

    @Column(name = "project_name")
    private String projectName;

    @Column(name = "project_budget")
    private Double projectBudget;

    @Column(name = "project_location")
    private String projectLocation;

}

Enter fullscreen mode Exit fullscreen mode

Example Matcher

According to Spring Docs

Query by Example (QBE) is a user-friendly querying technique with a simple interface. It allows dynamic query creation and does not require you to write queries that contain field names

The Query by Example API consists of three parts:

  1. Probe: The actual example of a domain object with populated fields.

  2. ExampleMatcher: The ExampleMatcher carries details on how to match particular fields. It can be reused across multiple Examples.

  3. Example: An Example consists of the probe and the ExampleMatcher. It is used to create the query.

@Override
public Page<EmployeeProjectView> findEmployeeProjectsExampleMatcher(EmployeeRequestDTO employeeRequestDTO)
    {
        /* Build Search object */
        EmployeeProjectView employeeProjectView=new EmployeeProjectView();
        employeeProjectView.setEmployeeId(employeeRequestDTO.getEmployeeId());
        employeeProjectView.setLastName(employeeRequestDTO.getFilterText());
        employeeProjectView.setFirstName(employeeRequestDTO.getFilterText());
        try
        {
            employeeProjectView.setProjectId(Long.valueOf(employeeRequestDTO.getFilterText()));
            employeeProjectView.setProjectBudget(Double.valueOf(employeeRequestDTO.getFilterText()));
        }
        catch (Exception e)
        {
            log.debug("Supplied filter text is not a Number");
        }
        employeeProjectView.setProjectName(employeeRequestDTO.getFilterText());
        employeeProjectView.setProjectLocation(employeeRequestDTO.getFilterText());

        /* Build Example and ExampleMatcher object */
        ExampleMatcher customExampleMatcher = ExampleMatcher.matchingAny()
                .withMatcher("firstName", ExampleMatcher.GenericPropertyMatchers.contains().ignoreCase())
                .withMatcher("lastName", ExampleMatcher.GenericPropertyMatchers.contains().ignoreCase())
                .withMatcher("projectId", ExampleMatcher.GenericPropertyMatchers.contains().ignoreCase())
                .withMatcher("projectName", ExampleMatcher.GenericPropertyMatchers.contains().ignoreCase())
                .withMatcher("projectLocation", ExampleMatcher.GenericPropertyMatchers.contains().ignoreCase())
                .withMatcher("projectBudget", 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()));
    }

Enter fullscreen mode Exit fullscreen mode

So let’s create EmployeeProjectView object and copy the user entered search values received from UI to it.

Then build ExampleMatcher to match any one of the requested values

So let’s create EmployeeProjectView object and copy the user entered search values received from UI to it.

Then build ExampleMatcher to match any one of the requested values
Enter fullscreen mode Exit fullscreen mode

then build Example from ExampleMatcher

Example<EmployeeProjectView> employeeExample= Example.of(employeeProjectView, customExampleMatcher);
Enter fullscreen mode Exit fullscreen mode

then use this Example to search for employee projects

/* Get employees based on search criteria*/
return employeeProjectViewRepository.findAll(employeeExample, PageRequest.of(employeeRequestDTO.getCurrentPageNumber(),
      employeeRequestDTO.getPageSize(), Sort.by(employeeRequestDTO.getSortColumnName()).descending()));
Enter fullscreen mode Exit fullscreen mode

Specification

According to Spring Docs

JPA Specification uses Criteria API to define and build queries programmatically. By writing a criteria, you define the where clause of a query for a domain class. To support specifications, you can extend your repository interface with the JpaSpecificationExecutor interface

public interface EmployeeRepository extends CrudRepository<Customer, Long>, JpaSpecificationExecutor 
{ }
Enter fullscreen mode Exit fullscreen mode

Specifications can easily be used to build an extensible set of predicates on top of an entity that then can be combined and used with JpaRepository without the need to declare a query (method) for every needed combination

/**
 * Builds and return specification object that filters data based on search string
 *
 * @param employeeRequestDTO Employee Projects Request DTO object
 *
 * @return Specification with Employee Id and Filter Text
 */
private Specification<EmployeeProjectView> getSpecification(EmployeeRequestDTO employeeRequestDTO)
{
    //Build Specification with Employee Id and Filter Text
    return (root, criteriaQuery, criteriaBuilder) ->
    {
        criteriaQuery.distinct(true);
        //Predicate for Employee Id
        Predicate predicateForEmployee = criteriaBuilder.equal(root.get("employeeId"), employeeRequestDTO.getEmployeeId());

        if (isNotNullOrEmpty(employeeRequestDTO.getFilterText()))
        {
            //Predicate for Employee Projects data
            Predicate predicateForData = criteriaBuilder.or(
                    criteriaBuilder.like(root.get("firstName"), "%" + employeeRequestDTO.getFilterText() + "%"),
                    criteriaBuilder.like(root.get("lastName"), "%" + employeeRequestDTO.getFilterText() + "%"),
                    criteriaBuilder.like(root.get("projectId").as(String.class), "%" + employeeRequestDTO.getFilterText() + "%"),
                    criteriaBuilder.like(root.get("projectName"), "%" + employeeRequestDTO.getFilterText() + "%"),
                    criteriaBuilder.like(root.get("projectBudget").as(String.class), "%" + employeeRequestDTO.getFilterText() + "%"),
                    criteriaBuilder.like(root.get("projectLocation"), "%" + employeeRequestDTO.getFilterText() + "%"));

            //Combine both predicates
            return criteriaBuilder.and(predicateForEmployee, predicateForData);
        }
        return criteriaBuilder.and(predicateForEmployee);
    };
}
Enter fullscreen mode Exit fullscreen mode

We can build the Predicate using the Employee Id and Filter Text. Predicate allows to specify one mandatory condition and many optional conditions. In this case we need to match Employee Id and then rest of the columns with matching string.

First create first predicate to match employee Id

Predicate predicateForEmployee = criteriaBuilder.equal( root.get("employeeId"), employeeRequestDTO.getEmployeeId());
Enter fullscreen mode Exit fullscreen mode

then create second predicate to match all columns with search text

//Predicate for Employee Projects data
  Predicate predicateForData = criteriaBuilder.or(
      criteriaBuilder.like(root.get("firstName"), "%" + employeeRequestDTO.getFilterText() + "%"),
      criteriaBuilder.like(root.get("lastName"), "%" + employeeRequestDTO.getFilterText() + "%"),
      criteriaBuilder.like(root.get("projectId").as(String.class), "%" + employeeRequestDTO.getFilterText() + "%"),
      criteriaBuilder.like(root.get("projectName"), "%" + employeeRequestDTO.getFilterText() + "%"),
      criteriaBuilder.like(root.get("projectBudget").as(String.class), "%" + employeeRequestDTO.getFilterText() + "%"),
      criteriaBuilder.like(root.get("projectLocation"), "%" + employeeRequestDTO.getFilterText() + "%"));
Enter fullscreen mode Exit fullscreen mode

then combine both predicates

/** Combine both predicates   */
return criteriaBuilder.and(predicateForEmployee, predicateForData);
Enter fullscreen mode Exit fullscreen mode

And refer this getSpecification() method in employeeProjectViewRepository.findAll() method

@Override
public Page<EmployeeProjectView> findEmployeeProjectsBySpecification(EmployeeRequestDTO employeeRequestDTO)
{
  return employeeProjectViewRepository.findAll(getSpecification(employeeRequestDTO), PageRequest.of(employeeRequestDTO.getCurrentPageNumber(), employeeRequestDTO.getPageSize(),
      Sort.by(isNotNullOrEmpty(employeeRequestDTO.getSortDirection()) ? Sort.Direction.fromString(employeeRequestDTO.getSortDirection()) : Sort.Direction.DESC, employeeRequestDTO.getSortColumnName())));
}
Enter fullscreen mode Exit fullscreen mode

Query

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

public interface EmployeeProjectViewRepository extends JpaRepository<EmployeeProjectView, Long>,  JpaSpecificationExecutor<EmployeeProjectView>
{
    @Query(value = "SELECT e FROM EmployeeProjectView as e WHERE e.employeeId=:employeeId and (:inputString is null or e.lastName like %:inputString% ) and " +
            "(:inputString is null or e.firstName like %:inputString%) and (:inputString is null or concat(e.projectId,'') like %:inputString%) and " +
            " (:inputString is null or e.projectName like %:inputString%) and  (:inputString is null or concat(e.projectBudget,'') like %:inputString%) and "+
            " (:inputString is null or e.projectLocation like %:inputString%)"
    )
    Page<EmployeeProjectView> findAllByInputString(Long employeeId, String inputString, Pageable pageable);
}

Enter fullscreen mode Exit fullscreen mode

As shown above, we can use Query annotation to match employeeId parameter to Employee Id and Search Text(inputString) parameter to match rest of the columns. When inputString is not null, it will be compared against all columns and results are combined.

Run the Project

  • Clone the repository

  • First bring the MySql database online using docker-compose file. This will start the MySql database and creates demodb database as well inserts data based on init.sql file

$ docker-compose  -f src/main/resources/docker-compose.yml up
Enter fullscreen mode Exit fullscreen mode
  • Start the Spring boot application by running MultiColumnSearchSpringDataJpaApplication class

  • Navigate to Angular web application directory

$ cd src/webapp
Enter fullscreen mode Exit fullscreen mode
  • Install all dependencies
$ npm install
Enter fullscreen mode Exit fullscreen mode
  • Start the angular application
$ ng serve --watch
Enter fullscreen mode Exit fullscreen mode

If you get any error like

The user specified as a definer (‘’@’’) does not exist
Enter fullscreen mode Exit fullscreen mode

Connect to Mysql database drop the view employee_project_view and create it again

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;
Enter fullscreen mode Exit fullscreen mode

Now that we have seen all, which approach to use? Here is my recommendation

  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.

Code uploaded to Github for reference

Discussion (0)