title: SQL prepared statement how to select via multiple possible menu selections?
body: <p>So I have 4 menu selections (product, location, courseType, and category), all of which can be null (programmed using JSF but that should be irrelevant to this question, as it is an SQL question). </p>
  
  <p>The menu selection will send the managed bean the variable that the user selected, and using a prepared statement search a database table using the information from the menu that the user selected (if any). </p>
  
  <p>If the user leaves the menu item null, it should search everything. </p>
  
  <p>If the user leaves 1 or 2 or 3 of the menu items with information, and the other one null, it should search accordingly. </p>
  
  <p>My problem is how do I do this without a bunch of if/then statements in the bean attached to an the appropriate sql statement for every one?</p>
  
  <p>Or is there one better sql statement I can make that does all of this?</p>
  
  <p>I am using a prepared statement in Java.</p>
  
  <p>I tried this:</p>
  
  <pre><code>if (product != null &amp;&amp; location != null &amp;&amp; courseType != null &amp;&amp; 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 &amp;&amp; location != null &amp;&amp; courseType != null &amp;&amp; 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);
          } 
  </code></pre>
  
  <p>etc etc but I would have to do this like 16 times for each case of 1 being null and not the others?
  There must be a smarter way (either by using 1 sql statement or only a few java if/then statements?)</p>
  
  <p>UPDATE thanks to Luiggi Mendoza! My code works like this:</p>
  
  <pre><code>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();
  </code></pre>
  
  <p>Update yeah I had to use = "" instead of is null for a different mysql database (maybe different versions or something)</p>