The do it yourself mybatis paging plugin of is extremely simple and easy to use
- 2020-05-17 05:27:17
- OfStack
Project at first, need to use mybatis paging, watch a lot of plug-ins, actually implement principle basic are the same, but most of all to the code, the annotation is not complete, so refer to a lot of articles (1 point each article to steal code, identified on its own, a copy), to imitate the write themselves a suitable project of paging plug-in, few words said, directly on the code, compared with most of the articles, notes is very complete
The most important interceptor
package com.dnkx.interceptor;
import java.sql.*;
import java.util.HashMap;
import java.util.Properties;
import org.apache.ibatis.executor.resultset.ResultSetHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
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.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import com.dnkx.pojo.Page;
/**
*
* Page blocker, which intercepts operations that require paging queries and then pages them.
* Implementation using interceptors Mybatis Principle of paging:
* To take advantage of JDBC You have to operate on the database 1 A corresponding Statement Object, Mybatis In the implementation Sql It comes before the statement 1 containing Sql The statement Statement Object, and the corresponding Sql statements
* Is in the Statement We generated it before, so we can generate it at this point Statement The previous pair was used to generate Statement the Sql Start with the statement. in Mybatis In the Statement Statement is through RoutingStatementHandler The object's
* prepare Method generated. So use the interceptor implementation Mybatis paging 1 The idea is intercept StatementHandler Of the interface prepare Method, and then in the interceptor method Sql Statement to the corresponding paging query Sql Statement and call it later
* StatementHandler The object's prepare Method, which is called invocation.proceed() .
* For paging, what we need to do in the interceptor 1 The operation is to count the records that meet the current conditions 1 How many total? Well, this is just getting the raw Sql After the statement, change it to the corresponding statistical statement and reuse it Mybatis Encapsulated parameters and Settings
* Set the function of the parameter to Sql The arguments in the statement are replaced, and the query is executed for the number of records Sql Statement for the total number of records.
*
* explain 1 The following classes may be used in the plug-in:
* MetaObject : mybatis To provide the 1 Class based on the object that returns the value of the property
* BoundSql : You can get all of them executed in this one sql And perform sql The parameter to use
* MappedStatement : This one can get the current execution sql Statements in xml Configured in a file id The value of the
* RowBounds : is mybatis Memory paging is used.
* ParameterHandler : is mybatis Be used to replace sql In the ? Of the value that appears .
*
* @author Turn the little li 2016 years 11 month 9 day 10:59:04
*/
@Intercepts({
@Signature(type=StatementHandler.class,method="prepare",args={Connection.class}),
@Signature(type = ResultSetHandler.class, method = "handleResultSets", args = {Statement.class})
})
public class PageInterceptor implements Interceptor{
// Block paging keywords
private static final String SELECT_ID="page";
// The plug-in runs code that will replace the original method , Rewrite the most important intercept the
@Override
public Object intercept(Invocation invocation) throws Throwable {
if (invocation.getTarget() instanceof StatementHandler) {
// Here we have 1 A set If the query method contains Page I'm going to page it Other ways to ignore
// So you get the method name
StatementHandler statementHandler=(StatementHandler)invocation.getTarget();
MetaObject metaObject=SystemMetaObject.forObject(statementHandler);
MappedStatement mappedStatement=(MappedStatement)metaObject.getValue("delegate.mappedStatement");
String selectId=mappedStatement.getId();
String methorName=selectId.substring(selectId.lastIndexOf(".")+1).toLowerCase();
// And then judge If they contain Page Just get sql
if(methorName.contains(SELECT_ID)){
BoundSql boundSql=(BoundSql)metaObject.getValue("delegate.boundSql");
// The paging parameter is used as the parameter object parameterObject the 1 A property
String sql=boundSql.getSql();
System.out.println(" To get to sql:"+sql);
HashMap<String, Object> map=(HashMap<String, Object>)(boundSql.getParameterObject());
//Page page=(Page)(boundSql.getParameterObject());
Page page=(Page)map.get("page");
// rewrite sql
String countSql=concatCountSql(sql);
String pageSql=concatPageSql(sql,page);
// System.out.println(" Rewrite the count sql :"+countSql);
System.out.println(" Rewrite the select sql :"+pageSql);
Connection connection = (Connection) invocation.getArgs()[0];
PreparedStatement countStmt = null;
ResultSet rs = null;
int totalCount = 0;
try {
countStmt = connection.prepareStatement(countSql);
rs = countStmt.executeQuery();
if (rs.next()) {
totalCount = rs.getInt(1);
}
} catch (SQLException e) {
System.out.println("Ignore this exception"+e);
} finally {
try {
rs.close();
countStmt.close();
} catch (SQLException e) {
System.out.println("Ignore this exception"+ e);
}
}
metaObject.setValue("delegate.boundSql.sql", pageSql);
// The binding count
page.setNumCount(totalCount);
}
}
return invocation.proceed();
}
// Intercept type StatementHandler, rewrite plugin methods
@Override
public Object plugin(Object target) {
if (target instanceof StatementHandler) {
return Plugin.wrap(target, this);
}else {
return target;
}
}
@Override
public void setProperties(Properties properties) {
}
// transform sql
public String concatCountSql(String sql){
//StringBuffer sb=new StringBuffer("select count(*) from ");
/*sql=sql.toLowerCase();
if(sql.lastIndexOf("order")>sql.lastIndexOf(")")){
sb.append(sql.substring(sql.indexOf("from")+4, sql.lastIndexOf("order")));
}else{
sb.append(sql.substring(sql.indexOf("from")+4));
}*/
StringBuffer sb=new StringBuffer();
sql=sql.toLowerCase();
if(sql.lastIndexOf("order")>0){
sql=sql.substring(0,sql.indexOf("order"));
}
sb.append("select count(*) from ("+sql+") tmp");
return sb.toString();
}
public String concatPageSql(String sql,Page page){
StringBuffer sb=new StringBuffer();
sb.append(sql);
sb.append(" limit ").append(page.getPageBegin()).append(" , ").append(page.getPageSize());
return sb.toString();
}
}
Paging object Page class
[java] view plain copy
package com.dnkx.pojo;
import java.util.HashMap;
import java.util.Map;
/**
*
* Paging queries helper classes
* @author Turn the little li 2016 years 11 month 9 day 13:55:37
*/
public class Page {
//---------- paging -----------
private int pageSize;// Number of bars per page
private int pageCurrentPage;// What page
private int pageBegin;// The starting position
private int numCount;// The total number of article
private int pageTotal;// The total number of article
private String orderField = "";// Control sort page display
private String orderDirection = "";
public Page(){
}
public Page(int pageSize, int pageCurrentPage) {
super();
this.pageSize = pageSize;
this.pageCurrentPage = pageCurrentPage;
}
public Page(Map<String, String> map){
if(map.get("pageNum")!=null){
this.setPageCurrentPage(this.pageCurrentPage = Integer.parseInt(map.get("pageNum")));// The number of pages to query
}else{
this.setPageCurrentPage(1);// Set initial value
}
if(map.get("numPerPage")!=null){
this.setPageSize(Integer.parseInt(map.get("numPerPage")));// Number of bars per page
}else{
this.setPageSize(5);// Set initial value
}
if(map.get("orderField")!=null){
this.setOrderField(map.get("orderField"));
}
if(map.get("orderDirection")!=null){
this.setOrderDirection(map.get("orderDirection"));
}
}
public int getPageCurrentPage() {
return pageCurrentPage;
}
public void setPageCurrentPage(int pageCurrentPage) {
this.pageCurrentPage = pageCurrentPage;
}
public int getNumCount() {
return numCount;
}
public void setNumCount(int numCount) {
this.numCount = numCount;
}
public int getPageTotal() {
return (numCount%pageSize>0)?(numCount/pageSize+1):(numCount/pageSize);
}
public void setPageTotal(int pageTotal) {
this.pageTotal = pageTotal;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getPageBegin() {
return pageSize*(pageCurrentPage-1);
}
public void setPageBegin(int pageBegin) {
this.pageBegin = pageBegin;
}
public String getOrderField() {
return orderField;
}
public void setOrderField(String orderField) {
this.orderField = orderField;
}
public String getOrderDirection() {
return orderDirection;
}
public void setOrderDirection(String orderDirection) {
this.orderDirection = orderDirection;
}
public static Page getPage(int pageSize, int pageCurrentPage){
return new Page(pageSize,pageCurrentPage);
}
public static Page getPage(Map map){
return new Page(map);
}
}
Call mode in Controller
public String list(HttpServletRequest request) {
long a=System.currentTimeMillis();
HashMap<String,Object> map=GetRequestMap.getMap(request);// Own encapsulation method, take request The parameters of the
Page page= Page.getPage(map);// Initialize the page
map.put("page", page);// the page The object is put into a collection of parameters ( this map is mybatis To use, including query criteria, sorting, paging, and so on )
// Control sort page display
map.put(map.get("orderField")+"", map.get("orderDirection"));
List<Employee> list=employeeService.getListPage(map);
request.setAttribute("emlist", list);
request.setAttribute("page", page);
request.setAttribute("map", map);
// take page Relevant properties
page.getNumCount();// The total number of article
page.getPageTotal();// Total number of pages
long b=System.currentTimeMillis();
System.out.println("--------- Time consuming :"+(b-a)+"ms");
return "basic/employee_list";
}
Finally, the plug-in is configured in spring
<bean id="PageInterector" class="com.dnkx.interceptor.PageInterceptor"></bean>
<!-- spring and MyBatis Perfect integration, no need mybatis Configuration mapping file -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<!-- Automatic scanning mapping.xml file -->
<property name="mapperLocations" value="classpath:com/dnkx/mapping/*.xml"></property>
<property name="plugins">
<ref bean="PageInterector"/>
</property>
</bean>
Ok, that's it, this article is for reference only! We also look forward to your comments