调用含UUID[]参数的PostgreSQL存储过程报错，求解决方案

我定义了如下PostgreSQL存储过程：

CREATE OR REPLACE PROCEDURE rvprficreationadm.copy_questions(IN target_componentid uuid, INOUT source_questionids uuid[], IN createdby uuid, IN is_copied_from_qb boolean, IN createdThrCd varchar)

该存储过程在数据库中直接调用正常，但通过Java结合Hibernate调用时，出现错误：

ERROR: function rvprficreationadm.copy_questions(uuid, bytea, uuid, boolean, character varying) does not exist

我的Java调用代码如下：

package com.gartner.rvp.rfi.creation.repository.impl;

import com.gartner.rvp.rfi.creation.repository.CopyQuestionProcedure;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.ParameterMode;
import javax.persistence.StoredProcedureQuery;
import javax.transaction.Transactional;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

@Repository
public class CopyQuestionProcedureImpl implements CopyQuestionProcedure {

    @Autowired
    private EntityManager entityManager;

    @Override
    @Transactional
    public List<UUID> copyQuestions(UUID componentId, List<UUID> targetQuestionIds, UUID userId) {
        StoredProcedureQuery query = entityManager.createStoredProcedureQuery("rvprficreationadm.copy_questions");
        
        // Register the parameters
        query.registerStoredProcedureParameter(1, UUID.class, ParameterMode.IN);
        query.registerStoredProcedureParameter(2, UUID[].class, ParameterMode.INOUT);
        query.registerStoredProcedureParameter(3, UUID.class, ParameterMode.IN);
        query.registerStoredProcedureParameter(4, Boolean.class, ParameterMode.IN);
        query.registerStoredProcedureParameter(5, String.class, ParameterMode.IN);

        // Set the parameter values
        query.setParameter(1, componentId);
        query.setParameter(2, targetQuestionIds.toArray(UUID[]::new));
        query.setParameter(3, userId);
        query.setParameter(4, false);
        query.setParameter(5, "COPY_QUESTION");

        // Execute the query
        query.execute();

        // Get the result (updated INOUT parameter)
        UUID[] resultArray = (UUID[]) query.getOutputParameterValue(2);
        return List.of(resultArray);
    }
}

问题出在第二个参数，Hibernate将其识别为bytea类型。我尝试过使用List和UUID[]两种方式传递参数，但均失败，请问有什么解决方案？

针对Hibernate把UUID数组识别为bytea的问题，可以尝试以下几种方案：

方案1：使用原生JDBC CallableStatement调用 #

绕过Hibernate的存储过程API，直接用JDBC的CallableStatement处理，明确指定数组类型：

@Override
@Transactional
public List<UUID> copyQuestions(UUID componentId, List<UUID> targetQuestionIds, UUID userId) {
    Session session = entityManager.unwrap(Session.class);
    final List<UUID> resultList = new ArrayList<>();
    
    session.doWork(connection -> {
        try (CallableStatement cs = connection.prepareCall("{call rvprficreationadm.copy_questions(?, ?, ?, ?, ?)}")) {
            // 设置输入参数
            cs.setObject(1, componentId);
            // 明确指定PostgreSQL的uuid数组类型
            cs.setArray(2, connection.createArrayOf("uuid", targetQuestionIds.toArray()));
            cs.setObject(3, userId);
            cs.setBoolean(4, false);
            cs.setString(5, "COPY_QUESTION");
            
            // 注册INOUT参数的输出类型
            cs.registerOutParameter(2, java.sql.Types.ARRAY, "uuid");
            
            // 执行存储过程
            cs.execute();
            
            // 获取返回的UUID数组
            UUID[] resultArray = (UUID[]) cs.getArray(2).getArray();
            if (resultArray != null) {
                resultList.addAll(List.of(resultArray));
            }
        } catch (SQLException e) {
            throw new RuntimeException("调用存储过程失败", e);
        }
    });
    
    return resultList;
}

