How to iterate List in MyBatis

Question

iBatis to MyBatis Migration:

Need Help for MyBatis foreach logic, because the Map contains Value as ArrayList. The below java code is the logic:

employeeRequest.put("ID", employeeId);
Map <String,ArrayList<String> employeeRequest = new HashMap<String, ArrayList<String>>();
Set<String> employeeSet = new HashSet<String>();
 for(Employee employee: employeeList) {
   String name = employee.getName();
   String value = employee.getValue(); 
  if("EMPLOYEE".equalsIgnoreCase(name) {
    employeeSet.add(value)
  }
}
if(!employeeSet.isEmpty()) {
  employeeRequest.put("EMPLOYEE", new ArrayList<String>(employeeSet))
}

iBatis SQL: My Previous code I am using iBatis which has the following query

<select id="getEmployeeName" resultclass="java.util.HashMap" parameterClass="java.util.Map">
SELECT EMP.EMPNAM NAME FROM EMPLOYEE EMP
WHERE EMP.ID = #ID#
<isNotEmpty property="EMPLOYEE" prepend="AND">
 <iterate property="EMPLOYEE" conjunction="AND">
  EMP.EMPNAM != #EMPLOYEE[]#
 <iterate>
</isNotEmpty>
</select>

MyBatis SQL: Now I am migrating to MyBatis, so formatted the query as below

<select id="getEmployeeName" resultclass="java.util.HashMap" parameterClass="java.util.Map">
SELECT EMP.EMPNAM NAME FROM EMPLOYEE EMP
WHERE EMP.ID = #{ID}#
<if test="EMPLOYEE !=null and EMPLOYEE>0">
 <foreach collection="EMPLOYEE" index="index" item="item" separator="AND">
  EMP.EMP_ID != ${item}
 </foreach>
</if>
</select>

Could any one of you please help me with the correct query for the above java code logic.

Answer 1

Missing spaces around separator value: AND instead of just AND.

For parameters use #{param} to bind parameter instead of ${param} that just concatenates values to the SQL string. That does not prevent from working but that is very bad.

!= is not standard SQL and will not work for every DB vendor (although it might do with the one you are using), unlike NOT column = value,

<foreach collection="EMPLOYEE" index="index" item="item" separator=" AND ">
  NOT EMP.EMP_ID = #{item}
</foreach>

furthermore better use IN:

  EMP.EMP_ID NOT IN (<foreach collection="EMPLOYEE" index="index" item="item" separator=", ">
  #{item}
</foreach>)