java 分页高级条件搜索_多条件搜索分页的实现

1 ///

2 ///默认下获取分页数据3 ///

4 /// 当前页码

5 /// 每一页的行数

6 /// BookID,BookName,Author,UserName,AcaName,Category,[State],AddDate,LastUpdateTime

7 public DataTable GetPagedModel(int pageIndex, intpageSize)8 {9 string strOrder = "order by AddDate DESC";10 string strSQL = string.Format(@"select * from11 (select BookID,BookName,Author,UserName,AcaName,Category,[State],AddDate,LastUpdateTime,ROW_NUMBER() over({0}) as num from ViewBookInfo where IsDel=0) as tb12 where num between {1}*({2}-1)+1 and {1}*{2} {0}", strOrder, pageSize, pageIndex);13

14 returnSQLHelper.GetDataTable(strSQL);15 }16

17 ///

18 ///含参数的查询19 ///

20 ///

21 ///

22 /// BookName、ClassName、DeptID、DeptPID、Category、[State]

23 /// BookID,BookName,Author,UserName,AcaName,Category,[State],AddDate,LastUpdateTime

24 public DataTable GetPagedModel(int pageIndex, intpageSize, Hashtable ht)25 {26 stringstrCondition;27 ListparasList;28 string sOrder="order by AddDate DESC";29

30 //获取参数

31 GetSearchCondition(ht,out strCondition,outparasList);32

33 string strSQL = string.Format(@"select * from (select BookID,BookName,Author,UserName,AcaName,Category,[State],AddDate,LastUpdateTime,ROW_NUMBER() over({0}) as num from ViewBookInfo where IsDel=0 {3}) as tb34 where num between {1}*({2}-1)+1 and {1}*{2} {0}", sOrder , pageSize, pageIndex,strCondition);35

36 returnSQLHelper.GetDataTable(strSQL,parasList.ToArray());37

38 }39

40 ///

41 ///获取详细信息42 ///

43 ///

44 ///

45 public DataTable GetDetail(intBookID)46 {47 string strSQL = "select ClassTarget,BookFeature from BookInfo where BookID="+BookID;48

49 returnSQLHelper.GetDataTable(strSQL);50 }51 ///

52 ///含参数记录的条数53 ///

54 /// BookName、ClassName、DeptID、DeptPID、Category、[State]

55 ///

56 public intGetRecordCount(Hashtable ht)57 {58 stringstrCondition;59 ListparasList;60

61 //获取参数

62 GetSearchCondition(ht, out strCondition, outparasList);63

64 string strSQL = string.Format("select count(0) from ViewBookInfo where IsDel=0 {0}", strCondition);65

66 returnConvert.ToInt32(SQLHelper.ExecuteScalar(strSQL, parasList.ToArray()));67 }68

69 ///

70 ///装配查询参数,获取查询要的字符串，和参数集合71 ///

72 /// BookName、ClassName、DeptID、DeptPID、Category、[State]

73 ///

74 ///

75 private void GetSearchCondition(Hashtable ht, out string contString, out Listparas)76 {77 StringBuilder sOrder = newStringBuilder();78 paras=new List();79

80 if (ht.Count == 0)81 {82 contString = "";83 return;84 }85

86 object oBookName = ht["BookName"];87 object oClassName = ht["ClassName"];88 object oDeptID = ht["DeptID"];89 object oDeptPID = ht["DeptPID"];90 object oCategory = ht["Category"];91 object oState = ht["State"];92

93 //书名

94 if (oBookName != null && !string.IsNullOrEmpty(oBookName.ToString()))95 {96 sOrder.Append("and BookName like @BookName or BookPY like @BookName");97 paras.Add(new SqlParameter("@BookName",oBookName.ToString()+"%"));98 }99 //课程名

100 if(oClassName!=null&&!string.IsNullOrEmpty(oClassName.ToString()))101 {102 sOrder.Append("and ClassName like @ClassName");103 paras.Add(new SqlParameter("@ClassName",oClassName.ToString()+"%"));104 }105 //部门ID

106 if (oDeptID != null && !string.IsNullOrEmpty(oDeptID.ToString()))107 {108 sOrder.Append("and DeptID=@DeptID");109 paras.Add(new SqlParameter("@DeptID",Convert.ToInt32(oDeptID)));110 }111

112 //上级部门PID

113 if (oDeptPID != null && !string.IsNullOrEmpty(oDeptPID.ToString()))114 {115 sOrder.Append("and DeptPID=@DeptPID");116 paras.Add(new SqlParameter("@DeptPID",Convert.ToInt32(oDeptPID)));117 }118

119 //种类Category

120 if (oCategory != null && !string.IsNullOrEmpty(oCategory.ToString()))121 {122 sOrder.Append("and Category=@Category");123 paras.Add(new SqlParameter("@Category",oCategory.ToString()));124 }125

126 //状态State

127 if (oState != null && !string.IsNullOrEmpty(oState.ToString()))128 {129 sOrder.Append("and [State]=@State");130 paras.Add(new SqlParameter("@State",oState.ToString()));131 }132

133 contString =sOrder.ToString();134

135 }