how can i dynamically modify an SQL query in Java?

Question

i'm making a Java program that contains a search engine feature to show the records from the database according to different criterias selected by the user out of comboboxes. I need to be able to modify the WHERE conditions of an SQL query according to what the user has selected. If he hasn't selected any value from the comboboxes then the default would be WHERE=1, if he has selected criteria from one combobox then override the variable so the 1 change it to WHERE state=oklahoma for example, and he selects 2 or more just keep concatenating them: WHERE state=oklahoma AND gender=male, and so on with as many comboboxes he sets.

i've tried to do this but i've read there are libraries that can make this easier but i don't know any, if this can be done with a library please name it and show the code to do it please

Google PreparedStatement - that's the way to go (safer both from security as well as from data binding perspectives). — Nir Alfasi
– Nir Alfasi, Commented May 3, 2015 at 22:11

MChaker · Accepted Answer · 2015-05-03 23:09:12Z

3

If the combo box number i is selected then isSelected[i]=true.
content[i] is the value of the combo box number i.

First Solution

PreparedStatement ps = (PreparedStatement) con.prepareStatement
                       (“SELECT username, password FROM users WHERE
                            state LIKE ? AND gender LIKE ?″); // AND ...



for(int i=0; i<n; i++) // n number of combo boxes
   if (!isSelected[i])
      ps.setString(i+1,"%");
    else
      ps.setString(i+1,content[i]);
 ResultSet rs=ps.executeQuery();

Second solution

PreparedStatement ps = (PreparedStatement) con.prepareStatement
                   (“SELECT username, password FROM users WHERE (? OR
                        state=?) AND (? OR gender=?)″); // AND ...

for(int i=0; i<n; i++) {// n number of combo boxes
    ps.setString(2*i+1,content[i]);
    ps.setBoolean(2*i+2,isSelected[i]);
 }
 ResultSet rs=ps.executeQuery();

edited May 3, 2015 at 23:09

answered May 3, 2015 at 22:23

MChaker

2,6572 gold badges24 silver badges38 bronze badges

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

6 Comments

Andres Miguel Over a year ago

i have 7 comboboxes and so my statement needs to change from a WHERE 1 that will just retrieve anything to a WHERE that includes 7 different conditions. With your example how can i input a WHERE 1? (nothing selected on the comboboxes)

maraca Over a year ago

@AndresMiguel you have to use WHERE state LIKE ? and then put in %

Andres Miguel Over a year ago

@MChaker i tested it in phpmyadmin and it gives me the results i need, now i'll write it into java. Thanks

maraca Over a year ago

You are welcome (+1 already). I forgot to mention, that you have to escape _ (matches 1 character) and % now and maybe also double '(?) if it appears in the state or somewhere else. @AndresMiguel you can accept the answer if it works ;-)

Andres Miguel Over a year ago

@MChaker i need more info to understand how it works, what does the setBoolean do in the second solution? how does it pass the value of the combobox to the loop? and please show where are the comboboxes called into the loop (there's just the number of the comboboxes but they aren't declared or called). maraca I'm waiting for more clarification because MChaker edited a new solution and i don't understand it well

|

Collectives™ on Stack Overflow

how can i dynamically modify an SQL query in Java?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related