Java – pass a List as a parameter to a MyBatis mapper

javamybatis

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?

Best Answer

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.

Related Solutions

Java – Is Java “pass-by-reference” or “pass-by-value”

Java is always pass-by-value. Unfortunately, when we deal with objects we are really dealing with object-handles called references which are passed-by-value as well. This terminology and semantics easily confuse many beginners.

It goes like this:

public static void main(String[] args) {
    Dog aDog = new Dog("Max");
    Dog oldDog = aDog;

    // we pass the object to foo
    foo(aDog);
    // aDog variable is still pointing to the "Max" dog when foo(...) returns
    aDog.getName().equals("Max"); // true
    aDog.getName().equals("Fifi"); // false
    aDog == oldDog; // true
}

public static void foo(Dog d) {
    d.getName().equals("Max"); // true
    // change d inside of foo() to point to a new Dog instance "Fifi"
    d = new Dog("Fifi");
    d.getName().equals("Fifi"); // true
}

In the example above aDog.getName() will still return "Max". The value aDog within main is not changed in the function foo with the Dog "Fifi" as the object reference is passed by value. If it were passed by reference, then the aDog.getName() in main would return "Fifi" after the call to foo.

Likewise:

public static void main(String[] args) {
    Dog aDog = new Dog("Max");
    Dog oldDog = aDog;

    foo(aDog);
    // when foo(...) returns, the name of the dog has been changed to "Fifi"
    aDog.getName().equals("Fifi"); // true
    // but it is still the same dog:
    aDog == oldDog; // true
}

public static void foo(Dog d) {
    d.getName().equals("Max"); // true
    // this changes the name of d to be "Fifi"
    d.setName("Fifi");
}

In the above example, Fifi is the dog's name after call to foo(aDog) because the object's name was set inside of foo(...). Any operations that foo performs on d are such that, for all practical purposes, they are performed on aDog, but it is not possible to change the value of the variable aDog itself.

For more information on pass by reference and pass by value, consult the following SO answer: https://stackoverflow.com/a/430958/6005228. This explains more thoroughly the semantics and history behind the two and also explains why Java and many other modern languages appear to do both in certain cases.

Java – How to create a memory leak in Java

Here's a good way to create a true memory leak (objects inaccessible by running code but still stored in memory) in pure Java:

The application creates a long-running thread (or use a thread pool to leak even faster).
The thread loads a class via an (optionally custom) ClassLoader.
The class allocates a large chunk of memory (e.g. new byte[1000000]), stores a strong reference to it in a static field, and then stores a reference to itself in a ThreadLocal. Allocating the extra memory is optional (leaking the class instance is enough), but it will make the leak work that much faster.
The application clears all references to the custom class or the ClassLoader it was loaded from.
Repeat.

Due to the way ThreadLocal is implemented in Oracle's JDK, this creates a memory leak:

Each Thread has a private field threadLocals, which actually stores the thread-local values.
Each key in this map is a weak reference to a ThreadLocal object, so after that ThreadLocal object is garbage-collected, its entry is removed from the map.
But each value is a strong reference, so when a value (directly or indirectly) points to the ThreadLocal object that is its key, that object will neither be garbage-collected nor removed from the map as long as the thread lives.

In this example, the chain of strong references looks like this:

Thread object → threadLocals map → instance of example class → example class → static ThreadLocal field → ThreadLocal object.

(The ClassLoader doesn't really play a role in creating the leak, it just makes the leak worse because of this additional reference chain: example class → ClassLoader → all the classes it has loaded. It was even worse in many JVM implementations, especially prior to Java 7, because classes and ClassLoaders were allocated straight into permgen and were never garbage-collected at all.)

A variation on this pattern is why application containers (like Tomcat) can leak memory like a sieve if you frequently redeploy applications which happen to use ThreadLocals that in some way point back to themselves. This can happen for a number of subtle reasons and is often hard to debug and/or fix.

Update: Since lots of people keep asking for it, here's some example code that shows this behavior in action.

Best Answer

Related Solutions

Java – Is Java “pass-by-reference” or “pass-by-value”

Java – How to create a memory leak in Java

Related Topic