插件介绍
通用 Mapper 是一个可以实现任意 MyBatis 通用方法的框架,项目提供了常规的增删改查操作以及 Example 相关的单表操作。通用 Mapper 是为了解决 MyBatis 使用中 90% 的基本操作,PageHelper则提供通用的分页查询功能,使用它们可以很方便的进行开发,可以节省开发人员大量的时间。
项目结构
导入依赖
1
3
4 tk.mybatis
5 mapper-spring-boot-starter
6 2.1.5
7
8
10
11 com.github.pagehelper
12 pagehelper-spring-boot-starter
13 1.2.10
14
15
16
17 mysql
18 mysql-connector-java
19
20
21
22 org.springframework.boot
23 spring-boot-starter-web
24
属性配置
在 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
2
3
4 src/main/resources
5
6
7 src/main/java
8
9 **/*.xml
10
11 true
12
13
14
15
16 org.springframework.boot
17 spring-boot-maven-plugin
18
19
20
具体编码
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 packagecom.carry.entity;2
3 importjava.io.Serializable;4
5 importjavax.persistence.GeneratedValue;6 importjavax.persistence.GenerationType;7 importjavax.persistence.Id;8 importjavax.persistence.Table;9
10 @Table(name = "user")11 public class User implementsSerializable {12
13 private static final long serialVersionUID = -8057591359892731452L;14
15 @Id16 @GeneratedValue(strategy =GenerationType.IDENTITY)17 privateLong id;18 privateString username;19 privateString password;20 publicLong getId() {21 returnid;22 }23 public voidsetId(Long id) {24 this.id =id;25 }26 publicString getUsername() {27 returnusername;28 }29 public voidsetUsername(String username) {30 this.username =username;31 }32 publicString getPassword() {33 returnpassword;34 }35 public voidsetPassword(String password) {36 this.password =password;37 }38
39
40 publicUser() {}41 publicUser(String username, String password) {42 super();43 this.username =username;44 this.password =password;45 }46 }
3、持久层
packagecom.carry.mapper;importcom.carry.entity.User;importtk.mybatis.mapper.common.Mapper;
public interface UserMapper extends Mapper{/*** 根据用户名统计(假设它是一个很复杂的SQL)
*@paramusername 用户名
*@return统计结果*/
intcountByUsername(String username);
}
4、映射文件UserMapper.xml
1 <?xml version="1.0" encoding="UTF-8"?>
2
3
4
5
6 SELECT count(1) FROM user WHERE username = #{username}7
8
5、启动类
importorg.springframework.boot.SpringApplication;importorg.springframework.boot.autoconfigure.SpringBootApplication;importtk.mybatis.spring.annotation.MapperScan;
@SpringBootApplication
@MapperScan(basePackages= "com.carry.mapper")public classSpringbootMapperPagehelperApplication {public static void main(String[] args) {
SpringApplication.run(SpringbootMapperPagehelperApplication.class, args);
}
}
测试
完成数据访问层接口后,编写一个junit测试类来检验代码的正确性。
1 packagecom.carry;2
3 importorg.junit.Test;4 importorg.junit.runner.RunWith;5 importorg.slf4j.Logger;6 importorg.slf4j.LoggerFactory;7 importorg.springframework.beans.factory.annotation.Autowired;8 importorg.springframework.boot.test.context.SpringBootTest;9 importorg.springframework.test.context.junit4.SpringRunner;10
11 importcom.carry.entity.User;12 importcom.carry.mapper.UserMapper;13 importcom.github.pagehelper.PageHelper;14 importcom.github.pagehelper.PageInfo;15
16 @RunWith(SpringRunner.class)17 @SpringBootTest18 public classSpringbootMapperPagehelperApplicationTests {19
20 private final Logger logger = LoggerFactory.getLogger(this.getClass());21
22 @Autowired23 privateUserMapper userMapper;24
25 @Test26 public voidtest() {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 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 userPageInfo = new PageInfo<>(this.userMapper.selectAll());51 logger.info("[普通写法] - [{}]", userPageInfo);52 } catch(Exception e) {53 logger.error(e.getMessage());54 }55 }56
57 }