Introduction
In Spring Boot with Spring Data JPA, you can use native queries to perform raw SQL operations directly on the database. This can be particularly useful when you need to insert data from one table to another. This tutorial will guide you through the process of setting up a Spring Boot project, creating the necessary entities, and using native queries to transfer data between tables.
Prerequisites
Before we start, ensure you have the following:
- Java Development Kit (JDK) installed
- Apache Maven installed
- MySQL database installed and running (or any other relational database)
- An IDE (such as IntelliJ IDEA, Eclipse, or VS Code) installed
Step 1: Setting Up the Project
1.1 Create a Spring Boot Project
-
Open your IDE and create a new Spring Boot project.
-
Configure the
pom.xmlfile with the following content:
<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.example</groupId>
<artifactId>spring-boot-native-query-example</artifactId>
<version>1.0-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.0.0</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<dependencies>
<!-- Spring Boot and JPA -->
<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>
<!-- MySQL Connector -->
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.0.26</version>
</dependency>
<!-- Spring Boot Test -->
<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>
1.2 Configure Application Properties
Create an application.properties file in the src/main/resources directory with the following content:
spring.datasource.url=jdbc:mysql://localhost:3306/your_database_name
spring.datasource.username=your_username
spring.datasource.password=your_password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect
Replace your_database_name, your_username, and your_password with your MySQL database credentials.
Step 2: Creating the Entities
2.1 Source Entity
Create a SourceEntity class in the com.example.springboothibernateexample.model package:
package com.example.springboothibernateexample.model;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
@Entity
public class SourceEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String data;
// Getters and Setters
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getData() {
return data;
}
public void setData(String data) {
this.data = data;
}
}
2.2 Target Entity
Create a TargetEntity class in the same package:
package com.example.springboothibernateexample.model;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
@Entity
public class TargetEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String data;
// Getters and Setters
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getData() {
return data;
}
public void setData(String data) {
this.data = data;
}
}
Step 3: Creating the Repository Interfaces
3.1 Source Repository
Create a repository interface for the SourceEntity in the com.example.springboothibernateexample.repository package:
package com.example.springboothibernateexample.repository;
import com.example.springboothibernateexample.model.SourceEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
@Repository
public interface SourceRepository extends JpaRepository<SourceEntity, Long> {
}
3.2 Target Repository
Create a repository interface for the TargetEntity in the same package:
package com.example.springboothibernateexample.repository;
import com.example.springboothibernateexample.model.TargetEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
@Repository
public interface TargetRepository extends JpaRepository<TargetEntity, Long> {
}
Step 4: Creating the Service Class
Create a service class to handle the data transfer logic in the com.example.springboothibernateexample.service package:
package com.example.springboothibernateexample.service;
import com.example.springboothibernateexample.repository.SourceRepository;
import com.example.springboothibernateexample.repository.TargetRepository;
import org.springframework.stereotype.Service;
@Service
public class DataTransferService {
private SourceRepository sourceRepository;
private TargetRepository targetRepository;
public DataTransferService(SourceRepository sourceRepository,
TargetRepository targetRepository){
this.sourceRepository = sourceRepository;
this.targetRepository = targetRepository;
}
public void transferData() {
// Fetch all data from source table
List<SourceEntity> sourceEntities = sourceRepository.findAll();
// Map source data to target entity and save to target table
List<TargetEntity> targetEntities = sourceEntities.stream().map(sourceEntity -> {
TargetEntity targetEntity = new TargetEntity();
targetEntity.setData(sourceEntity.getData());
return targetEntity;
}).collect(Collectors.toList());
targetRepository.saveAll(targetEntities);
}
}
Step 5: Creating the Controller Class
Create a controller class to trigger the data transfer in the com.example.springboothibernateexample.controller package:
package com.example.springboothibernateexample.controller;
import com.example.springboothibernateexample.service.DataTransferService;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping("/api/data-transfer")
public class DataTransferController {
private DataTransferService dataTransferService;
public DataTransferController(DataTransferService dataTransferService){
this.dataTransferService = dataTransferService;
}
@PostMapping
public String transferData() {
dataTransferService.transferData();
return "Data transferred successfully";
}
}
Step 6: Running the Application
Run the Spring Boot application from your IDE or from the command line using the following command:
mvn spring-boot:run
You can now test the data transfer operation by sending a POST request to the /api/data-transfer endpoint using a REST client (like Postman) or by visiting the endpoint in your web browser.
Conclusion
In this tutorial, we demonstrated how to use Spring Data JPA in Spring Boot to insert data from one table to another. We set up a Spring Boot project, created the necessary entities and repositories, implemented the data transfer logic in a service class, and created a controller to trigger the data transfer. By following these steps, you can effectively use Spring Data JPA in your Spring Boot applications to perform complex database operations.
Comments
Post a Comment
Leave Comment