MyBatis-plus基础

标签：queryWrapper name QueryWrapper age 基础 plus MyBatis new id

1.MyBatis-plus简介

官网：https://www.mybatis-plus.com/

2.快速开始(SpringBoot中使用MyBatis-plus的demo)

数据库表user如下：

DROP TABLE IF EXISTS user;

CREATE TABLE user
(
	id BIGINT(20) NOT NULL COMMENT '主键ID',
	name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',
	age INT(11) NULL DEFAULT NULL COMMENT '年龄',
	email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (id)
);

pom中依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
 <dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.1</version>
</dependency>

在application.properties或者application.yml中添加数据库配置

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: 123456
    url: jdbc:mysql://127.0.0.1:3306/ssm?useUnicode=true&characterEncoding=UTF-8
# 配置MyBatis-plus日志
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

创建与数据库表对应的实体类User

public class User {
    // TableId注解用于设置主键，type属性值
    // IdType.AUTO表示使用自动增长产生主键
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;
    private String name;
    private Integer age;
    private String email;

    // get,set,toString
}

创建Mapper

// 继承自MyBatis-plus中的BaseMapper，在UserMapper的
// 实现类对象中可以调用BaseMapper中的方法，实现CRUD
public interface UserMapper extends BaseMapper<User> {

}

在主类上添加MapperScan注解，用于扫描Mapper

@SpringBootApplication
@MapperScan(value = {"com.nrvcer.mapper"})
public class MybatisFirApplication {
    public static void main(String[] args) {
        SpringApplication.run(MybatisFirApplication.class, args);
    }

}

test

@SpringBootTest
@SuppressWarnings(value = "all")
class MybatisFirApplicationTests {
    @Autowired
    private UserMapper userDao;
    @Test
    public void testInsert() {
        User user = new User();
        user.setName("张三");
        user.setAge(23);
        user.setEmail("123456.com");
        int rows = userDao.insert(user);
        System.out.println("添加的记录行数：" + rows);
    }

}

3.CRUD基本用法

CRUD的操作来自BaseMapper接口中的方法。

insert操作：insert()方法返回数据插入成功的记录数。

User user = new User();
user.setName("李四");
user.setAge(66);
user.setEmail("666.com");
int rows = userDao.insert(user);
System.out.println("主键：" + user.getId());   // 4

update操作：判断字段是否要修改：null字段不修改，非null字段修改。实体类的属性是引用类型，有默认值null。

// 其中实体类的属性都为包装类型
User user = new User();
user.setId(2);
user.setEmail("1480034967@com");
// User{id=2, name='null', age=null, email='1480034967@com'}
// null字段name,age对应的User表中name,age字段不会被修改
// UPDATE user SET email=? WHERE id=?
int rows = userDao.updateById(user);

// 其中实体类的属性为基本类型
User user = new User();
user.setId(3);
user.setName("王五");
System.out.println(user);
// User{id=3, name='王五', age=0, email='null'}
// UPDATE user SET name=?, age=? WHERE id=?
// age不是null字段，所以数据库表中的age字段被改为0，email字段不修改
int rows = userDao.updateById(user);

delete操作

// 1. deleteById方法：按主键id删除
// DELETE FROM user WHERE id=?
int rows = userDao.deleteById(1);

// 2. deleteByMap方法：按照条件删除数据，一个或者多个条件封装到Map对象中
// key是列名，value是值
Map<String, Object> map = new HashMap<>();
map.put("name", "王五");
map.put("age", 0);
// DELETE FROM user WHERE name = ? AND age = ?
int rows = userDao.deleteByMap(map);

// 3.deleteBatchIds方法：批处理方式，使用多个主键值，删除数据
List<Integer> list = new ArrayList<>();
list.add(2);
list.add(3);
list.add(4);
// DELETE FROM user WHERE id IN ( ? , ? , ? )
int rows = userDao.deleteBatchIds(list);

select操作

