流式查询处理器
最近,平台做一个大数据量查询并逻辑处理最终同步,由于数量庞大导致每次查询消耗内存,最终选择流式查询批量处理数据。
/**
* 流式查询处理器(案例)
*
* @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);
}
}