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
Full article: JPA Repository Query example
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 8
- Spring Boot 2.6.3 (with Spring Data JPA)
- MySQL/PostgreSQL/H2 (embedded database)
- Maven 3.8.1
- 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 Entity
In model package, we define Tutorial
class.
Tutorial has four fields: id, title, level, description, published, createdAt.
model/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
}
-
@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 andjava.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 with tutorials table like this:
For step by step and Github, please visit:
JPA Repository Query example
Further Reading
Using Native Query instead:
Spring JPA Native Query example with Spring Boot
Or JPQL:
Spring JPA @Query example with JPQL
Associations:
- 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
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
- 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.
Top comments (0)