MyBatis-plus进阶之映射与条件构造器

MyBatis-plus进阶映射自动映射规则表名和实体类名映射-》 表名user 实体类名User字段名和实体类属性名映射-》字段名name 实体类属性名name这里需要注意User类里的 private String name 这个name不是属性名而是变量名。那么什么是属性名呢类里面的属性对应的get方法其方法名去掉“get”然后首字母小写对应的就是属性名比如// 属性名是name 映射到数据库的字段是name public String getName() { return name; } // 属性名是testName 映射到数据库的字段是testName public String getTestName() { return name; }字段名下划线命名方式和实体类属性小驼峰命名方式映射-》字段名 user_email 实体类属性名 userEmailMyBatis-plus支持这种映射规则可以通过配置来设置mybatis-plus: configuration: # 开启下划线到驼峰命名的映射 map-underscore-to-camel-case: true表映射TableName注解Pojo类通过TableName注解指定映射的数据库表名就会完成表名的映射。TableName注解主要应用在类名和数据库表名不一致的情况。// 表示User类映射到t_user表 TableName(t_user) public class User { private Long id; public String getName() { return name; } private String name; private Integer age; private String email; }那如果数据库表特别多的情况下这样每个类都指定具体表名会很麻烦此时就可以开启表映射的全局配置。表映射的全局配置mybatis-plus: global-config: db-config: # 全局配置表名的前缀这样会自动将Pojo类名的首字母小并且加上前缀t_ # 这样就不用使用TableName来指定表名 table-prefix: t_字段映射当数据库字段和表实体类的属性不一致时可以使用TableField注解改变字段和属性的映射让注解中的名称和表字段保持一致。TableField(user_email) private String email;此时的sql是select id,user_email as email from user有一个问题场景当数据库表中的某个字段设置为desc时注意desc为数据库的关键字此时查询的sql语句就是select id,name,desc from user很明显这样的sql执行时会报错。那么怎么解决呢给关键字加上分隔符就能执行成功select id,name,desc from user在Java中 给desc这个属性加上注解TableField(desc) 也就是字段名加上分隔符就可以了。字段失效当数据库中有字段不想被查询时可以使用TableField(select false)来隐藏这个字段。在拼接sql语句的时候就不会拼接这个字段// 查询时不会查出Email这个字段 TableField(select false) private String email;视图属性实际开发中有些字段不需要数据库存储但需要展示像这种实体类中存在但是数据库中不存在的字段叫做视图字段。这种字段可以通过TableField(exist false)注解来去掉这个字段不让他作为查询或DML字段。TableField(exist false) private String emailPhone;条件构造器MyBatis-plus的条件查询是基于面向对象的思想条件查询使用对象来完成封装。在实际开发中只需要关注QueryWrapper和LambdaQueryWrapper这两个即可。等值查询eq 方法下面的两种查询方式的结果是一样的。// 使用 QueryWrapper QueryWrapperUser wrapper new QueryWrapper(); // 设置条件 name‘test2’ wrapper.eq(name, test2); User user1 userService.getOne(wrapper); System.out.println(user1); // 使用LambdaQueryWrapper这种方式可以避免字段名写错 LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); lambdaQueryWrapper.eq(User::getName, test2); User user2 userService.getOne(lambdaQueryWrapper); System.out.println(user2);null的判断当前端传来的查询条件为null时。此时用户希望不需要以此条件进行查询我们就不需要进行查询拼接。可以使用下面的方式进行判断处理LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); String name test2; // 当name为null或者“”时不作为查询拼接 lambdaQueryWrapper.eq(StringUtils.isNotBlank(name),User::getName, name); User user2 userService.getOne(lambdaQueryWrapper); System.out.println(user2);多条件查询第一种方式可以通过多个eq 来构建多条件查询LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); String name test2; lambdaQueryWrapper.eq(User::getName, name); lambdaQueryWrapper.eq(User::getAge, 12); User user2 userService.getOne(lambdaQueryWrapper); System.out.println(user2);此时对应的sql是select * from t_user where nametest2 and age 12第二种方式allEq 方法通过将查询条件封装在一个map中然后调用allEq方法实现多条件查询HashMapString, Object hashMap new HashMap(); hashMap.put(name, test2); hashMap.put(age, 12); QueryWrapperUser queryWrapper new QueryWrapper(); // 第二个参数表示当map中的值为null时不作为查询条件进行拼接 queryWrapper.allEq(hashMap,false); User user3 userService.getOne(lambdaQueryWrapper); System.out.println(user3);ne 不等值查询LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); String name test2; lambdaQueryWrapper.ne(User::getName, name); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList);对应的sqlselect * from t_user where name test2范围查询gt 大于LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); lambdaQueryWrapper.gt(User::getAge, 12); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList);对应的sqlselect * from t_user where age 12ge 大于等于LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); lambdaQueryWrapper.ge(User::getAge, 12); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList);对应的sqlselect * from t_user where age 12lt 小于LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); lambdaQueryWrapper.lt(User::getAge, 12); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList);对应的sqlselect * from t_user where age 12le 小于等于LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); lambdaQueryWrapper.le(User::getAge, 12); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList);对应的sqlselect * from t_user where age 12between 范围内LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); // 查询年龄在12-36之间 lambdaQueryWrapper.between(User::getAge, 12,36); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList);对应的sqlselect * from t_user where age between 12 and 36 查询结果包含年龄为12和36的数据。notBetween 范围外LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); // 查询年龄不在12-36之间 lambdaQueryWrapper.between(User::getAge, 12,36); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList);对应的sqlselect * from t_user where age not between 12 and 36模糊查询likeLambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); lambdaQueryWrapper.like(User::getName, 张); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList);对应的sqlselect * from t_user where name like %张%notLikeLambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); lambdaQueryWrapper.notLike(User::getName, 张); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList);对应的sqlselect * from t_user where name not like %张%likeLeftLambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); lambdaQueryWrapper.likeLeft(User::getName, 张); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList);对应的sqlselect * from t_user where name like %张 查询name以’张‘结尾likeRightLambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); lambdaQueryWrapper.likeRight(User::getName, 张); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList);对应的sqlselect * from t_user where name like 张% 查询name以’张‘开头判空查询isNullLambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); lambdaQueryWrapper.isNull(User::getEmail); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList);对应的sqlselect * from t_user where email is nullisNotNullLambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); lambdaQueryWrapper.isNotNull(User::getEmail); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList);对应的sqlselect * from t_user where email is not null包含查询 inLambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); ArrayListInteger arrayList new ArrayList(); Collections.addAll(arrayList, 21, 22, 23); lambdaQueryWrapper.in(User::getAge, arrayList); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList);对应的sqlselect * from t_user where age in (21,22,23)不包含查询 notInLambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); ArrayListInteger arrayList new ArrayList(); Collections.addAll(arrayList, 21, 22, 23); lambdaQueryWrapper.notIn(User::getAge, arrayList); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList);对应的sqlselect * from t_user where age not in (21,22,23)包含查询 inSqlLambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); // 查询条件直接写死 // lambdaQueryWrapper.inSql(User::getAge, 11,12,13); // 查询条件时一条sql lambdaQueryWrapper.inSql(User::getAge, select age from k_user where age 20); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList);对应的sqlselect * from t_user where age in (11,12,13)​ select * from t_user where age in (select age from k_user where age 20)不包含查询 notInSql调用方式与上面类似这里不再举例分组查询 groupByQueryWrapperUser wrapper new QueryWrapper(); // 分组字段 wrapper.groupBy(age); // 查询字段 wrapper.select(age,count(*) as field_count); ListMapString, Object mapList userService.listMaps(wrapper); System.out.println(mapList);对应的sql select age,count(*) as field_count from t_user group by age聚合查询 havingQueryWrapperUser wrapper new QueryWrapper(); // 分组字段 wrapper.groupBy(age); // 查询字段 wrapper.select(age,count(*) as field_count); // 聚合条件筛选 wrapper.having(field_count 10); ListMapString, Object mapList userService.listMaps(wrapper); System.out.println(mapList);对应的sql select age,count(*) as field_count from t_user group by age having field_count 10排序查询orderByAsc 升序LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); lambdaQueryWrapper.orderByAsc(User::getAge,User::getId); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList); // 对应的sql select * from t_user order by age asc,id ascorderByDesc 降序LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); lambdaQueryWrapper.orderByDesc(User::getAge,User::getId); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList); // 对应的sql select * from t_user order by age desc,id descorderBy 有升序有降序LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); // 参数1如果排序的字段值为null是否还参与排序 // 参数2是否是升序 // 参数3排序的字段 lambdaQueryWrapper.orderBy(true,true,User::getAge); lambdaQueryWrapper.orderBy(true,false,User::getId); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList); // 对应的sql select * from t_user order by age asc,id desc内嵌逻辑查询 funcLambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); // 根据不同情况来选择拼接不同的查询条件 lambdaQueryWrapper.func(new ConsumerLambdaQueryWrapperUser() { Override public void accept(LambdaQueryWrapperUser userLambdaQueryWrapper) { if (11){ userLambdaQueryWrapper.eq(User::getAge,22); }else { userLambdaQueryWrapper.ne(User::getAge,22); } } }); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList); // 对应的sqlselect * from t_user where age 22逻辑查询and正常拼接默认就是and比如:LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); // 根据不同情况来选择拼接不同的查询条件 lambdaQueryWrapper.gt(User::getAge,12).lt(User::getAge,22); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList); // 对应的sqlselect * from t_user where age 12 and age 22and的嵌套查询LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); lambdaQueryWrapper.eq(User::getName, 张三).and(u - u.gt(User::getAge, 22).or().lt(User::getAge, 20)); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList); // 对应的sqlselect * from t_user where name 张三 and (age 22 or age 20)orLambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); lambdaQueryWrapper.gt(User::getAge, 22).or().lt(User::getAge, 20); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList); // 对应的sqlselect * from t_user where age 22 or age 20or 的嵌套查询LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); lambdaQueryWrapper.eq(User::getName, 张三).or(u-u.gt(User::getAge,20).lt(User::getAge,30)); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList); // 对应的sqlselect * from t_user where name 张三 or (age 20 and age 30)nested.nested()用于嵌套条件分组的核心方法作用是将一组条件用()包裹起来形成独立的条件块避免多个条件之间的逻辑运算优先级混乱。需求查询age 20且name 张三或email 包含 test的用户。LambdaQueryWrapperUser wrapper new LambdaQueryWrapperUser() .gt(User::getAge, 20) // 外层条件age20 .nested(qw - qw // 嵌套分组(name张三 OR email包含test) .eq(User::getName, 张三) .or() .like(User::getEmail, test) ); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList); // 对应的sqlselect * from t_user WHERE age 20 AND (name 张三 OR email LIKE %test%)自定义查询 applyapply方法的参数是字符串会跟在sql语句where的后面在复杂查询的应用场景中使用。LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); lambdaQueryWrapper.apply(id1); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList); // 对应的sqlselect * from t_user where id 1last 查询last方法允许你直接在查询的最后添加一个 SQL 片段。LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); lambdaQueryWrapper.last(limit 0,10); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList); // 对应的sqlselect * from t_user limit 0,10注意last方法只能调用一次多次调用将以最后一次为准。比如LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); lambdaQueryWrapper.last(where age 22); lambdaQueryWrapper.last(and name 张三); lambdaQueryWrapper.last(limit 0,10); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList); // 对应的sqlselect * from t_user limit 0,10exists查询exists方法用于在查询中添加一个 EXISTS 子查询。通过调用exists方法可以将一个完整的 SQL 子查询作为 EXISTS 条件添加到主查询中。exists表示当子查询有返回结果时才执行主查询如果子查询没有返回结果那么不执行主查询。LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); lambdaQueryWrapper.exists(select id from t_user where age 1); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList); // 对应的sqlselect * from t_user WHERE EXISTS (select id from t_user where age 1)notExists查询LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); lambdaQueryWrapper.notExists(select id from t_user where age 1); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList); // 对应的sqlselect * from t_user WHERE NOT EXISTS (select id from t_user where age 1)select查询select方法用于设置查询的字段。通过调用select方法可以指定在查询结果中包含哪些字段从而实现字段级别的查询定制。LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper(); lambdaQueryWrapper.select(User::getId, User::getName, User::getAge); ListUser userList userService.list(lambdaQueryWrapper); System.out.println(userList); // 对应的sqlselect id,name,age from t_user