How is MyBatis dealing with an empty result set?

Question

Recently I was using Mybatis3 and found that when your SQL statement gets an empty result set from the database, Mybatis creates a new List and returns it to your program.

Given some code, like:

List<User> resultList = (List<User>)sqlSession.select("statementId");

<select id="statementId" resultType="User">
   select * from user where id > 100
</select>

assume that the above SQL return no rows (i.e. there is no id greater than 100).

The variable resultList will then be an empty List, but I want it to be null instead. How can I do that?

Answer 1

It's better to have an empty collection instead of null as a result of your query. When working with a collection you usually loop through each item and do something with it, something like this:

List<User> resultList = (List<User>) sqlSession.select("statementId");
for (User u : resultList) { 
   //... 
}

which doesn't do anything if the list is empty.

But if you return null, you have to guard your code against NullPointerExceptions and write code like this instead:

List<User> resultList = (List<User>) sqlSession.select("statementId");
if (resultList != null) {
  for (User u : resultList) { 
     //... 
  }
}

The first approach is usually better and MyBatis does it like that, but you could force it to return null, if that is really what you want.

For that you could write a MyBatis plugin and intercept calls to any query and then return null if the query result is empty.

Here is some code:

In your configuration add:

<plugins>
   <plugin interceptor="pack.test.MyInterceptor" />
</plugins>

The interceptor code:

package pack.test;

import java.util.List;
import java.util.Properties;

import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

@Intercepts({ @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}) })
public class MyInterceptor implements Interceptor {
    public Object intercept(Invocation invocation) throws Throwable {
        Object result = invocation.proceed();
        List<?> list = (List<?>) result;
        return (list.size() == 0 ? null : result);
    }

    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    public void setProperties(Properties properties) {
    }
}

You could then further limit the scope of the interceptor if you intercept calls to ResultSetHandler instead of Executor.