自定义分页插件

最新推荐文章于 2023-05-24 09:08:55 发布

qq_38999509

最新推荐文章于 2023-05-24 09:08:55 发布

阅读量207

点赞数

分类专栏： mybatis

本文链接：https://blog.csdn.net/qq_38999509/article/details/119142330

版权

mybatis 专栏收录该内容

8 篇文章 1 订阅

订阅专栏

分页需要的实体类

import com.fasterxml.jackson.annotation.JsonFormat;
import org.springframework.format.annotation.DateTimeFormat;

import java.io.Serializable;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

public class Base implements Serializable {


    /**
     *
     */
    private static final long serialVersionUID = 1520837688012998514L;

    private Integer createdBy;
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date createdDate;
    private Integer updatedBy;
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date updatedDate;
    //删除字段状态
    private String validFlag = "Y";

    private String orderSort = "desc";//升序还是降序
    private String orderField;//排序字段

    protected int pageSize = 10; // 每页默认10条数据
    protected int page = 1; // 当前页
    protected int totalRows = 0; // 总数据数
    protected boolean pagination = false; // 是否分页


    /**
     * 动态字段. 在ibatis文件中可用“dynamicFields.xxx”方式读取动态字段值
     */
    protected Map dynamicFields = new HashMap();

    public Map getDynamicFields() {
        return dynamicFields;
    }

    public void setDynamicFields(Map dynamicFields) {
        this.dynamicFields = dynamicFields;
    }

    /**
     * 设置动态字段值.
     *
     * @param fieldName 字段名称
     * @param value     字段值
     */
    public void setField(String fieldName, Object value) {
        dynamicFields.put(fieldName, value);
    }

    /**
     * 返回动态字段值.
     *
     * @param fieldName 字段名称
     * @return 对象
     */
    public Object getField(String fieldName) {
        if (dynamicFields == null) {
            return null;
        }
        return getDynamicFields().get(fieldName);
    }

    public String getOrderSort() {
        return orderSort;
    }

    public void setOrderSort(String orderSort) {
        this.orderSort = orderSort;
    }

    public String getOrderField() {
        return orderField;
    }

    public void setOrderField(String orderField) {
        this.orderField = orderField;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public int getPage() {
        return page;
    }

    public void setPage(int page) {
        this.page = page;
    }

    public int getTotalRows() {
        return totalRows;
    }

    public void setTotalRows(int totalRows) {
        this.totalRows = totalRows;
    }

    public boolean isPagination() {
        return pagination;
    }

    public void setPagination(boolean pagination) {
        this.pagination = pagination;
    }

    public String getValidFlag() {
        return validFlag;
    }

    public void setValidFlag(String validFlag) {
        this.validFlag = validFlag;
    }

    public Integer getCreatedBy() {
        return createdBy;
    }

    public void setCreatedBy(Integer createdBy) {
        this.createdBy = createdBy;
    }

    public Date getCreatedDate() {
        return createdDate;
    }

    public void setCreatedDate(Date createdDate) {
        this.createdDate = createdDate;
    }

    public Integer getUpdatedBy() {
        return updatedBy;
    }

    public void setUpdatedBy(Integer updatedBy) {
        this.updatedBy = updatedBy;
    }

    public Date getUpdatedDate() {
        return updatedDate;
    }

    public void setUpdatedDate(Date updatedDate) {
        this.updatedDate = updatedDate;
    }
}


import java.io.Serializable;

public class Page implements Serializable{
    private static final long serialVersionUID = 1L;
    // 每页默认10条数据
    protected int pageSize = 10;
    // 当前页
    protected int currentPage = 1;
    // 总数据数
    protected int totalRows = 0;
    //总页数
    protected int totalPage;
    // 是否分页
    protected boolean pagination = true;

    public Page() {

    }

    public Page(Page clonePage) {
        this.pageSize = clonePage.pageSize;
        this.currentPage = clonePage.currentPage;
        this.totalRows = clonePage.totalRows;
        this.totalPage = clonePage.totalPage;
        this.pagination = clonePage.pagination;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public int getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }

    public int getTotalRows() {
        return totalRows;
    }

    public void setTotalRows(int totalRows) {
        this.totalRows = totalRows;
        if (pageSize == 0) {
            pageSize = 10;
        }
        totalPage = (totalRows / pageSize) + (totalRows % pageSize > 0 ? 1 : 0);
    }

    public boolean isPagination() {
        return pagination;
    }

    public void setPagination(boolean pagination) {
        this.pagination = pagination;
    }

