-
创建数据库
DROP TABLE IF EXISTS user; CREATE TABLE user ( id BIGINT(20) NOT NULL COMMENT '主键ID', name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名', age INT(11) NULL DEFAULT NULL COMMENT '年龄', email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱', PRIMARY KEY (id) ); INSERT INTO user (id, name, age, email) VALUES (1, 'Jone', 18, 'test1@baomidou.com'), (2, 'Jack', 20, 'test2@baomidou.com'), (3, 'Tom', 28, 'test3@baomidou.com'), (4, 'Sandy', 21, 'test4@baomidou.com'), (5, 'Billie', 24, 'test5@baomidou.com');
-
新建个springboot项目,导入依赖
<dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <!-- 数据库驱动--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <!-- mybatis-plus--> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.0.5</version> </dependency>
尽量不要同时导入Mybatis,Mybatis-plus
-
数据库连接配置
# mysql5 spring.datasource.username=root spring.datasource.password=123456 spring.datasource.url=jdbc:mysql://localhost:3306/mybatis_plus?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC spring.datasource.driver-class-name=com.mysql.jdbc.Driver
-
pojo,mapper,使用
@Data @AllArgsConstructor @NoArgsConstructor public class User { private int id; private String name; private int age; private String email; } @Repository // 代表持久层 public interface UserMapper extends BaseMapper<User> { // 所有的crud操作都编写完成了 } // 继承了BaseMapper,也可以编写自己的扩展方法 @Autowired private UserMapper userMapper; @Test void contextLoads() { List<User> userList = userMapper.selectList(null); for (int i = 0; i < userList.size(); i++) { System.out.println(userList.get(i)); } }
在SpringBoot启动类需要扫描mapper包
// 扫描mapper文件夹 @MapperScan("com.henry.mapper")
配置日志
现在所有的sql是不可见的,要看到怎么执行的,需要看日志
# 配置日志
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
CRUD扩展
插入操作
// 测试插入 @Test public void testInsert() { User user = new User(); user.setName("ui"); user.setAge(11); user.setEmail("8978@qq.com"); // 也可以通过User的构造函数n int insert = userMapper.insert(user); System.out.println(insert); }
没有设置id,系统会自己设置
主键生成策略
主键自增,uuid,雪花算法、redis生成
雪花算法
snowflake是Twitter开源的分布式ID生成算法,结果是一个long型的ID
主键自增
需要配置主键自增
-
实体类字段上
@TableId(type=IdType.AUTO)
-
数据库对应的字段设置自增
public enum IdType { AUTO(0), // 数据库id自增 NONE(1), // 不设置 INPUT(2), // 手动输入,需要自己写id ID_WORKER(3), // 全局唯一id UUID(4), // uuid ID_WORKER_STR(5); }
更新操作
@Test public void testUpdate() { User user = new User(); user.setId(6); user.setName("aui"); user.setAge(12); user.setEmail("89728@qq.com"); int count = userMapper.updateById(user); System.out.println(count); }
所有的sql都是动态配置的
字段填充
创建时间,修改时间需要自动获取
方式一:数据库级别
修改pojo
@Data @AllArgsConstructor @NoArgsConstructor public class User { @TableId(type=IdType.AUTO) private int id; private String name; private int age; private String email; private Date createTime; private Date updateTime; }
方式二:代码级别
-
创建cretetime和updatetime字段,无默认值
-
在实体类字段属性上加注释
// 自动填充内容 @TableField(fill = FieldFill.INSERT) private LocalDateTime createtime; @TableField(fill = FieldFill.INSERT_UPDATE) private LocalDateTime updatetime;
-
编写MyMetaObjectHandler
@Slf4j @Component public class MyMetaObjectHandler implements MetaObjectHandler { // 插入时的填充策略 @Override public void insertFill(MetaObject metaObject) { log.info("开始插入"); this.strictInsertFill(metaObject, "createtime", LocalDateTime.class, LocalDateTime.now()); this.strictUpdateFill(metaObject, "updatetime", LocalDateTime.class, LocalDateTime.now()); } // 更新时的填充策略 @Override public void updateFill(MetaObject metaObject) { log.info("start update fill ...."); this.strictUpdateFill(metaObject, "updatetime", LocalDateTime.class, LocalDateTime.now()); } }
乐观锁
乐观锁实现方式:
-
取出记录时,获取当前version
-
更新时,带上这个version
-
执行更新时, set version = newVersion where version = oldVersion
-
如果version不对,就更新失败
-
给数据库增加version字段默认值0
-
实体类增加对应的version字段
@Version // 乐观锁version注解 private int version;
-
注册组件
// 注册乐观锁插件 @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor(); mybatisPlusInterceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor()); return mybatisPlusInterceptor; }
查询操作
// 单个查询 User user = userMapper.selectById(1); System.out.println(user); // 批量查询selectBatchIds ArrayList<Integer> arrayList = new ArrayList<>(); arrayList.add(1); arrayList.add(2); List<User> userList = userMapper.selectBatchIds(arrayList); for (int i = 0; i < userList.size(); i++) { System.out.println(userList.get(i)); } // 按条件查询selectByMap HashMap<String,Object> map = new HashMap<>(); map.put("name","AAA"); List<User> userList1 = userMapper.selectByMap(map); for (int i = 0; i < userList1.size(); i++) { System.out.println(userList1.get(i)); }
分页查询
-
配置分页插件
@Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor(); // 乐观锁 mybatisPlusInterceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor()); // 分页 mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor()); return mybatisPlusInterceptor; }
-
直接使用page对象
public void testPage() { // 参数一:当前页 // 参数二:页面大小 Page<User> page = new Page<>(1,3); Page<User> userPage = userMapper.selectPage(page, null); List<User> records = userPage.getRecords(); for (User record : records) { System.out.println(record); } System.out.println(page.getTotal()); } image-20210208170529182
删除操作
和查询类似
public void testDelete() { // id删除 userMapper.deleteById(15); // 删除多个 ArrayList<Integer> list = new ArrayList<>(); list.add(5); list.add(6); userMapper.deleteBatchIds(list); // 根据条件删除 HashMap<String, Object> map = new HashMap<>(); map.put("name","AAA"); userMapper.deleteByMap(map); }
逻辑删除
-
在表中增加deleted字段,默认0
-
实体类增加属性
@TableLogic private int deleted;
-
MyBatisPlus 在3.3版本之上,无需添加组件
添加配置
# 配置逻辑删除 mybatis-plus.global-config.db-config.logic-delete-value=1 mybatis-plus.global-config.db-config.logic-not-delete-value=0
-
userMapper.deleteById(4);
查询的时候会自动添加deleted=0
性能分析插件
-
p6spy 依赖引入
<dependency> <groupId>p6spy</groupId> <artifactId>p6spy</artifactId> <version>最新版本</version> </dependency>
-
修改数据库连接配置
spring.datasource.url=jdbc:p6spy:mysql://localhost:3306/mybatis_plus?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver
-
编写spy.properties配置文件
#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 # 自定义日志打印 logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger #日志输出到控制台 appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger # 使用日志系统记录 sql #appender=com.p6spy.engine.spy.appender.Slf4JLogger # 设置 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=org.h2.Driver # 是否开启慢SQL记录 outagedetection=true # 慢SQL记录标准 2 秒 outagedetectioninterval=2
条件构造器
wrapper
查询多个--selectlist
// 查询name不为空,email不为空,age大于22的 QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.isNotNull("name").isNotNull("email").ge("age",22); List<User> userList = userMapper.selectList(wrapper); for (User user : userList) { System.out.println(user);
eq等于,ne不等于,ge大于等于,le小于等于
QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.eq("name","Tom"); // 查询一个 User user = userMapper.selectOne(wrapper); System.out.println(user);
between
// 查询年龄在20-22之间的用户,也可以用大于等于(ge)20且小于等于22(le) QueryWrapper<User> wrapper = new QueryWrapper<>(); // wrapper.ge("age",20).le("age",22) wrapper.between("age",20,22); Integer count = userMapper.selectCount(wrapper); System.out.println(count);
like
QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.like("name","o").notLike("name","j").likeRight("name","T").likeLeft("name","m"); List<User> userList = userMapper.selectList(wrapper);
子查询in,notIn ,inSql, notInSql
QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.inSql("id","select id from user where id < 3"); List<Object> objects = userMapper.selectObjs(wrapper);
排序orderby
QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.orderByAsc("age").orderByDesc("name"); List<User> userList = userMapper.selectList(wrapper);
代码生成器
// 需要构建一个 代码自动生成器 对象 AutoGenerator mpg = new AutoGenerator(); // 配置策略 // 1、全局配置 GlobalConfig gc = new GlobalConfig(); String projectPath = System.getProperty("user.dir"); gc.setOutputDir(projectPath+"/src/main/java"); gc.setAuthor("狂神说"); gc.setOpen(false); gc.setFileOverride(false); // 是否覆盖 gc.setServiceName("%sService"); // 去Service的I前缀 gc.setIdType(IdType.ID_WORKER); gc.setDateType(DateType.ONLY_DATE); gc.setSwagger2(true); mpg.setGlobalConfig(gc); //2、设置数据源 DataSourceConfig dsc = new DataSourceConfig(); dsc.setUrl("jdbc:mysql://localhost:3306/kuang_community? useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8"); dsc.setDriverName("com.mysql.cj.jdbc.Driver"); dsc.setUsername("root"); dsc.setPassword("123456"); dsc.setDbType(DbType.MYSQL); mpg.setDataSource(dsc); //3、包的配置 PackageConfig pc = new PackageConfig(); //只需要改实体类名字 和包名 还有 数据库配置即可 pc.setModuleName("blog"); pc.setParent("com.kuang"); pc.setEntity("entity"); pc.setMapper("mapper"); pc.setService("service"); pc.setController("controller"); mpg.setPackageInfo(pc); //4、策略配置 StrategyConfig strategy = new StrategyConfig(); --------------------------------- // 设置要映射的表名 strategy.setInclude("blog_tags","course","links","sys_settings","user_record"," user_say"); strategy.setNaming(NamingStrategy.underline_to_camel); strategy.setColumnNaming(NamingStrategy.underline_to_camel); strategy.setEntityLombokModel(true); // 自动lombok; strategy.setLogicDeleteFieldName("deleted"); // 自动填充配置 TableFill gmtCreate = new TableFill("gmt_create", FieldFill.INSERT); TableFill gmtModified = new TableFill("gmt_modified", FieldFill.INSERT_UPDATE); ArrayList<TableFill> tableFills = new ArrayList<>(); tableFills.add(gmtCreate); tableFills.add(gmtModified); strategy.setTableFillList(tableFills); // 乐观锁 strategy.setVersionFieldName("version"); strategy.setRestControllerStyle(true); strategy.setControllerMappingHyphenStyle(true); mpg.setStrategy(strategy); mpg.execute(); //执行 }