0

Short: My problem is writing a method that generates a prepared statement coresponding to the number of checked checkboxes and filling the "?".

Hey, I got a text search searching for corresponding titles and authors that looks like this.

String query = "SELECT * FROM xyz WHERE title LIKE ? OR author LIKE ?";
PreparedStatement ps = con.prepareStatement(query);
ps.setString(1, "%" + searchtext + "%");
ps.setString(2, "%" + searchtext + "%");

Works just fine. I now want to implement a checkbox based search. Every database entry has a genre, topic and use case. Each of that 3 has 8-9 possibilities.

As a result i want to show all database entries whose genre is one of the checked ones, whose topic is one of the checked ones and whose use case is one of the checked ones.

Checkboxes

What i want might be for example (genre1 or genre2) and topic7 and (usecase5 or usecase9)

As only checked checkboxes return a value to the servlet I dont't know how to write a prepared statement that fits all possible scenarios. I believe I need to count how many checkboxes return a value, get their values and dynamically generate a prepared statement with as many "?" as the number of checked checkboxes. Each ? has to be filled with ps.setString().

Would you group the checkboxes in the same group by using the same name or would you leave every checkbox alone?

Jartyyy
  • 13
  • 5
  • Do you need to use LIKE since the values are coming from the checkbox? Would it be better to use an IN statement? Then you could populate all the conditions as done here: https://stackoverflow.com/questions/3107044/preparedstatement-with-list-of-parameters-in-a-in-clause – Jason Warner Mar 11 '20 at 14:25
  • LIKE was used in my text search. In the tag search IN is better, you are right :) In the end I want something like SELECT * FROM xyz WHERE genre IN ('1','2') AND topic IN ('4','8'). The problem is that i don't know how to create these statements dynamically with prepared statements. According to how many checkboxes are checked I first need "SELECT * FROM xyz WHERE genre IN ('?'. '?'. '?') [...] and then fill the question marks with the values of the checkboxes with a loop. I cant figure out how to do the iteration. The solution in the link doens't seem to solve that. – Jartyyy Mar 11 '20 at 14:42
  • There are at least two valid answers on that question with code snippets, and three links to duplicate questions that each have detailed answers and snippets. – Jason Warner Mar 11 '20 at 15:23

0 Answers0