IBatis.Net使用总结（三）-- IBatis实现分页返回数据和总数

IBatis 分页,这里没有使用其他插件，只使用最原始的方法。

输入参数：

int　　 currentPage　　当前页

int　　 pageSize　　每页大小

Hashtable 　　findCondition　　查询条件

out　　int 　　total　　返回总数

输出：

DataTable 或者　　IList<T>

使用了三种分页方式，根据实际情况使用。

我在实际应用中，

第一种返回DataTable，在使用过程中，需要注意它所映射的实体对象名称字段。

第二种方法返回泛型集合，使用的比较顺手，也是习惯使用的方法。

第三种方法也是返回泛型集合。但是，它使用的两个参数，偏移量和页面大小，我平常用的概率小点。

1：在一个statements中，使用了两条语句，一个是返回所需的列，一个是返回总数。。

  1 <select id="Article_FindPageByCond" parameterClass="HashTable" resultClass="System.Data.DataSet" >
  2       <![CDATA[select           
  3                 T.[PK_Article]
  4                 ,T.[ArticleTitle]
  5                 ,T.[ArticleAuthor]
  6                 ,T.[ArticleSummary]
  7                 ,T.[ArticleContent]
  8                 ,T.[Sort]
  9                 ,T.[EditTime]
 10                 ,T.[Dr]
 11                 ,T.[Ts]
 12           from
 13           ( select A.*, ROW_NUMBER() OVER ( ORDER BY 
 14                  (A.[PK_Article] )
 15                   ) rn
 16               from
 17               (select *  from [dbo].[Article]]]>
 18                   <dynamic prepend="WHERE">
 19                     <isNotEmpty prepend="and" property="PKArticle">
 20                         PKArticle LIKE '%'+#PKArticle#+'%'
 21                     </isNotEmpty>
 22                     <isNotEmpty prepend="and" property="ArticleTitle">
 23                         ArticleTitle LIKE '%'+#ArticleTitle#+'%'
 24                     </isNotEmpty>
 25                     <isNotEmpty prepend="and" property="ArticleAuthor">
 26                         ArticleAuthor LIKE '%'+#ArticleAuthor#+'%'
 27                     </isNotEmpty>
 28                     <isNotEmpty prepend="and" property="ArticleSummary">
 29                         ArticleSummary LIKE '%'+#ArticleSummary#+'%'
 30                     </isNotEmpty>
 31                     <isNotEmpty prepend="and" property="ArticleContent">
 32                         ArticleContent LIKE '%'+#ArticleContent#+'%'
 33                     </isNotEmpty>
 34                     <isNotNull property="Sort">
 35                         <isNotEmpty property="Sort">
 36                             <isNotEqual prepend="and" property="Sort" compareValue="0">
 37                                 Sort LIKE '%'+#Sort#+'%'
 38                             </isNotEqual>
 39                         </isNotEmpty>
 40                     </isNotNull>
 41                     <isNotEmpty prepend="and" property="EditTime">
 42                         EditTime LIKE '%'+#EditTime#+'%'
 43                     </isNotEmpty>
 44                     <isNotEmpty prepend="and" property="Dr">
 45                         Dr LIKE '%'+#Dr#+'%'
 46                     </isNotEmpty>
 47                     <isNotEmpty prepend="and" property="Ts">
 48                         Ts LIKE '%'+#Ts#+'%'
 49                     </isNotEmpty>
 50                 </dynamic>
 51               ) A
 52           ) T
 53           where 1=1 and
 54           <![CDATA[  rn <= #currentPage# * #pageSize# ]]>
 55           and  <![CDATA[ rn >(#currentPage# - 1) * #pageSize# ]]>
 56           
 57           <![CDATA[      
 58            select count(*) as total
 59           from
 60           ( select A.*, ROW_NUMBER() OVER ( ORDER BY 
 61                  (A.[PK_Article] )
 62                   ) rn
 63               from
 64               (select *  from [dbo].[Article]]]>
 65                   <dynamic prepend="WHERE">
 66                     <isNotEmpty prepend="and" property="PKArticle">
 67                         PKArticle LIKE '%'+#PKArticle#+'%'
 68                     </isNotEmpty>
 69                     <isNotEmpty prepend="and" property="ArticleTitle">
 70                         ArticleTitle LIKE '%'+#ArticleTitle#+'%'
 71                     </isNotEmpty>
 72                     <isNotEmpty prepend="and" property="ArticleAuthor">
 73                         ArticleAuthor LIKE '%'+#ArticleAuthor#+'%'
 74                     </isNotEmpty>
 75                     <isNotEmpty prepend="and" property="ArticleSummary">
 76                         ArticleSummary LIKE '%'+#ArticleSummary#+'%'
 77                     </isNotEmpty>
 78                     <isNotEmpty prepend="and" property="ArticleContent">
 79                         ArticleContent LIKE '%'+#ArticleContent#+'%'
 80                     </isNotEmpty>
 81                     <isNotNull property="Sort">
 82                         <isNotEmpty property="Sort">
 83                             <isNotEqual prepend="and" property="Sort" compareValue="0">
 84                                 Sort LIKE '%'+#Sort#+'%'
 85                             </isNotEqual>
 86                         </isNotEmpty>
 87                     </isNotNull>
 88                     <isNotEmpty prepend="and" property="EditTime">
 89                         EditTime LIKE '%'+#EditTime#+'%'
 90                     </isNotEmpty>
 91                     <isNotEmpty prepend="and" property="Dr">
 92                         Dr LIKE '%'+#Dr#+'%'
 93                     </isNotEmpty>
 94                     <isNotEmpty prepend="and" property="Ts">
 95                         Ts LIKE '%'+#Ts#+'%'
 96                     </isNotEmpty>
 97                 </dynamic>
 98               ) A
 99           ) T         
100     </select>

返回所需列，返回总数

这种方式，调用前面所说的返回DataTable的方法，完成分页

 1        /// <summary>
 2         /// 按条件获取分页数据，返回DataTable对象
 3         /// </summary>
 4         /// <param name="currentPage"></param>
 5         /// <param name="pageSize"></param>
 6         /// <param name="findCondtion"></param>
 7         /// <param name="total"></param>
 8         /// <returns></returns>
 9         public DataTable FindPageByCondition(int currentPage, int pageSize, Hashtable findCondition, out int total)
10         {
11             String stmtId = "Article_FindPageByCond";
12             total = 0;
13             findCondition.Add("currentPage", currentPage);
14             findCondition.Add("pageSize", pageSize);
15             BaseDao bd = new BaseDao();
16             DataSet ds = bd.QueryForDataSet(stmtId, findCondition);
17             DataTable dt = ds.Tables[0];
18             total = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString());
19             return dt;
20         }
21

使用BaseDao.cs返回分页之后的DataTable

2：如果想要返回泛型集合IList<T>，则使用两个statements。一个select返回实体映射，一个select返回总数。

 1     <select id="Article_GetPageByCond" parameterClass="HashTable" resultMap="FullResultMap" >
 2       <![CDATA[select           
 3                 T.[PK_Article]
 4                 ,T.[ArticleTitle]
 5                 ,T.[ArticleAuthor]
 6                 ,T.[ArticleSummary]
 7                 ,T.[ArticleContent]
 8                 ,T.[Sort]
 9                 ,T.[EditTime]
10                 ,T.[Dr]
11                 ,T.[Ts]
12           from
13           ( select A.*, ROW_NUMBER() OVER ( ORDER BY 
14                  (A.[PK_Article] )
15                   ) rn
16           from
17           (select *  from [dbo].[Article]]]>
18               <dynamic prepend="WHERE">
19                 <isNotEmpty prepend="and" property="PKArticle">
20                     PKArticle LIKE '%'+#PKArticle#+'%'
21                 </isNotEmpty>
22                 <isNotEmpty prepend="and" property="ArticleTitle">
23                     ArticleTitle LIKE '%'+#ArticleTitle#+'%'
24                 </isNotEmpty>
25                 <isNotEmpty prepend="and" property="ArticleAuthor">
26                     ArticleAuthor LIKE '%'+#ArticleAuthor#+'%'
27                 </isNotEmpty>
28                 <isNotEmpty prepend="and" property="ArticleSummary">
29                     ArticleSummary LIKE '%'+#ArticleSummary#+'%'
30                 </isNotEmpty>
31                 <isNotEmpty prepend="and" property="ArticleContent">
32                     ArticleContent LIKE '%'+#ArticleContent#+'%'
33                 </isNotEmpty>
34                 <isNotNull property="Sort">
35                     <isNotEmpty property="Sort">
36                         <isNotEqual prepend="and" property="Sort" compareValue="0">
37                             Sort LIKE '%'+#Sort#+'%'
38                         </isNotEqual>
39                     </isNotEmpty>
40                 </isNotNull>
41                 <isNotEmpty prepend="and" property="EditTime">
42                     EditTime LIKE '%'+#EditTime#+'%'
43                 </isNotEmpty>
44                 <isNotEmpty prepend="and" property="Dr">
45                     Dr LIKE '%'+#Dr#+'%'
46                 </isNotEmpty>
47                 <isNotEmpty prepend="and" property="Ts">
48                     Ts LIKE '%'+#Ts#+'%'
49                 </isNotEmpty>
50             </dynamic>
51           ) A
52           ) T
53           where 1=1 and
54           <![CDATA[  rn <= #currentPage# * #pageSize# ]]>
55           and  <![CDATA[ rn >(#currentPage# - 1) * #pageSize# ]]>
56     </select>

返回映射FullResultMap

 1 <select id="Article_GetCountByCond" resultClass="System.Int32">
 2         <![CDATA[      
 3        SELECT count(*) as total
 4       FROM
 5       ( select A.*, ROW_NUMBER() OVER ( ORDER BY 
 6              (A.[PK_Article] )
 7               ) rn
 8       from
 9       (SELECT *  FROM [dbo].[Article]]]>
10       <dynamic prepend="WHERE">
11         <isNotEmpty prepend="and" property="PKArticle">
12             PKArticle LIKE '%'+#PKArticle#+'%'
13         </isNotEmpty>
14         <isNotEmpty prepend="and" property="ArticleTitle">
15             ArticleTitle LIKE '%'+#ArticleTitle#+'%'
16         </isNotEmpty>
17         <isNotEmpty prepend="and" property="ArticleAuthor">
18             ArticleAuthor LIKE '%'+#ArticleAuthor#+'%'
19         </isNotEmpty>
20         <isNotEmpty prepend="and" property="ArticleSummary">
21             ArticleSummary LIKE '%'+#ArticleSummary#+'%'
22         </isNotEmpty>
23         <isNotEmpty prepend="and" property="ArticleContent">
24             ArticleContent LIKE '%'+#ArticleContent#+'%'
25         </isNotEmpty>
26         <isNotNull property="Sort">
27             <isNotEmpty property="Sort">
28                 <isNotEqual prepend="and" property="Sort" compareValue="0">
29                     Sort LIKE '%'+#Sort#+'%'
30                 </isNotEqual>
31             </isNotEmpty>
32         </isNotNull>
33         <isNotEmpty prepend="and" property="EditTime">
34             EditTime LIKE '%'+#EditTime#+'%'
35         </isNotEmpty>
36         <isNotEmpty prepend="and" property="Dr">
37             Dr LIKE '%'+#Dr#+'%'
38         </isNotEmpty>
39         <isNotEmpty prepend="and" property="Ts">
40             Ts LIKE '%'+#Ts#+'%'
41         </isNotEmpty>
42     </dynamic>
43       ) A
44       ) T         
45     </select>

返回总数

使用IList<T> QueryForList<T>(string statementName, object parameterObject);

 1         /// <summary>
 2         /// 按条件获取分页数据,返回IList对象
 3         /// </summary>
 4         /// <param name="currentPage"></param>
 5         /// <param name="pageSize"></param>
 6         /// <param name="findCondtion"></param>
 7         /// <param name="total"></param>
 8         /// <returns></returns>
 9         public IList<Article> GetPageByCondition(int currentPage, int pageSize, Hashtable findCondition)
10         {
11             IList<Article> list=new List<Article>();
12             String stmtId = "Article_GetPageByCond";
13             findCondition.Add("currentPage", currentPage);
14             findCondition.Add("pageSize", pageSize);
15             IList<Article> result = SqlMap.QueryForList<Article>(stmtId,findCondition);
16             return result;           
17         }

分页返回IList对象

3：使用ibatis.net本身自带的分页功能。

int　skipResults　　偏移量

int maxResults　　每页大小(偏移量之后的页面大小）

statements还是使用了第2种方法，两个statements

IList<T> QueryForList<T>(string statementName, object parameterObject, int skipResults, int maxResults);
IList QueryForList(string statementName, object parameterObject, int skipResults, int maxResults);

1 public IList GetPage(int skipResults, int maxResults, Hashtable findCondition)
2         {
3             String stmtId = "Article_GetPageByCond";
4             IList result = SqlMap.QueryForList(stmtId, findCondition, skipResults, maxResults);
5             return result;
6         }

使用自带的分页功能

posted @ 2015-11-05 11:17 weiqinl 阅读(450) 评论(0) 编辑收藏举报

刷新页面返回顶部

web知识-平时工作生活中的总结

有志于web前端开发 https://github.com/weiqinl https://weiqinl.com

IBatis.Net使用总结（三）-- IBatis实现分页返回数据和总数

公告