Spring Data JPA @NamedQuery and @NamedQueries Example

Spring Data JPA provides multiple ways of selecting or fetching data from database server and one of the ways is using @NamedQuery or @NamedQueries. @NamedQuery and @NamedQueries can be created using XML configuration, external properties file or using Java based annotation config. Spring Data JPA supports both JPQL and SQL native named queries.

Here I am going to show you examples on @NamedQuery and @NamedQueries. I am also going to use named parameter and positional parameter in the named queries. In XML configuration you need to use <named-query/> and for annotation based configuration you need to use @NamedQuery. @NamedQuery is used to define only single query and you need to declare your multiple @NamedQuery inside @NamedQueries for multiple named queries.

Declaring named query is pretty simple and there are two parts – name and query. Name for your query must be unique in your application persistence context. The name of query is formed using entity class’s name followed by a dot (.) and the name of the method declared in the repository interface. For example, Item.findByName. Here Item is the name of the entity class and findByName is the method which is declared in the repository interface.

Named Queries Declaration

You can declare your named queries in different ways. If you want to declare named queries in classpath XML configuration then you can use following configuration:

....

<named-query name="Item.findAll">
  <query>select i from Item i</query>
</named-query>

<named-query name="Item.findByName">
  <query>select i from Item i where i.itemName = ?1</query>
</named-query>

<named-query name="Item.findByPrice">
  <query>select i from Item i where i.itemPrice = :price</query>
</named-query>

...

Using classpath properties file you can use the following configuration:

Item.findAll = select i from Item i

Item.findByName = select i from Item i where i.itemName = ?1

Item.findByPrice = select i from Item i where i.itemPrice = :price

Using Java based annotation you can use the following example. Here in the below example you will see the usage of @NamedQuery and @NamedQueries.

@Entity
@Table(name = "item")
@NamedQueries(value = { @NamedQuery(name = "Item.findAll", query = "select i from Item i"),
        @NamedQuery(name = "Item.findByName", query = "select i from Item i where i.itemName = ?1"),
        @NamedQuery(name = "Item.findByPrice", query = "select i from Item i where i.itemPrice = :price") })
public class Item {
    ...
}

The coressponding repository interface would look like below:

public interface NameQueryRepository extends JpaRepository<Item, Long> {

    List<Item> findAll();

    List<Item> findByName(String name);

    Item findByPrice(@Param("price") Double price);

}

Prerequisites

Java at least 8, Spring Boot 2.3.4, Gradle 6.5.1, Maven 3.6.3, H2 1.4.200, JAXB API

Project Setup

You can create either gradle or maven based project in your favorite tool. The name of the project is spring-data-jpa-named-query.

if you are creating gradle based project then you can use below build.gradle script:

buildscript {
	ext {
		springBootVersion = '2.3.4.RELEASE'
	}
	
    repositories {
    	mavenCentral()
    }
    
    dependencies {
    	classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
    }
}

plugins {
    id 'java-library'
    id 'org.springframework.boot' version "${springBootVersion}"
}

sourceCompatibility = 12
targetCompatibility = 12

repositories {
    mavenCentral()
}

dependencies {
	implementation("org.springframework.boot:spring-boot-starter:${springBootVersion}")
	implementation("org.springframework.boot:spring-boot-starter-data-jpa:${springBootVersion}")
	
	runtime("com.h2database:h2:1.4.200")
	
	//required for JDK 9 or above
    implementation 'javax.xml.bind:jaxb-api:2.4.0-b180830.0359'
}

If you are creating maven based project then you can use below pom.xml file:

<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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.roytuts</groupId>
	<artifactId>spring-data-jpa-named-query</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.3.4.RELEASE</version>
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>
		
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		
		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<version>1.4.200</version>
		</dependency>
		
		<!--required only if jdk 9 or higher version is used-->
		<dependency>
			<groupId>javax.xml.bind</groupId>
			<artifactId>jaxb-api</artifactId>
			<version>2.4.0-b180830.0359</version>
		</dependency>
	</dependencies>

    <build>
        <plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
				<configuration>
					<source>at least 8</source>
					<target>at least 8</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