    public int getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }
}


public class PageContext {

    private static ThreadLocal<Page> context = new ThreadLocal<Page>();

    public static Page getContext() {
        Page page = context.get();
        if (page == null) {
            page = new Page();
            context.set(page);
        }
        return page;
    }

    public static void remove() {
        context.remove();
    }

}

分页插件


import org.apache.ibatis.executor.statement.BaseStatementHandler;
import org.apache.ibatis.executor.statement.RoutingStatementHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;
import org.springframework.util.StringUtils;

import javax.xml.bind.PropertyException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

//@Intercepts({ @Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class }) })
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class})})
public class PageInterceptor implements Interceptor {
    private Dialect dialect = null;

    /**
     * 拦截后要执行的方法
     */
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        if (invocation.getTarget() instanceof RoutingStatementHandler) {
            RoutingStatementHandler routingStatementHandler = (RoutingStatementHandler) invocation.getTarget();
            Object obj = routingStatementHandler.getParameterHandler().getParameterObject();
            Page page = PageContext.getContext();
            if (obj instanceof Base) {
                Base base = (Base) obj;
                page.setPageSize(base.getPageSize());
                page.setCurrentPage(base.getPage());
                page.setPagination(base.isPagination());
                if (page == null || !page.isPagination()) {
                    // 不需要分页,则直接执行
                    return invocation.proceed();
                }
            } else {
                // 不需要分页,则直接执行
                return invocation.proceed();
            }


            RoutingStatementHandler statementHandler = (RoutingStatementHandler) invocation.getTarget();
            BaseStatementHandler delegate = (BaseStatementHandler) ReflectUtil.getValueByFieldName(statementHandler, "delegate");
            BoundSql boundSql = delegate.getBoundSql();

            // 获取原始sql
            String originalSql = boundSql.getSql();

            // 求总行数
            Connection connection = (Connection) invocation.getArgs()[0];
            if (page.getTotalRows() == 0 || page.getCurrentPage() == 1) {
                int totalRows = getTotalRows(connection, delegate, page);
                // 设置总行数
                page.setTotalRows(totalRows);
            }
            // 目标sql执行分页后,设置pagination==false,(避免关联sql执行分页)
            page.setPagination(false);
            // 构造用于分页查询的sql
            reboundSql(boundSql, page, originalSql);
        }

