Spring Boot系列六 Spring boot集成mybatis、分页插件pagehelper

╰半橙微兮° 2021-06-11 15:10 504阅读 0赞

## 1. 概述 ##

本文的内容包括如下内容：

1.  Spring Boot集成mybatis
2.  Spring Boot集成pagehelper分页插件，定义分页的相关类
3.  实现工具类：model转dto，实现数据层和传输层的解耦
4.  完整展示了从浏览器输入URL，并从数据库操作数据的完整流程

## 2. Spring Boot集成Mybatis ##

### 2.1. pom.xml ###

mybatis和数据库的相关的jar

<dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.0.31</version>
    </dependency>
    
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>1.3.1</version>
    </dependency>
    
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>6.0.6</version>
    </dependency>

### 2.2. Mapper java类和xml及Model ###

**测试表sql如下**

CREATE TABLE `test`.`test` ( `id` INT NOT NULL, `age` INT NULL, `name` VARCHAR(45) NULL, PRIMARY KEY (`id`), UNIQUE INDEX `id_UNIQUE` (`id` ASC));

**表对应Model类: com.hry.spring.mybatis.model.TestModel**

public class TestModel {
        private Integer id;
    
        private Integer age;
    
        private String name;
       // set/get略
    }

**配置Mapper类： com.hry.spring.mybatis.mapper.TestMapper**

public interface TestMapper {
        int deleteByPrimaryKey(Integer id);
    
        int insert(TestModel record);
    
        int insertSelective(TestModel record);
    
        TestModel selectByPrimaryKey(Integer id);
    
        List<TestModel> selectAll();
    
        int updateByPrimaryKeySelective(TestModel record);
    
        int updateByPrimaryKey(TestModel record);
    }

**配置Mapper xml: TestMapper.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.hry.spring.mybatis.mapper.TestMapper">
     <resultMap id="BaseResultMap" type="com.hry.spring.mybatis.model.TestModel">
       <id column="id" jdbcType="INTEGER" property="id" />
       <result column="age" jdbcType="INTEGER" property="age" />
       <result column="name" jdbcType="VARCHAR" property="name" />
     </resultMap>
     <sql id="Base_Column_List">
       id, age, name
     </sql>
     <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
       select 
       <include refid="Base_Column_List" />
       from test
       where id = #{id,jdbcType=INTEGER}
     </select>
     <select id="selectAll"  resultMap="BaseResultMap">
       select
       <include refid="Base_Column_List" />
       from test
         order by id desc
     </select>
    
     <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
       delete from test
       where id = #{id,jdbcType=INTEGER}
     </delete>
     <insert id="insert" parameterType="com.hry.spring.mybatis.model.TestModel">
       insert into test (id, age, name, 
         )
       values (#{id,jdbcType=INTEGER}, #{age,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}
        )
     </insert>
     <insert id="insertSelective" parameterType="com.hry.spring.mybatis.model.TestModel">
       insert into test
       <trim prefix="(" suffix=")" suffixOverrides=",">
         <if test="id != null">
           id,
         </if>
         <if test="age != null">
           age,
         </if>
         <if test="name != null">
           name,
         </if>
    
       </trim>
       <trim prefix="values (" suffix=")" suffixOverrides=",">
         <if test="id != null">
           #{id,jdbcType=INTEGER},
         </if>
         <if test="age != null">
           #{age,jdbcType=INTEGER},
         </if>
         <if test="name != null">
           #{name,jdbcType=VARCHAR},
         </if>
    
       </trim>
     </insert>
     <update id="updateByPrimaryKeySelective" parameterType="com.hry.spring.mybatis.model.TestModel">
       update test
       <set>
         <if test="age != null">
           age = #{age,jdbcType=INTEGER},
         </if>
         <if test="name != null">
           name = #{name,jdbcType=VARCHAR},
         </if>
       </set>
       where id = #{id,jdbcType=INTEGER}
     </update>
     <update id="updateByPrimaryKey" parameterType="com.hry.spring.mybatis.model.TestModel">
       update test
       set age = #{age,jdbcType=INTEGER},
         name = #{name,jdbcType=VARCHAR}
       where id = #{id,jdbcType=INTEGER}
     </update>
    </mapper>

### 2.3. mybatis配置 ###

