MybatisPlus使用详情

特种作战 發表於 2025-7-8 19:47:00

<h1 id="一简介">一、简介</h1>
<h2 id="11-概述">1.1 概述</h2>
<p><code>MyBatis-Plus</code>（简称<code>MP</code>）是一个<code>MyBatis</code>的增强工具，在<code>MyBatis</code>的基础上只做增强不做改变，为简化开发、提高效率而生。</p>
<p><code>MyBatisPlus</code>官网：https://baomidou.com/</p>
<h2 id="12-特性">1.2 特性</h2>
<ul>
<li>无侵入：只做增强不做改变，引入它不会对现有工程产生影响，如丝般顺滑</li>
<li>损耗小：启动即会自动注入基本<code>CURD</code>，性能基本无损耗，直接面向对象操作</li>
<li>强大的<code>CRUD</code>操作：内置通用<code>Mapper</code>、通用<code>Service</code>，仅仅通过少量配置即可实现单表大部分<code>CRUD</code>操作，更有强大的条件构造器，满足各类使用需求</li>
<li>支持<code>Lambda</code>形式调用：通过<code>Lambda</code>表达式，方便的编写各类查询条件，无需再担心字段写错</li>
<li>支持主键自动生成：支持多达<code>4</code>种主键策略（内含分布式唯一<code>ID</code>生成器-Sequence），可自由配置，完美解决主键问题</li>
<li>支持<code>ActiveRecord</code>模式：支持<code>ActiveRecord</code>形式调用，实体类只需继承<code>Model</code>类即可进行强大的<code>CRUD</code>操作</li>
<li>支持自定义全局通用操作：支持全局通用方法注入（Write once, use anywhere）</li>
<li>内置代码生成器：采用代码或者<code>Maven</code>插件可快速生成<code>Mapper</code>、<code>Model</code>、<code>Service</code>、<code>Controller</code>层代码，支持模板引擎，更有超多自定义配置等您来使用</li>
<li>内置分页插件：基于<code>MyBatis</code>物理分页，开发者无需关心具体操作，配置好插件之后，写分页等同于普通<code>List</code>查询</li>
<li>分页插件支持多种数据库：支持<code>MySQL</code>、<code>MariaDB</code>、<code>Oracle</code>、<code>DB2</code>、<code>H2</code>、<code>HSQL</code>、<code>SQLite</code>、<code>Postgre</code>、<code>SQLServer</code>等多种数据库</li>
<li>内置性能分析插件：可输出<code>SQL</code>语句以及其执行时间，建议开发测试时启用该功能，能快速揪出慢查询</li>
<li>内置全局拦截插件：提供全表<code>delete</code>、<code>update</code>操作智能分析阻断，也可自定义拦截规则，预防误操作</li>
</ul>
<h2 id="13-支持的数据库">1.3 支持的数据库</h2>
<p>任何能使用<code>MyBatis</code>进行<code>CRUD</code>，并且支持标准<code>SQL</code>的数据库，具体支持情况如下，如果不在下列表查看分页部分教程<code>PR</code>您的支持。</p>
<ul>
<li><code>MySQL</code>，<code>Oracle</code>，<code>DB2</code>，<code>H2</code>，<code>HSQL</code>，<code>SQLite</code>，<code>PostgreSQL</code>，<code>SQLServer</code>，<code>Phoenix</code>，<code>Gauss</code>，<code>ClickHouse</code>，<code>Sybase</code>，<code>OceanBase</code>，<code>Firebird</code>，<code>Cubrid</code>，<code>Goldilocks</code>，<code>csiidb</code></li>
<li>达梦数据库，虚谷数据库，人大金仓数据库，南大通用(华库)数据库，南大通用数据库，神通数据库，瀚高数据库</li>
</ul>
<h2 id="14-核心架构">1.4 核心架构</h2>
<p><img src="https://img2024.cnblogs.com/blog/2745643/202507/2745643-20250711134615715-1266517818.png" alt="d6ddb1d8b8da2fe82a8ab777a922f384" loading="lazy"></p>
<h1 id="二入门案例">二、入门案例</h1>
<p>创建测试表</p>
<pre><code class="language-mysql">DROP TABLE IF EXISTS user;

CREATE TABLE `user`
(
`id`    bigint(20) NOT NULL COMMENT '主键ID',
`name` varchar(30) DEFAULT NULL COMMENT '姓名',
`sex`    char(1) DEFAULT NULL COMMENT '性别 0:男 1:女',
`age`    int(11) DEFAULT NULL COMMENT '年龄',
`birthday` date    DEFAULT NULL COMMENT '生日',
PRIMARY KEY (`id`)
);

INSERT INTO `user` VALUES (1, 'Jone', '1', 27, '2001-10-04');
INSERT INTO `user` VALUES (2, 'Jack', '0', 20, '1999-10-04');
INSERT INTO `user` VALUES (3, 'Tom', '1', 28, '1996-08-12');
INSERT INTO `user` VALUES (4, 'Sandy', '1', 21, '2001-10-04');
INSERT INTO `user` VALUES (5, 'Billie', '0', 24, '1992-09-07');
INSERT INTO `user` VALUES (6, 'Jackson', '0', 18, '1996-08-12');
INSERT INTO `user` VALUES (7, 'Hardy', '1', 25, '1992-09-07');
INSERT INTO `user` VALUES (8, 'Rose', '1', 21, '1992-09-07');
INSERT INTO `user` VALUES (9, 'June', '0', 28, '1992-09-07');
INSERT INTO `user` VALUES (10, 'Aidan', '0', 17, '2001-10-04');
</code></pre>
<p>引入依赖</p>
<pre><code class="language-xml"><?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>

<groupId>com.test</groupId>
<artifactId>01_MyBatisPlus</artifactId>
<version>1.0-SNAPSHOT</version>
<parent>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-parent</artifactId>
   <version>2.6.3</version>
   <relativePath/>
</parent>

<dependencies>
   
   <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter</artifactId>
   </dependency>
   
   <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-test</artifactId>
         <scope>test</scope>
   </dependency>
   
   <dependency>
         <groupId>com.baomidou</groupId>
         <artifactId>mybatis-plus-boot-starter</artifactId>
         <version>3.4.2</version>
   </dependency>

   
   <dependency>
         <groupId>junit</groupId>
         <artifactId>junit</artifactId>
         <version>4.13</version>
         <scope>test</scope>
   </dependency>

   
   <dependency>
         <groupId>mysql</groupId>
         <artifactId>mysql-connector-java</artifactId>
   </dependency>

   
   <dependency>
         <groupId>org.projectlombok</groupId>
         <artifactId>lombok</artifactId>
   </dependency>
</dependencies>
</project>
</code></pre>
<p>实体类</p>
<pre><code class="language-java">package com.test.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private Long id;
private String name;
private String sex;
private Integer age;
private String birthday;
}
</code></pre>
<p>Mapper接口</p>
<pre><code class="language-java">package com.test.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.test.entity.User;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface UserMapper extends BaseMapper<User> {
}
</code></pre>
<p>application.yml</p>
<pre><code class="language-yaml">spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/mybatisplus?serverTimezone=GMT%2b8
username: root
password: admin
#配置日志
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
</code></pre>
<p>启动类</p>
<pre><code class="language-java">package com.test;

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

@SpringBootApplication
@MapperScan("com.test.mapper")
public class MyBatisPlusApplication {
public static void main(String[] args) {
   SpringApplication.run(MyBatisPlusApplication.class, args);
}
}
</code></pre>
<p>测试类</p>
<pre><code class="language-java">package com.test;

import com.test.entity.User;
import com.test.mapper.UserMapper;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;
import javax.annotation.Resource;

@SpringBootTest(classes = MyBatisPlusApplication.class)
@RunWith(SpringRunner.class)
public class Demo01 {

@Resource
private UserMapper userMapper;

@Test
public void testSelect() {
   // 传递null代表查询全部
   List<User> userList = userMapper.selectList(null);
   for (User user : userList) {
         System.out.println(user);
   }
}
}
</code></pre>
<h1 id="三basemapper接口">三、BaseMapper接口</h1>
<p>在<code>MyBatisPlus</code>中，我们编写的<code>Mapper</code>接口都继承与<code>MyBatisPlus</code>提供的<code>BaseMapper</code>接口，<code>BaseMapper</code>接口包含了<code>MyBatisPlus</code>帮我们提供操作数据库的一系列方法；</p>
<p>官网案例：https://baomidou.com/pages/49cc81/#mapper-crud-接口</p>
<p>使用示例：</p>
<pre><code class="language-java">package com.test;

import com.test.entity.User;
import com.test.mapper.UserMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import javax.annotation.Resource;

