springmvc mybatis 分页插件

结合Spring的配置

Xml代码 
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> 
    <property name="dataSource" ref="dataSource"/> 
    <property name="typeAliasesPackage" value="xx.xx.xx.entity" /> 
    <property name="plugins"> 
       <list> 
          <!-- 配置自己实现的分页插件 --> 
          <bean class="xx.xx.xx.mybatis.PagingPlugin"> 
            <property name="dialect" value="mysql"/> 
          </bean> 
        </list> 
    </property> 
</bean> 
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> 
    <property name="basePackage" value="xx.xx.xx.dao" /> 
</bean> 
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSource"/>
    <property name="typeAliasesPackage" value="xx.xx.xx.entity" />
    <property name="plugins">
       <list>
          <!-- 配置自己实现的分页插件 -->
          <bean class="xx.xx.xx.mybatis.PagingPlugin">
            <property name="dialect" value="mysql"/>
          </bean>
        </list>
    </property>
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="basePackage" value="xx.xx.xx.dao" />
</bean>

●传递多个参数

Java代码 
//使用@Param来注解，例如：  
List queryXX(@Param("arg1") int arg1, @Param("arg2") String arg2); 
//使用@Param来注解，例如：
List queryXX(@Param("arg1") int arg1, @Param("arg2") String arg2);

●分页插件。 有以下缺点：

暂时只实现mysql和oracle
oracle未测试
未考虑取总数的性能
未考虑排序
查找的结果集未能自动放到分页对象(Page)中

Page.java

Java代码 
public class Page{  
    private int limit = 20; //每页显示条数  
    private int start = 0;  //起始行号  
    private long total = -1; //总数  
    private List result = new ArrayList(); //结果集  
    //---- 省略get set ----//  
} 
public class Page{
 private int limit = 20; //每页显示条数
 private int start = 0;  //起始行号
 private long total = -1; //总数
 private List result = new ArrayList(); //结果集
 //---- 省略get set ----//
}
PagingPlugin.java

Java代码 
/** 
 * Mybatis的分页查询插件，通过拦截StatementHandler的prepare方法来实现。 
 * 只有在参数列表中包括Page类型的参数时才进行分页查询。 
 * 在多参数的情况下，只对第一个Page类型的参数生效。 
 * 另外，在参数列表中，Page类型的参数无需用@Param来标注 
 * @author linzongxue 2012-1-16（修改） 
 * 
 */ 
@Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class})})   
public class PagingPlugin implements Interceptor {  
    private String dialect;  
 
    @SuppressWarnings("unchecked")  
    @Override 
    public Object intercept(Invocation invocation) throws Throwable {  
        if(!(invocation.getTarget() instanceof RoutingStatementHandler))   
            return invocation.proceed();  
          
        RoutingStatementHandler statementHandler = (RoutingStatementHandler)invocation.getTarget();  
        BoundSql boundSql = statementHandler.getBoundSql();  
        //分析是否含有分页参数，如果没有则不是分页查询  
        //注意：在多参数的情况下，只处理第一个分页参数  
        Page page = null;  
        Object paramObj = boundSql.getParameterObject();  
        if (paramObj instanceof Page){ //只有一个参数的情况  
            page = (Page)paramObj;  
        }  
        else if (paramObj instanceof Map){ //多参数的情况，找到第一个Page的参数  
            for (Map.Entry<String, Object> e : ((Map<String, Object>)paramObj).entrySet()){  
                if (e.getValue() instanceof Page){  
                    page = (Page)e.getValue();  
                    break;  
                }  
            }  
        }  
          
        if (page == null) return invocation.proceed();  
          
        //查找总记录数，并设置Page的相关参数  
        long total = this.getTotal(invocation);  
        page.setTotal(total);  
        //生成分页SQL  
        String pageSql = generatePageSql(boundSql.getSql(), page);  
        //强制修改最终要执行的SQL  
        setFieldValue(boundSql, "sql", pageSql);  
        return invocation.proceed();  
    }  
      
