Spring Boot使用@Procedure调用含多字段自定义类型参数的存储过程报错

我在PostgreSQL中创建了接收自定义复合类型参数的存储过程，代码如下：

数据库端代码 #

DROP TYPE IF EXISTS custom_type CASCADE;
CREATE TYPE custom_type AS (
    field1 integer,
    field2 integer
);

CREATE OR REPLACE PROCEDURE my_proc(param1 custom_type) AS $$
BEGIN
    -- DO NOTHING FOR NOW
END;
$$ LANGUAGE 'plpgsql';

Java端实现 #

我创建了对应的Java类，并通过Spring Data Repository配置存储过程调用：

@Data
public class CustomType {
    int field1;
    int field2;
    public CustomType(Integer field1, Integer field2) {
        this.field1 = field1;
        this.field2 = field2;
    }
}

public interface MyRepo extends RdbFilterQueryRepository<MyEntity> {
   @Procedure(value = "my_proc")
   void myProc(@Param("param1") CustomType myParam1);
}

报错信息 #

调用时触发如下错误：

Caused by: java.lang.IllegalArgumentException: Cannot determine the bindable type for procedure parameter: null

已尝试的方案 #

• 单字段自定义类型可通过实现UserType<T>并结合TypeContributor注册成功调用
• 多字段复合类型尝试实现CompositeUserType<T>，但TypeContributor仅支持注册UserType<T>，无法完成绑定
• 尝试官方建议的@CompositeTypeRegistration作为临时方案，同样无效

方案1：使用Hibernate 6+的@Struct注解 #

Hibernate 6开始原生支持PostgreSQL的复合结构体类型，只需给Java类添加@Struct注解指定数据库类型名：

@Data
@Struct(name = "custom_type")
public class CustomType {
    int field1;
    int field2;
    public CustomType(Integer field1, Integer field2) {
        this.field1 = field1;
        this.field2 = field2;
    }
}

确保项目使用Hibernate 6及以上版本，PostgreSQL方言会自动完成Java类与数据库复合类型的绑定，Spring Data的@Procedure调用即可正常工作。

方案2：手动实现UserType兼容旧版本 #

如果无法升级Hibernate，可手动实现UserType<CustomType>处理复合类型的序列化与反序列化：

1. 实现自定义UserType：

import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.usertype.UserType;
import java.sql.*;
import java.util.Objects;
import org.postgresql.PGConnection;
import org.postgresql.util.PGStruct;

public class CustomTypeUserType implements UserType<CustomType> {

    @Override
    public int getSqlType() {
        return Types.OTHER;
    }

    @Override
    public Class<CustomType> returnedClass() {
        return CustomType.class;
    }

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

    @Override
    public int hashCode(CustomType x) {
        return Objects.hash(x.getField1(), x.getField2());
    }

    @Override
    public CustomType nullSafeGet(ResultSet rs, int position, SharedSessionContractImplementor session, Object owner) throws SQLException {
        Object struct = rs.getObject(position);
        if (struct == null) return null;
        if (struct instanceof PGStruct pgStruct) {
            Object[] values = pgStruct.getValues();
            return new CustomType((Integer) values[0], (Integer) values[1]);
        }
        return null;
    }

    @Override
    public void nullSafeSet(PreparedStatement st, CustomType value, int index, SharedSessionContractImplementor session) throws SQLException {
        if (value == null) {
            st.setNull(index, Types.OTHER);
            return;
        }
        PGConnection pgConn = st.getConnection().unwrap(PGConnection.class);
        Struct struct = pgConn.createStruct("custom_type", new Object[]{value.getField1(), value.getField2()});
        st.setObject(index, struct);
    }

    @Override
    public CustomType deepCopy(CustomType value) {
        return value == null ? null : new CustomType(value.getField1(), value.getField2());
    }

    @Override
    public boolean isMutable() {
        return false;
    }

    @Override
    public Serializable disassemble(CustomType value) {
        return value;
    }

    @Override
    public CustomType assemble(Serializable cached, Object owner) {
        return (CustomType) cached;
    }
}

2. 注册自定义类型：
   创建TypeContributor实现类：

import org.hibernate.boot.model.TypeContributions;
import org.hibernate.boot.model.TypeContributor;
import org.hibernate.service.ServiceRegistry;

public class CustomTypeContributor implements TypeContributor {
    @Override
    public void contribute(TypeContributions typeContributions, ServiceRegistry serviceRegistry) {
        typeContributions.contributeType(new CustomTypeUserType());
    }
}

在Spring Boot配置文件中指定类型贡献者：

spring.jpa.properties.hibernate.types.contributors=com.yourpackage.CustomTypeContributor

方案3：直接使用JdbcTemplate调用 #

如果上述Hibernate方案均无法生效，可绕过Spring Data的自动绑定，直接用JdbcTemplate手动处理参数：

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import java.sql.Connection;
import java.sql.CallableStatement;
import org.postgresql.PGConnection;

@Component
public class MyProcCaller {

    private final JdbcTemplate jdbcTemplate;

    public MyProcCaller(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public void callMyProc(CustomType param) {
        jdbcTemplate.execute((Connection connection) -> {
            try (CallableStatement cs = connection.prepareCall("{call my_proc(?)}")) {
                PGConnection pgConn = connection.unwrap(PGConnection.class);
                Struct struct = pgConn.createStruct("custom_type", new Object[]{param.getField1(), param.getField2()});
                cs.setObject(1, struct);
                cs.execute();
            }
            return null;
        });
    }
}

内容的提问来源于stack exchange，提问作者Raziza O