程序博客网 > php图片上传预览功能

MyBatis分页

来源：互联网 发布：php图片上传预览功能 编辑：程序博客网 时间：2024/05/31 18:30

MyBatis分页       

        分类：            Java2011-07-04 18:246681人阅读评论(10)收藏举报

本方法是通过拦截mybatis底层的数据库操作达到分页目的

[java] view plaincopyprint?
  1. //分页实体类 
[java] view plaincopyprint?
  1. public class Page { 
  2.     private int showCount =10; //每页显示记录数 
  3.     private int totalPage;     //总页数 
  4.     private int totalResult;   //总记录数 
  5.     private int currentPage;   //当前页 
  6.     private int currentResult; //当前记录起始索引 
  7.     private boolean entityOrField; //true:需要分页的地方，传入的参数就是Page实体；false:需要分页的地方，传入的参数所代表的实体拥有Page属性 
  8.     private String pageStr;     //最终页面显示的底部翻页导航，详细见：getPageStr(); 
  9.     public int getTotalPage() { 
  10.         if(totalResult%showCount==0) 
  11.             totalPage = totalResult/showCount; 
  12.         else 
  13.             totalPage = totalResult/showCount+1; 
  14.         return totalPage; 
  15.     } 
  16.     public void setTotalPage(int totalPage) { 
  17.         this.totalPage = totalPage; 
  18.     } 
  19.     public int getTotalResult() { 
  20.         return totalResult; 
  21.     } 
  22.     public void setTotalResult(int totalResult) { 
  23.         this.totalResult = totalResult; 
  24.     } 
  25.     public int getCurrentPage() { 
  26.         if(currentPage<=0) 
  27.             currentPage = 1; 
  28.         if(currentPage>getTotalPage()) 
  29.             currentPage = getTotalPage(); 
  30.         return currentPage; 
  31.     } 
  32.     public void setCurrentPage(int currentPage) { 
  33.         this.currentPage = currentPage; 
  34.     } 
  35.     public String getPageStr() { 
  36.         StringBuffer sb = new StringBuffer(); 
  37.         if(totalResult>0){ 
  38.             sb.append(" <ul>\n"); 
  39.             if(currentPage==1){ 
  40.                 sb.append(" <li class=\"pageinfo\">首页</li>\n"); 
  41.                 sb.append(" <li class=\"pageinfo\">上页</li>\n"); 
  42.             }else{   
  43.                 sb.append(" <li><a href=\"#@\" onclick=\"nextPage(1)\">首页</a></li>\n"); 
  44.                 sb.append(" <li><a href=\"#@\" onclick=\"nextPage("+(currentPage-1)+")\">上页</a></li>\n"); 
  45.             } 
  46.             int showTag = 3;   //分页标签显示数量 
  47.             int startTag =1; 
  48.             if(currentPage>showTag){ 
  49.                 startTag = currentPage-1; 
  50.             } 
  51.             int endTag = startTag+showTag-1; 
  52.             for(int i=startTag; i<=totalPage && i<=endTag; i++){ 
  53.                 if(currentPage==i) 
  54.                     sb.append("<li class=\"current\">"+i+"</li>\n"); 
  55.                 else 
  56.                     sb.append(" <li><a href=\"#@\" onclick=\"nextPage("+i+")\">"+i+"</a></li>\n"); 
  57.             } 
  58.             if(currentPage==totalPage){ 
  59.                 sb.append(" <li class=\"pageinfo\">下页</li>\n"); 
  60.                 sb.append(" <li class=\"pageinfo\">尾页</li>\n"); 
  61.             }else{ 
  62.                 sb.append(" <li><a href=\"#@\" onclick=\"nextPage("+(currentPage+1)+")\">下页</a></li>\n"); 
  63.                 sb.append(" <li><a href=\"#@\" onclick=\"nextPage("+totalPage+")\">尾页</a></li>\n"); 
  64.             } 
  65.             sb.append(" <li class=\"pageinfo\">第"+currentPage+"页</li>\n"); 
  66.             sb.append(" <li class=\"pageinfo\">共"+totalPage+"页</li>\n"); 
  67.             sb.append("</ul>\n"); 
  68.             sb.append("<script type=\"text/javascript\">\n"); 
  69.             sb.append("function nextPage(page){"); 
  70.             sb.append(" if(true && document.forms[0]){\n"); 
  71.             sb.append("     var url = document.forms[0].getAttribute(\"action\");\n"); 
  72.             sb.append("     if(url.indexOf('?')>-1){url += \"&"+(entityOrField?"currentPage":"page.currentPage")+"=\";}\n"); 
  73.             sb.append("     else{url += \"?"+(entityOrField?"currentPage":"page.currentPage")+"=\";}\n"); 
  74.             sb.append("     document.forms[0].action = url+page;\n"); 
  75.             sb.append("     document.forms[0].submit();\n"); 
  76.             sb.append(" }else{\n"); 
  77.             sb.append("     var url = document.location+';\n"); 
  78.             sb.append("     if(url.indexOf('?')>-1){\n"); 
  79.             sb.append("         if(url.indexOf('currentPage')>-1){\n"); 
  80.             sb.append("             var reg = /currentPage=\\d*/g;\n"); 
  81.             sb.append("             url = url.replace(reg,'currentPage=');\n"); 
  82.             sb.append("         }else{\n"); 
  83.             sb.append("             url += \"&"+(entityOrField?"currentPage":"page.currentPage")+"=\";\n"); 
  84.             sb.append("         }\n"); 
  85.             sb.append("     }else{url += \"?"+(entityOrField?"currentPage":"page.currentPage")+"=\";}\n"); 
  86.             sb.append("     document.location = url + page;\n"); 
  87.             sb.append(" }\n"); 
  88.             sb.append("}\n"); 
  89.             sb.append("</script>\n"); 
  90.         } 
  91.         pageStr = sb.toString(); 
  92.         return pageStr; 
  93.     } 
  94.     public void setPageStr(String pageStr) { 
  95.         this.pageStr = pageStr; 
  96.     } 
  97.     public int getShowCount() { 
  98.         return showCount; 
  99.     } 
  100.     public void setShowCount(int showCount) { 
  101.         this.showCount = showCount; 
  102.     } 
  103.     public int getCurrentResult() { 
  104.         currentResult = (getCurrentPage()-1)*getShowCount(); 
  105.         if(currentResult<0) 
  106.             currentResult = 0; 
  107.         return currentResult; 
  108.     } 
  109.     public void setCurrentResult(int currentResult) { 
  110.         this.currentResult = currentResult; 
  111.     } 
  112.     public boolean isEntityOrField() { 
  113.         return entityOrField; 
  114.     } 
  115.     public void setEntityOrField(boolean entityOrField) { 
  116.         this.entityOrField = entityOrField; 
  117.     } 
  118.      


通过拦截StatementHandler的prepare方法的分页插件类

[java] view plaincopyprint?
  1. @Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class})}) 
  2. public class PagePluginimplements Interceptor { 
  3.  
  4.     private static String dialect =""; //数据库方言 
  5.     private static String pageSqlId =""; //mapper.xml中需要拦截的ID(正则匹配) 
  6.      
  7.     public Object intercept(Invocation ivk)throws Throwable { 
  8.         // TODO Auto-generated method stub 
  9.         if(ivk.getTarget()instanceof RoutingStatementHandler){ 
  10.             RoutingStatementHandler statementHandler = (RoutingStatementHandler)ivk.getTarget(); 
  11.             BaseStatementHandler delegate = (BaseStatementHandler) ReflectHelper.getValueByFieldName(statementHandler,"delegate"); 
  12.             MappedStatement mappedStatement = (MappedStatement) ReflectHelper.getValueByFieldName(delegate,"mappedStatement"); 
  13.              
  14.             if(mappedStatement.getId().matches(pageSqlId)){//拦截需要分页的SQL 
  15.                 BoundSql boundSql = delegate.getBoundSql(); 
  16.                 Object parameterObject = boundSql.getParameterObject();//分页SQL<select>中parameterType属性对应的实体参数，即Mapper接口中执行分页方法的参数,该参数不得为空 
  17.                 if(parameterObject==null){ 
  18.                     throw new NullPointerException("parameterObject尚未实例化！"); 
  19.                 }else{ 
  20.                     Connection connection = (Connection) ivk.getArgs()[0]; 
  21.                     String sql = boundSql.getSql(); 
  22.                     String countSql = "select count(0) from (" + sql+") as tmp_count"; //记录统计 
  23.                     PreparedStatement countStmt = connection.prepareStatement(countSql); 
  24.                     BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(),countSql,boundSql.getParameterMappings(),parameterObject); 
  25.                     setParameters(countStmt,mappedStatement,countBS,parameterObject); 
  26.                     ResultSet rs = countStmt.executeQuery(); 
  27.                     int count =0; 
  28.                     if (rs.next()) { 
  29.                         count = rs.getInt(1); 
  30.                     } 
  31.                     rs.close(); 
  32.                     countStmt.close(); 
  33.                     //System.out.println(count); 
  34.                     Page page = null; 
  35.                     if(parameterObjectinstanceof Page){    //参数就是Page实体 
  36.                          page = (Page) parameterObject; 
  37.                          page.setEntityOrField(true);   //见com.flf.entity.Page.entityOrField 注释 
  38.                         page.setTotalResult(count); 
  39.                     }else{ //参数为某个实体，该实体拥有Page属性 
  40.                         Field pageField = ReflectHelper.getFieldByFieldName(parameterObject,"page"); 
  41.                         if(pageField!=null){ 
  42.                             page = (Page) ReflectHelper.getValueByFieldName(parameterObject,"page"); 
  43.                             if(page==null) 
  44.                                 page = new Page(); 
  45.                             page.setEntityOrField(false);//见com.flf.entity.Page.entityOrField 注释 
  46.                             page.setTotalResult(count); 
  47.                             ReflectHelper.setValueByFieldName(parameterObject,"page", page);//通过反射，对实体对象设置分页对象 
  48.                         }else{ 
  49.                             thrownew NoSuchFieldException(parameterObject.getClass().getName()+"不存在 page 属性！"); 
  50.                         } 
  51.                     } 
  52.                     String pageSql = generatePageSql(sql,page); 
  53.                     ReflectHelper.setValueByFieldName(boundSql,"sql", pageSql); //将分页sql语句反射回BoundSql. 
  54.                 } 
  55.             } 
  56.         } 
  57.         return ivk.proceed(); 
  58.     } 
  59.  
  60.      
  61.     /**
  62.      * 对SQL参数(?)设值,参考org.apache.ibatis.executor.parameter.DefaultParameterHandler
  63.      * @param ps
  64.      * @param mappedStatement
  65.      * @param boundSql
  66.      * @param parameterObject
  67.      * @throws SQLException
  68.      */ 
  69.     private void setParameters(PreparedStatement ps,MappedStatement mappedStatement,BoundSql boundSql,Object parameterObject)throws SQLException { 
  70.         ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId()); 
  71.         List<ParameterMapping> parameterMappings = boundSql.getParameterMappings(); 
  72.         if (parameterMappings != null) { 
  73.             Configuration configuration = mappedStatement.getConfiguration(); 
  74.             TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry(); 
  75.             MetaObject metaObject = parameterObject == null ? null: configuration.newMetaObject(parameterObject); 
  76.             for (int i =0; i < parameterMappings.size(); i++) { 
  77.                 ParameterMapping parameterMapping = parameterMappings.get(i); 
  78.                 if (parameterMapping.getMode() != ParameterMode.OUT) { 
  79.                     Object value; 
  80.                     String propertyName = parameterMapping.getProperty(); 
  81.                     PropertyTokenizer prop = new PropertyTokenizer(propertyName); 
  82.                     if (parameterObject ==null) { 
  83.                         value = null; 
  84.                     } else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) { 
  85.                         value = parameterObject; 
  86.                     } else if (boundSql.hasAdditionalParameter(propertyName)) { 
  87.                         value = boundSql.getAdditionalParameter(propertyName); 
  88.                     } else if (propertyName.startsWith(ForEachSqlNode.ITEM_PREFIX)&& boundSql.hasAdditionalParameter(prop.getName())) { 
  89.                         value = boundSql.getAdditionalParameter(prop.getName()); 
  90.                         if (value !=null) { 
  91.                             value = configuration.newMetaObject(value).getValue(propertyName.substring(prop.getName().length())); 
  92.                         } 
  93.                     } else { 
  94.                         value = metaObject == null ?null : metaObject.getValue(propertyName); 
  95.                     } 
  96.                     TypeHandler typeHandler = parameterMapping.getTypeHandler(); 
  97.                     if (typeHandler ==null) { 
  98.                         throw new ExecutorException("There was no TypeHandler found for parameter "+ propertyName +" of statement "+ mappedStatement.getId()); 
  99.                     } 
  100.                     typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType()); 
  101.                 } 
  102.             } 
  103.         } 
  104.     } 
  105.      
  106.     /**
  107.      * 根据数据库方言，生成特定的分页sql
  108.      * @param sql
  109.      * @param page
  110.      * @return
  111.      */ 
  112.     private String generatePageSql(String sql,Page page){ 
  113.         if(page!=null && Tools.notEmpty(dialect)){ 
  114.             StringBuffer pageSql = new StringBuffer(); 
  115.             if("mysql".equals(dialect)){ 
  116.                 pageSql.append(sql); 
  117.                 pageSql.append(" limit "+page.getCurrentResult()+","+page.getShowCount()); 
  118.             }else if("oracle".equals(dialect)){ 
  119.                 pageSql.append("select * from (select tmp_tb.*,ROWNUM row_id from ("); 
  120.                 pageSql.append(sql); 
  121.                 pageSql.append(") as tmp_tb where ROWNUM<="); 
  122.                 pageSql.append(page.getCurrentResult()+page.getShowCount()); 
  123.                 pageSql.append(") where row_id>"); 
  124.                 pageSql.append(page.getCurrentResult()); 
  125.             } 
  126.             return pageSql.toString(); 
  127.         }else{ 
  128.             return sql; 
  129.         } 
  130.     } 
  131.      
  132.     public Object plugin(Object arg0) { 
  133.         // TODO Auto-generated method stub 
  134.         return Plugin.wrap(arg0, this); 
  135.     } 
  136.  
  137.     public void setProperties(Properties p) { 
  138.         dialect = p.getProperty("dialect"); 
  139.         if (Tools.isEmpty(dialect)) { 
  140.             try { 
  141.                 throw new PropertyException("dialect property is not found!"); 
  142.             } catch (PropertyException e) { 
  143.                 // TODO Auto-generated catch block 
  144.                 e.printStackTrace(); 
  145.             } 
  146.         } 
  147.         pageSqlId = p.getProperty("pageSqlId"); 
  148.         if (Tools.isEmpty(pageSqlId)) { 
  149.             try { 
  150.                 throw new PropertyException("pageSqlId property is not found!"); 
  151.             } catch (PropertyException e) { 
  152.                 // TODO Auto-generated catch block 
  153.                 e.printStackTrace(); 
  154.             } 
  155.         } 
  156.     } 
  157.      
  158. } 

