How to create native query in JPA Repository

by

Native query is a plain SQL query of the database. We can create a native query in JPA Repository by using @Query annotation with its attribute nativeQuery set to be true in the method of the JPA Repository of the Spring Data JPA. In this topic, we will learn how to create a native query in JPA Repository using the @Query annotation of the Spring Data JPA. We will create a restful web services application in Spring Boot application using Maven, Spring Web, Spring Data JPA, Lombok, and H2 database.

create_native_query_jpa_repository

Example of native query

select * from employee;

Example of creating a native query in JPA Repository

public interface EmplRepository extends JpaRepository<Employee, Long>{
@Query(value = "select * from employee", nativeQuery = true)
public List<Employee> findAll();
}

JQPL vs Native SQL query

In JPQL we need to use Entity class name, alias name and its properties names for the condition clause whereas native SQL needs to use table name, star symbol (*) and column names for the condition clause. If we need a complex query, we will use a native query.

Let’s implement to create a native query in the JPA Repository using Spring Boot step-by-step.

1. Creating a Spring Boot Starter Project

We are creating a Spring Boot Application from the web tool Spring Initializr or you can create it from the IDE(STS, VS Code etc.) you are using. 

 Add the following dependencies: 

  • Spring Web
  • Spring Data JPA
  • Lombok
  • H2 Database

2. Keep the IDE ready

We are importing this created application into our Eclipse IDE or you can import it into another IDE you are using. You can refer to this article to create and set up the Spring Boot Project in Eclipse IDE.

Project Structure 

3. Maven Dependency

Here is the complete pom.xml file for the Spring Boot Application.
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.6.3</version>
		<relativePath />
		<!-- lookup parent from repository -->
	</parent>
	<groupId>com.springjava</groupId>
	<artifactId>demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>demo</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>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>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</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>

4. Defining the configuration

We are configuring the H2 database configuration in the application.properties file.
application.properties

# H2 Database Configuration
spring.datasource.url=jdbc:h2:mem:test
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=update
spring.h2.console.enabled=true

5. Creating a JPA Entity

We are creating a JPA entity class Employee with these properties(id, name, email, and mobileNo).
Employee.java

package com.springjava.entity;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import lombok.Data;

@Data

@Entity

public class Employee {

  @Id

  @GeneratedValue(strategy = GenerationType.IDENTITY)

  private Long id;

  private String name;

  private String email;

  private String mobileNo;

}
  • This @Data annotation is used for a constructor, setter method, getter method, etc.
  • This @Entity annotation is used to create a table through Java code in the database.

6. Creating a JPA Repository

We are a JPA Repository to interact with the JPA Entity class and add custom queries in the repository by using @Query annotation of the Spring Data JPA. Here we can write custom queries using native SQL queries.
EmplRepository.java

package com.springjava.repository;

import java.util.List;

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

import org.springframework.data.jpa.repository.Query;

import com.springjava.entity.Employee;

public interface EmplRepository extends JpaRepository < Employee, Long > {

  @Query(value = "select * from employee", nativeQuery = true)

  List < Employee > findAll();

  @Query(value = "select * from employee where email=?1", nativeQuery = true)

  Employee findByEmail(String email);

  @Query(value = "select * from employee where mobile_no=:mobNo", nativeQuery = true)

  Employee findByMobileNo(String mobNo);

}
  • Native SQL query is binding parameters in two ways(Positional Parameter and Named Parameter)
  • The Positional Parameter is defined by the “?” followed by the number(?1). This is their position in the query that references the parameter as we have used above. Spring Data JPA will automatically replace values with each parameter in the same position order as the positional parameter of the query.

For Example  

@Query(value="select * from employee where email=?1",nativeQuery = true)
Employee findByEmail(String email);
  •  The Named Parameter is the parameter that starts with the symbol colon(:) followed by the name of the parameter(:mobNo) in the query.

For Example  

@Query(value="select * from employee where mobile_no=:mobNo",nativeQuery = true)
Employee findByMobileNo(String mobNo);

7. Creating a Service Interface

We are creating a Service interface with some method declaration. So the implementation class of this interface overrides these declared methods in that class.
EmployeeService.java

package com.springjava.service;

import java.util.List;

import com.springjava.entity.Employee;

public interface EmployeeService {

  void save(Employee employee);

  List < Employee > findAll();

  Employee findByEmail(String email);

  Employee findByMobileNo(String mobNo);

}

8. Creating a Service class

We are creating a Service class EmployeeServiceImpl and this class is implementing the EmployeeService interface. This class is annotated with @Service annotation to act service for this implementation of the custom query with native SQL queries of the repository to communicate with persistence operation in the table of the database.
EmployeeServiceImpl.java

package com.springjava.service;

import java.util.List;

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

import org.springframework.stereotype.Service;

import com.springjava.entity.Employee;

import com.springjava.repository.EmplRepository;

@Service

public class EmployeeServiceImpl implements EmployeeService {

  @Autowired

  EmplRepository empRepo;

