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