JPA Repository query example in Spring Boot | Derived Query

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);

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

• @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 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.