**spring\_mybatis.xml**  
spring.datasource.url的值配置在application.yml

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"> 
      
      <property name="url" value="${spring.datasource.url}" />
      <property name="username" value="${spring.datasource.username}" />
      <property name="password" value="${spring.datasource.password}" />
      <property name="driverClassName" value="${spring.datasource.driverClassName}" />
    
    
      
      <property name="initialSize" value="${spring.datasource.initialSize:5}" />
      <property name="minIdle" value="${spring.datasource.minIdle:5}" /> 
      <property name="maxActive" value="${spring.datasource.maxActive:20}" />
    
      
      <property name="maxWait" value="${spring.datasource.maxWait:30000}" />
    
      
      <property name="timeBetweenEvictionRunsMillis" value="${spring.datasource.timeBetweenEvictionRunsMillis}" />
    
      
      <property name="minEvictableIdleTimeMillis" value="${spring.datasource.minEvictableIdleTimeMillis}" />
    
      <property name="validationQuery" value="${spring.datasource.validationQuery}" />
      <property name="testWhileIdle" value="${spring.datasource.testWhileIdle}" />
      <property name="testOnBorrow" value="${spring.datasource.testOnBorrow}" />
      <property name="testOnReturn" value="${spring.datasource.testOnReturn}" />
    
      
      <property name="poolPreparedStatements" value="${spring.datasource.poolPreparedStatements}" />
      <property name="maxPoolPreparedStatementPerConnectionSize" value="${spring.datasource.maxPoolPreparedStatementPerConnectionSize}" />
    
      
      <property name="filters" value="${spring.datasource.filters}" />
    
      <property name="connectionProperties" value="{spring.datasource.connectionProperties}" />
    </bean>
    
    
    
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        
        <property name="mapperLocations" value="classpath:com/hry/spring/mybatis/mapper/*.xml"/>
        
        <property name="configLocation" value="classpath:config/spring/mybatis_config.xml"/>
    
    </bean>
    
    
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.hry.spring.mybatis.mapper"/>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
    </bean>
    
    
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager" p:dataSource-ref="dataSource"/>
    
    <tx:advice id="txAdvice" transaction-manager="transactionManager" >
        <tx:attributes>
            <tx:method name="add*" propagation="REQUIRED" />
        <tx:method name="create*" propagation="REQUIRED" />
        <tx:method name="save*" propagation="REQUIRED" />
            <tx:method name="insert*" propagation="REQUIRED" />
        <tx:method name="update*" propagation="REQUIRED" />
        <tx:method name="batch*" propagation="REQUIRED" />
        <tx:method name="del*" propagation="REQUIRED" />
        <tx:method name="get*" propagation="SUPPORTS" read-only="true" />
        <tx:method name="find*" propagation="SUPPORTS" read-only="true" />
            <tx:method name="*" read-only="true"/>
        </tx:attributes>
    </tx:advice>
    <aop:config >
        <aop:pointcut id="pt" expression="execution(* com.hry.spring.mybatis.service..*.*(..))" />
        <aop:advisor pointcut-ref="pt" advice-ref="txAdvice"/>
    </aop:config>

mybatis\_config.xml

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
    
    </configuration>

### 2.4. application.yml ###

配置数据库的信息如下：

# 数据库配置
    spring:
      datasource:
        #### Datasource 配置 ####
        type: com.alibaba.druid.pool.DruidDataSource
        username: root
        password: root
        url: jdbc:mysql://127.0.0.1:3306/test?zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2b8&useSSL=true
        # url: jdbc:mysql://127.0.0.1:3306/test
        driverClassName: com.mysql.cj.jdbc.Driver
        # driverClassName: oracle.jdbc.driver.OracleDriver
        # 下面为连接池的补充设置，应用到上面所有数据源中# 初始化大小，最小，最大
        initialSize: 5
        minIdle: 5
        maxActive: 20
        # 配置获取连接等待超时的时间
        maxWait: 30000
        # 配置一个连接在池中最小生存的时间，单位是毫秒
        minEvictableIdleTimeMillis: 300000
        timeBetweenEvictionRunsMillis: 60000
        validationQuery: SELECT 1 FROM DUAL
        # 打开PSCache，并且指定每个连接上PSCache的大小
        poolPreparedStatements: false
        maxPoolPreparedStatementPerConnectionSize: 20
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
        filters: log4j
        connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

