mybatis-plus（快速使用全）

架构如下

P6SpyLogger SQL日志打印类## 标题

/**
 *新版SQL执行效率打印日志
 */
public class P6SpyLogger implements MessageFormattingStrategy {

    private SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SSS");
    @Override
    public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql, String s4) {
        return !"".equals(sql.trim()) ? this.format.format(new Date()) + " | took " + elapsed + "ms | " + category + " | connection " + connectionId + "\n " + sql + ";" : "";
    }
}

新版Mybatis-plus配置乐观锁

@Configuration
@MapperScan("com.chen.mapper")
@EnableTransactionManagement
public class MybatisPlusConfig {
    //配置乐观锁
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        return interceptor;
    }
}

自动填充配置（日期）

@Slf4j
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
    //插入填充策略
    @Override
    public void insertFill(MetaObject metaObject) {
        log.info("开始插入填充 ...");
        this.setFieldValByName("createTime", new Date(),metaObject);
        this.setFieldValByName("updateTime", new Date(),metaObject);
    }
    //更新时填充策略
    @Override
    public void updateFill(MetaObject metaObject) {
        log.info("开始更新填充 ...");
        this.setFieldValByName("updateTime",new Date(),metaObject);
    }
}

Mapper类

public interface UserMapper extends BaseMapper<User> {

}

实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    // mybatis 生成主键策略
    // AUTO(0),数据库ID自增
    // NONE(1), 未设置主键
    // INPUT(2), 手动输入
    // ID_WORKER(3), 默认的全局唯一ID
    // ID_WORKER_STR(3), 字符串表示法
    // UUID(4);  全局唯一id
    @TableId(type = IdType.AUTO)
    private Long id;
    private String name;
    private Integer age;
    private String email;
    @TableField(fill = FieldFill.INSERT)
    private Date createTime;
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private Date updateTime;
    @Version
    private Integer version;
    @TableLogic
    private Integer deleted;
}

springboot启动类

@SpringBootApplication
public class MybatisPlusTemplateApplication {

    public static void main(String[] args) {
        SpringApplication.run(MybatisPlusTemplateApplication.class, args);
    }

}

yml配置

# 应用服务 WEB 访问端口
server:
  port: 8080
spring:
  application:
    name: mybatis-plus-template # 应用名称
  datasource:
    username: root
    password: 333
    driver-class-name: com.p6spy.engine.spy.P6SpyDriver  #sql执行效率打印日志驱动
    #serverTimezone=UTC是欧洲时间与中国时间不同 把时间改为中国时区CTT
    url: jdbc:p6spy:mysql://localhost:3306/gz2130?serverTimezone=CTT&useUnicode=true&characterEncoding=utf8&useSSL=false
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl  # mybatis-plus控制台打印日志
  global-config:
    db-config:
      logic-delete-field: flag # 全局逻辑删除的实体字段名(since 3.3.0,配置后可以忽略不配置步骤2)
      logic-delete-value: 1 # 逻辑已删除值(默认为 1)
      logic-not-delete-value: 0 # 逻辑未删除值(默认为 0)

SQL打印的配置P6spy

#3.2.1以上使用
modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory
#3.2.1以下使用或者不配置
#modulelist=com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory
# 自定义日志打印 //自定义P6SpyLogger类的地址
logMessageFormat=com.chen.common.log.P6SpyLogger
# 使用日志系统记录sql
appender=com.p6spy.engine.spy.appender.StdoutLogger
# 设置 p6spy driver 代理
deregisterdrivers=true
# 取消JDBC URL前缀
useprefix=true
# 配置记录 Log 例外,可去掉的结果集有error,info,batch,debug,statement,commit,rollback,result,resultset.
excludecategories=info,debug,result,commit,resultset
# 日期格式
dateformat=yyyy-MM-dd HH:mm:ss
# 实际驱动可多个
driverlist=com.mysql.cj.jdbc.Driver
# 是否开启慢SQL记录
outagedetection=true
# 慢SQL记录标准 2 秒
outagedetectioninterval=2

pom所需要的依赖

  		<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.20</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
            <scope>provided</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>
        <!--sql分析打印依赖-->
        <!-- https://mvnrepository.com/artifact/p6spy/p6spy -->
        <dependency>
            <groupId>p6spy</groupId>
            <artifactId>p6spy</artifactId>
            <version>3.9.1</version>
        </dependency>

以上配置完成啦，开始测试

@SpringBootTest
class MybatisPlusTemplateApplicationTests {

    @Autowired
    private UserMapper userMapper;

    @Test
    void contextLoads() {
        List<User> users = userMapper.selectList(null);
        users.forEach(System.out::println);
    }
    //测试插入
    @Test
    public void testInsert(){
      User user=new User();
      user.setName("passersby复习mybatis-plus");
      user.setAge(18);
      user.setEmail("2320243015@qq.com");
      int result = userMapper.insert(user);
      System.out.println(result);
    }
    //测试更新
    @Test
    public void testUpdate(){
        User user=new User();
        user.setId(3L);
        user.setName("欧文");
        user.setEmail("2320243015@qq.com");
        user.setAge(1);
        int result = userMapper.updateById(user);
        System.out.println(result);
    }
    //测试删除
    @Test
    public void testDelete(){
        int result = userMapper.deleteById(2L);
        System.out.println(result);
    }
    /**
     *测试乐观锁成功
     */
    @Test
    void testOptimisticLocker(){
       	//查询用户
        User user = userMapper.selectById(3L);
        //修改用户
        user.setName("Mr.chen");
        user.setEmail("2320243015@qq.com");
        //更新用户
        int flag = userMapper.updateById(user);
        System.out.println(flag);
    }
    /**
     *测试乐观锁失败
     */
    @Test
    void testOptimisticLocker2(){
        //线程1
        User user = userMapper.selectById(1L);
        user.setName("Mr.lian111");
        user.setEmail("12345678@qq.com");
        //线程2
        User user2 = userMapper.selectById(1L);
        user2.setName("Mr.lian222");
        user2.setEmail("12345678@qq.com");
        userMapper.updateById(user2);
        userMapper.updateById(user);
    }
}

testOptimisticLocker执行一条进行测试
控制台打印结果 可看到我们sql语句执行的秒数是9毫秒 扛扛的

==>  Preparing: UPDATE user SET name=?, age=?, email=?, update_time=?, version=? WHERE id=? AND version=? AND deleted=0
==> Parameters: Mr.chen(String), 1(Integer), 2320243015@qq.com(String), 2022-03-19 22:40:47.404(Timestamp), 2(Integer), 3(Long), 1(Integer)
2022-03-19 22:40:47:430 | took 9ms | statement | connection 0
 UPDATE user  SET name='Mr.chen',
age=1,
email='2320243015@qq.com',

update_time='2022-03-19T22:40:47.404+0800',
version=2  WHERE id=3   AND version=1  AND deleted=0;
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5624657a]
1