Mybatis流式查询

存有一个需要遍历处理的需求，因数据量过大不能一次性将结果集装入客户端内存，就可以考虑使用流式查询或者分页查询, 本文仅讨论流式查询.

话不多说  直接上代码

方式一: 基于mybatis的游标规则  类似于oracle的fetchsize缓冲区

mapper层:

package com.abi.mapper;

import com.abi.entity.WholesalerBase;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.Select;

import org.apache.ibatis.mapping.ResultSetType;
import org.apache.ibatis.session.ResultHandler;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface WholesalerBaseMapper extends BaseMapper<WholesalerBase> {

    /**
     * 流式查询
     * @param resultHandler
     */
    @ResultType(WholesalerBase.class)
    @Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000)
    void getBaseList(ResultHandler<WholesalerBase> resultHandler);


    /**
     * 流式查询  查询上一周的更新数据
     * @param resultHandler
     */
    @ResultType(WholesalerBase.class)
    @Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000)
    void getBaseWeekList(ResultHandler<WholesalerBase> resultHandler);


    /**
     * 获取指定状态奖励的结算记录
     * 流式查询
     */
    @ResultType(TaskSettleLogDTO.class)
    @Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000)
    void getNotIssuedCycleSettleLogs(
            @Param("rewardStatuses") List<Integer> rewardStatuses,
            @Param("cycleType") Integer cycleType,
            @Param("starTime") LocalDateTime starTime,
            @Param("endTime") LocalDateTime endTime,
            ResultHandler<TaskSettleLogDTO> resultHandler
    );

}

xml层

 <select id="getBaseWeekList" resultType="com.abi.entity.WholesalerBase">
        SELECT base.name        name,
               base.id          id,
               base.code,
               address,
               base.boss_name   bossName,
               base.boss_mobile bossMobile,
               latitude,
               longitude,
               base.create_time createTime,
               base.update_time updateTime
        FROM wholesaler_base base
        WHERE base.verify_status != 3 and base.status !=99 and YEARWEEK(date_format(base.update_time,'%Y-%m-%d'), 1) = YEARWEEK(now(), 1)-1
                and base.deleted=0 and base.level=2
    </select>


<select id="getNotIssuedCycleSettleLogs" resultType="com.abi.common.dto.TaskSettleLogDTO">
        select settle.id as taskSettleLogId,
        settle.task_code,
        settle.task_cycle_id,
        settle.satisfied,
        task.id,
        task.`code`,
        task.task_type,
        task.biz_code,
        task.biz_name,
        task.processor_code,
        task.processor_type,
        task.executor_target_code
        task.enable_time,
        cycle.end_time as cycleEndTime,
        from task_settle_log as settle
        left join task_cycle as cycle on cycle.id = settle.task_cycle_id
        left join task on task.`code` = settle.task_code
        where settle.reward_status in
        <foreach collection="rewardStatuses" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
        and cycle.cycle_type = #{cycleType}
        and cycle.end_time between #{starTime} and #{endTime}
        and task.`enable` = 1
        and task.deleted = 0
    </select>

service层

package com.abi.service.impl;



@Slf4j
@Service
@RequiredArgsConstructor
public class WholesalerBaseServiceImpl extends ServiceImpl<WholesalerBaseMapper, WholesalerBase> implements WholesalerBaseService {


    @Override
    @Transactional(readOnly = true)
    public void fixReuseSendMsgNew() {
        this.baseMapper.getBaseWeekList(resultContext -> {
           WholesalerBase base = resultContext.getResultObject();
                try {
                     //逻辑代码
                     log.info("base-->{}",base);
                } catch (Exception e) {
                    log.error("XX发生错误,原因 = {}" e.getMessage());
                    e.printStackTrace();
                }
        });
    }
}



    @Override
    @Transactional(readOnly = true)
    public void scheduleGenerate() {

        // 流式查询
        // 获取指定状态奖励的[月周期]结算记录
        LocalDateTime now = LocalDateTime.now();
        this.taskSettleLogMapper.getNotIssuedCycleSettleLogs(
                Arrays.asList(RewardLogStatusEnum.TO_BE_ISSUED.getCode(), RewardLogStatusEnum.MONTH_CYCLE_FAIL.getCode()),
                TaskCycleTypeEnum.MONTH.getCode(),
                now.minusMonths(1),
                now,
                (resultContext) -> {
                    TaskSettleLogDTO taskSettleLog = resultContext.getResultObject();
                    try {

                        if (taskSettleLog.getEnableTime() != null
                                && taskSettleLog.getCycleEndTime().isBefore(taskSettleLog.getEnableTime())
                        ) {
                            // 如果结算月周期的结束时间 < 启用时间
                            // 不会发奖，并且改变相关记录的状态
                            this.taskRewardLogService.updateNoIncome(taskSettleLog);
                        } else {
                            this.taskRewardLogService.awardPrizes(taskSettleLog);
                        }

                    } catch (Exception e) {
                        log.error("发奖发生错误 = {}", taskSettleLog, e);
                    }

                }
        );

    }

基于Cursor构建的方式 

方式二: SqlSession 方式

mapper层

    @Select("select * from wholesaler_base base WHERE base.verify_status != 3 " +
            "                and base.deleted=0 and base.level=2")
    Cursor<WholesalerBase> scan();

测试程序

    @Test
    public void t5() {
        SqlSession sqlSession = null;
        try {
            sqlSession = this.sqlSessionFactory.openSession();
            Cursor<WholesalerBase> cursors =                                         
            sqlSession.getMapper(WholesalerBaseMapper.class).scan();
            cursor.forEach(base -> {
                    //业务需求
                    System.out.println("base = " + base);
                });
        } finally {
            if (sqlSession != null) {

                sqlSession.close();
            }
        }
    }

方式三: TransactionTemplate

测试程序

    @Resource
    private PlatformTransactionManager platformTransactionManager;    


    @Test
    void t3() {
        TransactionTemplate transactionTemplate = new TransactionTemplate(platformTransactionManager);

        transactionTemplate.execute(status -> {
            try (Cursor<WholesalerBase> cursor = wholesalerBaseMapper.scan()) {
                cursor.forEach(base -> {
                    //需求代码
                    System.out.println("base = " + base);
                });
            } catch (Exception e) {
                e.printStackTrace();
            }
            return null;
        });
    }

方式四: @Transactional 注解

    @Test
    @Transactional(readOnly = true)
    void t4() {
        try (Cursor<WholesalerBase> cursor = wholesalerBaseMapper.scan()) {
            cursor.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

流式查询中满1000条数据清空

    @Transactional(readOnly = true)
    public void test(Set<String> codes) {

        StopWatch stopWatch = new StopWatch();

        stopWatch.start("poc");

        int num = 1000;

        Set<String> pocMiddleIdList = new HashSet<>();
        Map<String, String> pocMiddleIdOrgMap = new HashMap<>();

        this.pocBaseInfoMapper.getPocBaseInfoList(codes, (resultContext) -> {
            PocBaseInfo resultObject = resultContext.getResultObject();
            System.out.println(JSONUtil.toJsonStr(resultObject));

            pocMiddleIdList.add(resultObject.getPocMiddleId());
            pocMiddleIdOrgMap.put(resultObject.getPocMiddleId(), resultObject.getOrgCode());
            if (pocMiddleIdList.size() == num) {
                //todo
                pocMiddleIdList.clear();

            }
        });
        stopWatch.stop();
        //测试进行时间
        System.out.println(stopWatch.prettyPrint());
    }

读取Classpath下的资源为字符串，使用UTF-8编码  转为字符串集合

    @Test
    void test() {

        //读取的文件位于resources 下test-codes.json
        String testCodesJson = ResourceUtil.readUtf8Str("test-codes.json");
        List<String> codes = JSONUtil.toList(testCodesJson, String.class);
        System.out.println(codes);
    }