In the above build files I have added JAXB API which is required when you are working with database server if you are using JDK 9 or above because JAXB API is no longed included into JDK 9 or above library version.

I am using here in-memory H2 database and embedded datasource for executing SQL scripts that are used to create table and insert data into the table on runtime. In-memory database is recommended to do quick PoC where you may not have access to the external database server.

SQL Scripts

Here I am using SQL scripts to create table and insert data into the table in runtime into H2 database. The following classpath file src/main/resources/sql/table.sql is used to create table.

CREATE TABLE `item` (
  `item_id` int(10) NOT NULL AUTO_INCREMENT,
  `item_name` varchar(45) NOT NULL,
  `item_desc` text,
  `item_price` double NOT NULL DEFAULT '0',
  PRIMARY KEY (`item_id`)
);

The following classpath file src/main/resources/data.sql is used to insert data into the above table.

insert into `item`(`item_id`,`item_name`,`item_desc`,`item_price`) 
values (1,'CD','CD is a compact disk',100),
(2,'DVD','DVD is larger than CD in size',150),
(3,'ABC','ABC test description',24),
(4,'XYZ','XYZ test description',25.32),
(5,'CD Player','CD player is used to play CD',30.02),
(6,'New Item1','New Item1 Desc',125);

App Config

The application.properties file in the classpath folder src/main/resources is used to configure your application for various reasons, such as, putting database details, encrypted credentials, environment setting details, etc.

Here I have enabled H2 database console to verify the query that gets executed in the application. I have also enabled the query logging in the console. The property spring.jpa.hibernate.ddl-auto with value update enables you to execute the data.sql file otherwise data.sql file will not be executed and your table will not have any data.

spring.jpa.show-sql=true
spring.h2.console.enabled=true
spring.jpa.hibernate.ddl-auto = update

DataSource Configuration

As I said previously that I am using embedded datasource, so look at the following code how I am configuring datasource to execute the SQL scripts.

package com.roytuts.spring.data.jpa.named.query.config;

import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabase;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;

@Configuration
public class Config {

    @Bean
    public DataSource dataSource() {
        EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
        EmbeddedDatabase db = builder.setType(EmbeddedDatabaseType.H2).addScript("classpath:sql/table.sql")
                .addScript("classpath:sql/data.sql").build();
        return db;
    }

}

Entity

Entity class that is a mapping between the Java object and database table. The class attributes or fields map to the corresponding table columns. If you have the exact column and field names then you don’t need to declare the name attribute in @Column annotation on Java fields.

In the entity class I have declared three @NamedQuery and these named queries are included into @NamedQueries. Each of the named query has name and query parts. The name part consists of entity class name followed by dot (.) and method name. The exact method name is declared in the repository interface.

package com.roytuts.spring.data.jpa.named.query.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

@Entity
@Table(name = "item")
@NamedQueries(value = { @NamedQuery(name = "Item.findAll", query = "select i from Item i"),
        @NamedQuery(name = "Item.findByName", query = "select i from Item i where i.itemName = ?1"),
        @NamedQuery(name = "Item.findByPrice", query = "select i from Item i where i.itemPrice = :price") })
public class Item {

    @Id
    @Column(name = "item_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long itemId;

    @Column(name = "item_name")
    private String itemName;

    @Column(name = "item_desc")
    private String itemDesc;

    @Column(name = "item_price")
    private Double itemPrice;

    // getters and setters
    
    @Override
    public String toString() {
        return "Item [itemId=" + itemId + ", itemName=" + itemName + ", itemDesc=" + itemDesc + ", itemPrice="
                + itemPrice + "]";
    }

}

Repository

The JPA repository provides basic CRUD operations out of the box. Look at the below query methods which I declared in the entity class above. In the third method I have used named parameter for the query.

Spring Data tries to resolve a call to these methods to a named query, starting with the simple name of the configured domain class, followed by the method name separated by a dot. So the preceding example would use the named queries defined earlier instead of trying to create a query from the method name.

package com.roytuts.spring.data.jpa.named.query.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.query.Param;

import com.roytuts.spring.data.jpa.named.query.entity.Item;

public interface NameQueryRepository extends JpaRepository<Item, Long> {

