Geek asks and answers

Prepared statement with dynamic where clause

I have a search page with multiple search criteria

  • Employee Name
  • Employee id
  • Date of joining
  • The Department

etc.

A user may provide one or more search criteria. I need to query the database to get the search results.

Using simple JDBC, there are two options for this.

  • Prepare the SQL query by adding the search criteria provided by the user.

Example:

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 ...
  1. 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 (taking into account the parameter index), the input should be set to the prepared statement. This does not seem to be the ideal solution.

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

+5
3

StringBuilder , .

. , , . , .

+3

1=1 where where , , 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 ...

question.

+5

This is easy to do without complex or expensive logic in one line ...

Assuming your three variables are @name, @surname and @gender.

It is also assumed that a zero-length string will be provided when a filter is not required.

Then your Select statement is simple:

    select * from table_name where (name = @name or @name = '') and (surname = @surname or @surname = '') and (gender = @gender or @gender = '')

That's all! No complicated or expensive logic.

+2
source

More articles:


All Articles