mybatis批量操作中可以这样做优化

应用背景

  在业务逻辑中，经常会遇到需要通过一个结果集去查询(增删查改)另一个结果集的情况，如果入参结果集只是一个基本数据类型，那可能都能想到使用标签的方式进行查询，但是如果入参是一个复杂的对象集合，那很多人都可能只会在代码逻辑上进行进行循环处理，当项目中到处都是这种使用方式，且数据量遇到高峰的时候，很可能就会导致应用响应慢，甚至硬件资源耗尽等情况。故我们需要去优化他们！

1. 实现方式一：SqlSession批量提交法

需求

做成公共方法，方便维护和引用

设计思路

SqlSession实现批量提交

代码

SqlBatchExecuteUtil

/**
 * @version V1.0
 * @Title:
 * @Description: 数据库批量操作接口实现类
 * @author: huanghao
 * @date: 2024/2/23$ 14:17$
 */
@Slf4j
@Component
public class SqlBatchExecuteUtil {

    public static final int FAIL = 0;
    public static final int BATCH_EXECUTE_COUNT = 1000;
    public static final int SUCCESS = 1;
    private static final Pattern COMPILE = Pattern.compile("\\t");
    private static final Pattern PATTERN = Pattern.compile("\\n");
    @Setter(onMethod_ = {@Autowired})
    private SqlSessionTemplate sqlSessionTemplate;

    /**
     * 根据操作类型执行对应操作
     *
     * @param statement
     * @param sqlCommandType
     * @param sqlSession
     * @param obj
     */
    private static void executeSql(String statement, SqlCommandType sqlCommandType, SqlSession sqlSession, Object obj) {
        if (sqlCommandType == SqlCommandType.DELETE) {
            sqlSession.delete(statement, obj);
        } else if (sqlCommandType == SqlCommandType.UPDATE) {
            sqlSession.update(statement, obj);
        } else if (sqlCommandType == SqlCommandType.INSERT) {
            sqlSession.insert(statement, obj);
        }
    }

    /**
     * 单个查询
     *
     * @param nameSpace
     * @param sqlId
     * @param params
     * @Return
     */
    public Object select(String nameSpace, String sqlId, Object params) {
        if (params == null) {
            return sqlSessionTemplate.selectOne(nameSpace + "." + sqlId);
        } else {
            return sqlSessionTemplate.selectOne(nameSpace + "." + sqlId, params);
        }
    }

    /**
     * 批量查询
     *
     * @param nameSpace
     * @param sqlId
     * @param params
     * @Return
     */
    public List selectList(String nameSpace, String sqlId, Object params) {
        if (params == null) {
            return sqlSessionTemplate.selectList(nameSpace + "." + sqlId);
        } else {
            return sqlSessionTemplate.selectList(nameSpace + "." + sqlId, params);
        }
    }


    /**
     * 批量条件查询
     *
     * @param nameSpace
     * @param sqlId
     * @param params
     * @Return
     */
    public List batchSelect(String nameSpace, String sqlId, List params) {
        return batchRead(nameSpace, sqlId, params);
    }

    /**
     * 查询所有
     *
     * @param nameSpace
     * @param sqlId
     * @param params
     * @Return
     */
    public void batchALL(String nameSpace, String sqlId, List params) {
        List data = new ArrayList<>();
        for (int i = 0; i < params.size(); i++) {
            data.add(params.get(i));
            if (data.size() == BATCH_EXECUTE_COUNT || i == params.size() - SUCCESS) {
                this.batchWrite(nameSpace, sqlId, data);
                data.clear();
            }
        }
    }

    /**
     * 单个修改
     *
     * @param nameSpace
     * @param sqlId
     * @param params
     * @Return
     */
    public int update(String nameSpace, String sqlId, Object params) {
        if (params == null) {
            return sqlSessionTemplate.update(nameSpace + "." + sqlId);
        } else {
            return sqlSessionTemplate.update(nameSpace + "." + sqlId, params);
        }
    }

