Spring JPA @Query example: Custom query in Spring Boot

In this tutorial, you will know how to use Spring JPA @Query for custom query in Spring Boot example. I will show you:

Way to use JPQL (Java Persistence Query Language)
How to execute SQL query in Spring Boot
JPA Select query with WHERE condition 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

JPQL vs Native Query
Spring JPA @Query example with Spring Boot
Create & Setup Spring Boot project
Configure Spring Datasource, JPA, Hibernate
Create Entity
Define Repository with JPA Custom Query methods
JPA Select query with where condition example
JPA Query Greater Than or Equal To
JPA Query Between
JPA Query example with parameters
JPA Query Order By Desc/Asc
JPA Query Sort By
JPA Query Pagination
JPA Query Update
Run Spring JPA Query project
Conclusion
Further Reading
Source Code

JPQL vs Native Query

Spring JPA supports both JPQL and Native Query.

The Jakarta Persistence Query Language (JPQL; formerly Java Persistence Query Language) is a platform-independent object-oriented query language defined as part of the Jakarta Persistence (JPA; formerly Java Persistence API) specification – Wikipedia

JPQL is inspired by SQL, and its queries resemble SQL queries in syntax, but operate against JPA entity objects stored in a relational database rather than directly with database tables.

This is example for custom query using JPQL and @Query annotation:

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

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

JPQL only supports a subset of SQL standard. If you want to make complex queries, take a look at Native SQL Query.
This is how to execute SQL query in Spring Boot with @Query annotation:

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

You need to note that:
– Spring Data JPA don’t adjust the query to database’s specific SQL dialect, so ensure that the provided statement is supported by RDBMS.
– Spring Data JPA does not currently support dynamic sorting for native queries, because it would have to manipulate the actual query declared, which it cannot do reliably for native SQL.

For example, we cannot use dynamic sorting in following method:

// JPQL: ok
@Query("SELECT * FROM tutorials t WHERE t.title LIKE %?1%")
List<Tutorial> findByTitleAndSort(String title, Sort sort);

// Native query: throw InvalidJpaQueryMethodException
@Query(value = "SELECT * FROM tutorials t WHERE t.title LIKE %?1%", nativeQuery = true)
List<Tutorial> findByTitleAndSortNative(String title, Sort sort);

For more details, please visit:
JPA Native Query example with Spring Boot

Let’s see how we make custom query in Spring Boot with JPA and JPQL example.

Spring JPA @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 CRUD methods and custom finder 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 & 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 or PostgreSQLDialect for PostgreSQL
spring.jpa.hibernate.ddl-auto is used for database initialization. We set the value to update 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 validate.

– 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 and jdbc: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 url http://localhost:8080/h2-console will change to http://localhost:8080/h2-ui.

Create Entity

In model package, we define Tutorial class.

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

model/Tutorial.java

package com.bezkoder.spring.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 Repository with JPA Custom 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.query.repository;

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

public interface TutorialRepository extends JpaRepository<Tutorial, Long> {

}

In this interface, we will write JPA Select query (with where condition) to fetch data from database.
I will show you how to use JPQL Query example in Spring Boot.

Assume that we’ve already have tutorials table like this:

JPA Select query with where condition example

Let’s use @Query annotation to create Spring JPA Query with SELECT and WHERE keywords.

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

@Query("SELECT t FROM Tutorial t WHERE t.published=?1")
List<Tutorial> findByPublished(boolean isPublished);

@Query("SELECT t FROM Tutorial t WHERE t.title LIKE %?1%")
List<Tutorial> findByTitleLike(String title);

@Query("SELECT t FROM Tutorial t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', ?1,'%'))")
List<Tutorial> findByTitleLikeCaseInsensitive(String title);

Result:

List<Tutorial> tutorials = new ArrayList<>();

tutorials = tutorialRepository.findAll();
show(tutorials);
/*
Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=2, title=Java Spring, description=Tut#2 Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
Tutorial [id=4, title=Spring Boot, description=Tut#4 Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
Tutorial [id=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=6, title=Spring Batch, description=Tut#6 Description, level=4, published=false, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=7, title=Spring Security, description=Tut#7 Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
*/

tutorials = tutorialRepository.findByPublished(true);
show(tutorials);
/*
Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
Tutorial [id=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
*/

tutorials = tutorialRepository.findByTitleLike("ata");
show(tutorials);
/*
Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
*/
   
tutorials = tutorialRepository.findByTitleLikeCaseInsensitive("dat");
show(tutorials);
/*
Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
*/

JPA Query Greater Than or Equal To

Spring Data JPA Query for Greater Than or Equal To date/column:

@Query("SELECT t FROM Tutorial t WHERE t.level >= ?1")
List<Tutorial> findByLevelGreaterThanEqual(int level);