### 2.5. spring boot集成mybatis ###

通过@ImportResource加载mybatis的配置  
用@Bean注解的方法fastJsonHttpMessageConverters表示使用fastjson解析json

@SpringBootApplication
    // 加载mybatis配置
    @ImportResource({
       "classpath:config/spring/spring_*.xml"})
    public class MybatisSpringBoot { 
        public static void main(String[] args){
            SpringApplication.run(MybatisSpringBoot.class, args);
        }
    
        @Bean
        public HttpMessageConverters fastJsonHttpMessageConverters() {
            // 格式化时间
            SerializeConfig mapping = new SerializeConfig();
            mapping.put(Date.class, new SimpleDateFormatSerializer(
                   "yyyy-MM-dd HH:mm:ss"));
    
            FastJsonHttpMessageConverter fastConverter = new FastJsonHttpMessageConverter();
            FastJsonConfig fastJsonConfig = new FastJsonConfig();
            // fastJsonConfig.setSerializerFeatures(SerializerFeature.PrettyFormat);
            fastJsonConfig.setSerializeConfig(mapping);
            fastConverter.setFastJsonConfig(fastJsonConfig);
            HttpMessageConverter<?> converter = fastConverter;
            return new HttpMessageConverters(converter);
        }
    
    }

## 3. Spring Boot集成分布插件Pagehelper ##

以上的功能实现了简单的mybatis应用。但是涉及到数据库的查询，不可避免需要使用到分页。这里我推荐pagehelper插件实现分页功能

### 3.1. pom.xml ###

引入相关的jar

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

### 3.2. 分页相关的辅助类 ###

**MyPage**  
包装返回给前台的信息，包括本次查询的状态码、错误信息、记录总数和数据列表

public class MyPage<T> {
        @JSONField(ordinal = 1)
        private Integer code = 200;// 状态码，默认状态
    
        @JSONField(ordinal = 2)
        private String message = "";// 提示消息或者错误消息
    
        @JSONField(ordinal = 3)
        private String apiId = "";// 请求的唯一标识，预留
    
        @JSONField(ordinal = 4)
        private Integer totalCount = 0;//记录总数
    
        @JSONField(ordinal = 5)
        private List<T> rows = Collections.emptyList();//本次返回的数据列表
       // set/get略
    }

**IPageHelperPageCallBack**  
定义分页的回调方法，如果使分页，则必须在这个方法里使用mapper的方法。  
此接口作为下文的PageCallBackUtil的参数

public interface IPageHelperPageCallBack {
        <T> List<T> select();
    }

**PageCallBackUtil**  
pagehelper的使用核心是：调用PageHelper.startPage(pageNum, pageSize,requireTotalCount)方法设置查询记录起始地址后，然后马上调用mapper类的方法，得到返回列表List，使用（ PageInfo pageInfo = new PageInfo(list)）包装list的PageInfo，PageInfo包含本次查询的信息，包括本次查询的总数，然后将Model转化为Dto类

/** * 分页的回调函数 * Created by huangrongyou@yixin.im on 2017/9/6. */
    public class PageCallBackUtil { 
    
        /** * 封装公共PageHelper的操作 * @param qry * @param callBack * @param <T> * @return */
        public static<T>  MyPage<T> selectRtnPage(AbstractQry qry, IPageHelperPageCallBack callBack){
            Assert.notNull(qry, "qry can't be null!");
            Assert.notNull(callBack, "callBack cant' be null!");
            setPageHelperStartPage(qry);
    
            List<T> list = callBack.select();
            // 分页时，实际返回的结果list类型是Page<E>
            if(!(list instanceof Page)){
                throw new RuntimeException("list must be 'com.github.pagehelper.Page', now is " + list.getClass().getCanonicalName());
            }
            MyPage<T> myPage = new MyPage<T>();
            PageInfo<T> pageInfo = new PageInfo<T>(list);
            myPage.setTotalCount((int) pageInfo.getTotal());
            myPage.setRows(pageInfo.getList());
            return myPage;
        }
    
        /** * 设置PageHelper的startPage * @param qry */
        private static void setPageHelperStartPage(AbstractQry qry) {
            // 设置分页信息
            // pageNum
            Integer pageNum = qry.getPageNum();
            pageNum = pageNum == null? AbstractQry.DEFAULT_PAGENUM : pageNum;
            // pageSize
            Integer pageSize = qry.getPageSize();
            pageSize = pageSize == null ? AbstractQry.DEFAULT_PAGESIZE : pageSize;
            // requireTotalCount
            Boolean requireTotalCount = qry.getRequireTotalCount();
            requireTotalCount = requireTotalCount == null ? AbstractQry.DEFAULT_REQUIRETOTALCOUNT : requireTotalCount;
            PageHelper.startPage(pageNum, pageSize,requireTotalCount);
        }
    }

