mybatis-plus_1

导入pom

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1.tmp</version>
        </dependency>

mysql配置

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mp?useUnicode=true&charcaterEncoding=uft8&serverTimezone=UTC
    username: root
    password: root

#设置日志级别
logging:
  level:
    root: warn
    top.mgy.mybatistest.dao: trace  #该包最低级别日志
  pattern:
    console: '%p%m%n'  # 日志级别  日志内容  换行

SQL 语句

#创建用户表
CREATE TABLE USER (
    id BIGINT(20) PRIMARY KEY NOT NULL COMMENT '主键',
    NAME VARCHAR(30) DEFAULT NULL COMMENT '姓名',
    age INT(11) DEFAULT NULL COMMENT '年龄',
    email VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    manager_id BIGINT(20) DEFAULT NULL COMMENT '直属上级id',
    create_time DATETIME DEFAULT NULL COMMENT '创建时间',
    CONSTRAINT manager_fk FOREIGN KEY (manager_id)
        REFERENCES USER (id)
)  ENGINE=INNODB CHARSET=UTF8;

#初始化数据：
INSERT INTO USER (id, NAME, age, email, manager_id
	, create_time)
VALUES (1087982257332887553, '大boss', 40, 'boss@baomidou.com', NULL
		, '2019-01-11 14:20:20'),
	(1088248166370832385, '王天风', 25, 'wtf@baomidou.com', 1087982257332887553
		, '2019-02-05 11:12:22'),
	(1088250446457389058, '李艺伟', 28, 'lyw@baomidou.com', 1088248166370832385
		, '2019-02-14 08:31:16'),
	(1094590409767661570, '张雨琪', 31, 'zjq@baomidou.com', 1088248166370832385
		, '2019-01-14 09:15:15'),
	(1094592041087729666, '刘红雨', 32, 'lhm@baomidou.com', 1088248166370832385
		, '2019-01-14 09:48:16');

创建 User 实体类

package top.mgy.mybatistest.entity;

import lombok.Data;

import java.time.LocalDateTime;

@Data
public class User {

    //主键
    private Long id;

    //姓名
    private String name;

    //年龄
    private Integer age;
    //邮箱
    private String email;
    //直属上级
    private Long managerId;
    //创建时间
    private LocalDateTime createTime;

}

创建mapper

package top.mgy.mybatistest.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import top.mgy.mybatistest.entity.User;

public interface UserMapper extends BaseMapper<User> {
}

在spring boot 启动类加上，包扫描

package top.mgy.mybatistest;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("top.mgy.mybatistest.dao") //添加包扫描
public class MybatisTestApplication {

    public static void main(String[] args) {
        SpringApplication.run(MybatisTestApplication.class, args);
    }

}

测试

package top.mgy.mybatistest.com.mp;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import top.mgy.mybatistest.dao.UserMapper;
import top.mgy.mybatistest.entity.User;

import java.util.List;


@SpringBootTest
public class SimpleTest {

    @Autowired
    private UserMapper userMapper;

    @Test
    public void select(){
        //查询全部
        List<User> users = userMapper.selectList(null);
        users.forEach(System.out::println);
    }
}

新增方法

package top.mgy.mybatistest.com.mp;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import top.mgy.mybatistest.dao.UserMapper;
import top.mgy.mybatistest.entity.User;

import java.time.LocalDateTime;

@SpringBootTest
public class Insert {

    @Autowired
    private UserMapper userMapper;

    @Test
    public void Insert(){
        User user = new User();
        user.setName("溜溜");
        user.setAge(26);
        user.setManagerId(1088248166370832385L);
        user.setCreateTime(LocalDateTime.now());

        int row = userMapper.insert(user);
        System.out.println("影响行数"+row);
    }


}

常用注解

当实体类和数据库表不对应是使用该注解指定表名

• @TableName("mp_user")

当数据库中主键不为id时,指定数据库中的主键列

• @TableId

//主键
    @TableId
    private Long user_id;

指定实体类中字段在数据库中对应字段

• @TableField("name")

    //姓名
    @TableField("name")
    private String t_name;

排除非表字段的三种方式

