针对Oracle的Java中的PreparedStatement问题 [英] PreparedStatement question in Java against Oracle

问题描述

出于安全性和性能原因，我正在修改一些代码以使用preparedStatement而不是普通的Statement。

I'm working on the modification of some code to use preparedStatement instead of normal Statement, for security and performance reason.

我们的应用程序当前正在将信息存储到嵌入式德比数据库，但我们很快将转向Oracle。

Our application is currently storing information into an embedded derby database, but we are going to move soon to Oracle.

我发现了两件事我需要你帮助的人关于Oracle和准备好的声明：

I've found two things that I need your help guys about Oracle and Prepared Statement :

1-我发现这份文件说Oracle不会将绑定参数处理为IN子句，因此我们无法提供如下查询：

1- I've found this document saying that Oracle doesn't handle bind parameters into IN clauses, so we cannot supply a query like :

Select pokemon from pokemonTable where capacity in (?,?,?,?)

这是真的吗？有没有解决方法？ ......为什么？

Is that true ? Is there any workaround ? ... Why ?

2-我们有一些TIMESTAMP类型的字段。因此，使用我们的实际Statement，查询如下所示：

2- We have some fields which are of type TIMESTAMP. So with our actual Statement, the query looks like this :

Select raichu from pokemonTable where evolution = TO_TIMESTAMP('2500-12-31 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.FF')

准备好的声明应该怎么做？我应该输入参数数组：
2500-12-31或TO_TIMESTAMP（'2500-12-31 00：00：00.000'，'YYYY-MM-DD HH24：MI：SS.FF'）？

What should be done for a prepared Statement ? Should I put into the array of parameters : 2500-12-31 or TO_TIMESTAMP('2500-12-31 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.FF') ?

感谢您的帮助，我希望我的问题清楚！

Thanks for your help, I hope my questions are clear !

问候，

推荐答案

我有点惊讶地看到这个文件。确实，您无法设置如下所示的数组/集合（这与所使用的数据库/ JDBC驱动程序无关）：

I'm a bit surprised to see this document. It is true that you cannot set an array/collection like follows (and this is regardless of the database / JDBC driver used):

String sql = "SELECT col FROM tbl WHERE id IN (?)";
statement = connection.prepareStatement(sql);
statement.setArray(1, arrayOfValues); // Fail.

但是文档中提到的查询应该有效。我可以从至少Oracle 10g XE与 ojdbc14.jar 的经验中得出这个结论。我怀疑文档的作者是混淆了什么，或者它实际上涉及到DB和/或JDBC驱动程序的不同（较旧的？）版本。

But the in the document mentioned query ought to work. I can tell this from experience with at least Oracle 10g XE in combination with ojdbc14.jar. I suspect that either the author of the document confused things, or it actually concerns a different (older?) version of the DB and/or JDBC driver.

以下应该无论使用哪种JDBC驱动程序都可以工作（尽管你依赖于数据库使用了IN子句可以包含多少项，但是Oracle（是的，再次）有大约1000项限制）：

The following ought to work regardless of the JDBC driver used (although you're dependent on the DB used how many items the IN clause can contain, Oracle (yes, again) has a limit of around 1000 items):

private static final String SQL_FIND = "SELECT id, name, value FROM data WHERE id IN (%s)";

public List<Data> find(Set<Long> ids) throws SQLException {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    List<Data> list = new ArrayList<Data>();
    String sql = String.format(SQL_FIND, preparePlaceHolders(ids.size()));

    try{
        connection = database.getConnection();
        statement = connection.prepareStatement(sql);
        setValues(statement, ids.toArray());
        resultSet = statement.executeQuery();
        while (resultSet.next()) {
            Data data = new Data();
            data.setId(resultSet.getLong("id"));
            data.setName(resultSet.getString("name"));
            data.setValue(resultSet.getInt("value"));
            list.add(data);
        }
    } finally {
        close(connection, statement, resultSet);
    }

    return list;
}

public static String preparePlaceHolders(int length) {
    StringBuilder builder = new StringBuilder();
    for (int i = 0; i < length;) {
        builder.append("?");
        if (++i < length) {
            builder.append(",");
        }
    }
    return builder.toString();
}

public static void setValues(PreparedStatement preparedStatement, Object... values) throws SQLException {
    for (int i = 0; i < values.length; i++) {
        preparedStatement.setObject(i + 1, values[i]);
    }
}

关于 TIMESTAMP 问题，只需使用 PreparedStatement＃setTimestamp（） 。

With regard to the TIMESTAMP question, just use PreparedStatement#setTimestamp().

这篇关于针对Oracle的Java中的PreparedStatement问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！