Spring + SpringMVC + mybatis + easyui integration instance (5) Use mybatis interceptor Paging

Spring + SpringMVC + mybatis + easyui integration instance (5) Use mybatis interceptor Paging

Today, we will sort out the server paging part, which is also an important part of instance integration.

Here we use the mybatis interceptor for paging.
Stamp this download->
The principle of interceptor paging is as follows:

The paging interceptor is used to intercept operations that require querying by PAGE, and then process by page. The principle of implementing Mybatis paging using the Interceptor: to operate the database using JDBC, a corresponding Statement object is required, before executing an SQL Statement, Mybatis generates a Statement object containing the SQL Statement, and the corresponding SQL Statement is generated before the Statement, so we can start with the SQL Statement used to generate the Statement before it generates the Statement. In Mybatis, the Statement is generated using the prepare method of the RoutingStatementHandler object. One idea of implementing Mybatis paging using the interceptor is to intercept the prepare method of the StatementHandler interface, and then change the SQL statement in the interceptor Method to the corresponding paging query SQL statement, then call the prepare method of the StatementHandler object, that is, call invocation. proceed (). For paging, one of the operations we need to do in the interceptor is to count the total number of records meeting the current condition. After obtaining the original SQL statement, change it to the corresponding statistical statement and replace the parameters in the SQL statement with the parameters encapsulated by Mybatis and set parameters, then, execute the SQL statement to query the number of records for the total number of records.

Well, we can modify it in the previous demo. If you haven't read it, you can refer to the previous blogs.

First, create the mybatis configuration file under src, which is only used to configure the interceptor database type. The mybatis interceptor supports oracle and mysql databases.
Mybatis-config.xml:

Add the mybatis configuration file to the spring configuration file.
Find bean ssf in applicationContext. xml and modify it as follows:

Create the com. etoak. page package under src and add the following three tool classes:
Interceptor PageInterceptor. java

