快捷導(dǎo)航

spring?boot?使用Mybatis-plus查詢方法解析

更新時間：2022年09月28日 14:32:44 作者：會飛地太陽

這篇文章主要介紹了spring?boot?使用Mybatis-plus查詢方法解析，文章圍繞主題展開詳細的內(nèi)容介紹，具有一定的參考價值，需要的小伙伴可以參考一下

前言

使用的數(shù)據(jù)庫和相關(guān)代碼見springboot整合mybatis plus與druid詳情。
使用test來進行使用：

普通查詢

@Autowired
    private StudentDao studentDao;
    @Test
    void selectById() {
        studentDao.selectById(1000);
    }

批量查詢

@SpringBootTest
class Spring1ApplicationTests {

    @Autowired
    private StudentDao studentDao;
    @Test
    void selectByIds() {
        List<Integer> ids = Arrays.asList(1000,1002);
        List<student> st = studentDao.selectBatchIds(ids);
    }
}

條件查詢

主要使用了QueryWrapper方法：

queryWrapper.lt（）——小于
queryWrapper.le（）——小于等于
queryWrapper.gt（）——大于
queryWrapper.ge（）——大于等于
queryWrapper.eq（）——等于
queryWrapper.ne（）——不等于
queryWrapper.betweeen（“age”,10,20）——age在值10到20之間
queryWrapper.notBetweeen（“age”,10,20）——age不在值10到20之間
queryWrapper.like（“屬性”,“值”）——模糊查詢匹配值‘%值%’
queryWrapper.notLike（“屬性”,“值”）——模糊查詢不匹配值‘%值%’
queryWrapper.likeLeft（“屬性”,“值”）——模糊查詢匹配最后一位值‘%值’
queryWrapper.likeRight（“屬性”,“值”）——模糊查詢匹配第一位值‘值%’
queryWrapper.isNull（）——值為空或null
queryWrapper.isNotNull（）——值不為空或null
queryWrapper.in（“屬性”，條件，條件）——符合多個條件的值
queryWrapper.notIn(“屬性”，條件，條件 )——不符合多個條件的值
queryWrapper.or（）——或者
queryWrapper.and（）——和
queryWrapper.orderByAsc(“屬性”)——根據(jù)屬性升序排序
queryWrapper.orderByDesc(“屬性”)——根據(jù)屬性降序排序
queryWrapper.inSql(“sql語句”)——符合sql語句的值
queryWrapper.notSql(“sql語句”)——不符合SQL語句的值
queryWrapper.esists（“SQL語句”）——查詢符合SQL語句的值
queryWrapper.notEsists（“SQL語句”）——查詢不符合SQL語句的值

查詢通過姓名：

@Autowired
    private StudentDao studentDao;
    @Test
    void selectByName() {
        QueryWrapper<student> queryWrapper = new QueryWrapper<>();
        queryWrapper.like("name","Li Ming");
        List<student> userInfoList = studentDao.selectList(queryWrapper);
    }

查詢方法詳細代碼：

@Test
public void contextLoads() {
}
 
@Autowired
private UserInfoMapper userInfoMapper;
 
/**
 * 普通查詢
 */
@Test
public void selectById() {
    UserInfo userInfo = userInfoMapper.selectById(123);
    System.out.println(userInfo);
}
 
/**
 * 批量查詢
 */
@Test
public void selectByIds() {
    List<Long> ids = Arrays.asList(123L,124L,125L);
    List<UserInfo> userInfo = userInfoMapper.selectBatchIds(ids);
    System.out.println(userInfo);
}
 
/**
 * 名字包含娟并且年齡小于30
 */
@Test
public void selectByWrapper() {
    QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
    queryWrapper.like("username","娟").lt("age",30);
    List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
    userInfoList.forEach(System.out::println);
}
/**
 * 名字包含娟并且年齡大于等于20且小于等于40并且email不為空
 */
@Test
public void selectByWrapper2() {
    QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
    queryWrapper.like("username","娟").between("age",20,30).isNotNull("email");
    List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
    userInfoList.forEach(System.out::println);
}
 
/**
 * 名字姓肖或者年量大于等于20，按照年齡降序排列，年齡相同按照id生序排列
 */
@Test
public void selectByWrapper3() {
    QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
    queryWrapper.likeRight("username","肖")
            .or().ge("age",20).orderByDesc("age").orderByAsc("id");
    List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
    userInfoList.forEach(System.out::println);
}
 
/**
 * 創(chuàng)建日期為2019年10月2日并且直屬上級名字為王姓
 */
@Test
public void selectByWrapper4() {
    QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
    queryWrapper.apply("date_format(create_time,'%Y-%m-%d')={0}","2019-10-02")
            .inSql("parent_id","select id from user where username like '王%'");
    List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
    userInfoList.forEach(System.out::println);
}
 
//上面的日期查詢使用的是占位符的形式進行查詢，目的就是為了防止SQL注入的風(fēng)險
 
/**
 * 名字為王姓并且（年齡小于40或郵箱不為空）
 */
@Test
public void selectByWrapper5() {
    QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
    queryWrapper.likeRight("username","王")
            .and(wq->wq.lt("age",40))
            .or().isNotNull("email");
    List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
    userInfoList.forEach(System.out::println);
}
/**
 * 名字為王姓并且（年齡小于40并且大與20或郵箱不為空）
 */
@Test
public void selectByWrapper6() {
    QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
    queryWrapper.likeRight("username","王")
            .and(wq->wq.lt("age",40).gt("age",20))
            .or().isNotNull("email");
    List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
    userInfoList.forEach(System.out::println);
}
/**
 * （年齡小于40并且大與20或郵箱不為空）并且名字為王姓
 */
@Test
public void selectByWrapper7() {
    QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
    queryWrapper.nested(wq->wq.lt("age",40).gt("age",20))
            .or().isNotNull("email")
            .likeRight("username","王");
    List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
    userInfoList.forEach(System.out::println);
}
/**
 * 年齡23，30，40
 */
@Test
public void selectByWrapper8() {
    QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
    queryWrapper.in("age",Arrays.asList(20,30,40));
    List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
    userInfoList.forEach(System.out::println);
}
/**
 * 只返回滿足條件的其中一條語句即可
 */
@Test
public void selectByWrapper9() {
    QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
    queryWrapper.in("age",Arrays.asList(20,30,40)).last("limit 1");
    List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
    userInfoList.forEach(System.out::println);
}
 
/**
 * 名字中包含雨并且年齡小于40(只取id,username)
 */
@Test
public void selectByWrapper10() {
    QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
    queryWrapper.select("id","username").like("username","雨").lt("age",40);
    List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
    userInfoList.forEach(System.out::println);
}
 
/**
 * 名字中包含雨并且年齡小于40(不取create_time,parent_id兩個字段,即不列出全部字段)
 */
@Test
public void selectByWrapper11() {
    QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
    queryWrapper.like("username","雨").lt("age",40)
                .select(UserInfo.class,info->!info.getColumn().equals("create_time")&&
                        !info.getColumn().equals("parent_id"));
    List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
    userInfoList.forEach(System.out::println);
}
 
/**
 * 姓名和郵箱不為空
 */
public void testCondition() {
    String username = "王";
    String email = "";
    condition(username,email);
}
 
private void condition(String username,String email){
    QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
    queryWrapper.like(StringUtils.isNullOrEmpty(username),"name",username)
                .like(StringUtils.isNullOrEmpty(email),"email",email);
    List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
    userInfoList.forEach(System.out::println);
}
 
/**
 * 實體作為條件構(gòu)造器方法的參數(shù)
 */
@Test
public void selectByWrapperEntity() {
    UserInfo whereUser = new UserInfo();
    whereUser.setUsername("xiaojuan");
    whereUser.setAge(22);
    QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>(whereUser);
    List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
    userInfoList.forEach(System.out::println);
}
/**
 * AllEq用法
 */
