When querying a table with BOOLEAN columns, the PreparedStatement.setBoolean() method does not act as expected. No matter what values are passed to it, it always returns 0 matches.
The following code illustrates the problem:
Connection con = ....
PreparedStatement stmt = con.prepareStatement("CREATE TABLE FOO (BAR BOOLEAN)");
stmt.execute();
stmt = con.prepareStatement("INSERT INTO FOO (BAR)VALUES(?)");
stmt.setBoolean(1,false);
stmt.execute();
stmt = con.prepareStatement("INSERT INTO FOO (BAR)VALUES(?)");
stmt.setBoolean(1,false);
stmt.execute();
stmt = con.prepareStatement("INSERT INTO FOO (BAR)VALUES(?)");
stmt.setBoolean(1,true);
stmt.execute();
stmt = con.prepareStatement("INSERT INTO FOO (BAR)VALUES(?)");
stmt.setBoolean(1,true);
stmt.execute();
stmt = con.prepareStatement("INSERT INTO FOO (BAR)VALUES(?)");
stmt.setBoolean(1,true);
stmt.execute();
stmt = con.prepareStatement("SELECT COUNT(*) FROM FOO WHERE BAR = ?");
stmt.setBoolean(1,false);
ResultSet res = stmt.executeQuery();
res.next();
//should print '2', but prints '0'
System.out.println(res.getInt(1));
stmt = con.prepareStatement("SELECT COUNT(*) FROM FOO WHERE BAR = ?");
stmt.setBoolean(1,true);
res = stmt.executeQuery();
res.next();
//should print '3', but prints '0'
System.out.println(res.getInt(1));
stmt = con.prepareStatement("SELECT COUNT(*) FROM FOO WHERE BAR = ?");
stmt.setInt(1,0);
res = stmt.executeQuery();
res.next();
//correctly prints '2'
System.out.println(res.getInt(1));
stmt = con.prepareStatement("SELECT COUNT(*) FROM FOO WHERE BAR = ?");
stmt.setInt(1,1);
res = stmt.executeQuery();
res.next();
//correctly prints '3'
System.out.println(res.getInt(1));
A quick workaround would be to simply have PreparedStatement.setBoolean call setInt:
public void setBoolean(int index, boolean value)
{
setInt(index,value?1:0);
}
When querying a table with BOOLEAN columns, the PreparedStatement.setBoolean() method does not act as expected. No matter what values are passed to it, it always returns 0 matches.
The following code illustrates the problem:
A quick workaround would be to simply have PreparedStatement.setBoolean call setInt: