该工具类使用mybatis的会话来手动提交事务，从而对批量sql进行控制。事务提交，多条sql执行结果会被更新到数据库，出现异常则回滚。

测试采用的表为oracle数据库scott下的dept表和salgrade表

 

 

 

 

 

MyBatisBatchHelper.java

package com.alphajuns.util;

import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.log4j.Logger;
import org.springframework.web.context.ContextLoader;
import org.springframework.web.context.WebApplicationContext;

/**
 * @ClassName MyBatisBatchHelper
 * @Description MyBatis批处理帮助类
 * @Author AlphaJunS
 * @Date 2020/4/1 19:42
 * @Version 1.0
 */
public class MyBatisBatchHelper {

    private static Logger logger = Logger.getLogger(MyBatisBatchHelper.class);

    /**
     * open session
     * @param sqlSessionFactoryBiz
     * @return
     */
    static public SqlSession openSession(SqlSessionFactory sqlSessionFactoryBiz){
        SqlSession sqlSession = null;
        WebApplicationContext wac = ContextLoader.getCurrentWebApplicationContext();
        String targetEnv = wac.getServletContext().getInitParameter("spring.profiles.default");
        if("dev".equals(targetEnv)){
            sqlSession = sqlSessionFactoryBiz.openSession(ExecutorType.BATCH, false);
        }else{
            sqlSession = sqlSessionFactoryBiz.openSession(ExecutorType.BATCH, true);
        }
        return sqlSession;
    }

    /**
     * commit
     * @param sqlSession
     */
    public static void  commit(SqlSession sqlSession){
        WebApplicationContext wac = ContextLoader.getCurrentWebApplicationContext();
        String targetEnv = wac.getServletContext().getInitParameter("spring.profiles.default");
        try{
            if("dev".equals(targetEnv)){
                sqlSession.commit();
                sqlSession.clearCache();
            }else{
                sqlSession.commit();
            }
        }catch(Exception e){
            if(e.getMessage().contains("自动提交")){
                logger.warn(e.getMessage().substring(0,20));
            }else{
                logger.error("commits error",e);
            }
        }
    }

    /**
     * rollback
     * @param sqlSession
     */
    public static void rollback(SqlSession sqlSession){
        WebApplicationContext wac = ContextLoader.getCurrentWebApplicationContext();
        String targetEnv = wac.getServletContext().getInitParameter("spring.profiles.default");
        try{
            if("dev".equals(targetEnv)){
                sqlSession.rollback();
            }
        }catch(Exception e){
            if(e.getMessage().contains("自动提交")){
                logger.warn(e.getMessage().substring(0,20));
            }else{
                logger.error("rollback error",e);
            }
        }
    }

    /**
     * close
     * @param sqlSession
     */
    public static void close(SqlSession sqlSession){
        WebApplicationContext wac = ContextLoader.getCurrentWebApplicationContext();
        String targetEnv = wac.getServletContext().getInitParameter("spring.profiles.default");
        try{
            if("dev".equals(targetEnv)){
                sqlSession.close();
            }else{
                sqlSession.close();
            }
        }catch(Exception e){
            if(e.getMessage().contains("自动提交")){
                logger.warn(e.getMessage().substring(0,20));
            }else{
                logger.error("close error",e);
            }
        }
    }

}

 

工具类中需要获取数据源，通过指定Spring Profile来加载数据源。在web.xml中配置服务器初始化参数

web.xml

<context-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>classpath*:applicationContext-profile.xml</param-value>
    </context-param>
    <!-- 指定服务器启动时加载的Profile -->
    <context-param>
        <param-name>spring.profiles.default</param-name>
        <param-value>dev</param-value>
    </context-param>

spring核心配置文件

applicationContext-profile.xml

<beans profile="dev">
        <!-- properties file -->
        <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
            <property name="locations">
                <list>
                    <value>classpath:oracle.properties</value>
                </list>
            </property>
        </bean>
        <!-- 配置连接池 -->
        <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
            <property name="driverClass" value="${oracle.driver}"/>
            <property name="jdbcUrl" value="${oracle.url}"/>
            <property name="user" value="${oracle.username}"/>
            <property name="password" value="${oracle.password}"/>
        </bean>
    </beans>

测试用的service

    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    @Override
    public void mybatisBatch() {
        // 开启会话
        SqlSession sqlSession = MyBatisBatchHelper.openSession(sqlSessionFactory);
        // 获取接口代理对象
        EmpMapper batchEmpMapper = sqlSession.getMapper(EmpMapper.class);
        // 批量操作数据库数据
        try {
            Map<String, String> paramMap = new HashMap<>();
            paramMap.put("DEPTNO", "50");
            paramMap.put("DNAME", "DOCTOR");
            paramMap.put("LOC", "BEIJING");
            // 插入Scott下DEPT表
            batchEmpMapper.insertDept(paramMap);

            // 无异常时，事务提交，数据被更新到数据库
            // 用于模拟异常，捕获异常时，回滚事务，注释打开时，可测试异常，发现前面操作数据库的数据没有被更新到数据库中
            // int i = 1/0;

            Map<String, Integer> map = new HashMap<>();
            map.put("GRADE", 6);
            map.put("LOSAL", 4001);
            map.put("HISAL", 10000);
            // 更新Scott下SALGRADE
            batchEmpMapper.insertSalGrade(map);

            // 提交事务
            MyBatisBatchHelper.commit(sqlSession);
        } catch (Exception e) {
            e.printStackTrace();
            // 回滚事务
            MyBatisBatchHelper.rollback(sqlSession);
        } finally {
            // 关闭会话
            MyBatisBatchHelper.close(sqlSession);
        }
    }

mapper接口

　　void insertDept(@Param("paramMap") Map<String, String> paramMap);

　　void insertSalGrade(@Param("paramMap") Map<String, Integer> map);

mapper.xml

　　<insert id="insertDept" parameterType="map">
        insert into dept (DEPTNO, DNAME, LOC)
        VALUES
        (
            #{paramMap.DEPTNO, jdbcType=INTEGER},
            #{paramMap.DNAME, jdbcType=VARCHAR},
            #{paramMap.LOC, jdbcType=VARCHAR}
        )
    </insert>

    <insert id="insertSalGrade" parameterType="map">
        insert into salgrade (GRADE, LOSAL, HISAL)
        VALUES
        (
            #{paramMap.GRADE, jdbcType=INTEGER},
            #{paramMap.LOSAL, jdbcType=INTEGER},
            #{paramMap.HISAL, jdbcType=INTEGER}
        )
    </insert>