CODEWITHSUNDEEP
javajdbcprepared-statement
crystal_ss
crystal_ss

Reputation: 171

Java prepareStatement setting

I have prepare Statement like this:

WHERE  TRADE_DATE >= TRUNC(add_months(current_date,-12))

But I want to have it like this:

WHERE  TRADE_DATE >= TRUNC(?)

And set the value later like this:

query.setString(1,"add_months(current_date,-12)");

or

query.setString(1,"trunc(sysdate, 'YEAR')");

However, I got error saying 

inconsistent datatypes: expected DATE got NUMBER

How can I do that?

Upvotes: 0

Views: 86

Answers (2)

Joop Eggen
Joop Eggen

Reputation: 109547

You could set a java.sql.Date calculated with Calendar as SQL did.

Otherwise - if the expressions to fill in can be enumerated, you can use an SQL CASE expression switching on a choice number:

CASE ? WHEN 1 THEN add_months(current_date,-12)
       WHEN 2 THEN trunc(sysdate, 'YEAR')
END

query.setInt(choice);

Maybe something for a stored procedure.

Upvotes: 0

Brian Agnew
Brian Agnew

Reputation: 272217

I think you'll have to build your template statement using string concatenation etc, and record the parameters you wish to insert alongside that (in an array of objects?).

Then iterate through each one and perform your setString() etc. You can't insert arbitrary SQL using PreparedStatement (it would effectively defeat one of PreparedStatement's benefits, which is to restrict the SQL construction to customising parameters)

Upvotes: 1

Related Questions