Spring Boot+Mybatis+Pagehelper分页

Spring Boot 集成MyBatis和Pagehelper分页插件

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

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>


<!-- 添加Spring Boot的父类依赖，这样当前项目就是Spring Boot项目了。 spring-boot-starter-parent是一个特殊的starter，他用来

提供相关的maven默认依赖， 使用它之后，常用的依赖可以省去version标签 -->


<parent>


<groupId>org.springframework.boot</groupId>


<artifactId>spring-boot-starter-parent</artifactId>


<version>1.5.3.RELEASE</version>


<relativePath/> <!-- lookup parent from repository -->


</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>


<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>


<!--pagehelper -->


<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" >

    <!--

      WARNING - @mbggenerated

      This element is automatically generated by MyBatis Generator, do not modify.

    -->

    <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" >
    <!--

      WARNING - @mbggenerated

      This element is automatically generated by MyBatis Generator, do not modify.

    -->

    id, name, age, address
  </sql>

  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >

    <!--

      WARNING - @mbggenerated

      This element is automatically generated by MyBatis Generator, do not modify.

    -->

    select 

    <include refid="Base_Column_List" />

    from person

    where id = #{id,jdbcType=BIGINT}

  </select>

  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long" >

    <!--

      WARNING - @mbggenerated

      This element is automatically generated by MyBatis Generator, do not modify.

    -->

    delete from person

    where id = #{id,jdbcType=BIGINT}

  </delete>

  <insert id="insert" parameterType="com.xiaolyuh.domain.model.Person" >

    <!--

      WARNING - @mbggenerated

      This element is automatically generated by MyBatis Generator, do not modify.

    -->

    <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" >

    <!--

      WARNING - @mbggenerated

      This element is automatically generated by MyBatis Generator, do not modify.

    -->

    <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" >

    <!--

      WARNING - @mbggenerated

      This element is automatically generated by MyBatis Generator, do not modify.

    -->

    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" >

    <!--

      WARNING - @mbggenerated

      This element is automatically generated by MyBatis Generator, do not modify.

    -->

    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));


}

}