MyBatis: Simple Physical paging implementation

First, necessity

First, it is necessary to introduce a custom interceptor for physical paging. We know that a method with paging functionality is available in the Sqlsession interface in MyBatis:

Public interface Sqlsession extends closeable {<E> list<e> selectlist (String statement, Object parameter,    Rowbounds rowbounds); // ....}

Using this method, we can query by SelectList (..) The Rowbounds method provides a parameter for paging, for example, assuming I need to take the first three records of a query record, you can:

The steps to get sqlsession are slightly statement, the mapping statement in Mapper is select * from userslist<user> list = Sqlsession.selectlist ( statement, NULL, new Rowbounds (0,3));

The record that we get is the first three records of the query record (the query result of select * from users)

At this point we have a question, since MyBatis has provided us with pagination of the processing class, why do we have to re-build the wheel (and then manually write an interceptor)?

This is because MyBatis has a built-in paging processor that is paged through memory, in conjunction with the example above, MyBatis first executes select * from users and then gets the result set resultset, The resultset is then processed by the offset and limit properties in the incoming rowbounds. If the record volume is large, this efficiency is undoubtedly quite low. To confirm the above conclusion, you can view the Defaultresultsethandler class in MyBatis

public class defaultresultsethandler implements resultsethandler {     // ....        private void  Handlerowvaluesforsimpleresultmap (resultsetwrapper rsw, resultmap resultmap,              ResultHandler<?> resultHandler,  rowbounds rowbounds, resultmapping parentmapping)  throws SQLException {                 defaultresultcontext <Object> resultContext = new DefaultResultContext<Object> ();         //  use SkipRows to make Resusltset point to the location specified by offset in rowbounds          skiprows (Rsw.getresultset (),  rowbounds);                 while  (Shouldprocessmorerows (resultcontext, rowbounds)  &&  Rsw.getresultset (). Next ())  {             Resultmap discriminatedresultmap = resolvediscriminatedresultmap (Rsw.getResultSet (),  Resultmap, null);            object  Rowvalue = getrowvalue (Rsw, discriminatedresultmap);             storeobject (resulthandler, resultcontext, rowvalue, parentmapping ,  rsw.getresultset ());        }    }     private void skiprows (resultset rs, rowbounds rowbounds)   throws sqlexception {        //  if the cursor in the resultset supports moving forward or backward         if  (Rs.gettype ()  != resultset.type_forward_only)  {             //  if the value of offset in rowbounds is not 0             if  (Rowbounds.getoffset ()  != rowbounds.no_row_offset)   {                //  Move the RS cursor to the location specified in the Rowbounds in offset                  rs.absolute (Rowbounds.getoffset ());             }        } else {    / /  if ResultSet only supports moving forward             //  Use Rs.next () to move the cursor from 0 to one position at a time until the position specified in offset rowbounds              for  (Int&nbsP;i = 0; i < rowbounds.getoffset ();  i++)  {                 rs.next ();             }        }    &NBSP;}&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;//&NBSP, ...}

To confirm the handlerowvaluesforsimpleresultmap (...) in this class. In the SkipRows (...) method to make a breakpoint at SkipRows (...) In the Rs.absolute (...) and Rs.next () to break the breakpoint, and then through the debug mode of execution

list<user> list = sqlsession.selectlist (statement, NULL, new Rowbounds (0,3));

You can see that the program actually entered the SkipRows (...) method, we can see in the SkipRows (...) , MyBatis is significantly less efficient by executing the SELECT * from users and then processing the resultset result set instead of directly executing the SELECT * from users limit 0,3 (assuming the MySQL database). So if there are two ways we can solve this problem in practical applications,

1) Manually change each executed statement to select * from users limit #{offset},#{limit in the mapper mapping file

2) Customize an interceptor to change the underlying final query statement to SELECT * from users limit 0,3

The 2nd workaround is described below.

Second, custom paging plugin

MyBatis allows you to intercept calls at a point during the execution of a mapped statement. By default, MyBatis allows method calls that use plug-ins to intercept include:

The preceding is the class name that allows the plugin to intercept, in parentheses is the method name that allows the plugin to intercept executor (update, query, Flushstatements, Commit, rollback, gettransaction, close, isClosed) Parameterhandler (Getparameterobject, Setparameters) Resultsethandler (Handleresultsets, Handleoutputparameters) Statementhandler (Prepare, parameterize, batch, update, query)

MyBatis is the prepare (...) in the Statementhandler. The parsing of SQL is done in the method, so we need to set up an interceptor in front of this method, that is, plugin to make the SQL statement, the following is the specific code:

package cn.kolbe.mybatis.plugin;import java.lang.reflect.field;import java.sql.connection; Import java.util.properties;import org.apache.ibatis.executor.statement.statementhandler;import  org.apache.ibatis.mapping.BoundSql;import org.apache.ibatis.plugin.Interceptor;import  org.apache.ibatis.plugin.intercepts;import org.apache.ibatis.plugin.invocation;import  org.apache.ibatis.plugin.plugin;import org.apache.ibatis.plugin.signature;import  Org.apache.ibatis.session.RowBounds, @Intercepts (@Signature (type = statementhandler.class,  method =  "Prepare",  args = { connection.class })) public class  Pageplugin implements interceptor {// select statement Regular expression match:  ^ represents the beginning position  \s represents a space  $ Represents the end position  * represents any number of  . private final static string regex =  "^\\s*[Ss][for any character ee][ll][ee][cc][tt].*$ "; @Overridepublic  object intercept (InvocatION&NBSP;INV)  throws Throwable {//  The target at this time is an instance of the Routingstatementhandler class Statementhandler  target =  (Statementhandler) inv.gettarget (); The// boundsql class has an SQL attribute, which is the SQL statement to be executed Boundsql  boundsql = target.getboundsql (); String sql = boundsql.getsql ();//  If the SQL statement is a SELECT statement, then it is necessary to see if the paging process is required if  (sql.matches ( REGEX))  {//  Delegate is routingstatementhandler to specify the specific statementhandlerobject delegate = by StatementType set in the Mapper mapping file  readfield (target,  "delegate");// rowbounds is bound to our custom paging information, including the starting position offset and the number of records fetched Limitrowbounds  rowBounds =  (rowbounds) Readfield (delegate,  "Rowbounds");//  if Rowbound is not empty, And the starting position of the rowbounds is not 0, which means we need to do the paging if  (Rowbounds != null && rowbounds.getoffset ( )  != rowbounds.no_row_offset)  {// assemsql (...) Complete the Assembly of the SQL statement and reset the Rowbounds writefield (boundsql,  "SQL",  assemsql (Sql, rowbounds));}} Return inv.proceEd ();} /** *  assemble the SQL statement and reset the offset and limit *  in Rowbounds @param  oldSql *  @param   rowbounds *  @return  */public string assemsql (string oldsql, rowbounds  rowbounds)  throws Exception {String sql = oldSql +  " limit "  + rowbounds.getoffset ()  +  ","  + rowbounds.getlimit ();//  These two steps are necessary, Because the actual result set is what we want after the SQL statement is replaced earlier, offset and limit must be reset to the initial value Writefield (rowbounds,  "offset",  rowbounds.no_row_ OFFSET); Writefield (rowbounds,  "Limit",  rowbounds.no_row_limit); return sql;} /** *  use reflection to get specified properties of the specified object  *  @param  target *  @param  fieldname * @ return *  @throws  exception */private object readfield (Object target,  String fieldname)  throws Exception {Field field = null;//  Iterates through the properties of the target and its parent class for  (CLASS&LT;?&GT;&NBSP;C&NBsp;= target.getclass ();  c != null; c = c.getsuperclass ())  {try { Field = c.getdeclaredfield (fieldName);}  catch  (Nosuchfieldexception ex)  {                                  //  did not find the property, it inherits the property of the parent class, so it does not handle the exception}}field.setaccessible (true);return  Field.get (target);} /** *  write values to specified properties of the specified object using reflection  *  @param  target *  @param  fieldname *   @param  value *  @throws  exception */private void writefield (Object  target, string fieldname, object value)  throws Exception {Field  field = null;//  iterates through the properties of the target and the properties of its parent for  (Class<?> c = target.getclass () ;  c != null; c = c.getsuperclass ())  {Try {field = c.getdeclaredfield (fieldName);}  catch  (Nosuchfieldexception ex)  {                                  //  did not find the property, it inherits the property of the parent class, so it does not handle the exception}}field.setaccessible (true); Field.set (Target,  value);} @Overridepublic  object plugin (Object target)  {//  through the plugin wrap (...) method to implement the proxy class generation operation Return plugin.wrap (Target, this);} @Overridepublic  void setproperties (Properties props)  {}}

Note:

1) in order to keep it simple and easy to understand, not using too many toolsets, the private property assignment acquisition and assignment of objects in specific applications can be handled by MyBatis built-in classes or Apache Commons-lang tools

2) This example uses MySQL as an example, do not consider other databases, the application can be considered in the configuration file to set up the database, and dynamically based on the configuration file to determine the specific SQL statement assembly, also for the sake of simplicity, here is not an example of

Configure the plug-in in MyBatis configuration file Mybatis-config.xml

<plugins> <plugin interceptor= "Cn.kolbe.mybatis.plugin.PagePlugin" ></plugin></plugins>

To add a query statement to a mapping file

<?xml version= "1.0" encoding= "UTF-8"? ><! DOCTYPE Mapper Public "-//mybatis.org//dtd mapper 3.0//en" "Http://mybatis.org/mybatis-3-mapper.dtd" ><mapper Namespace= "Cn.kolbe.mybatis.domain.UserMapper" ><select id= "GetAll" resulttype= "User" >select * from users </select></mapper>

Use in the app

package cn.kolbe.mybatis;import java.io.fileinputstream;import java.io.inputstream;import  java.util.list;import org.apache.ibatis.session.rowbounds;import  org.apache.ibatis.session.sqlsession;import org.apache.ibatis.session.sqlsessionfactory;import  org.apache.ibatis.session.sqlsessionfactorybuilder;import org.junit.test;import  cn.kolbe.mybatis.domain.user;public class mybatistest {@Testpublic  void querybypage ()  throws exception {inputstream in = new fileinputstream ("src/main/java/ Mybatis-config.xml "); Sqlsessionfactory factory = new sqlsessionfactorybuilder (). build (in); Sqlsession session = factory.opensession (); string statement =  "Cn.kolbe.mybatis.domain.UserMapper.getAll"; List<user> list = session.selectlist (Statement, null, new rowbounds (0,3)); SYSTEM.OUT.PRINTLN (list);}  }

MyBatis: Simple Physical paging implementation