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;}@Overridepublic 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>