目录
1:什么是mybatisPlus
1.1:mybatisPlus介绍
mybatisPlus官网:MyBatis-Plus
mybatisPlus是一个mybatis的增强工具,只做增强,不做改变。目的是为了简化开发代码,提高效率而生的。
1.2:mybatisPlus特性和框架结构
无侵入:只做增强不做改变,引入它不会对现有工程产生影响,如丝般顺滑
损耗小:启动即会自动注入基本 CURD,性能基本无损耗,直接面向对象操作
强大的 CRUD 操作:内置通用 Mapper、通用 Service,仅仅通过少量配置即可实现单表大部分 CRUD 操作,更有强大的条件构造器,满足各类使用需求
支持 Lambda 形式调用:通过 Lambda 表达式,方便的编写各类查询条件,无需再担心字段写错
支持主键自动生成:支持多达 4 种主键策略(内含分布式唯一 ID 生成器 - Sequence),可自由配置,完美解决主键问题
内置代码生成器:采用代码或者 Maven 插件可快速生成 Mapper 、 Model 、 Service 、 Controller 层代码,支持模板引擎,更有超多自定义配置等您来使用
内置分页插件:基于 MyBatis 物理分页,开发者无需关心具体操作,配置好插件之后,写分页等同于普通 List 查询
内置性能分析插件:可输出 SQL 语句以及其执行时间,建议开发测试时启用该功能,能快速揪出慢查询
内置全局拦截插件:提供全表 delete 、 update 操作智能分析阻断,也可自定义拦截规则,预防误操作
2:使用mybatisPlus实现增删改查
2.1:通用配置
mapper需要继承BaseMapper,BaseMapper是mp的核心mapper,里边有很多crud的方法,可以给我们直接调用。
第一步:配置pom文件依赖
<!--MybatisPlus的jar 3.0基于jdk8-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.3.1</version>
</dependency>
<!--直接使用druid的starter-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.16</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.33</version>
</dependency>
<!-- MybatisPlus分页插件注解-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-extension</artifactId>
<version>3.5.3.1</version>
<scope>compile</scope>
</dependency>
<!-- mybatisPlus多数据源依赖-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.6.1</version>
</dependency>
第二步:写接口继承baseMapper用来简化开发
//@Mapper 这里用注解 在启动类中 添加注解扫描解决
//继承BaseMapper 泛型必须写对否则报错
@Mapper
public interface UserMapper extends BaseMapper<A_User> {
@SuppressWarnings("MybatisXMapperMethodInspection")
@DS("slave")
//@DS("master")
//@DS 主库从库切换
//这个方法是自定义方法,需要在xml中自定义查询sql
Map<String, Object> selectMapById(@Param("id") int id);
//这个方法是自定义方法,需要在xml中自定义查询sql
Page<A_User> selectAllByPage(@Param("page") Page page, @Param("age") int age);
}
<?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.thit.springboot06_mybatisplus.mapper.UserMapper">
<select id="selectMapById" resultType="java.util.Map">
select * from user where uid=#{id}
</select>
<select id="selectAllByPage" resultType="com.thit.springboot06_mybatisplus.pojo.A_User">
select * from user where age>#{age}
</select>
</mapper>
第三步:创建实体类
表结构如下
@Data
@TableName(value = "user") //用于表名和实体不一致 这里设置具体的表名
public class A_User {
//TableI指定这个字段ids是主键 主键默认值是id 绑定数据库的uid字段
//默认主键自增
//type =IdType.AUTO 主键必须设置自增 获取插入的主键值
//type =IdType.NONE 默认值 获取不到主键
//type =IdType.ASSIGN_UUID uuid不能是Int类型
//IdType.ASSIGN_ID 雪花算法设置生成id 主键不能为递增
@TableId(value = "uid",type =IdType.AUTO )
private int ids;
//TableField指定这个字段对应表的字段name
@TableField(value = "name")
private String names;
//insertStrategy = FieldStrategy.NOT_NULL 插入是该字段不为空的动态sql
//select = false 查询的时候不查这个字段
@TableField(value = "address",insertStrategy = FieldStrategy.NOT_NULL,select = true)
private String addresss;
//多余字段 SELECT uid AS ids,name AS names,address AS addresss,age FROM user
//数据库没有age 查询报错
private Integer age;
//数据库没有age 查询报错
private SexEnum sex;
//数据库没有age 查询报错
@TableField(exist = false)
private String blood;
//value = "2",delval = "1"
//value 默认值2 delval删除时候替换值1 查询的时候 WHERE is_delete=2
@TableLogic(value = "2",delval = "1")
private int isDelete;
}
2.2:增删改查代码测试(BaseMapper实现)
1:添加方法(继承BaseMapper实现)
/**
* 添加数据到User表中 主键是自增的
* ==> Preparing: INSERT INTO user ( id, name, address ) VALUES ( ?, ?, ? )
* ==> Parameters: 0(Integer), mp1(String), 北京1(String)
* <== Updates: 1
*/
@Autowired
UserMapper userMapper;
@Test
void addUser() {
A_User a_user=new A_User();
a_user.setNames("mp4");
a_user.setAddresss("测试");
//调用userMapper 继承的baseMapper方法
int insert = userMapper.insert(a_user);
System.out.println("插入结果:"+insert);
System.out.println("插入结果id:"+a_user.getIds());
}
2:查询方法 (继承BaseMapper实现)
/**
* 查询全部数据
* SELECT uid AS ids,name AS names,address AS addresss FROM user
*/
@Test
void selectALL() {
//调用userMapper 继承的baseMapper方法
List<A_User> users = userMapper.selectList(null);
for (A_User user : users) {
System.out.println(user);
}
}
/**
* 根据id查询
==> Preparing: SELECT id,name,address FROM user WHERE id=?
==> Parameters: 2(Integer)
<== Columns: id, name, address
<== Row: 2, 李四2, 主库1
<== Total: 1
* mvn dependency:resolve -Dclassifier=sources
*/
@Test
void selectById() {
//调用userMapper 继承的baseMapper方法
A_User a_user = userMapper.selectById(2);
System.out.println(a_user);
//SELECT COUNT( 1 ) FROM user
long integer = userMapper.selectCount(null);
System.out.println("查询行数:"+integer);
}
/**
* 根据address name
* SELECT id,name,address FROM user WHERE address = ? AND name = ?
*/
@Test
void selectByMap() {
System.out.println("map查询");
Map map=new HashMap();
// map.put("id",1);
map.put("name","张三1");
map.put("address","主库1");
//调用userMapper 继承的baseMapper方法
List<A_User> users = userMapper.selectByMap(map);
for (A_User user : users) {
System.out.println("查询结果:"+user);
}
}
/**
* 根据id集合批量查询
==> Preparing: SELECT id,name,address FROM user WHERE id IN ( ? , ? )
==> Parameters: 1(Integer), 2(Integer)
<== Columns: id, name, address
<== Row: 1, 张三1, 主库1
<== Row: 2, 李四2, 主库1
<== Total: 2
*/
@Test
void selectBatchIds() {
List idList = Arrays.asList(1,2);
//调用userMapper 继承的baseMapper方法
List<A_User> users = userMapper.selectBatchIds(idList);
users.forEach(System.out::println);
}
3:修改方法(继承BaseMapper实现)
@Autowired
User1Mapper userMapper;
/**
* 根据id修改 实体中的其他属性
==> Preparing: UPDATE user1 SET name=? WHERE id=?
==> Parameters: 修改1(String), 1689257089960165377(Long)
<== Updates: 1
*/
@Test
void updateById() {
User1 user1=new User1();
user1.setUid(1689585507788529665L);
user1.setName("修改2");
//继承baseMapper实现
int i = userMapper.updateById(user1);
System.out.println("根据id修改:"+i);
}
4:删除方法
@Autowired
User1Mapper User1Mapper;
@Autowired
UserMapper userMapper;
/**
* 根据id删除
==> Preparing: DELETE FROM user1 WHERE id=?
==> Parameters: 1(Integer)
<== Updates: 1
*/
@Test
void deleteById() {
int i = User1Mapper.deleteById(1689640102061576194L);
System.out.println("根据id删除:"+i);
}
/**
* 根据map删除
==> Preparing: DELETE FROM user1 WHERE name = ? AND id = ?
==> Parameters: 北京(String), 1689254915574272002(Long)
<== Updates: 1
*/
@Test
void deleteMap() {
Map map=new HashMap();
map.put("uid",1689637782397550593L);
map.put("name","北京");
int i = User1Mapper.deleteByMap(map);
System.out.println("根据map删除:"+i);
}
/**
* 批量删除
==> Preparing: DELETE FROM user1 WHERE id IN ( ? , ? , ? )
==> Parameters: 0(Integer), 2(Integer), 1689256955377532930(Long)
<== Updates: 3
*/
@Test
void deleteBatchIds() {
List<? extends Number> numbers = Arrays.asList(1, 3, 1689256955377532930L);
int i = User1Mapper.deleteBatchIds(numbers);
System.out.println("批量删除:"+i);
}
/**
* 逻辑删除
* UPDATE user SET is_delete=1 WHERE uid=? AND is_delete=0
* 修改状态值
*/
@Test
void deleteBatchId1() {
int i = userMapper.deleteById(28);
System.out.println("批量删除:"+i);
}
2.3:增删改查代码测试(自定义实现)
以上的都是不需要自己写配置文件写sql,baseMapper中的方法就可以使用。
但是碰到需要自己写sql的时候就需要自己编写mapper.xml中的sql
@Autowired
UserMapper userMapper;
/**
* 自定义查询 配置mapper.xml文件
==> Preparing: select * from user where id=?
==> Parameters: 1(Integer)
<== Columns: id, name, address
<== Row: 1, 张三1, 主库1
<== Total: 1
*/
@Test
void selectMapById() {
//这个方法是自定义的查询方法
Map<String, Object> map = userMapper.selectMapById(1);
System.out.println(map);
}
2.4:在BaseMapper扩展Wapper包装类
在baseMapper中,有很多的方法需要传递参数包装类,这些包装类扩展的baseMapper的查询条件
@SpringBootTest
class selectWrapperTests {
@Autowired
UserMapper userMapper;
/**
* 查询条件构造器
* ==> Preparing: SELECT uid AS ids,name AS names,is_delete FROM user
* WHERE is_delete=2 AND (name LIKE ? AND uid BETWEEN ? AND ? AND address IS NOT NULL)
* ==> Parameters: %批%(String), 2(Integer), 100(Integer)
* <== Total: 0
*/
@Test
void selectALL() {
//selectList 参数是QueryWrapper 泛型跟实体类一致
QueryWrapper<A_User> queryWrapper = new QueryWrapper();
queryWrapper
.like("name", "批")
.between("uid", 2, 100).isNotNull("address");
List<A_User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
/**
* 查询条件构造器
* ==> Preparing: SELECT uid AS ids,name AS names,address AS addresss,age,is_delete FROM user
* WHERE is_delete=2 ORDER BY age DESC,uid ASC
*/
@Test
void selectALL1() {
//selectList 参数是QueryWrapper 泛型跟实体类一致
QueryWrapper<A_User> queryWrapper = new QueryWrapper();
queryWrapper
.orderByDesc("age").orderByAsc("uid");
List<A_User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
/**
* 查询指定字段
* SELECT uid,name,age FROM user WHERE is_delete=2
*/
@Test
void selectALL2() {
//selectList 参数是QueryWrapper 泛型跟实体类一致
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.select("uid", "name", "age");
List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
maps.forEach(System.out::println);
}
/**
* 查询指定字段
* SELECT uid,name,age FROM user WHERE is_delete=2 AND
* (age IN (select age from user where age<100))
*/
@Test
void selectALL3() {
//selectList 参数是QueryWrapper 泛型跟实体类一致
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.select("uid", "name", "age", "address");
queryWrapper.inSql("age", "select age from user where age<100");
List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
maps.forEach(System.out::println);
}
/**
* 条件装配
* SELECT uid AS ids,name AS names,address AS addresss,age,is_delete FROM user
* WHERE is_delete=2 当name是null 或者""的时候 不查询
*/
@Test
void selectALL4() {
//selectList 参数是QueryWrapper 泛型跟实体类一致
QueryWrapper queryWrapper = new QueryWrapper();
String name = "";
Integer startAge = 20;
Integer endAge = 10;
queryWrapper.like(StringUtils.isNotBlank(name), "name", name);
queryWrapper.ge(startAge != null, "age", startAge);
queryWrapper.le(endAge != null, "age", endAge);
List list = userMapper.selectList(queryWrapper);
list.forEach(System.out::println);
}
/**
* 条件装配
* SELECT uid AS ids,name AS names,address AS addresss,age,is_delete FROM user
* WHERE is_delete=2 当name是null 或者""的时候 不查询
*/
@Test
void selectALL5() {
//selectList 参数是QueryWrapper 泛型跟实体类一致
LambdaQueryWrapper<A_User> queryWrapper = new LambdaQueryWrapper();
String name = "张三";
//防止写错字段A_User::getNames
queryWrapper.like(StringUtils.isNotBlank(name), A_User::getNames, name);
List list = userMapper.selectList(queryWrapper);
list.forEach(System.out::println);
}
}
2.5:在BaseMapper扩展Service接口
1:接口继承IService 实现类继承ServiceImpl
//接口需要继承 IService<A_User>
public interface UserService extends IService<A_User> {
List<A_User> selectAll();
}
/**
* 泛型必须要写对
* 这里实现自定义的接口 继承mp的ServiceImpl<UserMapper,A_User>
* 能使用自定的方法 也能使用
*/
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper,A_User> implements UserService {
//因为是接口提示注入不了
//实际上运行时扫描mapper接口生成了wapper包装器类 可以以注入 这里不用管
@Autowired
UserMapper userMapper;
//自定义方法
@Override
public List<A_User> selectAll() {
List list = userMapper.selectList(null);
return list;
}
}
2:代码测试
@SpringBootTest
public class 扩展ServiceTest {
@Autowired
UserServiceImpl userService;
/**
* 既能使用自定义的方法
* 也能使用ServiceImpl的方法
*
* ==> Preparing: SELECT COUNT( 1 ) FROM user
* ==> Parameters:
* <== Columns: COUNT( 1 )
* <== Row: 11
* <== Total: 1
*/
@Test
void count() {
int count = (int) userService.count();
//ServiceImpl类的方法
boolean b = userService.removeById(2);
System.out.println("查询行数:"+count);
System.out.println(b);
}
/**
* saveBatch
==> Preparing: INSERT INTO user ( id, name, address ) VALUES ( ?, ?, ? )
==> Parameters: 0(Integer), 批量1(String), 洛阳(String)
==> Parameters: 0(Integer), 批量1(String), 洛阳(String)
批量添加数据:true
*/
@Test
void saveBatch() {
List list=new ArrayList();
A_User user=new A_User();
// user.setIds(48);
user.setNames("批量1");
user.setAddresss("洛阳");
A_User user1=new A_User();
// user1.setIds(49);
user1.setNames("批量1");
user1.setAddresss("洛阳");
list.add(user);
list.add(user1);
//ServiceImpl类的方法
boolean b = userService.saveBatch(list,10);
System.out.println("批量添加数据:"+b);
}
/**
* saveOrUpdateBatch
* 设置主键ID
* 存在就修改 不存在就添加数据
*/
@Test
void saveOrUpdateBatch() {
List list=new ArrayList();
A_User user=new A_User();
user.setIds(36);
user.setNames("批量1");
user.setAddresss("洛阳");
A_User user1=new A_User();
user1.setIds(37);
user1.setNames("批量1");
user1.setAddresss("洛阳");
list.add(user);
list.add(user1);
boolean b = userService.saveOrUpdateBatch(list,10);
System.out.println("批量添加数据:"+b);
}
}
3:mybatisPlus扩展功能
3.1:分页
1:配置分页拦截器,pom带入对应的jar
@Configuration
@MapperScan(value = "com.thit.springboot06_mybatisplus.mapper")
public class MybatisPlusConfig {
/**
* 创建mybatisPlus分页拦截器 类型是mysql
* 新的分页插件,一缓和二缓遵循mybatis的规则,
* 需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存出现问题(该属性会在旧插件移除后一同移除)
* @return
*/
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor(){
MybatisPlusInterceptor mybatisPlusInterceptor=new MybatisPlusInterceptor();
//添加mysql分页插件支持
mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
//添加乐观锁
mybatisPlusInterceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
return mybatisPlusInterceptor;
}
}
2:代码实现
@Autowired
UserMapper userMapper;
/**
* mybatisPlus的分页插件使用
* ==> Preparing: select * from user where age>? LIMIT ?,?
* ==> Parameters: 0(Integer), 5(Long), 5(Long)
*/
@Test
void fenYe(){
QueryWrapper<A_User> ueryWrapper=new QueryWrapper();
//baseMapper的分页方法 开始页和条数
Page<A_User> page=new Page<>(2,5);
Page<A_User> page1 = userMapper.selectPage(page, ueryWrapper);
System.out.println("输出page"+page);
System.out.println("输出page1:"+page.getMaxLimit());
}
/**
* mybatisPlus的分页插件使用
* 自定义分页 需要自定义接口
* ==> Preparing: select * from user where age>? LIMIT ?,?
* ==> Parameters: 0(Integer), 5(Long), 5(Long)
*/
@Test
void 自定义分页(){
//开始页和条数
Page<A_User> page=new Page<>(2,5);
int age=0;
//这个分页是自定义的分页 需要自己写方法 写sql
Page<A_User> page1 = userMapper.selectAllByPage(page, age);
System.out.println("输出page"+page);
System.out.println("输出page1:"+page.getMaxLimit());
}
3.2:乐观锁
乐观锁也就是不加锁,修改数据的时候数据库字段加一个版本号
1:实体类代码
@Data
@TableName(value = "Product")
public class Product {
private int id;
private String name;
private int price;
@Version //乐观锁注解
private int version;
}
2:mapper代码
@Mapper
public interface ProductMapper extends BaseMapper<Product> {
}
3:测试代码
@Autowired
ProductMapper productMapper;
@Test
void 乐观锁(){
//小李查询
Product productLi = productMapper.selectById(1);
System.out.println("小李查询价格:"+productLi.getPrice());
//小王查询
Product productWang = productMapper.selectById(1);
System.out.println("小王查询价格:"+productWang.getPrice());
//小李修改价格+50
productLi.setPrice(productLi.getPrice()+50);
productMapper.updateById(productLi);
//小王修改价格-30
productWang.setPrice(productWang.getPrice()-30);
int i = productMapper.updateById(productWang);
if (i==0){
productWang= productMapper.selectById(1);
productWang.setPrice(productWang.getPrice()-30);
productMapper.updateById(productWang);
}
//小李查询
Product productBoss = productMapper.selectById(1);
System.out.println("老板查询价格:"+productBoss.getPrice());
}
3.3:多数据源切换
mp的多数据源,不负责读写分离,只负责数据源切换,需要我们自己根据业务使用注解
1:引入依赖
<!-- mybatisPlus多数据源依赖-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.6.1</version>
</dependency>
2:配置数据源
spring:
datasource:
dynamic:
primary: master #设置默认的数据源或者数据源组,默认值即为master
strict: false #严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
datasource:
master:
url: jdbc:mysql://localhost:3306/W1?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: 123456
slave_1:
url: jdbc:mysql://localhost:3306/W1R1?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
slave_2:
url: jdbc:mysql://localhost:3306/W1R2?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
#......省略
#以上会配置一个默认库master,一个组slave下有两个子库slave_1,slave_2
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
mapper-locations: classpath*:/mapper/**/*.xml
server:
port: 8080
3:使用注解切换数据源
@Mapper
public interface UserMapper extends BaseMapper<A_User> {
@SuppressWarnings("MybatisXMapperMethodInspection")
@DS("slave") //多个读库 负载均衡轮训查询
//@DS("master")
//@DS 主库从库切换
//这个方法是自定义方法,需要在xml中自定义查询sql
Map<String, Object> selectMapById(@Param("id") int id);
//这个方法是自定义方法,需要在xml中自定义查询sql
Page<A_User> selectAllByPage(@Param("page") Page page, @Param("age") int age);
}
4:测试代码
/**
* 读写分离
* 1主库2从库
*/
@Autowired
UserMapper userMapper;
@GetMapping(value = "select1")
public Map<String, Object> selectMap() {
Map<String, Object> map = userMapper.selectMapById(1);
return map;
}
测试结果:在两个数据源之间切换