本文共 10965 字,大约阅读时间需要 36 分钟。
官方网站:baomidou.com/
4.0.0 org.springframework.boot spring-boot-starter-parent 2.3.4.RELEASE //spring boot 版本号com.example mybatis-plus 0.0.1-SNAPSHOT mybatis-plus 1.8 org.springframework.boot spring-boot-starter org.springframework.boot spring-boot-starter-test test com.baomidou mybatis-plus-boot-starter 3.4.2 mysql mysql-connector-java runtime org.projectlombok lombok org.springframework.boot spring-boot-maven-plugin
spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://localhost:3306/yogurt?serverTimezone=Asia/Shanghai username: root password: root mybatis-plus: configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #开启SQL语句打印
import lombok.Data; import java.time.LocalDateTime; @Data public class User { private Long id; private String name; private Integer age; private String email; private Long managerId; private LocalDateTime createTime; }
public interface UserMapper extends BaseMapper{ }
@SpringBootApplication @MapperScan("com.example.mp.mappers") public class MybatisPlusApplication { public static void main(String[] args) { SpringApplication.run(MybatisPlusApplication.class, args); } }
DROP TABLE IF EXISTS user; CREATE TABLE user ( id BIGINT(20) PRIMARY KEY NOT NULL COMMENT '主键', name VARCHAR(30) DEFAULT NULL COMMENT '姓名', age INT(11) DEFAULT NULL COMMENT '年龄', email VARCHAR(50) DEFAULT NULL COMMENT '邮箱', manager_id BIGINT(20) DEFAULT NULL COMMENT '直属上级id', create_time DATETIME DEFAULT NULL COMMENT '创建时间', CONSTRAINT manager_fk FOREIGN KEY(manager_id) REFERENCES user (id) ) ENGINE=INNODB CHARSET=UTF8; INSERT INTO user (id, name, age ,email, manager_id, create_time) VALUES (1, '大BOSS', 40, 'boss@baomidou.com', NULL, '2021-03-22 09:48:00'), (2, '李经理', 40, 'boss@baomidou.com', 1, '2021-01-22 09:48:00'), (3, '黄主管', 40, 'boss@baomidou.com', 2, '2021-01-22 09:48:00'), (4, '吴组长', 40, 'boss@baomidou.com', 2, '2021-02-22 09:48:00'), (5, '小菜', 40, 'boss@baomidou.com', 2, '2021-02-22 09:48:00')
@SpringBootTest public class SampleTest { @Autowired private UserMapper mapper; @Test public void testSelect() { Listlist = mapper.selectList(null); assertEquals(5, list.size()); list.forEach(System.out::println); } }
注解 -> mp一共提供了8个注解,这些注解是用在Java的实体类上面的。
@TableName
-> 注解在类上,指定类和数据库表的映射关系。实体类的类名(转成小写后)和数据库表名相同时,可以不指定该注解。
@TableId
-> 注解在实体类的某一字段上,表示这个字段对应数据库表的主键。当主键名为id时(表中列名为id,实体类中字段名为id),无需使用该注解显式指定主键,mp会自动关联。若类的字段名和表的列名不一致,可用value
属性指定表的列名。另,这个注解有个重要的属性type
,用于指定主键策略。
@TableField
-> 注解在某一字段上,指定Java实体类的字段和数据库表的列的映射关系。这个注解有如下几个应用场景。
exist
属性为false
,这样在对实体对象进行插入时,会忽略这个字段。排除非表字段也可以通过其他方式完成,如使用static
或transient
关键字,但个人觉得不是很合理,不做赘述insertStrategy
,updateStrategy
,whereStrategy
属性进行配置,可以控制在实体对象进行插入,更新,或作为WHERE条件时,对象中的字段要如何组装到SQL语句中。fill
属性指定,字段为空时会进行自动填充@Version
-> 乐观锁注解@EnumValue
-> 注解在枚举字段上@TableLogic
-> 逻辑删除KeySequence
-> 序列主键策略(oracle
)InterceptorIgnore
-> 插件过滤规则Mapper CRUD接口
insert(T entity)
插入一条记录deleteById(Serializable id)
根据主键id删除一条记录delete(Wrapper<T> wrapper)
根据条件构造器wrapper进行删除selectById(Serializable id)
根据主键id进行查找selectBatchIds(Collection idList)
根据主键id进行批量查找selectByMap(Map<String,Object> map)
根据map中指定的列名和列值进行等值匹配查找selectMaps(Wrapper<T> wrapper)
根据 wrapper 条件,查询记录,将查询结果封装为一个Map,Map的key为结果的列,value为值selectList(Wrapper<T> wrapper)
根据条件构造器wrapper进行查询update(T entity, Wrapper<T> wrapper)
根据条件构造器wrapper进行更新updateById(T entity)
selectMaps
@Test public void test3() { QueryWrapperwrapper = new QueryWrapper<>(); wrapper.select("id","name","email").likeRight("name","黄"); List
// 按照直属上级进行分组,查询每组的平均年龄,最大年龄,最小年龄 /** select avg(age) avg_age ,min(age) min_age, max(age) max_age from user group by manager_id having sum(age) < 500; **/ @Test public void test3() { QueryWrapperwrapper = new QueryWrapper<>(); wrapper.select("manager_id", "avg(age) avg_age", "min(age) min_age", "max(age) max_age") .groupBy("manager_id").having("sum(age) < {0}", 500); List
selectCount
@Test public void test3() { QueryWrapperwrapper = new QueryWrapper<>(); wrapper.like("name", "黄"); Integer count = userMapper.selectCount(wrapper); System.out.println(count); }
条件构造器
eq
:equals,等于allEq
:all equals,全等于ne
:not equals,不等于gt
:greater than ,大于 >ge
:greater than or equals,大于等于≥lt
:less than,小于<le
:less than or equals,小于等于≤between
:相当于SQL中的BETWEENnotBetween
like
:模糊匹配。like(“name”,“黄”),相当于SQL的name like ‘%黄%’likeRight
:模糊匹配右半边。likeRight(“name”,“黄”),相当于SQL的name like ‘黄%’likeLeft
:模糊匹配左半边。likeLeft(“name”,“黄”),相当于SQL的name like ‘%黄’notLike
:notLike(“name”,“黄”),相当于SQL的name not like ‘%黄%’isNull
isNotNull
in
and
:SQL连接符ANDor
:SQL连接符ORapply
:用于拼接SQL,该方法可用于数据库函数,并可以动态传参// 案例先展示需要完成的SQL语句,后展示Wrapper的写法// 1. 名字中包含佳,且年龄小于25// SELECT * FROM user WHERE name like '%佳%' AND age < 25QueryWrapperwrapper = new QueryWrapper<>();wrapper.like("name", "佳").lt("age", 25);List users = userMapper.selectList(wrapper);// 下面展示SQL时,仅展示WHERE条件;展示代码时, 仅展示Wrapper构建部分// 2. 姓名为黄姓,且年龄大于等于20,小于等于40,且email字段不为空// name like '黄%' AND age BETWEEN 20 AND 40 AND email is not nullwrapper.likeRight("name","黄").between("age", 20, 40).isNotNull("email");// 3. 姓名为黄姓,或者年龄大于等于40,按照年龄降序排列,年龄相同则按照id升序排列// name like '黄%' OR age >= 40 order by age desc, id ascwrapper.likeRight("name","黄").or().ge("age",40).orderByDesc("age").orderByAsc("id");// 4.创建日期为2021年3月22日,并且直属上级的名字为李姓// date_format(create_time,'%Y-%m-%d') = '2021-03-22' AND manager_id IN (SELECT id FROM user WHERE name like '李%')wrapper.apply("date_format(create_time, '%Y-%m-%d') = {0}", "2021-03-22") // 建议采用{index}这种方式动态传参, 可防止SQL注入 .inSql("manager_id", "SELECT id FROM user WHERE name like '李%'");// 上面的apply, 也可以直接使用下面这种方式做字符串拼接,但当这个日期是一个外部参数时,这种方式有SQL注入的风险wrapper.apply("date_format(create_time, '%Y-%m-%d') = '2021-03-22'");// 5. 名字为王姓,并且(年龄小于40,或者邮箱不为空)// name like '王%' AND (age < 40 OR email is not null)wrapper.likeRight("name", "王").and(q -> q.lt("age", 40).or().isNotNull("email"));// 6. 名字为王姓,或者(年龄小于40并且年龄大于20并且邮箱不为空)// name like '王%' OR (age < 40 AND age > 20 AND email is not null)wrapper.likeRight("name", "王").or( q -> q.lt("age",40) .gt("age",20) .isNotNull("email") );// 7. (年龄小于40或者邮箱不为空) 并且名字为王姓// (age < 40 OR email is not null) AND name like '王%'wrapper.nested(q -> q.lt("age", 40).or().isNotNull("email")) .likeRight("name", "王");// 8. 年龄为30,31,34,35// age IN (30,31,34,35)wrapper.in("age", Arrays.asList(30,31,34,35));// 或wrapper.inSql("age","30,31,34,35");// 9. 年龄为30,31,34,35, 返回满足条件的第一条记录// age IN (30,31,34,35) LIMIT 1wrapper.in("age", Arrays.asList(30,31,34,35)).last("LIMIT 1");// 10. 只选出id, name 列 (QueryWrapper 特有)// SELECT id, name FROM user;wrapper.select("id", "name");// 11. 选出id, name, age, email, 等同于排除 manager_id 和 create_time// 当列特别多, 而只需要排除个别列时, 采用上面的方式可能需要写很多个列, 可以采用重载的select方法,指定需要排除的列wrapper.select(User.class, info -> { String columnName = info.getColumn(); return !"create_time".equals(columnName) && !"manager_id".equals(columnName); });
自定义SQL -> 原生mybatis
public interface UserMapper extends BaseMapper{ @Select("select * from user") List selectRaw();}
public interface UserMapper extends BaseMapper{ List selectRaw();}
使用xml时,若xml文件与mapper接口文件不在同一目录下,则需要在application.yml中配置mapper.xml的存放路径
mybatis-plus: mapper-locations: /mappers/*
分页查询
@Configuration public class MybatisPlusConfig { /** 新版mp **/ @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL)); return interceptor; } /** 旧版mp 用 PaginationInterceptor **/ }
@Test public void testPage() { LambdaQueryWrapperwrapper = new LambdaQueryWrapper<>(); wrapper.ge(User::getAge, 28); // 设置分页信息, 查第3页, 每页2条数据 Page page = new Page<>(3, 2); // 执行分页查询 Page userPage = userMapper.selectPage(page, wrapper); System.out.println("总记录数 = " + userPage.getTotal()); System.out.println("总页数 = " + userPage.getPages()); System.out.println("当前页码 = " + userPage.getCurrent()); // 获取分页查询结果 List records = userPage.getRecords(); records.forEach(System.out::println); }
自动填充
@TableField
设置自动填充public class User2 { private Long id; private String name; private Integer age; private String email; private Long managerId; @TableField(fill = FieldFill.INSERT) // 插入时自动填充 private LocalDateTime createTime; @TableField(fill = FieldFill.UPDATE) // 更新时自动填充 private LocalDateTime updateTime; private Integer version; private Integer deleted; }
@Component //需要注册到Spring容器中 public class MyMetaObjectHandler implements MetaObjectHandler { @Override public void insertFill(MetaObject metaObject) { // 插入时自动填充 // 注意第二个参数要填写实体类中的字段名称,而不是表的列名称 strictFillStrategy(metaObject, "createTime", LocalDateTime::now); } @Override public void updateFill(MetaObject metaObject) { // 更新时自动填充 strictFillStrategy(metaObject, "updateTime", LocalDateTime::now); } }
转载地址:http://ljyki.baihongyu.com/