How to simplify the use of too many IF statements when finding specific cases

Question

I have code which a user will select different perameters from 5 dropdown menus, these can be: "None", 'Name', 'Manager', 'AccessLevel', 'Inhabitant' and 'Location' these will be matched with jtextfields that are searched to get the inputed value. However to get these values i am basically do alot of if statements to determine the combination of values e.g. Name, Manager and Location together will give the SQL statement: query = "SELECT * FROM propertyInfo WHERE propertyName = ? AND propertyManager = ? AND propertyLocation = ?";

this leads to a convoluted mess of if statements, is there any way to simplify it?

it works however to actually create it i would have to do hundreds of lines for different combinations and it will be hard to expand later. however this is some of my current code:

`if (searchingForName == true) {
          if (searchingForManager == true) {
              if (searchingForAccessLevel == true) { // final lvl 1
                   query = "SELECT * FROM propertyInfo WHERE propertyName = ? AND propertyManager = ? AND accessLevelRequired = ?"; 
                   query2 = "SELECT COUNT(*) AS recordCount FROM propertyInfo WHERE  propertyName = ? AND propertyManager = ? AND accessLevelRequired = ?"; 
              }else if ((searchType1.equals("Inhabitant") || searchType2.equals("Inhabitant") || searchType3.equals("Inhabitant"))) { // final lvl 2
                query = "SELECT * FROM propertyInfo WHERE propertyName = ? AND propertyManager = ? AND propertyInhabitant = ?"; 
                query2 = "SELECT COUNT(*) AS recordCount FROM propertyInfo WHERE  propertyName = ? AND propertyManager = ? AND propertyInhabitant = ?"; 
            }else if ((searchType1.equals("Location") || searchType2.equals("Location") || searchType3.equals("Location"))) { // finaly lvl 3
                query = "SELECT * FROM propertyInfo WHERE propertyName = ? AND propertyManager = ? AND propertyLocation = ?"; 
                query2 = "SELECT COUNT(*) AS recordCount FROM propertyInfo WHERE  propertyName = ? AND propertyManager = ? AND propertyLocation = ?"; 
            }
          }  else if ((searchType1.equals("AccessLevel") || searchType2.equals("AccessLevel") || searchType3.equals("AccessLevel"))) { // sub lvl 2
               if ((searchType1.equals("Inhabitant") || searchType2.equals("Inhabitant") || searchType3.equals("Inhabitant"))) { //final lvl 1
                query = "SELECT * FROM propertyInfo WHERE propertyName = ? AND accessLevelRequired = ? AND propertyInhabitant = ?"; 
                query2 = "SELECT COUNT(*) AS recordCount FROM propertyInfo WHERE  propertyName = ? AND accessLevelRequired = ? AND propertyInhabitant = ?"; 
            }else if ((searchType1.equals("Location") || searchType2.equals("Location") || searchType3.equals("Location"))) { // final lvl 2
                query = "SELECT * FROM propertyInfo WHERE propertyName = ? AND accessLevelRequired = ? AND propertyLocation = ?"; 
                query2 = "SELECT COUNT(*) AS recordCount FROM propertyInfo WHERE  propertyName = ? AND accessLevelRequired = ? AND propertyLocation = ?"; 
            }
          } else if ((searchType1.equals("Inhabitant") || searchType2.equals("Inhabitant") || searchType3.equals("Inhabitant"))) { // sub lvl 3
               if ((searchType1.equals("Location") || searchType2.equals("Location") || searchType3.equals("Location"))) { // final lvl 1
                query = "SELECT * FROM propertyInfo WHERE propertyName = ? AND propertyInhabitant = ? AND propertyLocation = ?"; 
                query2 = "SELECT COUNT(*) AS recordCount FROM propertyInfo WHERE  propertyName = ? AND propertyInhabitant = ? AND propertyLocation = ?"; 
          }
               
      //}
      }else if ((searchType1.equals("Manager") || searchType2.equals("Manager") || searchType3.equals("Manager"))) { // main lvl 2
          
          
          if ((searchType1.equals("AccessLevel") || searchType2.equals("AccessLevel") || searchType3.equals("AccessLevel"))) { // sub lvl 1
              
              
            if ((searchType1.equals("Inhabitant") || searchType2.equals("Inhabitant") || searchType3.equals("Inhabitant"))) {// final lvl 1
                   query = "SELECT * FROM propertyInfo WHERE propertyManager = ? AND accessLevelRequired = ? AND propertyInhabitant = ?"; 
                   query2 = "SELECT COUNT(*) AS recordCount FROM propertyInfo WHERE   propertyManager = ? AND accessLevelRequired = ? AND propertyInhabitant = ?"; 
                   
            }else if ((searchType1.equals("Location") || searchType2.equals("Location") || searchType3.equals("Location"))) { // final lvl 2
                query = "SELECT * FROM propertyInfo WHERE propertyManager = ? AND accessLevelRequired = ? AND propertyLocation = ?"; 
                query2 = "SELECT COUNT(*) AS recordCount FROM propertyInfo WHERE  propertyManager = ? AND accessLevelRequired = ? AND propertyLocation = ?"; 
            }
          }  else if ((searchType1.equals("Inhabitant") || searchType2.equals("Inhabitant") || searchType3.equals("Inhabitant"))) { // sub lvl 2
                if ((searchType1.equals("Location") || searchType2.equals("Location") || searchType3.equals("Location"))) { // final lvl 1
                query = "SELECT * FROM propertyInfo WHERE propertyManager = ? AND propertyInhabitant = ? AND propertyLocation = ?"; 
                query2 = "SELECT COUNT(*) AS recordCount FROM propertyInfo WHERE  propertyManager = ? AND propertyInhabitant = ? AND propertyLocation = ?"; 
            }
          }
        `  } 
      }

Answer 1

You can simplify this process by dynamically constructing the SQL query based on the selected parameters. Here's a more structured approach:

// Define the base query
String query = "SELECT * FROM propertyInfo WHERE 1 = 1";

// Initialize a list to store query parameters
List<Object> parameters = new ArrayList<>();

// Check if Name is selected
if (searchingForName) {
    query += " AND propertyName = ?";
    parameters.add(nameValue);
}

// Check if Manager is selected
if (searchingForManager) {
    query += " AND propertyManager = ?";
    parameters.add(managerValue);
}

// Check if AccessLevel is selected
if (searchingForAccessLevel) {
    query += " AND accessLevelRequired = ?";
    parameters.add(accessLevelValue);
}

// Check if Inhabitant is selected
if (searchingForInhabitant) {
    query += " AND propertyInhabitant = ?";
    parameters.add(inhabitantValue);
}

// Check if Location is selected
if (searchingForLocation) {
    query += " AND propertyLocation = ?";
    parameters.add(locationValue);
}

For each selected parameter (e.g., Name, Manager, etc.), you dynamically add a corresponding condition to the query string and add the parameter value to the parameters list. Finally, you can execute the query using a prepared statement with the parameters.

Have a good one!