MyBatis 分页代码实现



/**

 * 数据库方言接口

 * @author Administrator

 *

 */

public interface Dialect {

 

    public static enum Type {

       MYSQL {

           public String getValue(){return "mysql";}

       },

       SQLSERVER {

           public String getValue() {return "sqlserver";}

       },

       ORACLE {

           public String getValue() {return "oracle";}

       };

       public abstract String getValue();

    }

   

    /**

     * 获取分页sql

     * @param sql 原始查询sql

     * @param offset 开始记录索引（从0开始计数）

     * @param limit 每页记录大小

     * @return 数据库相关的分页sql

     */

    public String getPaginationSql(String sql, int offset, int limit);

}

 

public class MySQL5Dialect implements Dialect {

    @Override

    public String getPaginationSql(String sql, int offset, int limit) {

       return sql + " limit " + offset + "," + limit;

    }

}

 

public class SqlServerDialect implements Dialect {

    @Override

    public String getPaginationSql(String sql, int pageNo, int pageSize) {

       return "select top " + pageSize + " from (" + sql

              + ") t where t.id not in (select top " + (pageNo-1)*pageSzie + " t1.id from ("

              + sql + ") t1)";

    }

}

 

public class OrcaleDialect implements Dialect {

 

    @Override

    public String getPaginationSql(String sql, int paheNo, int pageSize) {

       return "select * from (select rownum rn, t.* from (" + sql

              + ") t where rownum <= " + (pageNo* pageSize)

              + ") t1 where t1.rn > " + ((pageNo- 1) * pageSize);

    }

}

 

@Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class})})

public class PaginationInterceptor implements Interceptor {

 

    private final static Log log = LogFactory.getLog(PaginationInterceptor.class);

    @Override

    public Object intercept(Invocation invocation) throws Throwable {

       StatementHandler statmentHandler = (StatementHandler) invocation.getTarget();

       BoundSql boundSql = statmentHandler.getBoundSql();

       MetaObject metaStatementHandler = MetaObject.forObject(statmentHandler);

       RowBounds rowBounds = (RowBounds) metaStatementHandler.getValue("delegate.rowBounds");

       if(rowBounds == null || rowBounds == RowBounds.DEFAULT) {

           return invocation.proceed();

       }

       Configuration configuration = (Configuration) metaStatementHandler

                                                          .getValue("delegate.configuration");

       Dialect.Type databaseType = null;

       try{          

           databaseType = Dialect.Type.valueOf(configuration.getVariables()

                                      .getProperty("dialect").toUpperCase());      

       } catch(Exception e){        

           throw new ConfigurationException(

                  "the value of the dialect property in mybatis-config.xml is not defined : "

                  + configuration.getVariables().getProperty("dialect")); 

       }      

      

       Dialect dialect =null;

       switch(databaseType){         

           case MYSQL: dialect =new MySQL5Dialect();

           case SQLSERVER : dialect = new SqlServerDialect();

           case ORACLE : dialect = new OrcaleDialect();

       }

      

       String originalSql = (String)metaStatementHandler.getValue("delegate.boundSql.sql");     

       metaStatementHandler.setValue("delegate.boundSql.sql", dialect.getPaginationSql(originalSql,

                                       rowBounds.getOffset(), rowBounds.getLimit()) );      

       metaStatementHandler.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET );      

       metaStatementHandler.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT );      

       if(log.isDebugEnabled()){          

           log.debug("生成分页SQL : "+ boundSql.getSql());      

       }

       return invocation.proceed();

    }

 

    @Override

    public Object plugin(Object target) {

       return Plugin.wrap(target, this);

    }

 

    @Override

    public void setProperties(Properties properties) {

       // TODO Auto-generated method stub

    }

}

 

public class PaginationHelper {

 

    public static int getRowCount(SqlSession sqlSession, String statementName, Object values) {

       Map parameterMap = toParameterMap(values);

       int count = 0;

       try {

           MappedStatement mst = sqlSession.getConfiguration().getMappedStatement(statementName);

           BoundSql boundSql = mst.getBoundSql(parameterMap);

           String sql = " select count(1) row_count from (" + boundSql.getSql() + ") ";

           PreparedStatement pstmt = sqlSession.getConnection().prepareStatement(sql);

           setParameters(pstmt, mst, boundSql, parameterMap);

           ResultSet rs = pstmt.executeQuery();

           if (rs.next()) {

              count = rs.getInt("row_count");

           }

           rs.close();

           pstmt.close();

       } catch (Exception e) {

           count = 0;

           throw new RuntimeException(e);

       }

       return count;

    }

 