@Query("SELECT t FROM Tutorial t WHERE t.createdAt >= ?1")
List<Tutorial> findByDateGreaterThanEqual(Date date);

Result:

tutorials = tutorialRepository.findByLevelGreaterThanEqual(3);
show(tutorials);
/*
Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
Tutorial [id=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=6, title=Spring Batch, description=Tut#6 Description, level=4, published=false, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=7, title=Spring Security, description=Tut#7 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.findByDateGreaterThanEqual(myDate);
show(tutorials);
/*
Tutorial [id=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=6, title=Spring Batch, description=Tut#6 Description, level=4, published=false, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=7, title=Spring Security, description=Tut#7 Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
*/

JPA Query Between

Spring Data JPA Query Between date/column:

@Query("SELECT t FROM Tutorial t WHERE t.level BETWEEN ?1 AND ?2")
List<Tutorial> findByLevelBetween(int start, int end);

@Query("SELECT t FROM Tutorial t WHERE t.createdAt BETWEEN ?1 AND ?2")
List<Tutorial> findByDateBetween(Date start, Date end);

Result:

tutorials = tutorialRepository.findByLevelBetween(3,5);
show(tutorials);
/*
Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
Tutorial [id=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=6, title=Spring Batch, description=Tut#6 Description, level=4, published=false, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=7, title=Spring Security, description=Tut#7 Description, level=5, published=false, 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.findByDateBetween(myDate1, myDate2);
show(tutorials);
/*
Tutorial [id=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
Tutorial [id=4, title=Spring Boot, description=Tut#4 Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
*/

JPA Query example with parameters

