java - 在 createNativeQuery 中使用generate_series时出错

Question

在 PostgreSQL 数据库中执行的查询正在正确获取记录。但是，当使用 createnativeQuery 实现时，我收到错误

错误 SqlExceptionHelper:131 - 错误:“:”处或附近存在语法错误

下面提到了在 Postgres 数据库中成功执行的查询

    select d.dt::date as date_current, 
   coalesce(underwriter_daily_cap, daily_file_cap) as file_cap, 
   user_id 
   from generate_series(date '2019-04-05', date '2019-04-09', 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 = 350 
         ) upd on upd.date_current = d.dt::date
          ORDER BY d.dt DESC;

DAO层代码如下

        @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  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 " ; 

            List<Object[]> records = session.createNativeQuery(queryString)
                        .getResultList();

            return records;  

  }

执行上述代码时控制台出现异常如下

hibernate :选择 d.dt:date 作为 date_current，coalesce(underwriter_daily_cap, daily_file_cap) 作为 file_cap，user_id fromgenerate_series (date '2019-04-04', date '2019-04-09', INTERVAL '1' DAY) as d(dt) left join ( select upd.*, ud.daily_file_cap from mod_under writer_daily_file_count upd join lsa_assignment_details ud on ud.user_id = upd.user_id where ud.user_id = 403) upd on upd.date_current = d.dt:date ORDER BY d.dt DESC2019-04-09 18:58 错误 SqlExceptionHelper:131 - 错误:“:”处或附近的语法错误 位置:14

Answer 1

这里有几个错误。比语法错误更重要的是如何将参数作为字符串文字附加。

而不是:

session.createNativeQuery("select * from tbl where param = '" + value + "'");

您必须使用准备好的语句(即要查找的术语)。这对于安全性极其重要。

Query q = session.createNativeQuery("select * from tbl where param = :par1");
q.setParameter("par1", value);

现在至于语法错误，这是由于您使用 PostgreSQL 的自定义类型转换语法引起的。您必须将其替换为从 d.dt::date 到 cast(d.dt as date) 的标准 SQL 类型转换，因为 Hibernate 期望 : 作为命名参数的开头。

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 ( current_date - 5, current_date, 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 = :dynuid ) upd on upd.date_current = cast(d.dt as date) ORDER BY d.dt DESC " ; 

List<Object[]> records = session.createNativeQuery(queryString)
                    .setParameter("dynuid", dynamicUserId)
                    .getResultList();

我还用数据库函数替换了您当前的日期 java 计算。而不是这样做:

Date currentDateMinusFive = DateUtil.getLastFiveDayOldPSTDate();
Date currentDate = DateUtil.getCurrentPSTDate();
...
.setParameter("ds", DateUtil.getDateFormat(currentDateMinusFive))
.setParameter("de", DateUtil.getDateFormat(currentDate))

您可以让数据库使用 current_date - 5 和 current_date 为您完成此操作。

您应该注意的另一件事是，由于您的 DateUtil 使用旧的 java.util.Date 类和相关内容，如果您使用 SimpleDateFormatter 对其进行格式化，那么每次调用时它都必须是一个新实例。不要将其优化为静态最终，因为它不是线程安全的。我就受过这个苦。

编辑:我也相信您的查询可以进一步优化，并且会产生相同的结果:

select upd.date_current, underwriter_daily_cap, 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 = :dynuid and
 upd.date_current between current_date - 5 and current_date
 ORDER BY d.dt DESC

请注意，我从选择中删除了一些字段，例如 user_id，因为您已经告诉数据库您想要哪个字段。一个主要区别是此查询将跳过没有条目的几天的结果。