java - Spring 命名参数 : how can I parameterize Oracle interval in my query?

Question

我在尝试对 Oracle 查询中的间隔参数进行参数化时遇到问题:

select current_timestamp - interval :hours hour from dual

如果我用常量替换间隔参数，那么它执行得很好。

尝试在 SQL 中引用和不引用参数。

请参阅下面使用最小片段的插图:

public class Main {
    private static String SQL_CONSTANT_INTERVAL = "select current_timestamp - interval '1' hour from dual";

    private static String SQL_PARAMETERIZED_INTERVAL_QUOTED = "select current_timestamp - interval ':hours' hour from dual";

    private static String SQL_PARAMETERIZED_INTERVAL_UNQUOTED = "select current_timestamp - interval :hours hour from dual";

    public static void main(String[] args) throws Exception {
        Properties properties = new Properties();
        properties.load(Main.class.getClassLoader().getSystemResourceAsStream("db.properties"));
        DataSource dataSource = BasicDataSourceFactory.createDataSource(properties);

        NamedParameterJdbcTemplate npTemplate = new NamedParameterJdbcTemplate(dataSource);

        Map<String, String> params = Collections.singletonMap("hours", "1");

        String[] queries =
                new String[] { SQL_CONSTANT_INTERVAL, SQL_PARAMETERIZED_INTERVAL_QUOTED, SQL_PARAMETERIZED_INTERVAL_UNQUOTED };

        for (String q : queries) {
            System.out.println("Executing " + q);
            try {
                System.out.println("Result = " + npTemplate.queryForObject(q, params, String.class));
            } catch (RuntimeException e) {
                System.out.println("Error: " + e);
            }
            System.out.println();
        }
    }
}

输出:

Executing select current_timestamp - interval '1' hour from dual
Result = 2013-01-24 18:55:16.373 Europe/Moscow

Executing select current_timestamp - interval ':hours' hour from dual
24-Jan-2013 20:55:16 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
24-Jan-2013 20:55:16 org.springframework.jdbc.support.SQLErrorCodesFactory <init>
INFO: SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
Error: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [select current_timestamp - interval ':hours' hour from dual]; ORA-01867: the interval is invalid
; nested exception is java.sql.SQLDataException: ORA-01867: the interval is invalid


Executing select current_timestamp - interval :hours hour from dual
Error: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select current_timestamp - interval ? hour from dual]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected

Answer 1

间隔“1”小时 是 Oracle literal .因此，您不能使用绑定(bind)参数来替换它中间的 '1'。

相反，你可以使用这个:

NUMTODSINTERVAL( 1, 'HOUR' )

然后您可以像这样替换 Java SQL 中的整数文字:

NUMTODSINTERVAL( ?, 'HOUR' )