When managing extensive quantities of data, multi-column search is frequently expected. Assuming that you already have familiarity with Sprint Data JPA, and Hibernate— this is a case study on what to do and where to do when searching for data in a given list of columns. 

‍

Let's say we have a Student table Data Description Language (DDL) as given below.

‍

CREATE TABLE student ( id serial PRIMARY KEY, firstName VARCHAR ( 255 ), lastName VARCHAR ( 255), parentName VARCHAR ( 255 ), grade VARCHAR ( 255 ), section VARCHAR ( 255 ), rollNo INTEGER (15));.

‍

The ask is to create a list by search terms in specific columns: firstName, lastName, parentName… etc., and to combine it with some other conditions to generate the final list. Given a partial data entry, how can we then generate the list we need.

We are going to cover the following use cases: 

‍

Use case — 1:

‍

Searching “sh” in column list [“firstName”]

‍

Use case — 2:

‍

Searching “sh” in column list [“firstName”, “lastName”]

‍

Use case — 3:

‍

Searching “sh” in column list [“firstName”, “lastName”, “parentName”]

‍

We'll use JPA Criteria and Predicate to build a dynamic query with the “where” clause and write a REST API that uses the underlying query to get results. Below is a walkthrough of the code for the solution.

‍

We'll create a project with Springboot Initializr, which contains the following classes along with build.gradle.

‍

1. StudentDAO

2. SearchDTO

3. Service and an Impl class (which contains the actual code)

4. Controller with get endpoint

‍

You can find the source code here:     

‍

GitHub — shoukathmd/dynamic-multi-column-search-with-jpa-criteria

‍Making the Spring Boot project

Go to Spring Initializr and create a new project with dependencies:

‍

• Spring Data JPA

• Spring Web

• PostgreSQL Connector

• Lombok

‍

The build.gradle file looks like this:

   plugins {

       id 'org.springframework.boot' version '2.5.8'

      id 'io.spring.dependency-management' version '1.0.11.RELEASE'

      id 'java'

   }

‍

   group = 'com.spring'

   version =  '0.0.1 - SNAPSHOT'

   sourceCompatibility =  '11'

‍

   configurations {

       compileOnly {

          extendsFrom annotationProcessor

     }

   }

‍

   repositories {

       mavenCentral()

   }

‍

   dependencies {

       Implementation  'org.springframework.boot:spring-boot-starter-data-jpa'

      Implementation  'org.springframework.boot:spring-boot-starter-web'

      compileOnly 'org.projectlombok:lombok'

      runtimeOnly  'org.postgresql:postgresql'

      annotationProcessor  'org.projectlombok:lombok'

      testImplementation  'org.springframework.boot:spring-boot-starter-test'

   }

‍

   test {

      useJUnitPlatform()

   }

‍

Create Student Entity:

‍

    @Entity

    @Data

    @AllArgsConstructor

    @NoArgsConstructor

    @Table(name = "student")

     public class StudentDAO {

‍

        @Id

        @GeneratedValue(strategy = GenerationType.IDENTITY)

        protected Long id;

‍

        private String firstName;

‍

        private String lastName;

‍

        private Integer rollNo;

‍

        private String grade;

‍

        private String parentName;

‍

        private String section;

‍

SearchRequest:

    @Data

    @AllArgsConstructor

    @NoArgsConstructor

    @EqualsAndHashCode(callSuper = false)

    public class SearchRequest {

        private List<String> columns;

   }

‍

Create Student Service:

‍

 public interface StudentService <StudentDAO, Long>

 {

     List<StudentDAO>  search(String keyword, SearchRequest searchRequest);

 }

‍

Create StudentServiceImpl:

‍

    @Service

    @Transactional

    public class StudentServiceImpl implements StudentService {

‍

        @PersistenceContext

        private EntityManager entityManager;

‍

        @Override

        public List<StudentDAO>  search(String keyword, SearchRequest searchRequest) {

‍

            List<String> columns;

‍

            CriteriaBuilder cb = entityManager.getCriteriaBuilder();

            CriteriaQuery<StudentDAO>  q = cb.createQuery(StudentDAO.class);

            Root<StudentDAO> studentDAORoot = q.from(StudentDAO.class);

‍

            List<Predicate> predicates = new ArrayList<>();

            columns = searchRequest.getColumns();

            for (int i = 0; i < columns.size(); i++)  {

    predicates.add(cb.or(cb.like(studentDAORoot.get(String.valueOf(columns.get(i))).as(String.class), "%" + keyword + "%")));

           }

‍

          q.select(studentDAORoot).where(

                   cb.or(predicates.toArray(new Predicate[predicates.size()])));

   Predicate[predicates.size() ] ) ) );

‍

          List<StudentDAO> resultList = 

   entityManager.createQuery(q).getResultList();

‍

          return resultList;

‍

      }

   }

‍

StudentController:

‍

    @CrossOrigin

    @RestController

    @RequestMapping("/student")

    public class StudentController {

‍

       private StudentService studentService;

‍

       @Autowired

       public StudentController(

               StudentService studentService) {

           this.studentService = studentService;

      }

‍

       @RequestMapping(value = "/search", method = RequestMethod.PUT)

        public List<StudentDAO> search(@RequestParam String keyword, @RequestBody(required = false) SearchRequest searchRequest) {

          return studentService.search(keyword, searchRequest);

      }

   }

‍

Application.properties: 

‍

    #Postgres

‍

    spring.jpa.database=POSTGRESQL

‍

    spring.datasource.platform=postgres

‍

    spring.datasource.url=jdbc:postgresql://localhost:5432/postgres

‍

    spring.datasource.username=postgres

‍

    spring.datasource.password=postgres

‍

    spring.jpa.hibernate.ddl-auto=update

To test this search application programming interface (API), there should be some data in the student table in the database. 

So, insert data using the below Structured Query Language (SQL) statements in the database.

‍

   INSERT INTO student (

          firstName, lastName, parentName, grade, rollNo, section)

   VALUES ('Rajesh', 'Sigh', 'Ajay', 'First', 10, 'A');

          INSERT INTO student (

          firstName, lastName, parentName, grade, rollNo, section)

   VALUES ('Ramesh', 'Sigh', 'Ajay', 'Second', 32, 'B');

          INSERT INTO student (

          firstName, lastName, parentName, grade, rollNo, section)

   VALUES ('Ankith', 'Sharma', 'Rajiv Sharma', 'Third', 23, 'A');

          INSERT INTO student (

          firstName, lastName, parentName, grade, rollNo, section)

   VALUES ('Anurag', 'Mishra', 'Rahul Mishra', 'Second', 31, 'B');

          INSERT INTO student (

          firstName, lastName, parentName, grade, rollNo, section)

   VALUES ('Harsha', 'Varma', 'Hari Varma', 'First', 16, 'A');

After successfully executing the above SQL script, open Postman Tool. 

Now, test the API as shown below example:

Sample Search Request (body):

In the above PUT request keyword (search term) is “sh” and the columns to search this keyword is as shown in search request object; which is:

{

“columns”: [

“firstName”,

“rollNo”,

“grade”,

“parentName”

]

}

Here we search for the keyword “sh” in multiple columns: firstName, rollNo, grade, and parentName.

So, we got the response as shown in the below screen:

Conclusion

Searching and filtering are omnipresent in all modern-day digital products, and the Spring Data JPA specification provides a neat and elegant way to create dynamic queries. 

‍

An earlier version of this blog was published on Medium by the author.

‍