Spring Data JPA or JPA stands for Java Persistence API, so before looking into that, we must know about ORM (Object Relation Mapping). So Object relation mapping is simply the process of persisting any Java object directly into a database table. A native query is a SQL statement that is specific to a particular database like MySQL. It varies a little from JPQL (Java Persistence Query Language) which is used by Spring Data JPA by default. Below is an example of a native SQL query
SELECT * FROM Student ORDER BY age
It is similar to the standard SQL query. But if you want to use this native query in the Spring Boot project then we have to take the help of @Query Annotation and we have to set an attribute nativeQuery=true in Query annotation to mark the query as native. For example:
@Query(nativeQuery = true, value = "SELECT * FROM Student ORDER BY age")
Optional<Student> findSortedStudentByAge();
So we are going to discuss all these things by developing a complete Spring Boot project. Please follow all the steps below.
Example Spring Boot Project
Step 1: Create a New Spring Boot Project in Spring Initializr
To create a new Spring Boot project, please refer to How to Create a Spring Boot Project in Spring Initializr and Run it in IntelliJ IDEA. For this project choose the following things
- Project: Maven
- Language: Java
- Packaging: Jar
- Java: 17
Please choose the following dependencies while creating the project.
- Spring Boot DevTools
- Spring Data JPA
- MySQL Driver
- Spring Web
Generate the project and run it in IntelliJ IDEA by referring to the above article.
Note: We have used the MySQL database in this project.
Step 2: Create Schema in MySQL Workbench and Put Some Sample Data
Go to your MySQL Workbench and create a schema named gfgmicroservicesdemo and inside that create a table called employee and address and put some sample data as shown in the below image.
Employee Table:
Here we have created 4 columns and put some sample data.
- id
- name
- email
- age
Address Table:
Here we have created 4 columns and put some sample data.
- id
- city
- state
- employee_id
Note: In the Address table, employee_id is a foreign key so create it accordingly. We are going to perform a SQL join operation in our native SQL query. So create tables carefully.
Before moving to IntelliJ IDEA let’s have a look at the complete project structure for our Microservices.
Step 3: Make Changes in Your application.properties File
Now make the following changes in your application.properties file.
spring.datasource.url=jdbc:mysql://localhost:3306/gfgmicroservicesdemo
spring.datasource.username=put your username here
spring.datasource.password=put your password here
server.port=8081
Refer to the below image.
Step 4: Create Your Entity/Model Class
Go to the src > main > java > entity and create a class Address and put the below code. This is our model class.
Java
package com.gfg.addressapp.entity;
import jakarta.persistence.*;
@Entity
@Table (name = "address" )
public class Address {
@Id
@GeneratedValue (strategy = GenerationType.IDENTITY)
@Column (name = "id" )
private int id;
@Column (name = "city" )
private String city;
@Column (name = "state" )
private String state;
public int getId() {
return id;
}
public void setId( int id) {
this .id = id;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this .city = city;
}
public String getState() {
return state;
}
public void setState(String state) {
this .state = state;
}
}
|
Step 5: Create Your Repository Interface
Go to the src > main > java > repository and create an interface AddressRepo and put the below code. This is our repository where we write code for all the database-related stuff.
Java
package com.gfg.addressapp.repository;
import com.gfg.addressapp.entity.Address;
import java.util.Optional;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
public interface AddressRepo extends JpaRepository<Address, Integer> {
@Query (
nativeQuery = true ,
value
= "SELECT ea.id, ea.city, ea.state FROM gfgmicroservicesdemo.address ea join gfgmicroservicesdemo.employee e on e.id = ea.employee_id where ea.employee_id=:employeeId" )
Optional<Address> findAddressByEmployeeId( @Param ( "employeeId" ) int employeeId);
}
|
Here you can see we have used the native SQL query in the same way as we have discussed. This is our native SQL query
SELECT ea.id, ea.city, ea.state FROM gfgmicroservicesdemo.address ea join gfgmicroservicesdemo.employee e on e.id = ea.employee_id where ea.employee_id=:employeeId
And this is how we have used it in Spring Boot
@Query(nativeQuery = true, value = "SELECT ea.id, ea.city, ea.state FROM gfgmicroservicesdemo.address ea join gfgmicroservicesdemo.employee e on e.id = ea.employee_id where ea.employee_id=:employeeId")
Refer to the below image for more explanation
Similarly, you can put your native SQL query according to your requirements in your Spring Boot project.
Note: Please refer to this article to know more about JpaRepository.
Step 6: Create an AddressResponse Class
Go to the src > main > java > response and create a class AddressResponse and put the below code.
Java
package com.gfg.addressapp.response;
public class AddressResponse {
private int id;
private String city;
private String state;
public int getId() {
return id;
}
public void setId( int id) {
this .id = id;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this .city = city;
}
public String getState() {
return state;
}
public void setState(String state) {
this .state = state;
}
}
|
Step 7: Create Your Service Class
Go to the src > main > java > service and create a class AddressService and put the below code. This is our service class where we write our business logic.
Java
package com.gfg.addressapp.service;
import com.gfg.addressapp.entity.Address;
import com.gfg.addressapp.repository.AddressRepo;
import com.gfg.addressapp.response.AddressResponse;
import org.modelmapper.ModelMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.Optional;
@Service
public class AddressService {
@Autowired
private AddressRepo addressRepo;
@Autowired
private ModelMapper mapper;
public AddressResponse findAddressByEmployeeId( int employeeId) {
Optional<Address> addressByEmployeeId = addressRepo.findAddressByEmployeeId(employeeId);
AddressResponse addressResponse = mapper.map(addressByEmployeeId, AddressResponse. class );
return addressResponse;
}
}
|
Step 8: Create an Address Controller
Go to the src > main > java > controller and create a class AddressController and put the below code. Here we are going to create an endpoint “/address/{employeeId}” to find the address using employee_id. Thats why we have created a foreign key in the Address table and we have performed the SQL join operation in the native query to get our desired result.
Java
package com.gfg.addressapp.controller;
import com.gfg.addressapp.response.AddressResponse;
import com.gfg.addressapp.service.AddressService;
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.RestController;
@RestController
public class AddressController {
@Autowired
private AddressService addressService;
@GetMapping ( "/address/{employeeId}" )
public ResponseEntity<AddressResponse> getAddressByEmployeeId( @PathVariable ( "employeeId" ) int employeeId) {
AddressResponse addressResponse = addressService.findAddressByEmployeeId(employeeId);
return ResponseEntity.status(HttpStatus.OK).body(addressResponse);
}
}
|
Step 9: Create a Configuration Class
Go to the src > main > java > configuration and create a class AddressConfig and put the below code.
Java
package com.gfg.addressapp.configuration;
import com.gfg.addressapp.service.AddressService;
import org.modelmapper.ModelMapper;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
public class AddressConfig {
@Bean
public AddressService employeeBean() {
return new AddressService();
}
@Bean
public ModelMapper modelMapperBean() {
return new ModelMapper();
}
}
|
Note: You may refer to these two articles
Before running the Microservice below is the complete pom.xml file. Please cross-verify if you have missed some dependencies
XML
<? xml version = "1.0" encoding = "UTF-8" ?>
< modelVersion >4.0.0</ modelVersion >
< parent >
< groupId >org.springframework.boot</ groupId >
< artifactId >spring-boot-starter-parent</ artifactId >
< version >3.0.2</ version >
< relativePath />
</ parent >
< groupId >com.gfg.addressapp</ groupId >
< artifactId >address-service</ artifactId >
< version >0.0.1-SNAPSHOT</ version >
< name >address-service</ name >
< description >Address Service</ description >
< properties >
< java.version >17</ 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.springframework.boot</ groupId >
< artifactId >spring-boot-devtools</ artifactId >
< scope >runtime</ scope >
< optional >true</ optional >
</ dependency >
< dependency >
< groupId >com.mysql</ groupId >
< artifactId >mysql-connector-j</ artifactId >
< scope >runtime</ scope >
</ dependency >
< dependency >
< groupId >org.springframework.boot</ groupId >
< artifactId >spring-boot-starter-test</ artifactId >
< scope >test</ scope >
</ dependency >
< dependency >
< groupId >org.modelmapper</ groupId >
< artifactId >modelmapper</ artifactId >
< version >3.1.1</ version >
</ dependency >
</ dependencies >
< build >
< plugins >
< plugin >
< groupId >org.springframework.boot</ groupId >
< artifactId >spring-boot-maven-plugin</ artifactId >
</ plugin >
</ plugins >
</ build >
</ project >
|
Step 10: Run Your Address Microservice
To run your Address Microservice src > main > java > AddressServiceApplication and click on the Run button. If everything goes well then you may see the following screen in your console. Please refer to the below image.
Step 11: Test Your Endpoint in Postman
Now open Postman and hit the following URL
GET: http://localhost:8081/address/2
And you can see the following response
{
"id": 1,
"city": "BLS",
"state": "Odisha"
}
Please refer to the below image.
Share your thoughts in the comments
Please Login to comment...