Prepared statement with dynamic where clause

Question

I have a search page with multiple search criteria

1. Employee Name
2. Employee Id
3. Date of joining
4. Department

etc

User can provide one or more search criteria. I need to query database to get the search results.

Using plain JDBC, there are two options to achieve this.

1. Prepare SQL query by appending search criteria provided by user.

ex:

String selectClause = "SELECT * FROM EMPLOYEES WHERE ";
String whereClause = "";
if(StringUtils.isNotBlank(empName)){
    if(whereClause.length > 0){
        whereClause += " AND ";
    }
    selectQuery += " EMP_NAME = " + empName;
}
if(StringUtils.isNotBlank(empID)){
    if(whereClause.length > 0){
        whereClause += " AND ";
    }
    selectQuery += " EMP_ID = " + empID;
}
//... and so on ...

2. Using preparestatement

ex:

String query = "SELECT * FROM EMPLOYEES WHERE EMP_NAME = ? AND EMP_ID = ? DATE_OF_JOINING = ? AND DEPARTMENT = ?";

This answer explains that like ex 1 above, ex2 can be modified, something like below

String selectClause = "SELECT * FROM EMPLOYEES WHERE ";
String whereClause = "";
if(StringUtils.isNotBlank(empName)){
    if(whereClause.length > 0){
        whereClause += " AND ";
    }
    selectQuery += " EMP_NAME = ?";
}
if(StringUtils.isNotBlank(empID)){
    if(whereClause.length > 0){
        whereClause += " AND ";
    }
    selectQuery += " EMP_ID = ?";
}
//... and so on ...

Then carefully (keeping parameter index in mind) the input needs to set to the prepared statement. This doesn't sounds to be a very ideal solution.

Is there a way to do this in an elegant way (without ORM frameworks) ?

Answer 1

I wouldn't like using a StringBuilder to dynamically create a query each and every time, especially when the number of meaningful combinations is countable and finite.

I'd always prefer static Strings. Yes, you have to type them in, but you'll do that once. I'd rather do that than pay the price in complexity and at runtime.

Answer 2

In such conditions I prefer adding 1=1 in where clause so that you dont have to keep track of where to insert AND.

String selectClause = "SELECT * FROM EMPLOYEES WHERE 1=1 ";
if(StringUtils.isNotBlank(empName)){
   selectQuery += "AND EMP_NAME = " + empName;
}
if(StringUtils.isNotBlank(empID)){
   selectQuery += "AND EMP_ID = " + empID;
}
//... and so on ...

Related question.