JPA Repository - find by multiple Columns
Tags: java, webdev, todayilearned, programming
Published: 2022-10-28 12:39:55

In this tutorial, I will show you how to use JPA Repository to find by multiple fields example. At the end, you will know way to filter by multiple Columns using 3 ways: Derived Query, JPQL and Native Query.

This tutorial is originally from Bezkoder:
[https://www.bezkoder.com/jpa-filter-by-multiple-columns/](https://www.bezkoder.com/jpa-filter-by-multiple-columns/)

<h2>Overview</h2>
For querying the data from the database with custom filter methods, Spring Data JPA support several ways:

- Derived Query: JPA creates SQL queries based on the finder method and execute the query behind the scenes.</li>

```java
List<Tutorial> findAll();

List<Tutorial> findByTitleContainingIgnoreCase(String title);
```

- JPQL: inspired by SQL. It resemble SQL queries in syntax, but operate against JPA entity objects stored in a relational database rather than directly with database tables.</li>

```java
@Query("SELECT t FROM Tutorial t")
List<Tutorial> findAll();

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

- Native Query: actual SQL queries which can be operated directly with database object and tables.</li>

```java
@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();
```

For more details and examples about each kind, please visit:
<ul>
	<li><a href="https://www.bezkoder.com/jpa-repository-query/">Spring JPA Derived Query example</a></li>
	<li><a href="https://www.bezkoder.com/spring-jpa-query/">Spring JPA @Query example with JPQL</a></li>
	<li><a href="https://www.bezkoder.com/jpa-native-query/">Spring JPA Native Query example</a></li>
</ul>

Let's continue to create project that uses JPA Repository with find by multiple columns methods.

<h2>JPA filter by multiple columns</h2>
<h3>Technology</h3>
<ul>
    	<li>Java 8</li>
    	<li>Spring Boot 2 (with Spring Data JPA)</li>
    	<li>MySQL/PostgreSQL/H2 (embedded database)</li>
    	<li>Maven 3.8.1</li>
</ul>

<h3>Project Structure</h3>

![jpa-findby-multiple-fields-example](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/iesnqb9foa8s7myauo5r.png)

Let me explain it briefly.

<ul>
    	<li><code>Tutorial</code> data model class correspond to entity and table <em>tutorials</em>.</li>
    	<li><code>TutorialRepository</code> is an interface that extends <a href="https://docs.spring.io/spring-data/jpa/docs/current/api/org/springframework/data/jpa/repository/JpaRepository.html">JpaRepository</a> for filter/findby methods. It will be autowired in <code>SpringBootQueryExampleApplication</code>.</li>
    	<li><code>SpringBootQueryExampleApplication</code> is <code><a href="https://docs.spring.io/spring-boot/docs/current/api/org/springframework/boot/autoconfigure/SpringBootApplication.html">SpringBootApplication</a></code> which implements <code><a href="https://docs.spring.io/spring-boot/docs/current/api/org/springframework/boot/CommandLineRunner.html">CommandLineRunner</a></code>. We will use <code>TutorialRepository</code> to run Query methods here.</li>
    	<li>Configuration for Spring Datasource, JPA & Hibernate in <strong>application.properties</strong>.</li>
    	<li><strong>pom.xml</strong> contains dependencies for Spring Boot and MySQL/PostgreSQL/H2 database.</li>
</ul>

<h3>Create &#038; Setup Spring Boot project</h3>
Use <a href="http://start.spring.io/">Spring web tool</a> or your development tool (<a href="https://spring.io/tools">Spring Tool Suite</a>, Eclipse, <a href="https://www.jetbrains.com/idea/download/">Intellij</a>) to create a Spring Boot project.

Then open <strong>pom.xml</strong> and add these dependencies:
```html
<!-- 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>
```

We also need to add one more dependency.
- If you want to use <strong>MySQL</strong>:
```html
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<scope>runtime</scope>
</dependency>
```

- or <strong>PostgreSQL</strong>:
```html
<dependency>
	<groupId>org.postgresql</groupId>
	<artifactId>postgresql</artifactId>
	<scope>runtime</scope>
</dependency>
```

- or <strong>H2</strong> (embedded database):
```html
<dependency>
	<groupId>com.h2database</groupId>
	<artifactId>h2</artifactId>
	<scope>runtime</scope>
</dependency>
```

<h3>Configure Spring Datasource, JPA, Hibernate</h3>
Under src/main/resources folder, open application.properties and write these lines.

- For MySQL:
```properties
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
```

- For PostgreSQL:
```properties
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
```

<ul>
	<li><code>spring.datasource.username</code> & <code>spring.datasource.password</code> properties are the same as your database installation.</li>
	<li>Spring Boot uses Hibernate for JPA implementation, we configure <code>MySQL5InnoDBDialect</code> for MySQL or <code>PostgreSQLDialect</code> for PostgreSQL</li>
	<li><code>spring.jpa.hibernate.ddl-auto</code> is used for database initialization. We set the value to <code>update</code> value so that a table will be created in the database automatically corresponding to defined data model. Any change to the model will also trigger an update to the table. For production, this property should be <code>validate</code>.</li>
</ul>

- For H2 database:
```properties
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
```

<ul>
	<li><code>spring.datasource.url</code>: <code>jdbc:h2:mem:[database-name]</code> for In-memory database and <code>jdbc:h2:file:[path/database-name]</code> for disk-based database.</li>
	<li>We configure <code>H2Dialect</code> for H2 Database</li>
	<li><code>spring.h2.console.enabled=true</code> tells the Spring to start H2 Database administration tool and you can access this tool on the browser: <code>http://localhost:8080/h2-console</code>.</li>
	<li><code>spring.h2.console.path=/h2-ui</code> is for H2 console's url, so the default url <code>http://localhost:8080/h2-console</code> will change to <code>http://localhost:8080/h2-ui</code>.</li>
</ul>

<h3>Create Entity</h3>
In <strong>model</strong> package, we define <code>Tutorial</code> class.

Tutorial has following fields: id, title, level, description, published, createdAt.

<strong>model</strong>/<em>Tutorial.java</em>
```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
}
```

- <code>@Entity</code> annotation indicates that the class is a persistent Java class.
- <code>@Table</code> annotation provides the table that maps this entity.

- <code>@Id</code> annotation is for the primary key.
- <code>@GeneratedValue</code> annotation is used to define generation strategy for the primary key. 

- <code>@Temporal</code> annotation converts back and forth between timestamp and <code>java.util.Date</code> or time-stamp into time. For example, <code>@Temporal(TemporalType.DATE)</code> drops the time value and only preserves the date.

```java
@Temporal(TemporalType.DATE)
private Date createdAt;
```

<h2>JPA Repository Find by multiple Columns methods</h2>
Assume that we've already have <strong>tutorials</strong> table like this:

![jpa-find-by-multiple-columns-example](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dlwmmzyqwumibfom7pi6.png)

Let's create a repository to interact with database.
In <strong>repository</strong> package, create <code>TutorialRepository</code> interface that extend <code>JpaRepository</code>.

<strong>repository</strong>/<em>TutorialRepository.java</em>
```java
package com.bezkoder.spring.jpa.query.repository;

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