    private static void setParameters(PreparedStatement ps, MappedStatement mappedStatement,

                    BoundSql boundSql, Object parameterObject) throws SQLException {

       ErrorContext.instance().activity("setting parameters")

                              .object(mappedStatement.getParameterMap().getId());

       List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();

       if (parameterMappings != null) {

           Configuration configuration = mappedStatement.getConfiguration();

           TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();

           MetaObject metaObject = parameterObject == null ? null : configuration

                                                     .newMetaObject(parameterObject);

           for (int i = 0; i < parameterMappings.size(); i++) {

              ParameterMapping parameterMapping = parameterMappings.get(i);

              if (parameterMapping.getMode() != ParameterMode.OUT) {

                  Object value;

                  String propertyName = parameterMapping.getProperty();

                  PropertyTokenizer prop = new PropertyTokenizer(propertyName);

                  if (parameterObject == null) {

                     value = null;

                  } else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {

                     value = parameterObject;

                  } else if (boundSql.hasAdditionalParameter(propertyName)) {

                     value = boundSql.getAdditionalParameter(propertyName);

                  } else if (propertyName.startsWith(ForEachSqlNode.ITEM_PREFIX)

                                           && boundSql.hasAdditionalParameter(prop.getName())) {

                     value = boundSql.getAdditionalParameter(prop.getName());

                     if (value != null) {

                         value = configuration.newMetaObject(value)

                                 .getValue(propertyName.substring(prop.getName().length()));

                     }

                  } else {

                     value = metaObject == null ? null : metaObject.getValue(propertyName);

                  }

                  TypeHandler typeHandler = parameterMapping.getTypeHandler();

                  if (typeHandler == null) {

                     throw new ExecutorException("There was no TypeHandler found for parameter "

                                   + propertyName + " of statement " + mappedStatement.getId());

                  }

                  typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType());

              }

           }

       }

    }

 

    protected static Map toParameterMap(Object parameter) {

       if (parameter == null) {

           return new HashMap();

       }

       if (parameter instanceof Map) {

           return (Map<?, ?>) parameter;

       } else {

           try {

              return PropertyUtils.describe(parameter);

           } catch (Exception e) {

              e.printStackTrace();

              return null;

           }

       }

    }

}
 
 

public class Pager implements Serializable{

 

    private static final long serialVersionUID = 1566826618769972857L;

    //默认的每页记录数

    public static final int DEFAULT_PAGE_SIZE = 25;

    // 每页的记录数

    private int pageSize = DEFAULT_PAGE_SIZE;

    // 当前页

    private int pageNo = 1;

    // 总行数

    private int rowCount;

    // 总页数

    private int pageCount;

    // 每页的记录

    private List resultList;

   

    public Pager() {

    }

   

    public Pager(int pageNo) {

       this.pageNo = pageNo;

    }

 

    public Pager(int pageSize, int pageNo) {

       this.pageSize = pageSize;

       this.pageNo = pageNo;

    }

 

    public int getPageSize() {

       return pageSize;

    }

 

    public void setPageSize(int pageSize) {

       this.pageSize = pageSize;

    }

 

    public int getPageNo() {

       return pageNo;

    }

 

    public void setPageNo(int pageNo) {

       this.pageNo = pageNo;

    }

 

    public int getRowCount() {

       return rowCount;

    }

 

    public void setRowCount(int rowCount) {

       this.rowCount = rowCount;

       if(rowCount % pageSize == 0) {

           this.pageCount = rowCount / pageSize;

       } else {

           this.pageCount = rowCount / pageSize + 1;

       }

    }

   

    public int getPageCount() {

       return pageCount;

    }

   

    public void setPageCount(int pageCount) {

       this.pageCount = pageCount;

    }

 

    public List getResultList() {

       return resultList;

    }

 

    public void setResultList(List resultList) {

       this.resultList = resultList;

    }

}

 

public class SqlSessionWrapper {

 

    private SqlSession sqlSession;

   

    public SqlSessionWrapper(SqlSession sqlSession) {

       this.sqlSession = sqlSession;

    }

     ...   

    public Pager selectPagination(String s, Object param, Pager pager) {

       if(pager == null) {

           pager = new Pager();

       }

       List resultList = sqlSession.selectList(s, param,

                                   new RowBounds(pager.getPageNo(), pager.getPageSize()));

       int rowCount = PaginationHelper.getRowCount(sqlSession, s, param);

       pager.setResultList(resultList);

       pager.setRowCount(rowCount);

       if(resultList == null || resultList.size() == 0) {

           pager.setPageCount(0);

           pager.setPageNo(0);

       }

       return pager;

    }

   

    public Pager selectPagination(String s, Pager pager) {

       return selectPagination(s, null, pager);

    }

    ...

 }