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

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>
		<!--web 模块依赖 -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<!--web 单元测试 -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<!-- apache common 模块 -->
		<!--commons-lang3 工具包 -->
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-lang3</artifactId>
			<version>${commons-lang3.version}</version>
		</dependency>
		<!--commons-codec 加密工具包 -->
		<dependency>
			<groupId>commons-codec</groupId>
			<artifactId>commons-codec</artifactId>
			<version>${commons-codec.version}</version>
		</dependency>
		<!--commons-net 网络工具包 -->
		<dependency>
			<groupId>commons-net</groupId>
			<artifactId>commons-net</artifactId>
			<version>${commons-net.version}</version>
		</dependency>
		<!--common-io 工具包 -->
		<dependency>
			<groupId>commons-io</groupId>
			<artifactId>commons-io</artifactId>
			<version>${commons-io.version}</version>
		</dependency>
		<!--common-collection 工具包 -->
		<dependency>
			<groupId>commons-collections</groupId>
			<artifactId>commons-collections</artifactId>
			<version>${commons-collections.version}</version>
		</dependency>
		<!-- common-text 工具包 -->
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-text</artifactId>
			<version>${commons-text.version}</version>
		</dependency>
		<!-- springboot整合mybatis -->
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>${mybatis-spring-boot-starter.version}</version>
		</dependency>
		<!-- oracle驱动 -->
		<dependency>
　			<groupId>com.oracle</groupId>
  			<artifactId>ojdbc6</artifactId>
  			<version>${oracle.version}</version>
		</dependency>
		<!-- alibaba druid 数据库连接池  -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid-spring-boot-starter</artifactId>
			<version>${com.alibaba.druid.version}</version>
		</dependency>
		<!-- PageHelp  -->
         <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.5</version>
        </dependency>
        <!-- 集成Servlet-api 模块  -->
        <dependency> 
		   <groupId>javax.servlet</groupId> 
		   <artifactId>servlet-api</artifactId> 
		   <version>2.5</version> 
		   <scope>provided</scope> 
		</dependency> 
	</dependencies>

</project>

SpringBoot 项目结构截图:

实现PageHelper 分页详细步骤

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

<!-- PageHelp  -->
         <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);
	}

效果截图：

分页功能公共类封装:

自定义分页对象：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;
	}
}