使用createNativeQuery调用generate_series时出现语法错误问题

这个问题我碰到过好几次了——Hibernate的原生查询解析器会把PostgreSQL专属的类型转换操作符::误当成参数占位符的开头（Hibernate用:标记参数），所以它自动把你的d.dt::date改成了d.dt:date，少了一个冒号，PostgreSQL自然认不出这个语法，直接抛出了语法错误。

看你的控制台日志就能明显发现这个问题：原SQL里的d.dt::date被转换成了d.dt:date，这就是报错的根源。

我推荐两种解决方式，优先选第一种，通用性更强：

方法1：用标准SQL的CAST()替代PostgreSQL的::语法 #

把所有::date的写法替换成CAST(字段名 AS DATE)，这是标准SQL的类型转换语法，Hibernate不会误解，同时跨数据库兼容性更好。

修改后的DAO层查询字符串代码如下：

String queryString = " select CAST(d.dt AS DATE) as date_current, coalesce(underwriter_daily_cap, daily_file_cap) as file_cap, user_id " +
" from generate_series ( date '"+DateUtil.getDateFormat(currentDateMinusFive)+"', date '"+DateUtil.getDateFormat(currentDate)+"', INTERVAL '1' DAY ) as d(dt) " +
" left join ( select upd.*, ud.daily_file_cap from mod_underwriter_daily_file_count upd " +
" join lsa_assignment_details ud on ud.user_id = upd.user_id where ud.user_id = " + dynamicUserId +") upd on upd.date_current = CAST(d.dt AS DATE) ORDER BY d.dt DESC ";

方法2：转义冒号（不推荐，可读性差） #

如果你非要坚持用PostgreSQL的::语法，可以把每个冒号转义成\\:，让Hibernate不要把它识别为参数占位符：

String queryString = " select d.dt\\:\\:date as date_current, coalesce(underwriter_daily_cap, daily_file_cap) as file_cap, user_id " +
" from generate_series ( date '"+DateUtil.getDateFormat(currentDateMinusFive)+"', date '"+DateUtil.getDateFormat(currentDate)+"', INTERVAL '1' DAY ) as d(dt) " +
" left join ( select upd.*, ud.daily_file_cap from mod_underwriter_daily_file_count upd " +
" join lsa_assignment_details ud on ud.user_id = upd.user_id where ud.user_id = " + dynamicUserId +") upd on upd.date_current = d.dt\\:\\:date ORDER BY d.dt DESC ";

顺便提一句，你现在直接把dynamicUserId拼接进SQL字符串里，存在严重的SQL注入风险！建议改用Hibernate的参数绑定功能，既安全又规范：

@SuppressWarnings("unchecked") 
public List<Object[]> getUnderWriterCap(String dynamicUserId) { 
    log.info("Entered UserSkillMappingHibDAO : getUnderWriterCap"); 
    Session session = sessionFactory.getCurrentSession(); 
    Date currentDateMinusFive = DateUtil.getLastFiveDayOldPSTDate(); 
    Date currentDate = DateUtil.getCurrentPSTDate(); 
    String queryString = " select CAST(d.dt AS DATE) as date_current, coalesce(underwriter_daily_cap, daily_file_cap) as file_cap, user_id " +
    " from generate_series ( date :startDate, date :endDate, INTERVAL '1' DAY ) as d(dt) " +
    " left join ( select upd.*, ud.daily_file_cap from mod_underwriter_daily_file_count upd " +
    " join lsa_assignment_details ud on ud.user_id = upd.user_id where ud.user_id = :userId ) upd on upd.date_current = CAST(d.dt AS DATE) ORDER BY d.dt DESC "; 
    List<Object[]> records = session.createNativeQuery(queryString) 
            .setParameter("startDate", DateUtil.getDateFormat(currentDateMinusFive))
            .setParameter("endDate", DateUtil.getDateFormat(currentDate))
            .setParameter("userId", dynamicUserId)
            .getResultList(); 
    return records; 
}

内容的提问来源于stack exchange，提问作者Karthik sa