Spring Data JPA - @NamedQueries Example

Author:

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:

@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 @NamedQuery annotations from JPA/Hibernate to define named queries:

@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 @NamedQuery annotation’s name attribute to set the name of the named query:

@NamedQuery(
        name = "Product.findByPrice",
        query = "SELECT p from Product p where p.price = :price"
)

4. Use @NamedQuery annotation’s query attribute to set the JPQL query the value:

@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:

    // 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<Product, Long> 
{
    // Define Named JPQL query
    Product findByPrice(@Param("price") BigDecimal price);
    List<Product> 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<Product> 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:


Related Spring Data JPA Tutorials and Examples

Comments

Post a Comment

Leave Comment