JPA Repository query example in Spring Boot | Derived Query

Tien Nguyen - Mar 7 '22 - - Dev Community

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 (between find/exists/count and By) for result-limiting keywords such as Distinct or Top/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);
Enter fullscreen mode Exit fullscreen mode

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:

jpa-repository-query-example-spring-boot-project

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 in SpringBootQueryExampleApplication.
  • SpringBootQueryExampleApplication is SpringBootApplication which implements CommandLineRunner. We will use TutorialRepository 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
}
Enter fullscreen mode Exit fullscreen mode
  • @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;
Enter fullscreen mode Exit fullscreen mode

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> {

}
Enter fullscreen mode Exit fullscreen mode

In this interface, we will write JPA Derived Queries to fetch data from database with tutorials table like this:

jpa-repository-query-example-spring-boot

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:

You can apply this implementation in following tutorials:

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:

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .