CODEWITHSUNDEEP
springjava.util.date
AbNig
AbNig

Reputation: 352

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.

Upvotes: 9

Views: 19974

Answers (3)

waffledood
waffledood

Reputation: 273

great concise answers by JavaTec & S0m30n3

wanted to share an alternative using a java.util.Date that has been formatted as a String in the following date & time format

Date date = new Date();
String formattedDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);

in case anyone might need this

Upvotes: 0

JavaTec
JavaTec

Reputation: 1044

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);

Upvotes: 4

S0m30n3
S0m30n3

Reputation: 393

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.

Upvotes: 5

Related Questions