广州网站建设策划,wordpress分类发布文章,广州网站制作费用,wordpress函数语言三、进阶篇
3.1映射
3.1.1自动映射规则
表名和实体类名映射 - 表名user 实体类名User字段名和实体类属性名映射 - 字段名name 实体类属性名name字段名下划线命名方式和实体类属性驼峰命名方式映射 - 字段名 user_email 实体类属性名 userEmail
开启驼峰命名
m…三、进阶篇
3.1映射
3.1.1自动映射规则
表名和实体类名映射 - 表名user 实体类名User字段名和实体类属性名映射 - 字段名name 实体类属性名name字段名下划线命名方式和实体类属性驼峰命名方式映射 - 字段名 user_email 实体类属性名 userEmail
开启驼峰命名
mybatis-plus.configuration.map-underscore-to-camel-casetrue3.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-prefixt_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(selectfalse)来隐藏这个字段拿在拼接SQL语句的时候就不会拼接这个字段。如如果不想显示年龄信息那么可以在age属性上添加这个注解来隐藏这个字段
TableField(select false)
private Integer age;通过查询生成的SQL语句发现并没有拼接age字段
3.1.5视图属性 在实际开发中有些字段不需要数据库存储但是却需要展示需要展示也就是意味着实体类中需要存在这个字段我们称这些实体类中存在但是数据库中不存在这个字段叫做视图字段。 我们可以通过TableField(existfalse)来去掉这个字段不让他作为查询字段。
TableField(exist false)
private Integer online;通过查询生成的SQL语句发现并没有online字段
3.2条件构造器 3.3等值查询
3.3.1eq
使用QueryWrapper对象构建查询条件
Test
void eq() {//1.创建条件查询对象QueryWrapperUser queryWrapper new QueryWrapper();//2.设置查询条件指定查询的字段和匹配的值queryWrapper.eq(username,Jack);//3.进行条件查询User user userMapper.selectOne(queryWrapper);System.out.println(user);
}测试效果
外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传
如果我们每次都是自己进行字段名称的编写有可能会出现名称写错的情况怎么避免这种情况呢我们可以使用LambdaQueryWrapper对象在构建字段时使用方法引用的方式来选择字段这样做可以避免字段拼写错误。
Test
void eq2() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.eq(User::getName,Jack);User user userMapper.selectOne(lambdaQueryWrapper);System.out.println(user);
}我们的构建条件是从哪里来的应该是从客户端通过请求发送过来的由服务器接收的。在网站中一般都会有多个条件入口用户可以选择一个或多个条件进行查询那这个时候在请求时我们不能确定所有的条件都是有值的部分条件可能用户没有传值那么该条件就为null。那么null的条件我们是不需要进行查询条件拼接的否则就会出现如下情况将为null的条件进行拼接筛选后无法查询出结果。
Test
void isNull() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.eq(User::getName,null);User user userMapper.selectOne(lambdaQueryWrapper);System.out.println(user);
}我们要解决这个问题可以先判断是否为空根据判断结果选择是否拼接该字段。
Test
void isNUll2() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();//String usernameJone;String usernamenull;lambdaQueryWrapper.eq(username!null,User::getName,username);//User user userMapper.selectOne(lambdaQueryWrapper);ListUser user userMapper.selectList(lambdaQueryWrapper);System.out.println(user);
}3.3.2allEq
如何通过多个eq构建多条件查询
Test
void allEq1() {LambdaQueryWrapperUser 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.构建条件MapHashMapString, Object map new HashMap();map.put(username,Tom);//map.put(age,28);map.put(age,null);//2.创建QueryWrapper对象QueryWrapperUser 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() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.ne(User::getName,Tom);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}3.4范围查询
3.4.1gt
Test
void gt() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.gt(User::getAge,20);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}3.4.2ge
Test
void ge() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.ge(User::getAge,20);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}3.4.3lt
Test
void lt() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.lt(User::getAge,20);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}3.4.4le
Test
void le() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.le(User::getAge,20);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}3.4.5between
Test
void between() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.between(User::getAge,20,28);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}3.4.6notBetween
Test
void notBetween() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.notBetween(User::getAge,20,28);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}3.5模糊查询
Test
void like() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.like(User::getName,J);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}Test
void notLike() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.notLike(User::getName,J);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}Test
void likeLeft() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.likeLeft(User::getName,e);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}Test
void likeRight() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.likeRight(User::getName,J);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}3.6判空查询
Test
void isNull() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.isNull(User::getDesc);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}Test
void isNotNull() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.isNotNull(User::getName);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}3.7包含查询
3.7.1in
Test
void in() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.in(User::getAge, 18, 20, 21);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}Test
void in2() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();ArrayListInteger list new ArrayList();//Collections.addAll(list,18,20,21);list.add(18);list.add(20);list.add(21);lambdaQueryWrapper.in(User::getAge, list);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}3.7.2notIn
Test
void notIn() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.notIn(User::getAge, 18, 20, 21);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}Test
void notIn2() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();ArrayListInteger list new ArrayList();Collections.addAll(list,18,20,21);lambdaQueryWrapper.notIn(User::getAge,list);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}3.7.3inSql
Test
void inSql() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.inSql(User::getAge, 18,20,21);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}Test
void inSql2() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.inSql(User::getAge, select age from t_user where age20);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}3.7.4notInSql
Test
void notInSql() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.notInSql(User::getAge, 18,20,21);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}Test
void notInSql2() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.notInSql(User::getAge, select age from t_user where age20);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}3.8分组查询
Test
void groupBy() {QueryWrapperUser queryWrapper new QueryWrapper();//分组字段queryWrapper.groupBy(age);//查询字段queryWrapper.select(age,count(*) as field_count);ListUser users userMapper.selectList(queryWrapper);System.out.println(users);
}3.9聚合查询
Test
void having() {QueryWrapperUser queryWrapper new QueryWrapper();//分组字段queryWrapper.groupBy(age);//查询字段queryWrapper.select(age,count(*) as field_count);//聚合字段queryWrapper.having(field_count1);ListUser users userMapper.selectList(queryWrapper);System.out.println(users);
}3.10排序查询
Test
void orderByAsc() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.orderByAsc(User::getAge,User::getId);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}Test
void orderByDesc() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.orderByDesc(User::getAge,User::getId);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}Test
void orderBy() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();/*** oderBy:设置排序字段和排序的方式* 参数一如果排序字段的值为null是否还要作为排序字段参与排序* 参数二是否升序排序* 参数三排序字段*/lambdaQueryWrapper.orderBy(true, true, User::getAge);lambdaQueryWrapper.orderBy(true, false, User::getId);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}3.11func查询
Testvoid func() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();//可能会根据不同的情况来选择进行拼接不同的查询条件/*lambdaQueryWrapper.func(new ConsumerLambdaQueryWrapperUser() {Overridepublic void accept(LambdaQueryWrapperUser 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);}});ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);}3.12逻辑查询
3.12.1and
正常拼接默认就是and例如
Test
void and() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.gt(User::getAge,20).lt(User::getAge,30);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}Test
void and2() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.eq(User::getName,Jone).and(i-i.lt(User::getAge,20).or().gt(User::getAge,30));ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}3.12.2or
Test
void or() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.lt(User::getAge, 20).or().gt(User::getAge, 30);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}Test
void or2() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.likeRight(User::getName,J).or(i-i.ge(User::getAge,20).le(User::getAge,30));ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}3.12.3nested
Test
void nested() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.nested(i-i.eq(User::getName,Tom).ne(User::getAge,23));ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}3.13自定义条件查询
Test
void apply() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.apply(id2);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}3.14last查询
Test
void last() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.last(limit 0,2);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}3.15exists查询
3.15.1exists
Test
void exists() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.exists(select id from t_user where age18);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}select id from t_user where age18语句能查询到结果执行SELECT id,username AS name,age,email,desc FROM t_user语句否则不执行。 3.15.2notExists
Test
void notExists() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.notExists(select id from t_user where age18);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}查询结果与exists相反。
3.16字段查询
Test
void select() {LambdaQueryWrapperUser lambdaQueryWrapper new LambdaQueryWrapper();lambdaQueryWrapper.select(User::getId, User::getName);ListUser users userMapper.selectList(lambdaQueryWrapper);System.out.println(users);
}
