postgresql - 组织.postgresql.util.PSQLException : ERROR: syntax error at or near ":" Java side

Question

我尝试像这样查询 postgresql:

SELECT id , run_time 
FROM run  WHERE run.run_time  > '2020-02-04' and  run.run_time::date <= '2020-02-05'

我的问题是 run.run_time::date

JAVA:当我在调试中打印查询时，查询显示:

SELECT id , run_time 
    FROM run  WHERE run.run_time  > '2020-02-04' and  run.run_time::date <= '2020-02-05'

(同上)

所以我希望它按原样发送到数据库，然后我得到这个

错误日志:

Hibernate: 
    select
        id ,
        run_time ,
    from
        run  
    where
        run.cm_platform = 'COUGAR_RUN' 
        and  run.cm_sw LIKE '%104%'  
        and  run.run_time  > '2020-02-04' 
        and  run.run_time:date <= '2020-02-05'

2020-05-07 16:36:42.238  WARN 11184 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42601
2020-05-07 16:36:42.238 ERROR 11184 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: syntax error at or near ":"
  Position: 495
2020-05-07 16:36:42.254 ERROR 11184 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause

org.postgresql.util.PSQLException: ERROR: syntax error at or near ":"
  Position: 495
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:106) ~[postgresql-42.2.5.jar:42.2.5]
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-3.2.0.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-3.2.0.jar:na]
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2167) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1930) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1892) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:937) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:340) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]

当我手动尝试查询时，它工作正常但我在这里看到 and run.run_time:date <= '2020-02-05'不见了:: ，所以我认为这就是问题所在，我如何强制它识别它？

代码

@Override
    public List<Run> getCMstatisticDetails(SearchStatisticsForm searchStatisticsForm) {
        final String tableName = " run"; 


        String columnsName =                 
                Arrays.stream(Run.class.getFields()).filter(e-> (e.getAnnotation(Column.class) != null || e.getAnnotation(JoinColumn.class) != null)).
                map(e->(tableName + "." + getColumnName(e))).
                collect(Collectors.joining(" , ")); 

        StringBuilder queryStr = new StringBuilder(); 

        String thirdParam = (searchStatisticsForm.getCmPlatformVersion() == null) ? "" :
            tableName + ".run_time  > " + "'" + searchStatisticsForm.getStartDate() + "'"
            + " and " + tableName + ".run_time::date <= " + "'" + searchStatisticsForm.getEndDate() +"'";


        String queryStringBase =   "select " + columnsName  +
                " from " + tableName + "  where " + thirdParam;

        queryStr.append(queryStringBase); 

        Query q = entityManager.createNativeQuery(queryStr.toString(), Run.class); 

        List<Run> l = q.getResultList();
        return l;
    }

我到处查了一下，没有找到任何帮助，在此先感谢:)

Answer 1

你需要像这样转义“:”字符:

run.run_time\\:\\:date

或者你可以使用强制转换运算符

cast(run.runtime AS date)