public interface TutorialRepository extends JpaRepository<Tutorial, Long> {

}
```

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

<h3>Findby multiple Fields using Derived Query</h3>
For multiple fields/multiple columns, we can use <code>And</code>, <code>Or</code> keywords between fields/columns.
Notice that you can concatenate as much <code>And</code>/<code>Or</code> as you want.

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

List<Tutorial> findByTitleOrDescription(String title, String description);
```

Result:

```java
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]
*/
```

We can perform SQL LIKE query with following keywords:
<ul>
	<li><code>Like</code>: where x.field like param</li>
	<li><code>NotLike</code>: where x.field not like param</li>
	<li><code>StartingWith</code>: where x.field like %param (with appended <code>%</code>)</li>
	<li><code>EndingWith</code>: where x.field like param% (with prepended <code>%</code>)</li>
	<li><code>Containing</code>: where x.field like %param% (wrapped in %)</li>
</ul>

For case insensitive query, in SQL, we can force the value to all capital or lower case letters, then compare with the query values.
Spring JPA provide <code>IgnoreCase</code> keyword to do this with Derived Query.

```java
List<Tutorial> findByTitleContainingIgnoreCase(String title);

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

List<Tutorial> findByTitleContainingIgnoreCaseAndPublished(String title, boolean isPublished);
```

Result:

```java
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]
*/
```