    /**
     * 批量修改
     *
     * @param nameSpace
     * @param sqlId
     * @param list
     * @Return
     */
    public int updateList(String nameSpace, String sqlId, List list) {
        return batchWrite(nameSpace, sqlId, list, SqlCommandType.UPDATE);
    }

    /**
     * 单个插入
     *
     * @param nameSpace
     * @param sqlId
     * @param params
     * @Return
     */
    public long insert(String nameSpace, String sqlId, Object params) {
        if (params == null) {
            return sqlSessionTemplate.insert(nameSpace + "." + sqlId);
        } else {
            return sqlSessionTemplate.insert(nameSpace + "." + sqlId, params);
        }
    }

    /**
     * 批量差入
     *
     * @param nameSpace
     * @param sqlId
     * @param list
     * @Return
     */
    public int insertList(String nameSpace, String sqlId, List list) {
        return batchWrite(nameSpace, sqlId, list, SqlCommandType.INSERT);
    }

    /**
     * 单个删除
     *
     * @param nameSpace
     * @param sqlId
     * @param params
     * @Return
     */
    public int delete(String nameSpace, String sqlId, Object params) {
        if (params == null) {
            return sqlSessionTemplate.delete(nameSpace + "." + sqlId);
        } else {
            return sqlSessionTemplate.delete(nameSpace + "." + sqlId, params);
        }
    }

    /**
     * 批量删除
     *
     * @param nameSpace
     * @param sqlId
     * @param list
     * @Return
     */
    public int deleteList(String nameSpace, String sqlId, List list) {
        return batchWrite(nameSpace, sqlId, list, SqlCommandType.DELETE);
    }

    /**
     * 批量读操作
     * 其实效率没什么显著提升，不太建议使用，Java逻辑直接foreach差不多
     *
     * @param nameSpace
     * @param sqlId
     * @param params
     * @return List
     */
    private List batchRead(String nameSpace, String sqlId, List params) {
        if (CollectionUtils.isEmpty(params)) {
            return Lists.newArrayList();
        }
        String statement = nameSpace + "." + sqlId;
        List result = Lists.newArrayList();
        SqlSession sqlSession = null;
        try {
            sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
            for (Object obj : params) {
                for (int index = 0; index < params.size(); index++) {
                    result.add(sqlSession.selectOne(statement, obj));
                    if (index != FAIL && index % BATCH_EXECUTE_COUNT == FAIL) {
                        sqlSession.commit();
                    }
                }
            }
            sqlSession.commit();
            //清理缓存防止溢出
            sqlSession.clearCache();
        } catch (Exception e) {
            log.error("batch query error:{}", e);
            if (sqlSession != null) {
                sqlSession.rollback();
            }
        } finally {
            if (sqlSession != null) {
                sqlSession.close();
            }
        }
        return result;
    }

    /**
     * 批量写操作
     * 要么整体成功，要么整体失败
     *
     * @param nameSpace
     * @param sqlId
     * @param params
     * @param sqlCommandType
     * @param
     * @return 0：失败； 1：成功
     */
    private int batchWrite(String nameSpace, String sqlId, List params, SqlCommandType sqlCommandType) {
        if (CollectionUtils.isEmpty(params)) {
            return FAIL;
        }
        String statement = nameSpace + "." + sqlId;
        SqlSession sqlSession = null;
        try {
            sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
            for (Object obj : params) {
                for (int index = 0; index < params.size(); index++) {
                    executeSql(statement, sqlCommandType, sqlSession, obj);
                    if (index != FAIL && index % BATCH_EXECUTE_COUNT == FAIL) {
                        sqlSession.commit();
                    }
                }
            }
            sqlSession.commit();
            //清理缓存防止溢出
            sqlSession.clearCache();
            return SUCCESS;
        } catch (Exception e) {
            log.error("batch read error:{}", e);
            if (sqlSession != null) {
                sqlSession.rollback();
            }
            return FAIL;
        } finally {
            if (sqlSession != null) {
                sqlSession.close();
            }
        }
    }
}