        return invocation.proceed();
    }

    /**
     * 重构分页查询语句
     *
     * @param page
     * @param originalSql
     * @throws SecurityException
     * @throws NoSuchFieldException
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     */
    private void reboundSql(BoundSql boundSql, Page page, String originalSql) throws SecurityException, NoSuchFieldException, IllegalArgumentException, IllegalAccessException {
        String pageSql = generatePagesSql(originalSql, page);
        // 将分页sql语句反射回BoundSql.
        ReflectUtil.setValueByFieldName(boundSql, "sql", pageSql);
    }

    /**
     * 根据数据库方言，生成特定的分页sql
     *
     * @param sql
     * @param page
     * @return
     */
    private String generatePagesSql(String sql, Page page) {
        if (dialect == null) {
            return sql;
        }
        return dialect.getLimitString(sql, (page.getCurrentPage() - 1) * page.getPageSize(), page.getPageSize());
    }

    /**
     * 求总行数
     *
     * @return
     * @throws SQLException
     * @throws IllegalAccessException
     * @throws IllegalArgumentException
     * @throws NoSuchFieldException
     * @throws SecurityException
     */
    private int getTotalRows(Connection connection, BaseStatementHandler delegate, Page page) throws SQLException, SecurityException, NoSuchFieldException,
            IllegalArgumentException, IllegalAccessException {

        MappedStatement mappedStatement = (MappedStatement) ReflectUtil.getValueByFieldName(delegate, "mappedStatement");

        BoundSql boundSql = delegate.getBoundSql();
        Object parameterObject = boundSql.getParameterObject();
        String originalSql = boundSql.getSql();
        String countSql = "select count(0) from (" + originalSql + ") t"; // 记录统计
        PreparedStatement countStatement = connection.prepareStatement(countSql);
        ReflectUtil.setValueByFieldName(boundSql, "sql", countSql);

        DefaultParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, boundSql);
        parameterHandler.setParameters(countStatement);
        ResultSet rs = countStatement.executeQuery();
        int count = 0;
        if (rs.next()) {
            count = rs.getInt(1);
        }
        rs.close();
        countStatement.close();

        return count;

    }

    /**
     * 拦截器对应的封装原始对象的方法
     */
    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    /**
     * 设置注册拦截器时设定的属性
     */
    @Override
    public void setProperties(Properties properties) {
        String databaseType = properties.getProperty("dialect");
        if (StringUtils.isEmpty(databaseType)) {
            try {
                throw new PropertyException("databaseType property is not found!");
            } catch (PropertyException e) {
                e.printStackTrace();
            }
        }

        if ("mysql".equalsIgnoreCase(databaseType)) {
            dialect = new MySql5Dialect();
        } else if ("oracle".equalsIgnoreCase(databaseType)) {
            dialect = new OracleDialect();
        }
    }

    /**
     * 利用反射进行操作的一个工具类
     */
    private static class ReflectUtil {
        /**
         * 设置obj对象fieldName的属性值
         *
         * @param obj
         * @param fieldName
         * @param value
         * @throws SecurityException
         * @throws NoSuchFieldException
         * @throws IllegalArgumentException
         * @throws IllegalAccessException
         */
        public static void setValueByFieldName(Object obj, String fieldName, Object value) throws SecurityException, NoSuchFieldException, IllegalArgumentException,
                IllegalAccessException {
            Field field = obj.getClass().getDeclaredField(fieldName);
            if (field.isAccessible()) {
                field.set(obj, value);
            } else {
                field.setAccessible(true);
                field.set(obj, value);
                field.setAccessible(false);
            }
        }

        /**
         * 获取obj对象fieldName的属性值
         *
         * @param obj
         * @param fieldName
         * @return
         * @throws SecurityException
         * @throws NoSuchFieldException
         * @throws IllegalArgumentException
         * @throws IllegalAccessException
         */
        public static Object getValueByFieldName(Object obj, String fieldName) throws SecurityException, NoSuchFieldException, IllegalArgumentException, IllegalAccessException {
            Field field = getFieldByFieldName(obj, fieldName);
            Object value = null;
            if (field != null) {
                if (field.isAccessible()) {
                    value = field.get(obj);
                } else {
                    field.setAccessible(true);
                    value = field.get(obj);
                    field.setAccessible(false);
                }
            }
            return value;
        }

        /**
         * 获取obj对象fieldName的Field
         *
         * @param obj
         * @param fieldName
         * @return
         */
        public static Field getFieldByFieldName(Object obj, String fieldName) {
            for (Class<?> superClass = obj.getClass(); superClass != Object.class; superClass = superClass.getSuperclass()) {
                try {
                    return superClass.getDeclaredField(fieldName);
                } catch (NoSuchFieldException e) {
                }
            }
            return null;
        }
    }
}

返回的对象



import com.fasterxml.jackson.annotation.JsonInclude;

import java.io.Serializable;
import java.util.HashMap;

@JsonInclude(JsonInclude.Include.NON_NULL)
public class Response implements Serializable{

	private int status = “SUCCESS”;

	public int getStatus() {
		return status;
	}

	public void setStatus(int status) {
		this.status = status;
	}

	public static Success success() {
		return new Success(new HashMap<>());
	}

	public static Success success(Object data) {
		Page page = PageContext.getContext();
		Page newPage = new Page(page);
		PageContext.remove();
		if (newPage.getTotalRows() > 0) {
			// 有分页数据,只能用getTotalRows判断
			// page.isPagination()在执行分页后设置为false,避免关联sql执行分页
			return new Success(data, newPage,newPage.totalRows);
		}
		return new Success(data);
	}
	
	public static Success success(int code, String msg) {
		return new Success(code, msg);
	}
	
	public static Failure fail() {
		return fail(IccConstants.FAIL, "亲~系统繁忙，请稍后再试！");
	}

	public static Failure fail(String msg) {
		return fail(1, msg);
	}
	public static FailureHint failHint(String msg) {
		return failHint(1, msg);
	}

	public static Failure fail(int code, String msg) {
		return new Failure(code, msg);
	}
	public static FailureHint failHint(int code, String msg) {
		return new FailureHint(code, msg);
	}

}

如下返回就会自动分页和填充数据

 @Override
    public Response serviceOrderList(ServiceOrder serviceOrder) {
        List<ServiceOrder> list = orderMapper.serviceOrderList(serviceOrder);
        return Response.success(list);
    }

mybatis-config.xml 加上如下代码

<!-- 分页插件  下面**.**写分页插件包名 -->
    <plugins>
        <plugin interceptor="**.**.PageInterceptor">
            <property name="dialect" value="mysql"/>
        </plugin>
    </plugins>