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

This article mainly introduces how to achieve Mybatis streaming query interface, the article is very detailed, has a certain reference value, interested friends must read it!

If there is no streaming query, when we want to fetch 10 million records from the database and do not have enough memory, we have to page the query, and the efficiency of the paging query depends on the table design. If the design is not good, we will not be able to execute efficient paging queries. Therefore, streaming query is a necessary function of a database access framework.

During the process of streaming query, the database connection remains open, so it should be noted that after executing a streaming query, the database access framework is not responsible for closing the database connection, and the application needs to close itself after fetching the data.

MyBatis streaming query interface

MyBatis provides an interface class called org.apache.ibatis.cursor.Cursor for streaming queries, which inherits the java.io.Closeable and java.lang.Iterable interfaces.

Cursor can be turned off. In fact, when you close Cursor, you also close the database connection.

Cursor is ergodic.

In addition, Cursor provides three methods:

IsOpen (): used to determine whether the Cursor object is open before fetching the data. Cursor can fetch data only when it is opened

IsConsumed (): used to determine whether all the query results have been fetched

GetCurrentIndex (): returns how many pieces of data have been fetched.

Because Cursor implements the iterator interface, in practice, fetching data from Cursor is very simple:

Try (Cursor cursor = mapper.querySomeData ()) {cursor.forEach (rowObject-> {/ /...});}

Using try-resource mode, you can make Cursor shut down automatically.

But the process of building Cursor is not simple.

Let's give a practical example. Here is a Mapper class:

Mapperpublic interface FooMapper {@ Select ("select * from foo limit # {limit}") Cursor scan (@ Param ("limit") int limit);}

The method scan () is a very simple query. When we define this side, we specify that the return value is of type Cursor, and MyBatis understands that the query method is a streaming query.

Then we write a SpringMVC Controller method to call Mapper (the extraneous code has been omitted):

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

Suppose fooMapper came in @ Autowired. Note 1 is to get the Cursor object and make sure it is finally closed; 2 is to fetch data from the cursor.

The above code looks fine, but an error occurs when executing scanFoo0 (int):

Java.lang.IllegalStateException: A Cursor is already closed.

This is because we said earlier that the database connection needs to be maintained while fetching the data, and the Mapper method usually closes the connection after execution, so Cusor is also closed.

Therefore, the idea to solve this problem is not complicated, just keep the database connection open. We have at least three options.

Option 1: SqlSessionFactory

We can manually open the database connection with SqlSessionFactory and modify the Controller method as follows:

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

In the above code, 1 where we open a SqlSession (which actually represents a database connection) and make sure it closes eventually; 2 places we use SqlSession to get the Mapper object. This ensures that the resulting Cursor object is open.

Option 2: TransactionTemplate

In Spring, we can use TransactionTemplate to perform a database transaction, during which the database connection is also open. 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 cursor = fooMapper.scan (limit)) {cursor.forEach (foo-> {});} catch (IOException) {e.printStackTrace () } return null;});}

In the above code, 1 we created a TransactionTemplate object (here there is no need to explain where transactionManager came from, this article assumes that the reader is familiar with the use of Spring database transactions), 2 places to execute database transactions, and the contents of database transactions are streaming queries that call Mapper objects. Note that the Mapper object here does not need to be created through SqlSession.

Scenario 3: @ Transactional annotation

This is essentially the same as scenario 2, with the following code:

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

It simply adds an @ Transactional annotation to the original method. This scheme looks the simplest, but note the pit used by the annotations in the Spring framework: it only takes effect when called externally. Calling this method in the current class will still report an error.

The above is all the contents of the article "how to implement Mybatis streaming query Interface". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.