三、进阶篇
3.1映射
3.1.1自动映射规则
- 表名和实体类名映射 -> 表名user 实体类名User
- 字段名和实体类属性名映射 -> 字段名name 实体类属性名name
- 字段名下划线命名方式和实体类属性驼峰命名方式映射 -> 字段名 user_email 实体类属性名 userEmail
开启驼峰命名
mybatis-plus.configuration.map-underscore-to-camel-case=true
3.1.2表映射
- 通过@TableName()注解指定映射的数据库表名,就会按照指定的表名进行映射
- 如:此时将数据库的表名改为t_user,要完成表名和实体类名的映射,需要将实体类名也要指定为t_user
@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName("t_user")
public class User {private Long id;private String name;private Integer age;private String email;
}
- 如果有很多实体类,对应到数据库表中的很多表,我们不需要每个依次配置,只需要配置一个全局的设置,他都会给每个实体类名前面添加指定的前缀。
#表映射全局配置
mybatis-plus.global-config.db-config.table-prefix=t_
3.1.3字段映射
- 什么场景下会改变字段映射呢?
场景一
-
当数据库字段和表实体类的属性不一致时,我们可以使用@TableField()注解改变字段和属性的映射,让注解中的名称和表字段保持一致。
-
如:此时数据库字段的名称我们改为username,在根据实体类的属性拼接SQL的使用,就会使用@TableField()中指定的名称username进行拼接,完成查询
@TableField("username")private String name;
- 此时的SQL语句是这样的
SELECT id,username AS name,email FROM t_user
场景二
- 数据库字段和表实体类的属性一致,框架在拼接SQL语句的时候,会使用属性名称直接拼接SQL语句,例如:
SELECT id,username AS name,age,email,desc FROM t_user
- 这条语句直接进行查询的时候,会出现错误:
Error querying database. Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘desc FROM t_user’ at line 1
- 原因是desc属于关键字,不能直接用于SQL查询,要解决这个问题,就需要将desc字段加上符号,将他变为非关键字,才能完成查询。这个问题的根本也是改变生成的SQL语句的字段名称,也就是我们需要通过@TableField()改变实体类的属性名称,将desc变为
desc
,就可以解决这个问题
@TableField("`desc`")
private String desc;
3.1.4字段失效
- 当数据库中有字段不希望被查询,我们可以通过@Table(select=false)来隐藏这个字段,拿在拼接SQL语句的时候,就不会拼接这个字段。
- 如:如果不想显示年龄信息,那么可以在age属性上添加这个注解来隐藏这个字段
@TableField(select = false)
private Integer age;
- 通过查询生成的SQL语句,发现并没有拼接age字段
3.1.5视图属性
-
在实际开发中,有些字段不需要数据库存储,但是却需要展示,需要展示也就是意味着实体类中需要存在这个字段,我们称这些实体类中存在但是数据库中不存在这个字段,叫做视图字段。
-
我们可以通过@TableField(exist=false)来去掉这个字段,不让他作为查询字段。
@TableField(exist = false)
private Integer online;
- 通过查询生成的SQL语句,发现并没有online字段
3.2条件构造器
3.3等值查询
3.3.1eq
- 使用QueryWrapper对象,构建查询条件
@Test
void eq() {//1.创建条件查询对象QueryWrapper<User> queryWrapper = new QueryWrapper<>();//2.设置查询条件,指定查询的字段和匹配的值queryWrapper.eq("username","Jack");//3.进行条件查询User user = userMapper.selectOne(queryWrapper);System.out.println(user);
}
- 测试效果
外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传
- 如果我们每次都是自己进行字段名称的编写,有可能会出现名称写错的情况,怎么避免这种情况呢,我们可以使用LambdaQueryWrapper对象,在构建字段时,使用方法引用的方式来选择字段,这样做可以避免字段拼写错误。
@Test
void eq2() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.eq(User::getName,"Jack");User user = userMapper.selectOne(lambdaQueryWrapper);System.out.println(user);
}
- 我们的构建条件是从哪里来的?应该是从客户端通过请求发送过来的,由服务器接收的。在网站中一般都会有多个条件入口,用户可以选择一个或多个条件进行查询,那这个时候在请求时,我们不能确定所有的条件都是有值的,部分条件可能用户没有传值,那么该条件就为null。
- 那么null的条件,我们是不需要进行查询条件拼接的,否则就会出现如下情况,将为null的条件进行拼接,筛选后无法查询出结果。
@Test
void isNull() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.eq(User::getName,null);User user = userMapper.selectOne(lambdaQueryWrapper);System.out.println(user);
}
- 我们要解决这个问题,可以先判断是否为空,根据判断结果选择是否拼接该字段。
@Test
void isNUll2() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();//String username="Jone";String username=null;lambdaQueryWrapper.eq(username!=null,User::getName,username);//User user = userMapper.selectOne(lambdaQueryWrapper);List<User> user = userMapper.selectList(lambdaQueryWrapper);System.out.println(user);
}
3.3.2allEq
- 如何通过多个eq,构建多条件查询
@Test
void allEq1() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.eq(User::getName,"Tom");lambdaQueryWrapper.eq(User::getAge,28);User user = userMapper.selectOne(lambdaQueryWrapper);System.out.println(user);
}
- 如果此时有多个条件需要同时判断,我们可以将这多个条件放入到Map集合中,更加的方便。
@Test
void allEq2() {//1.构建条件MapHashMap<String, Object> map = new HashMap<>();map.put("username","Tom");//map.put("age",28);map.put("age",null);//2.创建QueryWrapper对象QueryWrapper<User> queryWrapper = new QueryWrapper<>();//3.使用条件完成查询queryWrapper.allEq(map,false);User user = userMapper.selectOne(queryWrapper);System.out.println(user);
}
- 参数params:表示传递的Map集合
- 参数null2IsNull:表示对于为null的条件是否判断为isNull
3.3.3ne
- 不等ne
@Test
void ne() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.ne(User::getName,"Tom");List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
3.4范围查询
3.4.1gt
@Test
void gt() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.gt(User::getAge,20);List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
3.4.2ge
@Test
void ge() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.ge(User::getAge,20);List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
3.4.3lt
@Test
void lt() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.lt(User::getAge,20);List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
3.4.4le
@Test
void le() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.le(User::getAge,20);List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
3.4.5between
@Test
void between() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.between(User::getAge,20,28);List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
3.4.6notBetween
@Test
void notBetween() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.notBetween(User::getAge,20,28);List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
3.5模糊查询
@Test
void like() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.like(User::getName,"J");List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}@Test
void notLike() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.notLike(User::getName,"J");List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}@Test
void likeLeft() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.likeLeft(User::getName,"e");List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}@Test
void likeRight() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.likeRight(User::getName,"J");List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
3.6判空查询
Test
void isNull() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.isNull(User::getDesc);List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}@Test
void isNotNull() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.isNotNull(User::getName);List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
3.7包含查询
3.7.1in
@Test
void in() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.in(User::getAge, 18, 20, 21);List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
@Test
void in2() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();ArrayList<Integer> list = new ArrayList<>();//Collections.addAll(list,18,20,21);list.add(18);list.add(20);list.add(21);lambdaQueryWrapper.in(User::getAge, list);List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
3.7.2notIn
@Test
void notIn() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.notIn(User::getAge, 18, 20, 21);List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
@Test
void notIn2() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();ArrayList<Integer> list = new ArrayList<>();Collections.addAll(list,18,20,21);lambdaQueryWrapper.notIn(User::getAge,list);List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
3.7.3inSql
@Test
void inSql() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.inSql(User::getAge, "18,20,21");List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
@Test
void inSql2() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.inSql(User::getAge, "select age from t_user where age>=20");List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
3.7.4notInSql
@Test
void notInSql() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.notInSql(User::getAge, "18,20,21");List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
@Test
void notInSql2() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.notInSql(User::getAge, "select age from t_user where age>20");List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
3.8分组查询
@Test
void groupBy() {QueryWrapper<User> queryWrapper = new QueryWrapper<>();//分组字段queryWrapper.groupBy("age");//查询字段queryWrapper.select("age,count(*) as field_count");List<User> users = userMapper.selectList(queryWrapper);System.out.println(users);
}
3.9聚合查询
@Test
void having() {QueryWrapper<User> queryWrapper = new QueryWrapper<>();//分组字段queryWrapper.groupBy("age");//查询字段queryWrapper.select("age,count(*) as field_count");//聚合字段queryWrapper.having("field_count>1");List<User> users = userMapper.selectList(queryWrapper);System.out.println(users);
}
3.10排序查询
@Test
void orderByAsc() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.orderByAsc(User::getAge,User::getId);List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
@Test
void orderByDesc() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.orderByDesc(User::getAge,User::getId);List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
@Test
void orderBy() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();/*** oderBy:设置排序字段和排序的方式* 参数一:如果排序字段的值为null,是否还要作为排序字段参与排序* 参数二:是否升序排序* 参数三:排序字段*/lambdaQueryWrapper.orderBy(true, true, User::getAge);lambdaQueryWrapper.orderBy(true, false, User::getId);List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
3.11func查询
@Testvoid func() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();//可能会根据不同的情况来选择进行拼接不同的查询条件/*lambdaQueryWrapper.func(new Consumer<LambdaQueryWrapper<User>>() {@Overridepublic void accept(LambdaQueryWrapper<User> userLambdaQueryWrapper) {if (true){userLambdaQueryWrapper.eq(User::getId,1);}else {userLambdaQueryWrapper.ne(User::getId,1);}}});*///使用lambda表达式lambdaQueryWrapper.func(i -> {if (true) {i.eq(User::getId, 1);} else {i.ne(User::getId, 1);}});List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}
3.12逻辑查询
3.12.1and
- 正常拼接默认就是and,例如
@Test
void and() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.gt(User::getAge,20).lt(User::getAge,30);List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
@Test
void and2() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.eq(User::getName,"Jone").and(i->i.lt(User::getAge,20).or().gt(User::getAge,30));List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
3.12.2or
@Test
void or() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.lt(User::getAge, 20).or().gt(User::getAge, 30);List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
@Test
void or2() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.likeRight(User::getName,"J").or(i->i.ge(User::getAge,20).le(User::getAge,30));List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
3.12.3nested
@Test
void nested() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.nested(i->i.eq(User::getName,"Tom").ne(User::getAge,23));List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
3.13自定义条件查询
@Test
void apply() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.apply("id=2");List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
3.14last查询
@Test
void last() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.last("limit 0,2");List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
3.15exists查询
3.15.1exists
@Test
void exists() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.exists("select id from t_user where age=18");List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
select id from t_user where age=18
语句能查询到结果,执行SELECT id,username AS name,age,email,
descFROM t_user
语句,否则不执行。
3.15.2notExists
@Test
void notExists() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.notExists("select id from t_user where age=18");List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
- 查询结果与exists相反。
3.16字段查询
@Test
void select() {LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();lambdaQueryWrapper.select(User::getId, User::getName);List<User> users = userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}