springboot2.3+mybatis+pageHelper

系统环境：

操作系统: win10

jdk版本:

openjdk version "12" 2019-03-19
OpenJDK Runtime Environment (build 12+33)
OpenJDK 64-Bit Server VM (build 12+33, mixed mode, sharing)

idea版本：

IntelliJ IDEA 2020.2.1 (Ultimate Edition)
Build #IU-202.6948.69, built on August 25, 2020
Licensed to hello
Subscription is active until September 11, 2021
For educational use only.
Runtime version: 11.0.8+10-b944.31 amd64
VM: OpenJDK 64-Bit Server VM by JetBrains s.r.o.
Windows 10 10.0
GC: ParNew, ConcurrentMarkSweep
Memory: 966M
Cores: 8
Non-Bundled Plugins: Lombook Plugin, com.intellij.kubernetes, training

maven版本：3.6.3

表结构和数据

-- --------------------------------------------------------
-- 主机:                           127.0.0.1
-- 服务器版本:                        8.0.21 - MySQL Community Server - GPL
-- 服务器操作系统:                      Win64
-- HeidiSQL 版本:                  11.0.0.5919
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

-- 导出  表 test.user 结构
DROP TABLE IF EXISTS `user`;
CREATE TABLE IF NOT EXISTS `user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '',
  `update_at` bigint unsigned NOT NULL DEFAULT '0',
  `create_at` bigint NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=108 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 正在导出表  test.user 的数据：~2 rows (大约)
DELETE FROM `user`;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` (`id`, `name`, `update_at`, `create_at`) VALUES
    (19, '这个被我改动过', 1600150902, 1600150901),
    (20, '我插入了一个数据', 0, 1600150902),
    (107, '第三个', 0, 123);
