Spring Boot+Mybatis+Pagehelper分页

Myth丶恋晨 2022-06-08 13:23 266阅读 0赞

Spring Boot 集成MyBatis和Pagehelper分页插件

mybatis-spring-boot-starter依赖树如下：

![image][]

# pom配置 #

<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.xiaolyuh</groupId>
        <artifactId>spring-boot-student-mybatis</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <packaging>jar</packaging>
        <name>spring-boot-student-mybatis</name>
    
        
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>1.5.3.RELEASE</version>
            <relativePath/> 
        </parent>
    
        <properties>
            <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
            <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
            <java.version>1.8</java.version>
        </properties>
    
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
            </dependency>
    
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>1.3.0</version>
            </dependency>
            
            <dependency>
                <groupId>com.github.pagehelper</groupId>
                <artifactId>pagehelper-spring-boot-starter</artifactId>
                <version>1.1.1</version>
            </dependency>
    
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>fastjson</artifactId>
                <version>1.2.31</version>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    
    </project>

# application.properties配置 #

server.port=80
    # 数据源配置
    spring.datasource.url=jdbc:mysql://localhost:3306/ssb_test
    spring.datasource.driver-class-name=com.mysql.jdbc.Driver
    spring.datasource.username=root
    spring.datasource.password=root
    #连接池配置
    #spring.datasource.type=org.apache.commons.dbcp2.BasicDataSource
    
    #mybatis
    #entity扫描的包名
    mybatis.type-aliases-package=com.xiaolyuh.domain.model
    #Mapper.xml所在的位置
    mybatis.mapper-locations=classpath*:/mybaits/*Mapper.xml
    
    #pagehelper分页插件配置
    pagehelper.helperDialect=mysql
    pagehelper.reasonable=true
    pagehelper.supportMethodsArguments=true
    pagehelper.params=count=countSql
    
    #日志配置
    logging.level.com.xiaolyuh=debug
    logging.level.org.springframework.web=debug
    logging.level.org.springframework.transaction=debug
    logging.level.org.mybatis=debug
    
    debug=false

除了上面常见的两项配置，还有：

mybatis.config-location=mybatis-config.xml配置文件的路径
    mybatis.type-handlers-package=扫描typeHandlers的包
    mybatis.check-config-location=检查配置文件是否存在
    mybatis.executor-type=设置执行模式（SIMPLE, REUSE, BATCH），默认为SIMPLE

# Mapping XML文件 #

在resources文件夹下创建mybaits/PersonMapper.xml文件

<?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.xiaolyuh.domain.mapper.PersonMapper" >
      <resultMap id="BaseResultMap" type="com.xiaolyuh.domain.model.Person" >
        
        <id column="id" property="id" jdbcType="BIGINT" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="age" property="age" jdbcType="INTEGER" />
        <result column="address" property="address" jdbcType="VARCHAR" />
      </resultMap>
      <sql id="Base_Column_List" >
        
        id, name, age, address
      </sql>
      <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >
        
        select 
        <include refid="Base_Column_List" />
        from person
        where id = #{id,jdbcType=BIGINT}
      </select>
      <delete id="deleteByPrimaryKey" parameterType="java.lang.Long" >
        
        delete from person
        where id = #{id,jdbcType=BIGINT}
      </delete>
      <insert id="insert" parameterType="com.xiaolyuh.domain.model.Person" >
        
        <selectKey resultType="java.lang.Long" keyProperty="id" order="AFTER" >
          SELECT LAST_INSERT_ID()
        </selectKey>
        insert into person (name, age, address
          )
        values (#{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER}, #{address,jdbcType=VARCHAR}
          )
      </insert>
      <insert id="insertSelective" parameterType="com.xiaolyuh.domain.model.Person" >
        
        <selectKey resultType="java.lang.Long" keyProperty="id" order="AFTER" >
          SELECT LAST_INSERT_ID()
        </selectKey>
        insert into person
        <trim prefix="(" suffix=")" suffixOverrides="," >
          <if test="name != null" >
            name,
          </if>
          <if test="age != null" >
            age,
          </if>
          <if test="address != null" >
            address,
          </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides="," >
          <if test="name != null" >
            #{name,jdbcType=VARCHAR},
          </if>
          <if test="age != null" >
            #{age,jdbcType=INTEGER},
          </if>
          <if test="address != null" >
            #{address,jdbcType=VARCHAR},
          </if>
        </trim>
      </insert>
      <update id="updateByPrimaryKeySelective" parameterType="com.xiaolyuh.domain.model.Person" >
        
        update person
        <set >
          <if test="name != null" >
            name = #{name,jdbcType=VARCHAR},
          </if>
          <if test="age != null" >
            age = #{age,jdbcType=INTEGER},
          </if>
          <if test="address != null" >
            address = #{address,jdbcType=VARCHAR},
          </if>
        </set>
        where id = #{id,jdbcType=BIGINT}
      </update>
      <update id="updateByPrimaryKey" parameterType="com.xiaolyuh.domain.model.Person" >
        
        update person
        set name = #{name,jdbcType=VARCHAR},
          age = #{age,jdbcType=INTEGER},
          address = #{address,jdbcType=VARCHAR}
        where id = #{id,jdbcType=BIGINT}
      </update>
    
      <select id="findAll" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List" />
        from person
      </select>
    
      <select id="findByPage" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List" />
        from person
      </select>
    </mapper>

# DAO层Mapper类 #

在Mapper接口上需要加上@Mapper注解，@Mapper注解声明成mybatis Dao层的Bean。也可以在配置类上使用@MapperScan("com.xiaolyuh.domain.mapper")注解声明。

package com.xiaolyuh.domain.mapper;
    
    import com.github.pagehelper.Page;
    import com.xiaolyuh.domain.model.Person;
    import org.apache.ibatis.annotations.Mapper;
    
    import java.util.List;
    
    @Mapper//声明成mybatis Dao层的Bean，也可以在配置类上使用@MapperScan("com.xiaolyuh.domain.mapper")注解声明
    public interface PersonMapper {
    
        int deleteByPrimaryKey(Long id);
    
        int insert(Person record);
    
        int insertSelective(Person record);
    
        Person selectByPrimaryKey(Long id);
    
        int updateByPrimaryKeySelective(Person record);
    
        int updateByPrimaryKey(Person record);
    
        /** * 获取所有数据 * @return */
        List<Person> findAll();
    
        /** * 分页查询数据 * @return */
        Page<Person> findByPage();
    }