    /** 
     * 获取记录总数 
     */ 
    @SuppressWarnings("unchecked")  
    private long getTotal(Invocation invocation) throws Exception{  
        RoutingStatementHandler statementHandler = (RoutingStatementHandler)invocation.getTarget();  
        BoundSql boundSql = statementHandler.getBoundSql();  
        /* 
         * 为了设置查找总数SQL的参数，必须借助MappedStatement、Configuration等这些类， 
         * 但statementHandler并没有开放相应的API，所以只好用反射来强行获取。 
         */ 
        BaseStatementHandler delegate = (BaseStatementHandler)getFieldValue(statementHandler, "delegate");  
        MappedStatement mappedStatement = (MappedStatement)getFieldValue(delegate, "mappedStatement");  
        Configuration configuration = mappedStatement.getConfiguration();  
        TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();  
        Object param = boundSql.getParameterObject();  
        MetaObject metaObject = configuration.newMetaObject(param);  
          
        long total = 0;  
        String sql = boundSql.getSql();  
        String countSql = "select count(1) from (" + sql+ ") as t"; //记录统计  (mysql要求必须添加 最后的as t)  
        try{  
            Connection conn = (Connection)invocation.getArgs()[0];  
            PreparedStatement ps = conn.prepareStatement(countSql);  
            int i = 1;  
            for (ParameterMapping pm : boundSql.getParameterMappings()) {  
                Object value = null;  
                String propertyName = pm.getProperty();  
                PropertyTokenizer prop = new PropertyTokenizer(propertyName);  
                if (typeHandlerRegistry.hasTypeHandler(param.getClass())) {  
                    value = param;    
                }  
                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.getValue(propertyName);  
                }  
 
                pm.getTypeHandler().setParameter(ps, i++, value, pm.getJdbcType());  
            }  
            ResultSet rs = ps.executeQuery();  
            rs.next();  
            total = rs.getLong(1);  
            rs.close();    
            ps.close();  
        }  
        catch (Exception e){  
            throw new RuntimeException("分页查询无法获取总记录数", e);  
        }  
        return total;  
    }  
      
    /** 
     * 生成分页SQL 
     */ 
    private String generatePageSql(String sql, Page page){  
        StringBuilder pageSql = new StringBuilder();  
        if("mysql".equals(dialect)){  
            pageSql.append(sql);  
            pageSql.append(" limit ").append(page.getStart()).append(",").append(page.getLimit());    
        }  
        else if("oracle".equals(dialect)){  
            pageSql.append("select * from (select t.*, ROWNUM num from (")  
                .append(sql).append(") as t where ROWNUM <= ")  
                .append(page.getStart() + page.getLimit())  
                .append(") where num > ").append(page.getStart());  
        }  
        else{  
            throw new RuntimeException("分页插件还不支持数据库类型:" + dialect);  
        }  
        return pageSql.toString();  
    }  
      
    /** 
     * 用反射取对象的属性值 
     */ 
    private Object getFieldValue(Object obj, String fieldName) throws Exception{          
        for (Class<?> superClass = obj.getClass(); superClass != Object.class; superClass = superClass.getSuperclass()) {  
            try{  
                Field field = superClass.getDeclaredField(fieldName);  
                field.setAccessible(true);  
                return field.get(obj);  
            }  
            catch(Exception e){}  
        }  
        return null;  
    }  
 
    /** 
     * 用反射设置对象的属性值 
     */ 
    private void setFieldValue(Object obj, String fieldName, Object fieldValue) throws Exception{  
        Field field = obj.getClass().getDeclaredField(fieldName);  
        field.setAccessible(true);  
        field.set(obj, fieldValue);  
    }  
      
    @Override 
    public Object plugin(Object target) {  
        return Plugin.wrap(target, this);  
    }  
      
    @Override 
    public void setProperties(Properties props) {  
          
    }  
      
    public void setDialect(String dialect){  
        this.dialect = dialect.toLowerCase();  
    }  
      
    public String getDialect(){  
        return this.dialect;  
    }  
} 

 

<tr> 
          <td><%=empno%></td> 
          <td><%=ename%></td> 
          <td><%=job%></td> 
          <td><%=hiredate%></td> 
          <td><%=sal%></td> 
          <td><%=comm%></td> 
      </tr> 
          <% 
      }while(rs.next()); 
      con.close(); 
  } 
  catch(Exception e){ 
       
  } 
 
table> 
 href = "multipage.jsp?curPage=1" >首页</a> 
 href = "multipage.jsp?curPage=<%=curPage-1%>" >上一页</a> 
 href = "multipage.jsp?curPage=<%=curPage+1%>" >下一页</a> 
 href = "multipage.jsp?curPage=<%=pageCount%>" >尾页</a> 
<%=curPage%>页/共<%=pageCount%>页