<h3>Findby multiple Fields using JPQL</h3>
Let's use <code>@Query</code> annotation to create Spring JPA Query with SELECT and WHERE keywords.

```java
@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);
```

Result:

```java
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]
*/
```

<h3>Findby multiple Fields using Native Query</h3>
We also use <code>@Query</code> annotation to create Spring JPA Native Query with SELECT and WHERE keywords.

```java
@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);
```

Result:

```java
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]
*/
```

<h2>Run JPA Filter by Multiple Columns project</h2>
Let's open <code>SpringJpaRepositoryQueryExampleApplication.java</code>, we will implement <code>CommandLineRunner</a></code> and autowire <code>TutorialRepository</code> interface to run JPA Query methods here.

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

<h2>Conclusion</h2>
Today we've known how to use JPA Repository to find by/filter by multiple columns in Spring Boot example using Derived Query, JPQL and Native Query.
We can query any number of fields separated by logical operators (AND, OR).

You can continue to write CRUD Rest APIs with:
<a href="https://www.bezkoder.com/spring-boot-jpa-crud-rest-api/">Spring Boot, Spring Data JPA – Rest CRUD API example</a>

If you want to write Unit Test for the JPA Repository:
<a href="https://www.bezkoder.com/spring-boot-unit-test-jpa-repo-datajpatest/">Spring Boot Unit Test for JPA Repository with @DataJpaTest</a>

You can also know:
- how to deploy this Spring Boot App on AWS (for free) with <a href="https://www.bezkoder.com/deploy-spring-boot-aws-eb/">this tutorial</a>.
- dockerize with <a href="https://www.bezkoder.com/docker-compose-spring-boot-mysql/">Docker Compose: Spring Boot and MySQL example</a>
- way to upload an Excel file and store the data in MySQL database with <a href="https://www.bezkoder.com/spring-boot-upload-excel-file-database/">this post</a>
- upload CSV file and store the data in MySQL with <a href="https://www.bezkoder.com/spring-boot-upload-csv-file/">this post</a>.

Happy learning! See you again.

<h2>Source Code</h2>
You can find the complete source code for this tutorial on Github that uses:
- <a href="https://github.com/bezkoder/spring-jpa-repository-query-example">Derived Query</a>
- <a href="https://github.com/bezkoder/spring-jpa-query-example">JPQL</a>
- <a href="https://github.com/bezkoder/spring-jpa-native-query-example">Native Query</a>

<h2>Further Reading</h2>
<ul>
	<li><a href="https://www.bezkoder.com/spring-boot-jwt-authentication/">Secure Spring Boot App with Spring Security & JWT Authentication</a></li>
	<li><a href="https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#reference">Spring Data JPA Reference Documentation</a></li>
	<li><a href="https://www.bezkoder.com/spring-boot-pagination-sorting-example/">Spring Boot Pagination and Sorting example</a></li>
</ul>

Fullstack CRUD App:
- <a href="https://www.bezkoder.com/spring-boot-vue-js-crud-example/">Vue + Spring Boot example</a>
- <a href="https://www.bezkoder.com/angular-spring-boot-crud/">Angular 8 + Spring Boot example</a>
- <a href="https://www.bezkoder.com/angular-10-spring-boot-crud/">Angular 10 + Spring Boot example</a>
- <a href="https://www.bezkoder.com/angular-11-spring-boot-crud/">Angular 11 + Spring Boot example</a>
- <a href="https://www.bezkoder.com/angular-12-spring-boot-crud/">Angular 12 + Spring Boot example</a>
- <a href="https://www.bezkoder.com/spring-boot-angular-13-crud/">Angular 13 + Spring Boot example</a>
- <a href="https://www.bezkoder.com/spring-boot-angular-14-crud/">Angular 14 + Spring Boot example</a>
- <a href="https://www.bezkoder.com/react-spring-boot-crud/">React + Spring Boot example</a>

Associations:
- <a href="https://www.bezkoder.com/jpa-one-to-one/">JPA One To One example with Hibernate in Spring Boot</a>
- <a href="https://www.bezkoder.com/jpa-one-to-many/">JPA One To Many example with Hibernate and Spring Boot</a>
- <a href="https://www.bezkoder.com/jpa-many-to-many/">JPA Many to Many example with Hibernate in Spring Boot</a>