    List<Item> findAll();

    List<Item> findByName(String name);

    Item findByPrice(@Param("price") Double price);

}

Service

The service class is used to process the business logic. In this class I have used the repository interface.

package com.roytuts.spring.data.jpa.named.query.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.roytuts.spring.data.jpa.named.query.entity.Item;
import com.roytuts.spring.data.jpa.named.query.repository.NameQueryRepository;

@Service
public class NamedQueryService {

    @Autowired
    private NameQueryRepository repository;

    public List<Item> getAllItems() {
        return repository.findAll();
    }

    public List<Item> getItemsByName(final String name) {
        return repository.findByName(name);
    }

    public Item getItemByPrice(final Double price) {
        return repository.findByPrice(price);
    }

}

Main Class

A class with main method and and @SpringBootApplication annotation is enough to deploy the Spring Boot application into the embedded tomcat server.

package com.roytuts.spring.data.jpa.named.query;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

import com.roytuts.spring.data.jpa.named.query.entity.Item;
import com.roytuts.spring.data.jpa.named.query.service.NamedQueryService;

@SpringBootApplication
@EntityScan("com.roytuts.spring.data.jpa.named.query.entity")
@EnableJpaRepositories("com.roytuts.spring.data.jpa.named.query.repository")
public class SpringDataJpaNamedQueryApp implements CommandLineRunner {

    @Autowired
    private NamedQueryService service;

    public static void main(String[] args) {
        SpringApplication.run(SpringDataJpaNamedQueryApp.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
        List<Item> itemList = service.getAllItems();

        itemList.stream().forEach(i -> System.out.println(i));

        List<Item> items = service.getItemsByName("CD");

        items.stream().forEach(i -> System.out.println(i));

        Item item = service.getItemByPrice(30.02);
        System.out.println(item);
    }

}

Testing the Application

Executing the above main class will give you the following output. You can see all items, item by name and item by price.

Hibernate: select item0_.item_id as item_id1_0_, item0_.item_desc as item_des2_0_, item0_.item_name as item_nam3_0_, item0_.item_price as item_pri4_0_ from item item0_
Item [itemId=1, itemName=CD, itemDesc=CD is a compact disk, itemPrice=100.0]
Item [itemId=2, itemName=DVD, itemDesc=DVD is larger than CD in size, itemPrice=150.0]
Item [itemId=3, itemName=ABC, itemDesc=ABC test description, itemPrice=24.0]
Item [itemId=4, itemName=XYZ, itemDesc=XYZ test description, itemPrice=25.32]
Item [itemId=5, itemName=CD Player, itemDesc=CD player is used to play CD, itemPrice=30.02]
Item [itemId=6, itemName=New Item1, itemDesc=New Item1 Desc, itemPrice=125.0]
Hibernate: select item0_.item_id as item_id1_0_, item0_.item_desc as item_des2_0_, item0_.item_name as item_nam3_0_, item0_.item_price as item_pri4_0_ from item item0_ where item0_.item_name=?
Item [itemId=1, itemName=CD, itemDesc=CD is a compact disk, itemPrice=100.0]
Hibernate: select item0_.item_id as item_id1_0_, item0_.item_desc as item_des2_0_, item0_.item_name as item_nam3_0_, item0_.item_price as item_pri4_0_ from item item0_ where item0_.item_price=?
Item [itemId=5, itemName=CD Player, itemDesc=CD player is used to play CD, itemPrice=30.02]

Source Code

Download

One Thought to “Spring Data JPA @NamedQuery and @NamedQueries Example”

  1. Touche. Outstanding arguments. Keep up the good work. Paule Berkly Rodger

Leave a Comment