不足

1. 要么全部成功，要么全部失败，对允许部分成功不友好
2. 写操作若遇到操作表无索引或未触发到索引，形成的表级锁对表读写性能有影响
3. 效率提升有限

---

以上方式statement其实还是一个一个执行，故效率提升有限，可以考虑将多个sql拼接成一个

2. 实现方式二：通过executeBatch批处理

需求

做成公共方法，方便维护和引用

设计思路

PreparedStatement实现批处理，并进行参数获取与sql拼接

代码

同上 需将batchWrite替换成以下代码

        /**
     * 批量操作方法，通过拼接多个statement为一个，然后一次执行，相比于batchWrite，效率更高，
     * 但是本方法拼接的sql长度限制为String的最大长度：2^32；即最多能表示 65536 个字节，拼接数量控制不好就容易异常
     * 异常和回滚不好处理
     * executeBatch不对select进行批处理
     *
     * @param nameSpace
     * @param sqlId
     * @param list
     * @param
     */
    private int batchWrite(String nameSpace, String sqlId, List list) {
        try {
            if (CollectionUtils.isEmpty(list)) {
                return FAIL;
            }
            MappedStatement ms = sqlSessionTemplate.getConfiguration().getMappedStatement(nameSpace + "." + sqlId);
            SqlCommandType sqlCommandType = ms.getSqlCommandType();
            BoundSql boundSql = ms.getSqlSource().getBoundSql(list.get(0));
            String sql = boundSql.getSql();
            List<ParameterMapping> list2 = boundSql.getParameterMappings();
            Connection connection = sqlSessionTemplate.getSqlSessionFactory().openSession().getConnection();
            PreparedStatement statement = null;
            if (sqlCommandType == SqlCommandType.INSERT) {
                statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            } else {
                statement = connection.prepareStatement(sql);
            }
            sql = PATTERN.matcher(sql).replaceAll("");
            sql = COMPILE.matcher(sql).replaceAll("");
            sql = sql.replaceAll("[[ ]]{2,}", " ");
            log.info("==>  Preparing：" + sql);
            for (Object item : list) {
                if (item == null) {
                    continue;
                }
                StringBuffer values = new StringBuffer();
                if (item instanceof Map) {
                    Map<String, Object> map = (Map<String, Object>) item;
                    for (int index = 0; index < list2.size(); index++) {
                        ParameterMapping pm = list2.get(index);
                        Object value = map.get(pm.getProperty());
                        values.append(value).append("(").append(value.getClass()).append("),");
                        statement.setObject(index + 1, value);
                    }
                } else if (item instanceof Long || item instanceof String || item instanceof Integer) {
                    statement.setObject(1, item);
                    values.append(item).append("(").append(StringUtils.substringAfterLast(item.getClass().toString(), ".")).append("),");
                } else {
                    List<String> params = new ArrayList<>();
                    for (int index = 0; index < list2.size(); index++) {
                        ParameterMapping pm = list2.get(index);
                        String methodName = pm.getProperty();
                        Method method = item.getClass().getMethod(methodName);
                        Object value = method.invoke(item);
                        params.add(value.toString());
                        statement.setObject(index + 1, value);
                        values.append(value).append("(").append(StringUtils.substringAfterLast(value.getClass().toString(), ".")).append("),");
                    }
                }
                statement.addBatch();
                values.delete(values.length() - 1, values.length());
                log.info("==> Parameters：" + values);
            }
            List<Long> resultList = new ArrayList<>();
            int[] resultArray = statement.executeBatch();
            if (sqlCommandType != SqlCommandType.INSERT) {
                for (int intval : resultArray) {
                    resultList.add(Long.valueOf(intval + ""));
                }
            } else {
                ResultSet resultSet = statement.getGeneratedKeys();
                while (resultSet.next()) {
                    resultList.add(resultSet.getLong(1));
                }
            }
            return SUCCESS;
        } catch (Exception e) {
            log.error("数据库批量操作错误：{}" + e);
            return FAIL;
        }
    }

不足

1. 难以根据业务方向定回滚策略以及提示信息
2. 缺乏灵活性和对复杂问题的解决

---

以上方式不太灵活，对于复杂入参和函数等处理就很棘手，下面介绍这个方式

3. 实现方式三：拼接法

需求

要求效率高，灵活性高，能用于复杂入参和函数等处理

设计思路

使用标签

公共代码

 /**
     * 通过传可进行批量入参的statement进行数据库批处理
     *
     * @param nameSpace
     * @param sqlId
     * @param params
     * @param batchCount 自定义批量最大拼接数量，避免超长报错
     * @Return
     */
    public Object batchUtil(String nameSpace, String sqlId, List params, Integer batchCount) {
        String statement = nameSpace + "." + sqlId;

//        判断执行sql类型
        MappedStatement ms = sqlSessionTemplate.getConfiguration().getMappedStatement(statement);
        SqlCommandType sqlCommandType = ms.getSqlCommandType();
        final boolean isSelect = sqlCommandType == SqlCommandType.SELECT;
//        自定义批量最大拼接数量，避免超长报错
        int partitionCount = Objects.isNull(batchCount) ? BATCH_EXECUTE_COUNT : batchCount;

        List<Object> result = Lists.newArrayList();
        SqlSession sqlSession = null;
        try {
            if (CollectionUtils.isEmpty(params)) {
                return isSelect ? result : FAIL;
            }
            final List<List> partitions = Lists.partition(params, partitionCount);
            sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
            for (List obj : partitions) {
                if (sqlCommandType == SqlCommandType.SELECT) {
                    result.addAll(sqlSession.selectList(statement, obj));
                } else {
                    executeSql(statement, sqlCommandType, sqlSession, obj);
                }
                sqlSession.commit();
            }
            //清理缓存防止溢出
            sqlSession.clearCache();
            return isSelect ? result : SUCCESS;
        } catch (Exception e) {
            log.error("数据库批量操作错误：{}" + e);
            return isSelect ? result : FAIL;
        }
    }

3.4 特定场景举例

3.4.1 ：场景一

3.4.1.1 ：逻辑简述：

• 任务一逻辑查询出了符合条件的一组费用设置数据（字段 A,B,C）
• 任务二需要通过这组数据查询具体费用信息（D），
• 任务二需要任务一每条数据中的A,B,C三个字段才能获取到费用信息

    想要的结果：

    获取A与D的键值对信息

3.4.1.2 ：逻辑代码

BankCountServiceImpl

    @Override
    public List<BankCost> queryAccountBank(Map cond) {
        log.info("开始账户信息{}", cond);
        List<CostSetting> costSettings = bankInfoDao.querySetting(cond);
        return batchBaseService.batchUtil("com.www.gg.costsetting.dao.CostSettingDao", "batchQueryCosts", costSettings, null);
    }

XML

    <sql id="querySubjectCostSql">
        SELECT
        #{item.account} AS account,
        fuunctionAA('123',
        0,
        t.accountId,
        to_date(#{item.endDate},
        'yyyymmdd')) AS cost
        FROM account_info t
        WHERE t.accountId= #{item.accountId}
    </sql>

    <select id="batchQuerySettingCost" resultType="com.www.aa.bb.model.BankCost"
            parameterType="com.www.aa.bb.model.CostSetting">
        SELECT tt.* from (
        <foreach collection="obj" index="index" item="item" open="(" separator="union all" close=")">
            <include refid="querySubjectBalanceSql">
            </include>
        </foreach>
        ) tt
    </select>

3.4.2 ：场景二

3.4.2.1 ：逻辑简述：

• 任务一逻辑查询出了符合条件的一组产品数据（字段 A,B,C）
• 任务二需要通过这组数据查询过滤掉不需要的数据（需要通过多个字段关联数据库的两个表判断每一条数据是否符合要求），
• 任务三需要需要将任务二过滤后的数据update-or-insert到数据库
  （难点，任务二难以只通过一条查询处理完批量条件）

    想要的结果：

    优化任务一的数据for循环处理，缩短响应时间

    思路：

    将任务一查询的数据变成一张表，再与另外两个表关联

3.4.2.2 ：逻辑代码

BankCountServiceImpl

    @Override
    public int queryProductAccount(Map cond) {
        List<CostSetting> costSettings = bankInfoDao.querySetting(cond);
        costSettings = batchBaseService.batchUtil("com.www.gg.costsetting.dao.CostSettingDao", "batchQueryProductCosts", costSettings, null);
        return batchBaseService.batchUtil("com.www.gg.costsetting.dao.CostSettingDao", "batchInsertProductCosts", costSettings, null);
    }

XML

    <select id="batchQueryProductCosts" resultType="com.www.aa.bb.model.CostSetting"
            parameterType="com.www.aa.bb.model.CostSetting">
        <![CDATA[
            SELECT a.aa            	AS aa,
                a.bb 				AS bb,
                a.cc             	AS cc,
                a.dd              	AS dd,
                a.ee                AS ee,
                a.ff              	AS ff,
                a.gg               	AS gg,
                DECODE(a.bb,
                        0,
                        NVL2(c.hh, NULL, '审批人不存在;'),
                        1,
                        NVL2(b.vv, NULL, '搞事情')) AS message
                FROM (
        ]]>
            <foreach collection="obj" index="index" item="item" open="(" separator="union all" close=")">
                    SELECT #{item.aa}       AS aa,
                        #{item.bb}         as bb,
                        #{item.cc}      as cc,
                        #{item.dd}            AS dd,
                        nvl(#{item.ee},0)  AS ee,
                        nvl(#{item.ff},0)    AS ff,
                        #{item.gg}         AS gg
                    from dual
            </foreach>
                ) a
            LEFT JOIN product_info b ON a.aa= b.aa AND instr(b.dd, '好小子') > 0
            LEFT JOIN ymb_product_setting c ON  a.gg= c.gg  and a.dd = c.dd
    </select>

<insert id="batchInsertProductCosts" parameterType="com.www.aa.bb.model.CostSetting">
        <![CDATA[
            merge into ymb_product_setting_abc  t
            using (
        ]]>
               <foreach collection="obj" index="index" item="item" open="(" separator="union all" close=")">
                    SELECT #{item.aa}                          AS aa,
                        (select bb
                           from product_info 
                           where bb= #{item.bb})         AS bb,
                        nvl(#{item.cc},
                            (select cc
                               from user_info
                                where bb= #{item.bb}))   AS cc,
                        #{item.ee}                                AS ee,
                        #{item.ff}                             AS ff
                  from dual
               </foreach>
                ) a
            on (t.cc= a.cc AND t.ee= a.ee and t.gg= 1 and NVL(t.bb,-1) = NVL(a.bb,-1))
            when matched then
                  UPDATE
                    set t.ee= a.ee,
                    t.ff= a.ff,
                    t.hh= sysdate
            when not matched THEN
                   insert
                     (aa, bb, cc, dd, ee, ff, ll)
                     VALUES
                     (a.aa,
                     a.bb,
                     a.cc,
                     a.dd,
                     SYSDATE,
                     a.ff,
                    1)

    </insert>

PS: 建议使用batchUtil的statement，建议统一命名风格，因为很可能执行时间过长而被扫描出来，统一命名后方便统一设置忽略白名单。

---

可以看出，以上方式在不考虑事务的情况下 基本能解决大部分问题。
但是批量操作，进行事务处理要谨慎，加@Transactional，前提是不能手动提交，有手动提交，就可以考虑返回成功失败条数
所以优化方向是可以设置统一实体类，实体类设置3个参数（按需设置）————成功数量；失败数量；失败数据集合。