mybatis-plus-sgg-40-57

LambdaXxxWrapper #

  • LambdaQueryWrapper主要是为了防止字段名写错

       @Test
    public void test11(){

        String username="abc";
        Integer ageBegin=null;
        Integer ageEnd=30;
        LambdaQueryWrapper<User> queryWrapper=new LambdaQueryWrapper<>();
        queryWrapper.like(StringUtils.isNotBlank(username),User::getUserName,username)
                .ge(ageBegin!=null,User::getAge,ageBegin);
        userMapper.selectList(queryWrapper);
    }

    sql日志打印

    ==>  Preparing: SELECT uid AS id,name AS userName,age,email,is_deleted_ly FROM t_user WHERE is_deleted_ly=0 AND (name LIKE ?)
==> Parameters: %abc%(String)
<==      Total: 0

  • LambdaUpdateWrapper

        @Test
    public void test12() {

        //(age>23且用户名包含a)  或 (邮箱为null)
        LambdaUpdateWrapper<User> updateWrapper = new LambdaUpdateWrapper<>();
        updateWrapper.like(User::getUserName, "a")
                .and(userUpdateWrapper ->
                        userUpdateWrapper.gt(User::getAge, 23).or().isNotNull(User::getEmail));
        updateWrapper.set(User::getUserName, "小黑").set(User::getEmail, "abc@ly.com");
        userMapper.update(null, updateWrapper);
    }

    sql日志打印

    ==>  Preparing: UPDATE t_user SET name=?,email=? WHERE is_deleted_ly=0 AND (name LIKE ? AND (age > ? OR email IS NOT NULL))
==> Parameters: 小黑(String), abc@ly.com(String), %a%(String), 23(Integer)
<==    Updates: 0

MyBatis分页 #

  • 先使用配置类

    
@Configuration
@MapperScan("com.ly.mybatisplus.mapper")
public class MyBatisConfig {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor mybatisPlusInterceptor=new MybatisPlusInterceptor();
        mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return mybatisPlusInterceptor;
    }

}

  • 使用

        @Test
    public void testPage() {
        Page<User> page = new Page<>();
        page.setCurrent(2);//当前页页码
        page.setSize(3);//每页条数
        Page<User> userPage = userMapper.selectPage(page, null);
        System.out.println(userPage.getRecords() + "----\n"
                + userPage.getPages() + "----\n"
                + userPage.getTotal() + "---\n")
        ;
    }

    sql日志打印

    ==>  Preparing: SELECT uid AS id,name AS userName,age,email,is_deleted_ly FROM t_user WHERE is_deleted_ly=0 LIMIT ?,?
==> Parameters: 3(Long), 3(Long)
<==    Columns: id, userName, age, email, is_deleted_ly
<==        Row: 4, 被修改了, 21, test4@baomidou.com, 0
<==        Row: 5, 被修改了, 24, email被修改了, 0
<==        Row: 6, 张三5, 18, test5@baomidou.com, 0
<==      Total: 3

    • 结果Page对象的数据

      [User(id=4, userName=被修改了, age=21, email=test4@baomidou.com, isDeletedLy=0), User(id=5, userName=被修改了, age=24, email=email被修改了, isDeletedLy=0), User(id=6, userName=张三5, age=18, email=test5@baomidou.com, isDeletedLy=0)]----
3----
8---

  • 自定义分页功能

    • 首先，设置类型别名所在的包

      mybatis-plus: 
  type-aliases-package: com.ly.mybatisplus.pojo

    • 在Mapper类中编写接口方法

      @Repository
public interface UserMapper extends BaseMapper<User> { 

    /**
     * 通过年龄查询并分页
     * @param page mybatis-plus提供的，必须存在且在第一个位置
     * @param age
     * @return
     */
    Page<User> selectPageVO(Page<User> page,Integer age);
}

      注意第一个参数

    • 在Mapper.xml中编写语句

          <select id="selectPageVO" resultType="User">
        select uid,name,email from t_user where age > #{age}
    </select>

    • 测试方法

         @Test
    public void testPageCustom() {
        Page<User> page = new Page<>();
        page.setCurrent(3);//当前页页码
        page.setSize(5);//每页条数
        Page<User> userPage = userMapper.selectPageVO(page, 12);
        System.out.println(userPage.getRecords() + "----\n"
                + userPage.getPages() + "----\n"
                + userPage.getTotal() + "---\n")
        ;
    }

      sql日志输出

      ==>  Preparing: SELECT COUNT(*) AS total FROM t_user WHERE age > ?
