oracle分页查询

古城微笑少年丶 2023-10-05 19:52 24阅读 0赞

第一步：实体类准备  
分页查询条件封装

/**
     *  分页条件
     *
     */
    @Setter
    @Getter
    public class MapperPage {
        
    
    	/**
    	 * 默认从1开始
    	 * 起始序号 （页号-1）*显示数量+1
    	 */
    	protected Long pageSt;
    	
    	/**
    	 * 结束序号 页号*显示数量
    	 */
    	protected Long pageEd;
    	
    	/**
    	 * 查询条件
    	 */
    	protected String search;
    	
    	public MapperPage (Long pageNo,Long pageSize,String search) {
        
    		if(pageNo<=0) pageNo = 1L;
    		super.pageSt = (pageNo-1)*pageSize+1;
    		super.search = search;
    		super.pageEd = pageNo*pageSize;
    	}
    }

分页返回结果

/**
     * 分页返回
     *
     */
    @Setter
    @Getter
    @ToString
    public class PageResultVo<T> {
        
    
    	Page page; // page信息
    	
    	List<T> list; //分页数据
    }

/**
     * 分页类
     * */
    public class Page implements java.io.Serializable{
        
    
        private static final long serialVersionUID = 1L;
        //前一页
        private Boolean hasPrePage;
        //后一页
        private Boolean hasNextPage;
        //每页显示多少条:默认10条
        @ApiModelProperty(value = "每页显示多少条", required = false)
        private Long everyPage = 10L;
        //总页数
        private Long totalPage;
        //当前第多少页:默认第1页
        @ApiModelProperty(value = "当前页", required = false)
        private Long currentPage;
        //开始下标
        private Long beginIndex;
        //结束下标
        private Long endinIndex;
        //总共多少条
        @ApiModelProperty(value = "总数", required = false)
        private Integer totalCount;
    
        public Page() {
        
        }
    
        /**
         * 常用,用于计算分页
         * */
        public Page(Integer totalRecords){
        
            this.totalCount = totalRecords;
            setTotalPage(getTotalPage(totalRecords));
        }
    
       /**
         * 设置每页显示数everyPage 、当前页码currentPage 
         * */
    	public Page(Long pageNo, Long pageSize) {
        
    		this.everyPage = pageSize;
    		this.currentPage = pageNo;
    	}
    
    
        /**
         * 计算总页数 totalPage
         * */
        private Long getTotalPage(Integer totalRecords) {
        
            Long totalPage = 0L;
            everyPage = everyPage == null ? 10L : everyPage;
            if (totalRecords % everyPage == 0)
                totalPage = totalRecords / everyPage;
            else {
        
                totalPage = totalRecords / everyPage + 1;
            }
            return totalPage;
        }
    
      /**
         * 计算开始索引 beginIndex
         * */
        public Long getBeginIndex() {
        
            this.beginIndex = (currentPage-1) * everyPage+1;
            return this.beginIndex;
        }
    
        public void setBeginIndex(Long beginIndex) {
        
            this.beginIndex = beginIndex;
        }
    
        public Long getCurrentPage() {
        
      
            return this.currentPage;
        }
    
        public void setCurrentPage(Long currentPage) {
        
            this.currentPage = currentPage;
        }
    
        public Long getEveryPage() {
        
            this.everyPage = everyPage == 0 ? 10 : everyPage;
            return this.everyPage;
        }
    
        public void setEveryPage(Long everyPage) {
        
            this.everyPage = everyPage;
        }
    
         /**
         * 判断有无下一页 hasNextPage
         * */
        public Boolean getHasNextPage() {
        
            this.hasNextPage = (currentPage != totalPage) && (totalPage != 0);
            return this.hasNextPage;
        }
    
        public void setHasNextPage(Boolean hasNextPage) {
        
            this.hasNextPage = hasNextPage;
        }
     
        /**
         * 判断有无上一页 hasPrePage 
         * */
        public Boolean getHasPrePage() {
        
            this.hasPrePage = currentPage != 1;
            return this.hasPrePage;
        }
    
        public void setHasPrePage(Boolean hasPrePage) {
        
            this.hasPrePage = hasPrePage;
        }
    
        public Long getTotalPage() {
        
            return this.totalPage;
        }
    
        public void setTotalPage(Long totalPage) {
        
            if(totalPage != 0 && this.currentPage >= totalPage){
        
                this.currentPage = totalPage;
            }
            this.totalPage = totalPage;
        }
    
        public Integer getTotalCount() {
        
            return this.totalCount;
        }
    
        public void setTotalCount(Integer totalCount) {
        
            setTotalPage(getTotalPage(totalCount));
            this.totalCount = totalCount;
        }
    
        @Override
        public String toString() {
        
            return ToStringBuilder.reflectionToString(this);
        }
    
    }

