java - Getting error when using prepareStatement with interval in query

Question

When running this query SELECT SYSDATE + INTERVAL '7' DAY FROM DUAL; in a 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, it clearly is, cuz i'm able to run the same query in sql-developer.

Is this a bug in PreparedStatement ? can i use prepared statements together with interval?

Answer 1

The entire expression INTERVAL '7' DAY is a literal, you cannot simply replace a part of it with a variable (parameter). Use the function NUMTODSINTERVAL(?,'DAY') instead.