MyBatis批处理工具类MyBatisBatchHelper.java
该工具类使用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
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd"> <!-- 开启注解扫描，管理service和dao --> <context:component-scan base-package="com.alphajuns.service.*"> </context:component-scan> <context:component-scan base-package="com.alphajuns.dao"> </context:component-scan> <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> <beans profile="mysql"> <!-- properties file --> <!--<bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="locations"> <list> <value>classpath:mysql.properties</value> </list> </property> </bean>--> <context:property-placeholder location="classpath:mysql.properties"/> <!-- 配置连接池 --> <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="driverClass" value="${mysql.driver}"/> <property name="jdbcUrl" value="${mysql.url}"/> <property name="user" value="${mysql.username}"/> <property name="password" value="${mysql.password}"/> </bean> </beans> <beans> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"></property> </bean> <!-- 把交给IOC管理 SqlSessionFactory --> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dataSource"/> </bean> <!-- 扫描dao接口 --> <bean id="mapperScanner" class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <property name="basePackage" value="com.alphajuns.dao"/> </bean> <!-- 配置Spring的声明式事务管理 --> <!-- 配置事务管理器 --> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource"/> </bean> <tx:annotation-driven transaction-manager="transactionManager"/> </beans> </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>