// 1.selectById方法：根据主键id查询
// SELECT id,name,age,email FROM user WHERE id=?
User user = userDao.selectById(5);

// 2.selectBatchIds方法：实现批处理查询，获取到list
List<Integer> list = new ArrayList<>();
list.add(5);
list.add(8);
// SELECT id,name,age,email FROM user WHERE id IN ( ? , ? )
List<User> users = userDao.selectBatchIds(list);

// 3.selectMap方法：使用Map做多条件查询，返回List
// key是字段名，value是字段值
Map<String, Object> map = new HashMap<>();
map.put("name", "李四");
map.put("age", 25);
// SELECT id,name,age,email FROM user WHERE name = ? AND age = ?
List<User> users = userDao.selectByMap(map);

4.ActiveRecord（AR）

1.概述

每一个数据库表对应创建一个类，类的每一个对象实例对应于数据库表中的一行记录。通常表的每个字段在类中都有相应的Field
ActiveRecord负责将自己持久化，在ActiveRecord中封装了对数据库的访问，通过对象自己实现CRUD，实现优雅的数据库操作。
ActiveRecord也封装了部分业务逻辑，可以作为业务对象使用。

2.AR实现CRUD基本操作

创建数据库表对应的实体类Dept

// 使用AR，要求实体类继承MP中的Model
// Model中提供了对数据库的CRUD的操作
public class Dept extends Model<Dept> {
    // 指定主键
    @TableId(value = "id", type = IdType.AUTO)
    // Dept类中的属性名和表中列名一致
    private Integer id;
    private String name;
    private String mobile;
    private Integer manager;
    // get,set
}

定义mapper

// 不使用mapper，也需要定义这个类，MP通过mapper获取到表的结构
// 不定义时，MP报错无法获取表的结构信息
public interface DeptMapper extends BaseMapper<Dept> {
}

AR之insert

Dept dept = new Dept();
dept.setManager(666);
dept.setMobile("16839788");
dept.setName("张三");
// 插入成功则返回true
// INSERT INTO dept ( name, mobile, manager ) VALUES ( ?, ?, ? )
boolean flag = dept.insert();

AR之update

Dept dept = new Dept();
dept.setId(1);
dept.setMobile("11111111");
// 根据主键id更新记录
// 属性值为null的字段对应数据库表中的字段不做更新处理
// UPDATE dept SET mobile=? WHERE id=?
// name,manager字段不更新
boolean flags = dept.updateById();

AR之select

// 按主键字段值进行查询操作
// selectById()方法按实体的主键不能查出数据时，返回null不报错
Dept dept = new Dept();
dept.setId(4);
// SELECT id,name,mobile,manager FROM dept WHERE id=?
Dept select = dept.selectById();

// 按主键字段值进行查询操作
// selectById(主键)方法按实体的主键不能查出数据时，返回null不报错
Dept dept = new Dept();
// SELECT id,name,mobile,manager FROM dept WHERE id=?
Dept select = dept.selectById(3);

5.表和列

1.主键类型

IdType是一个枚举类，定义了主键的类型

public enum IdType {
    AUTO(0),
    NONE(1),
    INPUT(2),
    ASSIGN_ID(3),
    ASSIGN_UUID(4);
}

none:表示没有主键
auto：表示主键的值自动增长
input：手工输入

2.指定表名，使用@TableName注解

定义实体类，表名默认和实体类同名；如果不一致，在实体类定义上面使用@TableName注解。

// 指定数据库中的表名
// Address实体类对应数据库中的user_address表
@TableName(value = "user_address")
public class Address {
    // 指定主键的信息
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;
    private String city;
    private String street;
    private String zipcode;
    // get,set
}

3.指定列名，使用@TableField注解

@TableName(value = "student")
public class Student {
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;
    // value属性表示数据库中表的字段名称
    // 指定实体类属性和列名的对应关系
    @TableField(value = "name")
    private String sname;
    @TableField(value="age")
    private Integer sage;
    // get set
}

4.驼峰命名

