Optional Parameter query with IS NULL not working as expected in Java Sqlite

Question

I have this table PropertyDetails which contains a column "bedrooms". In my GUI, I have a filters Panel which allows user to select the number of bedroom(s) (1,2,3,4,5,6,7,8,9,10) and hence display the results with the filter(s).

I am using parameterized query to pass the number of bedrooms as a filter which can be null. My query doesn't supply the desired result when I use "IS NULL" but shows accurate results without it. When I use only this code, it works fine and delivers the desired output:

where
(PropertyDetails.bedrooms=?1)
 OR 
(PropertyDetails.bedrooms=?2)

I have already tried using IN() clause but it doesn't allow passing in a parameter (as researched on internet). Tried combinations of AND , OR .

This is the query I want to run since the parameters can have null value (might be unchecked). But it only returns expected output only when 1bhk and 2bhk both are checked And shows all results when only any one is ticked.

PreparedStatement stmt=conn.prepareStatement(
"Select * from PropertyDetails
 where
(PropertyDetails.bedrooms=?1 OR ?1 IS NULL)
 OR 
(PropertyDetails.bedrooms=?2 OR ?2 IS NULL)");
        stmt.setString(1,(bhk1));
        stmt.setString(2, bhk2);
        rs=stmt.executeQuery();

Initially,

bhk1=null;
bhk2=null;
//If the user checks the 1bhk box, bhk1 string is set to 1 and when the user checks 2bhk box, bhk2 string is set to 2 i.e : 
if(1bhkbox.isSelected())
  bhk1="1";
if(2bhkbox.isSelected())
  bhk2="2";

If no filter is selected I want to show all the records. If a single filter is selected I want to show all records with that filter (bhk) value. If multiple filters are selected (ex : 1bhk and 2 bhk both are checked) , I want to show all records for which PropertyDetails.Bedrooms value is either 1 or 2. The user can select one or more than one bedrooms option or none. Therefore, it can be NULL, single value or multiple values.

Answer 1

You should rewrite the query:

SELECT * FROM PropertyDetails WHERE bedrooms IN(?1, ?2) OR (?1 IS NULL AND ?2 IS NULL)

Explanation:

bedrooms IN(?1, ?2)
-- if any parameter is provided then filled one are used

OR (?1 IS NULL AND ?2 IS NULL)
-- none of parameter is provided, show all values