mybatis分页插件的简单实现

mybatis分页插件的简单实现

1. mybatis使用的版本是3.3.0，spring-mybatis使用的是1.1.0，使用的数据库是mySQL。

2. 自定义拦截器（）

package com.xinrui.micromessage.action.interceptor;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
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.BoundSql;
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.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;

import com.xinrui.micromessage.bean.Page;

/**
 * 
 * @ClassName: PageIntercetor
 * @Description: 分页拦截器
 * @author 梁志成
 * @date 2016年3月19日 上午11:52:02
 *
 */
@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }) })
public class PageIntercetor implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler statementHandler = (StatementHandler) invocation
                .getTarget();
        MetaObject metaObject = MetaObject.forObject(statementHandler,
                SystemMetaObject.DEFAULT_OBJECT_FACTORY,
                SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY,
                new DefaultReflectorFactory());
        MappedStatement mappedStatement = (MappedStatement) metaObject
                .getValue("delegate.mappedStatement");
        // 配置文件中SQL语句的ID
        String id = mappedStatement.getId();
        // 拦截原始sql语句中以ByPage结尾的语句
        if (id.matches(".+ByPage$")) {
            BoundSql boundSql = statementHandler.getBoundSql();
            // 原始的SQL语句
            String sql = boundSql.getSql();
            // 查询总条数的SQL语句
            String countSql = "select count(*) from (" + sql + ")a";
            Connection connection = (Connection) invocation.getArgs()[0];
            PreparedStatement countStatement = connection
                    .prepareStatement(countSql);
            ParameterHandler parameterHandler = (ParameterHandler) metaObject
                    .getValue("delegate.parameterHandler");
            parameterHandler.setParameters(countStatement);
            ResultSet rs = countStatement.executeQuery();

            Map<?, ?> parameter = (Map<?, ?>) boundSql.getParameterObject();
            Page page = (Page) parameter.get("page");
            if (rs.next()) {
                page.setTotalNumber(rs.getInt(1));
            }
            // 改造后带分页查询的SQL语句
            String pageSql = sql + " limit " + page.getDbIndex() + ","
                    + page.getDbNumber();
            metaObject.setValue("delegate.boundSql.sql", pageSql);
        }
        return invocation.proceed();
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties arg0) {

    }

}

3.分页实体类

package com.xinrui.micromessage.bean;

/**
 * 分页对应的实体类
 */
public class Page {
    /**
     * 总条数
     */
    private int totalNumber;
    /**
     * 当前第几页
     */
    private int currentPage;
    /**
     * 总页数
     */
    private int totalPage;
    /**
     * 每页显示条数
     */
    private int pageNumber = 5;
    /**
     * 数据库中limit的参数，从第几条开始取
     */
    private int dbIndex;
    /**
     * 数据库中limit的参数，一共取多少条
     */
    private int dbNumber;

    /**
     * 根据当前对象中属性值计算并设置相关属性值
     */
    public void count() {
        // 计算总页数
        int totalPageTemp = this.totalNumber / this.pageNumber;
        int plus = (this.totalNumber % this.pageNumber) == 0 ? 0 : 1;
        totalPageTemp = totalPageTemp + plus;
        if(totalPageTemp <= 0) {
            totalPageTemp = 1;
        }
        this.totalPage = totalPageTemp;

        // 设置当前页数
        // 总页数小于当前页数，应将当前页数设置为总页数
        if(this.totalPage < this.currentPage) {
            this.currentPage = this.totalPage;
        }
        // 当前页数小于1设置为1
        if(this.currentPage < 1) {
            this.currentPage = 1;
        }

        // 设置limit的参数
        this.dbIndex = (this.currentPage - 1) * this.pageNumber;
        this.dbNumber = this.pageNumber;
    }

    public int getTotalNumber() {
        return totalNumber;
    }

    public void setTotalNumber(int totalNumber) {
        this.totalNumber = totalNumber;
        this.count();
    }

    public int getCurrentPage() {
        return currentPage;
    }

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

    public int getTotalPage() {
        return totalPage;
    }

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

    public int getPageNumber() {
        return pageNumber;
    }

    public void setPageNumber(int pageNumber) {
        this.pageNumber = pageNumber;
        this.count();
    }

    public int getDbIndex() {
        return dbIndex;
    }

    public void setDbIndex(int dbIndex) {
        this.dbIndex = dbIndex;
    }

    public int getDbNumber() {
        return dbNumber;
    }

    public void setDbNumber(int dbNumber) {
        this.dbNumber = dbNumber;
    }
}

4.在mybatis-config.xml中注册该插件（拦截器）

<configuration>
    <plugins><!-- 为拦截器注册 -->
        <plugin interceptor="com.xinrui.micromessage.action.interceptor.PageIntercetor"></plugin>
    </plugins>
</configuration>

5.controller 层

package com.xinrui.micromessage.action;

import java.util.List;
import java.util.regex.Pattern;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;

