Oracle JDBC selection returning WHERE 0
Similar question: Strange JDBC issue, select returns null but people didn't ask for that.
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 returns 0. I am trying to run sql before executing and try to run it in SQLdeveloper and get the correct value (over 100). When I remove WHERE, the sql = "Select count(*) from MyTable
query returns the number of all rows in the table. I am using Oracle 10g with ojdbc-14.jar (latest version from maven repo) and Java 6.
source to share
day
was not specified correctly, I would suggest using a prepared statement like a prepared statement like this:
...
try {
prepareStatement = connection.prepareStatement("Select count(*) from MyTable WHERE someColumn = ?");
prepareStatement.setString(1,day);
...
matches:
sql = "Select count(*) from MyTable WHERE someColumn = '" + day + "'";
with several advantages over the latter (mainly security and performance). Cm:
http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html
source to share
First of all, using sql like this is not recommended. Because it leads to SQL injection.
In the future, try using as below and use PreparedStatement to do
String sql = "Select count(*) from MyTable WHERE someColumn = ? "
For your solution you tried
String sql = "Select count(*) from MyTable WHERE someColumn = '" + day + "'";
source to share