mybatiplus的apply_MyBatisPlus 入门到上手 (一)

54 阅读 0 评论 36 点赞

我是靠谱客的博主着急超短裙，最近开发中收集的这篇文章主要介绍mybatiplus的apply_MyBatisPlus 入门到上手 (一)，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一：简介

MyBatis-Plus(简称 MP)是一个MyBatis的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生。

二：ORM框架

ORM(Object Relational Mapping)框架采用元数据来描述对象与关系映射的细节，元数据一般采用XML格式，并且存放在专门的对象一映射文件中。只要提供了持久化类与表的映射关系，ORM框架在运行时就能参照映射文件的信息，把对象持久化到数据库中。

三：MP与JPA对比

在使用的角度来说少写一句sql就少写一句，在单表操做过程中两者都基本都是能满足这个特点。关于学习难度来说其实两者差不多，没有另外拔高知识点，只是在底层原理不同。

四：构建项目

前提准备

创建数据库：mp 数据表：tb_user

创建表

CREATE TABLE `tb_user` (

`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',

`user_name` varchar(20) DEFAULT NULL COMMENT '用户名',

`password` varchar(20) DEFAULT NULL COMMENT '密码',

`name` varchar(30) DEFAULT NULL COMMENT '姓名',

`age` int(11) DEFAULT NULL COMMENT '年龄',

`email` varchar(50) DEFAULT NULL COMMENT '邮箱',

`version` int(10) DEFAULT '1',

`deleted` int(1) DEFAULT '0' COMMENT '1代表删除，0代表未删除',

`sex` int(1) DEFAULT '1' COMMENT '1-男，2-女',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

我使用的是IDEA工具

Lombok

springBoot:2.2.4

mybatisPlus:3.1.1

mysql:8.1.19

pom文件

com.baomidou

mybatis-plus-boot-starter

3.1.1

mysql

mysql-connector-java

8.0.19

org.springframework.boot

spring-boot-starter-web

org.springframework.boot

spring-boot-starter-logging

org.projectlombok

lombok

true

org.springframework.boot

spring-boot-starter-test

test

org.junit.vintage

junit-vintage-engine

junit

test

org.slf4j

slf4j-log4j12

org.apache.commons

commons-lang3

3.8.1

com.baomidou

mybatis-plus-extension

3.1.1

com.baomidou

mybatis-plus-generator

3.1.1

org.springframework.boot

spring-boot-starter-freemarker

org.springframework.boot

spring-boot-maven-plugin

yml文件

spring:

application:

name: mybatisPlusSpringBoot

datasource:

driver-class-name: com.mysql.cj.jdbc.Driver

url: jdbc:mysql://127.0.0.1:3306/mp?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=utf8

username: root

password: root

mybatis-plus:

configuration:

log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

#关闭自动驼峰映射，该参数不能和mybatis-plus: config-location同时存在:

# map-underscore-to-camel-case: false

#全局地开启或关闭配置文件中的所有映射器已经配置的任何缓存，默认为 true。

#cache-enabled: false

#配置自定义的mapper文件 (多表查询适用)

mapper-locations: classpath*:mybatis/*.xml

#配置实体对象扫描包===在mapper.xml中简化使用

type-aliases-package: com.hhz.mp.pojo

#配置全局主键生成策略

# global-config:

# db-config:

# id-type: auto

#配置全局表名前缀

# global-config:

# db-config:

# table-prefix: tb_

#乐观锁配置

global-config:

db-config:

# 逻辑已删除值(默认为 1)

logic-delete-value: 1

# 逻辑未删除值(默认为 0)

logic-not-delete-value: 0

# 枚举包扫描

type-enums-package: com.hhz.mp.enums

创建实体类

@Data

@NoArgsConstructor

@AllArgsConstructor

@EqualsAndHashCode(callSuper = true)

@TableName("tb_user")

public class User extends Model {

//设置按照数据库自增长

@TableId(type = IdType.AUTO)

private Long id;

//下划线可自动转驼峰命名这里可以不写

@TableField(value = "user_name")

private String userName;

//查询时不返回该字段的值

//fill =FieldFill.INSERT 对插入密码的时候可以进行填充

@TableField(select = false, fill = FieldFill.INSERT)

private String password;

private String name;

private Integer age;

//字段名与数据库名不一致

@TableField(value = "email")

private String mail;

//忽略在数据库的字段

@TableField(exist = false)

private String address;

//添加版本信息__乐观锁

@Version

private Integer version;

//逻辑删除

@TableLogic

private Integer deleted;

//配置枚举值

private SexEnum sex;

public User(String userName, Integer age) {

this.userName = userName;

this.age = age;

}

public User(String userName, String password, String name, Integer age, String mail) {

this.userName = userName;

this.password = password;

this.name = name;

this.age = age;

this.mail = mail;

}

public User(Long id) {

this.id = id;

}

public User(Long id, String userName, String password, String name, Integer age, String mail) {

this.id = id;

this.userName = userName;

this.password = password;

this.name = name;

this.age = age;

this.mail = mail;

}

创建dao层

@Repository

public interface UserMapper extends BaseMapper {

}

创建一个测试类

@Slf4j

@RunWith(SpringJUnit4ClassRunner.class)

@SpringBootTest

public class MyApplicationTest {

@Autowired

private UserMapper userMapper;

}

五：正式开始

5.1 基础增删改

如果id未设置自增长默认是推特的雪花算法

mysql设置主键类型

@TableId(type = IdType.AUTO)

oracle设置主键类型

yml文件中

mybatis-plus:

global-config:

#主键类型 0:"数据库ID自增", 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";

id-type: 1

# Sequence序列接口实现类配置

key-generator: com.baomidou.mybatisplus.incrementer.OracleKeyGenerator

实体类上设置

@KeySequence(value = "序列名称",clazz = 主键类型.class)

主键上设置

@TableId(type=IdType.INPUT)

新增

@Test

public void testInsert() {

User user = new User("guanyu", "222", "关羽", 42, "guanyu@shu.com");

int insert = userMapper.insert(user);

Long id = user.getId();

//改变的记录数

System.err.println("change:" + insert);

//id未设置增长策略结果：1233004593171144706

System.err.println("id:" + id);

}

根据id新增

@Test

public void testupdateById() {

User user = new User(9L, "zhuge", "111", "诸葛", 36, "zhuge@shu.com");

int update = userMapper.updateById(user);

//改变的行数

System.err.println(update);

}

根据id删除

@Test

public void testdeleteById() {

int result = userMapper.deleteById(9L);

System.out.println(result);

}

构造map条件删除

@Test

public void testdeleteByMap() {

//DELETE FROM tb_user WHERE name = ?

HashMap map = new HashMap<>();

map.put("name", "关羽");

int result = userMapper.deleteByMap(map);

System.err.println(result);

}

构造wrapper条件删除

@Test

public void testdelete() {

//第一种办法

//DELETE FROM tb_user WHERE (password = ? AND age <= ?)

QueryWrapper wrapper1 = new QueryWrapper<>();

wrapper1.eq("password", "123").le("age", 21);

//第二种办法:直接面向对象但是大于小于不好使用单个删除建议使用方法二

//DELETE FROM tb_user WHERE password=? AND age=?

User user = new User();

user.setPassword("123");

user.setAge(21);

QueryWrapper wrapper2 = new QueryWrapper<>(user);

int result = userMapper.delete(wrapper2);

System.err.println(result);

}

批量删除

@Test

public void testdeleteBatchIds() {

//根据id批量删除

//DELETE FROM tb_user WHERE id IN ( ? , ? )

List longs = Arrays.asList(6L, 7L);

userMapper.deleteBatchIds(longs);

}

根据id更新

@Test

public void testupdateById() {

User user = new User(9L, "zhuge", "111", "诸葛", 36, "zhuge@shu.com");

int update = userMapper.updateById(user);

//改变的行数

System.err.println(update);

}

根据条件更新====QueryWrapper

@Test

public void testupdate() {

User user = new User();

//设置参数

user.setAge(29);

QueryWrapper wrapper = new QueryWrapper<>();

//构建where条件user_name为zhuge的

wrapper.eq("user_name", "zhuge");

// UPDATE tb_user SET age=? WHERE user_name = ?

int update = userMapper.update(user, wrapper);

//改变的行数

System.err.println(update);

}

根据条件更新2====UpdateWrapper

@Test

public void testupdate2() {

UpdateWrapper wrapper = new UpdateWrapper<>();

//构建where条件 user_name为zhuge并且设置修改的参数

wrapper.set("password", "abc456").eq("user_name", "zhuge");

//UPDATE tb_user SET password = ? WHERE AND user_name = ?

int update = userMapper.update(null, wrapper);

System.err.println(update);

}

5.2 mybatisPlus的查询

根据id查询

@Test

public void testselectById() {

User user = userMapper.selectById(3L);

System.err.println(user);

}

查询所有

@Test

public void testSelect() {

List userList = userMapper.selectList(null);

for (User user : userList) {

System.out.println(user);

}

根据多个id批量查询

@Test

public void testSelectOne() {

List longs = Arrays.asList(4L, 5L);

List users = userMapper.selectBatchIds(longs);

users.forEach(System.out::println);

}

根据条件查询一个

@Test

public void testSelectBatchIds() {

//没有数据返回null 多条数据报错

QueryWrapper wrapper = new QueryWrapper<>();

wrapper.eq("name", "张三");

User user = userMapper.selectOne(wrapper);

System.err.println(user);

}

查询记录数

@Test

public void testSelectCount() {

//所有记录数 wrapper 设为空即可

//Integer integer = userMapper.selectCount(null);

//查询年龄大于25岁的有多少人

QueryWrapper wrapper = new QueryWrapper<>();

wrapper.gt("age", 25);

Integer integer = userMapper.selectCount(wrapper);

System.err.println(integer);

}

分页查询

前题注入分页插件

@Bean

public PaginationInterceptor paginationInterceptor() {

PaginationInterceptor paginationInterceptor = new PaginationInterceptor();

//注入sql分析插件(生产环境最好不用) 阻止全表更新

List sqlParserList = new ArrayList<>();

sqlParserList.add(new BlockAttackSqlParser());

paginationInterceptor.setSqlParserList(sqlParserList);

return paginationInterceptor;

}

分页查询

@Test

public void testSelectPage() {

//SELECT id, email AS mail, name, user_name, age FROM tb_user WHERE (age > ?) ORDER BY age ASC LIMIT ?,?

//查询年龄大于20岁的第二页的数据

QueryWrapper wrapper = new QueryWrapper<>();

wrapper.gt("age", 20).orderByAsc("age");

//查询第二页数据每页三条 current：0和1都是只第一页

Page page = new Page<>(2L, 3L);

IPage userIPage = userMapper.selectPage(page, wrapper);

System.err.println("总记录:" + userIPage.getRecords().toString());

System.err.println("总页数:" + userIPage.getPages());

System.err.println("总条数:" + userIPage.getTotal());

System.err.println("当前页:" + userIPage.getCurrent());

System.err.println("页大小:" + userIPage.getSize());

}

总记录:[User(id=3, userName=wangwu, password=null, name=王五, age=28, mail=test3@itcast.cn, address=null, version=1, deleted=0, sex=男), User(id=11, userName=sunce, password=null, name=孙策, age=28, mail=null, address=null, version=1, deleted=0, sex=男), User(id=20, userName=null, password=null, name=小乔, age=28, mail=null, address=null, version=1, deleted=0, sex=女)]

总页数:4

总条数:12

当前页:2

页大小:3

分页查询只要记录不需要记录数

@Test

public void testSelectPage2() {

//查询年龄大于20岁的第二页的数据

QueryWrapper wrapper = new QueryWrapper<>();

wrapper.gt("age", 20).orderByAsc("age");

//查询第二页数据每页三条不查询总记录数

//Page(long current, long size, boolean isSearchCount)

Page page = new Page<>(2L, 3L, false);

IPage userIPage = userMapper.selectPage(page, wrapper);

System.err.println("总记录:" + userIPage.getRecords().toString());

System.err.println("总页数:" + userIPage.getPages());

System.err.println("总条数:" + userIPage.getTotal());

System.err.println("当前页:" + userIPage.getCurrent());

System.err.println("页大小:" + userIPage.getSize());

}

总页数:0

总条数:0

当前页:2

页大小:3

自定义mapper.xml的使用

在yml文件中添加

#配置自定义的mapper文件 (多表查询适用)

mapper-locations: classpath*:mybatis/*.xml

#配置实体对象扫描包===>在mapper.xml中简化使用

type-aliases-package: com.hhz.mp.pojo

在resources中建，mybatis文件夹再在下面建立userMapper.xml

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

select * from tb_user where id = #{id}

select * from tb_user ${ew.customSqlSegment}

在dao层的接口中添加

//在mapper.xml中写SQL

User getById(Long id);

//自定义sql

@Select("select * from tb_user ${ew.customSqlSegment}")

List selectAll(@Param(Constants.WRAPPER) Wrapper wrapper);

//自定义分页在mapper.xml中写SQL

IPage selectPageUser(Page page, @Param(Constants.WRAPPER) Wrapper wrapper);

根据id查询

@Test

public void testSelectMapper() {

User user = userMapper.getById(2L);

System.out.println(user);

}

自定义sql的使用

@Test

public void selectMy() {

//SELECT * FROM tb_user WHERE age > ?

QueryWrapper wrapper = new QueryWrapper<>();

wrapper.gt("age", 30);

List userList = userMapper.selectAll(wrapper);

for (User user : userList) {

System.out.println(user);

}

自定义分页

@Test

public void selectPageUser() {

Page page = new Page<>(1, 3);

QueryWrapper wrapper = new QueryWrapper<>();

wrapper.gt("age", 25);

IPage userIPage = userMapper.selectPageUser(page, wrapper);

System.err.println("总记录:" + userIPage.getRecords().toString());

System.err.println("总条数:" + userIPage.getTotal());

}

5.3 wrapper 条件构造器

allEq(Map params) 根据map给定字段的条件查询

@Test

public void testllEq() {

//SELECT id, email AS mail, name, user_name, age FROM tb_user WHERE (password IS NULL AND user_name = ? AND age = ?)

QueryWrapper wrapper = new QueryWrapper<>();

Map map = new HashMap<>();

map.put("age", 24);

map.put("user_name", "sunqi");

map.put("password", null);

wrapper.allEq(map);

List users = userMapper.selectList(wrapper);

users.forEach(System.err::println);

}

allEq(Map params, boolean null2IsNull) 根据map给定字段的条件查询过滤字段为空的数据

@Test

public void testAllEq2() {

//SELECT id, email AS mail, name, user_name, age FROM tb_user WHERE (user_name = ? AND age = ?)

QueryWrapper wrapper = new QueryWrapper<>();

Map map = new HashMap<>();

map.put("age", 24);

map.put("user_name", "sunqi");

map.put("password", null);

wrapper.allEq(map, false);

List users = userMapper.selectList(wrapper);

users.forEach(System.err::println);

}

@Test

public void testAllEq3() {

QueryWrapper wrapper = new QueryWrapper<>();

Map map = new HashMap<>();

map.put("age", 24);

map.put("user_name", "sunqi");

map.put("password", null);

wrapper.allEq((k, v) -> k.equals("age") || k.equals("name") || k.equals("password"), map, false);

List users = userMapper.selectList(wrapper);

users.forEach(System.err::println);

}

eq 等于

ge 大于等于

in 在范围内

gt 大于

lt 小于

le 小于等于

between 两个之间

isNotNULL 非空

@Test

public void testEq() {

QueryWrapper wrapper = new QueryWrapper<>();

//SELECT id, email AS mail, name, user_name, age FROM tb_user WHERE (password = ? AND age >= ? AND name IN (?, ?, ?))

wrapper.eq("password", "123456")

.ge("age", 20)

.in("name", "李四", "王五", "赵六");

List users = this.userMapper.selectList(wrapper);

users.forEach(System.err::println);

}

模糊查询like

/**

* 模糊查询

* like(R column, Object val) LIKE '%值%'

* like(boolean condition, R column, Object val)

* likeLeft(boolean condition, R column, Object val) LIKE '%值'

* likeRight(boolean condition, R column, Object val) LIKE '值%'

@Test

public void testLike() {

QueryWrapper wrapper = new QueryWrapper<>();

// wrapper.like("name", "孙");

User user = new User();

user.setName("孙");

//condition 条件判断如果为false 该条就会被忽略

wrapper.like(StringUtils.isNotBlank(user.getName()), "name", "孙");

List userList = userMapper.selectList(wrapper);

userList.forEach(System.err::println);

}

排序

/**

* orderByAsc 默认顺序

* orderByDesc 倒序

@Test

public void testOrderBy() {

QueryWrapper wrapper = new QueryWrapper<>();

//先按年龄倒序排序年龄一致按照user_name

wrapper.orderByDesc("age", "user_name");

List userList = userMapper.selectList(wrapper);

userList.forEach(System.err::println);

}

or 逻辑关联

/**

* Or 逻辑关联

* 查找姓名为孙权或者年龄为20的人

* SELECT id, email AS mail, name, user_name, age FROM tb_user WHERE (name = ? OR age = ?)

@Test

public void testOr() {

QueryWrapper wrapper = new QueryWrapper<>();

//先按年龄倒序排序年龄一致按照user_name

wrapper.eq("name", "孙权").or().eq("age", "20");

List userList = userMapper.selectList(wrapper);

userList.forEach(System.err::println);

}

条件逻辑关联

/**

* or(Function func) 逻辑关联

* 查找姓氏为李或者年龄28到30的女性

* SELECT id, user_name, name, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 AND name LIKE ? OR (age BETWEEN ? AND ? AND sex = ?)

@Test

public void testOr2() {

QueryWrapper wrapper = new QueryWrapper<>();

//先按年龄倒序排序年龄一致按照user_name

wrapper.likeRight("name", "李").or(wrap -> wrap.between("age", 28, 30).eq("sex", SexEnum.WOMAN));

List userList = userMapper.selectList(wrapper);

userList.forEach(System.err::println);

}

select 选择需要的字段

/**

* select 选择需要的字段

* SELECT name, age FROM tb_user

@Test

public void testSelect() {

QueryWrapper wrapper = new QueryWrapper<>();

//先按年龄倒序排序年龄一致按照user_name

wrapper.select("name", "age");

List userList = userMapper.selectList(wrapper);

userList.forEach(System.err::println);

}

select 排除字段

/**

* select 排除字段 deleted version email

* SELECT id, user_name, password, name, age, sex FROM tb_user WHERE deleted = 0

@Test

public void testSelect2() {

QueryWrapper wrapper = new QueryWrapper<>();

//先按年龄倒序排序年龄一致按照user_name

wrapper.select(User.class, t -> !t.getColumn().equals("deleted") &&

!t.getColumn().equals("version") &&

!t.getColumn().equals("email")

);

List userList = userMapper.selectList(wrapper);

userList.forEach(System.err::println);

}

apply 拼接 sql

/**

* apply

* SELECT id, user_name, name, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 AND age BETWEEN ? AND ?

* apply("date_format(dateColumn,'%Y-%m-%d') = {0}", "2008-08-08")--->date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")

@Test

public void testApply() {

QueryWrapper wrapper = new QueryWrapper<>();

//拼接sql，可以直接在applySql中输入值，但是有注入风险，用{}方式会更好

//wrapper.between("age", 30, 42);

//wrapper.apply("age between 30 and 42");

wrapper.apply("age between {0} and {1}", 30, 42);

List userList = userMapper.selectList(wrapper);

userList.forEach(System.err::println);

}

/**

* in

* SELECT id, user_name, name, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 AND age IN (?, ?, ?)

@Test

public void testIn() {

QueryWrapper wrapper = new QueryWrapper<>();

wrapper.in("age", 20, 30, 40);

List userList = userMapper.selectList(wrapper);

userList.forEach(System.err::println);

}

inSql 子查询

/**

* inSql 子查询

* 查询性别为女性并且和年龄和孙姓的有一样的

* SELECT id, user_name, name, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 AND sex = ? AND age IN (SELECT age FROM tb_user WHERE name LIKE '孙%')

@Test

public void testInSQL() {

QueryWrapper wrapper = new QueryWrapper<>();

wrapper.eq("sex", SexEnum.WOMAN).inSql("age", "select age from tb_user where name like '孙%'");

List userList = userMapper.selectList(wrapper);

userList.forEach(System.err::println);

}

having

/**

* having

@Test

public void testHaving() {

QueryWrapper wrapper = new QueryWrapper<>();

wrapper.groupBy("id").having("age > {0}", 30);

List userList = userMapper.selectList(wrapper);

userList.forEach(System.err::println);

}

nested(Function func) 嵌套语句

/**

* nested(Function func) 嵌套语句

* 查询姓名为李开头或者是女性或密码是888888的人

* SELECT id, user_name, nme, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 AND (name LIKE ? OR (sex <> ?)) OR password = ?

@Test

public void testNested() {

QueryWrapper wrapper = new QueryWrapper<>();

wrapper.nested(wr -> wr.likeRight("name", "李").or(w -> w.ne("sex", SexEnum.MAN))).or().eq("password", "888888");

List userList = userMapper.selectList(wrapper);

userList.forEach(System.err::println);

}

last 在最后拼接语句

/**

* last 在最后拼接语句

* SELECT id, user_name, name, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 LIMIT 2

@Test

public void testLast() {

QueryWrapper wrapper = new QueryWrapper<>();

wrapper.last("limit 2");

List userList = userMapper.selectList(wrapper);

userList.forEach(System.err::println);

}

exists

/**

* exists

* 拼接exists语句子查询返回true或者false 作为条件应用到外层sql

@Test

public void testExists() {

//SELECT id, user_name, name, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 AND EXISTS (SELECT * FROM tb_user WHERE age > 60)

QueryWrapper wrapper = new QueryWrapper<>();

wrapper.exists("select * from tb_user where age >60");

List userList = userMapper.selectList(wrapper);

userList.forEach(System.err::println);

}

5.3.2 wrapper Lambda条件构造器

lambda根据条件查询1

@Test

public void selectLambda() {

//创建对象的三种方式

LambdaQueryWrapper lambda = new QueryWrapper().lambda();

LambdaQueryWrapper lambdaQueryWrapper = new LambdaQueryWrapper<>();

LambdaQueryWrapper lambdaQuery = Wrappers.lambdaQuery();

//SELECT id, user_name, name, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 AND name LIKE ? AND age < ?

lambdaQuery.like(User::getName, "李").lt(User::getAge, 40);

List users = userMapper.selectList(lambdaQuery);

users.forEach(System.err::println);

}

lambda根据条件查询2

/**

* lambda根据条件查询

* 查询姓名为孙姓并且 (年龄小于40或者邮箱不为空)

* SELECT id, user_name, name, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 AND name LIKE ? AND (age < ? OR email IS NOT NULL)

@Test

public void selectLambda2() {

LambdaQueryWrapper wrapper = new QueryWrapper().lambda();

wrapper.likeRight(User::getName, "孙")

.and(e -> e.lt(User::getAge, 40).or().isNotNull(User::getMail));

List users = userMapper.selectList(wrapper);

users.forEach(System.err::println);

}

LambdaQueryChainWrapper

/**

* 3.0.7后的新的wrapper 可以直接返回结果

* LambdaQueryChainWrapper