javamysqloracle-databasehibernatecreatequery

Oracle TO_DATE function parallel in Mysql which should support both DB oracle and mysql

Initially, I was facing one weird problem, below query was running fine in Oracle SQL developer.

select * from sbill.act_sub_t where (act_sub_t.unsub_dt - act_sub_t.sub_dt) < 100;

but I was facing below issue in java Parameter value [100] did not match expected type[java.time.LocalDateTime (n/a)]

Below was the java code,

Query query1 = entityManager.createQuery("select * from ActSubT where (ActSubT.unsub_dt - ActSubT.sub_dt)<:days").setParameter("days", 100);

So to fix that problem I had used TO_DATE function in my java query and I was able to run in java 

select * from sbill.act_sub_t where (TO_DATE(ActSubT.unSubDt, 'DD-MM-YYYY') - TO_DATE(ActSubT.actualUnsubDt, 'DD-MM-YYYY')) < 100;

But actual problem is that my application supports both Oracle and MySQL Db and that TO_DATE function does not support in MYSQL so this query will not run when this application will run on MySQL.

So do we have any common parallel function of TO_DATE which supports both Oracle and MySQL?

Solution

  • Seems like hibernate doesn't check that act_sub_t.unsub_dt and act_sub_t.sub_dt are of type DATE.

    As TO_DATE is Oracle only, you can use the CAST-function which is common in your databases:

    CAST(ActSubT.unsubDt AS date) - CAST(ActSubT.subDt AS date)

    or

    CAST(ActSubT.unsubDt - ActSubT.subDt AS int)

    should be a good substitute.