Getting error while using prepareStatement with interval in query

Question

When executing this query SELECT SYSDATE + INTERVAL '7' DAY FROM DUAL; in prepareStatement like this

  PreparedStatement ps = connection.prepareStatement("select sysdate + interval ? day from dual" ); ps.setString(1, "7"); ps.executeQuery(); 

It will throw an exception that the syntax is not good, this is clear because I can run the same query in the sql developer.

Is this a bug in PreparedStatement ? Can I use prepared instructions along with the interval?

Answer 1

The entire INTERVAL '7' DAY expression is a literal; you cannot just replace part of its variable (parameter). Use the NUMTODSINTERVAL(?,'DAY') function NUMTODSINTERVAL(?,'DAY') .

Answer 2

PreparedStatement does not work with interval literals, so neither setInt nor setString work! http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm

For this reason, if you want to dynamically change the days in your query (with interval literals), the only way is to concatenate this value.

In your example:

 int yourDays = 7; String query ="select sysdate + interval '" + yourDays + "' day from dual "; 

Then you can use PreparedStatement if you need to add other parameters and execute the query.