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

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

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

try {

			/****************** 分页每次取100条记录 **********************/

			// 总记录数
			int totalNum = wsCisInPatRegDao.queryTotalNum();
			System.out.println("总记录数"+totalNum);
			// 每页数量
			int pageSize = 100;
			// 取余数值，根据此值判断页数是否加1
			int tempValue = totalNum % pageSize;
			// 循环次数（页数）
			int pageNum;
			// 如果总记录数除以页数整除，则结果为页数，否则页数为结果+1
			if (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);

			}

	/**
	 * 查询总记录数
	 */
	@Override
	public 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;

	}

	/**
	 * 分页查询
	 */
	@Override
	public 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='查询条件';。

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

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