Oracle JDBC select with WHERE return 0

问题:

Similar question to: Strange problem with JDBC, select returns null but people didn't ask for this.

My code:

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;
}

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.

回答1:

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

is the same as:

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

回答2:

First of all using sql like this is not advisable. Because it leads to SQL injection.

In the future try using like below and use PreparedStatement to execute

String sql = "Select count(*) from MyTable WHERE someColumn = ? "

For your solution did you try

String sql = "Select count(*) from MyTable WHERE someColumn = '" + day + "'";

回答3:

karim79 is good answer, you forgot add apostrophe signs in your "day" value

String sql = "Select count(*) from MyTable WHERE someColumn = '" + day + "'";