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?

You can use both xml and annotation based config together look at this post please http://stackoverflow.com/questions/8788250/xml-annotation-based-configuration-for-mybatis/9076435 — Moinul Hossain, Jan 31 '12 at 08:39

score 55 · Answer 1 · edited May 13 '21 at 18:43

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.

Do you know if mybatis can handle the limits in the IN clauses (like oracle is limited to 1000). I guess we should take care of it ourselves — Sebastien Lorber, Oct 19 '12 at 15:58
Honestly I wouldn't know off of the top of my head. This post was actually around the last time I used MyBatis so I'm rusty. That being said, I'm sure if you opened the user guide I linked to and did a search for "limit", something would show up - if it were there. That is my best guess. — Dave, Oct 19 '12 at 18:13

score 6 · Answer 2 · edited May 23 '17 at 10:29

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)

score 5 · Answer 3 · answered Apr 25 '14 at 06:42

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

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);

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();
}

Because the possible employeeIds are integers you can get away with this. However, if these were strings you'd have a problem if the string has any special characters in it — kasdega, Nov 20 '14 at 16:53
concatenating parameter values in SQL string is evil. And if parameters are strings, this is the recipe for SQL injection. — blackwizard, Mar 15 '17 at 20:45

score 5 · Answer 4 · answered Mar 11 '17 at 03:15

I'm facing the exact same issues recently. For my understanding, you prefer to use Java mapper instead of XML, which is the same here.

Following is what I'm doing to deal with it using: SqlBuilder.

The sql builder class:

public class EmployeeSqlBuilder {

    public String getEmployees(final List employeeIds) {

        String strSQL = new SQL() {{
            SELECT("*");
            FROM("employees");
            if (employeeIds != null) {
                WHERE(getSqlConditionCollection("employeeID", employeeIds));
            }
        }}.toString();

        return strSQL;
    }

    private String getSqlConditionCollection(String field, List conditions) {
        String strConditions = "";
        if (conditions != null && conditions.size() > 0) {
            int count = conditions.size();
            for (int i = 0; i < count; i++) {
                String condition = conditions.get(i).toString();

                strConditions += condition;
                if (i < count - 1) {
                    strConditions += ",";
                }
            }
            return field + " in (" + strConditions + ")";
        } else {
            return "1=1";
        }
    }

}

The mapper:

@SelectProvider(type = EmployeeSqlBuilder.class, method = "getEmployees")
List<RecordSubjectEx> getEmployees(@Param("employeeIds") List employeeIds);

That's it.

The EmployeeSqlBuilder will dynamically generate the sql statement. I'm using a function getSqlConditionCollection to do the logical manipulation. Of course you can encapsulate the getSqlConditionCollection as a static function in a class, which is what I'm doing in real project, then you can use it easily from other SqlBuilder.

score -1 · Answer 5 · answered Apr 20 '19 at 04:07

MyBatis support List params directly.

Assume this is you dao layer:

public List<User> getUsersByIds(List<Integer> ids);

you want to pass an ids list. then, in you mapper.xml, you can just use them:

    <select id="getUsersByIds" resultType="entity.User">
      select * from user
      where id = #{list[0]} or id = #{list[1]}
    </select>

you can use #{list[0]} or #{list[1]} to get list value.

    @Test
    public void getUsersByIds(){
        List<Integer> ids = new ArrayList<Integer>();
        ids.add(1);
        ids.add(2);
        List<User> users = userDao.getUsersByIds(ids);
        // you will get correct result
    }

This is somewhat misleading statement since most people will be looking at `IN(#{list})` case which is not supported, so we're stuck with http://www.mybatis.org/mybatis-3/dynamic-sql.html?#foreach — Bostone, Jul 22 '19 at 15:54

huang botao · Answer 6 · 2019-11-12T06:18:19.867

although this question is many years ago, and @Dave 's answer is right, but today i want to know how to sove it by java not xml config, i got it and share it to some of you.

    @Autowired
    PersonMapper personMapper;

    @Test
    public void testListParams(){
        String[] ids = {"1","2","3","4"};
        List<String> idList = Arrays.asList(ids);
        String idListToString = idList.toString().substring(1, idList.toString().length() - 1);
        List<Person> people = personMapper.selectByIds(idListToString);
        people.forEach(System.out::println);
    }

in PersonMapper.java

    @Select("select id,name,age from person where id in ( ${ids} )") // attention: here is $ not #, $ is use to replace with string, # is use to replace ? with param
    List<Person> selectByIds(@Param("ids") String ids);

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

6 Answers6

Linked