org.postgresql.util.PSQLException: ERROR: syntax error at or near ":" Java side

user_13491229 0
Viewed 4816

I trying to query to postgresql like this: 

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

my issue is the run.run_time::date

JAVA : When I print the query in debug , the query shows: 

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

(As above)

So I'm expecting this to be sent as is to DB, then I get this

ERROR log:

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]

When I try manually the query , it works fine BUT here I'm seeing that and run.run_time:date <= '2020-02-05' is missing :: , so I think that's the problem , how do I force it to recognize it?

Code 

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

I looked this up everywhere , found nothing helpful , thanks in advance :)

postgresql
2 Answers

You need to escape the ":" characters like this:

run.run_time\\:\\:date

or you can use the cast operator

cast(run.runtime AS date)
user_1210350

The reason why its happens, with ::" hibernate expects params, but there is no param, so it shows error. There is a other solutions:

cast (run.run_time as date) <= '2020-02-05'

but usually it`s bad idea to do any operations(cast or functions,) with field in where part, unless it is not indexed with function. You can rewrite it to

run.run_time < '2020-02-06'

OR

run.run_time < cast ('2020-02-05' as date) + interval '1 DAYS'
user_40430820
Related