Java 中的参数化 Oracle SQL 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3451269/
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
Parameterized Oracle SQL query in Java?
提问by echoblaze
I've been trying to figure out why the following code is not generating any data in my ResultSet:
我一直在试图弄清楚为什么下面的代码没有在我的 ResultSet 中生成任何数据：
String sql = "SELECT STUDENT FROM SCHOOL WHERE SCHOOL = ? ";
PreparedStatement prepStmt = conn.prepareStatement(sql);
prepStmt.setString(1, "Waterloo");
ResultSet rs = prepStmt.executeQuery();
On the other hand, the following runs properly:
另一方面，以下运行正常：
String sql = "SELECT STUDENT FROM SCHOOL WHERE SCHOOL = 'Waterloo' ";
PreparedStatement prepStmt = conn.prepareStatement(sql);
ResultSet rs = prepStmt.executeQuery();
The data type for SCHOOL is CHAR (9 Byte). Instead of setString, I also tried:
SCHOOL 的数据类型是 CHAR（9 字节）。而不是 setString，我还尝试过：
String sql = "SELECT STUDENT FROM SCHOOL WHERE SCHOOL = ? ";
PreparedStatement prepStmt = conn.prepareStatement(sql);
String school = "Waterloo";
Reader reader = new CharArrayReader(school.toCharArray());
prepStmt.setCharacterStream(1, reader, 9);
prepStmt.setString(1, "Waterloo");
ResultSet rs = prepStmt.executeQuery();
I'm completely stuck on what to investigate next; the Eclipse debugger says the SQL query doesn't change even after setString or setCharacterStream. I'm not sure if it's because setting parameters isn't working, or if the debugger simply can't pick up changes in the PreparedStatement.
我完全不知道下一步要调查什么；Eclipse 调试器说即使在 setString 或 setCharacterStream 之后 SQL 查询也不会改变。我不确定是因为设置参数不起作用，还是调试器根本无法获取 PreparedStatement 中的更改。
Any help will be greatly appreciated, thanks!
任何帮助将不胜感激，谢谢！
采纳答案by Soundlink
I think the problem is that your datatype is CHAR(9) and "Waterloo" has only 8 chars. I assume that this would return the expected results (LIKE and %). Or add the missing space.
我认为问题在于您的数据类型是 CHAR(9) 而“Waterloo”只有 8 个字符。我假设这会返回预期的结果（LIKE 和 %）。或者添加缺少的空间。
String sql = "SELECT STUDENT FROM SCHOOL WHERE SCHOOL LIKE ? ";
PreparedStatement prepStmt = conn.prepareStatement(sql);
prepStmt.setString(1, "Waterloo%");
ResultSet rs = prepStmt.executeQuery();
The best way would by to use varchar instead of char if your Strings have a flexible length. Then the PreparedStatement would work as expected.
如果您的字符串具有灵活的长度，最好的方法是使用 varchar 而不是 char。然后 PreparedStatement 将按预期工作。
A workaround would be to use the Oracle specific setFixedCHAR method (but it's better to change the datatype to varchar if possible).
一种解决方法是使用 Oracle 特定的 setFixedCHAR 方法（但如果可能，最好将数据类型更改为 varchar）。
The following is from Oracle's PreparedStatement JavaDoc:
以下来自 Oracle 的 PreparedStatement JavaDoc：
数据库中的 CHAR 数据填充到列宽。这导致在使用 setCHAR() 方法将字符数据绑定到 SELECT 语句的 WHERE 子句时受到限制——WHERE 子句中的字符数据也必须填充到列宽以在 SELECT 语句中产生匹配。如果您不知道列宽，这将特别麻烦。
setFixedCHAR() remedies this. This method executes a non-padded comparison.
setFixedCHAR() 解决了这个问题。此方法执行非填充比较。
Notes:
笔记：
- Remember to cast your prepared statement object to OraclePreparedStatement to use the setFixedCHAR() method.
- There is no need to use setFixedCHAR() for an INSERT statement. The database always automatically pads the data to the column width as it inserts it.
- 请记住将准备好的语句对象强制转换为 OraclePreparedStatement 以使用 setFixedCHAR() 方法。
- 不需要对 INSERT 语句使用 setFixedCHAR()。数据库总是在插入数据时自动将数据填充到列宽。
The following example demonstrates the difference between the setString(), setCHAR() and setFixedCHAR() methods.
以下示例演示了 setString()、setCHAR() 和 setFixedCHAR() 方法之间的区别。
// Schema is : create table my_table (col1 char(10));
// insert into my_table values ('JDBC');
PreparedStatement pstmt = conn.prepareStatement
("select count() from my_table where col1 = ?");
ResultSet rs;
pstmt.setString (1, "JDBC"); // Set the Bind Value
rs = pstmt.executeQuery(); // This does not match any row
// ... do something with rs
CHAR ch = new CHAR("JDBC ", null);
((OraclePreparedStatement)pstmt).setCHAR(1, ch); // Pad it to 10 bytes
rs = pstmt.executeQuery(); // This matches one row
// ... do something with rs
((OraclePreparedStatement)pstmt).setFixedCHAR(1, "JDBC");
rs = pstmt.executeQuery(); // This matches one row
// ... do something with rs
