Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

java

mybatis

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?

like image 664
Bill the Lizard Avatar asked Dec 09 '11 20:12

Bill the Lizard


People also ask

What is the difference between iBATIS and MyBatis?

MyBatis is a fork from iBATIS, and according to Wikipedia most of iBATIS' developers moved over to MyBatis too. The iBATIS project is currently marked as Inactive, therefore you should go with MyBatis for new projects and only use iBATIS if you're maintaining an existing project which already uses iBATIS.

How does MyBatis Mapper work?

It is an alternative to JDBC and Hibernate. It automates the mapping between SQL databases and objects in Java, . NET, and Ruby on Rails. The mappings are decoupled from the application logic by packaging the SQL statements in XML configuration files.

What is resultMap in MyBatis?

The resultMap element is the most important and powerful element in MyBatis. It's what allows you to do away with 90% of the code that JDBC requires to retrieve data from ResultSet s, and in some cases allows you to do things that JDBC does not even support.


3 Answers

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.

like image 160
Dave Avatar answered Oct 20 '22 20:10

Dave


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();
    }
    
like image 5
hemantvsn Avatar answered Oct 20 '22 20:10

hemantvsn


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)

like image 5
Arnaud Avatar answered Oct 20 '22 20:10

Arnaud