基于SqlServer+hibernate的简单分页查询

目前项目中用分页查询数据的方式，通过webService把数据循环查询出来传给对方存储，用到了分页。在此我们只需要看分页逻辑代码（相当于ServiceImpl），和Dao层代码（分页查询的SQL）即可。

分页逻辑代码块如下（放在ServiceImpl里）：

try {/****************** 分页每次取100条记录 **********************/// 总记录数int totalNum = wsCisInPatRegDao.queryTotalNum();System.out.println("总记录数"+totalNum);// 每页数量int pageSize = 100;// 取余数值，根据此值判断页数是否加1int tempValue = totalNum % pageSize;// 循环次数（页数）int pageNum;// 如果总记录数除以页数整除，则结果为页数，否则页数为结果+1if (tempValue != 0) {pageNum = totalNum / pageSize + 1;} else {pageNum = totalNum / pageSize;}// 循环，每次取100条记录 循环次数为页数for (int i = 0; i < pageNum; i++) {List<WsCISINPATREG> list = null;// 如每次取行号为如下以此类推的数据：1-100条 101-200条 201-300条// 从数据库查询数据list = wsCisInPatRegDao.pageQuery(1+i*pageSize,pageSize*(i+1));// 将数据转化成要求格式List<CISINPATREG> returnList= WsCISINPATREGVo.changeFromat(list);}

DaoImpl

/** * 查询总记录数 */@Overridepublic Integer queryTotalNum() {List<WsCISINPATREG> returnlist = new ArrayList<WsCISINPATREG>();String sql = " select count(*) as \"totalNum\" "+ "from T_VISIT_RECORD v left join T_CIS_MAIN c on v.HIS_NO=c.HIS_NO "+ "left join T_DIAGNOSIS d on v.HIS_NO=d.HIS_NO and d.BMLX='0' and d.ZDLB='2' "+ "where v.TREAT_TYPE='2' ";returnlist = super.getBySQL(sql, WsCISINPATREG.class, false, null);return returnlist.get(0).getTotalNum();}/** * 返回SQL语句 * @return */public static String getSql() {String sql = " select  distinct dbo.NumberConversion(v.HIS_NO)  as \"visitid\","+ "c.YLJGDM as \"branchcode\","+ "dbo.NumberConversion(v.PAT_ID) as \"patid\","+ "v.HIS_NO as \"visitno\","+ "c.BAH as \"mrno\","+ "v.BED_NO as \"bedid\","+ "dbo.NumberConversion(v.STATE) as \"state\","+ "dbo.NumberConversion(c.ZYYSGH) as \"residentdoctor\","+ "dbo.NumberConversion(c.ZZYSGH) as \"attendingdoctor\","+ "dbo.NumberConversion(c.ZRYSGH) as \"chiefdoctor\","+ "dbo.NumberConversion(v.ADMISSION_DEPT_CODE) as \"indeptid\","+ "dbo.NumberConversion(c.RYBQ) as \"inwardid\","+ "dbo.NumberConversion(c.CYFS) as \"outway\","+ " dbo.NumberConversion(c.BXLX) as \"feenature\","+ "dbo.DateConversion(v.TREAT_DATE) as \"regtime\","+ "dbo.DateConversion(v.LEAVE_TIME) as \"clnouttime\","+ "dbo.NumberConversion(v.TREAT_TYPE) as \"inway\","+ "d.ZDBM as \"indiagcode\","+ "d.ZDSM as \"indiagname\","+ "v.CARD_NUMBER as \"carddata\","+ "dbo.NumberConversion(v.NURSING_LEVEL) as \"carelevel\" ,"+" dbo.NumberConversion(v.ADMISSION_DEPT_CODE) as \"deptid\", "                +" dbo.NumberConversion(v.WARD_CODE) as \"wardid\" " + "from T_VISIT_RECORD v left join T_CIS_MAIN c on v.HIS_NO=c.HIS_NO "+ "left join T_DIAGNOSIS d on v.HIS_NO=d.HIS_NO and d.BMLX='0' and d.ZDLB='2' "+ "left join T_TRANSFER_RECORD t on v.HIS_NO=t.HIS_NO "+ " where v.TREAT_TYPE='2' ";return sql;}/** * 分页查询 */@Overridepublic List<WsCISINPATREG> pageQuery(int beginNum, int endNum) {List<WsCISINPATREG> returnlist = new ArrayList<WsCISINPATREG>();String querySl = WsCisInPatRegDaoImpl.getSql();String sql = "SELECT * FROM (SELECT t.*,ROW_NUMBER() OVER (ORDER BY t.visitid desc) AS rowNum FROM ("+ querySl+ ") t) t1  WHERE rowNum >="+ beginNum+ " AND rowNum <=" + endNum + "";returnlist = super.getBySQL(sql, WsCISINPATREG.class, false, null);return returnlist;}

这里在说明一下分页的原理。

分页最主要的就是两次查询：

第一次，查询出满足当前条件的总记录数，用于计算页数等。比如，select count(*) from Table where coloum='查询条件';。

第二次，才是根据第一次查询条件得出的相关参数，进行分页查询，通常页数是前端发送过来的，在我的这个案例里面并不是，我的这个属于循环分页取得所有的数据。

不同的数据库分页语句不一样，这个大家可以自行搜索，然后写语句的时候放到数据库客户端中调试。如果在程序中调试费时间，因为每次都要重新部署启动服务器。