## 实体类 ##

package com.xiaolyuh.domain.model;
    
    public class Person {
    
        private Long id;
    
        /** * 名称 */
        private String name;
    
        /** * 年龄 */
        private Integer age;
    
        /** * 地址 */
        private String address;
    
        public Long getId() {
            return id;
        }
    
        public void setId(Long id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public Integer getAge() {
            return age;
        }
    
        public void setAge(Integer age) {
            this.age = age;
        }
    
        public String getAddress() {
            return address;
        }
    
        public void setAddress(String address) {
            this.address = address;
        }
    }

# Service层 #

## 接口 ##

package com.xiaolyuh.service;
    
    import com.github.pagehelper.Page;
    import com.xiaolyuh.domain.model.Person;
    
    import java.util.List;
    
    /** * Created by yuhao.wang on 2017/6/19. */
    public interface PersonService {
    
        List<Person> findAll();
    
        /** * 分页查询 * @param pageNo 页号 * @param pageSize 每页显示记录数 * @return */
        Page<Person> findByPage(int pageNo, int pageSize);
    
        void insert(Person person);
    }

## 实现类 ##

package com.xiaolyuh.service.impl;
    
    import com.github.pagehelper.Page;
    import com.github.pagehelper.PageHelper;
    import com.xiaolyuh.domain.mapper.PersonMapper;
    import com.xiaolyuh.domain.model.Person;
    import com.xiaolyuh.service.PersonService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;
    
    import java.util.List;
    
    /** * Created by yuhao.wang on 2017/6/19. */
    @Service
    @Transactional(readOnly = true)
    public class PersonServiceImpl implements PersonService {
    
        @Autowired
        private PersonMapper personMapper;
    
        @Override
        public List<Person> findAll() {
            return personMapper.findAll();
        }
    
        @Override
        public Page<Person> findByPage(int pageNo, int pageSize) {
            PageHelper.startPage(pageNo, pageSize);
            return personMapper.findByPage();
        }
    
        @Override
        @Transactional
        public void insert(Person person) {
            personMapper.insert(person);
        }
    
    }

# 分页的包装类PageInfo #

需要把Page包装成PageInfo对象才能序列化。该插件也默认实现了一个PageInfo

package com.xiaolyuh.page;
    
    import java.io.Serializable;
    import java.util.Collection;
    import java.util.List;
    
    import com.github.pagehelper.Page;
    
    /** * 对Page<E>结果进行包装 * <p/> * 新增分页的多项属性，主要参考:http://bbs.csdn.net/topics/360010907 * * @author liuzh/abel533/isea533 * @version 3.3.0 * @since 3.2.2 * 项目地址 : http://git.oschina.net/free/Mybatis_PageHelper */
    @SuppressWarnings({
       "rawtypes", "unchecked"})
    public class PageInfo<T> implements Serializable {
        private static final long serialVersionUID = 1L;
        //当前页
        private int pageNum;
        //每页的数量
        private int pageSize;
        //总记录数
        private long total;
        //总页数
        private int pages;
        //结果集
        private List<T> list;
        //是否为第一页
        private boolean isFirstPage = false;
        //是否为最后一页
        private boolean isLastPage = false;
    
    
        public PageInfo() {
        }
    
        /** * 包装Page对象 * * @param list */
        public PageInfo(List<T> list) {
            if (list instanceof Page) {
                Page page = (Page) list;
                this.pageNum = page.getPageNum();
                this.pageSize = page.getPageSize();
    
                this.pages = page.getPages();
                this.list = page;
                this.total = page.getTotal();
            } else if (list instanceof Collection) {
                this.pageNum = 1;
                this.pageSize = list.size();
    
                this.pages = 1;
                this.list = list;
                this.total = list.size();
            }
            if (list instanceof Collection) {
                //判断页面边界
                judgePageBoudary();
            }
        }
    
        /** * 判定页面边界 */
        private void judgePageBoudary() {
            isFirstPage = pageNum == 1;
            isLastPage = pageNum == pages;
        }
    
        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 getTotal() {
            return total;
        }
    
        public void setTotal(long total) {
            this.total = total;
        }
    
        public int getPages() {
            return pages;
        }
    
        public void setPages(int pages) {
            this.pages = pages;
        }
    
        public List<T> getList() {
            return list;
        }
    
        public void setList(List<T> list) {
            this.list = list;
        }
    
        public boolean isIsFirstPage() {
            return isFirstPage;
        }
    
        public void setIsFirstPage(boolean isFirstPage) {
            this.isFirstPage = isFirstPage;
        }
    
        public boolean isIsLastPage() {
            return isLastPage;
        }
    
        public void setIsLastPage(boolean isLastPage) {
            this.isLastPage = isLastPage;
        }
    
        @Override
        public String toString() {
            final StringBuffer sb = new StringBuffer("PageInfo{");
            sb.append("pageNum=").append(pageNum);
            sb.append(", pageSize=").append(pageSize);
            sb.append(", total=").append(total);
            sb.append(", pages=").append(pages);
            sb.append(", list=").append(list);
            sb.append(", isFirstPage=").append(isFirstPage);
            sb.append(", isLastPage=").append(isLastPage);
            sb.append(", navigatepageNums=");
            sb.append('}');
            return sb.toString();
        }
    }

# 测试类 #

package com.xiaolyuh;
    
    import com.github.pagehelper.Page;
    import com.xiaolyuh.domain.model.Person;
    import com.xiaolyuh.page.PageInfo;
    import com.xiaolyuh.service.PersonService;
    import org.junit.Assert;
    import org.junit.Before;
    import org.junit.Test;
    import org.junit.runner.RunWith;
    import com.alibaba.fastjson.JSON;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.test.context.SpringBootTest;
    import org.springframework.test.context.junit4.SpringRunner;
    
    import java.util.List;
    
    @RunWith(SpringRunner.class)
    @SpringBootTest
    public class PersonMapperTests {
    
    	private Logger logger = LoggerFactory.getLogger(PersonMapperTests.class);
    
    	@Autowired
    	private PersonService personService;
    
    	@Before
    	public void testInsert() {
    		Person person = new Person();
    		person.setName("测试");
    		person.setAddress("address");
    		person.setAge(10);
    		personService.insert(person);
    
    		Assert.assertNotNull(person.getId());
    		logger.debug(JSON.toJSONString(person));
    	}
    
    	@Test
    	public void testFindAll() {
    		List<Person> persons = personService.findAll();
    
    		Assert.assertNotNull(persons);
    		logger.debug(JSON.toJSONString(persons));
    	}
    
    	@Test
    	public void testFindByPage() {
    		Page<Person> persons = personService.findByPage(1, 2);
    		// 需要把Page包装成PageInfo对象才能序列化。该插件也默认实现了一个PageInfo
    		PageInfo<Person> pageInfo = new PageInfo<>(persons);
    		Assert.assertNotNull(persons);
    		logger.debug(pageInfo.toString());
    		logger.debug(JSON.toJSONString(pageInfo));
    	}
    
    	@Test
    	public void testCacheByPage() {
    		long begin = System.currentTimeMillis();
    		List<Person> persons = personService.findAll();
    		long ing = System.currentTimeMillis();
    		personService.findAll();
    		long end = System.currentTimeMillis();
    		logger.debug("第一次请求时间：" + (ing - begin) + "ms");
    		logger.debug("第二次请求时间:" + (end - ing) + "ms");
    
    		Assert.assertNotNull(persons);
    		logger.debug(persons.toString());
    		logger.debug(JSON.toJSONString(persons));
    	}
    
    }

# 源码 #

[https://github.com/wyh-spring-ecosystem-student/spring-boot-student/tree/releases][https_github.com_wyh-spring-ecosystem-student_spring-boot-student_tree_releases]

spring-boot-student-mybatis工程

[image]: /images/20220608/d7aac07c86a94f7d9882aca581bcb4b6.png
[https_github.com_wyh-spring-ecosystem-student_spring-boot-student_tree_releases]: https://github.com/wyh-spring-ecosystem-student/spring-boot-student/tree/releases