==> Parameters: 12(Integer)
<==    Columns: total
<==        Row: 20
<==      Total: 1
//从第10行开始（不包括第10行），取5条记录
==>  Preparing: select uid,name,email from t_user where age > ? LIMIT ?,?
==> Parameters: 12(Integer), 10(Long), 5(Long)
<==    Columns: uid, name, email
<==        Row: 11, a, null
<==        Row: 12, a, null
<==        Row: 13, a, null
<==        Row: 14, a, null
<==        Row: 15, a, null
<==      Total: 5
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@706fe5c6]
[null, null, null, null, null]----
4----
20---

      注意上面那个sql，他会先查询条数，如果条数<=0，那么就不会执行下面的数据搜索了

悲观锁和乐观锁 #

  • 场景 ly-20241212142150898

  • 乐观锁根据版本号使用 version

  • 乐观锁实现流程 ly-20241212142151075

模拟冲突 #

  • 表创建

    CREATE TABLE t_product (
	id BIGINT ( 20 ) NOT NULL COMMENT '主键id',
	NAME VARCHAR ( 30 ) null DEFAULT NULL COMMENT '商品名称',
	price INT ( 11 ) DEFAULT 0 COMMENT '价格',
	version INT ( 11 ) DEFAULT 0 COMMENT '乐观锁版本号',
    PRIMARY KEY ( id ) 
)

  • 创建ProductMapper

    @Repository
public interface ProductMapper extends BaseMapper<Product> {
}

  • 数据库数据 ly-20241212142151128

  • 代码

        @Test
    public void testModel() {
        //小李查询商品
        Product productLi = productMapper.selectById(1L);
        //小王查询商品
        Product productWang = productMapper.selectById(1L);
        //小李将商品加50
        productLi.setPrice(productLi.getPrice()+50);
        productMapper.updateById(productLi);

        //小王将价格降低30
        productWang.setPrice(productWang.getPrice()-30);
        productMapper.updateById(productWang);

    }

    sql日志

    ==>  Preparing: UPDATE t_product SET name=?, price=?, version=? WHERE id=?
==> Parameters: 外星人(String), 150(Integer), 0(Integer), 1(Long)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6325f352]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@70730db] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@91831175 wrapping com.mysql.cj.jdbc.ConnectionImpl@74ea46e2] will not be managed by Spring
==>  Preparing: UPDATE t_product SET name=?, price=?, version=? WHERE id=?
==> Parameters: 外星人(String), 70(Integer), 0(Integer), 1(Long)
<==    Updates: 1

    //最终结果为70

乐观锁插件 #

  • 在实体类中使用@Version注解表示乐观锁版本号

        @Version
    private Integer version;

  • 配置类

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

  • 再次运行代码

        @Test
    public void testModel() {
        //小李查询商品
        Product productLi = productMapper.selectById(1L);
        //小王查询商品
        Product productWang = productMapper.selectById(1L);
        //小李将商品加50
        productLi.setPrice(productLi.getPrice()+50);
        productMapper.updateById(productLi);

        //小王将价格降低30
        productWang.setPrice(productWang.getPrice()-30);
        productMapper.updateById(productWang);

    }

    sql日志查看

    ==>  Preparing: UPDATE t_product SET name=?, price=?, version=? WHERE id=? AND version=?
==> Parameters: 外星人(String), 120(Integer), 1(Integer), 1(Long), 0(Integer)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2d64160c]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@33063f5b] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@356539350 wrapping com.mysql.cj.jdbc.ConnectionImpl@127a7272] will not be managed by Spring
==>  Preparing: UPDATE t_product SET name=?, price=?, version=? WHERE id=? AND version=?
==> Parameters: 外星人(String), 40(Integer), 1(Integer), 1(Long), 0(Integer)
<==    Updates: 0

