In previous posts, you’ve known how to use JPQL and native Query to retrieve data from the database using @Query
annotation. Today, I will show you way to implement Spring Data JPA Repository query in Spring Boot with Derived Query methods:
- Structure of Derived Query methods
- Configure Spring Boot application to work with different database
- JPA find by field, column name, multiple columns
- JPA query methods for pagination and sorting
Related Posts:
– Spring JPA Native Query example
– Spring JPA @Query example with JPQL
– JPA EntityManager example in Spring Boot
– Spring Boot, Spring Data JPA – Rest CRUD API example
– Spring Boot Pagination and Sorting example
– Spring Boot File upload example with Multipart File
– Spring Boot Authentication with Spring Security & JWT
– Spring JPA + H2 example
– Spring JPA + MySQL example
– Spring JPA + PostgreSQL example
– Spring JPA + Oracle example
– Spring JPA + SQL Server example
– Documentation: Spring Boot + Swagger 3 example (with OpenAPI 3)
– Caching: Spring Boot Redis Cache example
Associations:
– JPA One To One example with Hibernate in Spring Boot
– JPA One To Many example with Hibernate and Spring Boot
– JPA Many to Many example with Hibernate in Spring Boot
Contents
- Structure of Derived Query methods
- JPA Repository Query example with Spring Boot
- Create & Setup Spring Boot project
- Configure Spring Datasource, JPA, Hibernate
- Create Entity
- Define JPA Repository Query methods
- JPA find by field
- JPA find by multiple Columns
- JPA Like Query
- JPA Boolean Query
- JPA Repository Query with Comparison
- JPA Repository Query with Sorting
- JPA Repository Query with Pagination
- JPA Repository Query with Pagination and Sorting
- JPA Delete multiple Rows
- Run Spring JPA Derived Query project
- Conclusion
- Further Reading
- Source Code
Structure of Derived Query methods
Typically, a Derived Query method has 2 elements: subject (the action), and predicate (the conditions).
- Subject: is the introducing clause (
find…By
,exists…By
,count…By
for example), it may contain further expressions (betweenfind
/exists
/count
andBy
) for result-limiting keywords such asDistinct
orTop
/First
. - Predicate: is placed after the subject. It can be entity properties (concatenating with
And
/Or
) followed by one or more keywords (StartingWith
,EndingWith
,Containing
,IgnoreCase
…).
For example:
List<Tutorial> findByTitleContainingIgnoreCase(String title);
List<Tutorial> findTop3ByTitleContainingAndPublished(String title, boolean isPublished);
You can find the full list at query method subject keywords and query method predicate keywords.
JPA Repository Query example with Spring Boot
– Technology:
- Java 17 / 11 / 8
- Spring Boot 3 / 2 (with Spring Data JPA)
- MySQL/PostgreSQL/H2 (embedded database)
- Maven
– Project Structure:
Let me explain it briefly.
Tutorial
data model class correspond to entity and table tutorials.TutorialRepository
is an interface that extends JpaRepository for derived query methods. It will be autowired inSpringBootQueryExampleApplication
.SpringBootQueryExampleApplication
isSpringBootApplication
which implementsCommandLineRunner
. We will useTutorialRepository
to run Query methods here.- Configuration for Spring Datasource, JPA & Hibernate in application.properties.
- pom.xml contains dependencies for Spring Boot and MySQL/PostgreSQL/H2 database.
Create & Setup Spring Boot project
Use Spring web tool or your development tool (Spring Tool Suite, Eclipse, Intellij) to create 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>
We also need to add one more dependency.
– If you want to use MySQL:
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<scope>runtime</scope>
</dependency>
– or PostgreSQL:
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
– or H2 (embedded database):
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
Configure Spring Datasource, JPA, Hibernate
Under src/main/resources folder, open application.properties and write these lines.
– For MySQL:
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.MySQLDialect
# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto= update
– For PostgreSQL:
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
spring.datasource.username
&spring.datasource.password
properties are the same as your database installation.- Spring Boot uses Hibernate for JPA implementation, we configure
MySQLDialect
for MySQL orPostgreSQLDialect
for PostgreSQL spring.jpa.hibernate.ddl-auto
is used for database initialization. We set the value toupdate
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 bevalidate
.
– For H2 database:
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
spring.datasource.url
:jdbc:h2:mem:[database-name]
for In-memory database andjdbc:h2:file:[path/database-name]
for disk-based database.- We configure
H2Dialect
for H2 Database spring.h2.console.enabled=true
tells the Spring to start H2 Database administration tool and you can access this tool on the browser:http://localhost:8080/h2-console
.spring.h2.console.path=/h2-ui
is for H2 console’s url, so the default urlhttp://localhost:8080/h2-console
will change tohttp://localhost:8080/h2-ui
.
Create Entity
In model package, we define Tutorial
class.
Tutorial has following fields: id, title, level, description, published, createdAt.
model/Tutorial.java
package com.bezkoder.spring.jpa.query.model;
import jakarta.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
}
– @Entity
annotation indicates that the class is a persistent Java class.
– @Table
annotation provides the table that maps this entity.
– @Id
annotation is for the primary key.
– @GeneratedValue
annotation is used to define generation strategy for the primary key.
– @Temporal
annotation converts back and forth between timestamp and java.util.Date
or time-stamp into time. For example, @Temporal(TemporalType.DATE)
drops the time value and only preserves the date.
@Temporal(TemporalType.DATE)
private Date createdAt;
Define JPA Repository Query methods
Let’s create a repository to interact with database.
In repository package, create TutorialRepository
interface that extend JpaRepository
.
repository/TutorialRepository.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 Derived Queries to fetch data from database.
Assume that we’ve already have tutorials table like this:
Let’s check the basic query method: findAll()
first.
public interface TutorialRepository extends JpaRepository<Tutorial, Long> {
List<Tutorial> findAll();
}
Result:
List<Tutorial> tutorials = new ArrayList<>();
tutorials = tutorialRepository.findAll();
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=2, title=Java Spring Boot, description=Spring Framework Description, level=1, published=false, 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=4, title=Spring Boot, description=Spring Boot Description, level=2, published=false, 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]
Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
*/
JPA find by field
We only need to append the field right after by
, then add either Is
or Equals
keyword for readability (optional) on Derived Query.
Optional<Tutorial> findById(long id);
List<Tutorial> findByLevel(int level);
List<Tutorial> findByPublished(boolean isPublished);
List<Tutorial> findByLevelIs(int level);
List<Tutorial> findByLevelEquals(int level);
Result:
Tutorial tutorial = tutorialRepository.findById(1).get();
System.out.println(tutorial);
/*
Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
*/
tutorials = tutorialRepository.findByPublished(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]
Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
*/
tutorials = tutorialRepository.findByLevel(3);
// or
tutorials = tutorialRepository.findByLevelIs(3);
// or
tutorials = tutorialRepository.findByLevelEquals(3);
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]
*/
On the contrary, you can use Not
or IsNot
keyword.
List<Tutorial> findByLevelNot(int level);
List<Tutorial> findByLevelIsNot(int level);
Result:
tutorials = tutorialRepository.findByLevelNot(3);
// or
tutorials = tutorialRepository.findByLevelIsNot(3);
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]
Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
*/
JPA find by multiple Columns
For multiple fields/multiple columns, we can use And
, Or
keywords between fields/columns.
Notice that you can concatenate as much And
/Or
as you want.
List<Tutorial> findByLevelAndPublished(int level, boolean isPublished);
List<Tutorial> findByTitleOrDescription(String title, String description);
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]
*/
JPA Like Query
We can perform SQL LIKE query with following keywords:
Like
: where x.field like paramNotLike
: where x.field not like paramStartingWith
: where x.field like %param (with appended%
)EndingWith
: where x.field like param% (with prepended%
)Containing
: where x.field like %param% (wrapped in %)
List<Tutorial> findByTitleLike(String title);
List<Tutorial> findByTitleStartingWith(String title);
List<Tutorial> findByTitleEndingWith(String title);
List<Tutorial> findByTitleContaining(String title);
List<Tutorial> findByTitleContainingOrDescriptionContaining(String title, String description);
List<Tutorial> findByTitleContainingAndPublished(String title, boolean isPublished);
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 IgnoreCase
keyword to do this with Derived Query.
List<Tutorial> findByTitleContainingIgnoreCase(String title);
Result:
tutorials = tutorialRepository.findByTitleStartingWith("Spring");
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=4, title=Spring Boot, description=Spring Boot Description, level=2, published=false, 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]
Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
*/
tutorials = tutorialRepository.findByTitleEndingWith("ot");
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.findByTitleContaining("at");
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=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
*/
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);
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]
*/
JPA Boolean Query
We can use True
, False
keywords for making JPA Boolean Derived Query.
List<Tutorial> findByPublishedTrue();
List<Tutorial> findByPublishedFalse();
Result:
tutorials = tutorialRepository.findByPublishedTrue();
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]
Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
*/
tutorials = tutorialRepository.findByPublishedFalse();
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]
Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
*/
JPA Repository Query with Comparison
We can perform SQL Comparison query with following keywords:
Between
: where x.field between param_1 and param_2LessThan
: where x.field < paramLessThanEqual
: where x.field <= paramGreaterThan
: where x.field > paramGreaterThanEqual
: where x.field >= param
For JPA Query with Date
Comparison, we can also use After
, Before
keywords.
After
: where x.dateField > paramBefore
: where x.dateField < param
List<Tutorial> findByLevelGreaterThan(int level);
List<Tutorial> findByCreatedAtGreaterThanEqual(Date date);
List<Tutorial> findByCreatedAtAfter(Date date);
List<Tutorial> findByLevelBetween(int start, int end);
List<Tutorial> findByLevelBetweenAndPublished(int start, int end, boolean isPublished);
List<Tutorial> findByCreatedAtBetween(Date start, Date end);
Result:
tutorials = tutorialRepository.findByLevelGreaterThan(3);
show(tutorials);
/*
Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
*/
Date myDate = new SimpleDateFormat("yyyy-MM-dd").parse("2022-05-11");
tutorials = tutorialRepository.findByCreatedAtGreaterThanEqual(myDate);
show(tutorials);
/*
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]
Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
*/
tutorials = tutorialRepository.findByCreatedAtAfter(myDate);
show(tutorials);
/*
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]
Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
*/
tutorials = tutorialRepository.findByLevelBetween(3, 5);
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]
Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
*/
tutorials = tutorialRepository.findByLevelBetweenAndPublished(3, 5, 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]
Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
*/
Date myDate1 = new SimpleDateFormat("yyyy-MM-dd").parse("2022-04-11");
Date myDate2 = new SimpleDateFormat("yyyy-MM-dd").parse("2022-05-11");
tutorials = tutorialRepository.findByCreatedAtBetween(myDate1, myDate2);
show(tutorials);
/*
Tutorial [id=3, title=Hibernate, description=Hibernate ORM Description, level=3, published=true, createdAt=2022-04-26 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]
*/
JPA Repository Query with Sorting
We can use OrderBy
keyword along with Asc
(default) and Desc
for sorting by field/column with Derived Query.
List<Tutorial> findByOrderByLevel();
// same as
List<Tutorial> findByOrderByLevelAsc();
List<Tutorial> findByOrderByLevelDesc();
List<Tutorial> findByTitleContainingOrderByLevelDesc(String title);
List<Tutorial> findByPublishedOrderByCreatedAtDesc(boolean isPublished);
Result:
tutorials = tutorialRepository.findByOrderByLevel();
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]
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]
Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
*/
tutorials = tutorialRepository.findByOrderByLevelDesc();
show(tutorials);
/*
Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, 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]
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]
Tutorial [id=4, title=Spring Boot, description=Spring Boot Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
Tutorial [id=2, title=Java Spring Boot, description=Spring Framework Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
*/
tutorials = tutorialRepository.findByTitleContainingOrderByLevelDesc("at");
show(tutorials);
/*
Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
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]
*/
tutorials = tutorialRepository.findByPublishedOrderByCreatedAtDesc(true);
show(tutorials);
/*
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]
Tutorial [id=3, title=Hibernate, description=Hibernate ORM Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
Tutorial [id=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
*/
If you want to sort more than one field, you need to pass Sort
object as parameter with the help of Order
class.
List<Tutorial> findByTitleContaining(String title, Sort sort);
List<Tutorial> findByPublished(boolean isPublished, Sort sort);
Result:
tutorials = tutorialRepository.findByTitleContaining("at", Sort.by("level").descending());
show(tutorials);
/*
Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 00:00:00.0]
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]
*/
tutorials = tutorialRepository.findByPublished(false, Sort.by("level").descending());
show(tutorials);
/*
Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, createdAt=2022-05-19 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]
Tutorial [id=2, title=Java Spring Boot, description=Spring Framework Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
*/
List<Order> orders = new ArrayList<Order>();
orders.add(new Order(Sort.Direction.DESC, "level"));
orders.add(new Order(Sort.Direction.ASC, "title"));
tutorials = tutorialRepository.findByPublished(true, Sort.by(orders));
show(tutorials);
/*
Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 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=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]
*/
You can find way to implement Rest APIs for sorting multiple fields/columns at:
Spring Data JPA Sort/Order by multiple Columns | Spring Boot Rest APIs
JPA Repository Query with Pagination
Similar to Sort
, you can use Pageable
object as input parameter to make pagination on the Derived Query. We will add this parameter to method definition with Page<User>
as return type.
Page<Tutorial> findAll(Pageable pageable);
Page<Tutorial> findByTitle(String title, Pageable pageable);
Result:
int page = 0;
int size = 3;
Pageable pageable = PageRequest.of(page, size);
tutorials = tutorialRepository.findAll(pageable).getContent();
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=2, title=Java Spring Boot, description=Spring Framework Description, level=1, published=false, 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]
*/
tutorials = tutorialRepository.findByTitleContaining("ring", pageable).getContent();
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=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]
*/
You can find way to implement Rest APIs for pagination and filter at:
Spring Boot Rest APIs: Pagination & Filter example | Spring JPA, Pageable
JPA Repository Query with Pagination and Sorting
Pageable
can also be used for paging and sorting with the help of PageRequest.of()
method.
Page<Tutorial> findAll(Pageable pageable);
Page<Tutorial> findByTitleContaining(String title, Pageable pageable);
Result:
int page = 0;
int size = 3;
Pageable pageable = PageRequest.of(page, size, Sort.by("level").descending());
tutorials = tutorialRepository.findAll(pageable).getContent();
show(tutorials);
/*
Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, 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]
Tutorial [id=5, title=Spring JPA, description=Spring Data JPA Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
*/
pageable = PageRequest.of(page, size, Sort.by("level").descending());
tutorials = tutorialRepository.findByTitleContaining("at", pageable).getContent();
show(tutorials);
/*
Tutorial [id=6, title=Spring Batch, description=Spring Batch Description, level=4, published=true, createdAt=2022-05-19 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=1, title=Spring Data, description=Spring Data Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
*/
You can find way to implement Rest APIs for pagination and sorting at:
Spring Boot Rest APIs: Pagination and Sorting example
JPA Delete multiple Rows
Spring Data JPA also supports Derived Query for delete multiple rows:
@Transactional
void deleteAllByCreatedAtBefore(Date date);
Result:
Date outdate = new SimpleDateFormat("yyyy-MM-dd").parse("2022-05-1");
tutorialRepository.deleteAllByCreatedAtBefore(outdate);
tutorials = tutorialRepository.findAll();
show(tutorials);
/*
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]
Tutorial [id=7, title=Spring Security, description=Spring Security Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
*/
Run Spring JPA Derived Query project
Let’s open SpringJpaRepositoryQueryExampleApplication.java
, we will implement CommandLineRunner
and autowire TutorialRepository
interface to run JPA Query methods here.
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);
}
}
Conclusion
Today we’ve known how to use Spring JPA Repository Query in Spring Boot example using Derived Query.
You can continue to write CRUD Rest APIs with:
Spring Boot, Spring Data JPA – Rest CRUD API example
If you want to write Unit Test for the JPA Repository:
Spring Boot Unit Test for JPA Repository with @DataJpaTest
You can also know:
– how to deploy this Spring Boot App on AWS (for free) with this tutorial.
– dockerize with Docker Compose: Spring Boot and MySQL example
– or: Docker Compose: Spring Boot and Postgres example
– way to upload an Excel file and store the data in MySQL database with this post
– upload CSV file and store the data in MySQL with this post.
Happy learning! See you again.
Further Reading
- Secure Spring Boot App with Spring Security & JWT Authentication
- Spring Data JPA Reference Documentation
- Spring Boot Pagination and Sorting example
Fullstack CRUD App:
– Vue + Spring Boot example
– Angular 8 + Spring Boot example
– Angular 10 + Spring Boot example
– Angular 11 + Spring Boot example
– Angular 12 + Spring Boot example
– Angular 13 + Spring Boot example
– Angular 14 + Spring Boot example
– Angular 15 + Spring Boot example
– Angular 16 + Spring Boot example
– React + Spring Boot example
Source Code
You can find the complete source code for this tutorial on Github.
Using Native Query instead:
Spring JPA Native Query example with Spring Boot
Or JPQL:
Spring JPA @Query example with JPQL
Or EntityManager:
JPA EntityManager example in Spring Boot
Associations:
– JPA One To One example with Hibernate in Spring Boot
– JPA One To Many example with Hibernate and Spring Boot
– JPA Many to Many example with Hibernate in Spring Boot
You can apply this implementation in following tutorials:
– Spring JPA + H2 example
– Spring JPA + MySQL example
– Spring JPA + PostgreSQL example
– Spring JPA + Oracle example
– Spring JPA + SQL Server example
Documentation: Spring Boot Swagger 3 example
Caching: Spring Boot Redis Cache example