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

Mayank Jain

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.

Lukasz Szozda

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

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]

編集
0

コメントを追加

0

関連記事

分類Dev

Symfony Optional constraints not working as expected

分類Dev

LIKE query not working as expected

分類Dev

Code generation with parameter is not working as expected

分類Dev

SQLite query > operator not working

分類Dev

Firestore "where" query not working as expected

分類Dev

Yii update query not working as expected

分類Dev

Query not working as expected in stored procedure

分類Dev

Null parameter not working with Access and OLE

分類Dev

Quick Sort not working as expected in java

分類Dev

Why is a WHERE clause to exclude rows in SQLite not working as expected?

分類Dev

Error java.lang.AssertionError: expected: null<null> but was: java.lang.String<null> what does it mean?

分類Dev

Optional parameter on Angular Directive

分類Dev

Regex to match optional parameter

分類Dev

Writing a function with an optional parameter

分類Dev

Writing a function with an optional parameter

分類Dev

PHP mysqli_query() expects parameter 1 to be mysqli, null given in

分類Dev

mysqli_query() expects parameter 1 to be mysqli, null given :(

分類Dev

SQLite NOT NULL

分類Dev

AJAX call is not working as expected

分類Dev

jQuery .when().then() not working as expected

分類Dev

.vimrc file not working as expected

分類Dev

*ngIf not working as expected with observable

分類Dev

ItemIsAutoTristate flag not working as expected

分類Dev

KeyboardAvoidingView not working as expected on IOS

分類Dev

PowerShell variables not working as expected

分類Dev

XPath logical 'and' not working as expected?

分類Dev

CancellationTokenSource not working as expected on a TaskCompletionSource

分類Dev

string formatting not working as expected

分類Dev

Why is .on() Not working as expected in jquery?

Related 関連記事

ホットタグ

アーカイブ