优化修改流程 #

  •     @Test
    public void testModel() {
        //小李查询商品
        Product productLi = productMapper.selectById(1L);
        //小王查询商品
        Product productWang = productMapper.selectById(1L);
        //小李将商品加50
        productLi.setPrice(productLi.getPrice() + 50);
        productMapper.updateById(productLi);

        //小王将价格降低30
        productWang.setPrice(productWang.getPrice() - 30);
        int i = productMapper.updateById(productWang);

        //如果小王操作失败,再获取一次
        if (i == 0) {
            Product product = productMapper.selectById(1L);
            product.setPrice(product.getPrice() - 30);
            productMapper.updateById(product);
        }

    }

    sql日志打印

    ==>  Preparing: UPDATE t_product SET name=?, price=?, version=? WHERE id=? AND version=?
==> Parameters: 外星人(String), 150(Integer), 6(Integer), 1(Long), 5(Integer)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@544e8149]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@48a0c8aa] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@1637000661 wrapping com.mysql.cj.jdbc.ConnectionImpl@5f481b73] will not be managed by Spring
==>  Preparing: UPDATE t_product SET name=?, price=?, version=? WHERE id=? AND version=?
==> Parameters: 外星人(String), 70(Integer), 6(Integer), 1(Long), 5(Integer)
<==    Updates: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@48a0c8aa]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4cbc2e3b] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@43473566 wrapping com.mysql.cj.jdbc.ConnectionImpl@5f481b73] will not be managed by Spring
==>  Preparing: SELECT id,name,price,version FROM t_product WHERE id=?
==> Parameters: 1(Long)
<==    Columns: id, name, price, version
<==        Row: 1, 外星人, 150, 6
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4cbc2e3b]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@57562473] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@2050360660 wrapping com.mysql.cj.jdbc.ConnectionImpl@5f481b73] will not be managed by Spring
==>  Preparing: UPDATE t_product SET name=?, price=?, version=? WHERE id=? AND version=?
==> Parameters: 外星人(String), 120(Integer), 7(Integer), 1(Long), 6(Integer)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@57562473]

通用枚举 #

  • 添加一个enum类

    @Getter
public enum SexEnum {
    MALE(1, "男"),
    FEMALE(2, "女");

    private Integer sex;
    private String sexName;

    SexEnum(Integer sex, String sexName) {
        this.sex = sex;
        this.sexName = sexName;
    }
}

  • 数据库增加一个sex 字段，实体类增加一个sex属性 ly-20241212142151181

    • 实体类

       private SexEnum sex;

  • 进行添加

        @Test
    public void testEnum(){
        User user=new User();
        user.setUserName("enum - 测试名字");
        user.setSexEnum(SexEnum.MALE);
        int insert = userMapper.insert(user);
        System.out.println(insert);
    }

    • 注意看sql日志，有报错信息

      ==>  Preparing: INSERT INTO t_user ( name, sex ) VALUES ( ?, ? )
==> Parameters: enum - 测试名字(String), MALE(String)

### SQL: INSERT INTO t_user  ( name,    sex )  VALUES  ( ?,    ? )
### Cause: java.sql.SQLException: Incorrect integer value: 'MALE' for column 'sex' at row 1

      插入了非数字

    • 修正，enum类添加注解

      @EnumValue //将注解所标识的属性的值设置到数据库
    private Integer sex;

      • 扫描通用枚举的包 application.yml中

        mybatis-plus: 
  type-enums-package: com.ly.mybatisplus.enums

    • 运行测试类并查看日志

      ==>  Preparing: INSERT INTO t_user ( name, sex ) VALUES ( ?, ? )
==> Parameters: enum - 测试名字(String), 1(Integer)
<==    Updates: 1

代码生成器 #

{% post_link study/mybatis_plus/official/hello 在28%进度的地方 %}

  • mybatis-plus 代码自动生成

    • maven 依赖

      
        <!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-generator -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.5.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.velocity/velocity-engine-core -->
        <dependency>
            <groupId>org.apache.velocity</groupId>
            <artifactId>velocity-engine-core</artifactId>
            <version>2.3</version>
        </dependency>

    • 在测试类中编写程序让其自动生成

      import com.baomidou.mybatisplus.generator.FastAutoGenerator;
