如何通过Hibernate及Spring Data向存储过程传递实体数组？

你的推测路径是完全可行的，基于Hibernate/JPA + Oracle（或其他支持自定义复合数组类型的数据库）可以实现向存储过程传递实体数组。以下是完整的实现步骤与示例：

一、Oracle数据库端配置 #

1. 创建实体对应的自定义对象类型 #

假设Java实体为Employee，包含id、name、salary字段，先在Oracle中创建对应对象类型：

CREATE OR REPLACE TYPE EMPLOYEE_OBJ AS OBJECT (
    ID NUMBER,
    NAME VARCHAR2(100),
    SALARY NUMBER
);
/

2. 创建该对象类型的数组类型 #

CREATE OR REPLACE TYPE EMPLOYEE_ARRAY AS TABLE OF EMPLOYEE_OBJ;
/

3. 创建接收数组的存储过程 #

示例存储过程遍历传入的员工数组，将数据插入EMPLOYEES表：

CREATE OR REPLACE PROCEDURE INSERT_EMPLOYEES(p_employees EMPLOYEE_ARRAY)
IS
BEGIN
    FOR i IN p_employees.FIRST..p_employees.LAST LOOP
        INSERT INTO EMPLOYEES (ID, NAME, SALARY)
        VALUES (p_employees(i).ID, p_employees(i).NAME, p_employees(i).SALARY);
    END LOOP;
    COMMIT;
END;
/

二、Java端实现 #

1. 定义实体类与自定义类型映射 #

首先定义Employee实体：

import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import org.hibernate.annotations.Type;
import org.hibernate.annotations.TypeDef;

@Entity
@TypeDef(
    name = "employeeObj",
    typeClass = EmployeeObjectType.class
)
public class Employee {
    @Id
    private Long id;
    private String name;
    private Double salary;

    // 构造器、getter、setter省略
}

实现Hibernate的UserType映射Oracle的EMPLOYEE_OBJ类型：

import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.usertype.UserType;
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

public class EmployeeObjectType implements UserType {

    @Override
    public int[] sqlTypes() {
        return new int[]{Types.STRUCT};
    }

    @Override
    public Class returnedClass() {
        return Employee.class;
    }

    @Override
    public boolean equals(Object x, Object y) {
        if (x == y) return true;
        if (x == null || y == null) return false;
        return x.equals(y);
    }

    @Override
    public int hashCode(Object x) {
        return x.hashCode();
    }

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner) throws SQLException {
        STRUCT struct = (STRUCT) rs.getObject(names[0]);
        if (struct == null) return null;
        Object[] attributes = struct.getAttributes();
        Employee employee = new Employee();
        employee.setId((Long) attributes[0]);
        employee.setName((String) attributes[1]);
        employee.setSalary((Double) attributes[2]);
        return employee;
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session) throws SQLException {
        if (value == null) {
            st.setNull(index, Types.STRUCT, "EMPLOYEE_OBJ");
            return;
        }
        Employee employee = (Employee) value;
        Object[] attributes = new Object[]{
            employee.getId(),
            employee.getName(),
            employee.getSalary()
        };
        StructDescriptor descriptor = StructDescriptor.createDescriptor("EMPLOYEE_OBJ", st.getConnection());
        STRUCT struct = new STRUCT(descriptor, st.getConnection(), attributes);
        st.setObject(index, struct);
    }

    // deepCopy、disassemble、assemble、replace、isMutable方法默认实现省略
}

实现数组类型的映射UserType：

import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.usertype.UserType;
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;

public class EmployeeArrayType implements UserType {

    @Override
    public int[] sqlTypes() {
        return new int[]{Types.ARRAY};
    }

    @Override
    public Class returnedClass() {
        return Employee[].class;
    }

    @Override
    public boolean equals(Object x, Object y) {
        if (x == y) return true;
        if (x == null || y == null) return false;
        return ((Employee[]) x).length == ((Employee[]) y).length;
    }

    @Override
    public int hashCode(Object x) {
        return ((Employee[]) x).length;
    }

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner) throws SQLException {
        ARRAY array = (ARRAY) rs.getObject(names[0]);
        if (array == null) return null;
        Object[] structs = (Object[]) array.getArray();
        Employee[] employees = new Employee[structs.length];
        for (int i = 0; i < structs.length; i++) {
            STRUCT struct = (STRUCT) structs[i];
            Object[] attributes = struct.getAttributes();
            Employee employee = new Employee();
            employee.setId((Long) attributes[0]);
            employee.setName((String) attributes[1]);
            employee.setSalary((Double) attributes[2]);
            employees[i] = employee;
        }
        return employees;
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session) throws SQLException {
        if (value == null) {
            st.setNull(index, Types.ARRAY, "EMPLOYEE_ARRAY");
            return;
        }
        Employee[] employees = (Employee[]) value;
        Object[] structs = new Object[employees.length];
        StructDescriptor structDescriptor = StructDescriptor.createDescriptor("EMPLOYEE_OBJ", st.getConnection());
        for (int i = 0; i < employees.length; i++) {
            Employee emp = employees[i];
            Object[] attributes = new Object[]{emp.getId(), emp.getName(), emp.getSalary()};
            STRUCT struct = new STRUCT(structDescriptor, st.getConnection(), attributes);
            structs[i] = struct;
        }
        ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("EMPLOYEE_ARRAY", st.getConnection());
        ARRAY array = new ARRAY(arrayDescriptor, st.getConnection(), structs);
        st.setObject(index, array);
    }

    // deepCopy、disassemble、assemble、replace、isMutable方法默认实现省略
}

在实体类或包级别添加数组类型定义：

@TypeDef(
    name = "employeeArray",
    typeClass = EmployeeArrayType.class
)

2. Spring Data调用存储过程 #

定义Repository接口，用@Procedure注解调用：

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Procedure;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.hibernate.annotations.Type;

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {

    @Procedure(name = "INSERT_EMPLOYEES")
    void insertEmployees(@Param("p_employees") @Type(type = "employeeArray") Employee[] employees);
}

或使用EntityManager手动调用：

import jakarta.persistence.EntityManager;
import jakarta.persistence.ParameterMode;
import jakarta.persistence.StoredProcedureQuery;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class EmployeeService {

    private final EntityManager entityManager;

    public EmployeeService(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Transactional
    public void batchInsertEmployees(Employee[] employees) {
        StoredProcedureQuery query = entityManager.createStoredProcedureQuery("INSERT_EMPLOYEES");
        query.registerStoredProcedureParameter("p_employees", Employee[].class, ParameterMode.IN);
        query.setParameter("p_employees", employees);
        query.execute();
    }
}

注意事项 #

• 不同数据库的自定义类型语法有差异，需对应调整数据库端代码。
• Hibernate版本不同（如5 vs 6），UserType API有变化，上述示例基于Hibernate 6+与Jakarta Persistence。
• 需确保数据库驱动支持自定义类型（如Oracle的ojdbc驱动需对应版本）。

内容的提问来源于stack exchange，提问作者Martin Mucha