该类中定义了两个私有静态变量：dialect、pageSqlId，这两个变量是在该类初始化时，即调用setProperties方法时初始化的，相关的值是在MyBatis配置文件中配置的，如下：

[html] view plaincopyprint?
  1. <plugins> 
  2.     <plugininterceptor="com.flf.plugin.PagePlugin"> 
  3.         <propertyname="dialect"value="mysql"/> 
  4.         <propertyname="pageSqlId"value=".*listPage.*"/> 
  5.     </plugin> 
  6. </plugins> 

上面的示例表明数据库方言为mysql，拦截所有mapper.xml映射文件中id包含有listPage的SQL。

反射帮助类：

[java] view plaincopyprint?
  1. /**
  2. * @author Administrator
  3. *  反射工具
  4. */ 
  5. public class ReflectHelper { 
  6.     /**
  7.      * 获取obj对象fieldName的Field
  8.      * @param obj
  9.      * @param fieldName
  10.      * @return
  11.      */ 
  12.     public static Field getFieldByFieldName(Object obj, String fieldName) { 
  13.         for (Class<?> superClass = obj.getClass(); superClass != Object.class; superClass = superClass 
  14.                 .getSuperclass()) { 
  15.             try { 
  16.                 return superClass.getDeclaredField(fieldName); 
  17.             } catch (NoSuchFieldException e) { 
  18.             } 
  19.         } 
  20.         return null; 
  21.     } 
  22.  
  23.     /**
  24.      * 获取obj对象fieldName的属性值
  25.      * @param obj
  26.      * @param fieldName
  27.      * @return
  28.      * @throws SecurityException
  29.      * @throws NoSuchFieldException
  30.      * @throws IllegalArgumentException
  31.      * @throws IllegalAccessException
  32.      */ 
  33.     public static Object getValueByFieldName(Object obj, String fieldName) 
  34.             throws SecurityException, NoSuchFieldException, 
  35.             IllegalArgumentException, IllegalAccessException { 
  36.         Field field = getFieldByFieldName(obj, fieldName); 
  37.         Object value = null; 
  38.         if(field!=null){ 
  39.             if (field.isAccessible()) { 
  40.                 value = field.get(obj); 
  41.             } else { 
  42.                 field.setAccessible(true); 
  43.                 value = field.get(obj); 
  44.                 field.setAccessible(false); 
  45.             } 
  46.         } 
  47.         return value; 
  48.     } 
  49.  
  50.     /**
  51.      * 设置obj对象fieldName的属性值
  52.      * @param obj
  53.      * @param fieldName
  54.      * @param value
  55.      * @throws SecurityException
  56.      * @throws NoSuchFieldException
  57.      * @throws IllegalArgumentException
  58.      * @throws IllegalAccessException
  59.      */ 
  60.     public staticvoid setValueByFieldName(Object obj, String fieldName, 
  61.             Object value) throws SecurityException, NoSuchFieldException, 
  62.             IllegalArgumentException, IllegalAccessException { 
  63.         Field field = obj.getClass().getDeclaredField(fieldName); 
  64.         if (field.isAccessible()) { 
  65.             field.set(obj, value); 
  66.         } else { 
  67.             field.setAccessible(true); 
  68.             field.set(obj, value); 
  69.             field.setAccessible(false); 
  70.         } 
  71.     } 
  72. } 

