Introduction
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 class-path 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 = :priceUsing 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 1.8+, Spring Boot 2.3.4/2.7.0, Gradle 6.5.1, Maven 3.6.3/3.8.5, H2 1.4.200, MySQL 8.0.26, 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/2.7.0</version>
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>11</maven.compiler.source>
<maven.compiler.target>11</maven.compiler.target>
</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>
<!--<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</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>
</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.
H2 Database – 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);MySQL Database
You can also use MySQL database instead of H2 database. The following item table with data can be used for this example.
CREATE TABLE `item` (
`item_id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
`item_name` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
`item_desc` text COLLATE utf8mb4_unicode_ci,
`item_price` double COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
PRIMARY KEY (`item_id`)
);
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 = updateRemember: If you are using MySQL database and creating table manually in the MySQL database then you do not need to create .sql files under the classpath folder. You also need to make sure that your entity class does not create table in the MySQL database server by using the following line in the application.properties file:
spring.jpa.hibernate.ddl-auto = noneMake sure you put the application.properties file under classpath folder src/main/resources with the following content:
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/roytuts
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.show-sql=true
spring.h2.console.enabled=true
spring.jpa.hibernate.ddl-auto = none
#spring.jpa.hibernate.ddl-auto = updateDataSource 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. This configuration class only needed for H2 database and you don’t need for MySQL database because I have configured through application.properties file.
@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.
@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.
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.
@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.
@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 @NamedQuery and @NamedQueries
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]
you are beest, very nicee blog your amazing site.
Touche. Outstanding arguments. Keep up the good work. Paule Berkly Rodger