第二步：分页查询业务实现  
controller

@PostMapping(value = "/findPage")
    	public PageResultVo<XXXentity> findPage(@RequestParam(name = "currentPage", required = true)  Long currentPage,
    	                                           @RequestParam(name = "pageSize", required = true)  Long pageSize,
    	                                           @RequestParam(name = "search", required = false)  String search) {
        
    		return XXXService.findPage(currentPage,pageSize,search);
    	}

ServiceImpl

@Service
    @Slf4j
    public class XXXServiceImpl implements XXXService{
        
    
    	@Autowired
    	XXXMapper xxxMapper;
        @Override
    	public PageResultVo<NomenclatureSystem> findPage(Long currentPage, Long pageSize, String search) {
        
    		log.info("开始分页查询,查询条件:");
    		
    		PageResultVo pvo = new PageResultVo();//创建分页对象
    		Page page = new Page(currentPage,pageSize); 
    		pvo.setPage(page); 
    		StopWatch st = StopWatch.createStarted();
    
    		List<NomenclatureSystem> list = null;
    		try {
        
    			MapperPage npage = new MapperPage (currentPage,pageSize,search);
    			int count = xxxMapper.findPageCount(npage);
    			if (count != 0 ){
        
    	        	page.setTotalCount(count);
    	        	list = nomenclatureSystemMapper.findPage(npage);
    	        }
    			pvo.setList(list);
    			
    		} catch (Exception e) {
        
    			list = new ArrayList<NomenclatureSystem>(0);
    			pvo.setList(list);
    			log.error("分页查命名体系发送异常",e);
    		}finally {
        
    			st.stop();
    			log.info("本次分页查询耗时:"+st.getTime()+"ms");
    			
    		}
    		return pvo;
    	}
    }

第三步：Mapper

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
    <mapper namespace="com.ats.dt.Mapper.XXXMapper">
       <resultMap id="BaseResultMap" type="com.ats.dt.entity.NomenclatureSystem">
            <id column="NOMENCLATURE_SOID" property="nomenclatureSoid" jdbcType="DECIMAL"/>
            <result column="NAME" property="name" jdbcType="VARCHAR"/>
            <result column="PY_CODE" property="pyCode" jdbcType="VARCHAR"/>
            <result column="WB_CODE" property="wbCode" jdbcType="VARCHAR"/>
            <result column="STATUS" property="status" jdbcType="DECIMAL"/>
            <result column="IS_DELETE" property="isDelete" jdbcType="DECIMAL"/>
            <result column="CREATE_DATE" property="createDate" jdbcType="TIMESTAMP"/>
            <result column="UPDATE_DATE" property="updateDate" jdbcType="TIMESTAMP"/>
            <result column="UPDATE_USER" property="updateUser" jdbcType="DECIMAL"/>
            <result column="DEFINITION" property="definition" jdbcType="VARCHAR"/>
            <result column="IDENTIFIER" property="identifier" jdbcType="VARCHAR"/>
        </resultMap>
    
      <select id="findPageCount" parameterType="com.ats.dt.entity.vo.MapperPage " resultType="int">
            SELECT count(1) FROM NOMENCLATURE_SYSTEM WHERE 1=1
            and IS_DELETE=0
            <if test="search!=null">
                and (NAME like CONCAT(CONCAT('%',#{search}),'%') or PY_CODE  like CONCAT(CONCAT('%',#{search}),'%') or
                WB_CODE like CONCAT(CONCAT('%',#{search}),'%') )
            </if>
    
        </select>
        
       <select id="findPage" parameterType="com.ats.dt.entity.vo.MapperPage" resultMap="BaseResultMap">
            select * FROM
            (SELECT A.*, ROWNUM RN FROM (SELECT * FROM NOMENCLATURE_SYSTEM WHERE 1=1
            and IS_DELETE=0
            <if test="search!=null">
                and (name like CONCAT(CONCAT('%',#{search}),'%') or PY_CODE  like CONCAT(CONCAT('%',#{search}),'%') or
                WB_CODE like CONCAT(CONCAT('%',#{search}),'%') or ns.NOMENCLATURE_SOID like CONCAT(CONCAT('%',#{search}),'%') )
            </if>
            ORDER BY CREATE_DATE DESC) A
            )
            WHERE RN BETWEEN #{pageSt} AND #{pageEd}
        </select>
    </mapper>