欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MyBatis批量新增和更新 mybatisbatch 

程序员文章站 2022-06-10 09:38:43
...
网上参考了下总结:在连接SQL后面加上:&allowMultiQueries=true 才能实现批量更新

一、批量添加
1、XML
<insert id="addBatchs" useGeneratedKeys="true" parameterType="java.util.List">  
		    <selectKey resultType="long" keyProperty="id" order="AFTER">  
		        SELECT  
		       	 	LAST_INSERT_ID() AS ID
		    </selectKey>  
	    	INSERT INTO A (aa)   
	    	VALUES  
		    <foreach collection="list" item="item" index="index" separator="," >  
		        (#{item.aa})
		    </foreach>  
	</insert>  

2、代码方法体

        int result = 0;
        if (collections == null || collections.isEmpty()) {
            return result;
        }
        SqlSession batchSqlSession = null;
        try {
            // 获取批量方式的sqlsession
            batchSqlSession = getSqlSessionTemplate().getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
            String insertSqlId = getStatement("addBatchs");
            // 每批commit的个数 > 100
            int startIndex = 0;
            int temps = 100;
            int size = collections.size();
            int count = (size % temps == 0) ? (size / temps) : (size / temps) + 1;
            boolean tag = false;
            for (int i = 0; i < count; i++) {
                if (tag) {
                    break;
                }
                if (temps > size) {
                    temps = size;
                    tag = true;
                }
                List<T> tempLists = collections.subList(startIndex, temps);
                batchSqlSession.insert(insertSqlId, tempLists);
                result += tempLists.size();
                startIndex = temps;
                temps += 100;
                batchSqlSession.commit();
                batchSqlSession.clearCache();
            }
        } catch (Exception e) {
            batchSqlSession.rollback();
        } finally {
            batchSqlSession.close();
        }
        return result;
    


二 、更新
1、XML
<update id="updateBatchs"  parameterType="java.util.List">  	
		    <foreach collection="list" item="item" index="index" open="" close="" separator=";">
		       	UPDATE 
		       		<include refid="table_name" />   
		       	<set>	
			        <if test="item.aa != null"><![CDATA[aa= #{item.aa},]]></if>
				</set>	
				<![CDATA[WHERE id = #{item.id}]]>
		    </foreach>  
	</update>  

2、方法体

        int result = 0;
        if (collections == null || collections.isEmpty()) {
            return result;
        }
        SqlSession batchSqlSession = null;
        try {
            // 获取批量方式的sqlsession
            batchSqlSession = getSqlSessionTemplate().getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
            String updateSqlId = getStatement("updateBatchs");
            // 每批commit的个数 > 100
            int startIndex = 0;
            int temps = IConstant.BATH_QUERY_SIZE;
            int size = collections.size();
            int count = (size % temps == 0) ? (size / temps) : (size / temps) + 1;
            boolean tag = false;
            for (int i = 0; i < count; i++) {
                if (tag) {
                    break;
                }
                if (temps > size) {
                    temps = size;
                    tag = true;
                }
                List<T> tempLists = collections.subList(startIndex, temps);
               batchSqlSession.update(updateSqlId, tempLists);
                result += tempLists.size();
                startIndex = temps;
                temps += 100;
                batchSqlSession.commit();
                batchSqlSession.clearCache();
            }
        } catch (Exception e) {
            e.printStackTrace();            
            result = 0;
            batchSqlSession.rollback();
        } finally {
            batchSqlSession.close();
        }
        return result;
    
相关标签: mybatis batch