  @Override

  public void save(Employee employee) {

    empRepo.save(employee);

  }

  @Override

  public List < Employee > findAll() {

    return empRepo.findAll();

  }

  @Override

  public Employee findByEmail(String email) {

    return empRepo.findByEmail(email);

  }

  @Override

  public Employee findByMobileNo(String mobNo) {

    return empRepo.findByMobileNo(mobNo);

  }

}

9. Creating a RestConroller class

We are creating a RestController class EmployeeController in which all methods are created for API endpoints for handling requests from the client. 
EmployeeController.Java

package com.springjava.controller;

import java.util.LinkedHashMap;

import java.util.List;

import java.util.Map;

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

import org.springframework.http.HttpStatus;

import org.springframework.http.ResponseEntity;

import org.springframework.web.bind.annotation.GetMapping;

import org.springframework.web.bind.annotation.PathVariable;

import org.springframework.web.bind.annotation.PostMapping;

import org.springframework.web.bind.annotation.RequestBody;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;

import com.springjava.entity.Employee;

import com.springjava.service.EmployeeService;

@RestController

@RequestMapping("/api/employee")

public class EmployeeController {

  @Autowired

  EmployeeService empService;

  @PostMapping("/save")

  public ResponseEntity < ? > saveEmp(@RequestBody Employee employee) {

    Map < String, Object > respEmp = new LinkedHashMap < String, Object > ();

    empService.save(employee);

    respEmp.put("status", 1);

    respEmp.put("message", "Record is Saved Successfully!");

    return new ResponseEntity < > (respEmp, HttpStatus.CREATED);

  }

  @GetMapping("/list")

  public ResponseEntity < ? > getEmps() {

    Map < String, Object > respEmp = new LinkedHashMap < String, Object > ();

    List < Employee > emList = empService.findAll();

    if (!emList.isEmpty()) {

      respEmp.put("status", 1);

      respEmp.put("data", emList);

      return new ResponseEntity < > (respEmp, HttpStatus.OK);

    } else {

      respEmp.clear();

      respEmp.put("status", 0);

      respEmp.put("message", "Data is not found");

      return new ResponseEntity < > (respEmp, HttpStatus.NOT_FOUND);

    }

  }

  @GetMapping("/by-email/{email}")

  public ResponseEntity < ? > getEmpByEmail(@PathVariable String email) {

    Map < String, Object > respEmp = new LinkedHashMap < String, Object > ();

    try {

      Employee emp = empService.findByEmail(email);

      respEmp.put("status", 1);

      respEmp.put("data", emp);

      return new ResponseEntity < > (respEmp, HttpStatus.OK);

    } catch (Exception ex) {

      respEmp.clear();

      respEmp.put("status", 0);

      respEmp.put("message", "Data is not found");

      return new ResponseEntity < > (respEmp, HttpStatus.NOT_FOUND);

    }

  }

  @GetMapping("/by-mobile/{mob}")

  public ResponseEntity < ? > getEmpByMob(@PathVariable String mob) {

    Map < String, Object > respEmp = new LinkedHashMap < String, Object > ();

    try {

      Employee emp = empService.findByMobileNo(mob);

      respEmp.put("status", 1);

      respEmp.put("data", emp);

      return new ResponseEntity < > (respEmp, HttpStatus.OK);

    } catch (Exception ex) {

      respEmp.clear();

      respEmp.put("status", 0);

      respEmp.put("message", "Data is not found");

      return new ResponseEntity < > (respEmp, HttpStatus.NOT_FOUND);

    }

  }

}
  • This class is annotated with @RestController annotation to make this class act as Rest Controller for giving responses in JSON form.
  • We used @RequestMapping annotation to define the base URL for the application.
  • We used @PostMapping and @GetMapping annotations in the methods for handling HTTP requests from the client.
  • We used ResponseEntity to represent the entire HTTP response.
  • We used @Autowired to inject EmployeeService in the class.

10. Run the Spring Boot Application and Check

To Right-Click this Spring Boot application on the DemoApplication.java then click on Run As, and select Java Application. 

Check H2 Database

Check the H2 database console and browse this URL “http://localhost:8080/h2-console”.

create_native_query_jpa_repository

See the below table here:

create_native_query_jpa_repository

Testing API on the Postman

Saving the employee data
POST: http://localhost:8080/api/employee/save

create_native_query_jpa_repository

Check the table:

create_native_query_jpa_repository

Retrieving the employee data
GET: http://localhost:8080/api/employee/list

create_native_query_jpa_repository

Retrieving the employee data by email
GET: http://localhost:8080/api/employee/by-email/test@gmail.com

create_native_query_jpa_repository

Retrieving the employee by mobileNo
GET: http://localhost:8080/api/employee/by-mobile/1234567890

create_native_query_jpa_repository

Conclusion

In this topic, we learnt how to create custom queries in the JPA Repository in the Spring Boot restful web services application using @Query with native SQL query of the Spring Data JPA.

Leave a Comment