How to implement batch operations with MyBatis/Spring?

Question

I am wondering how to implement batch operations with my insert statements using MyBatis 3 & Spring 3?

For example, here is what is currently being done:

spring.xml:

<bean id="jndiTemplateDatasource" class="org.springframework.jndi.JndiTemplate">
    <property name="environment">
      <props>
        <prop key="java.naming.factory.initial">${context.factory}</prop>
      </props>
    </property>
</bean>
<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
  <property name="jndiTemplate" ref="jndiTemplateDatasource"/>
  <property name="jndiName" value="${connectionpool.jndi}"/>
</bean>
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
  <property name="dataSource" ref="dataSource"/>
</bean>

<tx:annotation-driven transaction-manager="transactionManager"/>

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
  <property name="dataSource" ref="dataSource" />
  <property name="configLocation" value="classpath:mybatis-config.xml"/>
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
  <property name="basePackage" value="com.test" />
</bean>

MyService.xml:

<insert id="insertMyRecord" parameterType="com.test.MyRecord"  >
   insert into ... // code removed
</insert> 

MyService.java:

public interface MyService {

  public void insertMyRecord (MyRecord);
}

MyController.java:

@Controller
public class MyController {

  @Autowired
  private MyService myService;

  @Transactional
  @RequestMapping( .... )
  public void bulkUpload (@RequestBody List<MyRecord> myRecords) {
    for (MyRecord record : myRecords) {
      myService.insertMyRecord(record);
    }
  }
}

Disclaimer: That is just pseudo code for demonstration purposes

So what can I do to turn that into a batch process?

Ideally I want to be able to do it with least "intrusion" into code, i.e. use annotations more preferred, but if not possible what is the next best thing?

Also, this needs to be configured just for this one service, not for everything in the project.

Answer 1

This is running and tested example ... Update multiple rows using batch (ibatis + java )

In this ex. I am updating attending count from table with respective to partyid.

public static int updateBatch(List<MyModel> attendingUsrList) {
    SqlSession session = ConnectionBuilderAction.getSqlSession();
    PartyDao partyDao = session.getMapper(PartyDao.class);
    try {
        if (attendingUsrList.size() > 0) {
            partyDao.updateAttendingCountForParties(attendingUsrList);
        }
        session.commit();
    } catch (Throwable t) {
        session.rollback();
        logger.error("Exception occurred during updateBatch : ", t);
        throw new PersistenceException(t);
    } finally {
        session.close();
    }
}

Model class where variable is defined :

public class MyModel  {

    private long attending_count;
    private String eid;

    public String getEid() {
        return eid;
    }

    public void setEid(String eid) {
        this.eid = eid;
    }

    public long getAttending_count() {
        return attending_count;
    }

    public void setAttending_count(long attending_count) {
        this.attending_count = attending_count;
    }


}

party.xml code

Actual query where batch execute

<foreach collection="attendingUsrList" item="model"  separator=";">
    UPDATE parties SET attending_user_count = #{model.attending_count}
    WHERE  fb_party_id = #{model.eid}  
</foreach>

Interface code here

public interface PartyDao {
    int updateAttendingCountForParties (@Param("attendingUsrList") List<FBEventModel>attendingUsrList);
}

Here is my batch session code

public static synchronized SqlSession getSqlBatchSession() {
    ConnectionBuilderAction connection = new ConnectionBuilderAction();
    sf = connection.getConnection();
    SqlSession session = sf.openSession(ExecutorType.BATCH);
    return session;
}

SqlSession session = ConnectionBuilderAction.getSqlSession();

Answer 2

The accepted answer above doesn't actually get you batch mode for MyBatis. You need to choose the proper Executor via ExecutorType.BATCH. That is either passed as a parameter to SqlSession.openSession in standard MyBatis API or, if using MyBatis-Spring, as an option to the SqlSessionTemplate. That is done via:

<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
    <constructor-arg index="0" ref="sqlSessionFactory" />
    <constructor-arg index="1" value="BATCH" />
</bean>

There is nothing else that needs to be done.

Answer 3

I'm not sure I understand the question fully correct but I will try to give you my thoughts.

For making the single service I would recommend to generify the service interface:

public void bulkUpload (@RequestBody List<T> myRecords) 

Then you can check the type of the object and call the propper mapper repository.

Then you can generify it more by creating a common interface:

public interface Creator<T> {
    void create(T object);
}

and extend it by your mapper interface:

public interface MyService extends Creator<MyRecord>{}

Now the most complicated step: you get the object of a particular type, see what exact mapper implements the Creator interface for this class (using java reflection API) and invoke the particular method.

Now I give you the code I use in one of my projects:

package com.mydomain.repository;

//imports ...
import org.reflections.Reflections;

@Repository(value = "dao")
public class MyBatisDao {

    private static final Reflections REFLECTIONS = new Reflections("com.mydomain");

    @Autowired
    public SqlSessionManager sqlSessionManager;

    public void create(Object o) {
        Creator creator = getSpecialMapper(Creator.class, o);
        creator.create(o);
    }

    // other CRUD methods

    @SuppressWarnings("unchecked")
    private <T> T getSpecialMapper(Class<T> specialClass, Object parameterObject) {
        Class parameterClass = parameterObject.getClass();
        Class<T> mapperClass = getSubInterfaceParametrizedWith(specialClass, parameterClass);
        return sqlSessionManager.getMapper(mapperClass);
    }

    private static <T, P> Class<? extends T> getSubInterfaceParametrizedWith(Class<T> superInterface, Class<P> parameterType) {
        Set<Class<? extends T>> subInterfaces = REFLECTIONS.getSubTypesOf(superInterface);
        for (Class<? extends T> subInterface: subInterfaces) {
            for (Type genericInterface : subInterface.getGenericInterfaces()) {
                if (!(genericInterface instanceof ParameterizedType)) continue;
                ParameterizedType parameterizedType = (ParameterizedType) genericInterface;
                Type rawType = parameterizedType.getRawType();
                if (rawType instanceof Class<?> && ((Class<?>) rawType).isAssignableFrom(superInterface)) {
                    for (Type type: parameterizedType.getActualTypeArguments()) {
                        if (type instanceof Class<?> && ((Class<?>) type).isAssignableFrom(parameterType)) {
                            return subInterface;
                        }
                    }
                }

            }
        }
        throw new IllegalStateException(String.format("No extension of %s found for parametrized type %s ", superInterface, parameterType));
    }
}

Warning! This approach can have bad performance impact so use it in non-performance-critical actions

If you want bulk insert I would recommend to use mybatis foreach for bulk insert as described here.

If you think you don't want to write sql for every type of objects you better use Hibernate or any other advanced ORM. MyBatis is just an SQL mapping interface.