import com.baomidou.mybatisplus.generator.config.DataSourceConfig;
import org.apache.ibatis.jdbc.ScriptRunner;

import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.SQLException;

/**
 * <p>
 * 快速生成
 * </p>
 *
 * @author lanjerry
 * @since 2021-09-16
 */
public class FastAutoGeneratorTest {

    /**
     * 执行初始化数据库脚本
     */
    public static void before() throws SQLException {
        Connection conn = DATA_SOURCE_CONFIG.build().getConn();
        InputStream inputStream = FastAutoGeneratorTest.class.getResourceAsStream("/db/schema-mysql.sql");
        ScriptRunner scriptRunner = new ScriptRunner(conn);
        scriptRunner.setAutoCommit(true);
        scriptRunner.runScript(new InputStreamReader(inputStream));
        conn.close();
    }

    /**
     * 数据源配置
     */
    private static final DataSourceConfig.Builder DATA_SOURCE_CONFIG = new DataSourceConfig
            .Builder("jdbc:mysql://localhost:3306/mybatis_plus_demo?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&nullCatalogMeansCurrent=true", "root", "123456");

    /**
     * 执行 run
     */
    public static void main(String[] args) throws SQLException {
        before();
        FastAutoGenerator.create(DATA_SOURCE_CONFIG)
                // 全局配置
                .globalConfig((scanner, builder) -> builder.author(scanner.apply("请输入作者名称")))
                // 包配置
                .packageConfig((scanner, builder) -> builder.parent(scanner.apply("请输入包名")))
                // 策略配置
                .strategyConfig((scanner, builder) -> builder.addInclude(scanner.apply("请输入表名，多个表名用,隔开")))
                /*
                    模板引擎配置，默认 Velocity 可选模板引擎 Beetl 或 Freemarker
                   .templateEngine(new BeetlTemplateEngine())
                   .templateEngine(new FreemarkerTemplateEngine())
                 */
                .execute();
    }
}
      • shang gui gu 配置 ly-20241212142151231

      ly-20241212142151284

模拟多数据源环境 #

  • 新建一个mybatis-plus数据库和表 ly-20241212142151333

  • maven依赖添加

    <!-- https://mvnrepository.com/artifact/com.baomidou/dynamic-datasource-spring-boot-starter -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>3.5.1</version>
</dependency>

  • 前提 使用mybatis_plus中的t_product表 及mybatis_plus1中的t_product1表

  • yml配置

    spring:
  datasource:
    dynamic:
      primary: master #设置默认的数据源或者数据源组,默认值即为master
      strict: false #严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
      datasource:
        master:
          url: jdbc:mysql://localhost:3306/mybatis_plus?characterEncoding=utf-8&&useSSL=false&&allowPublicKeyRetrieval=true
          username: root
          password: 123456
          driver-class-name: com.mysql.jdbc.Driver # 3.2.0开始支持SPI可省略此配置
        slave_1:
          url: jdbc:mysql://localhost:3306/mybatis_plus_1?characterEncoding=utf-8&&useSSL=false&&allowPublicKeyRetrieval=true
          username: root
          password: 123456
          driver-class-name: com.mysql.jdbc.Driver
        #slave_2:
        #  url: ENC(xxxxx) # 内置加密,使用请查看详细文档
        #  username: ENC(xxxxx)
        #  password: ENC(xxxxx)
       #   driver-class-name: com.mysql.jdbc.Driver
        #......省略
        #以上会配置一个默认库master，一个组slave下有两个子库slave_1,slave_2

  • 代码

    • 结构 ly-20241212142151384 ly-20241212142151435

安装MyBatisX插件 #

  • 插件市场 ly-20241212142151482
  • 自动定位 ly-20241212142151534

MyBatis代码快速生成 #

  • 配置 ly-20241212142151586
  • url及密码配置 ly-20241212142151634
  • 使用 ly-20241212142151683
  • ly-20241212142151730
  • 自动生成 ly-20241212142151781
  • ly-20241212142151830