方案2：指定Hibernate原生UUID数组类型 #

在注册参数时，显式指定Hibernate提供的PostgresUUIDArrayType，让框架正确识别参数类型：

@Override
@Transactional
public List<UUID> copyQuestions(UUID componentId, List<UUID> targetQuestionIds, UUID userId) {
    StoredProcedureQuery query = entityManager.createStoredProcedureQuery("rvprficreationadm.copy_questions");
    
    // 注册参数并指定类型
    query.registerStoredProcedureParameter(1, UUID.class, ParameterMode.IN);
    query.registerStoredProcedureParameter(2, UUID[].class, ParameterMode.INOUT);
    query.registerStoredProcedureParameter(3, UUID.class, ParameterMode.IN);
    query.registerStoredProcedureParameter(4, Boolean.class, ParameterMode.IN);
    query.registerStoredProcedureParameter(5, String.class, ParameterMode.IN);
    
    // 设置参数时指定Hibernate的UUID数组类型
    query.setParameter(1, componentId);
    query.setParameter(2, targetQuestionIds.toArray(UUID[]::new), org.hibernate.type.PostgresUUIDArrayType.INSTANCE);
    query.setParameter(3, userId);
    query.setParameter(4, false);
    query.setParameter(5, "COPY_QUESTION");
    
    query.execute();
    
    UUID[] resultArray = (UUID[]) query.getOutputParameterValue(2);
    return resultArray != null ? List.of(resultArray) : List.of();
}

方案3：临时调整存储过程参数类型 #

如果上述方案无法快速落地，可以修改存储过程，将uuid[]参数改为text[]，在存储过程内部完成类型转换：

CREATE OR REPLACE PROCEDURE rvprficreationadm.copy_questions(IN target_componentid uuid, INOUT source_questionids text[], IN createdby uuid, IN is_copied_from_qb boolean, IN createdThrCd varchar)
LANGUAGE plpgsql
AS $$
DECLARE
    uuid_array uuid[];
BEGIN
    -- 将text数组转换为uuid数组
    uuid_array := ARRAY(SELECT unnest(source_questionids)::uuid);
    
    -- 原存储过程的业务逻辑，使用uuid_array替代原source_questionids参数
    
    -- 处理完成后，将结果转换回text数组输出
    source_questionids := ARRAY(SELECT unnest(uuid_array)::text);
END;
$$;

然后修改Java代码，传递String数组并转换结果：

@Override
@Transactional
public List<UUID> copyQuestions(UUID componentId, List<UUID> targetQuestionIds, UUID userId) {
    StoredProcedureQuery query = entityManager.createStoredProcedureQuery("rvprficreationadm.copy_questions");
    
    query.registerStoredProcedureParameter(1, UUID.class, ParameterMode.IN);
    query.registerStoredProcedureParameter(2, String[].class, ParameterMode.INOUT);
    query.registerStoredProcedureParameter(3, UUID.class, ParameterMode.IN);
    query.registerStoredProcedureParameter(4, Boolean.class, ParameterMode.IN);
    query.registerStoredProcedureParameter(5, String.class, ParameterMode.IN);
    
    // 转换UUID列表为String数组
    String[] questionIdStrs = targetQuestionIds.stream()
                                               .map(UUID::toString)
                                               .toArray(String[]::new);
    
    query.setParameter(1, componentId);
    query.setParameter(2, questionIdStrs);
    query.setParameter(3, userId);
    query.setParameter(4, false);
    query.setParameter(5, "COPY_QUESTION");
    
    query.execute();
    
    // 将返回的String数组转换回UUID列表
    String[] resultStrs = (String[]) query.getOutputParameterValue(2);
    return resultStrs != null ? 
           Arrays.stream(resultStrs).map(UUID::fromString).toList() : 
           List.of();
}

内容的提问来源于stack exchange，提问作者Vikram Singh