How to handle NULL and regular values ​​without using different prepared statements?

Question

Consider this simple method:

public ResultSet getByOwnerId(final Connection connection, final Integer id) throws SQLException { PreparedStatement statement = connection.prepareStatement("SELECT * FROM MyTable WHERE MyColumn = ?"); statement.setObject(1, id); return statement.executeQuery(); } 

It is assumed that the sample method selects everything from the table where the column value corresponds, which should be simple. The ugly detail is that passing NULL for the identifier will result in an empty ResultSet, no matter how many rows there are in the database, since SQL defines NULL as not euqaling anyting, even NULL. The only way to select the lines that I know of is to use another where clause:

 SELECT * FROM MyTable WHERE MyColumn IS NULL 

Unfortunately, the only way to make the method work correctly in all cases, apparently, is to have two completely different execution patterns, one for the case when the id argument is null and the other for regular values.

This is very ugly, and when you have a few columns that can be reset, you can quickly become very dirty. How can I handle NULL and normal values ​​using the same code / code statement?

Answer 1

I have not tried this, but the way to send zeros in readyStatements is to use the setNull () parameter

  PreparedStatement statement = connection.prepareStatement("SELECT * FROM MyTable WHERE MyColumn = ?"); if ( id == null) statement.setNull(1, java.sql.Types.INTEGER); else statement.setObject(1, id); return statement.executeQuery(); 

EDIT : Thanks for the answers from @Gabe and @Vache. It doesn't seem like there is a simple way to do this otherwise than to use a longer approach to dynamically create a prepared state.

Something like this should work -

 String sql = " SELECT * FROM MyTable WHERE " + (id==null)?"MyColumn is null":"MyColumn = ?" ; PreparedStatement statement = connection.prepareStatement(sql); if ( id != null) statement.setObject(1, id); return statement.executeQuery(); 

Answer 2

You can use this query:

 select * from MyTable where case when ?1 is null then MyColumn is null else MyColumn = ?1 

But it repeats the same parameter, so I don’t know if the syntax (? 1) will be suggested.