In the example above, we use Positional Parameters: the parameters is referenced by their positions in the query (defined using ? followed by a number (?1, ?2, …). Spring Data JPA will automatically replaces the value of each parameter in the same position.

Another way of binding value is Named Parameters. A named parameter starts with : followed by the name of the parameter (:title, :date, …).

For example:

@Query("SELECT t FROM Tutorial t WHERE t.published=:isPublished AND t.level BETWEEN :start AND :end")
List<Tutorial> findByLevelBetween(@Param("start") int start, @Param("end") int end, @Param("isPublished") boolean isPublished);

Result:

tutorials = tutorialRepository.findByLevelBetween(3, 5, true);
show(tutorials);
/*
Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
Tutorial [id=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
*/

JPA Query Order By Desc/Asc

Spring Data JPA Query Order By column example with filtering:

@Query("SELECT t FROM Tutorial t ORDER BY t.level DESC")
List<Tutorial> findAllOrderByLevelDesc();

@Query("SELECT t FROM Tutorial t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', ?1,'%')) ORDER BY t.level ASC")
List<Tutorial> findByTitleOrderByLevelAsc(String title);

@Query("SELECT t FROM Tutorial t WHERE t.published=true ORDER BY t.createdAt DESC")
List<Tutorial> findAllPublishedOrderByCreatedDesc();

Result:

tutorials = tutorialRepository.findAllOrderByLevelDesc();
show(tutorials);
/*
Tutorial [id=7, title=Spring Security, description=Tut#7 Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=6, title=Spring Batch, description=Tut#6 Description, level=4, published=false, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
Tutorial [id=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=4, title=Spring Boot, description=Tut#4 Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
Tutorial [id=2, title=Java Spring, description=Tut#2 Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
*/

tutorials = tutorialRepository.findByTitleOrderByLevelAsc("at");
show(tutorials);
/*
Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
Tutorial [id=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=6, title=Spring Batch, description=Tut#6 Description, level=4, published=false, createdAt=2022-05-19 00:00:00.0]
*/

tutorials = tutorialRepository.findAllPublishedOrderByCreatedDesc();
show(tutorials);
/*
Tutorial [id=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
*/

JPA Query Sort By

Spring Data JPA Query example using Sort class with filtering:

@Query("SELECT t FROM Tutorial t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', ?1,'%'))")
List<Tutorial> findByTitleAndSort(String title, Sort sort);

@Query("SELECT t FROM Tutorial t WHERE t.published=?1")
List<Tutorial> findByPublishedAndSort(boolean isPublished, Sort sort);

Result:

tutorials = tutorialRepository.findByTitleAndSort("at", Sort.by("level").descending());
show(tutorials);
/*
Tutorial [id=6, title=Spring Batch, description=Tut#6 Description, level=4, published=false, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
Tutorial [id=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
*/

tutorials = tutorialRepository.findByTitleAndSort("at", Sort.by("createdAt").descending());
show(tutorials);
/*
Tutorial [id=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=6, title=Spring Batch, description=Tut#6 Description, level=4, published=false, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
*/

tutorials = tutorialRepository.findByPublishedAndSort(false, Sort.by("level").descending());
show(tutorials);
/*
Tutorial [id=7, title=Spring Security, description=Tut#7 Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=6, title=Spring Batch, description=Tut#6 Description, level=4, published=false, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=4, title=Spring Boot, description=Tut#4 Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
Tutorial [id=2, title=Java Spring, description=Tut#2 Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
*/

JPA Query Pagination

Spring Data JPA Query example using Pageable class for Pagination (with sorting and filtering):

@Query("SELECT t FROM Tutorial t")
Page<Tutorial> findAllWithPagination(Pageable pageable);

Result:

int page = 0;
int size = 3;

Pageable pageable = PageRequest.of(page, size);

tutorials = tutorialRepository.findAllWithPagination(pageable).getContent();
show(tutorials);
/*
Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=2, title=Java Spring, description=Tut#2 Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
*/

pageable = PageRequest.of(page, size, Sort.by("level").descending());

tutorials = tutorialRepository.findAllWithPagination(pageable).getContent();
show(tutorials);
/*
Tutorial [id=7, title=Spring Security, description=Tut#7 Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=6, title=Spring Batch, description=Tut#6 Description, level=4, published=false, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
*/

JPA Query Update

Spring Data JPA Query to update an entity using @Query along with @Transactional and @Modifying:

@Transactional
@Modifying
@Query("UPDATE Tutorial t SET t.published=true WHERE t.id=?1")
int publishTutorial(Long id);

Result:

tutorialRepository.deleteAll();

Date date1 = new SimpleDateFormat("yyyy-MM-dd").parse("2022-03-11");
Date date2 = new SimpleDateFormat("yyyy-MM-dd").parse("2022-04-26");
Date date3 = new SimpleDateFormat("yyyy-MM-dd").parse("2022-05-19");

tutorialRepository.save(new Tutorial("Spring Data", "Tut#1 Description", 3, false, date1));
tutorialRepository.save(new Tutorial("Java Spring", "Tut#2 Description", 1, false, date1));
tutorialRepository.save(new Tutorial("Hibernate", "Tut#3 Description", 3, false, date2));
tutorialRepository.save(new Tutorial("Spring Boot", "Tut#4 Description", 2, false, date2));
tutorialRepository.save(new Tutorial("Spring Data JPA", "Tut#5 Description", 3, false, date3));
tutorialRepository.save(new Tutorial("Spring Batch", "Tut#6 Description", 4, false, date3));
tutorialRepository.save(new Tutorial("Spring Security", "Tut#7 Description", 5, false, date3));

List<Tutorial> tutorials = new ArrayList<>();

tutorials = tutorialRepository.findAll();
show(tutorials); // published = false for all

tutorialRepository.publishTutorial(tutorials.get(0).getId());
tutorialRepository.publishTutorial(tutorials.get(2).getId());
tutorialRepository.publishTutorial(tutorials.get(4).getId());

tutorials = tutorialRepository.findByPublished(true);
show(tutorials);
/*
Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
Tutorial [id=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
*/

Run Spring JPA Query project

Let’s open SpringBootQueryExampleApplication.java, we will implement CommandLineRunner and autowire TutorialRepository interface to run JPA Query methods here.

package com.bezkoder.spring.query;

// import ...

@SpringBootApplication
public class SpringBootQueryExampleApplication implements CommandLineRunner {

  @Autowired
  TutorialRepository tutorialRepository;

  public static void main(String[] args) {
    SpringApplication.run(SpringBootQueryExampleApplication.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 @Query annotation for custom query in Spring Boot example using JPQL.

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.

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 Derived query:
Spring JPA Derived query example in Spring Boot

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 (with OpenAPI 3)
Caching: Spring Boot Redis Cache example

Spring JPA @Query example: Custom query in Spring Boot

JPQL vs Native Query

Spring JPA @Query example with Spring Boot

Create & Setup Spring Boot project

Configure Spring Datasource, JPA, Hibernate

Create Entity

Define Repository with JPA Custom Query methods

JPA Select query with where condition example

JPA Query Greater Than or Equal To

JPA Query Between

JPA Query example with parameters

JPA Query Order By Desc/Asc

JPA Query Sort By

JPA Query Pagination

JPA Query Update

Run Spring JPA Query project

Conclusion

Further Reading

Source Code

One thought to “Spring JPA @Query example: Custom query in Spring Boot”

Tools

JPQL vs Native Query

Spring JPA @Query example with Spring Boot

Create & Setup Spring Boot project

Configure Spring Datasource, JPA, Hibernate

Create Entity

Define Repository with JPA Custom Query methods

JPA Select query with where condition example

JPA Query Greater Than or Equal To

JPA Query Between

JPA Query example with parameters

JPA Query Order By Desc/Asc

JPA Query Sort By

JPA Query Pagination

JPA Query Update

Run Spring JPA Query project

Conclusion

Further Reading

Source Code

One thought to “Spring JPA @Query example: Custom query in Spring Boot”

Follow us

Tools