Spring Boot Native Query Example (2024)

To create a native 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 Native Query in Spring Boot JPA application.

Q: What is Native Query in JPA?
Ans:

Native queries are real SQL queries. These queries are SQL statements that can be simply run in the database.

Spring JPA do not customize the provided query to the SQL dialect according to our database. As a result, we must guarantee that the provided query syntax should be according to our database.

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>

Take a look at our suggested posts:

Spring Boot JPA CRUD Example

Spring Boot - Transaction Management

Spring Boot - JWT Authentication Example

Spring Boot - JWT + Angular 8 Authentication Example

Spring Boot - JWT + MYSQL Authentication Example

Spring Boot - Session Management using Redis

OAuth2.0 Tutorial

Spring Boot - Hazelcast

Spring Boot - JPA + REST + Swagger + MYSQL Example

Java 15

Java 14

Java 8 Interview Questions and Answers

Angular 8 Spring Boot Example

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

Native query in Entity Class

Create Employee data model entity class, contains id, empName and role.
Add the @NamedNativeQuery annotation to one of your entity classes and specify name , query and resultClass attributes.
The name of query must be unique. It will be used to link the query in business code or JPA repository.
If we're using native query in a Spring Data JPA repository, the naming convention should be entity class name + . + repository method's name, for example Employee.findByName.

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")
@NamedNativeQuery (name = "Employee.findByName",
		query = "SELECT * FROM employees WHERE emp_name = ?1",
		resultClass = Employee.class)
public class Employee {

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

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

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

JPA Repository

Create EmployeeRepository interface that extends JpaRepository, with the methods matching the native query name specified in the Employee Entity Class.
Extend class with PagingAndSortingRepository for sorting and pagination.
To use native query in Repository class, use @Query annotation with value as query and mark nativeQuery = true
For pagination, we need to provide count query.
Use @Modifying for any modification queries like update, delete.
TransactionRequiredException
javax.persistence.TransactionRequiredException Exception, don't forget to include @Transactional, which is required for modification queries, or else the following exception will occur.
```
javax.persistence.TransactionRequiredException: Executing an update/delete query
	at org.hibernate.internal.AbstractSharedSessionContract.checkTransactionNeededForUpdateOperation(AbstractSharedSessionContract.java:413) 
```

package com.techgeeknext.repository;

import com.techgeeknext.model.Employee;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;

@Repository
@Transactional
public interface EmployeeRepository extends JpaRepository<Employee, Long>,
        PagingAndSortingRepository<Employee, Long> {

    //it's linked with the native query from Employee Entity Class
    Employee findByName(String name);

    //using positional parameters with question mark prefix followed by parameter sequence number like ?1,?2
    @Query(value = "select * from employees where id = ?1", nativeQuery = true)
    Employee findById(String name);


    @Query(value="select * from employees",
            countQuery = "select count(id) from employees",
            nativeQuery = true)
    Page<Employee> getEmployeePageAcsByCol(Pageable page);

    @Modifying
    @Query(value="update employees set emp_name= ?1 where id = ?2", nativeQuery=true)
    void updateEmployeeById(String empName, long id);

    @Modifying
    // or can use named parameters with colon prefix followed by parameter like :id
    @Query(value="delete from employees e where e.id= :id", nativeQuery = true)
    void deleteEmployeeById(@Param("id") long id);
}

Employee Controller to test Named Native 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.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
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 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);
        }

    }


    /**
     * Create new employee
     *
     * @param employee
     * @return ResponseEntity
     */
    @PostMapping("/employee")
    public ResponseEntity<Employee> createEmployee(@RequestBody Employee employee) {
        Employee newEmployee = employeeRepository
                .save(Employee.builder()
                        .empName(employee.getEmpName())
                        .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);
        }
    }

    /**
     * Update Employee record by it's id
     *
     * @param id
     * @return
     */
    @PutMapping("/employee/{id}/name/{empName}")
    public ResponseEntity<Employee> updateEmployeeById(@PathVariable("id") long id, @PathVariable("empName") String empName) {

        //check if employee exist in database
        Employee empObj = getEmpRec(id);

        if (empObj != null) {
            employeeRepository.updateEmployeeById(empName, id);
            return new ResponseEntity<>(HttpStatus.OK);
        }

        return new ResponseEntity<>(HttpStatus.NOT_FOUND);
    }

    /**
     * Delete Employee by Id
     *
     * @param id
     * @return ResponseEntity
     */
    @DeleteMapping("/employee/{id}")
    public ResponseEntity<HttpStatus> deleteEmployeeById(@PathVariable("id") long id) {
        try {
            //check if employee exist in database
            Employee emp = getEmpRec(id);

            if (emp != null) {
                employeeRepository.deleteEmployeeById(id);
                return new ResponseEntity<>(HttpStatus.OK);
            }

            return new ResponseEntity<>(HttpStatus.NOT_FOUND);

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

    /**
     * Method to get the employee record by id
     *
     * @param id
     * @return Employee
     */
    private Employee getEmpRec(long id) {
        Optional<Employee> empObj = employeeRepository.findById(id);

        if (empObj.isPresent()) {
            return empObj.get();
        }
        return null;
    }

    @GetMapping("/employee/pagination/sortby/{columnName}")
    public ResponseEntity<List<Employee>> getEmpPaginationAsc(@PathVariable("columnName") String columnName) {
        try {

            Pageable pageRequest = PageRequest.of(0, 5, Sort.by(columnName).ascending());

            // retrieve the record from database
            Optional<List<Employee>> empObj = Optional.ofNullable(
                    employeeRepository.getEmployeePageAcsByCol(pageRequest)
                            .getContent());

            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);
        }

    }



}

Test Spring Boot Native Query Example

Start the Spring Boot Application by running spring-boot:run or by running main class.
Create New Employee
Open Postman, use POST method with end point http://localhost:8080/employee and provide Employee details to create new employee record.
List Employees
Use GET method with end point http://localhost:8080/employees to get all employees records.
Get Employee By Name
Use GET method with end point http://localhost:8080/employee/name/TechGeekNextUser to get employee by name.
Pagination + Sort By column name
Use GET method with end point http://localhost:8080/employee/pagination/sortby/role to get 5 employees, sorted by role in ascending order. We can provide any employee column name to sort.
Update employee name by id
Use PUT method with end point http://localhost:8080/employee/2/name/User to update the employee id "2" with name "User".
Delete employee by id
Use DELETE method with end point http://localhost:8080/employee/2 to delete the employee by id "2".