MyBatisPlus学习总结(2)

3.条件构造器和常用接口

3.1wapper介绍

3.2QueryWrapper

3.2.1组装查询条件

@Test
public void test01(){
//查询用户名包含a，年龄在20到30之间，并且邮箱不为null的用户信息
//SELECT id,username AS name,age,email,is_deleted FROM t_user WHERE
//is_deleted=0 AND (username LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL)
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.like("username", "a")
    .between("age", 20, 30)
    .isNotNull("email");
    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

3.2.2组装排序条件

@Test
public void test02(){
//按年龄降序查询用户，如果年龄相同则按id升序排列
//SELECT id,username AS name,age,email,is_deleted FROM t_user WHERE
//is_deleted=0 ORDER BY age DESC,id ASC
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper
    .orderByDesc("age")
    .orderByAsc("id");
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}

3.2.3组装删除条件

@Test
public void test03(){
    //删除email为空的用户
    //DELETE FROM t_user WHERE (email IS NULL)
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.isNull("email");
    //条件构造器也可以构建删除语句的条件
    int result = userMapper.delete(queryWrapper);
    System.out.println("受影响的行数：" + result);
}

3.2.4条件的优先级

@Test
public void test04() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//将（年龄大于20并且用户名中包含有a）或邮箱为null的用户信息修改
//UPDATE t_user SET age=?, email=? WHERE (username LIKE ? AND age > ? OR email IS NULL)
    queryWrapper
    .like("username", "a")
    .gt("age", 20)
    .or()
    .isNull("email");
        User user = new User();
        user.setAge(18);
        user.setEmail("user@atguigu.com");
        int result = userMapper.update(user, queryWrapper);
        System.out.println("受影响的行数：" + result);
}

@Test
public void test04() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//将用户名中包含有a并且（年龄大于20或邮箱为null）的用户信息修改
//UPDATE t_user SET age=?, email=? WHERE (username LIKE ? AND (age > ? OR email IS NULL))
//lambda表达式内的逻辑优先运算
    queryWrapper.like("username", "a")
    .and(i -> i.gt("age", 20).or().isNull("email"));
    User user = new User();
    user.setAge(18);
    user.setEmail("user@atguigu.com");
    int result = userMapper.update(user, queryWrapper);
    System.out.println("受影响的行数：" + result);
}

3.2.5组装select子句

@Test
public void test05() {
    //查询用户信息的username和age字段
    //SELECT username,age FROM t_user
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.select("username", "age");
    //selectMaps()返回Map集合列表，通常配合select()使用，避免User对象中没有被查询到的列值
    为null
    List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
    maps.forEach(System.out::println);
}

3.2.6实现子查询

@Test
public void test06() {
//查询id小于等于3的用户信息
//SELECT id,username AS name,age,email,is_deleted FROM t_user WHERE (id IN (select id from t_user where id <= 3))
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.inSql("id", "select id from t_user where id <= 3");
    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

3.3UpdateWrapper

@Test
public void test07() {
    //将（年龄大于20或邮箱为null）并且用户名中包含有a的用户信息修改
    //组装set子句以及修改条件
    UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
    //lambda表达式内的逻辑优先运算
    updateWrapper
    .set("age", 18)
    .set("email", "user@atguigu.com")
    .like("username", "a")
    .and(i -> i.gt("age", 20).or().isNull("email"));
    //这里必须要创建User对象，否则无法应用自动填充。如果没有自动填充，可以设置为null
    //UPDATE t_user SET username=?, age=?,email=? WHERE (username LIKE ? AND(age > ? OR email IS NULL))
    //User user = new User();
    //user.setName("张三");
    //int result = userMapper.update(user, updateWrapper);
    //UPDATE t_user SET age=?,email=? WHERE (username LIKE ? AND (age > ? ORemail IS NULL))
    int result = userMapper.update(null, updateWrapper);
    System.out.println(result);
}

3.4condition

3.4.1思路一

@Test
public void test08() {
//定义查询条件，有可能为null（用户未输入或未选择）
        String username = null;
        Integer ageBegin = 10;
        Integer ageEnd = 24;
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//StringUtils.isNotBlank()判断某字符串是否不为空且长度不为0且不由空白符(whitespace)构成
        if(StringUtils.isNotBlank(username)){
        queryWrapper.like("username","a");
        }
        if(ageBegin != null){
        queryWrapper.ge("age", ageBegin);
        }
        if(ageEnd != null){
        queryWrapper.le("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);
}

3.4.2思路二

@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);
}

3.5LambdaQueryWrapper

@Test
public void test09() {
//定义查询条件，有可能为null（用户未输入）
//LambdaQueryWrapper可以用函数式接口::,用get方法来获取属性名，防止自己手动输入属性名，然后出现错误。User::getId可以避免这个问题。
        String username = "a";
        Integer ageBegin = 10;
        Integer ageEnd = 24;
LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
//避免使用字符串表示字段，防止运行时错误
    queryWrapper
    .like(StringUtils.isNotBlank(username), User::getName, username)
    .ge(ageBegin != null, User::getAge, ageBegin)
    .le(ageEnd != null, User::getAge, ageEnd);
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}

3.6LambdaUpdateWrapper

@Test
public void test10() {
//组装set子句
LambdaUpdateWrapper<User> updateWrapper = new LambdaUpdateWrapper<>();
updateWrapper
.set(User::getAge, 18)
.set(User::getEmail, "user@atguigu.com")
.like(User::getName, "a")
.and(i -> i.lt(User::getAge, 24).or().isNull(User::getEmail)); //lambda表达式内的逻辑优先运算
User user = new User();
int result = userMapper.update(user, updateWrapper);
System.out.println("受影响的行数：" + result);
}

4.插件

4.1分页插件

4.1.1添加配置类

@Configuration
@MapperScan("com.atguigu.mybatisplus.mapper") //可以将主类中的注解移到此处
public class MybatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
    MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
    interceptor.addInnerInterceptor(new
    PaginationInnerInterceptor(DbType.MYSQL));
    return interceptor;
}
}

