Question

jdbc查询数据库报：java.sql.SQLException: ORA-01006: 绑定变量不存在

错误信息如下：
java.sql.SQLException: ORA-01006: 绑定变量不存在
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1451)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:651)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2117)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2331)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:422)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:366)

代码如下：
/**
* 按条件查询
* @param um
* @return
*/
public ArrayList getUserByCondithion(UserModel um){
ArrayList list = new ArrayList();
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
conn = JdbcConn.getConn();
String sql = "select uuid,name,age from tbl_user where 1=1";
pstmt = conn.prepareStatement(sql);
sql = getSql(sql, um);
setSqlValue(pstmt,um);
rs = pstmt.executeQuery();
list = this.rs2Model(rs);
return list;
}catch(Exception err){
err.printStackTrace();
}finally{
try {
rs.close();
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}

/**
* 按条件查询sql
* @param sql
* @param um
* @return
*/
private String getSql(String sql, UserModel um){
if(um.getUuid() != null && um.getUuid().length() > 0){
sql += " and uuid=? ";
}
if(um.getName() != null && um.getName().length() > 0){
sql += " and name=? ";
}
if(um.getAge() != null && um.getAge().length() > 0){
sql += " and age=? ";
}
return sql;
}

/**
* 设置sql位置
* @param pstmt
* @param um
* @throws SQLException
* @throws SQLException
*/
private void setSqlValue(PreparedStatement pstmt,UserModel um) throws SQLException{
int i = 1;
if(um.getUuid() != null && um.getUuid().trim().length() > 0){
pstmt.setString(i, um.getUuid());
i++;
}
if(um.getName() != null && um.getName().trim().length() > 0){
pstmt.setString(i, um.getName());
i++;
}
if(um.getAge() != null && um.getAge().trim().length() > 0){
pstmt.setString(i, um.getAge());
i++;
}

}

查询总报“绑定变量不存在”，我的建表语句是：
-- Create table
create table TBL_USER
(
UUID VARCHAR2(10),
NAME VARCHAR2(10),
AGE VARCHAR2(20)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64
next 1
minextents 1
maxextents unlimited
);

执行setSqlValue(pstmt,um)方法时pstmt.setString(i, um.getUuid())这个um.getUuid()是有值的，但到了rs = pstmt.executeQuery()时rs是null，请高手看看这个错是什么原因造成的，非常感谢！

Answer 1

setSqlValue方法为什么返回是void呢，你试试看把传入的pstmt返回回去，在主函数处接收返回的pstmt，就解决了！

追问

弱弱地问一下，如果setSqlValue返回pstmt的话，主函数处应该怎么写啊？

追答

setSqlValue方法修改为：
private PreparedStatement setSqlValue(PreparedStatement pstmt,UserModel um) throws SQLException

setSqlValue(pstmt,um);处修改为：
pstmt = setSqlValue(pstmt,um);

追问

改成这样之后报：java.lang.NullPointerException
我也认为是这个setSqlValue(PreparedStatement pstmt,UserModel um)方法的问题，pstmt.setString(i, um.getUuid())是可以取到值的，但最后传进去的sql等号后面没有值

追答

你是用单步调试看的吧？PreparedStatement 中的sql语句是不可见的
另外
pstmt = conn.prepareStatement(sql);
sql = getSql(sql, um);
此处的顺序有问题，你需要先getSql，再创建PreparedStatement ，调整为：
sql = getSql(sql, um);
pstmt = conn.prepareStatement(sql);