**Qry**  
查询条件的基类

public interface Qry {
        String getId();
        void setId(String id);
    }

**AbstractQry**  
定义分页的查询的页码信息

public class AbstractQry implements Qry { 
        public static final int DEFAULT_PAGENUM = 1;
        public static final int DEFAULT_PAGESIZE = 1;
        public static final boolean DEFAULT_REQUIRETOTALCOUNT = false;
    
        private String id;
        private Integer pageNum = 1;// 第几页，首页为1
        private Integer pageSize = 10;// 每页记录条数
        private Boolean requireTotalCount = Boolean.FALSE;// 是否需要记录总数
        // set/get略
    }

**TestQry**  
这个类用来定义具体的查询条件

public class TestQry extends AbstractQry{ 
    
    }

### 3.3. 服务层：ITestService和TestServiceImpl ###

**ITestService**

public interface ITestService {
        int deleteByPrimaryKey(Integer id);
    
        int insertSelective(TestModel record);
    
        TestModel selectByPrimaryKey(Integer id);
    
        List<TestModel> selectAll(TestQry qry);
    
        MyPage<TestModel> selectAllWithPage(TestQry qry);
    }

**TestServiceImpl**  
selectAllWithPage定义了方法的使用

@Service
    @Primary
    public class TestServiceImpl implements ITestService{ 
        @Autowired
        private TestMapper testMapper;
    
        @Override
        public int deleteByPrimaryKey(Integer id) {
            Assert.notNull(id, "id can't be null!");
            return testMapper.deleteByPrimaryKey(id);
        }
    
        @Override
        public MyPage<TestModel> selectAllWithPage(TestQry qry) {
            if(qry == null){
                qry = new TestQry();
            }
            MyPage<TestModel> myPage = PageCallBackUtil.selectRtnPage(qry, new IPageHelperPageCallBack() {
                @Override
                public List<TestModel> select() {
                    return  testMapper.selectAll();
                }
            });
            return myPage;
        }
    
        … 其他方法略
    
    }

### 3.4. Control层 ###

类 TestCtl

@RestController
    @RequestMapping(value = "/simple")
    @EnableSwagger2
    public class TestCtl { 
        @Autowired
        private ITestService testService;
    
        @RequestMapping(value = "delete-by-primary-key/{id}", method = RequestMethod.GET)
        public int deleteByPrimaryKey( @PathVariable("id") Integer id){
            // 参数验证略
            return testService.deleteByPrimaryKey(id);
        }
    
        @RequestMapping(value = "insert-selective", method = RequestMethod.POST)
        public int insertSelective(@RequestBody TestDto dto){
            // 参数验证略
            TestModel record = new TestModel();
            record.setId(dto.getId());
            record.setAge(dto.getAge());
            record.setName(dto.getName());
            return testService.insertSelective(record);
        }
    
        @RequestMapping(value = "select-by-primary-key/{id}", method = RequestMethod.POST)
        public TestDto selectByPrimaryKey(@PathVariable("id") String id){
            // 参数验证略
            return Model2DtoUtil.model2Dto(testService.selectByPrimaryKey(Integer.parseInt(id)), TestDto.class);
        }
    
        @RequestMapping(value = "select-all", method = {RequestMethod.POST })
        public List<TestDto> selectAll(@RequestBody TestQry qry){
            return Model2DtoUtil.model2Dto(testService.selectAll(qry), TestDto.class);
        }
    
        @RequestMapping(value = "select-all-with-page", method = {RequestMethod.POST })
        public MyPage<TestDto> selectAllWithPage(@RequestBody TestQry qry){
            MyPage<TestDto> page = Model2DtoUtil.model2Dto(testService.selectAllWithPage(qry), TestDto.class);
            page.setMessage(getLocalInfo());
            return page;
        }
    
        private String getLocalInfo(){
            StringBuilder sb = new StringBuilder();
            try {
                InetAddress inetAddress = InetAddress.getLocalHost();
                sb.append("server info :")
                        .append("[ip:").append(inetAddress.getHostAddress()).append(",hostname:").append(inetAddress.getHostName())
                        .append("]");
            } catch (UnknownHostException e) {
                e.printStackTrace();
            }
            return sb.toString();
        }
    }

