JPA Repository – Search by multiple columns

In this tutorial, I will show you how to find instance of multiple fields using JPA repository. Finally, you’ll learn how to filter by multiple columns using 3 methods: Derived Query, JPQL and Native Query.

This tutorial is originally from Bezcoder:
https://www.bezkoder.com/jpa-filter-by-multiple-columns/

Observation

To query data from the database with custom filter methods, Spring Data JPA supports several methods:

  • Derived Query: Creates SQL query based on JPA Finder method and executes the query behind the scenes.
List<Tutorial> findAll();

List<Tutorial> findByTitleContainingIgnoreCase(String title);
enter fullscreen mode

exit fullscreen mode

  • JPQL: Inspired by SQL. It resembles a SQL query in syntax, but operates directly against JPA entity objects stored in a relational database rather than against database tables.
@Query("SELECT t FROM Tutorial t")
List<Tutorial> findAll();

@Query("SELECT t FROM Tutorial t WHERE t.published=true")
List<Tutorial> findByPublished();
enter fullscreen mode

exit fullscreen mode

  • Native Query: Actual SQL queries that can be operated directly with database objects and tables.
@Query(value = "SELECT * FROM tutorials", nativeQuery = true)
List<Tutorial> findAllNative();

@Query(value = "SELECT * FROM tutorials t WHERE t.published=true", nativeQuery = true)
List<Tutorial> findByPublishedNative();
enter fullscreen mode

exit fullscreen mode

For more details and examples of each type, please visit:

Let’s continue to build project that uses JPA repository with find by multiple column methods.

JPA Filter by Multiple Columns

technology

  • java 8
  • Spring Boot 2 (with Spring Data JPA)
  • MySQL/PostgreSQL/H2 (embedded database)
  • Maven 3.8.1

project structure

Let me explain it in brief.

  • Tutorial The data model class corresponds to entity and table tutorial,
  • TutorialRepository There is an interface that extends JpaRepository for the Filter/Search methods. it will be autowired SpringBootQueryExampleApplication,
  • SpringBootQueryExampleApplication Is SpringBootApplication one who applies CommandLineRunner, we will use TutorialRepository Here to run the query methods.
  • Configuration for Spring Datasource, JPA and Hibernate application.properties,
  • pom.xml Includes dependencies for Spring Boot and MySQL/PostgreSQL/H2 databases.

Create and setup Spring Boot project

Use Spring Web Tools or your development tools (Spring Tool Suite, Eclipse, Intellij) to build a Spring Boot project.

then open pom.xml and add these dependencies:

<!-- web for access H2 database UI -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

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

exit fullscreen mode

We also need to add another dependency.

  • if you want to use MySQL,
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
enter fullscreen mode

exit fullscreen mode

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>
enter fullscreen mode

exit fullscreen mode

  • either h 2 (embedded database):
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>
enter fullscreen mode

exit fullscreen mode

Configure Spring Data Source, JPA, Hibernate

Open application.properties under src/main/resources folder and write these lines.

spring.datasource.url= jdbc:mysql://localhost:3306/testdb?useSSL=false
spring.datasource.username= root
spring.datasource.password= 123456

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

# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto= update
enter fullscreen mode

exit fullscreen mode

spring.datasource.url= jdbc:postgresql://localhost:5432/testdb
spring.datasource.username= postgres
spring.datasource.password= 123

spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation= true
spring.jpa.properties.hibernate.dialect= org.hibernate.dialect.PostgreSQLDialect

# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto= update
enter fullscreen mode

exit fullscreen mode

  • spring.datasource.username And spring.datasource.password The properties are the same as in your database installation.
  • Spring Boot uses Hibernate for JPA implementation, we configure MySQL5InnoDBDialect MySQL or . for PostgreSQLDialect for PostgreSQL
  • spring.jpa.hibernate.ddl-auto Used for database initialization. we set the value to update values ​​so that a table will be automatically created in the database corresponding to the defined data model. Any change to the model will also trigger an update to the table. For production, this property must be validate,
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto= update

spring.h2.console.enabled=true
# default path: h2-console
spring.h2.console.path=/h2-ui
enter fullscreen mode