/*!40000 ALTER TABLE `user` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

项目结构:

├─src
│  ├─main
│  │  ├─java
│  │  │  └─com
│  │  │      └─rurjs
│  │  │          └─mybatisxml
│  │  │              │  MybatisxmlApplication.java
│  │  │              │
│  │  │              ├─dao
│  │  │              │      RoleDao.java
│  │  │              │
│  │  │              ├─dto
│  │  │              ├─entity
│  │  │              │      BaseEntity.java
│  │  │              │      RoleEntity.java
│  │  │              │
│  │  │              ├─enums
│  │  │              ├─service
│  │  │              │  │  IUsers.java
│  │  │              │  │
│  │  │              │  └─impl
│  │  │              └─utils
│  │  │                      RjsTimeUtils.java
│  │  │
│  │  └─resources
│  │      │  application.yml
│  │      │
│  │      ├─mapper
│  │      │      RoleMapper.xml
│  │      │
│  │      ├─static
│  │      └─templates
│  └─test
│      └─java
│          └─com
│              └─rurjs
│                  └─mybatisxml
│                          MybatisxmlApplicationTests.java
│                          TestRoleModel.java

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.rurjs</groupId>
    <artifactId>mybatisxml</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mybatisxml</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>14</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper-spring-boot-starter -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.3.0</version>
        </dependency>



        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

application.yml

spring:
  #  profiles:
  #    active: dev
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
    driver-class-name: com.mysql.cj.jdbc.Driver
server:
  port: 8080
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.example.mybatisdemo.entity
  configuration:
    map-underscore-to-camel-case: true

# Logger Config
logging:
  level:
    com.baomidou.mybatisplus.samples: debug
    com.rurjs.mybatisxml: debug

RoleEntity

package com.rurjs.mybatisxml.entity;

public class RoleEntity extends BaseEntity{
}

BaseEntity

package com.rurjs.mybatisxml.entity;

import lombok.Data;

@Data
public class BaseEntity {
    private Long id;
    private String name;
    private Long createAt;
    private Long updateAt;

}

RoleMapper.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.rurjs.mybatisxml.dao.RoleDao">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.rurjs.mybatisxml.entity.RoleEntity">
        <result column="id" property="id" />
        <result column="name" property="name" />
        <result column="create_at" property="createAt" />
        <result column="update_at" property="updateAt" />
    </resultMap>
    <!--表名-->
    <sql id="tName">
        user
    </sql>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id,
        `name`,
        create_at,
        update_at
    </sql>
    <!--根据id查询一个实体-->
    <select id="find" resultMap="BaseResultMap">
        SELECT
            <include refid="Base_Column_List"/>
        FROM
            <include refid="tName"/>
        WHERE
            id=#{id}
    </select>
    <!--根据条件查询一个-->
    <select id="getFirst" resultMap="BaseResultMap">
        SELECT
            <include refid="Base_Column_List"/>
        FROM
            <include refid="tName"/>
        <where>
            <if test="name!=null">
                ,`name`=#{name}
            </if>
        </where>
        LIMIT 1
    </select>
<!--    简单查询-->
    <select id="getList" resultMap="BaseResultMap">
        SELECT
            <include refid="Base_Column_List" />
        FROM
            <include refid="tName"/>
        <where>
            <if test="name!=null">
              and  `name` like concat(#{name},'%')
            </if>
        </where>
    </select>
<!--    分页查询-->
    <!--添加一个(useGeneratedKeys:使用自增id，keyProperty:回写到实体类属性:id-->
    <insert id="insert" useGeneratedKeys="true" keyProperty="id">
        <bind name="timeNow" value='@com.rurjs.mybatisxml.utils.RjsTimeUtils@unixNow()'/>
        INSERT INTO
          <include refid="tName"/>
          (`name`,create_at,update_at) VALUES (#{name},#{timeNow},0)

    </insert>
    <!--更新一个-->
    <update id="update">
        UPDATE
        <include refid="tName"/>
        <set>
            <if test="name!=null">
                ,name=#{name}
            </if>
            ,update_at=${@com.rurjs.mybatisxml.utils.RjsTimeUtils@unixNow()}
        </set>
        WHERE
        id=#{id}
    </update>
    <!--删除一个（根据主键）-->
    <delete id="delete" parameterType="long">
        DELETE FROM
            <include refid="tName"/>
        WHERE
            id=#{id}
    </delete>
</mapper>

RoleDao

package com.rurjs.mybatisxml.dao;

import com.rurjs.mybatisxml.entity.RoleEntity;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface RoleDao {

    public RoleEntity find(Long id);

    public RoleEntity getFirst(RoleEntity entity);

    public List<RoleEntity> getList(RoleEntity entity);

    public int insert(RoleEntity entity);

    public int update(RoleEntity entity);

    public int delete(Long id);

}

单元测试(最后一个测试方法)

package com.rurjs.mybatisxml;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.rurjs.mybatisxml.dao.RoleDao;
import com.rurjs.mybatisxml.entity.RoleEntity;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;

import java.util.List;

@Slf4j
@SpringBootTest
public class TestRoleModel {
    @Autowired
    private RoleDao roleDao;

    @Transactional
    @Test
    void insert(){
        RoleEntity role = new RoleEntity();
        role.setName("我插入了一个数据");
        roleDao.insert(role);
        Assert.notNull(role.getId(),"回写id");
        log.debug(role.toString());
    }

    @Transactional
    @Test
    void update(){
        insert();
        RoleEntity role = roleDao.getFirst(new RoleEntity());
        Assert.notNull(role,"有一条数据");
        role.setName("这个被我改动过");
        roleDao.update(role);

        RoleEntity rq = roleDao.find(role.getId());
        Assert.isTrue(rq.getName().equals("这个被我改动过"),"更新成功");
    }

    @Transactional
    @Test
    void delete(){
        insert();
        RoleEntity role = roleDao.getFirst(null);
        roleDao.delete(role.getId());
        RoleEntity r2 = roleDao.find(role.getId());
        Assert.isNull(r2,"已删除");


    }

    @Transactional
    @Test
    void getList(){
        for (int i = 0 ;i<10;i++){
            RoleEntity role = new RoleEntity();
            role.setName("r"+i);
            roleDao.insert(role);
        }
        List<RoleEntity> list = roleDao.getList(null);
        Assert.notNull(list,"插入成功");
        RoleEntity roleQuery = new RoleEntity();roleQuery.setName("r");
        List<RoleEntity> list1 = roleDao.getList(roleQuery);
        Assert.isTrue(list1.size()==10,"插入10条");
    }
    @Transactional
    @Test
    void getListByPage(){
        PageHelper.startPage(2,2);
        PageInfo<RoleEntity> pageInfo = new PageInfo<>(roleDao.getList(null));
        log.debug(pageInfo.toString());
    }



}