CODEWITHSUNDEEP
javasqlprepared-statement
Apurv
Apurv

Reputation: 3753

Prepared statement with dynamic where clause

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 ...
  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 (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) ?

Upvotes: 7

Views: 25399

Answers (3)

John Joseph
John Joseph

Reputation: 1187

This is easy to do without any complex or expensive logic, in a single line...

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

Also assuming that a zero-length string will be provided when a filter is not required.

Then, your Select statement is simply:

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

That's all there is to it! No complex or expensive logic.

Upvotes: 4

Ajinkya
Ajinkya

Reputation: 22720

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.

Upvotes: 4

duffymo
duffymo

Reputation: 309008

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.

Upvotes: 4

Related Questions