使用 transient 标注

transient 关键字表示，该字段不参与序列化过程

//备注
    private transient String remark;

使用static 关键字

静态变量不会自动生成get/set需要手动生成

//备注
    private static String remark;

    public static String getRemark() {
        return remark;
    }

    public static void setRemark(String remark) {
        User.remark = remark;
    }

使用@TableField(exist = false)

exist 设置为 false表示，该字段不是数据库字段

//备注
    @TableField(exist = false)
    private String remark;

查询

根据id查询

 @Test
    public void selectById(){
        User user = userMapper.selectById(1088248166370832385L);
        System.out.println(user);

    }

批量id查询

 @Test
    public void selectIds(){
        List<Long> ids = Arrays.asList(1088248166370832385L, 1088250446457389058L, 1243755707546284033L);
        List<User> users = userMapper.selectBatchIds(ids);
        users.forEach(System.out::println);

    }

条件查询

    @Test
    public void selectByMap(){
        /**
         * map.put("name","张三")
         * map.put("age",20)
         * where name = '张三' and age = 20
         */
        Map<String,Object> map = new HashMap<>();
        map.put("name","李艺伟");
        map.put("age",28);
        List<User> users = userMapper.selectByMap(map);
        users.forEach(System.out::println);

    }

以条件构造器的查询

@Test
    public void selectByWrapper(){
        /**
         * 查询名字中包含雨并且年龄小于40
         * where name like '%雨%' and age < 40
         */
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.like("name","雨").lt("age",40);

        List<User> users = userMapper.selectList(userQueryWrapper);
        users.forEach(System.out::println);
    }

    @Test
    public void selectByWrapper2(){
        /**
         *名字中包含 "雨" 并且年龄 >= 20 且 <=40 且 email 不为空
         * where name '%雨%' and age between 20 and 40 and email not null
         */
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();

         userQueryWrapper.like("name","雨").between("age",20,40).isNotNull("email");

        List<User> users = userMapper.selectList(userQueryWrapper);
        users.forEach(System.out::println);
    }

    @Test
    public void selectByWrapper3() {
        /**
         * 名字为王姓 或者 年龄 >=25 按照年龄降序排列  年龄相同按照id升序排列
         * where like '王%' or age >=25 order by age desc,id asc
         */
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.likeRight("name", "王").or().ge("age", 25)
                .orderByDesc("age").orderByAsc("id");

        List<User> users = userMapper.selectList(userQueryWrapper);
        users.forEach(System.out::println);

    }
    
    
    @Test
    public void selectByWrapper4() {
        /**
         * 创建日期为2019年2月14日并且直属上级名字为王姓
         * date_format(create_time,'%Y-%m-%d') and manager_id in(select id from user where name like '王%')
         */
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();

        userQueryWrapper.apply("date_format(create_time,'%Y-%m-%d') ={0}","2019-02-14")
                .inSql("manager_id","select id from user where name like '王%'");

        List<User> users = userMapper.selectList(userQueryWrapper);
        users.forEach(System.out::println);

    }
    
    
     @Test
    public void selectByWrapper5() {
        /**
         * 名字为 王 姓， 且 (年龄小于40或邮箱不为空)
         * where name '王%' and (age < 40 or email is not null)
         */
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();

        userQueryWrapper.likeRight("name","王")
                .and(wq->wq.lt("age",40).or().isNotNull("email"));

        List<User> users = userMapper.selectList(userQueryWrapper);
        users.forEach(System.out::println);

    }   
    
    
    @Test
    public void selectByWrapper6() {
        /**
         * 名字为 王 姓 或者 (年龄<40 且 年龄 >20 且 邮箱不为空)
         *  WHERE (name LIKE ? OR (age < ? AND age > ? AND email IS NOT NULL))
         */
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();

        userQueryWrapper.likeRight("name","王").
                or(wq->wq.lt("age",40).gt("age",20).isNotNull("email"));

        List<User> users = userMapper.selectList(userQueryWrapper);
        users.forEach(System.out::println);

    }
    
    
    @Test
    public void selectByWrapper7() {
        /**
         *  (年龄小于40 或邮箱不为空) 并且名字为王姓
         *  (age<40 or email is not null) and name like '王%'
         *
         *  WHERE ((age < ? OR email IS NOT NULL) AND name LIKE ?)
         */
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();

        userQueryWrapper.nested(wq->wq.lt("age",40).or().isNotNull("email"))
               .likeRight("name","王");

        List<User> users = userMapper.selectList(userQueryWrapper);
        users.forEach(System.out::println);

    }
    
     @Test
    public void selectByWrapper8() {
        /**
         *  年龄为 30,31,34,35
         *
         *  where age in (30,31,34,35)
         */
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();

        userQueryWrapper.in("age",30,31,34,35);



        List<User> users = userMapper.selectList(userQueryWrapper);
        users.forEach(System.out::println);

    }
    
     @Test
    public void selectByWrapper9() {
        /**
         *  年龄为 30,31,34,35  返回满足条件的一条语句
         *
         *  where age in (30,31,34,35) limit 1;
         */
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();

        userQueryWrapper.in("age",30,31,34,35).last("limit 1");
        
        List<User> users = userMapper.selectList(userQueryWrapper);
        users.forEach(System.out::println);

    }

