Can I pass a List as a parameter to a MyBatis mapper?

Question

I'm trying to define a simple @Select annotation in MyBatis to get a collection of objects based on criteria defined by an IN clause. The SQL looks something like:

SELECT * FROM employees WHERE employeeID IN (1, 2, 3);

The list is generated dynamically, so I don't know how many parameters it will have. I'd like to just pass in a List of values, something like:

@Select("SELECT * FROM employees WHERE employeeID IN( #{employeeIds} )")
List<Employee> selectSpecificEmployees(@Param("employeeIds") List<Integer> employeeIds);

I'm creating an instance of the Mapper where the annotation above is defined and calling it as follows:

List<Integer> empIds = Arrays.asList(1, 2, 3);
List<Employee> result = mapper.selectSpecificEmployees(empIds);

I've discovered that this doesn't work.

org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: java.lang.NullPointerException
### The error may involve
com.mycompany.MySourceMapper.selectSpecificEmployees-Inline
### The error occurred while setting parameters ### Cause: java.lang.NullPointerException at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:77) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:69) at org.apache.ibatis.binding.MapperMethod.executeForList(MapperMethod.java:85) at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:65) at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:35) at $Proxy23.selectSpecificProductTypes(Unknown Source) at com.mycompany.MySourceMapperDebug.testSelectSpecificEmployees(MySourceMapperDebug.java:60) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at junit.framework.TestCase.runTest(TestCase.java:154) at junit.framework.TestCase.runBare(TestCase.java:127) at junit.framework.TestResult$1.protect(TestResult.java:106) at junit.framework.TestResult.runProtected(TestResult.java:124) at junit.framework.TestResult.run(TestResult.java:109) at junit.framework.TestCase.run(TestCase.java:118) at junit.framework.TestSuite.runTest(TestSuite.java:208) at junit.framework.TestSuite.run(TestSuite.java:203) at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:130) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197) Caused by: java.lang.NullPointerException at org.apache.ibatis.type.UnknownTypeHandler.setNonNullParameter(UnknownTypeHandler.java:21) at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:23) at org.apache.ibatis.executor.parameter.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:73) at org.apache.ibatis.executor.statement.PreparedStatementHandler.parameterize(PreparedStatementHandler.java:61) at org.apache.ibatis.executor.statement.RoutingStatementHandler.parameterize(RoutingStatementHandler.java:43) at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:56) at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:40) at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:216) at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:95) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:72) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:31)
... 36 more

I think the problem is in the annotation itself. This seems like it would be a fairly common requirement. Do I need to convert the List to a String myself and pass that in as a String parameter instead of a List<Integer>? Or is there some other syntax for passing a List as a parameter to a MyBatis annotation?

Answer 1

I've never used annotations and MyBatis before; I've always gone the xml configuration file route (not implying there is anything wrong with using annotations; just explaining I can't help you there).

That being said, page 46 from the MyBatis user guide:

foreach

Another common necessity for dynamic SQL is the need to iterate over a collection, often to build an IN condition. For example:

<select id="selectPostIn" resultType="domain.blog.Post">
    SELECT *
    FROM POST P
    WHERE ID in
    <foreach item="item" index="index" collection="list"
        open="(" separator="," close=")">
          #{item}
    </foreach>
  </select>

The foreach element is very powerful, and allows you to specify a collection, declare item and index variables that can be used inside the body of the element. It also allows you to specify opening and closing strings, and add a separator to place in between iterations. The element is smart in that it won’t accidentally append extra separators.

Answer 2

With a bit of overhead you can use JAVA to build a dynamic String after processing the List.

1. Define a Select Provider where you can build your dynamic query:

   @SelectProvider(type = com.data.sqlprovider.EmployeeSQLBuilder.class, method =      
    "selectSpecificEmployees")
    List<Employee> selectSpecificEmployees(@Param("employeeIds") List<Integer> 
     employeeIds);
   

2. In com.data.sqlprovider.EmployeeSQLBuilder.class , Using StringBuilder, generate the query

    public String selectSpecificEmployees(Map<String, Object> parameters) {
       List<Integer> employeeIds = (List<Integer>) parameters.get("employeeIds");
       StringBuilder builder = new StringBuilder("SELECT id, name FROM employees where id IN (");
       for (int i : employeeIds) {
           builder.append(i + ",");
       }
       builder.deleteCharAt(builder.length() - 1);
   
       builder.append(")");
       System.out.println(builder.toString());
       return builder.toString();
   }
   

Answer 3

If you wish to use foreach and annotations, you can use this syntax:

@Select("<script>" +
         "SELECT * FROM employees WHERE employeeID IN " +
           "<foreach item='item' index='index' collection='employeeIds'" +
             " open='(' separator=',' close=')'>" +
             " #{item}" +
           "</foreach>" +
         "</script>") 
List<Employee> selectSpecificEmployees(@Param("employeeIds") List<Integer> employeeIds);

(copied from that answer)