Spring boot-JPA的@Query不支持”TIMESTAMP“关键字------ORA-00933: SQL 命令未正确结束

Spring boot-JPA的@Query不支持”TIMESTAMP“关键字------ORA-00933: SQL 命令未正确结束

问题描述

因sql中需要group by分组以及添加其他Where条件过滤，将写好的sql 在客户端中执行正常获取到结果集，但是在Spring boot的JPA的@Query中去执行该sql就有遇到了惊喜：

Caused by: Error : 933, Position : 95, Sql = SELECT BOND_ID,COUNT(BOND_ID) AS NUM  FROM XXXX(数据库表名) WHERE WRITE_TIME = TIMESTAMP :1  AND (BOOK_MANAGER_CODE = :2  OR ORG_CODE =:3 ) GROUP BY BOND_ID , OriginalSql = SELECT BOND_ID,COUNT(BOND_ID) AS NUM  FROM KFJK_CDC_PLACING_INFO  WHERE WRITE_TIME = TIMESTAMP ? AND (BOOK_MANAGER_CODE = ? OR ORG_CODE =?) GROUP BY BOND_ID , Error Msg = ORA-00933: SQL 命令未正确结束

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
	... 91 more

问题原因：

Spring boot-JPA的@Query不支持”TIMESTAMP“关键字。

解决方案：

需要将SQL改成：

   @Query(value = "SELECT BOND_ID,COUNT(BOND_ID) AS NUM  FROM XXXX(数据库表名) WHERE to_CHAR(WRITE_TIME,'yyyy-MM-dd hh24:mi:ss')= ?1  AND (BOOK_MANAGER_CODE = ?2 OR ORG_CODE =?3) GROUP BY BOND_ID ", nativeQuery=true)

即用TO_CHAR函数进行转换字段类型进行where过滤。