Mybatis-plus_02:条件查询

Singcing

已于 2022-06-05 21:08:19 修改

阅读量756

点赞数

分类专栏： Spring Boot学习文章标签： java 开发语言 spring boot 后端 mybatis-plus

于 2022-06-05 20:08:18 首次发布

本文链接：https://blog.csdn.net/m0_46306264/article/details/125129991

版权

Spring Boot学习专栏收录该内容

7 篇文章 0 订阅

订阅专栏

项目目录结构

数据库文件

CREATE TABLE `user` (
  `id` int(24) primary key NOT NULL,
  `name` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  `age` int(3) ,
  `tel` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
--
-- Dumping data for table `user`
--
 
INSERT INTO `user` (`id`, `name`, `password`, `age`, `tel`) VALUES
(1, 'Tom', 'tom', 3, '18866668888'),
(2, 'Jerry', 'jerry', 4, '16688886666'),
(3, 'Jock', 'jock', 41, '18812345678'),
(4, '传智播客', 'itcast', 15, '400618400'),
(1, 'Tom', 'tom', 3, '18866668888'),
(2, 'Jerry', 'jerry', 4, '16688886666'),
(3, 'Jock', 'jock', 41, '18812345678'),
(4, '传智播客', 'itcast', 15, '400618400');
COMMIT;

application.yml

在application.yml中配置log-impl，能在控制台查看mybatis-plus的SQL语句，方便Debug

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test?serverTime=UTC
    username: root
    password: ""

#开启mp的日志(输出到控制台)
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

User类

package com.example.domain;

import lombok.*;

/**
 * @auther CharlieLiang
 * @date 2022/6/4-21:57
 */
@Setter
@Getter
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
@ToString
public class User {
    private Long id;
    private String name;
    private String password;
    private Integer age;
    private String tel;


}

UserDao类

package com.example.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.domain.User;
import org.apache.ibatis.annotations.Mapper;

/**
 * @auther CharlieLiang
 * @date 2022/6/4-22:17
 */
@Mapper
public interface UserDao extends BaseMapper<User> {
}

Mybatisplus02DqlApplicationTests

package com.example;


import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.dao.UserDao;
import com.example.domain.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class Mybatisplus02DqlApplicationTests{

    @Autowired
    private UserDao userDao;

    @Test
    void testGetAll(){
        //方式一：按条件查询
        QueryWrapper qw=new QueryWrapper();
        qw.lt("age",15);
        List<User> userList=userDao.selectList(qw);
        System.out.println(userList);

        System.out.println("================================================================");

        //方式二：lambda格式按条件查询
        QueryWrapper<User> qw2 =new QueryWrapper<>();
        qw2.lambda().lt(User::getAge,18);
        List<User> userList2=userDao.selectList(qw2);
        System.out.println(userList2);

        System.out.println("================================================================");

        //方式三：lambda格式按条件查询
        LambdaQueryWrapper<User> lqw=new LambdaQueryWrapper<>();
        lqw.gt(User::getAge,18);
        lqw.lt(User::getAge,60);
        //也可以用链式编程：
        // lqw.lt(User::getAge,60).gt(User::getAge,18)
        //小于18岁 或者 大于30岁
        // lqw.lt(User::getAge,18).or().gt(User::getAge,60)
        List<User> userList3=userDao.selectList(lqw);
        System.out.println(userList3);
    }
}

运行结果：

条件查询，null值处理

新建UserQuery类，继承User类

package com.example.domain.query;

import com.example.domain.User;
import lombok.Data;

/**
 * @auther CharlieLiang
 * @date 2022/6/5-11:50
 */
@Data
public class UserQuery extends User {
    private Integer age2;
}

Mybatisplus02DqlApplicationTests类的测试方法

在测试方法中，的条件也可以使用链式编程

lqw.lt( null != uq.getAge2(), User::getAge,uq.getAge2())

.gt( null != uq.getAge(), User::getAge, uq.getAge() );

@Test
    void testGetAll2(){
        //模拟页面传递过来的查询数据
        UserQuery uq=new UserQuery();
        uq.setAge(10);
        uq.setAge2(30);

        //null 判定
        LambdaQueryWrapper<User> lqw=new LambdaQueryWrapper<>();
        //如果uq.getAge() 不为空才查询，
        lqw.lt(null != uq.getAge2(),User::getAge,uq.getAge2());
        lqw.gt(null != uq.getAge(),User::getAge,uq.getAge());
        List<User> userList=userDao.selectList(lqw);
        System.out.println(userList);
    }

运行结果：

条件查询--条件投影

测试类的条件查询的方法

 //查询投影
    @Test
    void testGetAll3(){
        //查询结果包含模型类中的部分属性

        QueryWrapper<User> qw=new QueryWrapper<>();
        qw.select("id","name","age","tel");
        List<User> userList2=userDao.selectList(qw);
        System.out.println(userList2);

        System.out.println("------------------------------");
        //查询结果包含模型类中未定义的属性
        QueryWrapper<User> qw2=new QueryWrapper<>();
        qw2.select("count(*) as count ,tel");
        qw2.groupBy("tel");
        List<Map<String, Object>> maps = userDao.selectMaps(qw2);
        System.out.println(maps);

    }

运行结果：

相等查询

@Test
    void test4(){
        //条件查询
        LambdaQueryWrapper<User> lqw=new LambdaQueryWrapper<>();
        //等同于=
        lqw.eq(User::getName,"Jerry").eq(User::getPassword,"jerry");
        User loginUser=userDao.selectOne(lqw);
        System.out.println(loginUser);
    }

范围查询

@Test
    void test5(){
    //条件查询
    LambdaQueryWrapper<User> lqw=new LambdaQueryWrapper<>();
    //范围查询  lt le gt ge eq between
    lqw.between(User::getAge,10,30);
    List<User> userList=userDao.selectList(lqw);
        System.out.println(userList);
    }

模糊查询

说明：likeLeft：表明%J， likeRight: J%

 @Test
    void test6(){
        LambdaQueryWrapper<User> lqw=new LambdaQueryWrapper<>();

        //模糊匹配 like
        lqw.like(User::getName,"J");
        List<User> userList=userDao.selectList(lqw);
        System.out.println(userList);
    }

User类

package com.example.domain;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.*;

import java.net.PasswordAuthentication;

/**
 * @auther CharlieLiang
 * @date 2022/6/4-21:57
 */
@Setter
@Getter
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
@ToString
@TableName("user")
public class User {
    private Long id;
    private String name;
    @TableField(value = "password", select = false)
    private String password;
    private Integer age;
    private String tel;

    @TableField(exist = false)
    private Integer online;
}

当类名和表明不一致时，用@TableName() 指明类名

当属性名和数据库的列名不一致时，用 @TableField() 指明列名