PS：分页插件类PagePlugin只实现了mysql和oracle的分页，至于其他的数据库，可以修改该类的generatePageSql方法。

具体参考例子见http://blog.csdn.net/fulinkster/article/details/6585157！

查看评论
8楼 bobsanjin2012-12-25 10:09发表[回复][引用][举报]
引用“baiqinghai2004”的评论：select count(0) from (&quot; + sql+ &quot;) as tmp...
我的也是这样求教，mapp.xml的sql如何写
7楼 mtt_lau2012-11-14 16:31发表[回复][引用][举报]
你那个权限系统只是数字的权限啊，郁闷呢
6楼 zhen12262012-03-28 13:47发表[回复][引用][举报]
如果spring整合mybatis
配置出错。。
<bean id="paginationInterceptor" class="common.mybatisIntercept.PagePlugin"></bean> 

<!-- 配置mybatis -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="plugins">
<ref bean="paginationInterceptor"/>
    </property>
<property name="configLocation" value="classpath:ibatis-config.xml"/>
</bean>
Re: fulinkster2012-05-08 12:01发表[回复][引用][举报]
回复zhen1226：这个是配置在mybatis里的插件，不是配置在spring里的bean
5楼 Worm3400650342012-03-15 11:20发表[回复][引用][举报]
终于找到一个可以参考的例子了…………
4楼 epusoft2012-03-05 11:10发表[回复][引用][举报]
Tools.java没有这个类，报错 那位朋友知道的话能不能加我qq 告知一下 谢谢  781288778
3楼 epusoft2012-03-05 11:07发表[回复][引用][举报]
Tools.java没有这个类，报错
2楼 baiqinghai20042011-11-11 18:29发表[回复][引用][举报]
select count(0) from (" + sql+ ") as tmp_count