select 中不返回全部的列字段

 @Test
    public void selectByWrapperSuper(){
        /**
         * 查询名字中包含雨并且年龄小于40  只返回 id和name
         * where name like '%雨%' and age < 40
         */
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.select("id","name").like("name","雨").lt("age",40);

        List<User> users = userMapper.selectList(userQueryWrapper);
        users.forEach(System.out::println);
    }

like 中condition参数作用

//like 中 condition 的作用
    //有如下场景：在前端要通过 name 或者 email 查询用户信息
    //写法如下

    @Test
    public void conditionTest(){
        String name = "王";
        String email = "";

        condition(name,email);
    }

    private void condition(String name,String email){
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        //常规写法
//        if(StringUtils.isNotBlank(name)){
//            userQueryWrapper.like("name",name);
//        }
//        if(StringUtils.isNotBlank(email)){
//            userQueryWrapper.like("email",email);
//        }

        // condition写法
        userQueryWrapper.like(StringUtils.isNotBlank(name),"name",name)
                .like(StringUtils.isNotBlank(email),"email",email);
        List<User> users = userMapper.selectList(userQueryWrapper);
        users.forEach(System.out::println);
    }

实体作为条件构造方法的参数

 @Test
    public void selectByWrapper10() {
        /**
         * 实体作为查询构造器参数
         */

        User user = new User();
        user.setName("向");
        user.setAge(31);
        //将实体作为查询条件时，会把不为null的值放到 where中 且等值查询
        // 实体作为查询条件，不会影响到后面使用 API 作为查询条件，会把所有的条件拼接到 where中
        // 如果认为等值查询不满足需求时：可在实体类字段上添加注解修改，如下所述
        /**
         *     //姓名   姓名字段模糊匹配
         *     @TableField(condition = SqlCondition.LIKE)
         *     private String name;
         */
        //如果认为 SqlCondition类提供的常量还不满足，可自定义
        /**
         * //年龄   年龄 大于    %s表示字段名  <表示大于     #{%s} 表示值
         *     @TableField(condition = "%s < #{%s}")
         *     private Integer age;
         */

        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>(user);

        List<User> users = userMapper.selectList(userQueryWrapper);
        users.forEach(System.out::println);

    }

返回值是 Map的方式

@Test
    public void selectByWrapper11() {
        //返回泛型为Map的
        //在返回的字段比较少时建议使用，因为如果返回字段比较少，使用实体类会有大量 null 值字段，不优雅
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();

        queryWrapper.select("id","name");

        List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
        maps.forEach(map->{
            System.out.println(map.get("name"));
        });
    }
    
    
    
@Test
    public void selectByWrapper12() {
        /**
         * 应用场景2 ：返回不规范的统计结果数据
         *  按照直属上级分组，查询每组的平均年龄，最大年龄，最小年龄
         *  并且只取年龄总和小于500的组
         *
         *  select avg(age) avg_age,max(age) max_age,mix(age) mix_age
         *  from user
         *  group by manager_id
         *  having sum(age) < 500
         */

        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("avg(age) avg_age","max(age) max_age","min(age) min_age")
                .groupBy("manager_id")
                .having("sum(age) < {0}",500);

        List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
        maps.forEach(System.out::println);
    }

