In this tutorial, we will learn how to write a Spring Data JPA query method or finder method for multiple columns/fields.

Consider the following Product entity class and if we want to retrieve products by their name OR description fields then here is the Spring data JPA query method:

1

public List<Product> findByNameOrDescription(String name, String description);

Let’s create a complete example to understand end to end.

Maven Dependencies

Create a Spring boot project and add the following maven dependencies to it:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

Product Entity

Let’s first create a Product entity class and add the following content to it:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

package com.springdatajpa.springboot.entity;

import lombok.*;
import org.hibernate.annotations.CreationTimestamp;
import org.hibernate.annotations.UpdateTimestamp;

import javax.persistence.*;
import java.math.BigDecimal;
import java.time.LocalDateTime;

@Entity
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
@Table(
        name = "products",
        schema = "ecommerce",
        uniqueConstraints = {
                @UniqueConstraint(
                        name = "sku_unique",
                        columnNames = "stock_keeping_unit"
                )
        }
)
public class Product {

    @Id
    @GeneratedValue(
            strategy = GenerationType.IDENTITY
    )
    private Long id;

    @Column(name = "stock_keeping_unit", nullable = false)
    private String sku;

    @Column(nullable = false)
    private String name;

    private String description;
    private BigDecimal price;
    private boolean active;
    private String imageUrl;

    @CreationTimestamp
    private LocalDateTime dateCreated;

    @UpdateTimestamp
    private LocalDateTime lastUpdated;
}

ProductRepository

Let’s create ProductRepository which extends JpaRepository and add the following code to it:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

import java.util.List;

import net.javaguides.springdatajpacourse.entity.Product;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ProductRepository extends JpaRepository<Product, Long> {

    /**
        * Returns the found product entry whose title or description is given
        * as a method parameter. If no product entry is found, this method
        * returns an empty list.
        */
    public List<Product> findByNameOrDescription(String name, String description);
}

Note that the below query method returns the found product entry whose title or description is given as a method parameter. If no product entry is found, this method returns an empty list.

1

public List<Product> findByNameOrDescription(String name, String description);

Configure MySQL and Hibernate Properties

Let’s use the MySQL database to store and retrieve the data in this example and we gonna use Hibernate properties to create and drop tables.

Open the application.properties file and add the following configuration to it:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

spring.datasource.url=jdbc:mysql://localhost:3306/ecommerce?useSSL=false
spring.datasource.username=root
spring.datasource.password=Mysql@123

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect

spring.jpa.hibernate.ddl-auto = create-drop

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

Make sure that you will create ecommerce database before running the Spring boot application.

Also, change the MySQL username and password as per your MySQL installation on your machine.

Testing Spring Data JPA Query Method by Multiple Columns

Let’s write the JUnit test to test the Spring Data JPA query method or finder method for multiple columns/fields.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

package com.springdatajpa.springboot.repository;

import com.springdatajpa.springboot.entity.Product;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
public class QueryMethodsTest {

    @Autowired
    private ProductRepository productRepository;

    @Test
    void findByNameOrDescriptionMethod(){

        List<Product> products = productRepository.findByNameOrDescription("product 1",
                "product 1 description");

        products.forEach((p) -> {
            System.out.println(p.getId());
            System.out.println(p.getName());
        });
    }
}

Here is the output of the above JUnit test:

spring data jpa junit test

Note that the Spring Data JPA (uses Hibernate as JPA provider) generated SQL statements in a console.

Reference https://www.sourcecodeexamples.net/2022/03/spring-data-jpa-query-method-by-multiple-columns-example.html