mybatis開發一個分頁插件、mybatis實現分頁、mybatis攔截器

通過官網的mybatis插件說明可知，我們可以通過攔截器進行開發一個插件。

例如這樣的：

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);// 開始分頁MagicPage.startPage(1, 3);// 查詢List<UserEntity> list = mapper.selectAll();System.out.println(list);// 獲取到分頁的內容PageInfo page = MagicPage.getPage();System.out.println(page);

格式就是通過線程變量來實現

MagicPage.start(page,size);
// 執行查詢
...
MagicPage.getPage();

先編寫一個 MagicPage

import cn.hutool.core.lang.Assert;/*** @author lingkang* Created by 2024/3/3*/
public class MagicPage {private static final ThreadLocal<PageInfo> local = new ThreadLocal<>();/*** 開始分頁** @param page 默認 1* @param size 默認 10*/public static void startPage(int page, int size) {Assert.isTrue(page > 0, "page 最小值為 1");Assert.isTrue(size > 0, "size 必須大于 0");PageInfo info = new PageInfo();info.setSize(size);info.setPage(page);local.set(info);}public static PageInfo getPage() {return local.get();}public static void removePage() {local.remove();}
}

PageInfo

@Data
public class PageInfo {private int size;private int page;private long total;
}

mybatis插件實現：

import cn.hutool.core.convert.BasicType;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Collection;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;/*** 查詢分頁攔截** @author lingkang* Created by 2024/3/3*/
@Intercepts({@Signature(type = StatementHandler.class,method = "prepare",args = {Connection.class, Integer.class})})
public class MagicPageInterceptor implements Interceptor {@Overridepublic Object intercept(Invocation invocation) throws Throwable {PageInfo page = MagicPage.getPage();if (page == null)return invocation.proceed();StatementHandler statementHandler = (StatementHandler) invocation.getTarget();BoundSql boundSql = statementHandler.getBoundSql();if (!boundSql.getSql().toLowerCase().contains("select")) {return invocation.proceed();}Connection conn = (Connection) invocation.getArgs()[0];String totalSql = "select count(*) ";TotalConvert convert = pageSql(boundSql.getSql());if (convert.getOrderIndex() != 0)totalSql += boundSql.getSql().substring(convert.getTotalIndex()) + boundSql.getSql().substring(convert.getOrderIndex());elsetotalSql += boundSql.getSql().substring(convert.getTotalIndex());PreparedStatement statement = conn.prepareStatement(totalSql);Object parameterObject = boundSql.getParameterObject();// 設置參數if (parameterObject != null) {if (parameterObject.getClass().isPrimitive() || BasicType.WRAPPER_PRIMITIVE_MAP.containsKey(parameterObject.getClass())) {statement.setObject(1, parameterObject);} else if (parameterObject.getClass().isArray()) {Object[] arr = (Object[]) parameterObject;for (int i = 1; i <= arr.length; i++)statement.setObject(i, arr[i]);} else if (parameterObject instanceof Collection) {int i = 1;for (Object o : (Collection) parameterObject)statement.setObject(i++, o);} else if (parameterObject instanceof Map) {Map map = (Map) parameterObject;int i = 1;for (ParameterMapping mapping : boundSql.getParameterMappings())statement.setObject(i++, map.get(mapping.getProperty()));} else {// 將它當成對象int i = 1;for (ParameterMapping mapping : boundSql.getParameterMappings()) {Field field = parameterObject.getClass().getDeclaredField(mapping.getProperty());field.setAccessible(true);statement.setObject(i++, field.get(parameterObject));}}}ResultSet resultSet = statement.executeQuery();if (resultSet.next()) {long total = resultSet.getLong(1);page.setTotal(total);resultSet.close();}if (page.getTotal() > 0) {// 存在分頁String sql = boundSql.getSql() + " limit " + (page.getPage() - 1) * page.getSize() + "," + page.getSize();Field field = BoundSql.class.getDeclaredField("sql");field.setAccessible(true);field.set(boundSql, sql);}return invocation.proceed();}Pattern orderBy = Pattern.compile("order\\s+by");/*** select id,(select username from user where id=t_order.userid) as username from t_order order by id desc*/private TotalConvert pageSql(String sql) {TotalConvert convert = new TotalConvert();sql = sql.toLowerCase();int index = sql.indexOf("from");int start = sql.substring(0, index).indexOf("(");while (start != -1) {start = sql.indexOf("(", start + 1);if (start == -1)index = sql.indexOf("from", index + 1);}convert.setTotalIndex(index);// 匹配排序，可能存在排序，需要特殊處理Matcher matcher = orderBy.matcher(sql);if (matcher.find()) {convert.setOrderIndex(matcher.start());}return convert;}
}

需要注意，上面的插件只是做了mysql語法下的分頁，如果是sqlserver以及其他數據庫需要做調整

配置我們的插件

// 將攔截器添加到配置
configuration.addInterceptor(new MagicPageInterceptor());

執行效果：