MyBatis實現分頁的方式

FlyHippo發表於2024-05-27

MyBatis實現分頁的方式

1.SQL原生拼接

currIndexpageSize作為查詢時引數在執行查詢SQL時填充

xml檔案

<select id="queryStudentsBySql" parameterType="map" resultMap="studentmapper">
        select * from student limit #{currIndex} , #{pageSize}
</select>

service

介面
List<Student> queryStudentsBySql(int currPage, int pageSize);
實現類
public List<Student> queryStudentsBySql(int currPage, int pageSize) {
        Map<String, Object> data = new HashedMap();
        data.put("currIndex", (currPage-1)*pageSize);
        data.put("pageSize", pageSize);
        return studentMapper.queryStudentsBySql(data);
}

2.藉助MyBabtis提供的RowBounds進行分頁查詢

資料量小時,RowBounds不失為一種好辦法。但是資料量大時,實現攔截器就很有必要了。

mybatis介面加入RowBounds引數

public List<UserBean> queryUsersByPage(String userName, RowBounds rowBounds);

RowBounds

public class RowBounds {
    //預設值為0~~Java最大整數
    public static final int NO_ROW_OFFSET = 0;
    public static final int NO_ROW_LIMIT = Integer.MAX_VALUE;
    public static final RowBounds DEFAULT = new RowBounds();
    //偏移量,即從第幾行開始讀取
    private final int offset;
    //限制,即每頁顯示記錄數量
    private final int limit;
 
    public RowBounds() {
        this.offset = NO_ROW_OFFSET;
        this.limit = NO_ROW_LIMIT;
    }
    public RowBounds(int offset, int limit) {
        this.offset = offset;
        this.limit = limit;
    }
    public int getOffset() {
        return offset;
    }
    public int getLimit() {
        return limit;
    }
}

Service

@Service
public class OrderInfoService {
 
    @Resource
    private OrderInfoMapper orderInfoMapper;
 
    public List<OrderInfo> getOrderInfoList(int pageNow, int pageSize) {
        RowBounds rowBounds = new RowBounds(pageNow,pageSize);
        return orderInfoMapper.getOrderInfoList(rowBounds);
    }
}

Mapper

@Mapper
public interface OrderInfoMapper {
    List<OrderInfo> getOrderInfoList(RowBounds rowBounds);
}

3.自定義攔截器 Intercepter

自定義攔截器外掛分頁 需要自己定義一個類實現Interceptor介面,這個介面是Mybatis提供的。任何分頁外掛想要對Mybatis進行分頁就必須實現Interceptor介面,包括後面PageHelper分頁外掛。

①、建立MyPageInterceptor

/**
 * @Intercepts 表示是一個攔截器
 * @Signature 攔截器的簽名
 * type 攔截的型別 四大物件之一( Executor,ResultSetHandler,ParameterHandler,StatementHandler)
 * method 攔截的方法
 */
@Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class, Integer.class })})
public class MyPageInterceptor implements Interceptor {
 
    //當前頁碼
    private int currPage;
    //每頁顯示的條目數
    private int pageSize;
    //資料庫型別
    private String dbType;
 
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        System.out.println("plugin is running...");
        //獲取StatementHandler,預設是RoutingStatementHandler
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        //獲取statementHandler包裝類
        MetaObject MetaObjectHandler = SystemMetaObject.forObject(statementHandler);
 
        //分離代理物件鏈
        while (MetaObjectHandler.hasGetter("h")) {
            Object obj = MetaObjectHandler.getValue("h");
            MetaObjectHandler = SystemMetaObject.forObject(obj);
        }
 
        while (MetaObjectHandler.hasGetter("target")) {
            Object obj = MetaObjectHandler.getValue("target");
            MetaObjectHandler = SystemMetaObject.forObject(obj);
        }
 
        //獲取連線物件
        //Connection connection = (Connection) invocation.getArgs()[0];
        //object.getValue("delegate");  獲取StatementHandler的實現類
 
