01-FastStart
- MyBatis-Plus（简称 MP）是MyBatis增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生
- 官网地址
1. idea_module
2. threshold_eg
1. DB
-- auto-generated definition
create table mp_user
(
id bigint not null comment '主键ID' primary key,
name varchar(30) null comment '姓名',
age int null comment '年龄',
email varchar(50) null comment '邮箱',
is_del tinyint(1) null comment '是否删除'
);
INSERT INTO mp_user (id, name, age, email, is_del) VALUES (1, 'Jone', 18, 'test1@baomidou.com', 0);
INSERT INTO mp_user (id, name, age, email, is_del) VALUES (2, 'Jack', 20, 'test2@baomidou.com', 0);
INSERT INTO mp_user (id, name, age, email, is_del) VALUES (3, 'Tom', 28, 'test3@baomidou.com', 0);
INSERT INTO mp_user (id, name, age, email, is_del) VALUES (4, 'Sandy', 21, 'test4@baomidou.com', 0);
INSERT INTO mp_user (id, name, age, email, is_del) VALUES (5, 'Billie', 24, 'test5@baomidou.com', 0);
2. 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">
<parent>
<artifactId>mca_proj</artifactId>
<groupId>com.listao</groupId>
<version>1.0-SNAPSHOT</version>
</parent>
<modelVersion>4.0.0</modelVersion>
<artifactId>mybatis_plus</artifactId>
<name>mybatis_plus</name>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<version>2.7.4</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.24</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.22</version>
</dependency>
<!-- code_generator-->
<dependency>
<groupId>org.freemarker</groupId>
<artifactId>freemarker</artifactId>
<version>2.3.28</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.5.3</version>
</dependency>
<!-- spring-boot-starter-web 的依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.7.4</version>
</dependency>
<!-- 引入MyBatisPlus的依赖 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.1</version>
</dependency>
<!-- 数据库使用MySQL数据库 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.16</version>
</dependency>
<!-- 数据库连接池Druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.14</version>
</dependency>
</dependencies>
</project>
3. application.yml
- 数据源
- 指定日志输出
spring:
datasource:
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://ip:port/mybatis_plus?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
username: root
password: xxxxxxxx
type: com.alibaba.druid.pool.DruidDataSource
mybatis-plus:
configuration:
# 指定日志输出
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
global-config:
db-config:
# 操作表默认前缀
# table-prefix: mp_
# 主键策略
id-type: assign_id
# 逻辑删除配置
# logic-delete-field: is_del
# logic-delete-value: 1
# logic-not-delete-value: 0
4. po
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@TableName("mp_user")
public class User {
/**
* 主键ID
*/
@TableId("id")
private Long id;
/**
* 姓名
*/
@TableField("name")
private String name;
/**
* 年龄
*/
@TableField("age")
private Integer age;
/**
* 邮箱
*/
@TableField("email")
private String email;
/**
* 是否删除
*/
@TableLogic(value = "0", delval = "1")
private Byte isDel;
}
5. mapper
// dao层接口，extends BaseMapper<User>。不再写UserMappr.xml
// 拓展仍需写UserMappr.xml
public interface UserMapper extends BaseMapper<User> {
IPage<User> selPage(IPage<User> page, @Param(Constants.WRAPPER) LambdaQueryWrapper<UserDto> wrapper);
}
6. @MapperScan
// @MapperScan("com.listao.mybatis_plus.mapper")
@SpringBootApplication
public class MPApplication {
public static void main(String[] args) {
SpringApplication.run(MPApplication.class, args);
}
}
3. CRUD
1. MapperAPI
@SpringBootTest
class MapperAPI {
@Resource
private UserMapper userMapper;
@Test
public void insert() {
// 雪花算法生成id
User user = User.builder()
.name("ooxx")
.age(1)
.email("xx@qq.com").build();
// INSERT INTO user ( id, name, age, email ) VALUES ( 1566723971280293890, 'ooxx', 1, 'xx@qq.com' );
int insert = userMapper.insert(user);
System.out.println("insert = " + insert);
}
@Test
public void update() {
// 子类也是可以的
// UserDto user = new UserDto();
// user.setId(1L);
// user.setName("listao");
User user = User.builder()
.id(1566713907920289793L)
.name("ooxx")
.age(1)
.email("xx@qq.com").build();
// UPDATE user SET name='ooxx', age=1, email='xx@qq.com' WHERE id=1566713907920289793;
int updId = userMapper.updateById(user);
System.out.println("updId = " + updId);
}
@Test
public void delete() {
int row;
// DELETE FROM user WHERE id=1566713907920289793;
row = userMapper.deleteById(1566713907920289793L);
HashMap<String, Object> map = new HashMap<>();
map.put("id", 1);
map.put("name", "ooxx");
// DELETE FROM user WHERE name = 'ooxx' AND id = 1;
row = userMapper.deleteByMap(map);
// DELETE FROM user WHERE id IN ( 1 , 2 , 3 );
row = userMapper.deleteBatchIds(Arrays.asList(1L, 2L, 3L));
System.out.println("row = " + row);
}
@Test
public void select_Entity() {
// SELECT id,name,age,email,is_del FROM mp_user WHERE id=123 AND name='ooxx';
QueryWrapper<User> wrapper = new QueryWrapper<>();
User ooxx = User.builder()
.id(123L)
.name("ooxx")
.build();
wrapper.setEntity(ooxx);
List<User> select_Entity = userMapper.selectList(wrapper);
}
}
2. WrapperAPI
@Slf4j
@SpringBootTest
class WrapperAPI {
@Resource
private UserMapper mapper;
/**
* 1. 姓名包含o，年龄 > 20 and 邮箱 is not null
* 2. 排序：年龄升序，id降序
*/
@Test
void selectList() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
/*
select id, name, age, email, is_del
from mp_user
where (name like '%o%' and age > 20 and email is not null)
order by age asc, id desc;
*/
wrapper.likeRight("name", "o")
.gt("age", 20)
.isNotNull("email")
.orderByAsc("age")
.orderByDesc("id");
List<User> users = mapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* 删除年龄 < 18 数据
*/
@Test
void delete() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
// delete from mp_user where (age < 18);
wrapper.lt("age", 18);
int row = mapper.delete(wrapper);
System.out.println(row);
}
@Test
void lambda() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
// 1. where name like '%o%' or age > 23 and email is null;
// 2. where name like '%o%' or (age > 23 and email is null);
wrapper.like("name", "o")
.or().gt("age", 23)
.isNull("email");
mapper.selectList(wrapper);
wrapper.clear();
// 3. where name like ('%o%' or age > 23) and email is null;
wrapper.and((item) -> item.like("name", "o")
.gt("age", 23))
.isNull("email");
mapper.selectList(wrapper);
}
/**
* 1. 查询特定字段
* 2. 返回map
*/
@Test
void sel_map() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
// select id,name,age from mp_user where (age >= 49);
wrapper.ge("age", 49)
.select("id", "name", "age");
List<User> users = mapper.selectList(wrapper);
users.forEach(System.out::println);
List<Map<String, Object>> maps = mapper.selectMaps(wrapper);
maps.forEach(System.out::println);
}
/**
* 子查询
*/
@Test
void inSql() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
// select id,name from mp_user where (id in (select id from mp_user where id < 6 ));
wrapper.inSql("id", "select id from mp_user where id < 6 ")
.select("id", "name");
List<Map<String, Object>> maps = mapper.selectMaps(wrapper);
maps.forEach(System.out::println);
}
@Test
void update() {
UpdateWrapper<User> wrapper = new UpdateWrapper<>();
// update mp_user set age=33,email='xxx@qq.com' where (name = 'tom');
wrapper.set("age", 33)
.set("email", "xxx@qq.com")
.eq("name", "Tom");
mapper.update(null, wrapper);
wrapper.clear();
// update mp_user set name='username', age=100 where (id = 1);
wrapper.eq("id", 1);
User user = User.builder()
.name("userName")
.age(100).build();
mapper.update(user, wrapper);
}
@Test
void dynamic_sql() {
String name = "Tom";
Integer age = null;
String email = null;
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq(StrUtil.isNotBlank(name), "name", name)
.eq(age != null && age > 0, "age", age)
.eq(StrUtil.isNotBlank(email), "email", email);
List<User> users = mapper.selectList(wrapper);
users.forEach(System.out::println);
}
/**
* 1. 增加MyBatisPlusConfig
* 2. 注释启动类@MapperScan
*/
@Test
void page() {
Page<User> page =
// new Page<>(1, 5);
PageDTO.of(2, 3);
mapper.selectPage(page, null);
log.error("selectPage: \n{}", JSONUtil.toJsonPrettyStr(page));
// 自定义sql分页
LambdaQueryWrapper<UserDto> param = new LambdaQueryWrapper<>();
// select * from mp_user WHERE (name >= 'a') LIMIT 3,3;
param.ge(User::getName, "a");
mapper.selPage(page, param);
log.error("selPage: \n{}", JSONUtil.toJsonPrettyStr(page));
page.convert(o -> {
o.setName("ooxx");
return o;
});
log.error("selPage: \n{}", JSONUtil.toJsonPrettyStr(page));
}
@Test
public void LambdaQueryWrapper() {
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.ge(User::getId, "1");
List<User> users = mapper.selectList(wrapper);
System.out.println("users = " + users);
}
/**
* 查询java_bean里存在，DB里不存在的字段
* @TableField(exist = false)
* private String ooxx;
*/
@Test
public void selNoExist1() {
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.ge(User::getId, "1");
List<User> users = mapper.selectList(wrapper);
System.out.println("users = " + users);
}
@Test
public void selNoExist2() {
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.ge(User::getId, "1")
.select(User.class, o -> !o.getColumn().equals("ooxx"));
List<User> users = mapper.selectList(wrapper);
System.out.println("users = " + users);
}
@Test
public void lambdaUpdateWrapper1() {
LambdaUpdateWrapper<User> wrapper = new LambdaUpdateWrapper<>();
wrapper.eq(User::getId, "6")
.set(User::getName, "listao");
// UPDATE mp_user SET name=? WHERE is_del=0 AND (id = ?)
mapper.update(null, wrapper);
// 清空wrapper里的，set()和where。foreach()
wrapper.clear();
}
@Test
public void lambdaUpdateWrapper2() {
LambdaUpdateWrapper<User> wrapper = new LambdaUpdateWrapper<>();
wrapper.eq(User::getId, "6");
User upd = User.builder().name("listao").build();
// UPDATE mp_user SET name=? WHERE is_del=0 AND (id = ?)
mapper.update(upd, wrapper);
}
@Test
public void lambdaUpdateChainWrapper() {
LambdaUpdateChainWrapper<User> wrapper = new LambdaUpdateChainWrapper<>(mapper);
// UPDATE mp_user SET name=? WHERE is_del=0 AND (id = ?)
boolean update = wrapper.eq(User::getId, "6")
.set(User::getName, "LambdaUpdateChainWrapper")
.update();
System.out.println("update = " + update);
// 这个方法无法使用，LambdaUpdateWrapper支持
wrapper.clear();
}
}
3. ServiceAPI
public interface IUserSv extends IService<User> {
}
@ActiveProfiles("local")
@SpringBootTest
class ServiceAPI {
@Autowired
private IUserSv iUserSvI;
/**
* 单条get
* 多条list
*/
@Test
public void select() {
// SELECT id,name,age,email FROM user WHERE id=1;
User byId = iUserSvI.getById(1);
// SELECT id,name,age,email FROM user;
List<User> list = iUserSvI.list();
}
@Test
public void save() {
ArrayList<User> users = new ArrayList<>();
for (int i = 0; i < 50; i++) {
users.add(
User.builder().name("name" + i).age(i).build()
);
}
boolean saveBatch = iUserSvI.saveBatch(users, 10);
}
@Test
public void table_logic() {
boolean b = iUserSvI.removeById(4);
System.out.println("b = " + b);
}
}
4. Annotation
1. @TableName
@TableName("mp_user")
1. 全局配置
mybatis-plus:
configuration:
# 指定日志输出
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
global-config:
db-config:
# 操作表默认前缀
table-prefix: mp_
2. @TableId
/*
* 1. primary为id，可省略@TableId；primary不为id，@TableId表明字段为primary
* 2. class属性和DB字段不一致，value进行mapping
* 3. IdType
*/
@TableId(value = "id", type = IdType.NONE)
private Long id;
@Getter
public enum IdType {
/**
* 数据库ID自增
* <p>该类型请确保数据库设置了 ID自增 否则无效</p>
*/
AUTO(0),
/**
* 该类型为未设置主键类型(注解里等于跟随全局,全局里约等于 INPUT)
*/
NONE(1),
/**
* 用户输入ID
* <p>该类型可以通过自己注册自动填充插件进行填充</p>
*/
INPUT(2),
/* 以下3种类型、只有当插入对象ID 为空，才自动填充。 */
/**
* 分配ID (主键类型为number或string）,
* 默认实现类 {@link com.baomidou.mybatisplus.core.incrementer.DefaultIdentifierGenerator}(雪花算法)
*
* @since 3.3.0
*/
ASSIGN_ID(3),
/**
* 分配UUID (主键类型为 string)
* 默认实现类 {@link com.baomidou.mybatisplus.core.incrementer.DefaultIdentifierGenerator}(UUID.replace("-",""))
*/
ASSIGN_UUID(4);
}
1. 全局配置
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
global-config:
db-config:
table-prefix: mp_
# 主键策略
id-type: assign_id
3. @TableField
// DB是user_name，class是userName，MyBatisPlus会自动驼峰转换
@TableField(value = "name")
private String name;
1. select排除字段
- Obj中的字段DB中不存在
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: java.sql.SQLSyntaxErrorException: Unknown column 'ooxx' in 'field list'
### The error may exist in com/listao/mybatis_plus/mapper/UserMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT id,name,age,email,is_del,ooxx FROM mp_user WHERE is_del=0 AND (id >= ?)
### Cause: java.sql.SQLSyntaxErrorException: Unknown column 'ooxx' in 'field list'
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Unknown column 'ooxx' in 'field list'
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
// ......
Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'ooxx' in 'field list'
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
2. exist = false
- 查询的po属性增加
@TableField(exist = false)注解。表明属性不是DB中的字段
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@TableName("mp_user")
public class User {
/**
* DB中不存在的字段
*/
@TableField(exist = false)
private String ooxx;
}
@Test
public void selNoExist1() {
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.ge(User::getId, "1");
List<User> users = mapper.selectList(wrapper);
System.out.println("users = " + users);
}
3. select_lambda
@Test
public void selNoExist2() {
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.ge(User::getId, "1")
.select(User.class,
o -> !o.getColumn().equals("ooxx")
);
List<User> users = mapper.selectList(wrapper);
System.out.println("users = " + users);
}
4. @TableLogic
完成逻辑删除操作
- 逻辑删除：假删除，将表中《是否删除字段》的状态修改为”删除状态“
- 物理删除：真实删除，将表中对应数据删除
// UPDATE mp_user SET is_del=1 WHERE id=4 AND is_del=0;
@TableLogic(value = "0", delval = "1")
private Byte isDel;
1. 全局配置
mybatis-plus:
global-config:
db-config:
# 逻辑删除配置
logic-delete-field: is_del
logic-delete-value: 1
logic-not-delete-value: 0
5. 代码生成器
相比MybatisX优势
tinyint(1) => Booleanbit => Booleantinyint(4) => Byte
1. pom.xml
<!-- code_generator -->
<dependency>
<groupId>org.freemarker</groupId>
<artifactId>freemarker</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.5.3</version>
</dependency>
2. CodeGenerator
public class CodeGenerator {
public static void main(String[] args) {
// 要逆向的tables
List<String> tables = CollUtil.newArrayList("mp_ooxx");
// 项目modules路径
String finalProjectPath = "/Users/listao/mca_proj/mybatis_plus";
FastAutoGenerator.create(
"jdbc:mysql://ip:port/mybatis_plus?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false"
, "root"
, "******")
.globalConfig(builder ->
builder
// 关闭生成代码打开目录
.disableOpenDir()
// 设置作者
.author("listao")
//.enableSwagger() 关闭swagger
// 指定输出目录
.outputDir(finalProjectPath + "/src/main/java")
)
.packageConfig(builder ->
builder
// 设置父包名
.parent("com.listao")
// 设置父包模块名
.moduleName("mybatis_plus")
// 实体类包
.entity("model.po")
// 设置mapperXml生成路径
.pathInfo(Collections.singletonMap(OutputFile.xml, finalProjectPath + "/src/main/resources/mapper"))
)
.strategyConfig(builder ->
builder
// 设置需要生成的表名
.addInclude(tables)
// 设置过滤表前缀
.addTablePrefix("mp_", "c_")
// entity
.entityBuilder()
.enableFileOverride()
.enableLombok()
.enableTableFieldAnnotation()
.disableSerialVersionUID()
// mapper
.mapperBuilder()
.enableFileOverride()
.enableBaseResultMap()
.enableBaseColumnList()
// service
.serviceBuilder()
.enableFileOverride()
.formatServiceFileName("I%sSv")
.formatServiceImplFileName("%sSvI")
// controller
.controllerBuilder()
.enableFileOverride()
.formatFileName("%sCtl")
.enableRestStyle()
)
// 默认会从src/main/resources/templates目录下加载*.ftl
// .templateConfig(builder -> {
// builder.controller("/templates/controller.java");
// })
// 使用Freemarker引擎模板，默认的是Velocity引擎模板
.templateEngine(new FreemarkerTemplateEngine())
.execute();
}
}
3. Templetes
- generator项目有ftl，默认即用这些模板生成
6. MybatisX
1. Templetes
.meta.xml => controller.ftl默认没有，可自定义添加
<?xml version="1.0" encoding="utf-8" ?>
<templates>
<template>
<property name="configName" value="controller"/>
<property name="configFile" value="controller.ftl"/>
<property name="fileName" value="${domain.fileName}Ctl"/>
<property name="suffix" value=".java"/>
<property name="packageName" value="${domain.basePackage}.controller"/>
<property name="encoding" value="${domain.encoding}"/>
<property name="basePath" value="${domain.basePath}"/>
</template>
<template>
<property name="configName" value="serviceInterface"/>
<property name="configFile" value="serviceInterface.ftl"/>
<property name="fileName" value="I${domain.fileName}Sv"/>
<property name="suffix" value=".java"/>
<property name="packageName" value="${domain.basePackage}.service"/>
<property name="encoding" value="${domain.encoding}"/>
<property name="basePath" value="${domain.basePath}"/>
</template>
<template>
<property name="configName" value="serviceImpl"/>
<property name="configFile" value="serviceImpl.ftl"/>
<property name="fileName" value="${domain.fileName}SvI"/>
<property name="suffix" value=".java"/>
<property name="packageName" value="${domain.basePackage}.service.impl"/>
<property name="encoding" value="${domain.encoding}"/>
<property name="basePath" value="${domain.basePath}"/>
</template>
<template>
<property name="configName" value="mapperInterface"/>
<property name="configFile" value="mapperInterface.ftl"/>
<property name="fileName" value="${domain.fileName}Mapper"/>
<property name="suffix" value=".java"/>
<property name="packageName" value="${domain.basePackage}.mapper"/>
<property name="encoding" value="${domain.encoding}"/>
<property name="basePath" value="${domain.basePath}"/>
</template>
<template>
<property name="configName" value="mapperXml"/>
<property name="configFile" value="mapperXml.ftl"/>
<property name="fileName" value="${domain.fileName}Mapper"/>
<property name="suffix" value=".xml"/>
<property name="packageName" value="mapper"/>
<property name="encoding" value="${domain.encoding}"/>
<property name="basePath" value="src/main/resources"/>
</template>
</templates>
controller.ftl
package ${baseInfo.packageName};
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;
import org.springframework.validation.annotation.Validated;
@Slf4j
@Validated
@RestController
@RequestMapping("/${baseInfo.fileName}")
public class ${baseInfo.fileName} {
@PostMapping(value = "/")
public Object ooxx() {
return null;
}
}
2. ftl
