通过Hibernate调用PostgreSQL函数时的日期类型转换问题

你遇到的核心问题是PostgreSQL对函数参数的类型匹配要求严格，而Hibernate默认的日期类型映射和函数期望的date类型不匹配，导致数据库找不到对应的函数签名。下面是具体的分析和解决方案：

问题根源 #

直接执行SQL时，你传入的'1-nov-2019'这类字符串会被PostgreSQL自动解析为date类型；但通过Hibernate传递java.util.Date时，Hibernate默认会把它映射为PostgreSQL的timestamp类型（因为java.util.Date包含时间信息），而你的函数property.fn_pt_assessment_register_report的第二、第三个参数定义为date类型，数据库因此找不到参数类型完全匹配的函数，抛出ERROR: function ... does not exist的错误。

解决方案 #

方案1：在Hibernate中显式指定参数为SQL DATE类型 #

你可以在设置参数时，明确告诉Hibernate要把java.util.Date转换为PostgreSQL的date类型：

Session session = sessionFactory.getCurrentSession();
StoredProcedureQuery query = session 
    .createStoredProcedureQuery("property.fn_pt_assessment_register_report")
    .registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN)
    .registerStoredProcedureParameter(2, java.util.Date.class, ParameterMode.IN)
    .registerStoredProcedureParameter(3, java.util.Date.class, ParameterMode.IN)
    .setParameter(1, (int)qryprm.get("ULBID"))
    // 显式指定SQL类型为DATE
    .setParameter(2, ((java.util.Date)qryprm.get("FROMDATE")), Types.DATE)
    .setParameter(3, ((java.util.Date)qryprm.get("TODATE")), Types.DATE);
Boolean b = query.execute();
return query.getResultList();

或者在注册参数阶段就指定对应的SQL类型名称：

.registerStoredProcedureParameter(2, java.util.Date.class, ParameterMode.IN, "date")
.registerStoredProcedureParameter(3, java.util.Date.class, ParameterMode.IN, "date")

方案2：改用java.sql.Date代替java.util.Date #

java.sql.Date是专门对应SQL中date类型的类，不包含时间部分，能直接匹配PostgreSQL的date参数。修改你的日期转换代码：

SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
try {
    Date utilFromDate = sdf.parse((String)qryprm.get("FROMDATE"));
    Date utilToDate = sdf.parse((String)qryprm.get("TODATE"));
    // 转换为java.sql.Date，丢弃时间部分
    qryprm.put("FROMDATE", new java.sql.Date(utilFromDate.getTime()));
    qryprm.put("TODATE", new java.sql.Date(utilToDate.getTime()));
} catch (ParseException e1) {
    e1.printStackTrace();
}

然后更新Hibernate参数注册的类型：

StoredProcedureQuery query = session 
    .createStoredProcedureQuery("property.fn_pt_assessment_register_report")
    .registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN)
    .registerStoredProcedureParameter(2, java.sql.Date.class, ParameterMode.IN)
    .registerStoredProcedureParameter(3, java.sql.Date.class, ParameterMode.IN)
    .setParameter(1, (int)qryprm.get("ULBID"))
    .setParameter(2, ((java.sql.Date)qryprm.get("FROMDATE")))
    .setParameter(3, ((java.sql.Date)qryprm.get("TODATE")));

方案3：使用原生SQL并显式转换日期类型 #

如果上面的方法都不生效，你可以改用原生SQL查询，在SQL中显式把参数转换为date类型：

Session session = sessionFactory.getCurrentSession();
String sql = "select * from property.fn_pt_assessment_register_report(?, cast(? as date), cast(? as date))";
Query query = session.createNativeQuery(sql)
    .setParameter(1, (int)qryprm.get("ULBID"))
    .setParameter(2, ((java.util.Date)qryprm.get("FROMDATE")))
    .setParameter(3, ((java.util.Date)qryprm.get("TODATE")));
return query.getResultList();

前置验证步骤 #

在尝试解决方案前，建议先确认你的PostgreSQL函数参数类型是否确实为date：
在psql中执行命令：

\df property.fn_pt_assessment_register_report

查看函数的参数列表，确保第二、第三个参数类型是date而非timestamp或其他类型。

内容的提问来源于stack exchange，提问作者Chiranjib pattanayak