Implementation of simple mybatis cursor query

background

• In the actual project development, we often need to query the relevant records in the database through a certain association.
• If the front-end interface with paging is OK, but the problems caused by deep paging should also be considered.
• Sometimes, the amount of production data is not estimated, which leads to too much single query and hits the program to oom.

problem

• When checking the production log, the author found that a query with conditions returned 70W + records, resulting in mybaits direct oom.
• Related logic is to query all child records according to a parent ID, and then traverse the child records for related business logic operations

Related code

• There is a method in the Dao layer of the project

import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.mapping.ResultSetType;

public interface  xxxDao extends BaseDao{
    #Query the child records according to the parent appid, and use the options annotation to mark it as a cursor query. 10000 records are queried each time
    @Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 10000)
    List<xxxBean> selectByAppId(@Param("appId") Long appId);
}

• In the service layer of the project

import org.apache.ibatis.cursor.Cursor;
import org.apache.ibatis.session.SqlSession;
import org.mybatis.spring.SqlSessionTemplate;

    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;

public void test1() throws IOException {
        List<xxxBean> list= new ArrayList<>();
        SqlSession sqlSession =sqlSessionTemplate.getSqlSessionFactory().openSession();
        //90137l indicates the query parameters
        Cursor<HdfsPathRecord> cursor = sqlSession.selectCursor(xxxDao.class.getName() + ".selectByAppId",90137L);
       Iterator iter = cursor.iterator();
        while (iter.hasNext()) {
          xxxBean obj = (xxxBean) iter.next();
         //Todo: next, you can execute the actual logic according to obj or put obj into a list
        }
        cursor.close();
        sqlSession.close();
}

• If you have any questions or questions, please leave a message or send me a private letter