英文:
jdbc oracle 11g PreparedStatement not producing results
问题
以下是您要翻译的代码部分：
我试图根据委员会列上的条件使用jdbc检索数据。使用Statement可以产生期望的结果，但使用PreparedStatement则不行。我无法弄清楚出了什么问题。请帮助。以下是使用Statement和使用PreparedStatement的两个程序以及我的表结构。
import java.sql.*;
class SelectPrepared {
public static void main(String args[]) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "abcd", "abcd");
String sql = "select * from tatuserinfo where committee = 'GENERAL'";
Statement stmt = con.createStatement();
// stmt.setString(1, "GENERAL");//1 specifies the first parameter in the query
ResultSet myRs = stmt.executeQuery(sql);
while (myRs.next()) {
System.out.println(myRs.getString(1) + myRs.getString(2) + myRs.getString(3) + myRs.getString(4)
+ myRs.getString(5) + myRs.getString(6) + myRs.getString(7) + myRs.getString(8));
}
con.close();
} catch (Exception e) {
System.out.println(e);
}
}
}
import java.sql.*;
class SelectPreparedOne {
public static void main(String args[]) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String s = "GENERAL";
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "abcd", "abcd");
String sql = "select * from tatuserinfo where committee = ?";
PreparedStatement stmt = con.prepareStatement(sql);
stmt.setString(1, s);// 1 specifies the first parameter in the query
ResultSet myRs = stmt.executeQuery();
while (myRs.next()) {
System.out.println(myRs.getString(1) + myRs.getString(2) + myRs.getString(3) + myRs.getString(4)
+ myRs.getString(5) + myRs.getString(6) + myRs.getString(7) + myRs.getString(8));
}
con.close();
} catch (Exception e) {
System.out.println(e);
}
}
}
**表结构**
<pre>
USERNAME VARCHAR2(40)
PASSWORD VARCHAR2(40)
ROLE VARCHAR2(40)
NAME VARCHAR2(40)
DESIGNATION VARCHAR2(40)
DEPARTMENT VARCHAR2(40)
EMAILID VARCHAR2(40)
COMMITTEE CHAR(15)
</pre>
英文:
I am trying fetch data based on a condition on committee column using jdbc.Using Statement it produces the desired result but using PreparedStatement it does not.I cannot figure out what has gone wrong.Kindly help.Here is both the programs one using Statement and the other one using PreparedStatement and my table structure as well
import java.sql.*;
class SelectPrepared {
public static void main(String args[]) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "abcd","abcd");
String sql = "select * from tatuserinfo where committee = 'GENERAL'";
Statement stmt = con.createStatement();
// stmt.setString(1,"GENERAL");//1 specifies the first parameter in the query
ResultSet myRs = stmt.executeQuery(sql);
while (myRs.next()) {
System.out.println(myRs.getString(1) + myRs.getString(2) + myRs.getString(3) + myRs.getString(4)
+ myRs.getString(5) + myRs.getString(6) + myRs.getString(7) + myRs.getString(8));
}
con.close();
} catch (Exception e) {
System.out.println(e);
}
}
}
import java.sql.*;
class SelectPreparedOne {
public static void main(String args[]) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String s = "GENERAL";
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "abcd","abcd");
String sql = "select * from tatuserinfo where committee = ?";
PreparedStatement stmt = con.prepareStatement(sql);
stmt.setString(1, s);// 1 specifies the first parameter in the query
ResultSet myRs = stmt.executeQuery();
while (myRs.next()) {
System.out.println(myRs.getString(1) + myRs.getString(2) + myRs.getString(3) + myRs.getString(4)
+ myRs.getString(5) + myRs.getString(6) + myRs.getString(7) + myRs.getString(8));
}
con.close();
} catch (Exception e) {
System.out.println(e);
}
}
}
Table structure
<pre>
USERNAME VARCHAR2(40)
PASSWORD VARCHAR2(40)
ROLE VARCHAR2(40)
NAME VARCHAR2(40)
DESIGNATION VARCHAR2(40)
DEPARTMENT VARCHAR2(40)
EMAILID VARCHAR2(40)
COMMITTEE CHAR(15)
</pre>
答案1
得分: 1
TL/DR: 不要使用 char，使用 varchar2
CHAR(15) 会在后面添加空格以使列达到15个字符的长度，因此该列包含值 'GENERAL '，而这不等于提供的值 'GENERAL'
正确的修复方法是将列更改为 VARCHAR2(15)
一个中间的不太美观的解决方法（直到你修复列定义）是使用 trim 函数：
where trim(committee) = ?;
英文:
TL/DR: don't use char use varchar2
CHAR(15) gets blank padded to 15 characters, so the column contains the value 'GENERAL ' and that's not equal to the supplied value of 'GENERAL'
The correct fix is to change the column to VARCHAR2(15)
An intermediate ugly workaround (until you fix the column definition) is to use trim:
where trim(committee) = ?;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论