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

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

EmployeeProjectView.java

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;

}

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()));
	}

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

ExampleMatcher customExampleMatcher = ExampleMatcher.matchingAny().withMatcher("firstName",
ExampleMatcher.GenericPropertyMatchers.contains().ignoreCase())
....

then build Example from ExampleMatcher

Example<EmployeeProjectView> employeeExample= Example.of(employeeProjectView, customExampleMatcher);

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()));

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 
{ }

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);
	};
}

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());

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() + "%"));

then combine both predicates

/** Combine both predicates   */
return criteriaBuilder.and(predicateForEmployee, predicateForData);

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())));
}

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);
}

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

1. Clone the repository

2. 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

3. Start the Spring boot application by running MultiColumnSearchSpringDataJpaApplication class

4. Navigate to Angular web application directory

$ cd src/webapp

5. Install all dependencies

$ npm install

6. Start the angular application

$ ng serve --watch

7. Go to http://localhost:4200 in the browser to see the table.

If you get any error like

The user specified as a definer (‘’@’’) does not exist

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 row_number() OVER (ORDER BY employee_id ) AS id,
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;

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