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.

To be more clear, the goal is to get a JDBC batching type of handling in the record insertion for quick performance, instead of the one-by-one approach it does not — Trant
– Trant, Commented Jul 29, 2013 at 18:00

jkratz · Accepted Answer · 2014-03-13 15:41:34Z

12

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.

answered Mar 13, 2014 at 15:41

jkratz

3932 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Saorikido Over a year ago

Donno y, with this XML configuration, my batch insert performs very slow, no difference from insering one by one.

aioobe · Accepted Answer · 2015-12-18 18:07:55Z

11

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

edited Dec 18, 2015 at 18:07

aioobe

423k115 gold badges831 silver badges844 bronze badges

answered Sep 5, 2013 at 10:48

Mujammil H Kazi

17.4k2 gold badges37 silver badges47 bronze badges

3 Comments

jkratz Over a year ago

The main piece of information missing here is that you need to use ExecutorType.BATCH when you open your SqlSession. In standard MyBatis API it is a parameter to openSession of the SqlSession class. If you're using MyBatis-Spring you'd pass it in as a parameter to your SqlSessionTemplate. ie: <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate"> <constructor-arg index="0" ref="sqlSessionFactory" /> <constructor-arg index="1" value="BATCH" /> </bean>

aioobe Over a year ago

At the bottom you define a getSqlBatchSession, but in your code you call getSqlSession. Is this a typo?

rimsky Over a year ago

Should not use foreach. See here for more efficient way: stackoverflow.com/a/29264696/922348

Community · Accepted Answer · 2017-05-23 12:17:31Z

2

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.

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Jul 29, 2013 at 16:41

Nailgun

4,1894 gold badges35 silver badges47 bronze badges

4 Comments

Trant Over a year ago

Very nice info there which I may end up using but it doesn't address the desire to implement batching. What I want is for my loop of inserts to be handled similar to JDBC batch for performance reasons, instead of each insert being single transactions as it gets handled now

Nailgun Over a year ago

@Trant So doesn't this mean you need to use the bulk insert with mybatis foreach (the link I gave above)?

Trant Over a year ago

But does MyBatis turn that into a optimized batch operation or does it still do a one-by-one insert ?

Nailgun Over a year ago

@Trant JDBC batch sends multiple statements at once and doesn't make any other optimizations. MyBatis does the same if you include several SQL statements in a single method in your mapper xml. If your app insert multiple rows with one statement (as described in linked StackOverflow question) it will of cource perform even better.

Collectives™ on Stack Overflow

How to implement batch operations with MyBatis/Spring?

3 Answers 3

1 Comment

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related