Result Set Processing
1.1 Introduction to ResultHandler
The ResultHandler in Mybatis is equivalent to the processor of the data result set. It is a callback function (Callback) used to process the results of each row of data. This callback function can be triggered when the query result is processed to a certain amount, and the result set data is customized.
The use of ResultHandler can greatly improve the efficiency of data processing. When we need to process a large amount of data, we generally use ResultHandler to process the results, avoiding the return of all the results in one query, wasting memory resources or causing OOM.
The ResultHandler interface has only one method handleResult, which we can implement to process the data returned by each SQL query.
1.2 ResultHandler implements result streaming output (two writing methods)
Mybatis：
<select resultMap="BaseResultMap">
SELECT
<include ref />
FROM tbl_test_user
</select>
Mapper:
package ;
import ;
import ;
public interface TblTestUserMapper {
// The SQL return value is void, so we did not receive this return value. So no large object is produced // The first parameter in the method is the limiting condition during query, and the second is the result processor void selectByResultHandler(@Param(value = "actNo") String actNo,
ResultHandler resultHandler);
}
Implementing ResultHandler:
package ;
import .slf4j.Slf4j;
import ;
import ;
import ;
@Slf4j
public class TestResultHandler implements ResultHandler<TblTestUser> {
@Override
public void handleResult(ResultContext<? extends TblTestUser> resultContext) {
TblTestUser tblTestUser = ();
// TODO The logic of processing data try {
// Print each line of log ("userNo : [{}]", ());
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}Service:
package ;
import .slf4j.Slf4j;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
@Service
@Slf4j
public class TestService {
@Resource
private TblTestUserMapper tblTestUserMapper;
/**
* Writing method 1
*
* @param actNo Filter: Activity No
*/
@Async
public void test1(String actNo) {
("ResultHandler1 handles query results");
TestResultHandler testResultHandler = new TestResultHandler();
(actNo, testResultHandler);
}
/**
* Writing method 2
*
* @param actNo Filter: Activity No
*/
@Async
public void test2(String actNo) {
("ResultHandler2 handles query results");
(actNo, new ResultHandler<TblTestUser>() {
@Override
public void handleResult(ResultContext<? extends TblTestUser> resultContext) {
TblTestUser tblTestUser = ();
// Logic for processing data try {
// Print each line of log ("userNo : [{}]", ());
} catch (Exception e) {
throw new RuntimeException(e);
}
}
});
}
}
2. Cursor
MyBatis 3.2 and above support cursors, allowing the result set to be processed line by line. It provides a calledThe interface class is used for streaming query, and this interface inheritsandInterface, from this we can see:
Cursor is closed;
Cursor is traversable.
In addition, Cursor provides three methods:
isOpen(): Used to determine whether the Cursor object is open before fetching data. Cursor can only retrieve data when it is turned on;isConsumed(): Used to determine whether all the query results have been retrieved.getCurrentIndex(): Return how many pieces of data have been retrieved
Because Cursor implements an iterator interface, it is very simple to retrieve data from Cursor in actual use. In the Mapper interface, by specifying that the return value of the Mapper method is Cursor type, MyBatis knows that this query method is a streaming query.
(rowObject -> {...}); 2.1 Building Cursor
Mapper：
@Mapper
public interface FooMapper {
@Select("select * from foo limit #{limit}")
Cursor scan(@Param("limit") int limit);
}2.1.1 Error Demonstration
@GetMapping("foo/scan/0/{limit}")
public void scanFoo0(@PathVariable("limit") int limit) throws Exception {
try (Cursor cursor = (limit)) { // 1
(foo -> {}); // 2
}
} illustrate:
In the above code, fooMapper comes in with @Autowired.
Comment 1 calls the scan method to get the Cursor object and ensure that it can be closed in the end;
Note 2 is to get data from cursor.
The above code looks fine, but an error will be reported when executing scanFoo0():
: A Cursor is already closed.
This is because we mentioned earlier that the database connection needs to be maintained during the process of fetching data, and the Mapper method usually closes the connection after execution, so Cusor is also closed.
Therefore, the idea of solving this problem is not complicated, just keep the database connection open. We have at least three options to choose from.
2.1.2 Correct demonstration
SqlSessionFactory
We can use SqlSessionFactory to manually open database connections:
@GetMapping("foo/scan/1/{limit}")
public void scanFoo1(@PathVariable("limit") int limit) throws Exception {
try (
SqlSession sqlSession = (); // 1
Cursor cursor =
().scan(limit) // 2
) {
(foo -> { });
}
}In the above code, in 1 we open a SqlSession (actually also represents a database connection) and ensure that it can be closed in the end; in 2 we use SqlSession to obtain the Mapper object. This is the only way to ensure that the obtained Cursor object is open.
TransactionTemplate
In Spring, we can use TransactionTemplate to execute a database transaction, and the database connection is also open during this process:
@GetMapping("foo/scan/2/{limit}")
public void scanFoo2(@PathVariable("limit") int limit) throws Exception {
TransactionTemplate transactionTemplate =
new TransactionTemplate(transactionManager); // 1
(status -> { // 2
try (Cursor cursor = (limit)) {
(foo -> { });
} catch (IOException e) {
();
}
return null;
});
}In the above code, in 1 we created a TransactionTemplate object (there is no need to explain how transactionManager comes from here. This article assumes that readers are familiar with the use of Spring database transactions), and in 2, we execute database transactions, and the content of the database transaction is a stream query that calls the Mapper object. Note that the Mapper object here does not need to be created through SqlSession.
@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 cursor = (limit)) {
(foo -> { });
}
}It just added a one on the original method@TransactionalAnnotation. This solution looks the simplest, but please note the pitfall used in the Spring framework for annotations: only takes effect when external calls are called. Calling this method in the current class will still report an error.
This is the end of this article about the implementation example of streaming query in Mybatis. For more related Mybatis streaming query content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!