Spring Boot Named Query Example (2024)

To create a named query in Spring Boot JPA, we can either use a JPQL query or a native SQL query in quite similar fashion.

In this tutorial, we'll demonstrate how to use @NamedQuery + JPQL query in Spring Boot JPA application.

Q: What is Named Query in JPA?
Ans:

A named query is a query that is statically defined and has an unchangeable query string. By separating the JPQL query strings from the Java code, using named queries instead of dynamic queries may enhance code. JPQL query is created based on the entity model class.

The JPA specification provides a query language by itself. JPQL (JPA + SQL) syntax is identical to SQL.

Create Spring Boot application

Create Spring Boot application from Spring Initializr.

Project Structure

Add Dependencies

Add below dependencies for Web - spring-boot-starter-web , JPA - spring-boot-starter-data-jpa and MYSQL - mysql-connector-java in pom.xml.

<?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>2.2.1.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.techgeeknext</groupId>
	<artifactId>spring-boot-jpa-mysql-example</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>spring-boot-jpa-mysql-example</name>
	<description>Spring Boot + JPA + MYSQL CRUD Example</description>

	<properties>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>

		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>

	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

Application Properties

Add database connection details in application.properties file.

spring.datasource.url=jdbc:mysql://localhost/employeetestdb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.platform=mysql
spring.datasource.initialization-mode=always

## Hibernate Properties
# The SQL dialect makes Hibernate generate better SQL for the chosen database
# spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto = update

Named query in spring boot with parameters

1. Create Employee data model entity class, contains id, name and role.
2. Simply add the @NamedQuery annotation to one of your entity classes and specify name and query attributes.
3. The name of query must be unique. It will be used to link the query in business code or JPA repository.
4. If we're using named query in a Spring Data JPA repository, the naming convention should be entity class name + . + repository method's name, for example Employee.findById.

package com.techgeeknext.model;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Entity
@Table(name = "employees")
@NamedQuery(name = "Employee.findById", query = "FROM Employee WHERE id = ?1")
@NamedQuery(name = "Employee.findByName", query = "FROM Employee WHERE name = ?1")
@NamedQuery(name = "Employee.findByRole", query = "SELECT e FROM Employee e WHERE e.role = ?1")
public class Employee {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private long id;

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

	@Column(name = "role")
	private String role;
}

JPA Repository

Create EmployeeRepository interface that extends JpaRepository, with the methods matching the named query name.

package com.techgeeknext.repository;

import com.techgeeknext.model.Employee;
import org.springframework.data.jpa.repository.JpaRepository;

public interface EmployeeRepository extends JpaRepository<Employee, Long> {

    Employee findById(long id);
    Employee findByName(String name);
    Employee findByRole(String role);
}

Employee Controller to test Named Query

package com.techgeeknext.controller;

import com.techgeeknext.model.Employee;
import com.techgeeknext.repository.EmployeeRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.Optional;

@RestController
public class EmployeeController {

    @Autowired
    EmployeeRepository employeeRepository;


    /**
     * Get the employee by id
     *
     * @param id
     * @return ResponseEntity
     */
    @GetMapping("/employee/{id}")
    public ResponseEntity<Employee> getEmployeeById(@PathVariable("id") long id) {
        try {
           // retrieve the record from database
            Optional<Employee> empObj = Optional.ofNullable(employeeRepository.findById(id));

            //check if employee exist in database
            if (empObj.isPresent()) {
                return new ResponseEntity<>(empObj.get(), HttpStatus.OK);
            }

            return new ResponseEntity<>(HttpStatus.NOT_FOUND);

        } catch (Exception e) {
            return new ResponseEntity<>(null, HttpStatus.INTERNAL_SERVER_ERROR);
        }

    }

    /**
     * Get the employee by name
     *
     * @param name
     * @return ResponseEntity
     */
    @GetMapping("/employee/name/{name}")
    public ResponseEntity<Employee> getEmployeeByName(@PathVariable("name") String name) {
        try {
            // retrieve the record from database
            Optional<Employee> empObj = Optional.ofNullable(employeeRepository.findByName(name));

            //check if employee exist in database
            if (empObj.isPresent()) {
                return new ResponseEntity<>(empObj.get(), HttpStatus.OK);
            }

            return new ResponseEntity<>(HttpStatus.NOT_FOUND);

        } catch (Exception e) {
            return new ResponseEntity<>(null, HttpStatus.INTERNAL_SERVER_ERROR);
        }

    }


    /**
     * Get the employee by role
     *
     * @param role
     * @return ResponseEntity
     */
    @GetMapping("/employee/role/{role}")
    public ResponseEntity<Employee> getEmployeeByRole(@PathVariable("role") String role) {
        try {
            // retrieve the record from database
            Optional<Employee> empObj = Optional.ofNullable(employeeRepository.findByRole(role));

            //check if employee exist in database
            if (empObj.isPresent()) {
                return new ResponseEntity<>(empObj.get(), HttpStatus.OK);
            }

            return new ResponseEntity<>(HttpStatus.NOT_FOUND);

        } catch (Exception e) {
            return new ResponseEntity<>(null, HttpStatus.INTERNAL_SERVER_ERROR);
        }

    }

    /**
     * Create new employee
     *
     * @param employee
     * @return ResponseEntity
     */
    @PostMapping("/employee")
    public ResponseEntity<Employee> createEmployee(@RequestBody Employee employee) {
        Employee newEmployee = employeeRepository
                .save(Employee.builder()
                        .name(employee.getName())
                        .role(employee.getRole())
                        .build());
        return new ResponseEntity<>(newEmployee, HttpStatus.OK);
    }

    /**
     * Get all the employees
     *
     * @return ResponseEntity
     */
    @GetMapping("/employees")
    public ResponseEntity<List<Employee>> getEmployees() {
        try {
            return new ResponseEntity<>(employeeRepository.findAll(), HttpStatus.OK);
        } catch (Exception e) {
            return new ResponseEntity<>(null, HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }
}

Test Spring Boot Named Query Example

1. Start the Spring Boot Application by running spring-boot:run or by running main class.

2. Create New Employee

   Open Postman, use POST method with end point http://localhost:8080/employee and provide Employee details to create new employee record.

3. List Employees

   Use GET method with end point http://localhost:8080/employees to get all employees.

4. Get Employee By Id

   Use GET method with end point http://localhost:8080/employee/1 to get employee by id.

5. Get Employee By Name

   Use GET method with end point http://localhost:8080/employee/name/TechGeekNextUser to get employee by name.

6. Get Employee By Role

   Use GET method with end point http://localhost:8080/employee/role/Supervisor to get employee by role.

Download Source Code

The full source code for this article can be found below.

1. Download it here - Spring Boot JPA Named Query Example