@SpringBootTest(classes = MyBatisPlusApplication.class)
@RunWith(SpringRunner.class)
public class Demo01_BaseMapper {

@Resource
private UserMapper userMapper;

/**
* 新增
* INSERT INTO user ( id, name, age, email ) VALUES ( ?, ?, ?, ? )
*/
@Test
public void insert() {
   User user = new User(100L, "Ken", "0", 20);
   userMapper.insert(user);
}

/**
* 修改
* UPDATE user SET name=?, age=?, email=? WHERE id=?
*/
@Test
public void update() {
   User user = new User(100L, "Kevin", "0", 25);
   userMapper.updateById(user);
}

/**
* 根据id查询
* SELECT id,name,age,email FROM user WHERE id=?
*/
@Test
public void selectById() {
   User user = userMapper.selectById(100L);
   System.out.println(user);
}

/**
* 根据一批id查询
* SELECT id,name,age,email FROM user WHERE id IN ( ?, ?, ? )
*/
@Test
public void selectBatchIds() {
   List<User> userList = userMapper.selectBatchIds(Arrays.asList(1, 2, 3));
   for (User user : userList) {
         System.out.println(user);
   }
}

/**
* 根据条件查询数据
* SELECT id,name,age,email FROM user WHERE name = ? AND id = ?
*/
@Test
public void selectByMap() {
   // 封装条件
   HashMap<String, Object> param = new HashMap<>();
   param.put("id", 10L);
   param.put("name", "Kevin");
   // 根据条件查询,map中的多个条件是并列关系 id=10 and name='小灰灰'
   List<User> userList = userMapper.selectByMap(param);
   for (User user : userList) {
         System.out.println(user);
   }
}

/**
* 根据id删除
* DELETE FROM user WHERE id=?
*/
@Test
public void deleteById() {
   userMapper.deleteById(100L);
}

/**
* 根据id删除一批
* DELETE FROM user WHERE id IN ( ?, ?, ? )
*/
@Test
public void deleteBatchIds() {
   userMapper.deleteBatchIds(Arrays.asList(1, 2, 3));
}

/**
* 根据条件删除数据
* DELETE FROM user WHERE name = ? AND id = ?
*/
@Test
public void deleteByMap() {
   // 封装条件
   HashMap<String, Object> param = new HashMap<>();
   param.put("id", 100L);
   param.put("name", "Kevin");
   userMapper.deleteByMap(param);
}
}
</code></pre>
<h1 id="四wrapper接口">四、Wrapper接口</h1>
<p>通过<code>BaseMapper</code>提供的一些方法我们可以完成一些基本的<code>CRUD</code>，但无法完成复杂条件的查询；对于复杂条件的查询，<code>MyBatisPlus</code>提供了<code>Wrapper</code>接口来处理；</p>
<p><code>Wrapper</code>是<code>MyBatisPlus</code>提供的一个条件构造器，主要用于构建一系列条件，当<code>Wrapper</code>构建完成后，可以使用<code>Wrapper</code>中的条件进行查询、修改、删除等操作；</p>
<p><code>Wrapper</code>的继承体系如下：</p>
<p><img src="https://img2024.cnblogs.com/blog/2745643/202507/2745643-20250711134702651-1589482953.png" alt="417cc9fc586f5eba5dba85a2a56217d5" loading="lazy"></p>
<p><code>Wrapper</code>是条件构造抽象类，最顶端父类，其主要实现类有如下：</p>
<ul>
<li>AbstractWrapper：用于查询条件封装，生成sql的where条件
<ul>
<li>QueryWrapper：Query条件封装</li>
<li>UpdateWrapper：Update条件封装</li>
<li>AbstractLambdaWrapper：使用Lambda语法
<ul>
<li>LambdaQueryWrapper：基于Lambda语法使用的查询Wrapper</li>
<li>LambdaUpdateWrapper：基于Lambda语法使用的更新Wrapper</li>
</ul>
</li>
</ul>
</li>
</ul>
<blockquote>
<p>官方文档：https://baomidou.com/pages/10c804/</p>
</blockquote>
<h2 id="41-基本方法">4.1 基本方法</h2>
<p><code>AbstractWrapper</code>是其他常用<code>Wrapper</code>的父类，用于生成<code>sql</code>的<code>where</code>条件</p>
<h3 id="411-方法介绍">4.1.1 方法介绍</h3>
<p><code>AbstractWrapper</code>提供了很多公有的方法，其子类全部具备这些方法，方法列表如下：</p>
<table>
<thead>
<tr>
<th>方法名</th>
<th>解释</th>
<th>示例</th>
</tr>
</thead>
<tbody>
<tr>
<td>eq</td>
<td>等于 =</td>
<td>eq(“name”, “老王”)<strong>---></strong> <br> name = ‘老王’</td>
</tr>
<tr>
<td>ne</td>
<td>不等于 <></td>
<td>ne(“name”, “老王”)<strong>---></strong> <br> name <> ‘老王’</td>
</tr>
<tr>
<td>gt</td>
<td>大于 ></td>
<td>gt(“age”, 18)<strong>---></strong> <br> age > 18</td>
</tr>
<tr>
<td>ge</td>
<td>大于等于 >=</td>
<td>ge(“age”, 18)<strong>---></strong> <br> age >= 18</td>
</tr>
<tr>
<td>lt</td>
<td>小于 <</td>
<td>lt(“age”, 18)<strong>---></strong> <br> age < 18</td>
</tr>
<tr>
<td>le</td>
<td>小于等于 <=</td>
<td>le(“age”, 18)<strong>---></strong> <br> age <= 18</td>
</tr>
<tr>
<td>between</td>
<td>between 值1 and 值2</td>
<td>between(“age”, 18, 30)<strong>---></strong> <br> age between 18 and 30</td>
</tr>
<tr>
<td>notBetween</td>
<td>not between 值1 and 值2</td>
<td>notBetween(“age”, 18, 30)<strong>---></strong> <br> age not between 18 and 30</td>
</tr>
<tr>
<td>like</td>
<td>LIKE ‘%值%’</td>
<td>like(“name”, “王”)<strong>---></strong>    <br> name like ‘%王%’</td>
</tr>
<tr>
<td>notLike</td>
<td>NOT LIKE ‘%值%’</td>
<td>notLike(“name”, “王”)<strong>---></strong> <br>name not like ‘%王%’</td>
</tr>
<tr>
<td>likeLeft</td>
<td>LIKE ‘%值’</td>
<td>likeLeft(“name”, “王”)<strong>---></strong><br>name like ‘%王’</td>
</tr>
<tr>
<td>likeRight</td>
<td>LIKE ‘值%’</td>
<td>likeRight(“name”, “王”)<strong>---></strong>       <br> name like ‘王%’</td>
</tr>
<tr>
<td>isNull</td>
<td>字段 IS NULL</td>
<td>isNull(“name”)<strong>---></strong>                <br> name is null</td>
</tr>
<tr>
<td>isNotNull</td>
<td>字段 IS NOT NULL</td>
<td>isNotNull(“name”)<strong>---></strong>          <br> name is not null</td>
</tr>
<tr>
<td>in</td>
<td>字段 IN (v0, v1, …)</td>
<td>in(“age”, 1, 2, 3)<strong>---></strong>             <br>    age in (1,2,3)</td>
</tr>
<tr>
<td>notIn</td>
<td>字段 NOT IN (v0, v1, …)</td>
<td>notIn(“age”, 1, 2, 3)<strong>---></strong>             <br> age not in (1,2,3)</td>
</tr>
<tr>
<td>inSql</td>
<td>字段 IN ( sql语句 )</td>
<td>inSql(“id”, “select id from table where id < 3”)<strong>---></strong><br> id in (select id from table where id < 3)</td>
</tr>
<tr>
<td>notInSql</td>
<td>字段 NOT IN ( sql语句 )</td>
<td>notInSql(“id”, “select id from table where id < 3”)<strong>---></strong><br> id not in (select id from table where id < 3)</td>
</tr>
<tr>
<td>groupBy</td>
<td>分组：GROUP BY 字段, …</td>
<td>groupBy(“id”, “name”)<strong>---></strong> <br> group by id,name</td>
</tr>
<tr>
<td>orderByAsc</td>
<td>排序：ORDER BY 字段, … ASC</td>
<td>orderByAsc(“id”, “name”)<strong>---></strong> <br> order by id ASC,name ASC</td>
</tr>
<tr>
<td>orderByDesc</td>
<td>排序：ORDER BY 字段, … DESC</td>
<td>orderByDesc(“id”, “name”)<strong>---></strong> <br> order by id DESC,name DESC</td>
</tr>
<tr>
<td>orderBy</td>
<td>排序：ORDER BY 字段, …</td>
<td>orderBy(true, true, “id”, “name”)<strong>---></strong><br> order by id ASC,name ASC</td>
</tr>
<tr>
<td>having</td>
<td>HAVING ( sql语句 )</td>
<td>例1：having(“sum(age) > 10”)<strong>---></strong><br>having sum(age) > 10 <br> 例2：having(“sum(age) > {0}”, 11)<strong>---></strong> <br> having sum(age) > 11</td>
</tr>
<tr>
<td>func</td>
<td>主要解决条件拼接</td>
<td>func(i -> if(true) {i.eq(“id”, 1)} else {i.ne(“id”, 1)})</td>
</tr>
<tr>
<td>or</td>
<td>拼接 OR</td>
<td>eq(“id”,1).or().eq(“name”,“老王”)<strong>---></strong> <br> id = 1 or name = ‘老王’</td>
</tr>
<tr>
<td>and</td>
<td>AND 嵌套</td>
<td>and(i -> i.eq(“name”, “李白”).ne(“status”, “活着”))<strong>---></strong> <br> and (name = ‘李白’ and status <> ‘活着’)</td>
</tr>
<tr>
<td>nested</td>
<td>用于多条件拼接时</td>
<td>nested(i -> i.eq(“name”, “李白”).ne(“status”, “活着”))<strong>---></strong> <br> (name = ‘李白’ and status <> ‘活着’)</td>
</tr>
<tr>
<td>apply</td>
<td>用于拼接SQL语句</td>
<td>例1：apply(“id = 1”)<strong>---></strong> <br> id = 1 <br> 例2：apply(“id = {0}”,1)<strong>---></strong><br> id = 1 <br>例3：apply(“name like {0} and age > {1}”,“%J%”,18) <strong>---></strong> <br> name like ‘%J%’ and age > 18</td>
</tr>
<tr>
<td>last</td>
<td>无视优化规则直接拼接到 sql 的最后</td>
<td>last(“limit 1”) <strong>---></strong> <br> 在SQL语句最后面拼接：limit 1</td>
</tr>
<tr>
<td>exists</td>
<td>拼接 EXISTS ( sql语句 )</td>
<td>exists(“select id from table where age = 1”)<strong>---></strong> <br> exists (select id from table where age = 1)</td>
</tr>
<tr>
<td>notExists</td>
<td>拼接 NOT EXISTS ( sql语句 )</td>
<td>notExists(“select id from table where age = 1”)<strong>---></strong> <br> not exists (select id from table where age = 1)</td>
</tr>
</tbody>
</table>
<p>创建Wrapper对象：</p>
<ul>
<li>Wrappers静态方法：
<ul>
<li>public static <t> QueryWrapper<t> query()</t></t></li>
</ul>
</li>
<li>通过QueryWrapper对象的构造方法：
<ul>
<li>public QueryWrapper()</li>
</ul>
</li>
</ul>
<p>代码示例：</p>
<pre><code class="language-java">@SpringBootTest(classes = MyBatisPlusApplication.class)
@RunWith(SpringRunner.class)
public class Demo02_Wrapper {

@Resource
private UserMapper userMapper;

/**
* QueryWrapper的创建
* SELECT id,name,age,email FROM user
*/
@Test
public void test1() {
   // 创建QueryWrapper，默认情况下查询所有数据
   QueryWrapper<User> wrapper = Wrappers.query();
   QueryWrapper<User> wrapper2 = new QueryWrapper<>();

   List<User> users = userMapper.selectList(wrapper);
   users.forEach(System.out::println);
}
}
</code></pre>
<h3 id="412-基本方法的使用">4.1.2 基本方法的使用</h3>
<p>官网案例：https://baomidou.com/pages/10c804/#abstractwrapper</p>
<p>使用示例：</p>
<pre><code class="language-java">@Test
public void test2() {
QueryWrapper<User> wrapper = Wrappers.query();
// name ='Jack'
// wrapper.eq("name","Jack");

//参数1: 是否要进行name条件的拼接
String name = "Jack";
wrapper.eq(StringUtils.isNotBlank(name), "name", name);

// name != 'Jack'
// wrapper.ne("name","Jack");

// age > 20
// wrapper.gt("age",20);

// age < 20
// wrapper.lt("age",20);

// age=20
// wrapper.lt("age",20);

// age between 20 and 24
// wrapper.between("age",20,24);

// age not between 20 and 24
// wrapper.notBetween("age",20,24);

// name like "%J%" 自动拼接左右的%
// wrapper.like("name","J");

// name not like "%J%"
// wrapper.notLike("name","J");

// name like "%J"
// wrapper.likeLeft("name","J");

// name like 'J%'
// wrapper.likeRight("name","J");

// name is null
// wrapper.isNull("name");

// name is not null
// wrapper.isNotNull("name");

// name in ('Jack','Tom','Jone')
// wrapper.in("name","Jack","Tom","Jone");

// name not in ('Jack','Tom','Jone')
// wrapper.notIn("name","Jack","Tom","Jone");

List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
</code></pre>
<h3 id="413-子查询">4.1.3 子查询</h3>
<p>示例代码：</p>
<pre><code class="language-java">@Test
public void test3() {
// 创建wrapper对象
QueryWrapper<User> wrapper = Wrappers.query();
// 相当于: name in (select name from user where age > 21)
wrapper.inSql("name", "select name from user where age > 21");
// 相当于: name not in (select name from user where age > 21)
// wrapper.notInSql("name","select name from user where age > 21");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
</code></pre>
<h3 id="414-分组与排序">4.1.4 分组与排序</h3>
<p>1）分组：</p>
<p>通过<code>Wrapper.query()</code>构建的查询字段默认是表中的所有字段，因此在这种情况下分组是没有意义的，分组具体的用法我们后面再详细介绍；</p>
<pre><code class="language-java">@Test
public void test4() {
// 创建wrapper对象
QueryWrapper<User> wrapper = Wrappers.query();
// 相当于 select * from user where group sex
// 这是一个没有意义的分组,分组必须结合查询的字段来体现,后续学QueryWrapper的select方法再介绍
wrapper.groupBy("sex");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
</code></pre>
<p>2）having操作</p>
<pre><code class="language-java">@Test
public void test5() {
// 创建wrapper对象
QueryWrapper<User> wrapper = Wrappers.query();
// group by sex having sex = 0
wrapper.groupBy("sex");
wrapper.having("sex", "0");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
</code></pre>
<p>3）排序：</p>
<pre><code class="language-java">@Test
public void test6() {
//创建wrapper对象
QueryWrapper<User> wrapper = Wrappers.query();
/**
* 参数1: 是否是Asc排序(升序), true : asc排序, false: desc排序
* 参数2: 排序的字段
*/
// wrapper.orderByAsc("age"); // order by age asc
// wrapper.orderByDesc("age"); // order by age desc
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
</code></pre>
<h3 id="415-多条件的拼接">4.1.5 多条件的拼接</h3>
<p><code>Wrapper</code>对象在调用每一个方法时都会返回当前对象（<code>Wrapper</code>），这样可以很好的方便我们链式编程；另外<code>MyBatisPlus</code>在拼接多个条件时默认使用<code>and</code>拼接，如果需要使用<code>or</code>，那么需要显示的调用<code>or()</code>方法；</p>
<p>1）and拼接条件：</p>
<pre><code class="language-java">@Test
public void test7() {
//创建wrapper对象
QueryWrapper<User> wrapper = Wrappers.query();
// 默认情况下,多条件是以and拼接
// SQL语句: name LIKE "%a%" AND age > 20 AND sex = 0
wrapper.like("name", "%a%")
         .lt("age", 20)
         .eq("sex", 0);
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
</code></pre>
<p>2）or拼接条件：</p>
<pre><code class="language-java">@Test
public void test8() {
//创建wrapper对象
QueryWrapper<User> wrapper = Wrappers.query();
/*
默认情况下,多条件是以and拼接
SQL语句: name LIKE "%a%" OR age > 20 AND sex = 0
*/
wrapper.like("name", "%a%")
         .or()
         .lt("age", 20)
         .eq("sex", 0); // 该条件仍然以AND拼接
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
</code></pre>
<h3 id="416-其他方法">4.1.6 其他方法</h3>
<p>1）and方法：用于拼接一个其他的整体条件；示例如下：</p>
<pre><code class="language-java">@Test
public void test1() {
QueryWrapper<User> wrapper = Wrappers.query();

// 生成的SQL为: (age < ? AND (sex = ? OR name LIKE ?))

// wrapper.lt("age", 20);
// wrapper.and(new Consumer<QueryWrapper<User>>() {
//    @Override
//    public void accept(QueryWrapper<User> userQueryWrapper) {
//          userQueryWrapper.eq("sex", 0)
//                .or()
//                .like("name", "J");
//    }
// });

// 生成的SQL为:(age < ? AND sex = ? OR name LIKE ?)
wrapper.lt("age", 20)
         .eq("sex", 0)
         .or()
         .like("name", "J");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
</code></pre>
<p>2）func方法：用于多条件的拼接，直接使用之前的方法也可以做到这个功能；示例如下：</p>
<pre><code class="language-java">@Test
public void test2() {
QueryWrapper<User> wrapper = Wrappers.query();

// 生成的SQL语句条件: (age < ? AND name LIKE ? AND sex = ?)
wrapper.lt("age", 20);
wrapper.func(new Consumer<QueryWrapper<User>>() {
   @Override
   public void accept(QueryWrapper<User> userQueryWrapper) {
         userQueryWrapper.like("name", "a");
         userQueryWrapper.eq("sex", "0");
   }
});
// 等价于:
// wrapper.lt("age", 20)
//          .like("name", "a")
//          .eq("sex", "0");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
</code></pre>
<p>3）nested方法：功能等价于and方法</p>
<pre><code class="language-java">@Test
public void test3() {
QueryWrapper<User> wrapper = Wrappers.query();
// 生成的SQL语句条件为: (id = ? AND (name LIKE ? OR age > ?))
// nested()等价于and方法()
wrapper.eq("id", 1);
wrapper.nested(new Consumer<QueryWrapper<User>>() {
   @Override
   public void accept(QueryWrapper<User> userQueryWrapper) {
         // 默认情况下是用and来拼接多个条件
         userQueryWrapper.like("name", "a")
               .or().gt("age", 20);
   }
});
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
</code></pre>
<p>4）apply方法：可以使用占位符进行参数传参；示例如下：</p>
<pre><code class="language-java">@Test
public void test4() {
QueryWrapper<User> wrapper = Wrappers.query();
// SQL: (name like ? and age > ?)
// wrapper.apply("name like {0} and age > {1}", "%J%", 18);

// SQL: (date_format(birthday, '%Y-%m-%d') = ?)
wrapper.apply("date_format(birthday, '%Y-%m-%d') = {0}", "2001-10-04");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
</code></pre>
<p>5）last方法：无视优化规则直接拼接到 sql 的最后,只能调用一次,多次调用以最后一次为准有sql注入的风险</p>
<blockquote>
<p>Tips：apply方法可以防止SQL注入，但last方法不能；</p>
</blockquote>
<pre><code class="language-java">@Test
public void test5() {
QueryWrapper<User> wrapper = Wrappers.query();

// 无视优化规则直接拼接到 sql 的最后,只能调用一次,多次调用以最后一次为准有sql注入的风险

// SQL: name = 'Jone'
// String name = "Jone";
// wrapper.last("where name = '" + name + "'");

// SQL: SELECT id,name,sex,age FROM user where name ='' or 1=1; -- '
String name = "' or 1=1; -- ";
wrapper.last("where name ='" + name + "'");// 出现SQL注入问题

List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
</code></pre>
<p>6）exists方法：用于exists语句</p>
<pre><code class="language-java">@Test
public void test6() {
QueryWrapper<User> wrapper = Wrappers.query();
// SQL: (EXISTS (select 1))
wrapper.exists("select 1");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
</code></pre>
<p>7）notExists方法：</p>
<pre><code class="language-java">@Test
public void test7() {
QueryWrapper<User> wrapper = Wrappers.query();
// SQL: (NOT EXISTS (select 1))
wrapper.notExists("select 1");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
</code></pre>
<h2 id="42-querywrapper">4.2 QueryWrapper</h2>
<p><code>QueryWrapper</code>是<code>AbstractWrapper</code>的子类，主要用于查询指定字段，方法列表如下：</p>
<table>
<thead>
<tr>
<th>方法名</th>
<th>解释</th>
<th>示例</th>
</tr>
</thead>
<tbody>
<tr>
<td>select(String… sqlSelect)</td>
<td>设置查询字段</td>
<td>例1：select(“id”, “name”, “age”) <br> 例2：select(i -> i.getProperty().startsWith(“test”))</td>
</tr>
</tbody>
</table>
<p>示例代码：</p>
<pre><code class="language-java">package com.test;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.test.entity.User;
import com.test.mapper.UserMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.annotation.Resource;
import java.util.List;

@SpringBootTest(classes = MyBatisPlusApplication.class)
@RunWith(SpringRunner.class)
public class Demo04_QueryWrapper {

@Resource
private UserMapper userMapper;

// 选择查询的字段
@Test
public void test1() throws Exception {
   QueryWrapper<User> wrapper = Wrappers.query();
   // 确定要查询的字段
   wrapper.select("id", "name", "sex");
   // in
   wrapper.in("id", "1", "2");
   // SQL: SELECT id,name,sex FROM user WHERE (id IN (?,?))
   List<User> userList = userMapper.selectList(wrapper);
   userList.forEach(System.out::println);
}

@Test
public void test2() throws Exception {
   User user = new User();
   user.setId(1L);
   // user当做查询的条件
   QueryWrapper<User> wrapper = Wrappers.query(user);
   // 指定查询的列
   wrapper.select("id", "name", "sex");

   // SQL: SELECT id,name,sex FROM user WHERE id=?
   List<User> userList = userMapper.selectList(wrapper);
   userList.forEach(System.out::println);
}
}
</code></pre>
<h2 id="43-updatewrapper">4.3 UpdateWrapper</h2>
<p><code>UpdateWrapper</code>也是<code>AbstractWrapper</code>的子类，因此<code>UpdateWrapper</code>也具备之前的那些查询方法，不同的是，<code>UpdateMapper</code>在那些方法基础之上还提供了很多有关于更新操作的方法；</p>
<p>方法如下：</p>
<table>
<thead>
<tr>
<th>方法名</th>
<th>解释</th>
<th>示例</th>
</tr>
</thead>
<tbody>
<tr>
<td>set(String column, Object val)</td>
<td>设置查询字段</td>
<td>例1：set("name", "老李头") <br> 例2：set("name", "") <br> —>数据库字段值变为空字符串<br>例3：set("name", null) <br> —>数据库字段值变为null</td>
</tr>
<tr>
<td>setSql(String sql)</td>
<td>设置set子句的部分SQL</td>
<td>例1：setSql("name = '老李头'")<br> 例2：setSql("name = '老李头',age=20 where id=1")</td>
</tr>
</tbody>
</table>
<p>示例代码：</p>
<pre><code class="language-java">package com.test;

import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.test.entity.User;
import com.test.mapper.UserMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.annotation.Resource;
import java.util.List;

@SpringBootTest(classes = MyBatisPlusApplication.class)
@RunWith(SpringRunner.class)
public class Demo05_UpdateWrapper {

@Resource
private UserMapper userMapper;

@Test
public void test1() throws Exception {
   UpdateWrapper<User> wrapper = Wrappers.update();
   // UpdateWrapper也是AbstractWrapper的子类,因此也具备一些基本的查询方法
   wrapper.like("name", "J");
   List<User> userList = userMapper.selectList(wrapper);
   for (User user : userList) {
         System.out.println(user);
   }
}

/**
* 第一种方法: 使用wrapper来修改,并且指定查询条件
*
* @throws Exception
*/
@Test
public void test2() throws Exception {
   UpdateWrapper<User> wrapper = Wrappers.update();
   wrapper.set("name", "Jackson");
   wrapper.set("age", "16");
   wrapper.set("sex", "1");
   wrapper.eq("id", 2L);

   // SQL: UPDATE user SET name=?, sex=?, age=? WHERE (id = ?)
   userMapper.update(null, wrapper);
}

/**
* 第二种方法: 使用wrapper来封装条件,使用entity来封装修改的数据
*
* @throws Exception
*/
@Test
public void test3() throws Exception {
   UpdateWrapper<User> wrapper = Wrappers.update();
   wrapper.eq("id", 2L);
   User user = new User(null, "Jack", "0", 28);
   // SQL: UPDATE user SET name=?, sex=?, age=? WHERE (id = ?)
   userMapper.update(user, wrapper);
}

/**
* 第三种方法: Wrappers.update(user)传递查询的条件,使用wrapper来修改
*
* @throws Exception
*/
@Test
public void test4() throws Exception {
   User user = new User();
   user.setId(1L);

   // user当做查询条件
   UpdateWrapper<User> wrapper = Wrappers.update(user);
   wrapper.set("name", "xiaohui");
   wrapper.set("sex", "0");
   wrapper.set("age", "22");

   // SQL : UPDATE user SET name=?,sex=?,age=? WHERE id=?
   userMapper.update(null, wrapper);
}

/**
* setSql方法
*
* @throws Exception
*/
@Test
public void test5() throws Exception {
   UpdateWrapper<User> wrapper = Wrappers.update();
   wrapper.setSql("name='abc',sex='0',age=18 where id=1");

   // SQL: UPDATE user SET name='abc',sex='0',age=18 where id=1
   userMapper.update(null, wrapper);
}
}
</code></pre>
<h2 id="44-lambdaquerywrapper">4.4 LambdaQueryWrapper</h2>
<p><code>LambdaQueryWrapper</code>是<code>QueryWrapper</code>的子类，具备<code>QueryWrapper</code>的所有方法，<code>QueryWrapper</code>的方法上提供了一系列有关于方法引的操作；</p>
<p>使用示例：</p>
<pre><code class="language-java">package com.test;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.test.entity.User;
import com.test.mapper.UserMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;
import javax.annotation.Resource;

@SpringBootTest(classes = MyBatisPlusApplication.class)
@RunWith(SpringRunner.class)
public class Demo06_LambdaQueryWrapper {

@Resource
private UserMapper userMapper;

/**
* 使用QueryWrapper
* @throws Exception
*/
@Test
public void test1() throws Exception {
   QueryWrapper<User> wrapper = Wrappers.query();
   wrapper.eq("id", "1");
   List<User> userList = userMapper.selectList(wrapper);
   for (User user : userList) {
         System.out.println(user);
   }
}

/**
* 使用LambdaQueryWrapper
* @throws Exception
*/
@Test
public void test2() throws Exception {
   LambdaQueryWrapper<User> wrapper = Wrappers.lambdaQuery();
   // id=1
   // wrapper.eq(User::getId,1);

   // select id,name,age from user where id in (1,2,3) and name like "%a%"
   wrapper.in(User::getId, "1", "2", "3")
            .like(User::getName, "a")
            .select(User::getId, User::getName, User::getAge);
   List<User> userList = userMapper.selectList(wrapper);
   for (User user : userList) {
         System.out.println(user);
   }
}
}
</code></pre>
<h2 id="45-lambdaupdatemapper">4.5 LambdaUpdateMapper</h2>
<p><code>LambdaUpdateMapper</code>同样是<code>UpdateMapper</code>的子类，具备<code>UpdateMapper</code>的所有方法，<code>UpdateMapper</code>的方法上提供了一系列有关于方法引的操作；</p>
<p>示例代码：</p>
<pre><code class="language-java">package com.test;

import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.test.entity.User;
import com.test.mapper.UserMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.annotation.Resource;

@SpringBootTest(classes = MyBatisPlusApplication.class)
@RunWith(SpringRunner.class)
public class Demo07_LambdaUpdateWrapper {

@Resource
private UserMapper userMapper;

/**
* 使用UpdateWrapper
*
* @throws Exception
*/
@Test
public void test1() throws Exception {
   UpdateWrapper<User> wrapper = Wrappers.update();
   wrapper.eq("id", "1");
   wrapper.set("name", "xiaohui");
   wrapper.set("age", 20);
   userMapper.update(null, wrapper);
}

/**
* 使用LambdaUpdateWrapper
*
* @throws Exception
*/
@Test
public void test2() throws Exception {
   LambdaUpdateWrapper<User> wrapper = Wrappers.lambdaUpdate();
   wrapper.eq(User::getId, "1");
   wrapper.set(User::getName, "xiaolan");
   wrapper.set(User::getAge, 18);
   userMapper.update(null, wrapper);
}
}
</code></pre>
<h1 id="五mapper分页查询">五、Mapper分页查询</h1>
<h2 id="51-配置">5.1 配置</h2>
<p>在<code>MyBatis</code>中提供有<code>Page</code>对象来帮助我们实现分页查询，在<code>Page</code>对象中有如下成员：</p>
<table>
<thead>
<tr>
<th>成员变量</th>
<th>说明</th>
</tr>
</thead>
<tbody>
<tr>
<td>List getRecords()</td>
<td>当前页数据</td>
</tr>
<tr>
<td>public long getTotal()</td>
<td>总记录数</td>
</tr>
<tr>
<td>public long getSize()</td>
<td>页大小</td>
</tr>
<tr>
<td>public long getCurrent()</td>
<td>当前页</td>
</tr>
<tr>
<td>default long getPages()</td>
<td>总页数</td>
</tr>
<tr>
<td>public boolean hasNext()</td>
<td>是否有上一页</td>
</tr>
<tr>
<td>public boolean hasPrevious()</td>
<td>是否有上一页</td>
</tr>
</tbody>
</table>
<p><code>MyBatisPlus</code>的分页逻辑底层是通过分页插件来完成的，因此我们首先要配置<code>MyBatisPlus</code>的分页插件；</p>
<p>配置分页插件：</p>
<pre><code class="language-java">package com.test.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
@MapperScan("com.test.mapper")       // mapper接口的所在位置
public class MyBatisPlusConfig {
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
   MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
   interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
   return interceptor;
}
}
</code></pre>
<h2 id="52-分页查询">5.2 分页查询</h2>
<p>在<code>BaseMapper</code>中主要提供有如下方法来完成分页查询：</p>
<ul>
<li><E extends IPage<t>> E selectPage(E page, @Param(Constants.WRAPPER) Wrapper<t> queryWrapper)：
<ul>
<li>参数1：分页配置类</li>
<li>参数2：分页查询条件</li>
<li>解释：根据分页配置和分页查询条件来完成分页查询，当前页数据为指定类型</li>
</ul>
</t></t></li>
<li><E extends IPage<Map<String, Object>>> E selectMapsPage(E page, @Param(Constants.WRAPPER) Wrapper<t> queryWrapper)
<ul>
<li>参数1：分页配置类</li>
<li>参数2：分页查询条件</li>
<li>解释：根据分页配置和分页查询条件来完成分页查询，当前页数据为Map类型</li>
</ul>
</t></li>
</ul>
<p>1）无条件分页查询：</p>
<pre><code class="language-java">package com.test;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.test.entity.User;
import com.test.mapper.UserMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;
import javax.annotation.Resource;

@SpringBootTest(classes = MyBatisPlusApplication.class)
@RunWith(SpringRunner.class)
public class Demo06_BaseMapper {

@Resource
private UserMapper userMapper;

/**
* 无条件分页查询
* @throws Exception
*/
@Test
public void test1() throws Exception {
   // 封装分页信息
   Page<User> page = new Page<>(1, 3);
   /*
   执行分页查询,并将结果封装到page中
   参数1: 分页配置
   参数2: 查询条件
   */
   userMapper.selectPage(page, null);

   // 当前页数据
   List<User> pageData = page.getRecords();
   for (User user : pageData) {
         System.out.println(user);
   }
   System.out.println("------------");
   System.out.println("当前页：" + page.getCurrent());
   System.out.println("每页显示的条数：" + page.getSize());
   System.out.println("总记录数：" + page.getTotal());
   System.out.println("总页数：" + page.getPages());
   System.out.println("是否有上一页：" + page.hasPrevious());
   System.out.println("是否有下一页：" + page.hasNext());
}
}
</code></pre>
<p>查询结果：</p>
<pre><code class="language-text"># 首先查询总记录数
==>Preparing: SELECT COUNT(*) FROM user
==> Parameters:
<== Columns: COUNT(*)
<==    Row: 10
<==    Total: 1

# 再查询分页
==>Preparing: SELECT id,name,sex,age FROM user LIMIT ?
==> Parameters: 3(Long)
<== Columns: id, name, sex, age
<==    Row: 1, Jone, 1, 27
<==    Row: 2, Jack, 0, 20
<==    Row: 3, Tom, 1, 28
<==    Total: 3
Closing non transactional SqlSession
User(id=1, name=Jone, sex=1, age=27)
User(id=2, name=Jack, sex=0, age=20)
User(id=3, name=Tom, sex=1, age=28)
------------
当前页：1
每页显示的条数：3
总记录数：10
总页数：4
是否有上一页：false
是否有下一页：true
2022-11-15 15:28:16 INFO 8724 --- com.zaxxer.hikari.HikariDataSource: HikariPool-1 - Shutdown initiated...
2022-11-15 15:28:16 INFO 8724 --- com.zaxxer.hikari.HikariDataSource: HikariPool-1 - Shutdown completed.

Process finished with exit code 0
</code></pre>
<p>带条件分页查询：</p>
<pre><code class="language-java">/**
* 带条件分页查询
*
* @throws Exception
*/
@Test
public void test2() throws Exception {
QueryWrapper<User> wrapper = Wrappers.query();
wrapper.like("name", "a");

// 封装分页信息
Page<User> page = new Page<>(1, 3);
/*
执行分页查询,并将结果封装到page中
参数1: 分页配置
参数2: 查询条件
*/
userMapper.selectPage(page, wrapper);

// 当前页数据
List<User> pageData = page.getRecords();
for (User user : pageData) {
   System.out.println(user);
}
System.out.println("------------");
System.out.println("当前页：" + page.getCurrent());
System.out.println("每页显示的条数：" + page.getSize());
System.out.println("总记录数：" + page.getTotal());
System.out.println("总页数：" + page.getPages());
System.out.println("是否有上一页：" + page.hasPrevious());
System.out.println("是否有下一页：" + page.hasNext());
}
</code></pre>
<p>查询结果：</p>
<pre><code class="language-text">==>Preparing: SELECT COUNT(*) FROM user WHERE (name LIKE ?)
==> Parameters: %a%(String)
<== Columns: COUNT(*)
<==    Row: 5
<==    Total: 1
==>Preparing: SELECT id,name,sex,age FROM user WHERE (name LIKE ?) LIMIT ?
==> Parameters: %a%(String), 3(Long)
<== Columns: id, name, sex, age
<==    Row: 2, Jack, 0, 20
<==    Row: 4, Sandy, 1, 21
<==    Row: 6, Jackson, 0, 18
<==    Total: 3
Closing non transactional SqlSession
User(id=2, name=Jack, sex=0, age=20)
User(id=4, name=Sandy, sex=1, age=21)
User(id=6, name=Jackson, sex=0, age=18)
------------
当前页：1
每页显示的条数：3
总记录数：5
总页数：2
是否有上一页：false
是否有下一页：true
</code></pre>
<p>3）将分页数据的查询结果以Map类型返回</p>
<pre><code class="language-java">/**
* 查询结果以Map返回
*
* @throws Exception
*/
@Test
public void test3() throws Exception {
// 封装分页信息
Page page = new Page<>(1, 3);
userMapper.selectMapsPage(page, null);
// 每一条记录都是一个HashMap
List<HashMap<String, Object>> pageData = page.getRecords();
for (HashMap userMap : pageData) {
   System.out.println(userMap);
}
System.out.println("------------");
System.out.println("当前页：" + page.getCurrent());
System.out.println("每页显示的条数：" + page.getSize());
System.out.println("总记录数：" + page.getTotal());
System.out.println("总页数：" + page.getPages());
System.out.println("是否有上一页：" + page.hasPrevious());
System.out.println("是否有下一页：" + page.hasNext());
}
</code></pre>
<p>查询结果：</p>
<pre><code class="language-text">==>Preparing: SELECT COUNT(*) FROM user
==> Parameters:
<== Columns: COUNT(*)
<==    Row: 10
<==    Total: 1
==>Preparing: SELECT id,name,sex,age FROM user LIMIT ?
==> Parameters: 3(Long)
<== Columns: id, name, sex, age
<==    Row: 1, Jone, 1, 27
<==    Row: 2, Jack, 0, 20
<==    Row: 3, Tom, 1, 28
<==    Total: 3
Closing non transactional SqlSession
{sex=1, name=Jone, id=1, age=27}
{sex=0, name=Jack, id=2, age=20}
{sex=1, name=Tom, id=3, age=28}
------------
当前页：1
每页显示的条数：3
总记录数：10
总页数：4
是否有上一页：false
是否有下一页：true
2022-11-15 15:50:37 INFO 20980 --- com.zaxxer.hikari.HikariDataSource: HikariPool-1 - Shutdown initiated...
2022-11-15 15:50:37 INFO 20980 --- com.zaxxer.hikari.HikariDataSource: HikariPool-1 - Shutdown completed.

Process finished with exit code 0
</code></pre>
<h1 id="六通用service查询">六、通用Service查询</h1>
<h2 id="61-简介">6.1 简介</h2>
<p>通用<code>Service CRUD</code>封装<code>IService</code>接口，进一步封装<code>CRUD</code>采用<code>get</code>查询单行、<code>remove</code>删除、<code>list</code>查询集合、<code>page</code>查询分页。</p>
<p>官网案例：https://baomidou.com/pages/49cc81/</p>
<p>使用步骤：</p>
<p>1）定义一个<code>UserService</code>接口继承与<code>MyBatisPlus</code>提供的<code>IService</code>接口：</p>
<pre><code class="language-java">package com.test.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.test.entity.User;

public interface IUserService extends IService<User> {
}
</code></pre>
<p>2）定义一个<code>UserService</code>的实现类，并且继承与<code>MyBatisPlus</code>提供的<code>ServiceImpl</code>：</p>
<pre><code class="language-java">package com.test.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.test.entity.User;
import com.test.mapper.UserMapper;
import com.test.service.IUserService;
import org.springframework.stereotype.Service;

@Service("userService")
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {
}
</code></pre>
<h2 id="62-常用方法">6.2 常用方法</h2>
<p>新增：</p>
<ul>
<li>default boolean save(T entity)：新增记录</li>
<li>boolean saveBatch(Collection<t> entityList)：批量插入</t></li>
<li>saveBatch(Collection<t> entityList, int batchSize)：一次性批量插入batchSize条记录</t></li>
</ul>
<p>删除：</p>
<ul>
<li>boolean removeById(Serializable id)：根据id删除</li>
<li>boolean removeByMap(Map<String, Object> columnMap)：根据条件删除</li>
<li>boolean remove(Wrapper<t> queryWrapper)：使用Wrapper封装条件删除</t></li>
<li>boolean removeByIds(Collection<? extends Serializable> idList)：删除一批</li>
</ul>
<p>修改：</p>
<ul>
<li>boolean updateById(T entity)：修改</li>
<li>boolean update(Wrapper<t> updateWrapper)：根据Wrapper修改</t></li>
<li>boolean update(T entity, Wrapper<t> updateWrapper)：使用Wrapper查询出结果，修改为entity</t></li>
<li>boolean updateBatchById(Collection<t> entityList)：批量修改</t></li>
<li>updateBatchById(Collection<t> entityList, int batchSize)：一次性批量修改batchSize条记录</t></li>
<li>boolean saveOrUpdate(T entity)：如果id存在则修改，如果id不存在则新增</li>
</ul>
<p>查询：</p>
<ul>
<li>T getById(Serializable id)：根据id查询</li>
<li>List<t> listByIds(Collection<? extends Serializable> idList)：根据一批id查询多条记录</t></li>
<li>List<t> listByMap(Map<String, Object> columnMap)：根据条件查询多条记录</t></li>
<li>T getOne(Wrapper<t> queryWrapper)：根据Wrapper查询一条记录，如果查询到多条则抛出异常</t></li>
<li>T getOne(Wrapper<t> queryWrapper, boolean throwEx)：根据Wrapper查询一条记录，通过throwEx决定是否抛出异常</t></li>
<li>int count()：查询总记录数</li>
<li>int count(Wrapper<t> queryWrapper)：根据条件查询总记录数</t></li>
</ul>
<p>分页：</p>
<ul>
<li><E extends IPage<t>> E page(E page, Wrapper<t> queryWrapper)：带条件分页查询，当前页数据为T类型</t></t></li>
<li><E extends IPage<t>> E page(E page)：无条件分页</t></li>
<li>List<Map<String, Object>> listMaps(Wrapper<t> queryWrapper)：带条件分页查询，当前页数据为HashMap类型</t></li>
<li>List<Map<String, Object>> listMaps()：无条件分页</li>
</ul>
<p>测试代码：</p>
<pre><code class="language-java">package com.test;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.conditions.query.QueryChainWrapper;
import com.baomidou.mybatisplus.extension.conditions.update.UpdateChainWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.test.entity.User;
import com.test.service.IUserService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.HashMap;
import java.util.List;
import javax.annotation.Resource;

@SpringBootTest(classes = MyBatisPlusApplication.class)
@RunWith(SpringRunner.class)
public class Demo07_Service {

@Resource
private IUserService userService;

/**
* 新增
*
* @throws Exception
*/
@Test
public void test1() throws Exception {
   User user = new User(null, "xiaohui", "0", 20);
   userService.save(user);
}

/**
* 如果id存在则修改,不存在则新增
*
* @throws Exception
*/
@Test
public void test2() throws Exception {
   User user = new User(1L, "xiaohui", "0", 20);
   userService.saveOrUpdate(user);
}

/**
* 根据id删除
*
* @throws Exception
*/
@Test
public void test3() throws Exception {
   userService.removeById(1L);
}

/**
* 根据id修改
*
* @throws Exception
*/
@Test
public void test4() throws Exception {
   User user = new User(1L, "xiaolan", "1", 18);
   userService.updateById(user);
}

/**
* 根据id查询
*
* @throws Exception
*/
@Test
public void test5() throws Exception {
   User user = userService.getById(1L);
   System.out.println(user);
}

/**
* 查询列表
*
* @throws Exception
*/
@Test
public void test6() throws Exception {
   QueryWrapper<User> wrapper = Wrappers.query();
   wrapper.in("id", "1", "2");
   // 查询所有
   // List<User> userList = userService.list();
   // 通过wrapper查询
   List<User> userList = userService.list(wrapper);
   for (User user : userList) {
         System.out.println(user);
   }
}

/**
* 查询总记录数
*
* @throws Exception
*/
@Test
public void test7() throws Exception {
   QueryWrapper<User> wrapper = Wrappers.query();
   wrapper.like("name", "a");
   // 查询总记录数
   //int count = userService.count();
   // 根据条件查询总记录数
   int count = userService.count(wrapper);
   System.out.println(count);
}

/**
* 分页查询(当前页类型为指定类型)
*
* @throws Exception
*/
@Test
public void test8() throws Exception {
   Page<User> page = new Page<>(1, 3);
   userService.page(page);
   // 当前页数据
   List<User> pageData = page.getRecords();
   for (User user : pageData) {
         System.out.println(user);
   }
   System.out.println("------------");
   System.out.println("当前页：" + page.getCurrent());
   System.out.println("每页显示的条数：" + page.getSize());
   System.out.println("总记录数：" + page.getTotal());
   System.out.println("总页数：" + page.getPages());
   System.out.println("是否有上一页：" + page.hasPrevious());
   System.out.println("是否有下一页：" + page.hasNext());
}

/**
* 分页查询(当前页结果为HashMap类型)
*
* @throws Exception
*/
@Test
public void test9() throws Exception {
   Page page = new Page<>(1, 3);
   userService.pageMaps(page);
   // 当前页数据
   List<HashMap<String, Object>> pageData = page.getRecords();
   for (HashMap userMap : pageData) {
         System.out.println(userMap);
   }
   System.out.println("------------");
   System.out.println("当前页：" + page.getCurrent());
   System.out.println("每页显示的条数：" + page.getSize());
   System.out.println("总记录数：" + page.getTotal());
   System.out.println("总页数：" + page.getPages());
   System.out.println("是否有上一页：" + page.hasPrevious());
   System.out.println("是否有下一页：" + page.hasNext());
}

/**
* 链式查询
*
* @throws Exception
*/
@Test
public void test10() throws Exception {
   QueryChainWrapper<User> chainWrapper = userService.query();
   // SQL: SELECT id,name,age FROM user WHERE (id IN (?,?,?) AND name LIKE ?)
   List<User> userList = chainWrapper.select("id", "name", "age")
            .in("id", "1", "2", "3")
            .like("name", "a")
            .list();
   for (User user : userList) {
         System.out.println(user);
   }
}

/**
* 链式修改
*
* @throws Exception
*/
@Test
public void test11() throws Exception {
   UpdateChainWrapper<User> chainWrapper = userService.update();
   // SQL: UPDATE user SET age=? WHERE (id IN (?,?) OR sex = ?)
   chainWrapper.in("id","1","2")
            .or()
            .eq("sex","0")
            .set("age",20)
            .update();
}
}
</code></pre>
<h1 id="七常用注解">七、常用注解</h1>
<h2 id="71-tablename">7.1 @TableName</h2>
<p>操作数据库表时，<code>Mapper</code>接口继承<code>BaseMapper</code>，泛型名和数据库表名对应，如果数据表名为<code>t_user</code>，而<code>BaseMapper</code>的泛型为实体类<code>User</code>，导致找不到数据库的表。</p>
<p>1）解决方法1：实体类使用<code>@TableName</code>注解，<code>value</code>值为表名</p>
<pre><code class="language-java">@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("t_user")
public class User {
private Long id;
private String name;
private String sex;
private Integer age;
}
</code></pre>
<p>2）解决方法2：如果多张表的表名为<code>t_user/t_cat/t_xxx</code>，不需要为每一个实体类添加<code>@TableName</code>注解，在<code>MyBatis</code>全局配置即可，为所有表名添加前缀</p>
<pre><code class="language-yaml">mybatis-plus:
global-config: # MyBatisPlus全局配置
db-config: # 配置数据库
   table-prefix: t_#配置表名前缀为t_
</code></pre>
<h2 id="72-tableid">7.2 @TableId</h2>
<p><code>MyBatisPlus</code>在实现<code>CRUD</code>默认会将<code>Id</code>作为主键，在插入数据时，如果主键不叫<code>Id</code>则添加功能会失败</p>
<p>解决方案：<code>@TableId</code>注解标识属性，将此属性对应的字段指定为主键</p>
<pre><code class="language-java">@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("t_user")
public class User {
// 将当前属性所对应的字段作为主键
@TableId
private Long id;
private String name;
private String sex;
private Integer age;
}
</code></pre>
<p>1）value属性</p>
<p>问题：实体类中被标识为主键的属性名为<code>id</code>，而数据库的主键为<code>uid</code>，则<code>id</code>属性不会对应<code>uid</code>字段上</p>
<p>解决方案：使用<code>@TableId</code>的<code>value</code>属性设置当前主键字段的字段名为<code>uid</code></p>
<pre><code class="language-java">package com.test.entity;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
@TableId(value = "uid")
private Long id;
private String name;
private String sex;
private Integer age;
}
</code></pre>
<p>2）主键策略</p>
<p><code>MybatisPlus</code>为我们支持了许多种的主键策略；</p>
<p>主键策略是指<code>Mybatis-plus</code>可以自动生成主键的策略，不需要手动插入主键，由<code>MybatisPlus</code>的主键策略帮我们自动生成主键</p>
<p>官网资料：https://baomidou.com/pages/e131bd/#spring-boot)</p>
<p>在枚举类<code>IdType</code>中定制有如下几种注解策略：</p>
<pre><code class="language-java">public enum IdType {

// 数据自增(该类型请确保数据库设置了 ID自增否则无效)
AUTO(0),

// 采用雪花算法生成ID
NONE(1),

// 交由用户自己分配ID(必须分配,不分配则出现异常,除非数据库表本身设置了自增)
INPUT(2),

// 采用雪花算法((主键类型为number或string)
// 默认实现类 {@link com.baomidou.mybatisplus.core.incrementer.DefaultIdentifierGenerator}
// (雪花算法)
ASSIGN_ID(3),

// 分配UUID (主键类型为 string)
// 默认实现类 {@link com.baomidou.mybatisplus.core.incrementer.DefaultIdentifierGenerator}
// (UUID.replace("-",""))
ASSIGN_UUID(4),

// 不推荐使用,推荐使用ASSIGN_ID
@Deprecated
ID_WORKER(3),

// 不推荐使用,推荐使用ASSIGN_ID
@Deprecated
ID_WORKER_STR(3),

// 不推荐使用,推荐使用ASSIGN_UUID
@Deprecated
UUID(4);

private final int key;

IdType(int key) {
   this.key = key;
}
}
</code></pre>
<p>实体类：</p>
<pre><code class="language-java">@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
// 使用数据库自增策略
@TableId(type = IdType.AUTO)
private Long id;
private String name;
private String sex;
private Integer age;
}
</code></pre>
<p>全局配置自增策略：</p>
<pre><code class="language-yaml">mybatis-plus:
global-config: # MyBatisPlus全局配置
db-config: # 配置数据库
   id-type: auto # 统一设置id生成策略
</code></pre>
<p>测试代码：</p>
<pre><code class="language-java">package com.test;

import com.test.entity.User;
import com.test.service.IUserService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.annotation.Resource;

@SpringBootTest(classes = MyBatisPlusApplication.class)
@RunWith(SpringRunner.class)
public class Demo08 {

@Resource
private IUserService userService;

@Test
public void test1() throws Exception {
   User user = new User(111L, "xiaohui", "0", 20);
   userService.save(user);
}
}
</code></pre>
<h2 id="73-tablefield">7.3 @TableField</h2>
<p>如果实体类的普通属性名，和数据库非主键的字段名不一致解决方案：<code>@TableField</code>设置对应字段名</p>
<pre><code class="language-java">@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {

@TableId(type = IdType.AUTO)
private Long id;

@TableField("user_name")
private String name;

@TableField("user_sex")
private String sex;

@TableField("user_age")
private Integer age;
}
</code></pre>
<h2 id="74-tablelogic">7.4 @TableLogic</h2>
<p>在实际开发中，我们删除一条记录可能只是修改其状态，并不是真正的从数据库中删除掉；这样的删除成为逻辑删除；</p>
<ul>
<li>逻辑删除：表中设置字段为删除状态比如删除为1 未删除为0 则查询时，只会查到状态为0的数据(可以进行数据恢复)。</li>
<li>物理删除：从表中删除。</li>
</ul>
<p>准备一张数据表：</p>
<pre><code class="language-mysql">DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`(
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
`is_delete` int(11) NULL DEFAULT NULL COMMENT '是否删除 0：未删除 1：删除',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES (1, 'xiaohui', 0);
INSERT INTO `emp` VALUES (2, 'xiaolan', 0);
</code></pre>
<p>实体类：</p>
<pre><code class="language-java">package com.test.entity;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Emp {

@TableId(type = IdType.AUTO)
private Long id;
private String name;

/*
* 表示该列为逻辑删除字段
* 0: 表示未删除
* 1: 表示已删除
*/
@TableLogic
private Integer isDelete;
}
</code></pre>
<p>测试代码：</p>
<pre><code class="language-java">@Resource
private EmpMapper empMapper;

@Test
public void test2() throws Exception {
// 只是逻辑删除(把id为1的记录的is_delete改为1)
empMapper.deleteById(1L);
}
</code></pre>
<p>执行代码，观察日志：</p>
<p><img src="https://img-blog.csdnimg.cn/img_convert/88932cba52ce951e2a8844dfb3228b27.png" alt="" loading="lazy"></p>
<blockquote>
<p>Tips：本质上就是执行了一个update</p>
</blockquote>
<p>当实体类中有标注逻辑删除字段时，在查询时是不会查询被逻辑删除的记录的，示例代码：</p>
<pre><code class="language-java">@Test
public void test3() throws Exception {
// 不会查询is_delete为1的记录
List<Emp> empList = empMapper.selectList(null);
for (Emp emp : empList) {
   System.out.println(emp);
}
}
</code></pre>
<p>执行结果：</p>
<p><img src="https://img2024.cnblogs.com/blog/2745643/202507/2745643-20250711134902034-2085318479.png" alt="1ccd868ff360a8b9bc50324249304f7a" loading="lazy"></p>
<h2 id="75-enumvalue">7.5 @EnumValue</h2>
<p>在数据库中，经常会有一些字段符合枚举类型；</p>
<p>例如：</p>
<ul>
<li>sex：0代表男，1代表女</li>
<li>status：0代表未开始，1代表进行中，2代表已结束</li>
<li>is_secret：0代表私密，1代表公开等</li>
<li>is_mark：0代表未签收，1代表拒签，2代表已签收</li>
</ul>
<p>我们通常都是将以上字段设置为char或者int类型，然后通过对应的字符/数字代表对应的含义，然后到Java代码中我们通常都需要做类似于如下的判断：</p>
<pre><code><span>${sex==0?'男':'女'}</span>

<if test=${status==0}>未开始</if>
<if test=${status==1}>进行中</if>
<if test=${status==2}>已结束</if>

if(status=0){
// 未开始
}else if(status==1){
// 进行中
} else if(status==2){
// 已结束
}
</code></pre>
<p>以上代码比较繁琐，并且可读性不是很高；</p>
<p>在<code>MyBatisPlus</code>中支持我们定义一个枚举与数据库中的字段对应起来，然后在枚举类中，使用<code>@EnumValue</code>注解标注真实的值（与数据库的字段对应），然后定义一个<code>String</code>类型的字段表示这个枚举项代表的字符串含义；</p>
<p>准备一张数据表：</p>
<pre><code class="language-mysql">DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`
(
`id` int(11)                                              NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
`sex`varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别 0:男 1:女',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
AUTO_INCREMENT = 3
CHARACTER SET = utf8
COLLATE = utf8_general_ci
ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '小灰', '0');
INSERT INTO `student` VALUES (2, '小蓝', '1');
</code></pre>
<p>定义性别枚举：</p>
<pre><code class="language-java">package com.test.enums;

import com.baomidou.mybatisplus.annotation.EnumValue;
import lombok.Getter;

@Getter
@AllArgsConstructor
public enum SexEnum {
MAN(0, "男"),
WOMAN(1, "女");

// 这一列的值和数据库表映射
@EnumValue
private Integer sexValue;

// 这个字段就是这个枚举项的字符串含义
private String sexName;
}
</code></pre>
<p>扫描枚举包：</p>
<pre><code class="language-yaml">#配置日志
mybatis-plus:
# 扫描枚举包
type-enums-package: com.test.enum_
</code></pre>
<p>定义实体类：</p>
<pre><code class="language-java">package com.test.entity;

import com.test.enums.SexEnum;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
private Integer id;
private String name;

/*
   使用枚举类型
   当从数据库中查询到了0则赋值SexEnum.MAN给sex变量
   当从数据库中查询到了1则赋值SexEnum.WOMAN给sex变量
*/
private SexEnum sex;
}
</code></pre>
<p>定义Mapper接口：</p>
<pre><code class="language-java">@Repository
public interface StudentMapper extends BaseMapper<Student> {
}
</code></pre>
<p>测试代码：</p>
<pre><code class="language-java">@Resource
private StudentMapper studentMapper;

@Test
public void test4() throws Exception {
System.out.println(studentMapper.selectById(1L));
System.out.println(studentMapper.selectById(2L));
}
</code></pre>
<p><img src="https://img2024.cnblogs.com/blog/2745643/202507/2745643-20250711134924880-27268799.png" alt="df9ee7757e38a7fd47df2849c4327514" loading="lazy"></p>
<p>虽然sex字段为变为了枚举类型，但是将搜索条件设置为sex=0依旧可以搜索到符合条件的记录：</p>
<pre><code class="language-java">@Test
public void test5() throws Exception {
QueryWrapper<Student> wrapper = Wrappers.query();
// 搜索条件为0依旧可以搜索出来
wrapper.eq("sex", "0");
List<Student> stuList = studentMapper.selectList(wrapper);
for (Student student : stuList) {
   System.out.println(student);
}
}
</code></pre>
<p><img src="https://img2024.cnblogs.com/blog/2745643/202507/2745643-20250711134945689-289414397.png" alt="097a44edf9028c2f505b63cf783c99e8" loading="lazy"></p>
<h2 id="76-version">7.6 @Version</h2>
<p>1）乐观锁与悲观锁</p>
<p>在多个客户端（线程/进程）操作同一个资源并发生写的操作时，我们就需要保证数据的安全性了。</p>
<p>如图所示：<br>
当我们在购买车票时，首先会进行票数的查询，例如A1在购买车票时查询到票还有100张，准备购买第100张票，与此同时，A2也查询到票数为100，也将购买第100张票；</p>
<p><img src="https://img2024.cnblogs.com/blog/2745643/202507/2745643-20250711135004162-468127181.png" alt="074e0c858dea517bf0c8230034ffca74" loading="lazy"></p>
<blockquote>
<p>Tips：上述图中，两个窗口在买票之前分别查询了票，发现票数余额为100，然后都卖了第100张票，出现多卖情况</p>
</blockquote>
<p>在并发修改某一资源时，我们必须保证线程安全的问题。在操作之前先加锁，这种方式就是采用悲观锁的方式；</p>
<ul>
<li>悲观锁的概念：悲观锁简单的理解就是程序处于悲观状态，在操作任何资源时都认为其他程序会来修改当前资源，自己不放心，因此在操作资源时加锁。</li>
</ul>
<p><img src="https://img2024.cnblogs.com/blog/2745643/202507/2745643-20250711135024556-207072286.png" alt="57cad027648bbf54992f3d9afacc9e79" loading="lazy"></p>
<p>悲观锁虽然保证了程序的安全性，同时效率也降低了很多，在一个客户端操作时，其他客户端均不可操作，降低了系统的并发性能。</p>
<ul>
<li>乐观锁概念：乐观锁简单理解就是程序一直处于乐观状态，在操作任何资源时认为其他程序不会来修改当前资源，整个过程不加锁，不加锁效率是可以保证，但不是又回到了我们最初的那个状态吗？即线程安全问题。</li>
</ul>
<p>我们可以在每条记录中分配一个_version字段，每当我们对记录进行更新时，此版本号都会自增。我们可以借助该字段帮我们完成乐观锁。保证线程安全问题。</p>
<p><img src="https://img2024.cnblogs.com/blog/2745643/202507/2745643-20250711135041815-1385299393.png" alt="2b1cc810fd2950e584c258bcc7b76ff4" loading="lazy"></p>
<p>实现方式：</p>
<pre><code class="language-mysql">-- 要修改数据之前，先查该数据上一次修改的时间戳
select version
from table_
where id = 1;

-- 修改数据时，更新版本号
update table_
set goods_name='小苹果',
version=version + 1
where version = ${version};
</code></pre>
<p>2）@Version实现乐观锁<br>
建立数据表：</p>
<pre><code class="language-mysql">DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods`
(
`id` int(11)                                              NOT NULL AUTO_INCREMENT,
`name`varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`count` int(11)                                              NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
AUTO_INCREMENT = 3
CHARACTER SET = utf8
COLLATE = utf8_general_ci
ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of goods
-- ----------------------------
INSERT INTO `goods` VALUES (1, '手机', 100);
INSERT INTO `goods` VALUES (2, '电脑', 100);
</code></pre>
<p>实体类：</p>
<pre><code class="language-java">package com.test.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.Version;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Goods {
@TableId(type = IdType.AUTO)
private Long id;
private String name;
private Integer count;

@Version          // 乐观锁字段
private Integer version;
}
</code></pre>
<p>Mapper接口：</p>
<pre><code class="language-java">package com.test.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.test.entity.Goods;
import org.springframework.stereotype.Repository;

@Repository
public interface GoodsMapper extends BaseMapper<Goods> {
}
</code></pre>
<p>测试代码：</p>
<pre><code class="language-java">@Resource
private GoodsMapper goodsMapper;

@Test
public void test6() throws Exception {
Goods goods = goodsMapper.selectById(1L);
goods.setCount(goods.getCount() - 1);
goodsMapper.updateById(goods);    // 修改完毕后,version在本次version的基础上+1
}
</code></pre>
<p><img src="https://img2024.cnblogs.com/blog/2745643/202507/2745643-20250711135101917-1213050493.png" alt="f6993f48c8de597599eec0f6c7e40cd0" loading="lazy"></p>
<blockquote>
<p>Tips：修改的时候version自动在原来的基础上+1；</p>
</blockquote>
<h1 id="八mybatisplus绑定xml">八、MyBatisPlus绑定xml</h1>
<p><code>MyBatisPlus</code>本质上也是<code>MyBatis</code>，因此也支持我们自定义<code>SQL</code>语句，编写<code>Mapper.xml</code>与<code>Mapper</code>接口进行绑定；</p>
<p>1）在application.yml中扫描Mapper.xml：</p>
<pre><code class="language-yaml">#配置日志
mybatis-plus:
# 扫描mapper.xml文件
mapper-locations: classpath:com/test/mapper/*.xml
# 配置包别名
type-aliases-package: com.test.entity
</code></pre>
<p>2）在Mapper接口中扩展方法：</p>
<pre><code class="language-java">@Repository
public interface UserMapper extends BaseMapper<User> {
List<User> findAll();
}
</code></pre>
<p>3）编写Mapper.xml：</p>
<pre><code class="language-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.test.mapper.UserMapper">
<select id="findAll" resultType="user">
   select * from user
</select>
</mapper>
</code></pre>
<h1 id="九activerecord使用">九、ActiveRecord使用</h1>
<h2 id="91-activerecord简介">9.1 ActiveRecord简介</h2>
<p><code>ActiveRecord</code>也属于<code>ORM</code>（对象关系映射）层，由<code>Rails</code>最早提出，遵循标准的<code>ORM</code>模型：表映射到记录，记录映射到对象，字段映射到对象属性。配合遵循的命名和配置惯例，能够很大程度的快速实现模型的操作，而且简洁易懂。</p>
<p><code>ActiveRecord</code>的主要思想是：</p>
<p>1）每一个数据库表对应创建一个类，类的每一个对象实例对应于数据库中表的一行记录，通常表的每个字段在类中都有相应的<code>Field</code>；<br>
2）<code>ActiveRecord</code>同时负责把自己持久化，在<code>ActiveRecord</code>中封装了对数据库的访问，即<code>CURD</code>；<br>
3）<code>ActiveRecord</code>是一种领域模型(<code>Domain Model</code>)，封装了部分业务逻辑。</p>
<p>简而言之，<code>AR</code>建立了<code>Java</code>对象与数据库表逻辑上的直接映射，方便了程序的编写。而在<code>MyBatisPlus</code>中使用<code>AR</code>非常简单，只需让<code>JavaBean</code>继承<code>Model</code>类即可。</p>
<h2 id="92-model类使用">9.2 Model类使用</h2>
<p>在<code>MyBatisPlus</code>中，只要将我们的<code>JavaBean</code>继承与<code>Model</code>类即可获取<code>AR</code>功能，即<code>JavaBean</code>自身实现自身的<code>CURD</code>；</p>
<p>让<code>JavaBean</code>继承<code>Model</code>：</p>
<pre><code class="language-java">package com.test.entity;

@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("user")
public class User extends Model<User> {       // 继承Model
@TableId(type = IdType.AUTO)
private Long id;
private String name;
private String sex;
private Integer age;
}
</code></pre>
<p>测试代码：</p>
<pre><code class="language-java">package com.test;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.test.entity.User;
import com.test.mapper.UserMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;
import javax.annotation.Resource;

@SpringBootTest(classes = MyBatisPlusApplication.class)
@RunWith(SpringRunner.class)
public class Demo12_ActiveRecord {

@Resource
private UserMapper userMapper;

/**
* 新增
* @throws Exception
*/
@Test
public void test1() throws Exception {
   User user = new User(100L, "xiaoming", "0", 20);
   user.insert();
}

/**
* 删除
* @throws Exception
*/
@Test
public void test2() throws Exception {
   User user = new User();
   user.setId(100L);
   user.deleteById();
}

/**
* 修改
* @throws Exception
*/
@Test
public void test3() throws Exception {
   User user = new User(1L, "xiaohui", "1", 22);
   user.updateById();
}

/**
* 根据id查询
* @throws Exception
*/
@Test
public void test4() throws Exception {
   User user = new User();
   user.setId(100L);
   User dbUser = user.selectById();
   System.out.println(dbUser);
}

/**
* 查询全部
* @throws Exception
*/
@Test
public void test5() throws Exception {
   User user = new User();
   List<User> userList = user.selectAll();
   for (User dbUser : userList) {
         System.out.println(dbUser);
   }
}

/**
* 根据Wrapper条件查询
* @throws Exception
*/
@Test
public void test6() throws Exception {
   QueryWrapper<User> wrapper = Wrappers.query();
   wrapper.like("name", "a");
   User user = new User();
   // 根据条件查询
   List<User> userList = user.selectList(wrapper);
   for (User dbUser : userList) {
         System.out.println(dbUser);
   }
}

/**
* 分页查询
* @throws Exception
*/
@Test
public void test7() throws Exception {
   QueryWrapper<User> wrapper = Wrappers.query();
   wrapper.like("name", "a");
   Page<User> page = new Page<>(1, 3);
   User user = new User();
   // 分页查询
   user.selectPage(page, wrapper);
   List<User> records = page.getRecords();
   for (User record : records) {
         System.out.println(record);
   }
}
}
</code></pre>
<h1 id="十多数据源配置">十、多数据源配置</h1>
<p>1）引入多数据源依赖：</p>
<pre><code class="language-xml">
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.5.1</version>
</dependency>
</code></pre>
<p>2）准备两个数据库</p>
<pre><code class="language-mysql">DROP database IF EXISTS test1;
create database test1;

use test1;

DROP TABLE IF EXISTS user;
CREATE TABLE `user`
(
`id`    bigint(20) NOT NULL COMMENT '主键ID',
`name` varchar(30) DEFAULT NULL COMMENT '姓名',
`sex`    char(1) DEFAULT NULL COMMENT '性别 0:男 1:女',
`age`    int(11) DEFAULT NULL COMMENT '年龄',
`birthday` date    DEFAULT NULL COMMENT '生日',
PRIMARY KEY (`id`)
);

INSERT INTO `user` VALUES (1, 'Jone', '1', 27, '2001-10-04');
INSERT INTO `user` VALUES (2, 'Jack', '0', 20, '1999-10-04');
INSERT INTO `user` VALUES (3, 'Tom', '1', 28, '1996-08-12');

-- -----------------------------------------------

DROP database IF EXISTS test2;
create database test2;

use test2;

DROP TABLE IF EXISTS user;
CREATE TABLE `user`
(
`id`    bigint(20) NOT NULL COMMENT '主键ID',
`name` varchar(30) DEFAULT NULL COMMENT '姓名',
`sex`    char(1) DEFAULT NULL COMMENT '性别 0:男 1:女',
`age`    int(11) DEFAULT NULL COMMENT '年龄',
`birthday` date    DEFAULT NULL COMMENT '生日',
PRIMARY KEY (`id`)
);

INSERT INTO `user` VALUES (4, 'Sandy', '1', 21, '2001-10-04');
INSERT INTO `user` VALUES (5, 'Billie', '0', 24, '1992-09-07');
INSERT INTO `user` VALUES (6, 'Jackson', '0', 18, '1996-08-12');
</code></pre>
<p>3）多数据源配置：</p>
<pre><code class="language-yaml">spring:
datasource:
dynamic:
# 没有匹配的数据源时默认匹配的数据源
   primary: master
   # 当没有匹配的数据源是是否采用默认的数据源,
   # true: 严格匹配,如果没有匹配到数据源则抛出异常
   # false(默认值):如果没有匹配到数据源则使用默认的数据源
   strict: false
   datasource:
   # 数据源的名称(名字任意)
   master:
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://localhost:3306/test1?serverTimezone=GMT%2b8
      username: root
      password: admin
   # 数据源的名称(名字任意)
   slave:
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://localhost:3306/test2?serverTimezone=GMT%2b8
      username: root
      password: admin
#配置日志
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
</code></pre>
<p>4）启动类：</p>
<pre><code class="language-java">package com.test;

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

@SpringBootApplication
@MapperScan("com.test.mapper")
public class MyBatisPlusApplication {
public static void main(String[] args) {
   SpringApplication.run(MyBatisPlusApplication.class, args);
}
}
</code></pre>
<p>5）实体类：</p>
<pre><code class="language-java">package com.test.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;

@Data
@EqualsAndHashCode(callSuper = true)
@AllArgsConstructor
@NoArgsConstructor
@TableName("user")
public class User extends Model<User> {

@TableId(type = IdType.AUTO)
private Long id;
private String name;
private String sex;
private Integer age;
}
</code></pre>
<p>6）编写两个Mapper：</p>
<pre><code class="language-java">package com.test.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.test.entity.User;
import org.springframework.stereotype.Repository;

@DS("master")       // 指定数据源的名称(若指定的数据源不存在则使用默认的)
@Repository
public interface UserMapperMaster extends BaseMapper<User> {
}
</code></pre>
<pre><code class="language-java">package com.test.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.test.entity.User;
import org.springframework.stereotype.Repository;

@DS("slave")                // 指定数据源的名称(若指定的数据源不存在则使用默认的)
@Repository
public interface UserMapperSlave extends BaseMapper<User> {
}
</code></pre>
<p>7）测试类：</p>
<pre><code class="language-java">package com.test;

import com.test.entity.User;
import com.test.mapper.UserMapperMaster;
import com.test.mapper.UserMapperSlave;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;
import javax.annotation.Resource;

@SpringBootTest(classes = MyBatisPlusApplication.class)
@RunWith(SpringRunner.class)
public class Demo01_Dynamic {

@Resource
private UserMapperMaster userMapperMaster;

@Resource
private UserMapperSlave userMapperSlave;

@Test
public void test1() {
   // 使用的是master数据源
   List<User> userList = userMapperMaster.selectList(null);
   for (User user : userList) {
         System.out.println(user);
   }
}

@Test
public void test2() {
   // 使用slave数据源
   List<User> userList = userMapperSlave.selectList(null);
   for (User user : userList) {
         System.out.println(user);
   }
}
}
</code></pre><br><br>
来源：https://www.cnblogs.com/ciel717/p/18949729

頁: [1]

琼殿技术论坛's Archiver

MybatisPlus使用详情