SQL prepared statement, how to choose through several possible menu options?

Question

So, I have 4 menu selections (product, location, course type and category), all of which can be null (programmed using JSF, but that doesn’t matter for this question, since this is an SQL question).

Selecting the menu will send the bean-driven variable that the user selected, and using the prepared search operator in the database table, using the information from the menu that the user selected (if any).

If the user leaves the null menu item, he should look for everything.

If the user leaves 1 or 2 or 3 menu items with information and the other zero, he should search accordingly.

My problem is how can I do this without a bunch of if / then statements in the bean attached to the corresponding sql statement for each?

Or is there one better sql statement that I can do to do all this?

I use a prepared statement in Java.

I tried this:

if (product != null && location != null && courseType != null && category != null) { pstmt = conn.prepareStatement("select * FROM Courses WHERE " + "product = ? " + "and location = ? " + "and courseType = ? " + "and category = ?"); pstmt.setString(1, product); pstmt.setString(2, location); pstmt.setString(3, courseType); pstmt.setString(4, category); } else if (product == null && location != null && courseType != null && category != null) { pstmt = conn.prepareStatement("select * FROM Courses WHERE " + "location = ? " + "and courseType = ? " + "and category = ?"); pstmt.setString(1, location); pstmt.setString(2, courseType); pstmt.setString(3, category); } 

etc., but I would have to do it as 16 times for each case 1, which is zero, and not others? There should be a smarter way (either using 1 sql statement, or just a few java if / then statements?)

UPDATE thanks to Luigi Mendoza! My code works as follows:

 pstmt = conn.prepareStatement("select * FROM Courses WHERE " + "(product = ? or ? is null) " + "and (location = ? or ? is null) " + "and (courseType = ? or ? is null)" + "and (category = ? or ? is null)"); pstmt.setString(1, product); pstmt.setString(2, product); pstmt.setString(3, location); pstmt.setString(4, location); pstmt.setString(5, courseType); pstmt.setString(6, courseType); pstmt.setString(7, category); pstmt.setString(8, category); rset = pstmt.executeQuery(); 

Update yes, I had to use = "" instead of null for another mysql database (maybe different versions or something else)

Answer 1

I assume that you have a default value for your NULL selected values ​​(since I don’t know of another way to do this using PreparedStatement ) as '0' (as an example).

Knowing this, I prefer to use this type of SQL:

 SELECT * FROM Courses WHERE (product = ? or ? = '0') and (location = ? or ? = '0') and (courseType = ? or ? = '0') and (category = ? or ? = '0') 

With this approach, you use the use of IF and let the database handle the work. The only bad point about this is that you have to set each variable twice (there is nothing in this life).

---

EDIT: I did a test based on your requirements. First, the SQL table and contents:

 create table pruebaMultiSelect (id int primary key auto_increment, nombre varchar(50), tipo varchar(10), categoria varchar(10)); insert into pruebaMultiSelect values (null, 'Luiggi', 'A', 'X'); insert into pruebaMultiSelect values (null, 'Thomas', 'A', 'Y'); insert into pruebaMultiSelect values (null, 'Jose', 'B', 'X'); insert into pruebaMultiSelect values (null, 'Trina', 'B', 'Y'); 

Now the corresponding Java code:

 public class DBTest { public void testPreparedStatement(String p1, String p2) throws SQLException { Connection con = null; PreparedStatement pstmt = null; ResultSet rs = null; try { con = getConnection(); pstmt = con.prepareStatement("SELECT * FROM pruebaMultiSelect WHERE (tipo = ? OR ? = 'EMPTY') " + "AND (categoria = ? OR ? = 'EMPTY')"); pstmt.setString(1, p1); pstmt.setString(2, p1); pstmt.setString(3, p2); pstmt.setString(4, p2); rs = pstmt.executeQuery(); while (rs.next()) { System.out.printf("%5d %15s\n", rs.getInt(1), rs.getString(2)); } } catch(ClassNotFoundException e) { e.printStackTrace(); } finally { rs.close(); pstmt.close(); con.close(); } } public Connection getConnection() throws SQLException, ClassNotFoundException { Class.forName("com.mysql.jdbc.Driver"); return DriverManager.getConnection("jdbc:mysql://localhost:3306/luiggi_test", "user", "password"); } public static void main(String[] args) throws SQLException { //this will return all the data String p1 = "EMPTY"; String p2 = "EMPTY"; new DBTest().testPreparedStatement(p1, p2); } } 

Tested using MySQL 5.1.18.

Answer 2

I tend to build dynamic queries using helper methods:

 StringBuilder query = new StringBuidler(); query.append("SELECT foo, bar FROM baz"); query.append(buildProductClause(product)); query.append(buildLocationClause(location)); query.append(buildCourseTypeClause(courseType)); // etc... 

You can use a member field to build an array of arguments if you want to use a prepared statement.