架构如下
P6SpyLogger 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实体类{ }
@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=2pom所需要的依赖
org.springframework.boot spring-boot-starter-web mysql mysql-connector-java 8.0.20 org.projectlombok lombok 1.18.12 provided com.baomidou mybatis-plus-boot-starter 3.4.2 p6spy p6spy 3.9.1
以上配置完成啦，开始测试
@SpringBootTest
class MybatisPlusTemplateApplicationTests {
@Autowired
private UserMapper userMapper;
@Test
void contextLoads() {
List 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
