SpringBoot + MyBatist + Oracle +PageHelper 实现分页功能

女爷i 2023-10-10 11:44 34阅读 0赞

SpringBoot 项目pom.xml 依赖配置文件:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    	<modelVersion>4.0.0</modelVersion>
    	<groupId>com.zzg</groupId>
    	<artifactId>migrate</artifactId>
    	<version>0.0.1-SNAPSHOT</version>
    
    	<parent>
    		<groupId>org.springframework.boot</groupId>
    		<artifactId>spring-boot-starter-parent</artifactId>
    		<version>1.5.18.RELEASE</version>
    	</parent>
    
    	<properties>
    		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    		<encoding>UTF-8</encoding>
    		<java.version>1.8</java.version>
    		<commons-lang.version>2.6</commons-lang.version>
    		<commons-codec.version>1.10</commons-codec.version>
    		<commons-lang3.version>3.9</commons-lang3.version>
    		<commons-net.version>3.6</commons-net.version>
    		<commons-io.version>2.6</commons-io.version>
    		<commons-collections.version>3.2.1</commons-collections.version>
    		<commons-text.version>1.8</commons-text.version>
    		<oracle.version>1.0</oracle.version>
    		<mybatis-spring-boot-starter.version>1.3.2</mybatis-spring-boot-starter.version>
    		<com.alibaba.druid.version>1.1.10</com.alibaba.druid.version>
    	</properties>
    
    	<dependencies>
    		
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-web</artifactId>
    		</dependency>
    		
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-test</artifactId>
    			<scope>test</scope>
    		</dependency>
    		
    		
    		<dependency>
    			<groupId>org.apache.commons</groupId>
    			<artifactId>commons-lang3</artifactId>
    			<version>${commons-lang3.version}</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>commons-codec</groupId>
    			<artifactId>commons-codec</artifactId>
    			<version>${commons-codec.version}</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>commons-net</groupId>
    			<artifactId>commons-net</artifactId>
    			<version>${commons-net.version}</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>commons-io</groupId>
    			<artifactId>commons-io</artifactId>
    			<version>${commons-io.version}</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>commons-collections</groupId>
    			<artifactId>commons-collections</artifactId>
    			<version>${commons-collections.version}</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>org.apache.commons</groupId>
    			<artifactId>commons-text</artifactId>
    			<version>${commons-text.version}</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>org.mybatis.spring.boot</groupId>
    			<artifactId>mybatis-spring-boot-starter</artifactId>
    			<version>${mybatis-spring-boot-starter.version}</version>
    		</dependency>
    		
    		<dependency>
    　			<groupId>com.oracle</groupId>
      			<artifactId>ojdbc6</artifactId>
      			<version>${oracle.version}</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>com.alibaba</groupId>
    			<artifactId>druid-spring-boot-starter</artifactId>
    			<version>${com.alibaba.druid.version}</version>
    		</dependency>
    		
             <dependency>
                <groupId>com.github.pagehelper</groupId>
                <artifactId>pagehelper-spring-boot-starter</artifactId>
                <version>1.2.5</version>
            </dependency>
            
            <dependency> 
    		   <groupId>javax.servlet</groupId> 
    		   <artifactId>servlet-api</artifactId> 
    		   <version>2.5</version> 
    		   <scope>provided</scope> 
    		</dependency> 
    	</dependencies>
    
    </project>

SpringBoot 项目结构截图:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n_size_16_color_FFFFFF_t_70][]

实现PageHelper 分页详细步骤

步骤一：pom.xml 文件添加如下配置:

<dependency>
                <groupId>com.github.pagehelper</groupId>
                <artifactId>pagehelper-spring-boot-starter</artifactId>
                <version>1.2.5</version>
            </dependency>

步骤二：实列化PageHelper对象，方式分为两种：第一种：通过application.properties 文件方式，第二种：通过@Configuration配置标签，实列化PageHelper对象(推荐第二种方式)

第一种实现方式：

在application.properties 配置文件中添加如下配置:

#pagehelper分页插件配置
    pagehelper.helperDialect=Oracle
    pagehelper.reasonable=true
    pagehelper.offsetAsPageNum=true
    pagehelper.rowBoundsWithCount=true

第二种实现方式:

package com.zzg.configuration;
    
    import java.util.Properties;
    
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    
    import com.github.pagehelper.PageHelper;
    
    /**
     * 分页配置对象
     * @author zzg
     *
     */
    @Configuration
    public class PageHelperConfiguration {
    	/**
    	 * 分页对象实列化
    	 * @return
    	 */
    	@Bean
    	public PageHelper pageHelper() {
    		PageHelper pageHelper = new PageHelper();
    		Properties p = new Properties();
    		p.setProperty("offsetAsPageNum", "true");
    		p.setProperty("rowBoundsWithCount", "true");
    		p.setProperty("reasonable", "true");
    		p.setProperty("dialect", "Oracle");
    		pageHelper.setProperties(p);
    		return pageHelper;
    	}
    }

注意:pagehelper.helperDialect=oracle ，指定数据库类型，PageHelper 支持数据库标识符如下: Oracle,Mysql,MariaDB,SQLite,Hsqldb,PostgreSQL (温馨提示:不区分大小写)

步骤三：SpringBoot 封装PageHelper 分页功能

service接口定义:

package com.zzg.service;
    
    import java.util.Map;
    
    
    import com.zzg.common.BaseService;
    import com.zzg.common.entity.PageDate;
    import com.zzg.common.entity.PageParam;
    import com.zzg.entity.ErmsGuideProjBaseInfo;
    
    public interface ErmsGuideProjBaseInfoService extends BaseService<ErmsGuideProjBaseInfo> {
    	/**
    	 * 自定义分页
    	 * @param parame
    	 * @param rb
    	 * @return
    	 */
    	public PageDate<ErmsGuideProjBaseInfo> selectPage(Map<String, Object> parame, PageParam rb);
    }

service接口实现定义:

package com.zzg.service.impl;
    
    import java.util.List;
    import java.util.Map;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Isolation;
    import org.springframework.transaction.annotation.Propagation;
    import org.springframework.transaction.annotation.Transactional;
    
    import com.github.pagehelper.PageHelper;
    import com.github.pagehelper.PageInfo;
    import com.zzg.common.AbstractService;
    import com.zzg.common.entity.PageDate;
    import com.zzg.common.entity.PageParam;
    import com.zzg.entity.ErmsGuideProjBaseInfo;
    import com.zzg.mapper.ErmsGuideProjBaseInfoMapper;
    import com.zzg.service.ErmsGuideProjBaseInfoService;
    
    @Service
    public class ErmsGuideProjBaseInfoServiceImpl extends AbstractService<ErmsGuideProjBaseInfo> implements ErmsGuideProjBaseInfoService {
    	@Autowired
    	ErmsGuideProjBaseInfoMapper mapper;
    	@Override
    	@Transactional(propagation = Propagation.REQUIRED,isolation = Isolation.DEFAULT,timeout=36000,rollbackFor=Exception.class)
    	public int insert(ErmsGuideProjBaseInfo record) {
    		// TODO Auto-generated method stub
    		return mapper.insert(record);
    	}
    
    	@Override
    	@Transactional(propagation = Propagation.REQUIRED,isolation = Isolation.DEFAULT,timeout=36000,rollbackFor=Exception.class)
    	public int insertSelective(ErmsGuideProjBaseInfo record) {
    		// TODO Auto-generated method stub
    		return mapper.insertSelective(record);
    	}
    
    	@Override
    	public ErmsGuideProjBaseInfo selectByPrimaryKey(Long sid) {
    		// TODO Auto-generated method stub
    		return mapper.selectByPrimaryKey(sid);
    	}
    
    	@Override
    	@Transactional(propagation = Propagation.REQUIRED,isolation = Isolation.DEFAULT,timeout=36000,rollbackFor=Exception.class)
    	public int updateByPrimaryKeySelective(ErmsGuideProjBaseInfo record) {
    		// TODO Auto-generated method stub
    		return mapper.updateByPrimaryKeySelective(record);
    	}
    
    	@Override
    	@Transactional(propagation = Propagation.REQUIRED,isolation = Isolation.DEFAULT,timeout=36000,rollbackFor=Exception.class)
    	public int updateByPrimaryKey(ErmsGuideProjBaseInfo record) {
    		// TODO Auto-generated method stub
    		return mapper.updateByPrimaryKey(record);
    	}
    
    	@Override
    	public PageDate<ErmsGuideProjBaseInfo> selectPage(Map<String, Object> parame, PageParam rb) {
    		// TODO Auto-generated method stub
    		PageHelper.startPage(rb.getPageNo(), rb.getLimit());
    		List<ErmsGuideProjBaseInfo> rs = mapper.select(parame);
    		PageInfo<ErmsGuideProjBaseInfo> pageInfo = new PageInfo<ErmsGuideProjBaseInfo>(rs);
    		return super.page(pageInfo.getList(), pageInfo.getPageNum(), pageInfo.getPageSize(), pageInfo.getTotal());
    	}
    
    	@Override
    	@Transactional(propagation = Propagation.REQUIRED,isolation = Isolation.DEFAULT,timeout=36000,rollbackFor=Exception.class)
    	public void deleteByPrimaryKey(Long sid) {
    		// TODO Auto-generated method stub
    		mapper.deleteByPrimaryKey(sid);
    		//模拟事务失败处理
    		int a = 10/0;
    	}
    
    }

controller 调用：

