目录
一、条件构造器
1.1 wapper介绍
Wrapper : 条件构造抽象类,最顶端父类
AbstractWrapper : 用于查询条件封装,生成 sql 的 where 条件
QueryWrapper : 查询条件封装
UpdateWrapper : Update条件封装
AbstractLambdaWrapper : 使用Lambda 语法
LambdaQueryWrapper :基于Lambda语法的查询Wrapper
LambdaUpdateWrapper : 基于Lambda语法的 更新Wrapper
1.2 QueryWrapper
@Test
void test1(){
//查询名字包括a、年龄介于18、30之间、邮箱非空的记录
//SELECT id,name,age,email,is_deleted FROM user WHERE is_deleted=0 AND (name LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL)
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.like("name","a").between("age",18,30).isNotNull("email");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
@Test
void test2(){
//查询记录,按年龄降序、id升序排列
//SELECT id,name,age,email,is_deleted FROM user WHERE is_deleted=0 ORDER BY age DESC,id ASC
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.orderByDesc("age").orderByAsc("id");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
@Test
void test3(){
//删除邮箱为空的记录
//UPDATE user SET is_deleted=1 WHERE is_deleted=0 AND (email IS NULL)
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.isNull("email");
userMapper.delete(wrapper);
}
@Test
void test4(){
//修改年龄大于18,名字包含a,或邮箱非空的记录
//UPDATE user SET name=?, email=? WHERE is_deleted=0 AND (age > ? AND name LIKE ? OR email IS NULL)
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.gt("age",18)
.like("name","a")
.or()
.isNull("email");
User user=new User();
user.setName("kunkun");
user.setEmail("ikun@qq.com");
userMapper.update(user,wrapper);
}
@Test
void test5(){
//修改名字包含a,且(年龄大于18或邮箱为空)的记录
//UPDATE user SET name=?, email=? WHERE is_deleted=0 AND (name LIKE ? AND (age > ? OR email IS NULL))
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.like("name","a")
.and(i->i.gt("age",18).or().isNull("email"));
User user=new User();
user.setName("kunkun");
user.setEmail("ikun@qq.com");
userMapper.update(user,wrapper);
}
@Test
void test6(){
//查询所有记录的部分字段
//SELECT name,age,email FROM user WHERE is_deleted=0
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.select("name","age","email");
List<Map<String, Object>> list = userMapper.selectMaps(wrapper);
list.forEach(System.out::println);
}
@Test
void test7(){
//查询id小于等于100的所有记录(子查询)
//SELECT id,name,age,email,is_deleted FROM user WHERE is_deleted=0 AND (id IN (select id from user where id<=100))
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.inSql("id","select id from user where id<=100");
List<Map<String, Object>> list = userMapper.selectMaps(wrapper);
list.forEach(System.out::println);
}
在真正开发的过程中,组装条件是常见的功能,而这些条件数据来源于用户输入,是可选的,因 此我们在组装这些条件时,必须先判断用户是否选择了这些条件,若选择则需要组装该条件,若没有选择则一定不能组装,以免影响SQL执行的结果。我们可以使用带condition参数的重载方法构建查询条件:
@Test
public void test08UseCondition() {
//定义查询条件,有可能为null(用户未输入或未选择)
String username = null;
Integer ageBegin = 10;
Integer ageEnd = 24;
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//StringUtils.isNotBlank()判断某字符串是否不为空且长度不为0且不由空白符(whitespace)构成
queryWrapper.like(StringUtils.isNotBlank(username), "username", "a")
.ge(ageBegin != null, "age", ageBegin)
.le(ageEnd != null, "age", ageEnd);
//SELECT id,username AS name,age,email,is_deleted FROM t_user WHERE (age >=? AND age <= ?)
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
1.3 UpdateWrapper
@Test
void test8(){
//更新名字包含a,且(年龄大于20或邮箱为空)的记录
//UPDATE user SET name=?,email=? WHERE is_deleted=0 AND (name LIKE ? AND (age > ? OR email IS NULL))
UpdateWrapper<User> wrapper = new UpdateWrapper<>();
wrapper.like("name","a")
.and(i->i.gt("age",20).or().isNull("email"))
.set("name","haha")
.set("email","haha@qq.com");
userMapper.update(null,wrapper);
}
@Test
void test9(){
//查询名字包含a,且年龄小于等于20的记录
//SELECT id,name,age,email,is_deleted FROM user WHERE is_deleted=0 AND (name LIKE ? AND age <= ?)
String name="zp";
Integer ageBegin=null;
Integer ageEnd=30;
UpdateWrapper<User> wrapper = new UpdateWrapper<>();
wrapper.like(StringUtils.isNotBlank(name),"name",name)
.ge(ageBegin!=null,"age",ageBegin)
.le(ageEnd!=null,"age",ageEnd);
userMapper.selectList(wrapper);
}
1.4 LambdaQueryWrapper
@Test
void test10(){
//查询名字包含a,且年龄小于等于20的记录
//SELECT id,name,age,email,is_deleted FROM user WHERE is_deleted=0 AND (name LIKE ? AND age <= ?)
String name="zp";
Integer ageBegin=null;
Integer ageEnd=30;
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.like(StringUtils.isNotBlank(name),User::getName,name)
.ge(ageBegin!=null,User::getAge,ageBegin)
.le(ageEnd!=null,User::getAge,ageEnd);
userMapper.selectList(wrapper);
}
1.5 LambdaUpdateWrapper
@Test
void test11(){
//更新名字包含a,且(年龄大于20或邮箱为空)的记录
//UPDATE user SET name=?,email=? WHERE is_deleted=0 AND (name LIKE ? AND (age > ? OR email IS NULL))
LambdaUpdateWrapper<User> wrapper = new LambdaUpdateWrapper<>();
wrapper.like(User::getName,"a")
.and(i->i.gt(User::getAge,20).or().isNull(User::getEmail))
.set(User::getName,"haha")
.set(User::getEmail,"haha@qq.com");
userMapper.update(null,wrapper);
}
二、分页插件
MyBatis Plus自带分页插件,只要简单的配置即可实现分页功能
2.1 使用BaseMapper中的分页方法
创建配置类:
@Configuration
public class MyBatisPlusConfig {
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor(){
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
return interceptor;
}
}
不同版本可能有区别
@Bean
public PaginationInterceptor paginationInterceptor(){
return new PaginationInterceptor();
}
测试:
@Test
void testPage(){
//SELECT id,name,age,email,is_deleted FROM user WHERE is_deleted=0 LIMIT ?
Page<User> page = new Page<>(1, 3);//当前页码:1 每页的记录数:3
userMapper.selectPage(page,null);
System.out.println(page); //page对象
System.out.println(page.getRecords()); //所有的记录
System.out.println(page.getPages()); //总页数
System.out.println(page.getTotal()); //总记录数
System.out.println(page.hasNext()); //是否有下一页
System.out.println(page.hasPrevious()); //是否有上一页
}
//测试结果:
com.baomidou.mybatisplus.extension.plugins.pagination.Page@4527f70a
[User(id=3, name=zp, age=19, email=zp@qq.com, isDeleted=0), User(id=4, name=kunkun, age=21, email=ikun@qq.com, isDeleted=0), User(id=5, name=Billie, age=24, email=test5@baomidou.com, isDeleted=0)]
3
7
true
false
2.2 在自定义mapper方法中使用分页
方法返回值必须是Page类型且指定泛型,参数用@param标注且第一个参数是Page类型
@Repository
public interface UserMapper extends BaseMapper<User> {
Page<User> selectPageVo(@Param("page") Page<User> page,@Param("age") Integer 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.mybatisplus.mapper.UserMapper">
<select id="selectPageVo" resultType="com.mybatisplus.pojo.User">
select id,name,age,email from user where age>#{age}
</select>
</mapper>
@Test
void testPage1(){
//select id,name,age,email from user where age>? LIMIT ?
Page<User> page = new Page<>(1, 3);//当前页码:1 每页的记录数:3
userMapper.selectPageVo(page,20); //查询年龄大于20的记录
System.out.println(page); //page对象
System.out.println(page.getRecords()); //所有的记录
System.out.println(page.getPages()); //总页数
System.out.println(page.getTotal()); //总记录数
System.out.println(page.hasNext()); //是否有下一页
System.out.println(page.hasPrevious()); //是否有上一页
}
//测试结果:
com.baomidou.mybatisplus.extension.plugins.pagination.Page@4bc33720
[User(id=4, name=kunkun, age=21, email=ikun@qq.com, isDeleted=null), User(id=5, name=Billie, age=24, email=test5@baomidou.com, isDeleted=null), User(id=1556111099474116610, name=wzz, age=28, email=1@qq.com, isDeleted=null)]
2
4
true
false
三、多数据源
适用于多种场景:纯粹多库、 读写分离、 一主多从、 混合模式等
假如有的表放在数据库1中,有的表放在数据库2中,该如何处理?
1.引入依赖
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.5.0</version>
</dependency>
2.配置多数据源
spring:
# 配置数据源信息
datasource:
dynamic:
# 设置默认的数据源或者数据源组,默认值即为master
primary: master
# 严格匹配数据源,默认false.true未匹配到指定数据源时抛异常,false使用默认数据源
strict: false
datasource:
#主数据库
master:
url: jdbc:mysql://localhost:3306/mybatis_plus?characterEncoding=utf-8&useSSL=false
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: 123456
//从数据库1
slave_1:
url: jdbc:mysql://localhost:3306/mybatis_plus_1?characterEncoding=utf-8&useSSL=false
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: 123456
3.在类或方法上标注@DB注解,指明使用哪个数据库
@DS("master") //指定所操作的数据源-master
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements
UserService {
}
@DS("slave_1") //指定所操作的数据源-slave_1
@Service
public class ProductServiceImpl extends ServiceImpl<ProductMapper, Product>
implements ProductService {
}
四、MyBatisX插件
MyBatis-Plus为我们提供了强大的mapper和service模板,能够大大的提高开发效率。但是在真正开发过程中,MyBatis-Plus并不能为我们解决所有问题,例如一些复杂的SQL,多表联查,我们就需要自己去编写代码和SQL语句,我们该如何快速的解决这个问题呢?这个时候可以使用MyBatisX插件
1.首先要安装在idea中MyBatisX插件
2.在 idea 配置 Database 数据源
3.在需要生成相关pojo、mapper、service的数据库表上点右键使用生成功能
4. 设置生成策略,如字段前后缀、命名方法、包的路径
5.生成相关文件
6.在mapper中键入关键词,可自动提示sql语句模板,根据代码提示生成sql语句
自动生成的方法:
public interface UserMapper extends BaseMapper<User> {
List<User> selectAllById(@Param("id") Long id);
}
自动生成的sql语句:
<?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.mybatisplus.mapper.UserMapper">
<resultMap id="BaseResultMap" type="com.mybatisplus.domain.User">
<id property="id" column="id" jdbcType="BIGINT"/>
<result property="name" column="name" jdbcType="VARCHAR"/>
<result property="age" column="age" jdbcType="INTEGER"/>
<result property="email" column="email" jdbcType="VARCHAR"/>
<result property="isDeleted" column="is_deleted" jdbcType="INTEGER"/>
</resultMap>
<sql id="Base_Column_List">
id,name,age,
email,is_deleted
</sql>
<select id="selectAllById" resultMap="BaseResultMap"> //为我们自动生成的
select
<include refid="Base_Column_List"/>
from user
where
id = #{id,jdbcType=NUMERIC}
</select>
</mapper>