oracle 准备好的语句 - 使用函数作为 where 子句的一部分
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1520209/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Prepared statement - using a function as part of the where clause
提问by emulcahy
I am working with a Java prepared statement that gets data from an Oracle database. Due to some performance problems, the query uses a "virtual column" as an index.
我正在使用从 Oracle 数据库获取数据的 Java 准备语句。由于一些性能问题，查询使用“虚拟列”作为索引。
The query looks like this:
查询如下所示：
String status = "processed";
String customerId = 123;
String query = "SELECT DISTINCT trans_id FROM trans WHERE status = " + status + " AND FN_GET_CUST_ID(trans.trans_id) = " + customerId;
Connection conn = getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(query);
ps.execute();
...
} catch (...)
This does not work. Having the function as part of the where clause causes a SQLException. I am aware of CallableStatement, and know I could use that first and then concatenate the results. However, this table uses FN_GET_CUST_ID(trans_id) as part of it's index. Is there a way to use a prepared statement with a database function as a query parameter?
这不起作用。将该函数作为 where 子句的一部分会导致 SQLException。我知道 CallableStatement，并且知道我可以先使用它，然后连接结果。但是，该表使用 FN_GET_CUST_ID(trans_id) 作为其索引的一部分。有没有办法将准备好的语句与数据库函数一起用作查询参数？
回答by Aaron Digulla
Never concatenate arguments for the SQL into the String. Always use placeholders (
?) andsetXxx(column, value);.You'll get the same error if you'd run the SQL in a your favorite DB tool. The problem is that Oracle can't use the function for some reason. What error code do you get?
切勿将 SQL 的参数连接到字符串中。始终使用占位符 (
?) 和setXxx(column, value);.如果您在您喜欢的数据库工具中运行 SQL，您将得到同样的错误。问题是由于某种原因，Oracle 无法使用该功能。你得到什么错误代码？
回答by quosoo
If Customer ID is numeric keep in int not in String. Then try doing the following:
如果客户 ID 是数字，则保留在 int 中而不是 String 中。然后尝试执行以下操作：
String query = "SELECT DISTINCT trans_id FROM trans WHERE status = ? AND FN_GET_CUST_ID(trans.trans_id) = ?";
ps = conn.prepareStatement(query);
ps.setString(1, status);
ps.setInt(2, customerId);
ps.execute();
Besides other benefits of prepared statement you won't have to remember about string quotations (this causes your error most likely) and escaping of the special characters.
除了准备好的语句的其他好处之外，您不必记住字符串引用（这很可能会导致您的错误）和特殊字符的转义。
回答by shahkalpesh
At the first glance, the query seems to be incorrect. You are missing an apostrophe before and after the usage of statusvariable (assuming that status is a varchar column).
乍一看，查询似乎不正确。在使用status变量之前和之后都缺少撇号（假设状态是 varchar 列）。
String query = "SELECT DISTINCT trans_id FROM trans
WHERE status = '" + status + "' AND FN_GET_CUST_ID(trans.trans_id) = " + customerId;
EDIT: I am not from java background. However, as @Aron has said, it is better to use placeholders & then use some method to set values for parameters to avoid SQL Injection.
编辑：我不是来自 java 背景。但是，正如@Aron 所说，最好使用占位符然后使用某种方法设置参数值以避免SQL 注入。
