mybatis分页

Question

mybatis怎么实现物理分页啊，有没有什么插件之类的，类似于hibernate那样好用的方法

Answer 1

你可以写mybatis分页插件，mybatisConfig.xml中加

 <plugins>
    <plugin interceptor="com.xxx.xxx.utils.MyBatisPagePlugin">
      <property name="dialect" value="oracle"/>
      <property name="pageMatch" value=".*Page$"/>
    </plugin>
  </plugins>

插件类

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
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.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
 * 
 * @ClassName MyBatisPagePlugin 
 * @Description mybatis分页插件
 *
 */
@Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class})})
public class MyBatisPagePlugin implements Interceptor{
    private static final Logger log = LoggerFactory.getLogger(MyBatisPagePlugin.class);
    private String dialect="oracle";
    private String pageMatch=".*Page$";

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        if(invocation.getTarget() instanceof StatementHandler){
            StatementHandler statementHandler = (StatementHandler)invocation.getTarget();
            MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
            while(metaObject.hasGetter("h")){
                Object object = metaObject.getValue("h");
                metaObject = SystemMetaObject.forObject(object);
            }
            while(metaObject.hasGetter("target")){
                Object object = metaObject.getValue("target");
                metaObject = SystemMetaObject.forObject(object);
            }
            MappedStatement mappedStatement = (MappedStatement)metaObject.getValue("delegate.mappedStatement");
            if(mappedStatement.getId().matches(pageMatch)){
                BoundSql boundSql = (BoundSql)metaObject.getValue("delegate.boundSql");
                String sql = boundSql.getSql();
                Assert.notNull(metaObject.getValue("delegate.boundSql.parameterObject"), "parameterObject is null");
                PageBean pageBean = (PageBean)metaObject.getValue("delegate.boundSql.parameterObject.pageBean");
                Object needCountObj = metaObject.getValue("delegate.boundSql.parameterObject.needCount");
                Object needPageObj = metaObject.getValue("delegate.boundSql.parameterObject.needPage");
                boolean needCount = true;
                boolean needPage = true;
                if(needCountObj!=null){
                    needCount = (Boolean)needCountObj;
                }
                if(needPageObj!=null){
                    needPage = (Boolean)needPageObj;
                }
                if(needPage){
                    String pageSql = buildPageSql(sql, pageBean);
                    metaObject.setValue("delegate.boundSql.sql", pageSql);
                    if(needCount){
                        Connection conn = (Connection)invocation.getArgs()[0];
                        setPageParameters(sql, pageBean, conn, mappedStatement, boundSql);
                    }
                }
            }
        }
        return invocation.proceed();
    }
    @Override
    public Object plugin(Object target) {
        if(target instanceof StatementHandler){
            return Plugin.wrap(target, this);
        }else{
            return target;
        }
    }

    @Override
    public void setProperties(Properties properties) {
        this.dialect = properties.getProperty("dialect");
        this.pageMatch = properties.getProperty("pageMatch");
    }

    private String buildPageSql(String sql,PageBean pageBean){
        Assert.notNull(pageBean, "pageBean is null");
        return buildPageSqlForDialect(dialect, sql, pageBean).toString();
    }

    private StringBuilder buildPageSqlForDialect(String dialect,String sql,PageBean pageBean){
        StringBuilder builder = new StringBuilder();
        if("oracle".equalsIgnoreCase(dialect)){
            builder.append("SELECT * FROM (SELECT A.*,ROWNUM R FROM (");
            builder.append(sql);
            builder.append(") A WHERE ROWNUM<=").append(pageBean.getPageLast());
            builder.append(") WHERE R>").append(pageBean.getStart());
        }else if("mysql".equalsIgnoreCase(dialect)){
            builder.append(sql);
            builder.append(" LIMIT ").append(pageBean.getStart()).append(",").append(pageBean.getPageLast());
        }else{
            builder.append(sql);
        }
        log.debug("分页sql:{}",builder);
        return builder;
    }

    private void setPageParameters(String sql,PageBean pageBean,Connection connection,MappedStatement mappedStatement,BoundSql boundSql){
        String countSql = "SELECT COUNT(*) FROM ("+sql+")";
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            statement = connection.prepareStatement(countSql);
            BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), boundSql.getParameterObject());
            setParameters(statement, mappedStatement, countBS, boundSql.getParameterObject());
            rs = statement.executeQuery();
            int totalCount = 0;
            if(rs.next()){
                totalCount = rs.getInt(1);
            }
            pageBean.setTotal(totalCount);
        } catch (SQLException e) {
            log.error("PagePlugin error get total count:",e);
        }finally{
            try {
                rs.close();
                statement.close();
            } catch (SQLException e) {
                log.error("PagePlugin error close rs or stmt:",e);
            }
        }
    }

    private void setParameters(PreparedStatement statement,MappedStatement mappedStatement,BoundSql boundSql,Object parameterObject)throws SQLException{
        ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, boundSql);
        parameterHandler.setParameters(statement);
    }

}

Mybatis sqlmap中已Page结尾的会加分页，oracle的你看着改吧

我们这边只需要用实体类生成诸如 dao,service,action, sqlmap,struts配置等等，基本不需要写太多代码，而是否分页取决于你sql的id上是否带有Page后缀

Answer 2

我们公司写mybatis分页直接是在SQL语句中做的。如果靠mybatis分页，会增加很多代码。limit #{limit} offset #{offset}，基本上大部分数据库都支持这种分页写法。如果不想分页加个if判断。