通用mapper集成示例
配合easyCode IDEA插件自动生成xml文件，开发效率能够大大提升
插件介绍
通用 Mapper 是一个可以实现任意 MyBatis 通用方法的框架，项目提供了常规的增删改查操作以及 Example 相关的单表操作。通用 Mapper 是为了解决 MyBatis 使用中 90% 的单表sql编写基本操作，PageHelper则提供通用的分页查询功能，使用它们可以很方便的进行开发，可以节省开发人员大量的时间。
通用Mapper的GIT地址： https://github.com/abel533/Mapper
分页插件的GIT地址： https://github.com/pagehelper/Mybatis-PageHelper
项目结构
导入pom依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://maven.apache.org/POM/4.0.0"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>2.3.0.RELEASE</version>
</parent>
<groupId>org.ailun</groupId>
<artifactId>test</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- 通用mapper -->
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
<version>2.1.5</version>
</dependency>
<!-- 通用分页 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.3.1</version>
</dependency>
</dependencies>
</project>
配置文件
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: root
url: jdbc:mysql://192.168.8.6:3306/znpg?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false
# 通用tk.mapper配置
mapper:
enum-as-simple-type: true
identity: MYSQL
mappers: tk.mybatis.mapper.common.Mapper
not-empty: true
# 通用tk.mapper组件中mybatis配置
mybatis:
mapper-locations: classpath*:mapper/**/*Mapper.xml
# 主要用于xml文件中返回类型时不用写实体的全类名
type-aliases-package: com.ailun.test.entity
configuration:
map-underscore-to-camel-case: true
# 通用分页插件
pagehelper:
helper-dialect: mysql
params: count=countSql
reasonable: false
support-methods-arguments: true
# 控制台sql打印日志配置
logging:
level:
com:
ailun: DEBUG
通用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:mapper/**/Mapper.xml配置的在java package中，而Spring Boot默认只打入java package -> *.java，所以我们需要给pom.xml文件添加如下内容
<build>
<resources>
<resource>
<directory>src/main/resources</directory>
</resource>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
</resources>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</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、实体类
import java.io.Serializable;
public class User implements Serializable {
private static final long serialVersionUID = -8057591359892731452L;
private Long id;
private String username;
private String password;
public User() {}
public User(String username, String password) {
super();
this.username = username;
this.password = password;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
3、持久层
import com.ailun.entity.User;
import tk.mybatis.mapper.common.Mapper;
public interface UserMapper extends Mapper<User> {
/**
* 根据用户名统计（假设它是一个很复杂的SQL）
* @param username 用户名
* @return 统计结果
*/
int countByUsername(String username);
}
4、mapper方法映射文件UserMapper.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.ailun.mapper.UserMapper">
<select id="countByUsername" resultType="java.lang.Integer">
SELECT count(1) FROM user WHERE username = #{username}
</select>
</mapper>
5、启动类
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import tk.mybatis.spring.annotation.MapperScan;
/**
* @author JHL
*/
@SpringBootApplication
@MapperScan(basePackages = "com.ailun.test.mapper")
public class App {
public static void main(String[] args) {
SpringApplication.run(App.class, args);
}
}
6.功能测试
完成数据访问层接口后，编写一个junit测试类来检验代码的正确性。
import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import com.ailun.entity.User;
import com.ailun.mapper.UserMapper;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
@RunWith(SpringRunner.class)
@SpringBootTest
public class Test {
private final Logger logger = LoggerFactory.getLogger(this.getClass());
@Autowired
private UserMapper userMapper;
@Test
public void test() {
try {
final User user1 = new User("u1", "p1");
final User user2 = new User("u1", "p2");
final User user3 = new User("u3", "p3");
userMapper.insertSelective(user1);
logger.info("[user1回写主键] - [{}]", user1.getId());
userMapper.insertSelective(user2);
logger.info("[user2回写主键] - [{}]", user2.getId());
userMapper.insertSelective(user3);
logger.info("[user3回写主键] - [{}]", user3.getId());
final int count = userMapper.countByUsername("u1");
logger.info("[调用自己写的SQL] - [{}]", count);
// TODO 模拟分页
for (int i = 0; i < 20; i++) {
userMapper.insertSelective(new User("u" + i, "p" + i));
}
// TODO 分页 + 排序 this.userMapper.selectAll() 这一句就是我们需要写的查询，有了这两款插件无缝切换各种数据库
final PageInfo<User> pageInfo = PageHelper.startPage(1, 10).setOrderBy("id desc")
.doSelectPageInfo(() -> this.userMapper.selectAll());
logger.info("[lambda写法] - [分页信息] - [{}]", pageInfo.toString());
PageHelper.startPage(1, 10).setOrderBy("id desc");
final PageInfo<User> userPageInfo = new PageInfo<>(this.userMapper.selectAll());
logger.info("[普通写法] - [{}]", userPageInfo);
} catch (Exception e) {
logger.error(e.getMessage());
}
}
}
