How to execute several sql-statements in one session with mybatis-string

Question

I am setting up an application with mybatis-spring, which should execute several sql-statements (mostly selects) and print the result to the console.

My applicationContext.xml looks like this:

    <context:property-placeholder location="classpath:application.properties"/>

    <!-- BEANS -->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="${spring.datasource.driverClassName}"/>
        <property name="url" value="${spring.datasource.url}"/>
        <property name="username" value="${spring.datasource.username}"/>
        <property name="password" value="${spring.datasource.password}"/>
    </bean>

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="mapperLocations" value="classpath:sql_mapper.xml"/>
    </bean>

    <bean id="organisationMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">
        <property name="mapperInterface" value="mapper.OrganisationMapper" />
        <property name="sqlSessionFactory" ref="sqlSessionFactory" />
    </bean>

I have noticed that whenever I execute a sql-statement the session is created just for this statement and closes directly after execution.

Is there a way to execute multiple sql-statements in just one session, which closes itself not until all methods/statements are executed?

Thanks and Greetings.

Answer 1

It seems that transactions are not demarcated properly and the default behaviour (one call to mapper - one transaction - one mybatis session) is used.

In spring-mybatis the session bound to spring transaction. If transactions are not demarcated then a transaction is created (and hence mybatis SqlSession) for every call to mybatis mapper method.

In order to change this you need to properly configure spring so that:

1. transactions can be used at all
2. configure transaction boundaries that is what calls to the database should be executed in one transaction

There are many ways to configure transactions for details see documentation. I'll show here the simple way that uses xml configuration.

Firstly, add transaction manager to the spring configuration:

<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
     <property name="dataSource" ref="dataSource"/>
</bean>

Then you need to specify transactions boundaries. The simple way is to use declarative transaction management:

Add this to spring configuration

<tx:annotation-driven/>

And then use @Transactional annotation on methods that should be executed transactionally:

@Service
class SomeService {

  @Autowired MyMapper mapper;

  @Transactional
  SomeResult someMethod () {
    Piece1 piece1 = mapper.getSome();
    Piece2 piece2 = mapper.getMore();
    SomeResult result = SomeResult(piece1, piece2);
  }

}