exit fullscreen mode

  • spring.datasource.url, jdbc:h2:mem:[database-name] for in-memory databases and jdbc:h2:file:[path/database-name] For disk-based databases.
  • we configure H2Dialect for H2 database
  • spring.h2.console.enabled=true tells Spring to start the H2 Database Administration Tool and you can access this tool on the browser: http://localhost:8080/h2-console,
  • spring.h2.console.path=/h2-ui H2 stands for console’s url, so the default url http://localhost:8080/h2-console will turn into http://localhost:8080/h2-ui,

create unit

In pattern package, we define Tutorial Class.

The tutorial has the following fields: id, title, level, description, published, createdAt.

pattern,tutorial.java

package com.bezkoder.spring.jpa.query.model;

import javax.persistence.*;
import java.util.Date;

@Entity
@Table(name = "tutorials")
public class Tutorial {

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private long id;

  private String title;

  private String description;

  private int level;

  private boolean published;

  @Temporal(TemporalType.TIMESTAMP)
  private Date createdAt;

  public Tutorial() {

  }

  public Tutorial(String title, String description, int level, boolean published, Date createdAt) {
    this.title = title;
    this.description = description;
    this.level = level;
    this.published = published;
    this.createdAt = createdAt;
  }

  // getters and setters
}
enter fullscreen mode

exit fullscreen mode

  • @Entity The annotation indicates that the class is a persistent Java class.
  • @Table Annotation provides the table that maps to this entity.

  • @Id Annotation is for primary key.

  • @GeneratedValue Annotations are used to define the generation strategy for the primary key.

  • @Temporal Converts back and forth between annotation timestamp and java.util.Date Or in timestamp time. For example, @Temporal(TemporalType.DATE) Drops the time value and preserves only the date.

@Temporal(TemporalType.DATE)
private Date createdAt;
enter fullscreen mode

exit fullscreen mode

Search by JPA Repository Multiple Column Methods

Let’s say we already have tutorial Table like this:

jpa-search-by-multiple-column-instance

Let’s create a repository to interact with the database.
In fund package, create TutorialRepository interface that extends JpaRepository,

fund,TutorialRepository.java

package com.bezkoder.spring.jpa.query.repository;

import com.bezkoder.spring.jpa.query.model.Tutorial;

public interface TutorialRepository extends JpaRepository<Tutorial, Long> {

}
enter fullscreen mode

exit fullscreen mode

In this interface, we will write JPA queries to filter the data from the database.

Search by multiple fields using derived query

For multiple fields/multiple columns, we can use And, Or Keywords between fields/columns.
Note that you can add more than And,Or as your wish.

List<Tutorial> findByLevelAndPublished(int level, boolean isPublished);

List<Tutorial> findByTitleOrDescription(String title, String description);
enter fullscreen mode

exit fullscreen mode

Result:

tutorials = tutorialRepository.findByLevelAndPublished(3, true);
show(tutorials);
/*
Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=3, title=Hibernate, description=Hibernate ORM Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
Tutorial [id=5, title=Spring JPA, description=Spring Data JPA Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
*/

tutorials = tutorialRepository.findByTitleOrDescription("Hibernate", "Spring Data Description");
show(tutorials);
/*
Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=3, title=Hibernate, description=Hibernate ORM Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
*/
enter fullscreen mode

exit fullscreen mode

We can execute a SQL LIKE query with the following keywords:

  • Likelike :where x.field param
  • NotLike:where x.field is not like param
  • StartingWith: where x.field like %param (with appended) %,
  • EndingWith:where x.field like param% (with prepended) %,
  • ContainingLike: where x.field %param% (wrapped in %)

In case of case insensitive query, in SQL, we can apply the value to all uppercase or lowercase letters, then compare with query values.
Spring provides JPA IgnoreCase Keywords to do this with a derived query.

List<Tutorial> findByTitleContainingIgnoreCase(String title);

List<Tutorial> findByTitleContainingOrDescriptionContaining(String title, String description);

List<Tutorial> findByTitleContainingIgnoreCaseAndPublished(String title, boolean isPublished);
enter fullscreen mode

exit fullscreen mode

Result:

tutorials = tutorialRepository.findByTitleContainingIgnoreCase("dat");
show(tutorials);
/*
Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
*/

