MyBatis物理分页的代码实现

一.分页

MyBatis有两种分页方法：内存分页，也就是假分页，本质是查出所有的数据然后根据游标的方式，截取需要的记录，如果数据量大，执行效率低，可能造成内存溢出。物理分页，就是数据库本身提供了分页方式，如MySql的limit，执行效率高，不同数据库实现不同。

MyBatis Generator使用：MyBatis Generator使用示例

Spring集成MyBatis：Spring集成MyBatis持久层框架

 

二.MyBatis执行流程

MyBatis执行sql流程如下图，实现数据库的物理分页，需要通过拦截StatementHandler重写的sql语句。

 

三.分页实现

1.实现MyBatis的Interceptor接口，创建PageInterceptor类

@Intercepts({@Signature(type=StatementHandler.class, method = "prepare", args={Connection.class, Integer.class})})
public class PageInterceptor implements Interceptor {

    private String sqlRegEx = ".*Page";

    public Object intercept(Invocation invocation) throws Throwable {
        RoutingStatementHandler handler = (RoutingStatementHandler)invocation.getTarget();
        StatementHandler delegate = (StatementHandler) ReflectUtil.getFieldValue(handler, "delegate");
        BoundSql boundSql = delegate.getBoundSql();
        MappedStatement mappedStatement = (MappedStatement)ReflectUtil.getFieldValue(delegate, "mappedStatement");
        // 获取参数
        Object parameterObject = boundSql.getParameterObject();
        // 判断是否分页
        if (mappedStatement.getId().matches(sqlRegEx)) {
            Page page = (Page) ((Map<?, ?>) parameterObject).get("page");
            if (page != null) {
                Connection connection = (Connection) invocation.getArgs()[0];
                // 获取mapper映射文件中对应的sql语句
                String sql = boundSql.getSql();
                // 给当前page参数设置总记录数
                this.setPageParameter(mappedStatement, connection, boundSql, page);
                // 获取分页sql语句
                String pageSql = this.getPageSql(page, sql);
                ReflectUtil.setFieldValue(boundSql, "sql", pageSql);
            }
        }
        return invocation.proceed();
    }

