如何通过Spring Criteria API调用PostgreSQL表返回函数

问题根源 #

1. 标识符大小写不匹配：PostgreSQL会自动将未加双引号的函数名转换为小写，你定义的statVRP实际存储为statvrp，但Java调用时使用大写名称，导致函数查找失败。
2. 调用方式适配问题：StoredProcedureQuery默认适配存储过程调用逻辑，而PostgreSQL返回表的函数更适合通过SELECT语句调用，原生SQL方式能更好处理参数类型映射。
3. 参数隐式转换失效：即使注册了Timestamp类型，驱动仍可能将参数以字符串形式传递，PostgreSQL无法自动转换为timestamp类型，触发类型不匹配提示。

修复方案 #

方案1：原生SQL查询（推荐） #

直接通过原生SQL调用函数，显式指定参数类型，并手动映射结果到DTO：

String sql = "SELECT idfamille, libelle, idcode_vendeur, nom_vendeur, total_ht FROM statvrp(CAST(? AS timestamp), CAST(? AS timestamp))";
Query query = entityManager.createNativeQuery(sql)
        .setParameter(1, criteria.dateDebut(), Timestamp.class)
        .setParameter(2, criteria.dateFin(), Timestamp.class);

List<Object[]> resultRows = query.getResultList();
List<VenteVRPDTO> dtoList = new ArrayList<>();
for (Object[] row : resultRows) {
    VenteVRPDTO dto = new VenteVRPDTO();
    dto.setIdfamille((Integer) row[0]);
    dto.setLibelle((String) row[1]);
    dto.setIdcode_vendeur((Integer) row[2]);
    dto.setNom_vendeur((String) row[3]);
    dto.setTotal_ht((Float) row[4]);
    dtoList.add(dto);
}
return dtoList;

方案2：修正StoredProcedureQuery调用 #

若需保留存储过程调用方式，调整以下内容：

StoredProcedureQuery query = entityManager.createStoredProcedureQuery("statvrp")
        .registerStoredProcedureParameter("dateDebut", Timestamp.class, ParameterMode.IN)
        .registerStoredProcedureParameter("dateFin", Timestamp.class, ParameterMode.IN)
        // 注册返回表的列映射
        .registerStoredProcedureParameter("idfamille", Integer.class, ParameterMode.OUT)
        .registerStoredProcedureParameter("libelle", String.class, ParameterMode.OUT)
        .registerStoredProcedureParameter("idcode_vendeur", Integer.class, ParameterMode.OUT)
        .registerStoredProcedureParameter("nom_vendeur", String.class, ParameterMode.OUT)
        .registerStoredProcedureParameter("total_ht", Float.class, ParameterMode.OUT)
        .setParameter("dateDebut", criteria.dateDebut())
        .setParameter("dateFin", criteria.dateFin());

query.execute();

List<VenteVRPDTO> dtoList = new ArrayList<>();
do {
    VenteVRPDTO dto = new VenteVRPDTO();
    dto.setIdfamille((Integer) query.getOutputParameterValue("idfamille"));
    dto.setLibelle((String) query.getOutputParameterValue("libelle"));
    dto.setIdcode_vendeur((Integer) query.getOutputParameterValue("idcode_vendeur"));
    dto.setNom_vendeur((String) query.getOutputParameterValue("nom_vendeur"));
    dto.setTotal_ht((Float) query.getOutputParameterValue("total_ht"));
    dtoList.add(dto);
} while (query.getMoreResults());

return dtoList;

方案3：保留驼峰函数名（可选） #

若要保留statVRP的驼峰命名，创建函数时需用双引号包裹：

create or replace function "statVRP"(dateDebut timestamp, dateFin timestamp)
    returns table
        (
            idfamille int,
            libelle varchar(50),
            idcode_vendeur int,
            nom_vendeur varchar(50),
            total_ht real
        )
    language plpgsql
as
$$ begin
    -- 原函数逻辑不变
end; $$;

Java调用时需同步用双引号包裹函数名：

StoredProcedureQuery query = entityManager.createStoredProcedureQuery("\"statVRP\"")
// 后续参数注册逻辑不变

关键注意事项 #

• PostgreSQL对未加双引号的标识符自动转小写，Java调用时必须保持名称一致。
• 返回表结构的函数优先使用原生SQL调用，兼容性更强。
• 显式通过CAST转换参数类型，避免驱动隐式转换失败。

内容的提问来源于stack exchange，提问作者Neko-Tenshi