java - SpringBoot oracle.jdbc.OracleDatabaseException : ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Question

2019-03-01 16:38:44.930 WARN 55052 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 932, SQLState: 42000

2019-03-01 16:38:44.930 ERROR 55052 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : ORA-00932: inconsistent datatypes: expected DATE got NUMBER

2019-03-01 16:38:44.946 ERROR 55052 --- [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

oracle.jdbc.OracleDatabaseException: ORA-00932: inconsistent datatypes: expected DATE got NUMBER

我最终想要的查询，我想只是没有使用 spring CrudRepository 正确形成:

select * from user_usage where Prs_Id=1104438622 and createddate > sysdate -1;

描述用户使用情况

Name          Null     Type           
------------- -------- -------------- 
USAGETYPEID   NOT NULL NUMBER(3)      
PRS_ID        NOT NULL NUMBER(18)     
CREATEDID     NOT NULL NUMBER(10)     
CREATEDDATE   NOT NULL DATE           
COMMENTS               VARCHAR2(4000) 
USERAGENTID            NUMBER(10)  

1)POJO

    @Entity
    @Table(name="USER_USAGE)
    public class Usage {

        @Column(name="Prs_Id")
        @Id
        private long prsId;

        @Column(name="createddate")
        private Date createddate;

2)存储库

    public interface UsageRepository extends CrudRepository<Usage, Date>{


        //select * from user_usage where Prs_Id=1104438622 and createddate > sysdate -1; --query that I want eventually 
         @Query("SELECT a FROM Usage a WHERE a.prsId=:prsId and a.createddate>=:createddate-1")
         Usage fetchUsageGreaterThanEqual(@Param("prsId") Long prsId, @Param("createddate") Date createddate);
    }

3) Controller :

    @Autowired
    UsageRepository usageRepository; 
    static Date myDate;

    @GetMapping("/{prsId}")
     public Usage getUsageByPrsId(@PathVariable Long prsId) {
      return usageRepository.fetchUsageGreaterThanEqual(prsId, myDate);
     }

Answer 1

您的Date类很可能是java.util.Date

static Date myDate;

您必须将其转换为 java.sql.Date，如所述 here

new java.sql.Date(createddate.getTime())

避免出现 ORA-00932: 不一致的数据类型: 预期 DATE 为 NUMBER

的问题

或者，您也可以使用 java.time所讨论的类here和there