        //獲取查詢介面對映的相關資訊
        MappedStatement mappedStatement = (MappedStatement) MetaObjectHandler.getValue("delegate.mappedStatement");
        String mapId = mappedStatement.getId();
 
        //statementHandler.getBoundSql().getParameterObject();
 
        //攔截以.ByPage結尾的請求,分頁功能的統一實現
        if (mapId.matches(".+ByPage$")) {
            //獲取進行資料庫操作時管理引數的handler
            ParameterHandler parameterHandler = (ParameterHandler) MetaObjectHandler.getValue("delegate.parameterHandler");
            //獲取請求時的引數
            Map<String, Object> paraObject = (Map<String, Object>) parameterHandler.getParameterObject();
            //也可以這樣獲取
            //paraObject = (Map<String, Object>) statementHandler.getBoundSql().getParameterObject();
 
            //引數名稱和在service中設定到map中的名稱一致
            currPage = (int) paraObject.get("currPage");
            pageSize = (int) paraObject.get("pageSize");
 
            String sql = (String) MetaObjectHandler.getValue("delegate.boundSql.sql");
            //也可以透過statementHandler直接獲取
            //sql = statementHandler.getBoundSql().getSql();
 
            //構建分頁功能的sql語句
            String limitSql;
            sql = sql.trim();
            limitSql = sql + " limit " + (currPage - 1) * pageSize + "," + pageSize;
 
            //將構建完成的分頁sql語句賦值個體'delegate.boundSql.sql',偷天換日
            MetaObjectHandler.setValue("delegate.boundSql.sql", limitSql);
        }
        //呼叫原物件的方法,進入責任鏈的下一級
        return invocation.proceed();
    }
 
    //獲取代理物件
    @Override
    public Object plugin(Object o) {
        //生成object物件的動態代理物件
        return Plugin.wrap(o, this);
    }
 
    //設定代理物件的引數
    @Override
    public void setProperties(Properties properties) {
        //如果專案中分頁的pageSize是統一的,也可以在這裡統一配置和獲取,這樣就不用每次請求都傳遞pageSize引數了。引數是在配置攔截器時配置的。
        String limit1 = properties.getProperty("limit", "10");
        this.pageSize = Integer.valueOf(limit1);
        this.dbType = properties.getProperty("dbType", "mysql");
    }
}

②、全域性配置檔案增加plugin設定(注意位置)

<!-- 配置自定義分頁外掛 -->
<plugins>  
    <plugin interceptor="com.thr.interceptor.MyPageInterceptor"></plugin>
 </plugins>

③、介面方法

//分頁查詢所有使用者,透過原生自定義攔截器
List<User> selectAllUserByPage(Map map);

由於攔截器中設定了攔截以ByPage結尾的方法,所以方法一定要命名正確,

4. PageHelper外掛

①、引入PageHelper依賴:

<!-- pagehelper分頁外掛 -->
<dependency>  
    <groupId>com.github.pagehelper</groupId>  
    <artifactId>pagehelper</artifactId>  
    <version>5.2.0</version></dependency>
</dependency>

②、全域性配置檔案增加plugin設定(注意位置)

<!-- 配置分頁外掛 -->
<plugins>  
    <!-- PageHelper5版本配置 -->  
    <plugin` `interceptor="com.github.pagehelper.PageInterceptor"/>
</plugins>

③、測試方法

//分頁查詢所有使用者資訊,透過PageHelper
@Test
public void selectAllUserByPageHelper(){  
    int currPage = 2;
    //當前頁碼  int` `pageSize = 3;
    //當前頁記錄數量  
    //表示獲取第2頁,3條內容,預設會查詢總數count  
    PageHelper.startPage(currPage,pageSize);  
    List<User> userList = mapper.selectAllUserByPageHelper();  
    for(User user : userList) 
    {    System.out.println(user);  
    }
}

相關文章