How to use IN clause with Mybatis Annotation inside SQL Provider

Question

I have seen links pointing to the solution but most relevant is How to use Annotations with iBatis (myBatis) for an IN query? but even this doesn't provide solution for Oracle driver.

public String getEmployees(Map<String, Object> params){

//Value hold by params    params={empId={123,345,667,888}}

StringBuilder sql=new StringBuilder();
sql.append("Select * from employee where emp_id in (#{empId}"); 

Mybatis substitute the values from the params. But when the value is substituted the query becomes some thing below.

Select * from employee where emp_id in ('123,345,667,888');

Which is a invalid Query as mybatis has added the single quotes in the query.

How should I handle this issue for a fix? I cannot concatenate the values because to prevent SQL Injection.

Answer 1

The accepted answer in How to use Annotations with iBatis (myBatis) for an IN query? gives a solution working for postgres, a string representation of the list/array is passed and converted by the database. Oracle does not support this. The list must be iterared to bind every value.

In my opinion, what you are looking for is Dynamic SQL, explained by LordOfThePigs in the next answer. Adapted to this case would be:

@Select({"<script>",
         "SELECT *", 
         "FROM employee",
         "WHERE emp_id IN", 
           "<foreach item='emp' collection='empId'",
             "open='(' separator=', ' close=')'>",
             "#{emp}",
           "</foreach>",
         "</script>"}) 
List<Employee selectEmployees(Map<String, Object> params);

@SelectProvider provides SQL string built in Java. But binding parameters become much more tedious.