平常使用mybatis时并没有考虑内存问题,但今天遇到一件事情是一次需要查询的数据量很大,直接导致系统oom,所以对需要大量查询的sql做了如下修改…
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
| @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()); 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方法处理
原理:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
| 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 方式
1 2 3 4 5
| <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
1
| <select id="selectByExampleForPetaData" fetchSize="-2147483648" resultMap="xxx">
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
| 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 {
myMyBatisCursorItemReader.close(); }
}
|
参考:
jdbc的流式查询 http://ifeve.com/mysq-stream-search/