Several common mybatis paging implementations

There are several ways to realize the paging of the mybatis framework. The simplest is to use the native sql keyword limit. The other is to use the interceptor to splice sql to achieve the same functions as limit. The other is to use the PageHelper. Here are three common implementation methods:

No matter which implementation method is used, we can no longer use List for the returned results. We need a custom object Pager.

package com.xxx.mybatis.bean;
import java.util.List;
public class Pager<T> {
	private int page;//Paging start page
	private int size;//Records per page
	private List<T> rows;//Collection of records returned
	private long total;//Total records
	public int getPage() {
		return page;
	}
	public void setPage(int page) {
		this.page = page;
	}
	public int getSize() {
		return size;
	}
	public void setSize(int size) {
		this.size = size;
	}
	public List<T> getRows() {
		return rows;
	}
	public void setRows(List<T> rows) {
		this.rows = rows;
	}
	public long getTotal() {
		return total;
	}
	public void setTotal(long total) {
		this.total = total;
	}
}

limit keyword implementation:

UserDao.java adds two methods

public List<User> findByPager(Map<String, Object> params);
public long count();

UserMapper. Add two queries to XML

<select id="findByPager" resultType="com.xxx.mybatis.domain.User">
	select * from xx_user limit #{page},#{size}
</select>
<select id="count" resultType="long">
	select count(1) from xx_user
</select>

 UserService. Add paging method in Java

public Pager<User> findByPager(int page,int size){
	Map<String, Object> params = new HashMap<String, Object>();
	params.put("page", (page-1)*size);
	params.put("size", size);
	Pager<User> pager = new Pager<User>();
	List<User> list = userDao.findByPager(params);
	pager.setRows(list);
	pager.setTotal(userDao.count());
	return pager;
}

This is the most intuitive implementation method and the simplest one. It can be easily implemented without any plug-ins or tools.  

interceptor plugin implementation:

You need to define a class to implement the Interceptor interface

MyPageInterceptor.java

package com.xxx.mybatis.bean;
import java.sql.Connection;
import java.util.Map;
import java.util.Properties;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
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;
@Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class,Integer.class})})
public class MyPageInterceptor implements Interceptor {
	
	private int page;
	private int size;
	@SuppressWarnings("unused")
	private String dbType;
 
	@SuppressWarnings("unchecked")
	@Override
	public Object intercept(Invocation invocation) throws Throwable {
		System.out.println("plugin is running...");
		StatementHandler statementHandler = (StatementHandler)invocation.getTarget();
		MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
		while(metaObject.hasGetter("h")){
			Object object = metaObject.getValue("h");
			metaObject = SystemMetaObject.forObject(object);
		}
		while(metaObject.hasGetter("target")){
			Object object = metaObject.getValue("target");
			metaObject = SystemMetaObject.forObject(object);
		}
		MappedStatement mappedStatement = (MappedStatement)metaObject.getValue("delegate.mappedStatement");
		String mapId = mappedStatement.getId();
		if(mapId.matches(".+ByPager$")){
			ParameterHandler parameterHandler = (ParameterHandler)metaObject.getValue("delegate.parameterHandler");
			Map<String, Object> params = (Map<String, Object>)parameterHandler.getParameterObject();
			page = (int)params.get("page");
			size = (int)params.get("size");
			String sql = (String) metaObject.getValue("delegate.boundSql.sql");
			sql += " limit "+(page-1)*size +","+size;
			metaObject.setValue("delegate.boundSql.sql", sql);
		}
		return invocation.proceed();
	}
 
	@Override
	public Object plugin(Object target) {
		return Plugin.wrap(target, this);
	}
 
	@Override
	public void setProperties(Properties properties) {
		String limit = properties.getProperty("limit","10");
		this.page = Integer.parseInt(limit);
		this.dbType = properties.getProperty("dbType", "mysql");
	}
 
}

Previously, in the findByPager method of the service, in order to pass in two parameters to the limit, where the page is calculated, there is no need to calculate by using the Interceptor:

public Pager<User> findByPager(int page,int size){
	Map<String, Object> params = new HashMap<String, Object>();
	params.put("page", page);
	params.put("size", size);
	Pager<User> pager = new Pager<User>();
	List<User> list = userDao.findByPager(params);
	pager.setRows(list);
	pager.setTotal(userDao.count());
	return pager;
}

In spring configuration, add plugin settings:

Here, you may have guessed that MyPageInterceptor is actually an interceptor. When the program executes findByPager method, the splicing of limit page and size will be added to the statement, or it is the same as the first native implementation idea. Therefore, usermapper The limit #{page},#{size} in the query corresponding to findByPager in the XML configuration file is removed and changed to the following:

So far, the paging function is also realized through the interceptor plug-in.  

PageHelper implementation:

This implementation requires us to introduce maven dependencies.

<dependency>
	<groupId>com.github.pagehelper</groupId>
	<artifactId>pagehelper</artifactId>
	<version>4.2.1</version>
 </dependency>

spring. Modify the XML configuration file:

 <bean id="pageInterceptor" class="com.github.pagehelper.PageHelper">
	<property name="properties">
		<props>
			<prop key="helperDialect">mysql</prop>
			<prop key="reasonable">true</prop>
			<prop key="supportMethodsArguments">true</prop>
			<prop key="params">count=countSql</prop>
		</props>
	</property>
 </bean>
 
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
	<property name="dataSource" ref="dataSource" />
	<property name="mapperLocations"  value="classpath:com/xxx/mybatis/dao/*Mapper.xml"/>
	<property name="plugins" ref="pageInterceptor"></property>
</bean> 

Modify the method of the service layer:

public Pager<User> findByPager(int page,int size){
	Pager<User> pager = new Pager<User>();
	Page<User> res = PageHelper.startPage(page,size);
	userDao.findAll();
	pager.setRows(res.getResult());
	pager.setTotal(res.getTotal());
	return pager;
}

So far, the PageHelper tool method has also been implemented. In fact, the PageHelper method is also a third-party implementation of the second way to use the Interceptor interceptor. It internally helps us realize the functions of the Interceptor. So we don't need to customize the MyPageInterceptor class. In fact, when running the query method, intercept and set paging parameters. So PageHelper The start page (page, size) sentence needs to display the call, and then execute userdao Findall(), when querying all user information, will set a paging parameter so that the returned result is only the paging result, not all sets.

Reprinted from: https://blog.csdn.net/feinifi/article/details/88769101

Keywords: Mybatis PageHelper

Added by FrobinRobin on Sun, 19 Dec 2021 17:23:28 +0200