更多内容请浏览本人博客
平常使用mybatis时并没有考虑内存问题,但今天遇到一件事情是一次需要查询的数据量很大,直接导致系统oom,所以对需要大量查询的sql做了如下修改...
直接上代码
@Autowired
private SqlSessionTemplate sqlSessionTemplate;
@Test
public void test() {
MerchantOrderPushExample merchantOrderPushExample = new MerchantOrderPushExample();
MerchantOrderPushExample.Criteria criteria = merchantOrderPushExample.createCriteria();
criteria.andIdIsNotNull();
merchantOrderPushExample.setOrderByClause(" id desc");
HashMap<String, Object> param = new HashMap<>();
param.put("oredCriteria", merchantOrderPushExample.getOredCriteria());
param.put("orderByClause", merchantOrderPushExample.getOrderByClause());
// param.put("limit",20); 测试分页的使用
sqlSessionTemplate.select("com.xws.business.mapper.MerchantOrderPushMapper.selectByExample", param, resultContext -> {
MerchantOrderPush resultObject = (MerchantOrderPush) resultContext.getResultObject();
System.out.println(JSON.toJSONString(resultObject));
});
}
这里使用了mybatis的流式查询,具体原理是内部直接操作 ResultContext 逐条获取数据并回调handler的handleResult方法处理
原理:
private void handleRowValuesForSimpleResultMap(ResultSetWrapper rsw, ResultMap resultMap, ResultHandler<?> resultHandler, RowBounds rowBounds, ResultMapping parentMapping)
throws SQLException {
DefaultResultContext<Object> resultContext = new DefaultResultContext<Object>();
skipRows(rsw.getResultSet(), rowBounds);
while (shouldProcessMoreRows(resultContext, rowBounds) && rsw.getResultSet().next()) {
ResultMap discriminatedResultMap = resolveDiscriminatedResultMap(rsw.getResultSet(), resultMap, null);
Object rowValue = getRowValue(rsw, discriminatedResultMap);
storeObject(resultHandler, resultContext, rowValue, parentMapping, rsw.getResultSet());
}
}
private void storeObject(ResultHandler<?> resultHandler, DefaultResultContext<Object> resultContext, Object rowValue, ResultMapping parentMapping, ResultSet rs) throws SQLException {
if (parentMapping != null) {
linkToParents(rs, parentMapping, rowValue);
} else {
callResultHandler(resultHandler, resultContext, rowValue);
}
}
//调用回调
@SuppressWarnings("unchecked" /* because ResultHandler<?> is always ResultHandler<Object>*/)
private void callResultHandler(ResultHandler<?> resultHandler, DefaultResultContext<Object> resultContext, Object rowValue) {
resultContext.nextResultObject(rowValue);
((ResultHandler<Object>) resultHandler).handleResult(resultContext);
}
结果对比:
图就不上了,使用非流式查询内存使用时线性增长的,使用流式查询后内存使用是平稳的.成功避免oom.
另外,mybatis流式查询还有一种方法:没有验证,
注入bean,springboot 可以使用 @Configration 方式
<bean id="myMyBatisCursorItemReader" class="org.mybatis.spring.batch.MyBatisCursorItemReader">
<property name="sqlSessionFactory" ref="sqlSessionFactory" />
<property name="queryId"
value="com.taobao.accs.mass.petadata.dal.sqlmap.AccsDeviceInfoDAOMapper.selectByExampleForPetaData" />
</bean>
在mapper.xml的sql上加上fetchSize="-2147483648" ,Integer.MIN_VALUE=-2147483648
<select id="selectByExampleForPetaData" fetchSize="-2147483648" resultMap="xxx">
static void testCursor1() throws UnexpectedInputException, ParseException, Exception {
try {
Map<String, Object> param = new HashMap<String, Object>();
AccsDeviceInfoDAOExample accsDeviceInfoDAOExample = new AccsDeviceInfoDAOExample();
accsDeviceInfoDAOExample.createCriteria().andAppKeyEqualTo("12345").andAppVersionEqualTo("5.7.2.4.5")
.andPackageNameEqualTo("com.test.zlx");
param.put("oredCriteria", accsDeviceInfoDAOExample.getOredCriteria());
// 设置参数
myMyBatisCursorItemReader.setParameterValues(param);
// 创建游标
myMyBatisCursorItemReader.open(new ExecutionContext());
//使用游标迭代获取每个记录
Long count = 0L;
AccsDeviceInfoDAO accsDeviceInfoDAO;
while ((accsDeviceInfoDAO = myMyBatisCursorItemReader.read()) != null) {
System.out.println(JSON.toJSONString(accsDeviceInfoDAO));
++count;
System.out.println(count);
}
} catch (Exception e) {
System.out.println("error:" + e.getLocalizedMessage());
} finally {
// do some
myMyBatisCursorItemReader.close();
}
}
参考
jdbc的流式查询 http://ifeve.com/mysq-stream-search/
