Springboot集成mybatis通用Mapper与分页插件PageHelper彻底告别简单的CURD。
插件介绍
通用 Mapper 是一个可以实现任意 MyBatis 通用方法的框架,项目提供了常规的增删改查操作以及 Example
相关的单表操作。通用 Mapper 是为了解决 MyBatis 使用中 90% 的基本操作,PageHelper则提供通用的分页查询功能,使用它们可以很方便的进行开发,可以节省开发人员大量的时间。
通用Mapper的GIT地址: https://gitee.com/free/Mapper
分页插件的GIT地址: https://github.com/pagehelper/Mybatis-PageHelper
项目结构
导入依赖
1 <!-- 通用Mapper插件
2 文档地址:https://gitee.com/free/Mapper/wikis/Home -->
3 <dependency>
4 <groupId>tk.mybatis</groupId>
5 <artifactId>mapper-spring-boot-starter</artifactId>
6 <version>2.1.5</version>
7 </dependency>
8 <!-- 分页插件
9 文档地址:https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md -->
10 <dependency>
11 <groupId>com.github.pagehelper</groupId>
12 <artifactId>pagehelper-spring-boot-starter</artifactId>
13 <version>1.2.10</version>
14 </dependency>
15 <!-- MYSQL包 -->
16 <dependency>
17 <groupId>mysql</groupId>
18 <artifactId>mysql-connector-java</artifactId>
19 </dependency>
20 <!-- 默认就内嵌了Tomcat 容器,如需要更换容器也极其简单-->
21 <dependency>
22 <groupId>org.springframework.boot</groupId>
23 <artifactId>spring-boot-starter-web</artifactId>
24 </dependency>
属性配置
在 application.properties
文件中分别添加上数据库、Mybatis、通用Mapper、PageHelper的属性配置
spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false
spring.datasource.username=root
spring.datasource.password=123456
# 如果想看到mybatis日志需要做如下配置
logging.level.com.carry=DEBUG
######### Mybatis 自身配置 ##########
mybatis.mapper-locations=classpath:com/carry/mapper/*Mapper.xml
mybatis.type-aliases-package=com.carry.entity
# 驼峰命名规范 如:数据库字段是 order_id 那么 实体字段就要写成 orderId
mybatis.configuration.map-underscore-to-camel-case=true
######### 通用Mapper ##########
# 主键自增回写方法,默认值MYSQL,详细说明请看文档
mapper.identity=MYSQL
mapper.mappers=tk.mybatis.mapper.common.Mapper
# 设置 insert 和 update 中,是否判断字符串类型!=''
mapper.not-empty=true
# 枚举按简单类型处理
mapper.enum-as-simple-type=true
######### 分页插件 ##########
pagehelper.helper-dialect=mysql
pagehelper.params=count=countSql
pagehelper.reasonable=false
pagehelper.support-methods-arguments=true
通用Mapper:
- mapper.enum-as-simple-type: 枚举按简单类型处理,如果有枚举字段则需要加上该配置才会做映射
- mapper.not-empty: 设置以后,会去判断 insert 和 update 中符串类型!=''
分页插件:
- pagehelper.reasonable: 分页合理化参数,默认值为false。当该参数设置为 true 时,pageNum<=0 时会查询第一页, pageNum>pages(超过总数时),会查询最后一页。默认false 时,直接根据参数进行查询
- support-methods-arguments: 支持通过 Mapper 接口参数来传递分页参数,默认值false,分页插件会从查询方法的参数值中,自动根据上面 params 配置的字段中取值,查找到合适的值时就会自动分页
注意事项:
由于 mybatis.mapper-locations=classpath:com/carry/mapper/*.xml配置的在java package
中,而Spring Boot
默认只打入java package -> *.java
,所以我们需要给pom.xml
文件添加如下内容
1 <build>
2 <resources>
3 <resource>
4 <directory>src/main/resources</directory>
5 </resource>
6 <resource>
7 <directory>src/main/java</directory>
8 <includes>
9 <include>**/*.xml</include>
10 </includes>
11 <filtering>true</filtering>
12 </resource>
13 </resources>
14 <plugins>
15 <plugin>
16 <groupId>org.springframework.boot</groupId>
17 <artifactId>spring-boot-maven-plugin</artifactId>
18 </plugin>
19 </plugins>
20 </build>
具体编码
1、表结构
创建一张 user
表
CREATE TABLE `user` (
`id` int(8) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
`username` varchar(50) NOT NULL COMMENT '用户名',
`password` varchar(50) NOT NULL COMMENT '密码',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';
2、实体类
1 package com.carry.entity;
2
3 import java.io.Serializable;
4
5 import javax.persistence.GeneratedValue;
6 import javax.persistence.GenerationType;
7 import javax.persistence.Id;
8 import javax.persistence.Table;
9
10 @Table(name = "user")
11 public class User implements Serializable {
12
13 private static final long serialVersionUID = -8057591359892731452L;
14
15 @Id
16 @GeneratedValue(strategy = GenerationType.IDENTITY)
17 private Long id;
18 private String username;
19 private String password;
20 public Long getId() {
21 return id;
22 }
23 public void setId(Long id) {
24 this.id = id;
25 }
26 public String getUsername() {
27 return username;
28 }
29 public void setUsername(String username) {
30 this.username = username;
31 }
32 public String getPassword() {
33 return password;
34 }
35 public void setPassword(String password) {
36 this.password = password;
37 }
38
39
40 public User() {}
41 public User(String username, String password) {
42 super();
43 this.username = username;
44 this.password = password;
45 }
46 }
3、持久层
package com.carry.mapper;
import com.carry.entity.User;
import tk.mybatis.mapper.common.Mapper;
public interface UserMapper extends Mapper<User> {
/**
* 根据用户名统计(假设它是一个很复杂的SQL)
* @param username 用户名
* @return 统计结果
*/
int countByUsername(String username);
}
4、映射文件UserMapper.xml
1 <?xml version="1.0" encoding="UTF-8" ?>
2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
3 <mapper namespace="com.carry.mapper.UserMapper">
4
5 <select id="countByUsername" resultType="java.lang.Integer">
6 SELECT count(1) FROM user WHERE username = #{username}
7 </select>
8 </mapper>
5、启动类
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import tk.mybatis.spring.annotation.MapperScan;
@SpringBootApplication
@MapperScan(basePackages = "com.carry.mapper")
public class SpringbootMapperPagehelperApplication {
public static void main(String[] args) {
SpringApplication.run(SpringbootMapperPagehelperApplication.class, args);
}
}
测试
完成数据访问层接口后,编写一个junit
测试类来检验代码的正确性。
1 package com.carry;
2
3 import org.junit.Test;
4 import org.junit.runner.RunWith;
5 import org.slf4j.Logger;
6 import org.slf4j.LoggerFactory;
7 import org.springframework.beans.factory.annotation.Autowired;
8 import org.springframework.boot.test.context.SpringBootTest;
9 import org.springframework.test.context.junit4.SpringRunner;
10
11 import com.carry.entity.User;
12 import com.carry.mapper.UserMapper;
13 import com.github.pagehelper.PageHelper;
14 import com.github.pagehelper.PageInfo;
15
16 @RunWith(SpringRunner.class)
17 @SpringBootTest
18 public class SpringbootMapperPagehelperApplicationTests {
19
20 private final Logger logger = LoggerFactory.getLogger(this.getClass());
21
22 @Autowired
23 private UserMapper userMapper;
24
25 @Test
26 public void test() {
27 try {
28 final User user1 = new User("u1", "p1");
29 final User user2 = new User("u1", "p2");
30 final User user3 = new User("u3", "p3");
31 userMapper.insertSelective(user1);
32 logger.info("[user1回写主键] - [{}]", user1.getId());
33 userMapper.insertSelective(user2);
34 logger.info("[user2回写主键] - [{}]", user2.getId());
35 userMapper.insertSelective(user3);
36 logger.info("[user3回写主键] - [{}]", user3.getId());
37 final int count = userMapper.countByUsername("u1");
38 logger.info("[调用自己写的SQL] - [{}]", count);
39
40 // TODO 模拟分页
41 for (int i = 0; i < 20; i++) {
42 userMapper.insertSelective(new User("u" + i, "p" + i));
43 }
44 // TODO 分页 + 排序 this.userMapper.selectAll() 这一句就是我们需要写的查询,有了这两款插件无缝切换各种数据库
45 final PageInfo<User> pageInfo = PageHelper.startPage(1, 10).setOrderBy("id desc")
46 .doSelectPageInfo(() -> this.userMapper.selectAll());
47 logger.info("[lambda写法] - [分页信息] - [{}]", pageInfo.toString());
48
49 PageHelper.startPage(1, 10).setOrderBy("id desc");
50 final PageInfo<User> userPageInfo = new PageInfo<>(this.userMapper.selectAll());
51 logger.info("[普通写法] - [{}]", userPageInfo);
52 } catch (Exception e) {
53 logger.error(e.getMessage());
54 }
55 }
56
57 }