springjava.util.date

Passing Date to NamedParameterJdbcTemplate in Select query to oracle

I have a query as below which is returning expected records when run from the SQL Developer

SELECT *
FROM MY_TABLE WHERE ( CRT_TS > TO_DATE('25-Aug-2016 15:08:18', 'DD-MON-YYYY HH24:MI:SS') 
or UPD_TS > TO_DATE('25-Aug-2016 15:08:18', 'DD-MON-YYYY HH24:MI:SS'));

I think that we will not need to apply TO_DATE when we are passing java.util.Date object as date parameters but the below code snippet is silently returning me 0 records.

My SQL query in Java class is as below:

SELECT * 
FROM MY_TABLE WHERE ( CRT_TS > :lastSuccessfulReplicationTimeStamp1 
or UPD_TS > :lastSuccessfulReplicationTimeStamp2);

The code which executes the above query is as below but the below code snippet is silently returning me 0 records:

    parameters.put("lastSuccessfulReplicationTimeStamp1", new java.sql.Date(outputFileMetaData.getLastSuccessfulReplicationTimeStamp().getTime()));
    parameters.put("lastSuccessfulReplicationTimeStamp2", new java.sql.Date(outputFileMetaData.getLastSuccessfulReplicationTimeStamp().getTime()));
    list = namedParameterJdbcTemplateOracle.query(sql, parameters, myTabRowMapper);

Please advise.

Solution

  • I guess you already found the answer but if anybody else needs it, here's what I've found:

    java.sql.Date doesn't have time, just the date fields. Either use java.sql.Timestamp or java.util.Date. Both seems to be working for me with NamedParameterJdbcTemplate.