    /**
     * 从数据库里查询总的记录数并计算总页数，回写进分页参数page
     * @param mappedStatement
     * @param connection
     * @param boundSql
     * @param page
     */
    private void setPageParameter(MappedStatement mappedStatement, Connection connection, BoundSql boundSql, Page page) {
        // 获取mapper映射文件中对应的sql语句
        String sql = boundSql.getSql();
        // 获取计算总记录数的sql语句
        String countSql = this.getCountSql(sql);
        // 获取BoundSql参数映射
        List<ParameterMapping> parameterMappinglist = boundSql.getParameterMappings();
        // 构造查询总量的BoundSql
        BoundSql countBoundSql = new BoundSql(mappedStatement.getConfiguration(), countSql, parameterMappinglist, boundSql.getParameterObject());
        ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, boundSql.getParameterObject(), countBoundSql);
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            // 通过connection建立countSql对应的PreparedStatement对象
            pstmt = connection.prepareStatement(countSql);
            parameterHandler.setParameters(pstmt);
            // 执行countSql语句
            rs = pstmt.executeQuery();
            if (rs.next()) {
                int totalRecord = rs.getInt(1);
                page.setTotalRecord(totalRecord);
                page.setTotalPage(totalRecord/page.getPageSize() + (totalRecord % page.getPageSize() == 0? 0: 1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据源sql语句获取对应的查询总记录数的sql语句
     * @param sql
     * @return
     */
    private String getCountSql(String sql) {
        int index = sql.indexOf("from");
        return "select count(*) " + sql.substring(index);
    }

    /**
     * 获取MySql数据库的分页查询语句
     * @param page
     * @param sql
     * @return
     */
    private String getPageSql(Page<?> page, String sql) {
        StringBuffer sqlBuffer = new StringBuffer(sql);
        int offset = (page.getPageNum() - 1) * page.getPageSize();
        sqlBuffer.append(" limit ").append(offset).append(",").append(page.getPageSize());
        return sqlBuffer.toString();
    }

    /**
     * 只处理StatementHandler类型
     * @param o
     * @return
     */
    public Object plugin(Object o) {
        if (o instanceof  StatementHandler) {
            return Plugin.wrap(o, this);
        } else {
            return o;
        }
    }

    /**
     * 拦截器属性设定
     * @param properties
     */
    public void setProperties(Properties properties) {
    }

    public String getSqlRegEx() {
        return sqlRegEx;
    }

    public void setSqlRegEx(String sqlRegEx) {
        this.sqlRegEx = sqlRegEx;
    }
}

2.保存页面的相关信息，创建Page类

public class Page<T> {

    private int pageNum = 1;

    private int pageSize = 5;

    private int totalRecord;

    private int totalPage;

    private List<T> results;

    public int getPageNum() {
        return pageNum;
    }

    public void setPageNum(int pageNum) {
        this.pageNum = pageNum;
    }

    public int getPageSize() {
        return pageSize;
    }

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

    public int getTotalRecord() {
        return totalRecord;
    }

    public void setTotalRecord(int totalRecord) {
        this.totalRecord = totalRecord;
    }

    public int getTotalPage() {
        return totalPage;
    }

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

    public List<T> getResults() {
        return results;
    }

    public void setResults(List<T> results) {
        this.results = results;
    }
}

3.通过反射获取对象的属性，创建ReflectUtil工具类，用于获取RoutingStatementHandler对象的私有属性delegate

public class ReflectUtil {

    public static Object getFieldValue(Object obj, String fieldName) {
        Object result = null;
        Field field = ReflectUtil.getField(obj, fieldName);
        if (null != field) {
            field.setAccessible(true);
            try {
                result = field.get(obj);
            } catch (IllegalArgumentException e) {
            } catch (IllegalAccessException e) {
            }
        }
        return result;
    }

    private static Field getField(Object obj, String fieldName) {
        Field field = null;
        for (Class<?> clazz = obj.getClass(); clazz != Object.class; clazz = clazz.getSuperclass()) {
            try {
                field = clazz.getDeclaredField(fieldName);
                break;
            } catch (NoSuchFieldException e) {
            }
        }
        return field;
    }

    public static void setFieldValue(Object obj, String fieldName, String fieldValue) {
        Field field = ReflectUtil.getField(obj, fieldName);
        if (null != field) {
            try {
                field.setAccessible(true);
                field.set(obj, fieldValue);
            } catch (IllegalArgumentException e) {
            } catch (IllegalAccessException e) {
            }
        }
    }
}

4.启用分页Interceptor，编辑applicationContext_database.xml

    <!-- mybatis配置,mapper.xml文件扫描 -->
    <bean id="sessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="configLocation" value="classpath:/mybatis/mybatis.xml"/>
        <property name="mapperLocations" value="classpath:/mybatis/mapper/*Mapper.xml"/>
        <property name="dataSource" ref="dataSource"/>

        <property name="plugins">
            <array>
                <!-- 分页 -->
                <bean class="com.learn.spring.server.intercept.PageInterceptor">
                    <property name="properties">
                        <value>
                            sqlRegEx = ".*Page"
                        </value>
                    </property>
                </bean>
            </array>
        </property>
    </bean>

 

四.调用示例

1.编辑UserServiceImpl类

@Service
public class UserServiceImpl implements UserService {

    @Resource
    private UserDOMapper userDao;

    @Override
    public Page<UserDO> listByCondPage(Integer status, Page page) {
        Map<String, Object> param = new HashMap<>();
        param.put("status", status);
        param.put("page", page);
        List<UserDO> userDOList = userDao.selectByCondPage(param);
        page.setResults(userDOList);
        return page;
    }
}

2.编辑IndexController类，调用Service

@Controller
@RequestMapping("/server")
public class IndexController {

    @Resource
    private UserService userService;

    @ResponseBody
    @RequestMapping("/list")
    public Object list(Integer status, Integer pageNum, Integer pageSize) {
        Page<UserDO> userDOPage = new Page<>();
        userDOPage.setPageNum(pageNum);
        userDOPage.setPageSize(pageSize);
        return userService.listByCondPage(status, userDOPage);
    }
}