Spring Data JPA - @NamedQueries Example

In this tutorial, we will learn how to create multiple Named JPQL Queries using @NamedQueries annotation in Spring Data JPA.

Well, we use @NamedQueries annotation from JPA to define multiple Named JPQL queries.

Steps to Define Named JPQL Query

If we want to create a Named JPQL query, we must follow these steps:

  1. Annotate the entity with the @NamedQueries annotation from JPA:
    2. 
@NamedQueries(
        {
                @NamedQuery(
                        name = "Product.findAllOrderByNameDesc",
                        query = "SELECT p from Product p ORDER By p.name DESC"
                ),
                @NamedQuery(
                        name = "Product.findByPrice",
                        query = "SELECT p from Product p where p.price = :price"
                )
        }
)
public class Product {}
  2. Use multiple @NamedQueries annotations from JPA/Hibernate to define named queries:
    3. 
@NamedQueries(
        {
                @NamedQuery(
                        name = "Product.findAllOrderByNameDesc",
                        query = "SELECT p from Product p ORDER By p.name DESC"
                ),
                @NamedQuery(
                        name = "Product.findByPrice",
                        query = "SELECT p from Product p where p.price = :price"
                )
        }
)
  3. Use @NamedQueries annotation’s name attribute to set the name of the named query:
    4. 
@NamedQuery(
        name = "Product.findByPrice",
        query = "SELECT p from Product p where p.price = :price"
)
  4. Use @NamedQueries annotation’s query attribute to set the JPQL query the value:
    5. 
@NamedQuery(
        name = "Product.findByPrice",
        query = "SELECT p from Product p where p.price = :price"
)
  5. Use named query name in a Spring Data JPA Repository:
    6. 
// Define Named JPQL query
Product findByPrice(@Param("price") BigDecimal price);

List<Product> findAllOrderByNameDesc();

Let's create a Spring boot project from the scratch and let's understand the usage of @NamedQueries annotation in Spring Data JPA.

1. Creating Spring Boot Project

Spring Boot provides a web tool called https://start.spring.io to bootstrap an application quickly. Just go to https://start.spring.io and generate a new spring boot project.

Use the below details in the Spring boot creation:

Project Name: spring-data-jpa-course

Project Type: Maven

Choose dependencies: Spring Data JPA, MySQL Driver, Lombok

Package name: net.javaguides.springboot

2. Maven Dependencies

Here is the complete pom.xml for your reference:


    <?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>3.0.4</version>
            <relativePath/> <!-- lookup parent from repository -->
        </parent>
        <groupId>net.javaguides</groupId>
        <artifactId>spring-data-jpa-course</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>spring-data-jpa-course</name>
        <description>Demo project for Spring Boot</description>
        <properties>
            <java.version>17</java.version>
        </properties>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jpa</artifactId>
            </dependency>
    
            <dependency>
                <groupId>com.mysql</groupId>
                <artifactId>mysql-connector-j</artifactId>
                <scope>runtime</scope>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    
    </project>

3. Configure MySQL database

Let's use the MySQL database to store and retrieve the data in this example and we gonna use Hibernate properties to create and drop tables.

Open the application.properties file and add the following configuration to it:


    spring.datasource.url=jdbc:mysql://localhost:3306/demo?useSSL=false
    spring.datasource.username=root
    spring.datasource.password=Mysql@123
    
    spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQLDialect
    
    spring.jpa.hibernate.ddl-auto = create-drop

Make sure that you will create a demo database before running the Spring boot application.

Also, change the MySQL username and password as per your MySQL installation on your machine.

4. Create JPA Entity - Product.java

Let's create an entity package inside a base package "net.javaguides.springboot".

Within the entity package, create a Product class with the following content:


import lombok.*;
import org.hibernate.annotations.CreationTimestamp;
import org.hibernate.annotations.UpdateTimestamp;

import jakarta.persistence.*;
import java.math.BigDecimal;
import java.time.LocalDateTime;

@Entity
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString

@NamedQueries(
        {
                @NamedQuery(
                        name = "Product.findAllOrderByNameDesc",
                        query = "SELECT p from Product p ORDER By p.name DESC"
                ),
                @NamedQuery(
                        name = "Product.findByPrice",
                        query = "SELECT p from Product p where p.price = :price"
                )
        }
)
@Table(
        name = "products",
        schema = "ecommerce",
        uniqueConstraints = {
                @UniqueConstraint(
                        name = "sku_unique",
                        columnNames = "stock_keeping_unit"
                )
        }
)
public class Product {

    @Id
    @GeneratedValue(
            strategy = GenerationType.SEQUENCE,
            generator = "product_generator"
    )

    @SequenceGenerator(
            name = "product_generator",
            sequenceName = "product_sequence_name",
            allocationSize = 1
    )
    private Long id;

    @Column(name = "stock_keeping_unit", nullable = false)
    private String sku;

    @Column(nullable = false)
    private String name;

    private String description;
    private BigDecimal price;
    private boolean active;
    private String imageUrl;

    @CreationTimestamp
    private LocalDateTime dateCreated;

    @UpdateTimestamp
    private LocalDateTime lastUpdated;
}

Note that we are using Lombok annotations to reduce the boilerplate code.

5. Create Spring Data JPA Repository

The next thing we’re gonna do is to create a repository to access Product entity data from the database..

The JpaRepository interface defines methods for all the CRUD operations on the entity, and a default implementation of the JpaRepository called SimpleJpaRepository

Let's create a repository package inside a base package "net.javaguides.springdatarest".

Within the repository package, create a ProductRepository interface with the following content:


import com.springdatajpa.springboot.entity.Product;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.query.Param;

import java.math.BigDecimal;


public interface ProductRepository extends JpaRepository 
{
    // Define Named JPQL query
    Product findByPrice(@Param("price") BigDecimal price);
    List findAllOrderByNameDesc();
}

6. Testing Named JPQL Query


import com.springdatajpa.springboot.entity.Product;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.math.BigDecimal;
import java.util.List;

@SpringBootTest
public class NamedQueriesTest {

    @Autowired
    private ProductRepository productRepository;

    @Test
    void namedJPQLQueries(){

        List products = productRepository.findAllOrderByNameDesc();

        products.forEach((p) -> {
            System.out.println(p.getName());
            System.out.println(p.getDescription());
        });

        Product product = productRepository.findByPrice(new BigDecimal(200));

        System.out.println(product.getName());
        System.out.println(product.getDescription());

    }
}

Output: