【mybatis】大数据流式查询(案例)

流式查询处理器

最近，平台做一个大数据量查询并逻辑处理最终同步，由于数量庞大导致每次查询消耗内存，最终选择流式查询批量处理数据。

/**
 * 流式查询处理器(案例)
 *
 * @author lucky
 * @date 2022/10/14 10:44
 * @description 流式查询处理器(案例)
 **/
public class DynamicBigDataHandler implements ResultHandler<Map<String, Object>> {
	private static Logger logger = LoggerFactory.getLogger(DynamicBigDataHandler.class);
	private static final int QUERY_BATCH_SIZE = 1000; 	// 查询默认批量大小
	private static final int HANDLE_BATCH_SIZE = 1000;  // 处理批量大小
	private long total;
	private int curSize;
	private List<Map<String, Object>> resultList;
	private DynamicBigDataMapper dynamicBigDataMapper;

	/**
	 * 构造
	 */
	public DynamicBigDataHandler(DynamicBigDataMapper dynamicBigDataMapper) {
		this.resultList = new ArrayList<Map<String, Object>>();
		this.dynamicBigDataMapper = dynamicBigDataMapper;
	}

	@Override
	public void handleResult(ResultContext<? extends Map<String, Object>> resultContext) {
		resultList.add(resultContext.getResultObject());
		curSize++;
		total++;
		if (curSize == QUERY_BATCH_SIZE) {
			handle();
		}
	}

	/**
	 * 针对批量结果集进行处理 TODO
	 */
	public void handle() {
		//忽略，根据实际业务需要处理resultList
		try {
			List<List<Map<String, Object>>> lists = Lists.partition(resultList, HANDLE_BATCH_SIZE);
			if (CollectionUtils.isEmpty(lists)) {
				return;
			}
			for (int i = 0, j = lists.size(); i < j; i++) {
				List<Map<String, Object>> list = lists.get(i);
				System.out.println("===>针对批量结果集进行处理:" + list.size());
			}
		} catch (Exception e) {
			logger.error("异常:{}", e.getMessage(), e);
		} finally {
			curSize = 0;
			resultList.clear();  //  结果集清空
		}
	}

	/**
	 * 处理最后未达到的最后数据batchSize
	 */
	public void end() {
		handle();
	}

	/**
	 * 获取处理总记录数
	 *
	 * @return
	 */
	public long getTotal() {
		return total;
	}
}

Mapper接口

import cn.hutool.json.JSONObject;
import io.lettuce.core.dynamic.annotation.Param;
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 java.util.List;
import java.util.Map;

/**
 * 大数据流式查询Mapper接口
 *
 * @author lucky
 * @date 2022-10-14
 * @description 大数据流式查询Mapper接口
 */
public interface PigDynamicBigDataMapper {

	@Select("${sql}")
	//这个注解是设定每次流式查询的iterator大小的，这里是1000条
	//ResultSetType.FORWARD_ONLY 只允许游标向下移动
	@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 5)
	//这个注解是标明返回结果集类型的
	@ResultType(Map.class)//cn.hutool.json.JSONObject
	List<Map<String, Object>> dynamicSelectLargeData(@Param("sql") String sql, JSONObject RsqDSN);

	/**
	 * 动态查询大数据
	 *
	 * @param sql     sql脚本:select * from test where 1=1
	 * @param handler 处理
	 * @return
	 */
	@SuppressWarnings("MybatisXMapperMethodInspection")
	void dynamicBigData(@Param("sql") String sql, ResultHandler<Map<String, Object>> handler);

	/**
	 * 动态查询大数据
	 *
	 * @param map     对象，包含sql脚本和param参数
	 *                sql脚本:select * from test where 1=1 and ID = #{ID}
	 *                参数   :"ID":"10001"
	 * @param handler 处理
	 * @return
	 */
	@SuppressWarnings("MybatisXMapperMethodInspection")
	void dynamicBigData(Map map, ResultHandler<Map<String, Object>> handler);
}

Mapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.pigcloud.form.layer.mapper.DynamicBigDataMapper">
	<select id="dynamicBigData" parameterType="Map" resultType="Map" resultSetType="FORWARD_ONLY" fetchSize="500">
		${sql}
	</select>
</mapper>

控制类

/**
 * @author lucky
 */
@Slf4j
@RestController
@RequestMapping("/api/pig")
public class SyncDataController extends BaseController {

	@Resource
	private DynamicBigDataMapper dynamicBigDataMapper;

	/**
	 * 动态查询大数据查询
	 * <p>
	 * /api/pig/dynamicBigData
	 *
	 * @return {@link SpElRegular}
	 * @throws Exception
	 * @Resource private DynamicBigDataMapper dynamicBigDataMapper;
	 */
	@PostMapping("/dynamicBigData")
	public ActionResult dynamicBigData(@RequestBody DynamicParamVO paramVO) {
		String sql = paramVO.getSql();
		if (StringUtils.isBlank(sql)) {
			return apiErrorReturn(500, "参数不能为空", "参数不能为空");
		}
		sql = sql.replace("delete", "").replace("'", "").replace("update", "");
		Map<String, Object> param = new HashMap();
		if (paramVO.getParam() != null) {
			param = paramVO.getParam();
		}
		DynamicBigDataHandler dynamicBigDataHandler = new DynamicBigDataHandler(dynamicBigDataMapper);
		param.put("sql", sql);
		dynamicBigDataMapper.dynamicBigData(param, dynamicBigDataHandler);
		dynamicBigDataHandler.end();//处理最后未达到batchSize的最后数据
		long handleTotal = dynamicBigDataHandler.getTotal();
		return apiSuccessReturn(handleTotal);
	}
}

查询结果