SQL request - SELECT with multiple empty values

Question

I have a database with multiple columns. Now i have a Java program where i can search for different rows in my database. But i need help for the SQL SELECT request. I have some texfields and some checkboxes. Now i want if the textfields are empty, or "any" is selected in one of the checkboxes , the select request should select ALL from this column.

Here is my request right now:

resultSet = sqlStatement.executeQuery("SELECT * FROM Messgerate WHERE COLUMN1 LIKE '%"+sColumn1+"%'" +
"AND COLUMN2 LIKE '%"+sColumn2+"%'" +
"AND COLUMN3 LIKE '%"+sColumn3+"%'" +
"AND COLUMN4 LIKE '%"+sColumn4+"%'" +
"AND COLUMN5 LIKE '%"+sColumn5+"%'" +
"AND COLUMN6 LIKE '%"+sColumn6+"%'" +"");

This request ist working if i type in something in all of the textfields and checkboxes.

Can someone help me please?

You really should read up on proper use of PreparedStatement: docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html — Keppil
– Keppil, Commented Dec 10, 2013 at 9:20

Linga · Accepted Answer · 2013-12-10 09:22:23Z

1

Can you replace AND with OR. It ll solve your issue. Because the AND operator displays a record if both the first condition AND the second condition are true.

The OR operator displays a record if either the first condition OR the second condition is true.

"SELECT * FROM Messgerate WHERE COLUMN1 LIKE '%"+sColumn1+"%'" +
"OR COLUMN2 LIKE '%"+sColumn2+"%'"
"OR COLUMN3 LIKE '%"+sColumn3+"%'" +
"OR COLUMN4 LIKE '%"+sColumn4+"%'" +
"OR COLUMN5 LIKE '%"+sColumn5+"%'" +
"OR COLUMN6 LIKE '%"+sColumn6+"%'" +"");

answered Dec 10, 2013 at 9:22

Linga

10.6k10 gold badges57 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mike_NotGuilty Over a year ago

but if i use OR , just one of all statements must me true , right? but i want all statements must be true

Linga Over a year ago

Yes, if you want all statements must be true, use AND and check Which checkboxes are selected and pass those values

Linga Over a year ago

If AND, all the column should be matched

Mike_NotGuilty Over a year ago

is there no easier way? Because the code will be huge if i have to write so many requests

Linga Over a year ago

You want if the textfields are empty, or "any" is selected in one of the checkboxes. Just check it

|

Ganesh Jadhav · Accepted Answer · 2013-12-10 09:22:48Z

0

You should pass an empty string when Any is selected in the dropdown. And then you can use this:

resultSet = sqlStatement.executeQuery("SELECT * FROM Messgerate WHERE COLUMN1 LIKE '%"+sColumn1+"%'" +
"OR COLUMN2 LIKE '%"+sColumn2+"%'" +
"OR COLUMN3 LIKE '%"+sColumn3+"%'" +
"OR COLUMN4 LIKE '%"+sColumn4+"%'" +
"OR COLUMN5 LIKE '%"+sColumn5+"%'" +
"OR COLUMN6 LIKE '%"+sColumn6+"%'" +"");

answered Dec 10, 2013 at 9:22

Ganesh Jadhav

2,8481 gold badge22 silver badges34 bronze badges

Comments

Hitesh · Accepted Answer · 2013-12-10 09:26:43Z

0

select * from messgerate where case when isnull(sColumn1,'') = '' then '' when isnull(sColumn1,'') = 'any' then '' else COLUMN1 end LIKE '%"+sColumn1+"%'"

Repeat the case statement for the rest of the columns

answered Dec 10, 2013 at 9:26

Hitesh

3,5068 gold badges43 silver badges61 bronze badges

Collectives™ on Stack Overflow

SQL request - SELECT with multiple empty values

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related