Count 的使用

@Test
    public void selectByWrapper13() {

        //返回总条数
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();

        Integer count = userMapper.selectCount(queryWrapper);
        System.out.println("count:"+ count);
    }

查询一条记录

  @Test
    public void selectByWrapper14() {
        
        User user = new User();
        user.setName("刘红雨");
        //返回总条数
        QueryWrapper<User> queryWrapper = new QueryWrapper<>(user);

        user = userMapper.selectOne(queryWrapper);
        System.out.println(user);
    }

查询构造器 Lambda 版

    @Test
    public void selectByWrapper15() {
        /**
         * 查询构造器 Lambda 版
         */

        LambdaQueryWrapper<User> lambdaQuery = new QueryWrapper<User>().lambda();
        lambdaQuery.like(User::getName,"雨").lt(User::getAge,40);
        //where name like '%雨%' and age < 40
        List<User> users = userMapper.selectList(lambdaQuery);
        users.forEach(System.out::println);
    }


    @Test
    public void selectByWrapper16() {
        /**
         * 查询构造器 Lambda 版
         *
         * 名字为 王 姓， 且 (年龄小于40或邮箱不为空)
         * where name '王%' and (age < 40 or email is not null)
         */
        LambdaQueryWrapper<User> lambdaQuery = new QueryWrapper<User>().lambda();
        lambdaQuery.like(User::getName,"王")
                .and(lqw->lqw.lt(User::getAge,40).or().isNotNull(User::getEmail));
        List<User> users = userMapper.selectList(lambdaQuery);
        users.forEach(System.out::println);

    }

简化版(链式)查询构造器 Lambda 版

 @Test
    public void selectByWrapper17() {
        /**
         * 简化版(链式)查询构造器 Lambda 版
         *
         */
        List<User> users = new LambdaQueryChainWrapper<User>(userMapper)
                .like(User::getName, "雨").ge(User::getAge, 20).list();
        users.forEach(System.out::println);

    }

自定义sql(sql写在注解中)

• Mapper

package top.mgy.mybatistest.dao;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import top.mgy.mybatistest.entity.User;

import java.util.List;

public interface UserMapper extends BaseMapper<User> {

    /**
     * 自定义 sql
     * @param wrapper
     * @return
     */
    @Select("select * from user ${ew.customSqlSegment}")
    List<User> selectAll(@Param(Constants.WRAPPER)Wrapper<User> wrapper);
}

 @Test
    public void selectByWrapper18() {
        LambdaQueryWrapper<User> lambdaQuery = new QueryWrapper<User>().lambda();
        lambdaQuery.like(User::getName,"王")
                .and(lqw->lqw.lt(User::getAge,40).or().isNotNull(User::getEmail));
        List<User> users = userMapper.selectAll(lambdaQuery);
        users.forEach(System.out::println);
    }

分页查询

 @Test
    public void selectPage() {
        //分页  返回值是实体对象
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.ge("age",26);

        // 当前页  每页返回多少条    第三个参数传入false只查询记录，不查询总条数
        Page<User> userPage = new Page<>(2, 3);

        Page<User> page = userMapper.selectPage(userPage, queryWrapper);

        System.out.println("总页数：" +page.getPages());
        System.out.println("总记录数："+page.getTotal());
        List<User> records = page.getRecords();
        records.forEach(System.out::println);



        //返回值是 Map
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.ge("age",26);

        // 当前页  每页返回多少条
        IPage<Map<String,Object>> page = new Page<>(2, 3);

        IPage<Map<String, Object>> page1 = userMapper.selectMapsPage(page, queryWrapper);

        System.out.println("总页数：" +page1.getPages());
        System.out.println("总记录数："+page1.getTotal());
        List<Map<String, Object>> records = page1.getRecords();
        records.forEach(System.out::println);

    }