Shulou(Shulou.com)06/01 Report--

This article mainly explains "what is the method of querying a large amount of data by Mybatis cursors". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. please follow the editor's train of thought to study and learn "what is the method of querying a large amount of data by Mybatis cursors".

Mybatis cursors query large amounts of data

When dealing with a large amount of data, in order to prevent memory leakage, mybatis plus cursors are used for data query processing. When querying millions of data, using cursors can save memory consumption, do not need to take out all the data at once, and can be processed one by one or partially taken out in batches.

Mapper layer

Using Cursor types for data reception

@ Options,fetchSize set to the minimum value of Integer

@ Select, write query sql

@ Options (resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = Integer.MIN_VALUE) @ Select ("select domain from illegal_domain where icpstatus! = # {icpstatus}") Cursor getDayJobDomain (@ Param ("icpstatus") Integer icpstatus); service layer Cursor domainList = illegalDomainMapper.getDayJobDomain (1)

Data processing.

ForEach mode

DomainList.forEach (illegalDomain-> {/ / processing logic, Future future = checkIcpThreadPool.submit (new IcpCheckThread (illegalDomain.getDomain (), configMap)); results.add (future);})

Iterator

Iterator iter = domainList.iterator (); while (iter.hasNext ()) {/ / processing logic, complete Future future = checkIcpThreadPool.submit (new IcpCheckThread (illegalDomain.getDomain (), configMap)); results.add (future);} resource release according to business requirements

After use, release resources in the finally block, otherwise the cursor does not close may also cause memory overflow problems

Try {/ / your code} catch (Exception e) {log.error (e);} finally {if (null! = domainList) {try {domainList.close ();} catch (IOException e) {e.printStackTrace ();} Mybatis cursor usage summary

When querying millions of data, OOM (OutOfMemoryException) occurs when querying all the data and putting it into memory. Using cursors can save memory consumption. You don't need to retrieve all the data at once, and you can process or batch process part of the data one by one. In this scenario, you can use the concept of cursors to solve this problem.

What is a cursor?

Cursor is a method of processing data. In order to view or process the data in the result set, the cursor provides the ability to browse the data forward or backward one or more rows at a time.

Demo:

/ / the first @ Options (resultSetType = ResultSetType.FORWARD_ONLY) @ Select ("SELECT * FROM department WHERE status = 0") List queryDepartmentAll (); / / the second version does not support the @ select annotation, but has been fixed in version 3.4.1: @ Options (resultSetType = ResultSetType.FORWARD_ONLY) @ Select ("SELECT * FROM department WHERE status = 0") Cursor cursorQueryDepartmentAll (); @ Servicepublic class DepartmentServiceImpl implements DepartmentService {private static final Logger LOG = LoggerFactory.getLogger (DepartmentServiceImpl.class) @ Autowired private DepartmentDao departmentDao; @ Autowired private SqlSessionTemplate sqlSessionTemplate; public List getDepartmentList (DepartmentSearchParam param) {Cursor cursor = null; SqlSession sqlSession = null; try {sqlSession = sqlSessionTemplate.getSqlSessionFactory () .openSession (); cursor = sqlSession.selectCursor (DepartmentDao.class.getName () + ".queryDepartmentAll") Cursor.forEach (e-> {/ / processing logic}); / / you can also use iterators: Iterator iterator = cursor.iterator ();} catch (Exception e) {e.printStackTrace () } finally {if (null! = cursor) {try {cursor.close ();} catch (Exception e) {LOG.error (e.getMessage (), e) }} if (null! = sqlSession) {try {sqlSession.close ();} catch (Exception e) {LOG.error (e.getMessage (), e);}

The cursor of the ResultSet.TYPE_FORWORD_ONLY result set can only scroll down.

The cursor of the ResultSet.TYPE_SCROLL_INSENSITIVE result set can move up and down, and when the database changes, the current result set remains the same.

ResultSet.TYPE_SCROLL_SENSITIVE returns a scrollable result set, and when the database changes, the current result set changes synchronously.

Note: cursors can be moved back and forth. If resultSetType = TYPE_SCROLL_INSENSITIVE, you can set the cursor to move back and forth.

To ensure that Mybatis can move back and forth, Mybatis keeps the previously queried data in memory all the time.

So OOM can't be solved fundamentally, so we need to set it to @ Options (resultSetType = ResultSetType.FORWARD_ONLY) (actually the default is ResultSetType.FORWARD_ONLY).

Thank you for your reading, the above is the content of "what is the method of querying a large amount of data by Mybatis cursors". After the study of this article, I believe you have a deeper understanding of what the method of querying a large amount of data by Mybatis cursors is, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.