查询方法
package com.mp;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.mp.dao.UserMapper;
import com.mp.entity.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @Description
* @auther mohuani
* @create 2019-12-25 11:37
*/
@RunWith(SpringRunner.class)
@SpringBootTest
public class RetrieveTest {
@Autowired
private UserMapper userMapper;
@Test
public void selectById() {
User user = userMapper.selectById(1088250446457389058L);
System.out.println(user);
}
@Test
public void selectBatchIds() {
List<Long> list = Arrays.asList(1088248166370832385L, 1094590409767661570L, 1209509417456001025L);
List<User> userList = userMapper.selectBatchIds(list);
userList.forEach(System.out::println);
}
@Test
public void selectByMap() {
Map<String, Object> columnMap = new HashMap<>();
columnMap.put("name", "李艺伟");
columnMap.put("age", 28);
List<User> userList = userMapper.selectByMap(columnMap);
userList.forEach(System.out::println);
}
/**
* 1、名字中包含雨并且年龄小于40
* name like '%雨%' and age<40
*/
@Test
public void selectByWrapper() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name", "雨").lt("age", 40);
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
/**
* 2、名字中包含雨年并且龄大于等于20且小于等于40并且email不为空
* name like '%雨%' and age between 20 and 40 and email is not null
*/
@Test
public void selectByWrapper2() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name", "雨").between("age" ,20 ,40).isNotNull("email");
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
/**
* 3、名字为王姓或者年龄大于等于25,按照年龄降序排列,年龄相同按照id升序排列
* name like '王%' or age>=25 order by age desc,id asc
*/
@Test
public void selectByWrapper3() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name", "王").or().gt("age", 25).orderByDesc("age").orderByAsc("id");
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
/**
* 4、创建日期为2019年2月14日并且直属上级为名字为王姓
* date_format(create_time,'%Y-%m-%d')='2019-02-14' and manager_id in (select id from user where name like '王%')
*/
@Test
public void selectByWrapper4() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.apply("date_format(create_time, '%Y-%m-%d') = {0}", "2019-02-14")
.inSql("manager_id", "select id from user where name like '王%'");
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
/**
* 5、名字为王姓并且(年龄小于40或邮箱不为空)
* name like '王%' and (age<40 or email is not null)
*/
@Test
public void selectByWrapper5() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name", "王").and(userQueryWrapper -> userQueryWrapper.gt("age", 40).or().isNotNull("email"));
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
/**
* 6、名字为王姓或者(年龄小于40并且年龄大于20并且邮箱不为空)
* name like '王%' or (age<40 and age>20 and email is not null)
*/
@Test
public void selectByWrapper6() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name", "王").or(userQueryWrapper -> userQueryWrapper.lt("age", 40).gt("age", 20).isNotNull("email"));
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
/**
* 7、(年龄小于40或邮箱不为空)并且名字为王姓
* (age<40 or email is not null) and name like '王%'
*/
@Test
public void selectByWrapper7() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.nested(userQueryWrapper -> userQueryWrapper.lt("age", 40).or().isNotNull("email"))
.likeRight("name", "王");
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
/**
* 8、年龄为30、31、34、35
* age in (30、31、34、35)
*/
@Test
public void selectByWrapper8() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.in("age", Arrays.asList(30, 31, 34, 35));
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
/**
* 9、只返回满足条件的其中一条语句即可
* limit 1
*/
@Test
public void selectByWrapper9() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.in("age", Arrays.asList(30, 31, 34, 35)).last("limit 1");
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
/**
* 二、select中字段不全部出现的查询
* 10.1、名字中包含雨并且年龄小于40(需求1加强版)
* 第一种情况:select id,name
* from user
* where name like '%雨%' and age<40
*/
@Test
public void selectByWrapperSupper() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("id, name").like("name", "雨").lt("age", 40);
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
/**
* 10.2第二种情况:select id,name,age,email
* from user
* where name like '%雨%' and age<40
*/
@Test
public void selectByWrapperSupper2() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name", "雨").lt("age", 40)
.select(User.class, tableFieldInfo -> !tableFieldInfo.getColumn().equals("create_time") &&
!tableFieldInfo.getColumn().equals("manager_id"));
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
@Test
public void selectByWrapperMaps() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("id", "name").like("name", "雨").lt("age", 40);
List<Map<String, Object>> userList = userMapper.selectMaps(queryWrapper);
userList.forEach(System.out::println);
}
/**
* 11、按照直属上级分组,查询每组的平均年龄、最大年龄、最小年龄。
* 并且只取年龄总和小于500的组。
* 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 selectByWrapperMaps2() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("avg(age) avg_age", "min(age) min_age", "max(age) max_age")
.groupBy("manager_id").having("sum(age)<{0}", 500);
List<Map<String, Object>> userList = userMapper.selectMaps(queryWrapper);
userList.forEach(System.out::println);
}
}
实体作为条件构造器构造方法的参数时
- 实体参数和条件构造器构造的条件互不干扰,都会拼接到SQL语句里面,所以用的时候选择其中的一种方式就行了。
allEq
allEq(Map<R, V> params)
allEq(Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, Map<R, V> params, boolean null2IsNull)
- 全部eq(或个别isNull)
params : key为数据库字段名,value为字段值
null2IsNull : 为true则在map的value为null时调用 isNull 方法,为false时则忽略value为null的
参考: https://mp.baomidou.com/guide/wrapper.html#alleq
selectObjs
- 只会返回结果集的第一列,其他列不返回,比较鸡肋
selectCount
- 查询总记录数
selectOne
- 返回一个实体对象,如果查询的结果不止一条就会直接报错
Lambda查询
- 编写查询条件的时候我们容易把字段名写错,但是编译检查不出来。使用Lambda的方式,可以减少字段名写错的情况,在编译的时候会直接检测出来。
// 第一种构建方式
LambdaQueryWrapper<User> lambda = new QueryWrapper<User>().lambda();
// 第二种构建方式
LambdaQueryWrapper<User> userLambdaQueryWrapper = new LambdaQueryWrapper<>();
// 第三种构建方式
LambdaQueryWrapper<User> lambdaQuery = Wrappers.<User>lambdaQuery();
查询示例代码
@Test
public void selectLambda() {
// LambdaQueryWrapper<User> lambda = new QueryWrapper<User>().lambda();
// LambdaQueryWrapper<User> userLambdaQueryWrapper = new LambdaQueryWrapper<>();
LambdaQueryWrapper<User> lambdaQuery = Wrappers.<User>lambdaQuery();
lambdaQuery.like(User::getName, "雨").lt(User::getAge, 40);
List<User> userList = userMapper.selectList(lambdaQuery);
userList.forEach(System.out::println);
}
/**
* 5.名字为王姓并且(年龄小于40或邮箱不为空)
* name like '王%' and (age<40 or email is not null)
*/
@Test
public void selectLambda2() {
LambdaQueryWrapper<User> lambdaQuery = Wrappers.lambdaQuery();
lambdaQuery.likeRight(User::getName, "王")
.and(userLambdaQueryWrapper -> userLambdaQueryWrapper.lt(User::getAge, 40).or().isNotNull(User::getEmail));
List<User> userList = userMapper.selectList(lambdaQuery);
userList.forEach(System.out::println);
}
@Test
public void selectLambda3() {
List<User> userList = new LambdaQueryChainWrapper<User>(userMapper)
.like(User::getName, "雨").ge(User::getAge, 20).list();
userList.forEach(System.out::println);
}
自定义查询
package com.mp.dao;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.mp.entity.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
/**
* @Description
* @auther mohuani
* @create 2019-12-24 22:55
*/
public interface UserMapper extends BaseMapper<User> {
// 此处的ew,写法是固定的,框架约定的名字
@Select("select * from user ${ew.customSqlSegment}")
List<User> selectAll(@Param(Constants.WRAPPER) Wrapper<User> wrapper);
}
@Test
public void selectMy() {
LambdaQueryWrapper<User> lambdaQuery = Wrappers.lambdaQuery();
lambdaQuery.likeRight(User::getName, "王")
.and(userLambdaQueryWrapper -> userLambdaQueryWrapper.lt(User::getAge, 40).or().isNotNull(User::getEmail));
List<User> userList = userMapper.selectAll(lambdaQuery);
userList.forEach(System.out::println);
}
也可以通过xml配置自定义查询
分页查询
// 无条件翻页查询
IPage<T> page(IPage<T> page);
// 翻页查询
IPage<T> page(IPage<T> page, Wrapper<T> queryWrapper);
// 无条件翻页查询
IPage<Map<String, Object>> pageMaps(IPage<T> page);
// 翻页查询
IPage<Map<String, Object>> pageMaps(IPage<T> page, Wrapper<T> queryWrapper);
分页查询步骤:
1:创建并完善配置类MybatisPlusConfig.java
2.实例化Page对象
2.1: Page对象构造函数参数:
1:当前页
2:一页的数量
3.分页总数
4:是否需要查询总条数(false:不查,true:查,少发出一条sql)
3.1 使用selectPage 或 selectMapsPage(区别:前者封装进实体类中,后者封装进Map对象中)
3.2 如果为多表查询,则需要进行自定义方法,此时需要配置UserMapper接口文件,返回值为IPage类型
注:切记不可返回Page类型,否者代码运行无报错,也能看到sql查询,但是在获取getRecords时无数据
3.2.1 IPage<User> selectAllByPage(Page<User> page, @Param(Constants.WRAPPER) Wrapper<User> wrapper);
3.2.2 配置@select注解 或者 配置xml文件
@select注解附:@Select("select * from User ${ew.customSqlSegment}")
xml配置附:<select id="selectAllByPageXml" resultType="com.mp.pojo.User">
select * from User ${ew.customSqlSegment}
</select>
4.传入参数Page对象和QueryWrapper对象
4.1: 使用getTotal获取总条数
4.2: 使用getPages获取总页数
参考:https://mp.baomidou.com/guide/crud-interface.html#page