import com.xinrui.micromessage.bean.Message;
import com.xinrui.micromessage.bean.Page;
import com.xinrui.micromessage.service.impl.PageTwoServiceImpl;

/**
 * 
 * @ClassName: PageTwoController
 * @Description: 使用拦截器实现分页控制
 * @author 梁志成
 * @date 2016年3月19日 下午4:33:20
 *
 */
@Controller
public class PageTwoController {

    @Resource
    private PageTwoServiceImpl pageTwoService;

    @RequestMapping(value = "pagetwo", method = RequestMethod.GET)
    public ModelAndView pagetwo(String command, String description,
            String currentPage) {
        // 进入主框架
        ModelAndView mav = new ModelAndView("back/list");
        // 创建分页对象
        Page page = new Page();
        // 页数由数字组成,不能超过9位
        Pattern pattern = Pattern.compile("[0-9]{1,9}");
        // 页数不符合规则,设置为第一页
        if (StringUtils.isEmpty(currentPage)
                || !pattern.matcher(currentPage).matches()) {
            page.setCurrentPage(1);
        } else {
            page.setCurrentPage(Integer.valueOf(currentPage));
        }
        List<Message> messageList = pageTwoService.queryMessageListByPage(command, description, page);
        // 向页面输出内容
        mav.addObject("command", command);
        mav.addObject("description", description);
        mav.addObject("page", page);
        mav.addObject("messageList", messageList);
        return mav;
    }
}

6.service层

package com.xinrui.micromessage.service.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.xinrui.micromessage.bean.Message;
import com.xinrui.micromessage.bean.Page;
import com.xinrui.micromessage.dao.PageTwoDao;

/**
 * 
 * @ClassName: PageTwoServiceImpl
 * @Description: 使用拦截器实现分页拦截实现类
 * @author 梁志成
 * @date 2016年3月19日 下午4:26:48
 *
 */
@Service
public class PageTwoServiceImpl {

    @Resource
    private PageTwoDao pageTwoDao;

    /**
     * 
     * @Title: queryMessageListByPage
     * @Description: 使用拦截器实现分页拦截服务
     * @param @param command
     * @param @param description
     * @param @param page
     * @param @return
     * @return List<Message>
     * @throws
     */
    @Transactional
    public List<Message> queryMessageListByPage(String command,
            String description, Page page) {
        Map<String, Object> parameter = new HashMap<String, Object>();
        // 组织消息对象
        Message message = new Message();
        message.setCommand(command);
        message.setDescription(description);
        parameter.put("message", message);
        parameter.put("page", page);

        return this.pageTwoDao.queryMessageListByPage(parameter);
    }
}

7.dao层

package com.xinrui.micromessage.dao;

import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.xinrui.micromessage.bean.Message;

/**
 * 
 * @ClassName: PageTwoDao
 * @Description: 分页查询与数据库交互部分
 * @author 梁志成
 * @date 2016年3月19日 下午4:24:11
 *
 */
@Repository
public class PageTwoDao extends BaseDao {
    /**
     * 
     * @Title: queryMessageListByPage
     * @Description: 分页查询消息表
     * @param @param parameter
     * @param @return
     * @return List<Message>
     * @throws
     */
    public List<Message> queryMessageListByPage(Map<String, Object> parameter) {
        return this.getSqlSession().selectList(
                "com.xinrui.micromessage.bean.Message.queryMessageListByPage",
                parameter);
    }
}

8.mybatis mapper

<select id="queryMessageListByPage" parameterType="java.util.Map"
        resultType="com.xinrui.micromessage.bean.Message">
        select
        id,command,description,content
        from message
        <where>
            <if
                test="message.command != null and !&quot;&quot;.equals(message.command.trim())">
                and command=#{message.command}
            </if>
            <if
                test="message.description != null and !&quot;&quot;.equals(message.description.trim())">
                and description like '%' #{message.description} '%'
            </if>
        </where>
        order by id
    </select>

9.jsp页面呈现

                        <div class='page fix'>
                            共 <b>${page.totalNumber }</b> 条
                            <c:if test="${page.currentPage != 1 }">
                                <a href="javascript:changeCurrentPage(1)" class='first'>首页</a>
                                <a href="javascript:changeCurrentPage(${page.currentPage-1 })" class='pre'>上一页</a>
                            </c:if>
                            当前第<span>${page.currentPage }/${page.totalPage }</span>页
                            <c:if test="${page.currentPage != page.totalPage }">
                                <a href="javascript:changeCurrentPage(${page.currentPage+1 })" class='next'>下一页</a>
                                <a href="javascript:changeCurrentPage(${page.totalPage })" class='last'>末页</a>
                            </c:if>
                            跳至&nbsp;<input  id="currentPageText" type='text' value="${page.currentPage}" class='allInput w28' />&nbsp;页&nbsp;
                            <a href="javascript:changeCurrentPage($('#currentPageText').val())" class='go'>GO</a>
                        </div>

10.js脚本

function changeCurrentPage(currentPage){
    $("#currentPage").val(currentPage);
    $("#mainForm").submit();
}