编程知识 cdmana.com

New skills mybatis ten million data table, fast pagination!

Python The actual combat community

Java The actual combat community

Long press to identify the QR code below , Add as needed

Scan code, pay attention to add customer service

Into the Python community ▲

Scan code, pay attention to add customer service

Into the Java community

The author's fabricated belief

source :

https://segmentfault.com/a/1190000022478915

Basic concepts

A stream query means that after a successful query, it returns an iterator instead of a collection , The application takes one query result from the iterator at a time . The advantage of streaming query is that it can reduce memory usage .

If there is no streaming query , We want to get... From the database 1000 Million records without enough memory , You have to query by page , The efficiency of paging query depends on the table design , If the design is not good , You can't perform efficient paging queries . Therefore, streaming query is a necessary function of a database access framework .

In the process of streaming query , The database connection remains open , So it's important to note that : After executing a streaming query , The database access framework is not responsible for closing the database connection , The application needs to close itself after fetching the data .

MyBatis Streaming query interface

MyBatis A name is provided  org.apache.ibatis.cursor.Cursor  Is used for streaming queries , This interface inherits java.io.Closeable and java.lang.Iterable Interface , Thus we can see that :

  1. Cursor It can be turned off ;

  2. Cursor It's ergodic .

besides ,Cursor Three methods are also provided :

  • isOpen(): Used to judge before data retrieval Cursor Is the object open . Only when on Cursor To access data ;

  • isConsumed(): It is used to judge whether the query results are all fetched .

  • getCurrentIndex(): Returns how many pieces of data have been obtained

because Cursor Implemented the iterator interface , Therefore, in the actual use , from Cursor It's very easy to get the data :

cursor.forEach(rowObject -> {...});

But build Cursor The process is not simple

Let's take a practical example . Here's a Mapper class :

@Mapper
public interface FooMapper {
    @Select("select * from foo limit #{limit}")
    Cursor<Foo> scan(@Param("limit") int limit);
}

Method scan() Is a very simple query . By designation Mapper The return value of the method is Cursor type ,MyBatis This query method is known as a stream query .

And then we write another SpringMVC Controller Method to call Mapper( Irrelevant code has been omitted ):

@GetMapping("foo/scan/0/{limit}")
public void scanFoo0(@PathVariable("limit") int limit) throws Exception {
    try (Cursor<Foo> cursor = fooMapper.scan(limit)) {  // 1
        cursor.forEach(foo -> {});                      // 2
    }
}

In the above code ,fooMapper yes @Autowired Come in . notes 1 In the call scan Method , obtain Cursor Object and ensure that it is finally closed ;2 From the cursor Take data from .

The code above looks ok , But to perform scanFoo0() When an error :

java.lang.IllegalStateExceptionA Cursor is already closed.

This is because we mentioned earlier that the database connection needs to be maintained during the data fetching process , and Mapper The connection is usually closed after the method is executed , therefore Cusor It's also closed .

therefore , The solution to this problem is not complicated , Keep the database connection open . We have at least three options .

Scheme 1 :SqlSessionFactory

We can use SqlSessionFactory To manually open the database connection , take Controller The method changes as follows :

@GetMapping("foo/scan/1/{limit}")
public void scanFoo1(@PathVariable("limit") int limit) throws Exception {
    try (
        SqlSession sqlSession = sqlSessionFactory.openSession();  // 1
        Cursor<Foo> cursor = 
              sqlSession.getMapper(FooMapper.class).scan(limit)   // 2
    ) {
        cursor.forEach(foo -> { });
    }
}

In the above code ,1 We opened one SqlSession ( It actually represents a database connection ), And make sure it turns off in the end ;2 Where we use SqlSession To obtain a Mapper object . That's what you can guarantee Cursor Object is open .

Option two :TransactionTemplate

stay Spring in , We can use TransactionTemplate To execute a database transaction , The database connection is also open during this process . The code is as follows :

@GetMapping("foo/scan/2/{limit}")
public void scanFoo2(@PathVariable("limit") int limit) throws Exception {
    TransactionTemplate transactionTemplate = 
            new TransactionTemplate(transactionManager);  // 1

    transactionTemplate.execute(status -> {               // 2
        try (Cursor<Foo> cursor = fooMapper.scan(limit)) {
            cursor.forEach(foo -> { });
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    });
}

In the above code ,1 We created a TransactionTemplate object ( here transactionManager How did it come from? Don't explain it , This paper assumes that the readers are Spring I am familiar with the use of database transactions ),2 Perform database transactions , The content of a database transaction is a call Mapper Stream query of object . Notice the Mapper Object does not need to pass through SqlSession establish .

Option three :@Transactional annotation

This is essentially the same as scheme 2 , The code is as follows :

@GetMapping("foo/scan/3/{limit}")
@Transactional
public void scanFoo3(@PathVariable("limit") int limit) throws Exception {
    try (Cursor<Foo> cursor = fooMapper.scan(limit)) {
        cursor.forEach(foo -> { });
    }
}

It just adds one to the original method  @Transactional  annotation . This scheme seems the most concise , But please pay attention to Spring Holes used for annotations in frames : Only takes effect on external calls . Invoke this method in the current class , Still report a mistake .

These are three implementations MyBatis The method of stream query .

author |  A fabricated belief

source | https://segmentfault.com/a/1190000022478915

 Programmer column   Scan code and pay attention to customer service   Press and hold to recognize the QR code below to enter the group 


Recent highlights are recommended :  

  Another programmer , Caught !( Real events )

  What kind of experience is it for a programmer to have a cute girlfriend ?

 “12306” How awesome is the architecture of ?

 csv A simple solution to the problem of file reading and writing garbled code


Here's a look Good articles to share with more people ↓↓

版权声明
本文为[osc_ ee3ody6v]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201224131149440q.html

Scroll to Top