springmvc mybatis 分页插件 -

longge308

浏览: 37523 次

最近访客更多访客>>

浩克山东小码农

noverlist

jiwenfengtiger

ayrtonsenna

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

springmvc mybatis 分页插件

博客分类：

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%>页