JAVA 分页查询

啥也不说了，直接撸代码！

	//import com.cup.framework.ssi.common.base.pagination.Page;
	//import com.cup.framework.ssi.common.base.pagination.PagingList;

	public PagingQueryResult pagingQuery(String userId, String currentPage) {
	
		PagingQueryResult result = new PagingQueryResult();
		
		MonoPagingQuery query = new MonoPagingQuery();
		query.setuserId(userId);
		query.setMonoType("0");
		query.setStatus(new String[]{"0", "1", "2", "3"});
		if(StringUtils.isNotBlank(currentPage)){
			//默认为第1页
			query.setPageNumber(Integer.valueOf(currentPage)); 
		}
		
		try {
			PagingList<MonoInner> pagingList;
			int count = (Integer) MonoInnerDao.countQuery(query);
			
			Integer totalPage = count % query.getPageSize() == 0 ? (count / query.getPageSize()) : (count / query.getPageSize() + 1);
			result.setTotalpage(String.valueOf(totalPage));
			result.setCurrentPage(currentPage);
			
			if(query.getStartIndex() > count){
				pagingList = new PagingList<MonoInner>(null, new Page()); 
				
			}
			
			List<MonoInner> queryResult = MonoInnerDao.query(query);
			
			Page page = new Page();
			page.setTotalCount(count);
			page.setPageSize(query.getPageSize());
			page.setPageNo(query.getPageNumber());
			pagingList = new PagingList<MonoInner>(queryResult, page);
			
			List<MonoInner> monoOrderList = pagingList.getList();
			if(monoOrderList == null || monoOrderList.size() <= 0){
				//分页查询完了
				return result;
			}
			
			List<monoHisOrderList> orderListTemp = new ArrayList<monoHisOrderList>();
			for(MonoInner aSingleOrder : monoOrderList){
				try {
					//处理结果
					orderListTemp.add(monoHisOrderList);
				} catch (Exception e) {
					logger.warn(" ");
					continue;
				}
			} 
			result.setOrderList(orderListTemp);
		} catch (Exception e) {
			logger.error(" ");
			result.setTotalpage("-1"); 
			return result;
		}
		return result;
	}

//import java.util.Arrays;
//import java.util.Date;

public class MonoPagingQuery extends PageQuery {
	
	private String userId;
	
	private String monoType;
	
	private String[] status;
	
	private Date startDate;
	
	private Date endDate;

	public String getUserId() {
		return userId;
	}

	public void setUserId(String userId) {
		this.userId = userId;
	}

	public String getMonoType() {
		return monoType;
	}

	public void setMonoType(String monoType) {
		this.monoType = monoType;
	}

	public String[] getStatus() {
		return status;
	}

	public void setStatus(String[] status) {
		this.status = status;
	}
	
	public Date getStartDate() {
		return startDate;
	}

	public void setStartDate(Date startDate) {
		this.startDate = startDate;
	}

	public Date getEndDate() {
		return endDate;
	}

	public void setEndDate(Date endDate) {
		this.endDate = endDate;
	}

	@Override
	public String toString() {
		return "MonoPagingQuery [userId=" + userId + ", monoType=" + monoType
				+ ", status=" + Arrays.toString(status) + ", startDate="
				+ startDate + ", endDate=" + endDate + "]";
	}

}

 

public class PageQuery {
	int pageSize = 10;
	int pageNumber = 1;
	public int getPageSize() {
		return pageSize;
	}
	public void setPageSize(int pageSize) {
		if(pageSize>0 && pageSize<=1000){
			this.pageSize = pageSize;
		}
	}
	
	public int getPageNumber() {
		return pageNumber;
	}
	
	public void setPageNumber(int pageNumber) {
		if(pageNumber>0)
			this.pageNumber = pageNumber;
	}
	
	public int getStartIndex() {
		return (this.pageNumber-1) * this.pageSize + 1;
	}
	
	public int getEndIndex() {
		return getStartIndex() + this.pageSize - 1;
	}
}

 

<sql id="query_dynamic_where">
  	<trim prefix="WHERE" prefixOverrides="AND ">
  		<if test="userId != null and userId != ''">
  			AND <![CDATA[USER_ID = #{userId}]]>
  		</if>
  		<if test="monoType != null and monoType != ''">
  			AND <![CDATA[TRANS_TYPE = #{monoType}]]>
  		</if>
     	<if test="status != null">
	    	AND STATUS in 
	    	<foreach collection="status" item="item" open="(" separator="," close=")">
	    		#{item}
	    	</foreach>
	    </if>
	    <if test="startDate != null and startDate != ''">
	    	AND <![CDATA[CREATE_TIME >= #{startDate}]]>
	    </if>
	    <if test="endDate != null and endDate != ''">
	    	AND <![CDATA[CREATE_TIME < #{endDate}]]>
	    </if>
    </trim>
  </sql>
  
  <select id="countQuery" resultType="java.lang.Integer" parameterType="com.zhouzifei.MonoPagingQuery">
	select count(1) from TBL_MONO_ORDER
	<include refid="query_dynamic_where" />
	with ur
  </select>
  
  <select id="query" resultMap="BaseResultMap"  parameterType="com.zhouzifei.MonoPagingQuery">
 	select  
 		<include refid="Base_Column_List" /> 
 	from (
  		select  
  			<include refid="Base_Column_List" />, ROW_NUMBER() OVER(order by CREATE_TIME desc) AS row
  		from TBL_MONO_ORDER
  			<include refid="query_dynamic_where" />
  		
  	) as temp where temp.row between #{startIndex} and #{endIndex} with ur
  </select>