4.1.2测试

@Test
public void testPage(){
    //设置分页参数
    Page<User> page = new Page<>(1, 5);
    userMapper.selectPage(page, null);
    //获取分页数据
    List<User> list = page.getRecords();
    list.forEach(System.out::println);
    System.out.println("当前页："+page.getCurrent());
    System.out.println("每页显示的条数："+page.getSize());
    System.out.println("总记录数："+page.getTotal());
    System.out.println("总页数："+page.getPages());
    System.out.println("是否有上一页："+page.hasPrevious());
    System.out.println("是否有下一页："+page.hasNext());
}

4.2乐观锁

4.2.1修改实体类

package com.atguigu.mybatisplus.entity;
import com.baomidou.mybatisplus.annotation.Version;
import lombok.Data;
@Data
public class Product {
    private Long id;
    private String name;
    private Integer price;
    @Version
    private Integer version;
}

4.2.2添加乐观锁插件配置

@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor(){
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
//添加分页插件
interceptor.addInnerInterceptor(new
PaginationInnerInterceptor(DbType.MYSQL));
//添加乐观锁插件
interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
return interceptor;
}

4.2.3优化流程

@Test
public void testConcurrentVersionUpdate() {
//小李取数据
Product p1 = productMapper.selectById(1L);
//小王取数据
Product p2 = productMapper.selectById(1L);
//小李修改 + 50
p1.setPrice(p1.getPrice() + 50);
int result1 = productMapper.updateById(p1);
System.out.println("小李修改的结果：" + result1);
//小王修改 - 30
p2.setPrice(p2.getPrice() - 30);
int result2 = productMapper.updateById(p2);
System.out.println("小王修改的结果：" + result2);
if(result2 == 0){
//失败重试，重新获取version并更新
p2 = productMapper.selectById(1L);
    p2.setPrice(p2.getPrice() - 30);
result2 = productMapper.updateById(p2);
}
System.out.println("小王修改重试的结果：" + result2);
//老板看价格
Product p3 = productMapper.selectById(1L);
System.out.println("老板看价格：" + p3.getPrice());
}

5.通用枚举

5.1创建通用枚举类型

package com.atguigu.mp.enums;
import com.baomidou.mybatisplus.annotation.EnumValue;
import lombok.Getter;
@Getter
public enum SexEnum {
        MALE(1, "男"),
        FEMALE(2, "女");
        @EnumValue
        private Integer sex;
        private String sexName;
    SexEnum(Integer sex, String sexName) {
    this.sex = sex;
    this.sexName = sexName;
    }
}

5.2配置扫描通过枚举

mybatis-plus:
    configuration:
    # 配置MyBatis日志
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    global-config:
        db-config:
            # 配置MyBatis-Plus操作表的默认前缀
            table-prefix: t_
            # 配置MyBatis-Plus的主键策略
            id-type: auto
    # 配置扫描通用枚举
    type-enums-package: com.atguigu.mybatisplus.enums

5.3测试

@Test
public void testSexEnum(){
    User user = new User();
    user.setName("Enum");
    user.setAge(20);
    //设置性别信息为枚举项，会将@EnumValue注解所标识的属性值存储到数据库
    user.setSex(SexEnum.MALE);
    //INSERT INTO t_user ( username, age, sex ) VALUES ( ?, ?, ? )
    //Parameters: Enum(String), 20(Integer), 1(Integer)
    userMapper.insert(user);
}

6.多数据源

6.1引入依赖

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>3.5.0</version>
</dependency>

6.2配置多数据源

spring:
# 配置数据源信息
    datasource:
        dynamic:
            # 设置默认的数据源或者数据源组,默认值即为master
            primary: master
# 严格匹配数据源,默认false.true未匹配到指定数据源时抛异常,false使用默认数据源
            strict: false
            datasource:
                master:
                url: jdbc:mysql://localhost:3306/mybatis_plus?characterEncoding=utf8&useSSL=false
                driver-class-name: com.mysql.cj.jdbc.Driver
                username: root
                password: 123456
                slave_1:
                url: jdbc:mysql://localhost:3306/mybatis_plus_1?characterEncoding=utf8&useSSL=false
                driver-class-name: com.mysql.cj.jdbc.Driver
                username: root
                password: 123456

6.3创建用户Service和商品Service

@DS("master") //指定所操作的数据源
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements
UserService {
}

//商品Service
@DS("slave_1")
@Service
public class ProductServiceImpl extends ServiceImpl<ProductMapper, Product>
implements ProductService {
}

6.4测试

@Autowired
private UserService userService;
@Autowired
private ProductService productService;
@Test
public void testDynamicDataSource(){
System.out.println(userService.getById(1L));
System.out.println(productService.getById(1L));
}

结果：
1、都能顺利获取对象，则测试成功
2、如果我们实现读写分离，将写操作方法加上主库数据源，读操作方法加上从库数据源，自动切换，是不是就能实现读写分离？