数据库中表的列名使用下划线，例如user_name.实体类中的属性名采用驼峰命名方式,例如String userName。MyBatis默认支持这种规则。

6.自定义SQL

创建实体类

@TableName(value = "student")
public class Student {
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;
    private String name;
    private Integer age;
    private String email;

    // get set
}

创建Mapper

public interface StudentMapper extends BaseMapper<Student> {
    // 自定义CURD操作 
    int insertStudent(Student student);
    List<Student> selectByName(String name);
}

新建SQL映射XML文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.nrvcer.mapper.StudentMapper">
    <insert id="insertStudent">
        insert into student(name, age, email) values (#{name},#{age},#{email})
    </insert>
    <select id="selectByName" resultType="com.nrvcer.entity.Student">
        select * from student where name=#{name}
    </select>
</mapper>

配置XML文件位置

# 配置MyBatis-plus日志
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    # XML文件位置
  mapper-locations: classpath*:mapper/*Mapper.xml

测试

@SpringBootTest
public class TableNameTest {

    @Resource
    private StudentMapper studentDao;
    @Test
    public void testInsert() {
        Student student = new Student();
        student.setAge(25);
        student.setName("张三");
        student.setEmail("111.com");
        // insert into student(name, age, email) values (?,?,?)
        int flags = studentDao.insertStudent(student);
        System.out.println(flags);
    }
    @Test
    public void testSelect() {
        // select * from student where name=?
        List<Student> students = studentDao.selectByName("张三");
        for (Student student : students) {
            System.out.println(student);
        }
    }
}

7.查询和分页

QueryMapper和UpdateMapper的父类AbstractMapper用于生成SQL的where条件，entity属性也用于生成SQL的where条件。Wrapper称为构造器，构造条件的。

1.条件

allEq:基于Map的键值对，该Map中组装多个条件。根据条件判断相等

// 1.allEq(Map<R, V> params)方法的使用
 QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
Map<String, Object> params = new HashMap<>();
params.put("name", "张三");
params.put("age", 23);
queryWrapper.allEq(params);
// SELECT id,name,age,email FROM student WHERE (name = ? AND age = ?)
List<Student> students = studentDao.selectList(queryWrapper);

// 2.allEq(Map<R, V> params, boolean null2IsNull)方法的使用
// 第二参数传递true表示处理null值，where条件中加入字段is null
// 第二参数传递false表示忽略null,不作为where条件
QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
Map<String, Object> params = new HashMap<>();
params.put("name", "张三");
params.put("age", null);
queryWrapper.allEq(params, true);
// SELECT id,name,age,email FROM student WHERE (name = ? AND age IS NULL)
List<Student> students = studentDao.selectList(queryWrapper);

QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
Map<String, Object> params = new HashMap<>();
params.put("name", "张三");
params.put("age", null);
queryWrapper.allEq(params, false);
// SELECT id,name,age,email FROM student WHERE (name = ?)
List<Student> students = studentDao.selectList(queryWrapper);

eq:等于

QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name", "张三");
queryWrapper.eq("age", 23);
// SELECT id,name,age,email FROM student WHERE (name = ? AND age = ?)
List<Student> students = studentDao.selectList(queryWrapper);

ne:不等于

QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
queryWrapper.ne("name", "李四");
// SELECT id,name,age,email FROM student WHERE (name <> ?)
List<Student> students = studentDao.selectList(queryWrapper);

gt:大于

QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
queryWrapper.gt("age", 20);
// SELECT id,name,age,email FROM student WHERE (age > ?)
List<Student> students = studentDao.selectList(queryWrapper);

ge:大于等于

QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
queryWrapper.ge("age", 23);
// SELECT id,name,age,email FROM student WHERE (age >= ?)
List<Student> students = studentDao.selectList(queryWrapper);

lt:小于

QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
queryWrapper.lt("age", 30);
// SELECT id,name,age,email FROM student WHERE (age < ?)
List<Student> students = studentDao.selectList(queryWrapper);

le:小于等于

QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
queryWrapper.le("age", 23);
// SELECT id,name,age,email FROM student WHERE (age <= ?)
List<Student> students = studentDao.selectList(queryWrapper);

between：在两个值范围之间,相当于大于等于某个值并且小于等于某个值

QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
queryWrapper.between("age", 23, 25);
// SELECT id,name,age,email FROM student WHERE (age BETWEEN ? AND ?)
List<Student> students = studentDao.selectList(queryWrapper);

notBetween：不在两个值范围之间

QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
queryWrapper.notBetween("age", 23, 25);
// SELECT id,name,age,email FROM student WHERE (age NOT BETWEEN ? AND ?)
List<Student> students = studentDao.selectList(queryWrapper);

like:匹配某个值 "%值%"

QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name", "张");
// SELECT id,name,age,email FROM student WHERE (name LIKE ?)
//  Parameters: %张%(String)
List<Student> students = studentDao.selectList(queryWrapper);

notLike:不匹配 "%值%"

QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
queryWrapper.notLike("name", "三");
// SELECT id,name,age,email FROM student WHERE (name NOT LIKE ?)
// Parameters: %三%(String)
List<Student> students = studentDao.selectList(queryWrapper);

LikeRight:匹配 like "值%"

// 匹配以张开头的
QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name", "张");
// SELECT id,name,age,email FROM student WHERE (name LIKE ?)
// Parameters: 张%(String)
List<Student> students = studentDao.selectList(queryWrapper);

LikeLeft:匹配 like "%值"

// 匹配以张结尾的,比如刘张
QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
queryWrapper.likeLeft("name", "张");
// SELECT id,name,age,email FROM student WHERE (name LIKE ?)
// Parameters: %张(String)
List<Student> students = studentDao.selectList(queryWrapper);

isNull:判断字段值为null

 QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
queryWrapper.isNull("age");
// SELECT id,name,age,email FROM student WHERE (age IS NULL)
List<Student> students = studentDao.selectList(queryWrapper);

isNotNull:判断字段值不为null

QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
queryWrapper.isNotNull("age");
// SELECT id,name,age,email FROM student WHERE (age IS NOT NULL)
List<Student> students = studentDao.selectList(queryWrapper);

in:in 后面的值列表,表示筛选符合列表中的条件的。

QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
queryWrapper.in("name", "张三", "李四");
// SELECT id,name,age,email FROM student WHERE (name IN (?,?))
List<Student> students = studentDao.selectList(queryWrapper);

notIn:不在列表中的

QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
queryWrapper.notIn("name", "张三", "李四");
// SELECT id,name,age,email FROM student WHERE (name NOT IN (?,?))
List<Student> students = studentDao.selectList(queryWrapper);

inSql:常用来做子查询，类似in()

QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
// 查询和表中记录id=1的年龄相同的记录
queryWrapper.inSql("age", "select age from student where id=1");
// SELECT id,name,age,email FROM student WHERE (age IN (select age from student where id=1))
List<Student> students = studentDao.selectList(queryWrapper);

notInSql类似notIn()

QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
// 查询和表中记录id=1的年龄不相同的记录
queryWrapper.notInSql("age", "select age from student where id=1");
// SELECT id,name,age,email FROM student WHERE (age NOT IN (select age from student where id=1))
List<Student> students = studentDao.selectList(queryWrapper);

groupBy:基于多个字段分组

// 按照姓名进行分组
QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
// SELECT name,count(*) personNumbers FROM student
queryWrapper.select("name", "count(*) personNumbers");
queryWrapper.groupBy("name");
// SELECT name,count(*) personNumbers FROM student GROUP BY name
List<Student> students = studentDao.selectList(queryWrapper);

orderByAsc:按字段升序

// 按照姓名，年龄升序
QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
queryWrapper.orderByAsc("name", "age");
// SELECT id,name,age,email FROM student ORDER BY name ASC,age ASC
List<Student> students = studentDao.selectList(queryWrapper);

orderByDesc:按字段降序
orderBy:每个字段指定排序方向

// orderBy()方法的第一参数：
// 第一参数为true：SELECT id,name,age,email FROM student ORDER BY age ASC
// 第一参数为false：SELECT id,name,age,email FROM student
QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
// 指定年龄字段升序
queryWrapper.orderBy(true, true, "age");
List<Student> students = studentDao.selectList(queryWrapper);

or:连接条件用or

QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name", "张三")
        .or()
        .eq("age", 23);
// SELECT id,name,age,email FROM student WHERE (name = ? OR age = ?)
List<Student> students = studentDao.selectList(queryWrapper);

and:连接条件用and，默认是and
last:拼接SQL语句,将SQL语句拼接到MP的SQL语句的末尾

QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name", "张三")
        .or()
        .eq("age", 23)
        .last("limit 2");
// SELECT id,name,age,email FROM student WHERE (name = ? OR age = ?) limit 2
List<Student> students = studentDao.selectList(queryWrapper);

exists:做判断，条件为真执行查询。EXISTS(sql语句)

QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
// SELECT id,name,age,email FROM student WHERE (EXISTS (select * from student where age > 40))
queryWrapper.exists("select * from student where age > 40");
List<Student> students = studentDao.selectList(queryWrapper);

notExists:是exists的相反操作

2.分页

实现物理分页需要配置分页插件。默认是内存分页。

// 新版本
@Configuration
public class MyBatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.H2));
        return interceptor;
    }
}

分页查询示例

QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
queryWrapper.lt("age", 35);
IPage<Student> iPage = new Page<>();
// 设置分页信息
iPage.setCurrent(1);        // 第一页
iPage.setSize(2);           // 每页的记录数
// SELECT id,name,age,email FROM student WHERE (age < ?) LIMIT ?
IPage<Student> pageResult = studentDao.selectPage(iPage, queryWrapper);
// 获取分页后的记录
List<Student> records = pageResult.getRecords();
// 分页的信息
System.out.println(records);
System.out.println("总页数：" + pageResult.getPages());//3
System.out.println("总记录数:" + pageResult.getTotal());//5
System.out.println("当前页码：" + pageResult.getCurrent());//1
System.out.println("每页大小：" + pageResult.getSize());//2

8.MP生成器

参考官网

9.MP中的自动填充

MP的自动填充功能，可以完成字段的赋值工作。

数据库user表中存在create_time和update_time两个字段
实体类上对应的字段添加自动填充注解

@TableField(fill = FieldFill.INSERT)
private Date createTime;
@TableField(fill = FieldFill.INSERT_UPDATE)
private Date updateTime;

实现元对象处理器接口

@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
    @Override
    public void insertFill(MetaObject metaObject) {
        this.setFieldValByName("createTime", new Date(), metaObject);
        this.setFieldValByName("updateTime", new Date(), metaObject);
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        this.setFieldValByName("updateTime", new Date(), metaObject);
    }
}

10.MP中使用乐观锁

数据库表user添加version字段
实体类对应的version字段添加@Version注解
配置乐观锁插件

@Configuration
@MapperScan("com.nrvcer.mapper")
public class MybatisPlusConfig {
    @Bean
    public OptimisticLockerInnerInterceptor optimisticLockerInnerInterceptor() {
        return new OptimisticLockerInnerInterceptor();
    }

}

11.逻辑删除

逻辑删除：数据库表中选取一个字段表示是否被删除的状态。

数据库表user添加deleted字段
实体类对应的deleted字段添加@TableLogic注解
application.properties配置如下：

mybatis-plus.global-config.db-config.logic-delete-value=1
mybatis-plus.global-config.db-config.logic-not-delete-value=0

标签：queryWrapper,name,QueryWrapper,age,基础,plus,MyBatis,new,id
From： https://www.cnblogs.com/xiaocer/p/16625560.html