How to Insert Data from One Table to Another Using Native Query in Spring Boot

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

1. Open your IDE and create a new Spring Boot project.

2. Configure the pom.xml file 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.