jfinal分頁中使用order by遇到的坑

我遇到的問題是排序沒有作用。

原代碼

public Page<House> searchIzz(int pageNum, Integer pageSize, String keyword, Integer cityId, Integer areaId, Integer leaseType, String roomNumType, String hallNumType
			, String minPrice, String maxPrice, String longitude, String latitude, Integer limitDistance, Integer orderBy){
		List<Object> params = new ArrayList<Object>();
		StringBuilder builder = new StringBuilder("select a.id,a.image,a.leaseType,a.area,a.money,a.roomNum,a.hallNum,a.numberPlate,b.landlordAccountId,b.regionName,b.address,b.communityName,b.buildNo");
		builder.append(" from fwzl_house a inner join fwzl_building b on a.buildingId = b.id");
		builder.append(" where a.delFlag = 0 and b.delFlag = 0");
		builder.append(" and a.rentStatus = ?");
		params.add(FwzlConstant.HOUSE_RENT_STATUS_UPPER);
		if(StrUtil.isNotBlank(keyword)){
			builder.append(" and (a.title like ? or b.communityName like ? or b.buildNo like ? or a.numberPlate like ?)");
			params.add("%"+keyword+"%");
			params.add("%"+keyword+"%");
			params.add("%"+keyword+"%");
			params.add("%"+keyword+"%");
		}
		if(cityId != null){
			builder.append(" and b.cityId = ?");
			params.add(cityId);
		}
		if(areaId != null){
			builder.append(" and b.areaId = ?");
			params.add(areaId);
		}
		if(leaseType != null){
			builder.append(" and a.leaseType = ?");
			params.add(leaseType);
		}
		if(StrUtil.isNotBlank(roomNumType)){
			builder.append(" and (");
			String[] str = roomNumType.split(",");
			for(int i=0; i <str.length; i++){
				if(i>0){
					builder.append(" or ");
				}
				builder.append("a.roomNum");
				Integer room = Integer.valueOf(str[i]);
				builder.append(room < 5 ? " = " : " >= ");
				builder.append(room);
			}
			builder.append(")");
		}
		if(StrUtil.isNotBlank(hallNumType)){
			builder.append(" and (");
			String[] str = hallNumType.split(",");
			for(int i=0; i <str.length; i++){
				if(i>0){
					builder.append(" or ");
				}
				builder.append("a.hallNum");
				Integer room = Integer.valueOf(str[i]);
				builder.append(room < 2 ? " = " : " >= ");
				builder.append(room);
			}
			builder.append(")");
		}
		if(StrUtil.isNotBlank(minPrice)){
			if(!CalculateKit.isMoney(minPrice)){
				return null;
			}
			builder.append(" and a.money >= ?");
			params.add(minPrice);
		}
		if(StrUtil.isNotBlank(maxPrice)){
			if(!CalculateKit.isMoney(maxPrice)){
				return null;
			}
			builder.append(" and a.money <= ?");
			params.add(maxPrice);
		}
		if(limitDistance != null){
			if(!CalculateKit.isDouble(longitude) || !CalculateKit.isDouble(latitude)){
				return null;
			}
			builder.append(" and round((6371 * acos (cos(radians("+latitude+")) * cos(radians(b.latitude)) * cos(radians(b.longitude) - radians("+longitude+"))+sin(radians("+latitude+")) * sin(radians(b.latitude))) * 1000)) <= ?");
			params.add(limitDistance);
		}
		if(orderBy != null && orderBy > 0){
			if(orderBy == 1){
				builder.append(" order by a.money asc");
			}else if(orderBy == 2){
				builder.append(" order by a.money desc");
			}else if(orderBy == 3){
				builder.append(" order by a.upperTime desc");
			}
		}else{
			builder.append(" order by a.createTime desc");
		}
		return getDao().paginate(pageNum, pageSize, "select *", "from("+builder.toString()+") as temp", params.toArray());
	}
           

修改後代碼：