### 3.5. model转化为dto类的工具类 ###

**Model2DtoUtil**  
工具类，封装将model转化为dto类，实现数据层和传输层的解耦

public class Model2DtoUtil { 
    
        /** * 将 MyPage<model> 修改为 MyPage<Dto> * * @param sourcePage * @param cls * @param <T> * @param <K> * @return */
        public static <T, K> MyPage<K> model2Dto(MyPage<T> sourcePage, Class<K> cls) {
            if(sourcePage == null){
                return null;
            }
            Assert.notNull(cls, "cls can't be null!");
    
            MyPage<K> dstPage = new MyPage<K>();
            dstPage.setTotalCount(sourcePage.getTotalCount());
            dstPage.setApiId(sourcePage.getApiId());
            dstPage.setMessage(sourcePage.getMessage());
            dstPage.setCode(sourcePage.getCode());
    
            // list
            List<T> sourceList = sourcePage.getRows();
            List<K> dstList = new ArrayList<K>();
    
            dealListModel2Dto(sourceList, cls, dstList);
            dstPage.setRows(dstList);
            return dstPage;
        }
        private static <T, K> void dealListModel2Dto(List<T> sourceList, Class<K> cls, List<K> dstList) {
            for (T source : sourceList) {
                try {
                    K dst = cls.newInstance();
                    CommonBeanUtils.copyProperties(source, dst);
                    dstList.add(dst);
                } catch (InstantiationException e) {
                    e.printStackTrace();
                    throw new BeanCreationException(e.getMessage());
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                    throw new BeanCreationException(e.getMessage());
                }
            }
        }
    
    }

**CommonBeanUtils**  
BeanUtils工具类：

public class CommonBeanUtils {
        public static void copyProperties(Object source, Object target){
            BeanUtils.copyProperties(source, target);
        }
    }

### 3.6. application.yml ###

pagehelper相关的配置，其它的配置参数见[官网][Link 1]

如果不配置以下参数，则分页机制不会启作用

# 分页配置： https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md
    pagehelper:
        helperDialect: oracle
        reasonable: true
        supportMethodsArguments: true
        params: count=countSql

## 4. Spring Boot集成swagger和测试 ##

为了对测试方便，我们引入swagger

### 4.1. pom.xml ###

<dependency>
        <groupId>io.springfox</groupId>
        <artifactId>springfox-swagger2</artifactId>
        <version>2.7.0</version>
    </dependency>
    
    <dependency>
        <groupId>io.springfox</groupId>
        <artifactId>springfox-swagger-ui</artifactId>
        <version>2.7.0</version>
    </dependency>

### 4.2. @EnableSwagger2 ###

在TestCtl的类上加上@EnableSwagger2

@RestController
    @RequestMapping(value = "/simple")
    @EnableSwagger2
    public class TestCtl { 
        …
    }

### 4.3. 测试 ###

打开地址：[http://127.0.0.1:8080/swagger-ui.html][http_127.0.0.1_8080_swagger-ui.html]  
可以看到当前可用的url接口  
![这里写图片描述][SouthEast]  
测试 /simple/select-all-with-page接口的分页功能，设置查询功能  
![这里写图片描述][SouthEast 1]  
返回结果如下

![这里写图片描述][SouthEast 2]

## 5.代码 ##

上文的详细代码见github代码，请尽量使用tag v0.2，不要使用master，因为master一直在变，不能保证文章中代码和github上的代码一直相同

[Link 1]: https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md
[http_127.0.0.1_8080_swagger-ui.html]: http://127.0.0.1:8080/swagger-ui.html
[SouthEast]: /images/20210520/97301b08b88643a28a60434b71b8a6c5.png
[SouthEast 1]: /images/20210520/d4bdc6f6b20843d0851489d9d2d9860d.png
[SouthEast 2]: /images/20210520/34ecf75f026d4e679a1891e07ed3dc3e.png