@RequestMapping("/selectPage")
    	@ResponseBody
    	public Result selectAll(HttpServletRequest request) {
    		log.warn("执行分页查询");
    		PageParam rb = super.initPageBounds(request);
    		Map<String,Object> parameter = new HashMap<String,Object>();
    		PageDate<ErmsGuideProjBaseInfo> list = ermsGuideProjBaseInfoService.selectPage(parameter, rb);
    		return Result.ok().setData(list);
    	}

效果截图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n_size_16_color_FFFFFF_t_70 1][]

分页功能公共类封装:

自定义分页对象：PageDate<T>.java

package com.zzg.common.entity;
    
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * 自定义分页实体对象
     * @author zzg
     *
     * @param <T>
     */
    public class PageDate<T> implements java.io.Serializable{
    	/**
    	 * 
    	 */
    	private static final long serialVersionUID = 6552522645407013282L;
    	private int pageNum; // 当前页
    	private int pageSize;// 每页数量
    	private long totalCount;// 总条数
    	private int totalPageNum;// 总页数
    	private List<T> data;// 当前页返回数据
    	
    	public int getPageNum() {
    		return pageNum;
    	}
    	public void setPageNum(int pageNum) {
    		this.pageNum = pageNum;
    	}
    	public int getPageSize() {
    		return pageSize;
    	}
    	public void setPageSize(int pageSize) {
    		this.pageSize = pageSize;
    	}
    	public long getTotalCount() {
    		return totalCount;
    	}
    	public void setTotalCount(long totalCount) {
    		this.totalCount = totalCount;
    	}
    	public int getTotalPageNum() {
    		return totalPageNum;
    	}
    	public void setTotalPageNum(int totalPageNum) {
    		this.totalPageNum = totalPageNum;
    	}
    	public List<T> getData() {
    		return data;
    	}
    	public void setData(List<T> data) {
    		this.data = data;
    	}
    	public PageDate(){
    		data = new ArrayList<>();
    	}
    	
    }

自定义分页请求参数:PageParam.java

package com.zzg.common.entity;
    
    public class PageParam implements java.io.Serializable{
    	/**
    	 * 
    	 */
    	private static final long serialVersionUID = -6546262537179268627L;
    
    	public PageParam(int pageNo, int limit){
    		this.pageNo = pageNo;
    		this.limit = limit;
    	}
    	
    	
    	// 一页显示的记录数
        /** The limit. */
        private int limit;
        // 当前页码
        /** The page no. */
        private int pageNo;
        
    	public int getLimit() {
    		return limit;
    	}
    	public void setLimit(int limit) {
    		this.limit = limit;
    	}
    	public int getPageNo() {
    		return pageNo;
    	}
    	public void setPageNo(int pageNo) {
    		this.pageNo = pageNo;
    	}
    }

抽象公共方法，实现PageInfo 分页转换为自定义分页对象PageDate<T>

AbstractService<T>.java

package com.zzg.common;
    
    import java.util.List;
    
    import com.zzg.common.entity.PageDate;
    
    /**
     * 公共服务之分页服务接口
     * @author zzg
     *
     * @param <T>
     */
    public abstract class AbstractService<T> {
    	/**
    	 * 分页抽象服务方法
    	 * @param list
    	 * @param pageNum
    	 * @param pageSize
    	 * @param totla
    	 * @return
    	 */
    	public PageDate<T> page(List<T> list,Integer pageNum, Integer pageSize, Long totla){
    		PageDate<T> page = new PageDate<T>();		
    		page.setData(list);
    		page.setPageNum(pageNum);
    		page.setPageSize(pageSize);
    		page.setTotalCount(totla);
    		return page;
    	}
    }

抽象公共方法，实现HTTP请求转换为分页参数PageParam

AbstractController.java

package com.zzg.common;
    
    import javax.servlet.http.HttpServletRequest;
    
    import org.apache.commons.lang3.StringUtils;
    import org.springframework.beans.SimpleTypeConverter;
    
    import com.zzg.common.constant.WebAppConstants;
    import com.zzg.common.entity.PageParam;
    
    public abstract class AbstractController {
    	/**
    	 * 参数分页参数转换校验：现在仅支持:get请求、参数格式:?page=1&limit=10
    	 * 
    	 * @param param
    	 * @return
    	 */
    	protected PageParam initPageBounds(HttpServletRequest request) {
    		SimpleTypeConverter converter = new SimpleTypeConverter();
    		// 页码、分页大小初始化设置值
    		int page = 1;
    		int limit = 10;
    		if(StringUtils.isNotEmpty(request.getParameter(WebAppConstants.PAGE))){
    			page = converter.convertIfNecessary(request.getParameter(WebAppConstants.PAGE), int.class);
    		}
    		if(StringUtils.isNotEmpty(request.getParameter(WebAppConstants.LIMIT))){
    			limit = converter.convertIfNecessary(request.getParameter(WebAppConstants.LIMIT), int.class);
    		}
    		PageParam pb = new PageParam(page, limit);         
    		return pb;
    	}
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20201105172534631.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201105173736998.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n,size_16,color_FFFFFF,t_70