public Page<House> searchIzz2(int pageNum, Integer pageSize, String keyword, Integer cityId, Integer areaId, Integer leaseType, String roomNumType, String hallNumType
			, String minPrice, String maxPrice, String longitude, String latitude, Integer limitDistance, Integer orderBy){
		List<Object> params = new ArrayList<Object>();
		String b = "select a.id,a.image,a.leaseType,a.area,a.money,a.roomNum,a.hallNum,a.numberPlate,b.landlordAccountId,b.regionName,b.address,b.communityName,b.buildNo";
		StringBuilder builder = new StringBuilder(" from fwzl_house a inner join fwzl_building b on a.buildingId = b.id");
		builder.append(" where a.delFlag = 0 and b.delFlag = 0");
		builder.append(" and a.rentStatus = ?");
		params.add(FwzlConstant.HOUSE_RENT_STATUS_UPPER);
		if(StrUtil.isNotBlank(keyword)){
			builder.append(" and (a.title like ? or b.communityName like ? or b.buildNo like ? or a.numberPlate like ?)");
			params.add("%"+keyword+"%");
			params.add("%"+keyword+"%");
			params.add("%"+keyword+"%");
			params.add("%"+keyword+"%");
		}
		if(cityId != null){
			builder.append(" and b.cityId = ?");
			params.add(cityId);
		}
		if(areaId != null){
			builder.append(" and b.areaId = ?");
			params.add(areaId);
		}
		if(leaseType != null){
			builder.append(" and a.leaseType = ?");
			params.add(leaseType);
		}
		if(StrUtil.isNotBlank(roomNumType)){
			builder.append(" and (");
			String[] str = roomNumType.split(",");
			for(int i=0; i <str.length; i++){
				if(i>0){
					builder.append(" or ");
				}
				builder.append("a.roomNum");
				Integer room = Integer.valueOf(str[i]);
				builder.append(room < 5 ? " = " : " >= ");
				builder.append(room);
			}
			builder.append(")");
		}
		if(StrUtil.isNotBlank(hallNumType)){
			builder.append(" and (");
			String[] str = hallNumType.split(",");
			for(int i=0; i <str.length; i++){
				if(i>0){
					builder.append(" or ");
				}
				builder.append("a.hallNum");
				Integer room = Integer.valueOf(str[i]);
				builder.append(room < 2 ? " = " : " >= ");
				builder.append(room);
			}
			builder.append(")");
		}
		if(StrUtil.isNotBlank(minPrice)){
			if(!CalculateKit.isMoney(minPrice)){
				return null;
			}
			builder.append(" and a.money >= ?");
			params.add(minPrice);
		}
		if(StrUtil.isNotBlank(maxPrice)){
			if(!CalculateKit.isMoney(maxPrice)){
				return null;
			}
			builder.append(" and a.money <= ?");
			params.add(maxPrice);
		}
		if(limitDistance != null){
			if(!CalculateKit.isDouble(longitude) || !CalculateKit.isDouble(latitude)){
				return null;
			}
			builder.append(" and round((6371 * acos (cos(radians("+latitude+")) * cos(radians(b.latitude)) * cos(radians(b.longitude) - radians("+longitude+"))+sin(radians("+latitude+")) * sin(radians(b.latitude))) * 1000)) <= ?");
			params.add(limitDistance);
		}
		String orderByStr = "";
		if(orderBy != null && orderBy > 0){
			if(orderBy == 1){
				orderByStr = " order by a.money asc";
			}else if(orderBy == 2){
				orderByStr = " order by a.money desc";
			}else if(orderBy == 3){
				orderByStr = " order by a.upperTime desc";
			}
		}else{
			orderByStr = " order by a.createTime desc";
		}
		return getDao().paginateByFullSql(pageNum, pageSize, "select count(*) " + builder.toString(), b+builder.toString()+orderByStr, params.toArray());
	}
           

具體解決辦法是看了看操作手冊說複雜排序的sql可以換一種解決辦法，雖然我這個不複雜，但換一種方式能解決也挺好。

如下：