提示命令未正确结束？你测试的时候不会有问题吗？
1楼 liaoxiaohua19812011-08-30 17:12发表[回复][引用][举报]
请教下：
我的代码在
return ivk.proceed(); 
时报错The SQL statement must not be null or empty

这是什么原因喃
Re: fulinkster2011-08-31 10:30发表[回复][引用][举报]
回复liaoxiaohua1981：有详细的异常信息吗？
php图片上传预览功能 php图片上传预览功能
原创粉丝点击
热门IT博客
node sass pythonnode sass python
淘宝橱窗位有多少个淘宝橱窗位有多少个
mac空格键失灵
c语言产生1到9的随机数
网络编程和多线程编程
java maven
电脑免费相册视频软件
人工智能失业潮
网络电视直播不清楚
淘宝内在小孩解道德经
飞机杯买什么牌子 知乎
查询表数据的sql语句
人工智能 医生
淘宝卖奶粉需要什么证
fastadmin cms
php 替换双引号
趣丸网络待遇怎么样
美工与平面设计的区别
腾讯jar软件下载
js做二级下拉菜单
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 鼎力分期 iphone分期 分期额度 和分期 既分期 即分期 dukes分期 库分期 轻松分期 有分期 来分期被踢出支付宝 信用卡分期还款 来分期欠1800逾期44天 2019分期购车计算器 分期购买手机需要什么条件 怎么分期手机 手机分期是什么意思 怎样分期买手机 分期买手机怎么买 实体店分期买手机需要什么 手机分期付款条件 手机分期付款是什么意思 怎么分期购买手机 分期手机利息怎么算 在手机店分期买手机需要什么 实体店怎么分期买手机 手机分期需要什么手续 实体店手机分期条件 分期手机需要什么条件 手机店怎么分期买手机 信用卡分期付款买手机 网上怎么分期付款买手机 分期付款手机商城 苏宁分期付款买手机 怎样分期付款买手机 实体店手机分期付款流程 手机分期付款需要什么条件 手机分期付款怎么还款 怎么分期付款买手机 分期付款买手机流程 手机分期付款要什么条件

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里