Improvements to the ibatis paging function (1)

Today, I accidentally saw an article about this. I think it is a good article on improving ibatis paging on the Internet. Here I will repeat it and hope it can be used by more people, I also hope that others can contribute better solutions!

Enable ibatis to support hibernate physical Paging

For a long time, ibatis paging is implemented through rolling resultset. It should be regarded as logical paging. Although logical paging can be very clean and independent from specific databases, it is not as efficient as physical paging supported by specific databases in most cases, while hibernate directly assembles SQL pages, make full use of the paging mechanism of a specific database, and the efficiency is relatively high. This article describes how to introduce a hibernate physical paging mechanism for ibatis without re-compiling the ibatis source code.

The basic idea is to find the place where ibatis executes the SQL statement, intercept the SQL statement, and re-assemble the SQL statement. By analyzing the ibatis source code, the class responsible for SQL Execution is com.ibatis.sqlmap.engine.exe cution. sqlexecutor. This class does not implement any interfaces, which is somewhat regrettable because the interfaces are stable contracts and are not updated in major versions, the class is relatively easy to change, so the code here can only be effective for the ibatis of the current version (2.1.7. The following describes how sqlexecutor executes a query:

/**
* Long Form of the method to execute a query
*
* @ Param request-the request Scope
* @ Param Conn-The database connection
* @ Param SQL-the SQL statement to execute
* @ Param parameters-the parameters for the statement
* @ Param skipresults-the number of results to skip
* @ Param maxresults-the maximum number of results to return
* @ Param callback-the row handler for the query
*
* @ Throws sqlexception-if the query fails
*/
Public void executequery (requestscope request, connection Conn, string SQL, object [] parameters,
Int skipresults, int maxresults, rowhandlercallback callback)
Throws sqlexception {
Errorcontext = request. geterrorcontext ();
Errorcontext. setactivity ("executing query ");
Errorcontext. setobjectid (SQL );

Preparedstatement PS = NULL;
Resultset rs = NULL;

Try {
Errorcontext. setmoreinfo ("Check the SQL statement (preparation failed ).");

Integer rstype = request. getstatement (). getresultsettype ();
If (rstype! = NULL ){
PS = conn. preparestatement (SQL, rstype. intvalue (), resultset. concur_read_only );
} Else {
PS = conn. preparestatement (SQL );
}

Integer fetchsize = request. getstatement (). getfetchsize ();
If (fetchsize! = NULL ){
PS. setfetchsize (fetchsize. intvalue ());
}

Errorcontext. setmoreinfo ("Check the parameters (set parameters failed ).");
Request. getparametermap (). setparameters (request, PS, parameters );

Errorcontext. setmoreinfo ("Check the statement (query failed ).");

Ps.exe cute ();
Rs = getfirstresultset (PS );

If (RS! = NULL ){
Errorcontext. setmoreinfo ("check the results (failed to retrieve Results ).");
Handleresults (request, RS, skipresults, maxresults, callback );
}

// Clear out remaining results
While (PS. getmoreresults ());

} Finally {
Try {
Closeresultset (RS );
} Finally {
Closestatement (PS );
}
}

}

 

Handleresults (request, RS, skipresults, maxresults, callback) is used to process pages. In fact, the query has been executed, so you do not have to worry about the handleresults method, let's take a look at the implementation of handleresults:

Private void handleresults (requestscope request, resultset RS, int skipresults, int maxresults, rowhandlercallback callback) throws sqlexception {
Try {
Request. setresultset (RS );
Resultmap = request. getresultmap ();
If (resultmap! = NULL ){
// Skip results
If (Rs. GetType ()! = Resultset. type_forward_only ){
If (skipresults> 0 ){
Rs. Absolute (skipresults );
}
} Else {
For (INT I = 0; I <skipresults; I ++ ){
If (! Rs. Next ()){
Break;
}
}
}

// Get results
Int resultsfetched = 0;
While (maxresults = sqlexecutor. no_maximum_results | resultsfetched <maxresults) & Rs. Next ()){
Object [] columnvalues = resultmap. resolvesubmap (request, RS). getresults (request, RS );
Callback. handleresultobject (request, columnvalues, RS );
Resultsfetched ++;
}
}
} Finally {
Request. setresultset (null );
}
}

 

The priority here is to use the absolute method of the resultset to locate records. Whether the support for absolute depends on the specific database driver, but the current version of the database generally supports this method, if not, the previous records are skipped one by one. From this we can see that if the database supports absolute, the difference between the ibatis built-in paging policy and the physical paging efficiency of a specific database lies in the gap between the physical paging query and non-Paging query execution efficiency in the database. Because the database does not return all the results to the memory before reading data after the query is executed, the storage usage is not much different. If indexes are used, the execution speed is estimated to be low.

Continue with our topic. In fact, you only need to assemble the SQL statement before executing executequery, then pass it to executequery, And tell handleresults that we don't need to perform logic paging. To intercept executequery, you can adopt the dynamic implementation of AOP, or inherit sqlexecutor to overwrite executequery for static implementation. In contrast, the latter is much simpler. Because sqlexecutor does not implement any interfaces, it is easier to change, dynamic interception increases the maintenance workload, so we will cover executequery below:

Package com. Aladdin. Dao. ibatis. Ext;

Import java. SQL. connection;
Import java. SQL. sqlexception;

Import org. Apache. commons. Logging. log;
Import org. Apache. commons. Logging. logfactory;

Import com. Aladdin. Dao. dialect. dialect;
Import com.ibatis.sqlmap.engine.exe cution. sqlexecutor;
Import com. ibatis. sqlmap. Engine. Mapping. Statement. rowhandlercallback;
Import com. ibatis. sqlmap. Engine. Scope. requestscope;

Public class limitsqlexecutor extends sqlexecutor {

Private Static final log logger = logfactory. getlog (limitsqlexecutor. Class );

Private dialect;

Private Boolean enablelimit = true;

Public dialect getdialect (){
Return dialect;
}

Public void setdialect (dialect ){
This. dialect = dialect;
}

Public Boolean isenablelimit (){
Return enablelimit;
}

Public void setenablelimit (Boolean enablelimit ){
This. enablelimit = enablelimit;
}

@ Override
Public void executequery (requestscope request, connection Conn, string SQL,
Object [] parameters, int skipresults, int maxresults,
Rowhandlercallback callback) throws sqlexception {
If (skipresults! = No_skipped_results | maxresults! = No_maximum_results)
& Supportslimit ()){
SQL = dialect. getlimitstring (SQL, skipresults, maxresults );
If (logger. isdebugenabled ()){
Logger. debug (SQL );
}
Skipresults = no_skipped_results;
Maxresults = no_maximum_results;
}
Super.exe cutequery (request, Conn, SQL, parameters, skipresults,
Maxresults, callback );
}

Public Boolean supportslimit (){
If (enablelimit & dialect! = NULL ){
Return dialect. supportslimit ();
}
Return false;
}

}

Where:

Skipresults = no_skipped_results;
Maxresults = no_maximum_results;