String text = "ot";
tutorials = tutorialRepository.findByTitleContainingOrDescriptionContaining(text, text);
show(tutorials);
/*
Tutorial [id=2, title=Java Spring Boot, description=Spring Framework Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=4, title=Spring Boot, description=Spring Boot Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
*/

tutorials = tutorialRepository.findByTitleContainingAndPublished("ring", true);
// or
// tutorials = tutorialRepository.findByTitleContainingIgnoreCaseAndPublished("spring", true);
show(tutorials);
/*
Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=5, title=Spring JPA, description=Spring Data JPA Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
*/
enter fullscreen mode

exit fullscreen mode

Search multiple fields using JPQL

let’s use @Query Annotations for building Spring JPA queries with SELECT and WHERE keywords.

@Query("SELECT t FROM Tutorial t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', :keyword,'%')) OR LOWER(t.description) LIKE LOWER(CONCAT('%', :keyword,'%'))")
List<Tutorial> findByTitleContainingOrDescriptionContainingCaseInsensitive(String keyword);

@Query("SELECT t FROM Tutorial t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', :title,'%')) AND t.published=:isPublished")
List<Tutorial> findByTitleContainingCaseInsensitiveAndPublished(String title, boolean isPublished);
enter fullscreen mode

exit fullscreen mode

Result:

tutorials = tutorialRepository.findByTitleContainingOrDescriptionContainingCaseInsensitive("data");
show(tutorials);
/*
Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=5, title=Spring JPA, description=Spring Data JPA Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
*/

tutorials = tutorialRepository.findByTitleContainingCaseInsensitiveAndPublished("spring", true);
show(tutorials);
/*
Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=5, title=Spring JPA, description=Spring Data JPA Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
*/
enter fullscreen mode

exit fullscreen mode

Search by multiple fields using native query

we also use @Query Annotation for creating Spring JPA Native Query with SELECT and WHERE keywords.

@Query(value = "SELECT * FROM tutorials t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', :keyword,'%')) OR LOWER(t.description) LIKE LOWER(CONCAT('%', :keyword,'%'))", nativeQuery = true)
List<Tutorial> findByTitleContainingOrDescriptionContainingCaseInsensitive(String keyword);

  @Query(value = "SELECT * FROM tutorials t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', :title,'%')) AND t.published=:isPublished", nativeQuery = true)
List<Tutorial> findByTitleContainingCaseInsensitiveAndPublished(String title, boolean isPublished);
enter fullscreen mode

exit fullscreen mode

Result:

tutorials = tutorialRepository.findByTitleContainingOrDescriptionContainingCaseInsensitive("data");
show(tutorials);
/*
Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=5, title=Spring JPA, description=Spring Data JPA Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
*/

tutorials = tutorialRepository.findByTitleContainingCaseInsensitiveAndPublished("spring", true);
show(tutorials);
/*
Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=5, title=Spring JPA, description=Spring Data JPA Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
*/
enter fullscreen mode

exit fullscreen mode

Run JPA Filter by Multiple Column Project

let’s open SpringJpaRepositoryQueryExampleApplication.javawe will implement CommandLineRunner and autowire TutorialRepository Here the interface for running JPA query methods.

package com.bezkoder.spring.jpa.query;

// import ...

@SpringBootApplication
public class SpringJpaRepositoryQueryExampleApplication implements CommandLineRunner {

  @Autowired
  TutorialRepository tutorialRepository;

  public static void main(String[] args) {
    SpringApplication.run(SpringJpaRepositoryQueryExampleApplication.class, args);
  }

  @Override
  public void run(String... args) throws Exception {
    // call tutorialRepository methods here
  }

  private void show(List<Tutorial> tutorials) {
    tutorials.forEach(System.out::println);
  }
}
enter fullscreen mode

exit fullscreen mode

conclusion

Today we learned how to use JPA Repository to filter by/multiple columns in a Spring Boot instance using Derived Query, JPQL and Native Query.
We can query any field separated by logical operators (AND, OR).

You can continue writing the CRUD Rest API:
Spring Boot, Spring Data JPA – REST CRUD API Example

If you want to write unit test for JPA repository:
@DataJpaTest . Spring Boot Unit Test for JPA Repository with

You can also know:

Happy Learning! See you.

source code

You can find the complete source code for this tutorial on Github which uses:

Further reading

Fullstack CRUD App:

Federation:

Leave a Comment