带有WHERE的Oracle JDBC select返回0 [英] Oracle JDBC select with WHERE return 0
问题描述
类似的问题: JDBC的奇怪问题,select返回null 但是人们并没有要求.
Similar question to: Strange problem with JDBC, select returns null but people didn't ask for this.
我的代码:
public int myMethod(String day) throws SQLException{
String sql = "Select count(*) from MyTable WHERE someColumn = " + day;
Connection connection = ConnFactory.get();
PreparedStatement prepareStatement = null;
ResultSet resultSet = null;
int ret = -1;
try{
prepareStatement = connection.prepareStatement(sql);
resultSet = prepareStatement.executeQuery(sql);
if(resultSet.next()){
ret = resultSet.getInt(1);
}
}
catch(SQLException sqle){
// closing statement & ResultSet, log and throw exception
}
finally{
// closing statement & ResultSet
}
ConnFactory.kill(connection);
return ret;
}
此代码始终返回0.我尝试在执行之前记录sql,并尝试在SQLdeveloper中运行它并获得正确的值(超过100).
当我删除WHERE时,sql = "Select count(*) from MyTable
查询返回表中所有行的编号.
我将Oracle 10g与ojdbc-14.jar(来自Maven Repo的最新版本)和Java 6配合使用.
This code always return 0. I try to log sql before execution and try to run it in SQLdeveloper and get correct value (over 100).
When I remove WHERE, sql = "Select count(*) from MyTable
query return number of all rows in table.
I use Oracle 10g with ojdbc-14.jar (last version from maven repo) and Java 6.
推荐答案
day
的引用不正确,我建议使用一条准备好的语句 like 一条准备好的语句,如下所示:
day
has not been quoted correctly, I would suggest using a prepared statement like a prepared statement as follows:
...
try {
prepareStatement = connection.prepareStatement("Select count(*) from MyTable WHERE someColumn = ?");
prepareStatement.setString(1,day);
...
与以下相同:
sql = "Select count(*) from MyTable WHERE someColumn = '" + day + "'";
与后者相比有几个优点(主要是安全性和性能).参见:
with several advantages over the latter (mainly security and performance). See:
http://java.sun.com/docs /books/tutorial/jdbc/basics/prepared.html
这篇关于带有WHERE的Oracle JDBC select返回0的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!