Passing Date to NamedParameterJdbcTemplate in Select query to oracle

Question

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.

Answer 1

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.

Answer 2

A little variation to above solution can be when your input(lastSuccessfulReplicationTimeStamp1/lastSuccessfulReplicationTimeStamp2) is a String instead of Date/TimeStamp (which is what i was looking for and found at this link -> may be it can help someone):

MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("lastSuccessfulReplicationTimeStamp1", lastSuccessfulReplicationTimeStamp1, Types.TIMESTAMP);
parameters.addValue("lastSuccessfulReplicationTimeStamp2", lastSuccessfulReplicationTimeStamp2, Types.TIMESTAMP);

list = namedParameterJdbcTemplateOracle.query(sql, parameters, myTabRowMapper);