@ Intercepts ({@ Signature (method = prepare, type = StatementHandler. class, args = {Connection. class}) public class PageInterceptor implements Interceptor {private String databaseType; // database type, different databases have different paging methods/*** methods to be executed after interception */public Object intercept (Invocation invocation) throws Throwable {final RoutingStatementHandler handler = (RoutingStatementHandler) invocation. getTarget (); // get the current Rou through reflection The delegate attribute of the tingStatementHandler object is final StatementHandler delegate = (StatementHandler) ReflectUtil. getFieldValue (handler, delegate); // obtain the current StatementHandler's // boundSql, whether it is to call handler. getBoundSql () or directly call delegate. the results of getBoundSql () are the same, because all StatementHandler interface methods implemented by // RoutingStatementHandler are the corresponding delegate methods called. Final BoundSql boundSql = delegate. getBoundSql (); // get the parameter Object of the currently bound SQL, that is, the final Object obj = boundSql parameter Object passed in when we call the corresponding Mapper ing statement. getParameterObject (); // here we simply pass in the Page object to determine that it requires paging. If (obj instanceof SearchPageUtil) {final SearchPageUtil page = (SearchPageUtil) obj; // obtain the mappedStatement attribute of the delegate parent class Using Reflection // MappedStatement mappedStatement = (MappedStatement) // callback. getFieldValue (delegate, mappedStatement); // The intercepted prepare method parameter is a Connection object // Connection connection = (Connection) invocation. getArgs () [0]; // obtain the SQL statement to be executed, that is, the SQL statement written directly in the er ing statement. Final String SQL = boundSql. getSql (); // set the total number of records for the current page parameter object to affect performance. // this. setTotalRecord (page, mappedStatement, connection); // obtain the paging SQL statement final String pageSql = this. getPageSql (page, SQL); // use reflection to set the SQL attribute corresponding to the current BoundSql to ReflectUtil. setFieldValue (boundSql, SQL, pageSql);} return invocation. proceed ();}/*** Method for encapsulating the original Object corresponding to the interceptor */public Object plugin (Object target) {return Plugin. wrap (targ Et, this);}/*** set the property set when the interceptor is registered */public void setProperties (Properties properties Properties) {this. databaseType = properties. getProperty (databaseType);}/*** obtain the corresponding paging query SQL statement based on the page object. Only two database types are available here, neither Mysql nor other Oracle databases are paging */private String getPageSql (SearchPageUtil page, String SQL) {final StringBuffer sqlBuffer = new StringBuffer (SQL); if (mysql. equalsIgnoreCase (databaseType) {return getMysqlPageSq L (page, sqlBuffer);} else if (oracle. repeated signorecase (databaseType) {return getOraclePageSql (page, sqlBuffer);} return sqlBuffer. toString ();}/*** obtain the Mysql database paging query statement */private String getMysqlPageSql (SearchPageUtil page, StringBuffer sqlBuffer) {// calculate the location of the first record, the position recorded in Mysql starts from 0. // Int offset = (page. getPage (). getPageIndex ()-1) * // page. getPageSize (); sqlBuffer. append (limit ). append (page. getStartRow ()). append (,). append (page. getPageSize (); return sqlBuffer. toString ();}/*** obtain the paging query statement of the Oracle database */private String getOraclePageSql (SearchPageUtil page, StringBuffer sqlBuffer) {// calculate the location of the first record, oracle paging is performed through rownum, while rownum is the final int offset = (page. getPage (). getPageInd Ex ()-1) * page. getPageSize () + 1; sqlBuffer. insert (0, select u. *, rownum r from (). append () u where rownum <). append (offset + page. getPageSize (); sqlBuffer. insert (0, select * from (). append () where r> = ). append (offset); // The preceding SQL statement is like this after splicing: // select * from (select u. *, rownum r from (select * from t_user) u // where rownum <31) where r> = 16 return sqlBuffer. toString ();}/*** to the current parameter object Total number of records set by page */private void setTotalRecord (SearchPageUtil page, MappedStatement mappedStatement, Connection connection) {// obtain the corresponding BoundSql, this BoundSql is actually the same object as the BoundSql obtained by using StatementHandler. // BoundSql in delegate is also obtained through the mappedStatement. getBoundSql (paramObj) method. Final BoundSql boundSql = mappedStatement. getBoundSql (page); // obtain the SQL statement final String SQL = boundSql that we wrote in The Mapper ing statement. getSql (); // obtain the corresponding SQL statement for calculating the total number of records through the query SQL statement final String countSql = this. getCountSql (SQL); // obtain the corresponding parameter ing final List through BoundSql
  
   
ParameterMappings = boundSql. getParameterMappings (); // use the Configuration, query record count SQL statement countSql, parameter ing relationship parameterMappings, and parameter object page to create a BoundSql object corresponding to the number of query records. Final BoundSql countBoundSql = new BoundSql (mappedStatement. getConfiguration (), countSql, parameterMappings, page); // you can use mappedStatement, parameter object page, and BoundSql object countBoundSql to create a ParameterHandler object final ParameterHandler parameterHandler = new org.apache.ibatis.exe cutor. parameter. defaultParameterHandler (mappedStatement, page, countBoundSql); // create a PreparedStatement object corresponding to countSql through connection. PreparedStatement pstmt = null; ResultSet rs = null; try {pstmt = connection. prepareStatement (countSql); // you can use parameterHandler to set the parameter parameterHandler for the PreparedStatement object. setParameters (pstmt); // then execute the SQL statement to obtain the total number of records and obtain the results. Rs = pstmt.exe cuteQuery (); if (rs. next () {final int totalRecord = rs. getInt (1); // set the total number of records page for the current parameter page Object. getPage (). setRowTotal (totalRecord) ;}} catch (SQLException e) {e. printStackTrace ();} finally {try {if (rs! = Null) rs. close (); if (pstmt! = Null) pstmt. close ();} catch (SQLException e) {e. printStackTrace () ;}}/ *** obtain the corresponding SQL statement for the total number of records queried based on the original SQL statement */private String getCountSql (String SQL) {final int index = SQL. indexOf (from); return select count (*) + SQL. substring (index );} /*** a tool class operated by reflection */private static class ReflectUtil {/*** obtains the specified attribute of the specified Object using reflection */public static Object getFieldValue (Object obj, string fieldName) {Object resu Lt = null; final Field field = ReflectUtil. getField (obj, fieldName); if (field! = Null) {field. setAccessible (true); try {result = field. get (obj);} catch (IllegalArgumentException e) {// TODO Auto-generated catch block e. printStackTrace ();} catch (IllegalAccessException e) {// TODO Auto-generated catch block e. printStackTrace () ;}} return result;}/*** get the specified attribute in the specified Object using reflection */private static Field getField (Object obj, String fieldName) {Field field = null; for (Class
   Clazz = obj. getClass (); clazz! = Object. class; clazz = clazz. getSuperclass () {try {field = clazz. getDeclaredField (fieldName); break;} catch (NoSuchFieldException e) {// No processing is required here. If the child class does not have the parent class that the field may correspond to, null is returned if none of them exist.} Return field;}/*** use reflection to set the specified attribute of the specified Object to the specified value */public static void setFieldValue (Object obj, String fieldName, String fieldValue) {final Field field = ReflectUtil. getField (obj, fieldName); if (field! = Null) {try {field. setAccessible (true); field. set (obj, fieldValue);} catch (IllegalArgumentException e) {// TODO Auto-generated catch block e. printStackTrace ();} catch (IllegalAccessException e) {// TODO Auto-generated catch block e. printStackTrace ();}}}}}
  

SearchPageUtil. java

Public class SearchPageUtil {// query Object private object Object; // sort FIELD private String [] orderBys; // start line private int startRow; // terminate line private int pageSize; // condition String private String filter; // sort String private String orderBy; // Page-based private page Page; public Object getObject () {return object ;} public void setObject (Object object) {this. object = object;} public int getStartRow () {return startRow;} public void setStartRow (int startRow) {this. startRow = startRow;} public String getFilter () {return filter;} public int getPageSize () {return pageSize;} public void setPageSize (int pageSize) {this. pageSize = pageSize;} public void setFilter (String filter) {this. filter = filter;} public String [] getOrderBys () {return orderBys;} public void setOrderBys (String [] orderBys) {this. orderBys = orderBys;} public String getOrderBy () {return orderBy;} public void setOrderBy (String orderBy) {this. orderBy = orderBy;} public Page getPage () {return page;} public void setPage (Page page) {this. startRow = (page. getPageIndex ()-1) * page. getPageSize (); this. pageSize = page. getPageSize (); this. page = page ;}}

Page. java

Public class Page {// Page number of private int pageIndex; // size of each Page private int pageSize; // total number of private int rowTotal; // total number of private int pageTotal pages; public Page (int pageIndex, int pageSize, int rowTotal) {this. pageIndex = pageIndex; this. pageSize = pageSize; this. rowTotal = rowTotal;} public int getRowTotal () {return rowTotal;} public void setRowTotal (int rowTotal) {this. rowTotal = rowTotal;} public int getPageIndex () {return pageIndex;} public void setPageIndex (int pageIndex) {this. pageIndex = pageIndex;} public int getPageSize () {return pageSize;} public void setPageSize (int pageSize) {this. pageSize = pageSize;} public int getPageTotal () {int pageTotal = 0; if (rowTotal % pageSize = 0) {pageTotal = this. rowTotal/this. pageSize;} else {pageTotal = rowTotal/pageSize + 1;} return pageTotal;} public void setPageTotal (int pageTotal) {this. pageTotal = pageTotal ;}}

Modify the selectAllStudents method in StudentDaoIF. java

/*** Query by condition * @ param searchPageUtil * @ return */@ Select (select * from student) @ Results ({@ Result (column = id, property = id ), @ Result (column = name, property = name), @ Result (column = password, property = password)}) public List
  
   
SelectAllStudents (SearchPageUtil searchPageUtil );
  

Modify the service layer method:

    @Override    public List
  
    selectAllStudents(Student stu,Page page) {        SearchPageUtil searchPageUtil = new SearchPageUtil();        String a[] = { name  desc, id asc };        searchPageUtil.setOrderBys(a);        searchPageUtil.setPage(page);        searchPageUtil.setObject(stu);        final List list = dao.selectAllStudents(searchPageUtil);        return list;    }
  

Note: we can see that the method in the searchPageUtil tool class can be used to concatenate an SQL statement.

Controller layer:

private Page page;    private int pageIndex = 1;    private int pageSize = 10;    private int total = 0;    @RequestMapping(/ShowStudents.do)    @ResponseBody    public Map showStudents(HttpServletRequest request,HttpServletResponse response) throws Exception{        pageIndex = Integer.parseInt(request.getParameter(page));        total = dao.StudentCount();        page = new Page(pageIndex,pageSize,total);        pageSize = Integer.parseInt(request.getParameter(rows));        Map map = new HashMap();        List
  
    students = dao.selectAllStudents(stu,page);        map.put(rows,students);        map.put(total, total);        System.out.println(map);        return map;    }
  

Note: To use easyui, you must return the rows and total parameters.

Now we're done!