JPA Native Query example with Spring Boot

In this tutorial, you will know how to use Spring Data JPA Native Query example (with parameters) in Spring Boot. I will show you:

Way to use Spring JPA Native query with @Query annotation
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 Native Query example with Spring Boot
Create & Setup Spring Boot project
Configure Spring Datasource, JPA, Hibernate
Create Entity
Define Repository for JPA Native Query methods
JPA native query Select with where condition example
JPA Native Query Greater Than or Equal To
JPA Native Query Between
JPA Native Query example with parameters
JPA Native Query Order By Desc/Asc
JPA Native Query Sort By
JPA Native Query Pagination
JPA Native 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();

For more details, please visit:
Spring JPA @Query example with JPQL in Spring Boot

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 native query in Spring Boot with @Query annotation:

define SQL in the value attribute
set the nativeQuery attribute value to true

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

But we can use Pageable object for sorting instead.

Let’s see how we make JPA native query in Spring Boot example.

Spring JPA Native 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 (using native queries). 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 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 Repository for JPA Native 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 native query (with parameters) to fetch data from database.

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

JPA native query Select with where condition example

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

@Query(value = "SELECT * FROM tutorials", nativeQuery = true)
List<Tutorial> findAll();

@Query(value = "SELECT * FROM tutorials t WHERE t.published=?1", nativeQuery = true)
List<Tutorial> findByPublished(boolean isPublished);

@Query(value = "SELECT * FROM tutorials t WHERE t.title LIKE %?1%", nativeQuery = true)
List<Tutorial> findByTitleLike(String title);

@Query(value = "SELECT * FROM tutorials t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', ?1,'%'))", nativeQuery = true)
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 Native Query Greater Than or Equal To

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

@Query(value = "SELECT * FROM tutorials t WHERE t.level >= ?1", nativeQuery = true)
List<Tutorial> findByLevelGreaterThanEqual(int level);

@Query(value = "SELECT * FROM tutorials t WHERE t.created_at >= ?1", nativeQuery = true)
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 Native Query Between

Spring Data JPA Native Query Between date/column:

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

@Query(value = "SELECT * FROM tutorials t WHERE t.created_at BETWEEN ?1 AND ?2", nativeQuery = true)
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 Native 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(value = "SELECT * FROM tutorials t WHERE t.published=:isPublished AND t.level BETWEEN :start AND :end", nativeQuery = true)
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 Native Query Order By Desc/Asc

Spring Data JPA Native Query Order By column example with filtering:

@Query(value = "SELECT * FROM tutorials t ORDER BY t.level DESC", nativeQuery = true)
List<Tutorial> findAllOrderByLevelDesc();

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

@Query(value = "SELECT * FROM tutorials t WHERE t.published=true ORDER BY t.created_at DESC", nativeQuery = true)
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 Native Query Sort By

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. You can, however, use native queries for pagination by specifying the count query yourself – Official Spring Document

/* InvalidJpaQueryMethodException
@Query(value = "SELECT * FROM tutorials t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', ?1,'%'))", nativeQuery = true)
List<Tutorial> findByTitleAndSort(String title, Sort sort);
*/

So, how to deal with dynamic sorting?
We can use Pageable object instead. For example:

@Query(value = "SELECT * FROM tutorials t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', ?1,'%'))", nativeQuery = true)
Page<Tutorial> findByTitleLike(String title, Pageable pageable);

@Query(value = "SELECT * FROM tutorials t WHERE t.published=?1", nativeQuery = true)
Page<Tutorial> findByPublished(boolean isPublished, Pageable pageable);

Result:

Pageable pageable1 = PageRequest.of(0, 1000, Sort.by("level").descending());

tutorials = tutorialRepository.findByTitleLike("at", pageable1).getContent();
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=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]
*/

Pageable pageable2 = PageRequest.of(0, 1000, Sort.by("title").descending());

tutorials = tutorialRepository.findByTitleLike("at", pageable2).getContent();
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=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 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]
*/

Pageable pageable3 = PageRequest.of(0, 1000, Sort.by("level").descending());

tutorials = tutorialRepository.findByPublished(false, pageable3).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=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 Native Query Pagination

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

@Query(value = "SELECT * FROM tutorials", nativeQuery = true)
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 Native Query Update

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

@Transactional
@Modifying
@Query(value = "UPDATE tutorials SET published=true WHERE id=?1", nativeQuery = true)
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.jpa.query;

// import ...

@SpringBootApplication
public class SpringJpaNativeQueryExampleApplication implements CommandLineRunner {

  @Autowired
  TutorialRepository tutorialRepository;

  public static void main(String[] args) {
    SpringApplication.run(SpringJpaNativeQueryExampleApplication.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 Native query (with parameters) using @Query annotation in Spring Boot 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
– 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 JPQL instead:
Spring JPA @Query example with JPQL in 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

JPA Native Query example with Spring Boot

JPQL vs Native Query

Spring JPA Native Query example with Spring Boot

Create & Setup Spring Boot project

Configure Spring Datasource, JPA, Hibernate

Create Entity

Define Repository for JPA Native Query methods

JPA native query Select with where condition example

JPA Native Query Greater Than or Equal To

JPA Native Query Between

JPA Native Query example with parameters

JPA Native Query Order By Desc/Asc

JPA Native Query Sort By

JPA Native Query Pagination

JPA Native Query Update

Run Spring JPA Query project

Conclusion

Further Reading

Source Code

One thought to “JPA Native Query example with Spring Boot”

Tools

JPQL vs Native Query

Spring JPA Native Query example with Spring Boot

Create & Setup Spring Boot project

Configure Spring Datasource, JPA, Hibernate

Create Entity

Define Repository for JPA Native Query methods

JPA native query Select with where condition example

JPA Native Query Greater Than or Equal To

JPA Native Query Between

JPA Native Query example with parameters

JPA Native Query Order By Desc/Asc

JPA Native Query Sort By

JPA Native Query Pagination

JPA Native Query Update

Run Spring JPA Query project

Conclusion

Further Reading

Source Code

One thought to “JPA Native Query example with Spring Boot”

Follow us

Tools