mybatis中用foreach批量处理优化的一些事。

有一个工作需求，是对代码做优化，看了代码主要原因是在for循环里操作了数据库。

以前也写过类似的，就直接想到在mapper里用foreach。然后写起来一直报错：

最主要的是这个报错一直报语法错误，实际语法并没错，所以浪费了很多时间。这个问题是mysql在用foreach批量添加修改时，在连接数据库的url连接要拼上&allowMultiQueries=true（oracle不用），拼上后如果服务起不来的话，shutDown.bat次再重启。

批量添加、修改

这个需要循环出多条sql语句，如果集合过大的话，一次性将集合传到mapper，虽然减少了数据库连接次数，但sql过长对数据库不是很友好，所以应该将list拆解，提供个demo

if (insertList != null && insertList.size() > 0) {

            if (insertList.size() <= 500) {
                aMapper.batchInsertSelective(insertList);
            } else {
                int pages = (int) Math.ceil(insertList.size() / 500);
                for (int p = 0; p <= pages; p++) {
                    if (CollectionUtils.isNotEmpty(insertList.subList(p * 500, Math.min((p + 1) * 500, insertList.size())))) {
                        assessmentScoreMapper.batchInsertSelective(insertList.subList(p * 500, Math.min((p + 1) * 500, insertList.size())));
                    }
                }
            }

        }

每500个向后传一次，降低数据库的连接次数同时数据库压力不会太大。

mysql写修改：

<update id="batchUpdateByPrimaryKeySelective" >

      <foreach collection="list" item="item" index="index" open="" close="" separator=";">
        update stu
        <set >

            <if test="item.name!= null and item.name!=''" >
                `name` = #{item.name,jdbcType=VARCHAR},
            </if>
            <if test="item.age != null" >
                `age` = #{item.age,jdbcType=DECIMAL},
            </if>
            
        </set>

        where `ID` = #{item.id,jdbcType=DECIMAL}
      </foreach>
</update>

oracle写修改：

<update id="batchUpdateByPrimaryKeySelective" >

      <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">
        update stu
        <set >

            <if test="item.name!= null and item.name!=''" >
                `name` = #{item.name,jdbcType=VARCHAR},
            </if>
            <if test="item.age != null" >
                `age` = #{item.age,jdbcType=DECIMAL},
            </if>
            
        </set>

        where `ID` = #{item.id,jdbcType=DECIMAL}
      </foreach>
</update>

mysql下添加：

<insert id="batchInsertSelective" useGeneratedKeys="true">

        insert into stu(id,name,age) values
        <foreach collection="list" item="item" index="index" open="" close="" separator=",">
            (null,
            #{item.name,jdbcType=VARCHAR},
            #{item.age,jdbcType=DECIMAL})

        </foreach>


 </insert>

oracle下添加

<insert id="batchInsertSelective" >

        insert into  stu(id,name,age) 
        <foreach collection="list" item="item" index="index" open="" close="" separator="UNION ALL">
         select
            #{item.id,jdbcType=DECIMAL},
            #{item.name,jdbcType=VARCHAR},
            #{item.age,jdbcType=DECIMAL}
         from dual
        </foreach>

</insert>

批量查询、删除

这个只需写一条sql,不用考虑数据库压力，但是，in()里面参数超过1000个会有bug，这个解决方法有很多，提供一种方法

    public List<Stu> batchSelect(Long[] ids) {

        List<Stu> all  = new ArrayList<>();
        if(ids.length<=1000){
            all = aMapper.batchSelect(ids);
        }else if(ids.length>1000){
            int count  = ids.length;
            int pageSize = 1000;
            for(int i = 0;i<=(count/pageSize);i++){
                int beg = i*pageSize;//开始
                int end =(i+1)*pageSize; //结束
                if(i==count/pageSize){
                    end= ids.length;
                }
                System.out.println("第"+(i+1)+"次获取，从"+beg+"获取到"+end);
                Long [] newData = Arrays.copyOfRange(ids,  beg ,  end );
                List<Stu> some = aMapper.batchSelect(newData);
                all.addAll(some);
            }
        }

        return all;
    }

xml写法：查找和删除拼接差不多

    <select id="batchSelect" resultMap="BaseResultMap" >
        select
        <include refid="Base_Column_List" />
        from stu
        where ID in
        <foreach collection="ids" index="index" open="(" close=")" separator="," item="id">
            #{id}
        </foreach>
    </select>

最后经过优化，接口从原来的6s变成0.5s。