Java “ORA-01008：并非所有变量都绑定”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6469753/
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
"ORA-01008: not all variables bound" error
提问by Adnan
I am using following method for calculating payroll by using jdbc but "ORA-01008: not all variables bound" error is not removing.
我正在使用以下方法通过 jdbc 计算工资单，但“ORA-01008：并非所有变量都绑定”错误未消除。
Any idea please?
请问有什么想法吗？
I am using following code
我正在使用以下代码
public double getPayroll(){
ResultSet rs = null;
ResultSet rs1 = null;
ResultSet rs2 = null;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getDBConnection();
double dailyPay=0,basicPay=0,payroll2=0;
int houseRent=0,convAllow=0,noOfPresents=0,empId=0;
String q = "select e_id from employee";
pstmt = conn.prepareStatement(q);
rs = pstmt.executeQuery();
while (rs.next()) {
empId=rs.getInt(1);
String q1 = "select count(att_status) from attendance where att_status='p'";
pstmt = conn.prepareStatement(q1);
rs1 = pstmt.executeQuery(q1);
while(rs1.next()){
noOfPresents=rs1.getInt(1);
String q2 = "select e_salary,e_house_rent,e_conv_allow from employee where e_id=?";
pstmt = conn.prepareStatement(q2);
pstmt.setInt(1,empId);
rs2 = pstmt.executeQuery(q2);
while(rs2.next()){
dailyPay=rs2.getInt(1)/22;
houseRent=rs2.getInt(2);
convAllow=rs2.getInt(3);
basicPay=dailyPay*noOfPresents;
payroll2+=basicPay+houseRent+convAllow;
}
}
}
return payroll2;
}catch (Exception e) {
e.printStackTrace();
return 0.0;
} finally {
try {
rs.close();
pstmt.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
采纳答案by skaffman
Your problem is here:
你的问题在这里：
rs2 = pstmt.executeQuery(q2);
You're telling the PreparedStatementto execute the SQL q2, rather than executing the SQL previously prepared. This should just be:
您告诉PreparedStatement执行 SQL q2，而不是执行先前准备的 SQL。这应该只是：
rs2 = pstmt.executeQuery();
This is a fairly common mistake, caused mainly by the bad class design of java.sql.Statementand its subtypes.
这是一个相当常见的错误，主要是由于 的类设计java.sql.Statement及其子类型的错误造成的。
As @RMT points out, you make the same mistake here:
正如@RMT 指出的那样，您在这里犯了同样的错误：
rs1 = pstmt.executeQuery(q1);
This doesn't matter so much, since there are no placeholders in q1, so the SQL executes as-is. It's still wrong, though.
这无关紧要，因为 中没有占位符q1，所以 SQL 按原样执行。尽管如此，它仍然是错误的。
Lastly, you should consider calling close()on the first PreparedStatement, before re-assigning the pstmtvariable to another one. You risk a leak if you don't do that.
最后，您应该考虑close()在PreparedStatement将pstmt变量重新分配给另一个变量之前调用第一个。如果你不这样做，你就有泄漏的风险。
回答by a_horse_with_no_name
One reason might be that you cannot re-use the instance of pstmt like that. You have to use a separate PreparedStatement instance in each level of the loop.
一个原因可能是您不能像这样重用 pstmt 的实例。您必须在循环的每个级别中使用单独的 PreparedStatement 实例。
Are you aware that this can be done with just a single statement as well?
你知道这也可以用一个单一的语句来完成吗？
Edit:
Assuming there isa relation between employee and attendance, something like this would return the sum in a single request:
编辑：
假设有是员工考勤之间的关系，这样的事情会返回一个请求的总和：
select sum( (e_salary / 22) * att_count + e_house_rent + e_conv_allow )
from (
select emp.e_salary
emp.e_house_rent,
emp.e_conv_allow,
(select count(att.att_status) from attendance att where att.e_id = mp.e_id) s att_count
from employee emp
) t
If indeed attendance is not linked to employee, just leave out the where clause in the nested select.
如果出勤确实与员工无关，只需在嵌套选择中省略 where 子句即可。
回答by Basanth Roy
pstmt = conn.prepareStatement(q2);
pstmt.setInt(1,empId);
rs2 = pstmt.executeQuery(q2);
You have already created the prepared statement with the query q2 and bound the variable empId to it. if you now invoke pstmt.executeQuery(q2), the variable binding is lost. The JDBC driver probably parses the unbound sql q2 when you execute pstmt.executeQuery(q2).
您已经使用查询 q2 创建了准备好的语句并将变量 empId 绑定到它。如果您现在调用 pstmt.executeQuery(q2)，则变量绑定将丢失。当您执行 pstmt.executeQuery(q2) 时，JDBC 驱动程序可能会解析未绑定的 sql q2。
回答by Amit Agarwal
UPDATE TESTCP SET CP_KEY2 =?, CP_DESC =?, CP_MAKER =?, CP_MAKER_DT =SYSDATE, CP_STATUS ='M' WHERE CP_LANGUAGE = ? AND CP_ENG_CODE = ? AND CP_KEY1 =? AND CP_LANGUAGE =?
更新 TESTCP SET CP_KEY2 =?, CP_DESC =?, CP_MAKER =?, CP_MAKER_DT =SYSDATE, CP_STATUS ='M' WHERE CP_LANGUAGE = ? AND CP_ENG_CODE = ? AND CP_KEY1 =？和 CP_LANGUAGE =?
In the above query we have 7 in parameter but if in your java code PreparedStatement you have set only 6 parameter values .
在上面的查询中，我们有 7 个参数，但如果在您的 Java 代码 PreparedStatement 中，您只设置了 6 个参数值。
That time also this error will occur.
那个时候也会出现这个错误。