@Test
public void selectByWrapperAllEq() {
    QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("nuserame","xiaojuan");
    params.put("age",null);
    queryWrapper.allEq(params);
    List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
    userInfoList.forEach(System.out::println);
}
 
/**
 * AllEq用法(排除不是條件的字段)
 */
@Test
public void selectByWrapperAllEq2() {
    QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("nuserame","xiaojuan");
    params.put("age",null);
    queryWrapper.allEq((k,v)->!k.equals("name"),params);
    List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
    userInfoList.forEach(System.out::println);
}
 
/**
 * selectMaps
 */
@Test
public void selectByWrapperMaps() {
    QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
    queryWrapper.like("name","肖").lt("age",40);
    List<Map<String,Object>> userInfoList = userInfoMapper.selectMaps(queryWrapper);
    userInfoList.forEach(System.out::println);
}
 
/**
 * 按照直屬上級分組，查詢每組的平均年齡，最大年齡，最小年齡。并且只取年齡總和小于500的組
 */
@Test
public void selectByWrapperMaps2() {
    QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
    queryWrapper.select("avg(age) avg_age","min(min) min_age","max(age) max_age")
            .groupBy("parent_id").having("sum(age)<{0}",500);
    List<Map<String,Object>> userInfoList = userInfoMapper.selectMaps(queryWrapper);
    userInfoList.forEach(System.out::println);
}
 
/**
 * selectObjs
 */
@Test
public void selectByWrapperObjs() {
    QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
    queryWrapper.select("id","name").like("name","肖").lt("age",40);
    List<Object> userInfoList = userInfoMapper.selectObjs(queryWrapper);
    userInfoList.forEach(System.out::println);
}
 
/**
 * selectCount
 */
@Test
public void selectByWrapperCount() {
    QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
    queryWrapper.like("name","肖").lt("age",40);
    Integer count = userInfoMapper.selectCount(queryWrapper);
    System.out.println(count);
}
 
/**
 * selectOne
 */
@Test
public void selectByWrapperSelectOne() {
    QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
    queryWrapper.like("name","肖").lt("age",40);
    UserInfo user = userInfoMapper.selectOne(queryWrapper);
    System.out.println(user);
}
/**
 * 使用Lambda
 */
@Test
public void selectLambda() {
   // LambdaQueryWrapper<UserInfo> lambda = new QueryWrapper<UserInfo>().lambda();
    LambdaQueryWrapper<UserInfo> lambda = new LambdaQueryWrapper<UserInfo>();
    lambda.like(UserInfo::getUsername,"娟").lt(UserInfo::getAge,40);
    List<UserInfo> userInfoList = userInfoMapper.selectList(lambda);
    userInfoList.forEach(System.out::println);
}
 
/**
 * 使用Lambda,名字為王姓（年齡小于40或郵箱不為空）
 */
@Test
public void selectLambd2() {
    LambdaQueryWrapper<UserInfo> lambda = new LambdaQueryWrapper<UserInfo>();
    lambda.like(UserInfo::getUsername,"娟")
            .and(lqw->lqw.lt(UserInfo::getAge,40).or().isNotNull(UserInfo::getEmail));
    List<UserInfo> userInfoList = userInfoMapper.selectList(lambda);
    userInfoList.forEach(System.out::println);
}
/**
 * 使用Lambda鏈式
 */
@Test
public void selectLambd3() {
    List<UserInfo> userInfoList = new LambdaQueryChainWrapper<UserInfo>(userInfoMapper)
            .like(UserInfo::getUsername,"娟").ge(UserInfo::getAge,20).list();
    userInfoList.forEach(System.out::println);
}

到此這篇關(guān)于spring boot 使用Mybatis-plus查詢方法解析的文章就介紹到這了,更多相關(guān)spring boot Mybatis-plus內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: