java - 无法在本机查询中使用“：”作为分隔符

Question

使用“：”作为分隔符将时间与日期分开。当我们在 sql 中运行查询时，它的工作原理。当 JPA 附带时，jpa 认为这个“：”分隔符作为命名参数，如何克服这个问题。

询问：

select (case when SPLIT_PART(
                     SPLIT_PART(inq.suggest_time::text, ' ', 2),
                     ':', 1
                  )::int <= 10 and
                  SPLIT_PART(
                     SPLIT_PART(inq.suggest_time::text, ' ', 2),
                     ':', 2
                  )::int < 30
             then CONCAT(
                     (SPLIT_PART(
                         SPLIT_PART(inq.suggest_time::text, ' ', 1),
                         '-', 1
                     )::int)::text,
                     '-',
                     (SPLIT_PART(
                         SPLIT_PART(inq.suggest_time::text, ' ', 1),
                         '-', 2
                     )::int)::text,
                     '-',
                     (SPLIT_PART(
                         SPLIT_PART(inq.suggest_time::text, ' ', 1),
                         '-', 3
                     )::int + 1)::text)
             else (SPLIT_PART(inq.suggest_time::text, ' ', 1))::text
             end) as dateValue,
       count(inq.suggest_time )
from inquiry_table inq
where inq.assigned_to= 63
  and (inq.suggest_time is not null)
  and inq.inquiry_status IN (select ld.lookup_detail_id
                             from lookup_detail ld
                             where ld.lookup_detail IN ('New','In Process'))
group by dateValue
ORDER BY dateValue;

应用程序错误：

Caused by: org.springframework.web.util.NestedServletException: Request processing failed;
nested exception is org.hibernate.QueryException: Not all named parameters have been set: [:text, :int] [select (case when SPLIT_PART(SPLIT_PART(inq.suggest_time::text, ' ', 2), ':', 1)::int <= 10 and SPLIT_PART(SPLIT_PART(inq.suggest_time::text, ' ', 2), ':', 2)::int < 30 then CONCAT((SPLIT_PART(SPLIT_PART(inq.suggest_time::text, ' ', 1), '-', 1)::int)::text, '-',(SPLIT_PART(SPLIT_PART(inq.suggest_time::text, ' ', 1), '-', 2)::int)::text, '-',(SPLIT_PART(SPLIT_PART(inq.suggest_time::text, ' ', 1), '-', 3)::int + 1)::text) else (SPLIT_PART(inq.suggest_time::text, ' ', 1))::text end) as dateValue,count(inq.suggest_time )  from inquiry inq where inq.assigned_to= 63 and (inq.suggest_time is not null) and inq.inquiry_status IN (select ld.lookup_detail_id from lookup_detail ld where ld.lookup_detail IN ('New','In Process')) group by dateValue ORDER BY dateValue]

标签： javaspringpostgresqljpa

Answer 1

将 ::text 和 ::int 中的 :: 转义，如下所示：

\\:\\:text
\\:\\:int

参考：https ://stackoverflow.com/a/29161044/6294804