Using Stored Procedures for Bulk Operations in Spring Data JPA

1. Introduction

Spring Data JPA provides a way to call stored procedures using the @Procedure annotation or native queries. Stored procedures are useful for bulk operations, as they can significantly improve performance by reducing round trips to the database.

In this guide, we will:

• Create a stored procedure for bulk operations (insert/update/delete).
• Call the stored procedure using Spring Data JPA in a Spring Boot application.
• Test the stored procedure execution.

---

2. Setting Up the Project

2.1 Dependencies

Add the following dependencies to your pom.xml:

<dependencies>
    <!-- Spring Boot Starter Data JPA -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

    <!-- Spring Boot Starter Web -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <!-- H2 Database (For Testing, use MySQL/PostgreSQL in Production) -->
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <scope>runtime</scope>
    </dependency>

    <!-- Lombok (Optional, for cleaner code) -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <scope>provided</scope>
    </dependency>
</dependencies>

---

3. Database Setup and Stored Procedure

3.1 Create Table

Let's assume we have a products table.

CREATE TABLE products (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

3.2 Create a Stored Procedure for Bulk Insert

This stored procedure will insert multiple products at once.

For MySQL

DELIMITER $$

CREATE PROCEDURE bulk_insert_products(IN product_data JSON)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE total INT DEFAULT JSON_LENGTH(product_data);
    
    WHILE i < total DO
        INSERT INTO products (name, price)
        VALUES (
            JSON_UNQUOTE(JSON_EXTRACT(product_data, CONCAT('$[', i, '].name'))),
            JSON_UNQUOTE(JSON_EXTRACT(product_data, CONCAT('$[', i, '].price')))
        );
        SET i = i + 1;
    END WHILE;
END $$

DELIMITER ;

For PostgreSQL

CREATE OR REPLACE FUNCTION bulk_insert_products(product_data JSONB)
RETURNS VOID AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT * FROM jsonb_array_elements(product_data) LOOP
        INSERT INTO products (name, price) VALUES
        (rec->>'name', (rec->>'price')::DECIMAL);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

---

4. Spring Data JPA Configuration

4.1 Entity Class

package com.example.bulkoperations.model;

import jakarta.persistence.*;
import lombok.*;

@Entity
@Table(name = "products")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Product {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private String name;
    private Double price;
}

---

4.2 Repository Interface

package com.example.bulkoperations.repository;

import com.example.bulkoperations.model.Product;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    // Calling a stored procedure for bulk insert
    @Procedure(name = "bulk_insert_products")
    void bulkInsert(@Param("product_data") String productData);
}

• @Procedure(name = "bulk_insert_products") is used to call the stored procedure.
• We pass a JSON string (product_data) containing multiple product details.

---

5. Service Layer

package com.example.bulkoperations.service;

import com.example.bulkoperations.model.Product;
import com.example.bulkoperations.repository.ProductRepository;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
@RequiredArgsConstructor
public class ProductService {

    private final ProductRepository productRepository;
    private final ObjectMapper objectMapper;

    @Transactional
    public void bulkInsertProducts(List<Product> products) throws JsonProcessingException {
        String jsonProducts = objectMapper.writeValueAsString(products);
        productRepository.bulkInsert(jsonProducts);
    }
}

---

6. REST Controller

package com.example.bulkoperations.controller;

import com.example.bulkoperations.model.Product;
import com.example.bulkoperations.service.ProductService;
import lombok.RequiredArgsConstructor;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/products")
@RequiredArgsConstructor
public class ProductController {

    private final ProductService productService;

    @PostMapping("/bulk-insert")
    public ResponseEntity<String> bulkInsert(@RequestBody List<Product> products) {
        try {
            productService.bulkInsertProducts(products);
            return ResponseEntity.ok("Bulk insert successful!");
        } catch (Exception e) {
            return ResponseEntity.status(500).body("Error: " + e.getMessage());
        }
    }
}

---

7. Testing the API

7.1 Start the Spring Boot Application

Run the application:

mvn spring-boot:run

7.2 Test the API using Postman or Curl

Request

POST http://localhost:8080/products/bulk-insert
Content-Type: application/json

[
    {"name": "Laptop", "price": 1200.50},
    {"name": "Smartphone", "price": 799.99},
    {"name": "Tablet", "price": 499.00}
]

Response

"Bulk insert successful!"

---

8. Verifying in Database

Run:

SELECT * FROM products;

You should see the inserted records.

---

9. Conclusion

• Stored procedures are useful for bulk operations in databases.
• Spring Data JPA provides @Procedure to call stored procedures.
• Using JSON as an input parameter allows handling dynamic lists.
• This approach reduces database round trips, improving performance.