#58 Filtering on NULL values doesn't work

[Anonymous]

Filtering on NULL values is not implemented correctly in the table manager. This is the code generated:

protected int fillWhere(StringBuilder sqlWhere, ReqDetailBean bean, int searchType)
{
....
if (bean.isExpiryDateModified()) {
_dirtyCount ++;
if (bean.getExpiryDate() == null) {
sqlWhere.append((sqlWhere.length() == 0) ? " " : " AND ").append("EXPIRY_DATE IS NULL");
} else {
sqlWhere.append((sqlWhere.length() == 0) ? " " : " AND ").append("EXPIRY_DATE = ?");
}
}
....
}

protected int fillPreparedStatement(PreparedStatement ps, ReqDetailBean bean, int searchType) throws DAOException
{
....
if (bean.isExpiryDateModified())
{
// System.out.println("Setting for " + _dirtyCount + " [" + bean.getExpiryDate() + "]");
if (bean.getExpiryDate() == null) {
// *** THIS WILL THROW AN EXCEPTION ***
ps.setNull(++_dirtyCount, Types.DATE); } else { ps.setDate(++_dirtyCount, new java.sql.Date(bean.getExpiryDate().getTime())); }
}
....
}

If the ExpiryDate is NULL the SQL code generated in fillWhere is

EXPIRY_DATE IS NULL

and in fillPreparedStatement a NULL value parameter is set for the ExpiryDate position with

ps.setNull(++_dirtyCount, Types.DATE);

But there's no parameter for the column.

The "EXPIRY_DATE IS NULL" should be changed to "? IS NULL" or the setNull() call should be removed.

[Alain Fagot Béarez]

The same fillPreparedStatement() method is used for both selection and update situations.

While the currently implemented code is fully functional for updates and creations, it should omit the setNull() call when used after the fillWhere() method was called, meaning there will be a selection or deletion.