代码之家 › 专栏 › 技术社区 › echoblaze

参数化oraclesql查询在Java中的应用?

parameterized-query jdbc oracle sql java

echoblaze · 技术社区 · 16 年前

我一直在试图找出为什么以下代码没有在我的结果集中生成任何数据:

String sql = "SELECT STUDENT FROM SCHOOL WHERE SCHOOL = ? ";
PreparedStatement prepStmt = conn.prepareStatement(sql);
prepStmt.setString(1, "Waterloo");
ResultSet rs = prepStmt.executeQuery();

String sql = "SELECT STUDENT FROM SCHOOL WHERE SCHOOL = 'Waterloo' ";
PreparedStatement prepStmt = conn.prepareStatement(sql);
ResultSet rs = prepStmt.executeQuery();

学校的数据类型是CHAR(9字节)。除了设置字符串,我还尝试了:

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();

我完全不知道下一步要调查什么;Eclipse调试器说,即使在setString或setCharacterStream之后,SQL查询也不会更改。我不确定这是因为设置参数不起作用,还是调试器无法检测到PreparedStatement中的更改。

任何帮助都将不胜感激,谢谢!

1 回复 | 直到 16 年前

Soundlink 16 年前

我认为问题是您的数据类型是CHAR(9),而“Waterloo”只有8个字符。我假设这将返回预期的结果(如和%)。或添加缺少的空间。

String sql = "SELECT STUDENT FROM SCHOOL WHERE SCHOOL LIKE ? ";
PreparedStatement prepStmt = conn.prepareStatement(sql);
prepStmt.setString(1, "Waterloo%");
ResultSet rs = prepStmt.executeQuery();

一种解决方法是使用特定于Oracle的setFixedCHAR方法(但如果可能,最好将数据类型更改为varchar)。

记住将prepared statement对象强制转换为OraclePreparedStatement以使用setFixedCHAR()方法。

下面的示例演示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

推荐文章