天天看點
傳回

MyBatis-Plus 架構學習筆記

文章目錄

    • 1. 基本使用
    • 2. 常用注解
      • 2.1. @TableName
      • 2.2. @TableId
      • 2.3. @TableField
      • 2.4. @Version
      • 2.5. @EnumValue
      • 2.6. @TableLogic
    • 3. CRUD
      • 3.1. 查詢
        • 3.1.1. selectList
          • 3.1.1.1. 單條件查詢示例
          • 3.1.1.2. 多條件查詢示例
          • 3.1.1.3. 查詢結果列的控制
          • 3.1.1.4. 條件作用，非空判斷
          • 3.1.1.5. allEq
        • 3.1.2. selectById
        • 3.1.3. selectBatchIds
        • 3.1.4. selectByMap
        • 3.1.5. selectCount
        • 3.1.6. selectMaps
        • 3.1.7. selectPage（分頁查詢）
        • 3.1.8. selectMapsPage（分頁查詢）
        • 3.1.9. selectObjs
        • 3.1.10. selectOne
      • 3.2. 添加
        • 3.2.1. insert
        • 3.2.2. insertOrUpdate
      • 3.3. 删除
        • 3.3.1. deleteById
        • 3.3.2. deleteBatchIds
        • 3.3.3. delete
        • 3.3.4. deleteByMap
      • 3.4. 修改
        • 3.4.1. updateById
        • 3.4.2. update
    • 4. Lambda 條件構造器
    • 5. 自定義方法 SQL
      • 5.1. 簡單自定義
      • 5.2. 複雜自定義
      • 5.3. 自定義分頁
    • 6. service 方法調用
    • 7. MyBatis-Plus 自動生成
    • 8. 其他配置
    • 9. 注意事項

1. 基本使用

1、建立 Maven 工程

MyBatis-Plus 架構學習筆記

2、pom.xml 引入 MyBatis Plus 的依賴

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

3、建立實體類

package com.example.mybatisplus.entity;

import lombok.Data;

@Data
public class User {
    private Long id;
    private String name;
    private Integer age;
}

4、建立 Mapper 接口

package com.example.mybatisplus.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.southwind.mybatisplus.entity.User;

public interface UserMapper extends BaseMapper<User> {
}

5、application.yml

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
    username: root
    password: 123456789
#SQL語句輸出，不設定這個，直接用下邊的方法，設定對應包的日志級别也行
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
#列印SQL語句，trace級别能把SQL執行結果列印出來，設定mapper包為trace級别
logging.level.com.example.demo.generator.mapper=trace

6、啟動類需要添加 @MapperScan(“mapper 所在的包”)，否則無法加載 Mppaer bean。

package com.example.mybatisplus;

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

@SpringBootApplication
@MapperScan(basePackages = {"com.example.mybatisplus.mapper"})
public class MybatisplusApplication {
    public static void main(String[] args) {
        SpringApplication.run(MybatisplusApplication.class, args);
    }
}

7、測試

package com.example.mybatisplus.mapper;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class UserMapperTest {

    @Autowired
    private UserMapper mapper;

    @Test
    void test(){
        mapper.selectList(null).forEach(System.out::println);
    }

}

2. 常用注解

2.1. @TableName

mp 會自動将實體駝峰命名對應為資料庫下劃線命名，如果命名沒有對應上，需要用該注解進行指定表名。

value

屬性映射資料庫的表名。

package com.example.mybatisplus.entity;

import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

@Data
@TableName(value = "user")
public class Account {
    private Long id;
    private String name;
    private Integer age;
}

2.2. @TableId

設定主鍵映射。mp 用來指定主鍵 id，預設會直接将 id 字段作為主鍵，如果主鍵不是 id，需要使用該注解進行指定。

value

屬性映射主鍵字段名。當主鍵名和實體類主鍵字段名不一緻的時候使用。

type

屬性設定主鍵類型，即主鍵的生成政策，預設為

IdType.NONE

。

  • AUTO：資料庫主鍵自增，開發者無需指派。該類型請確定資料庫設定了 ID自增，否則無效。主鍵值會回填到參數對象的主鍵字段中。如果開發者手動指派了，指派無效，依然是資料庫自增。
  • NONE：type 的預設類型，如果開發者沒有手動指派，則資料庫通過雪花算法算出一個 Long 值給主鍵指派，主鍵值會回填到參數對象的主鍵字段中。如果開發者手動指派，則存入該值。
  • INPUT：如果開發者沒有手動指派，則資料庫通過自增的方式給主鍵指派（該功能需要在資料庫表的主鍵開啟自動自增屬性，否則會報 id 不能為 null 的錯誤了），主鍵值不會回填到參數對象的主鍵字段中。如果開發者手動指派，則存入該值。
  • ASSIGN_ID：mp 自動指派，雪花算法。主鍵類型可為數值和字元串。如果開發者沒有手動指派，則通過雪花算法的方式給主鍵指派，主鍵值會回填到參數對象的主鍵字段中。如果開發者手動指派，則存入該值。
  • ASSIGN_UUID：主鍵的資料類型必須是 String，自動生成 UUID 進行指派，主鍵值會回填到參數對象的主鍵字段中。如果開發者手動指派，則存入該值。 
package com.example.mybatisplus.entity;

import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;

@Data
public class User {
	@TableId(value = "id", type = IdType.AUTO)
    private Long num;
    private String name;
    private Integer age;
}

2.3. @TableField

映射非主鍵字段。

value

屬性映射字段名。

exist

屬性表示是否為資料庫字段，預設 true 是，false 不是。如果實體類中的成員變量在資料庫中沒有對應的字段，則可以使用該屬性。主要用于 VO、DTO。

select

屬性表示是否查詢該字段。大字段可設定為 false 不加入 select 查詢範圍，邏輯删除字段 deleted 也可以使用該屬性。

fill

屬性表示是否自動填充，将對象存入資料庫的時候，由 MyBatis-Plus 自動給某些字段指派。常用于 create_time、update_time 指派。也用于新增人、修改人字段指派。

1、給表添加 create_time、update_time 字段

2、實體類中添加成員變量

package com.example.mybatisplus.entity;

import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.TableField;
import lombok.Data;

import java.util.Date;

@Data
public class User {
    private Long id;
    @TableField(value = "name",select = false)
    private String title;
    private Integer age;
    @TableField(exist = false)
    private String gender;
    @TableField(fill = FieldFill.INSERT)
    private Date createTime;
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private Date updateTime;
}

3、建立自動填充處理器

package com.example.mybatisplus.handler;

import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;

import java.util.Date;

@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
    @Override
    public void insertFill(MetaObject metaObject) {
        //判斷表中是否有對應字段，沒有對應字段的時候，不去執行自動填充，提高性能
        boolean hasCreateTimeSetter = metaObject.hasSetter("createTime");
        if (hasCreateTimeSetter){
            this.setFieldValByName("createTime", new Date(), metaObject);
        }
        boolean hasUpdateTimeSetter = metaObject.hasSetter("updateTime");
        if (hasUpdateTimeSetter){
            this.setFieldValByName("updateTime", new Date(), metaObject);
        }
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        //判斷表中是否有對應字段，沒有對應字段的時候，不去執行自動填充，提高性能
        boolean hasUpdateTimeSetter = metaObject.hasSetter("updateTime");
        if (hasUpdateTimeSetter) {
            //判斷該字段是否已經被手動指派，手動指派的将不會被自動填充，否則會被覆寫的
            Object val = this.getFieldValByName("updateTime", metaObject);
            if (val == null){
                this.setFieldValByName("updateTime", new Date(), metaObject);
            }
        }
    }
}

2.4. @Version

标記樂觀鎖，通過

version

字段來保證資料的安全性，當修改資料的時候，會以

version

作為條件，當條件成立的時候才會修改成功。用于并發

update

操作。每次

updaate

語句都會在條件中拼接

version

字段。

version = 1，線程1和線程2，并發執行，隻有一個能成功。

線程 1：update … set version = 2 where version = 1

線程 2：update … set version = 2 where version = 1

1、資料庫表添加 version 字段，預設值為 1

2、實體類添加 version 成員變量，并且添加 @Version 注解。

package com.example.mybatisplus.entity;

import com.baomidou.mybatisplus.annotation.Version;
import lombok.Data;

import java.util.Date;

@Data
public class User {
    private Long id;
    private String name;
    private Integer age;
    @Version
    private Integer version;
}

3、注冊配置類

package com.example.mybatisplus.config;

import com.baomidou.mybatisplus.extension.plugins.OptimisticLockerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MyBatisPlusConfig {
    //老版本
    @Bean
    public OptimisticLockerInterceptor optimisticLockerInterceptor(){
        return new OptimisticLockerInterceptor();
    }
    //新版本（老版本提示已删除的則寫這個）
	@Bean
	public MybatisPlusInterceptor mybatisPlusInterceptor() {
	    MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
	    interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
	    return interceptor;
	}
    
}

2.5. @EnumValue

1、通用枚舉類注解，将資料庫字段映射成實體類的枚舉類型成員變量。

package com.example.mybatisplus.enums;

import com.baomidou.mybatisplus.annotation.EnumValue;

public enum StatusEnum {
    WORK(1,"上班"),
    REST(0,"休息");

    StatusEnum(Integer code, String msg) {
        this.code = code;
        this.msg = msg;
    }

    @EnumValue
    private Integer code;
    private String msg;
}
           
package com.example.mybatisplus.entity;

import com.baomidou.mybatisplus.annotation.Version;
import lombok.Data;

import java.util.Date;

@Data
public class User {
    private Long id;
    private String name;
    private Integer age;
    
    private StatusEnum status;
}

application.yml 配置枚舉包掃描，枚舉值和枚舉自動轉換。

mybatis-plus
  type-enums-package: 
  	com.example.mybatisplus.enums

2、還有一種方法是通過實作接口的方式

package com.example.mybatisplus.enums;

import com.baomidou.mybatisplus.annotation.IEnum;

public enum StatusEnum implements IEnum<Integer> {
    WORK(1,"上班"),
    REST(0,"休息");

    StatusEnum(Integer code, String msg) {
        this.code = code;
        this.msg = msg;
    }

    private Integer code;
    private String msg;
	//這裡配置的是轉換的值，也就是時候資料庫存的是code值，查詢出來會被自動轉換為枚舉 WORK 或者 REST
    @Override
    public Integer getValue() {
        return this.code;
    }
}

2.6. @TableLogic

映射邏輯删除。

  • 邏輯删除，則 delete 語句會變為 update 語句，會将 deleted 字段置為已删除的值，比如1。
  • 查詢語句和更新語句會自動加過濾條件，比如 deleted=0（0表示未删除，1表示删除）。
  • 自定義 SQL 語句不會添加邏輯删除标志。需要自己添加這個過濾條件 deleted=0。

1、資料表添加 deleted 字段。

2、實體類添加注解和字段。

package com.example.mybatisplus.entity;

import com.baomidou.mybatisplus.annotation.TableLogic;
import lombok.Data;

import java.util.Date;

@Data
public class User {
    private Long id;
    private String title;
    private Integer age;
    @TableLogic
    private Integer deleted;
}

3、application.yml 添加配置。配置0表示未删除，1表示已删除。

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

3. CRUD

3.1. 查詢

3.1.1. selectList

3.1.1.1. 單條件查詢示例 
//SELECT * FROM user;
List<User> userList = userMapper.selectList(null);
           
QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (name = '張三')
wrapper.eq("name", "張三");
List<User> userList = userMapper.selectList(wrapper);
           
QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (status = 1)
wrapper.eq("status", StatusEnum.WORK);
List<User> userList = userMapper.selectList(wrapper);
           
QueryWrapper<User> wrapper = new QueryWrapper<>();
Map<String, Object> map = new HashMap<>();
map.put("name", "張三");
map.put("age", 20);
//SELECT * FROM user WHERE (name = '張三' AND age = 20)
wrapper.allEq(map);
List<User> userList = userMapper.selectList(wrapper);
           
QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (age < 20)
wrapper.lt("age", 20);
List<User> userList = userMapper.selectList(wrapper);
           
QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (age > 20)
wrapper.gt("age", 20);
List<User> userList = userMapper.selectList(wrapper);
           
QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (age <= 20)
wrapper.le("age", 20);
List<User> userList = userMapper.selectList(wrapper);
           
QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (age >= 20)
wrapper.ge("age", 20);
List<User> userList = userMapper.selectList(wrapper);
           
QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (age <> 20)
wrapper.ne("age", 20);
List<User> userList = userMapper.selectList(wrapper);
           
QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (name LIKE '%馬%')
wrapper.like("name", "馬");
List<User> userList = userMapper.selectList(wrapper);
           
QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (name LIKE '張%')
wrapper.likeRight("name", "張");
List<User> userList = userMapper.selectList(wrapper);
           
QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (name LIKE '%三')
wrapper.likeLeft("name", "三");
List<User> userList = userMapper.selectList(wrapper);
           
QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (
// id IN (select id from user where id < 10) AND
// age IN (select age from user where age > 3)
// )
wrapper.inSql("id", "select id from user where id < 10");
wrapper.inSql("age", "select age from user where age > 3");
List<User> userList = userMapper.selectList(wrapper);
           
QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user ORDER BY age DESC
wrapper.orderByDesc("age");
List<User> userList = userMapper.selectList(wrapper);
           
QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user ORDER BY age ASC
wrapper.orderByAsc("age");
List<User> userList = userMapper.selectList(wrapper);
           
QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user HAVING id > 5 ORDER BY age ASC
wrapper.orderByAsc("age");
wrapper.having("id > 5" );
List<User> userList = userMapper.selectList(wrapper);
3.1.1.2. 多條件查詢示例 
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//QueryWrapper<User> queryWrapper1 = Wrappers.query();
queryWrapper.like("name", "雨").lt("age", 40);
//WHERE (name LIKE '%雨%' AND age < 40?)
List<User> userList = userMapper.selectList(queryWrapper);
           
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name", "雨").between("age", 20, 40).isNotNull("email");
//WHERE (name LIKE '%雨%' AND age BETWEEN 20 AND 40 AND email IS NOT NULL)
List<User> userList = userMapper.selectList(queryWrapper);
           
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name", "王").or().ge("age", 25)
        .orderByDesc("age").orderByAsc("id");
//WHERE (name LIKE '王%' OR age >= 25) ORDER BY age DESC,id ASC
List<User> userList = userMapper.selectList(queryWrapper);
           
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.apply("date_format(create_time,'%Y-%m-%d') = {0}", "2020-11-03")
       .inSql("manager_id", "select id from user where name like '王%'");
//WHERE (date_format(create_time,'%Y-%m-%d') = '2020-11-03' AND manager_id IN (select id from user where name like '王%'))
List<User> userList = userMapper.selectList(queryWrapper);
           
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.ge("create_time", "2020-11-03 14:42:22").le("create_time", "2020-11-13 14:42:22");
//WHERE (create_time >= ? AND create_time <= ?)
List<User> userList = userMapper.selectList(queryWrapper);
           
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name", "王").and(
        wq -> wq.lt("age", 40).or().isNotNull("email")
);
//WHERE (name LIKE '王%' AND (age < 40 OR email IS NOT NULL))
List<User> userList = userMapper.selectList(queryWrapper);
           
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name", "王").or(
        wq -> wq.lt("age", 40).isNotNull("email")
);
//WHERE (name LIKE '王%' OR (age < 40 AND email IS NOT NULL))
List<User> userList = userMapper.selectList(queryWrapper);
           
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.nested(wq -> wq.lt("age", 40).or().isNotNull("email")).likeRight("name", "王");
//WHERE ((age < 40 OR email IS NOT NULL) AND name LIKE '王%')
List<User> userList = userMapper.selectList(queryWrapper);
           
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.in("age", Arrays.asList(30,31,32,33)).last("limit 1");
//WHERE (age IN (30,31,32,33)) limit 1
List<User> userList = userMapper.selectList(queryWrapper);
3.1.1.3. 查詢結果列的控制

隻查詢某幾列

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("id", "name").like("name", "雨").lt("age", 40);
//SELECT id,name FROM user WHERE (name LIKE '%雨%' AND age < 40?)
List<User> userList = userMapper.selectList(queryWrapper);

不查詢某幾列

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select(User.class,
        info-> !info.getColumn().equals("create_time") && !info.getColumn().equals("manager_id")
).like("name", "雨").lt("age", 40);
//SELECT id,name,age,email FROM user WHERE (name LIKE '%雨%' AND age < 40?)
List<User> userList = userMapper.selectList(queryWrapper);
3.1.1.4. 條件作用，非空判斷 
@Test
public void conditionTest(){
    String name = "王";
    String email = "";
    condition(name, email);
}
public void condition(String name, String email){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.like(StringUtils.isNotBlank(name),"name", name)
            .eq(StringUtils.isNotBlank(email),"email", email);
    //WHERE (name LIKE ?)
    List<User> userList = userMapper.selectList(queryWrapper);
    userList.forEach(System.out::println);
}
3.1.1.5. allEq 
Map<String, Object> params = new HashMap<>();
params.put("name", "王天風");
params.put("age", 25);
params.put("email", null);

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.allEq(params);
//FROM user WHERE (name = '王天風' AND age = 25 AND email IS NULL)
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);

QueryWrapper<User> queryWrapper1 = new QueryWrapper<>();
queryWrapper1.allEq(params, false);
//WHERE (name = '王天風' AND age = ?)
List<User> userList1 = userMapper.selectList(queryWrapper1);
userList1.forEach(System.out::println);

//将name字段過濾掉
QueryWrapper<User> queryWrapper2 = new QueryWrapper<>();
queryWrapper2.allEq((k,v)->!k.equals("name"), params);
//WHERE (age = 25 AND email IS NULL)
List<User> userList2 = userMapper.selectList(queryWrapper2);
userList2.forEach(System.out::println);

3.1.2. selectById 

//SELECT * FROM user WHERE id=7
User user = userMapper.selectById(7);

3.1.3. selectBatchIds 

//SELECT * FROM user WHERE id IN ( 7 , 8 , 9 )
List<User> userList = userMapper.selectBatchIds(Arrays.asList(7,8,9));

3.1.4. selectByMap 

Map<String, Object> map = new HashMap<>();
map.put("name", "張三");
map.put("age", 20);
//隻能做等值判斷，和allEq用法類似
//SELECT * FROM user WHERE name = '張三' AND age = 20
List<User> userList = userMapper.selectByMap(map);

3.1.5. selectCount 

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT COUNT( * ) FROM user WHERE (id > ?)
wrapper.gt("id", 1);
Integer count = userMapper.selectCount(wrapper);

3.1.6. selectMaps 

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (id > ?)
wrapper.gt("id", 1);
//傳回的是map，不是user對象
List<Map<String, Object>> mapList = userMapper.selectMaps(wrapper);

隻查詢某幾列，傳回 map，不會出現沒查詢的字段為 null

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("id", "name").like("name", "雨").lt("age", 40);
//SELECT id,name FROM user WHERE (name LIKE '%雨%' AND age < 40?)
List<Map<String, Object>> userList = userMapper.selectMaps(queryWrapper);
userList.forEach(System.out::println);

分組查詢統計，使用 map

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("manager_id", "avg(age) avg_age", "min(age) min_age", "max(age) max_age")
        .groupBy("manager_id").having("sum(age)<{0}", 500);
//SELECT manager_id,avg(age) avg_age,min(age) min_age,max(age) max_age FROM user GROUP BY manager_id HAVING sum(age)<500
List<Map<String, Object>> userList = userMapper.selectMaps(queryWrapper);
userList.forEach(System.out::println);

3.1.7. selectPage（分頁查詢）

分頁查詢都需要先配置 PaginationInterceptor 。

package com.example.mybatisplus.config;

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MyBatisPlusConfig {
    /**
     * 分頁查詢
     */
    @Bean
    public PaginationInterceptor paginationInterceptor(){
        return new PaginationInterceptor();
    }
}

新版本配置：

package com.example.demo.common.config;

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

/**
 * @author wangbo
 * @date 2021/5/12
 */
@Configuration
public class MyBatisPlusConfig {

    /**
     * 分頁插件配置
     * @return MybatisPlusInterceptor
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
}

然後直接使用 Page 對象進行分頁查詢。

//SELECT * FROM user LIMIT 3,3
Page<User> page = new Page<>(2,3);
Page<User> result = userMapper.selectPage(page, null);
//每頁大小
System.out.println(result.getSize());
//總記錄數
System.out.println(result.getTotal());
//記錄
result.getRecords().forEach(System.out::println);

詳細版本寫法：

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.ge("age", 26);

Page<User> page = new Page<>(3, 2);
IPage<User> iPage = userMapper.selectPage(page, queryWrapper);
//SELECT COUNT(1) FROM user WHERE (age >= 26)
//SELECT * FROM user WHERE (age >= 26) LIMIT 4,2
System.out.println("總頁數："+ iPage.getPages());
System.out.println("總記錄數：" + iPage.getTotal());
List<User> userList = iPage.getRecords();
userList.forEach(System.out::println);

不查總記錄數，可少執行一條SQL

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.ge("age", 26);

Page<User> page2 = new Page<>(3, 2, false);
IPage<User> iPage2 = userMapper.selectPage(page2, queryWrapper);
//SELECT * FROM user WHERE (age >= 26) LIMIT 4,2
System.out.println("總頁數："+ iPage2.getPages());
System.out.println("總記錄數：" + iPage2.getTotal());
List<User> userList2 = iPage2.getRecords();
userList2.forEach(System.out::println);

3.1.8. selectMapsPage（分頁查詢） 

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.ge("age", 26);

Page<Map<String, Object>> page1 = new Page<>(3, 2);
IPage<Map<String, Object>> iPage1 = userMapper.selectMapsPage(page1, queryWrapper);
//SELECT COUNT(1) FROM user WHERE (age >= 26)
//SELECT * FROM user WHERE (age >= 26) LIMIT 4,2
System.out.println("總頁數1："+ iPage.getPages());
System.out.println("總記錄數1：" + iPage.getTotal());
List<Map<String, Object>> userList1 = iPage1.getRecords();
userList1.forEach(System.out::println);

3.1.9. selectObjs

隻傳回了主鍵

//SELECT * FROM user
List<Object> list = userMapper.selectObjs(null);

隻傳回第一列

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("manager_id", "avg(age) avg_age", "min(age) min_age", "max(age) max_age")
        .groupBy("manager_id").having("sum(age)<{0}", 500);
//SELECT manager_id,avg(age) avg_age,min(age) min_age,max(age) max_age FROM user GROUP BY manager_id HAVING sum(age)<500
List<Object> userList = userMapper.selectObjs(queryWrapper);
userList.forEach(System.out::println);

3.1.10. selectOne

查詢一條記錄數，可以為null，注意不能為多條

//SELECT * FROM user WHERE (id = 1)
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("id", 1);
//結果集必須是一條記錄
User user = userMapper.selectOne(wrapper);
System.out.println(user);

3.2. 添加

3.2.1. insert 

User user = new User();
user.setName("小明");
user.setAge(22);
int i = userMapper.insert(user);
System.out.println(i);
System.out.println(user);

3.2.2. insertOrUpdate

無ID 就是插入，有 ID 會先執行查詢，有則更新，無則插入。

User user = new User();
user.setId(3L);
user.setAge(32);
user.setName("史蒂夫");
boolean result = user.insertOrUpdate();
System.out.println("插入是否成功：" + result);
//SELECT * FROM user WHERE id=3
//INSERT INTO user ( name, age) VALUES ( '史蒂夫', 32 )
//自動插入的記錄ID按照自增原則，生成的是8，并不是3

3.3. 删除

3.3.1. deleteById 

//DELETE FROM user WHERE id=1
userMapper.deleteById(1);

3.3.2. deleteBatchIds 

//DELETE FROM user WHERE id IN ( 1 , 2, 3 )
userMapper.deleteBatchIds(Arrays.asList(1,2,3));

3.3.3. delete 

QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("age", 14);
//DELETE FROM user WHERE (age = 14)
userMapper.delete(wrapper);

3.3.4. deleteByMap 

Map<String, Object> map = new HashMap<>();
map.put("name", "張三");
//DELETE FROM user WHERE name = '張三'
userMapper.deleteByMap(map);

3.4. 修改

3.4.1. updateById 

User user = userMapper.selectById(6);
user.setName("内馬爾休閑鞋");
//UPDATE user SET age=22, name='内馬爾休閑鞋' WHERE id=6
userMapper.updateById(user);

3.4.2. update

更新大量字段使用實體

User user = new User();
user.setAge(30);
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("name", "張三");
//UPDATE user SET age=30 WHERE (name = '張三')
userMapper.update(user, wrapper);
           
User whereUser = new User();
whereUser.setEmail("[email protected]");

UpdateWrapper<User> updateWrapper = new UpdateWrapper<>(whereUser);
updateWrapper.eq("name", "李藝偉").eq("age", 30);

User user = new User();
user.setAge(32);
user.setEmail("[email protected]");

int rows = userMapper.update(user, updateWrapper);
//UPDATE user SET age=?, email=? WHERE email=? AND (name = ? AND age = ?)
System.out.println("修改記錄數：" + rows);

更新少量字段，不用使用實體，直接使用 set

UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
updateWrapper.eq("name", "李藝偉").eq("age", 32).set("age", 50);

int rows = userMapper.update(null, updateWrapper);
//UPDATE user SET age=? WHERE (name = ? AND age = ?)
System.out.println("修改記錄數：" + rows);

Lambda 可防止誤寫字段

LambdaUpdateWrapper<User> updateWrapper = new LambdaUpdateWrapper<>();
updateWrapper.eq(User::getName, "李藝偉").eq(User::getAge, 50).set(User::getAge, 51);

int rows = userMapper.update(null, updateWrapper);
//UPDATE user SET age=? WHERE (name = ? AND age = ?)
System.out.println("修改記錄數：" + rows);

Lambda可防止誤寫字段，鍊式調用

boolean update = new LambdaUpdateChainWrapper<User>(userMapper)
	.eq(User::getName, "李藝偉").eq(User::getAge, 50)
	.set(User::getAge, 51).update();
//UPDATE user SET age=? WHERE (name = ? AND age = ?)
System.out.println("是否修改成功：" + update);

4. Lambda 條件構造器

Lambda 條件構造器，防止寫錯列名。

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
//LambdaQueryWrapper<User> lambdaQueryWrapper1 = new QueryWrapper<User>().lambda();
//LambdaQueryWrapper<User> lambdaQueryWrapper2 = Wrappers.lambdaQuery();
lambdaQueryWrapper.like(User::getName, "雨").lt(User::getAge, 40);
//WHERE (name LIKE '%雨%' AND age < 40)
List<User> userList = userMapper.selectList(lambdaQueryWrapper);
userList.forEach(System.out::println);
           
LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.likeRight(User::getName, "王").and(
        lwq -> lwq.lt(User::getAge, 40).or().isNotNull(User::getEmail)
);
//WHERE (name LIKE '王' AND (age < 40 OR email IS NOT NULL))
List<User> userList = userMapper.selectList(lambdaQueryWrapper);
userList.forEach(System.out::println);

鍊式調用

//WHERE (name LIKE '%雨%' AND age >= 20)
List<User> userList = new LambdaQueryChainWrapper<>(userMapper)
	.like(User::getName, "雨").ge(User::getAge, 20).list();
           
//SELECT id,name,age,email,manager_id,create_time FROM user WHERE (name = ?)
User user = new LambdaQueryChainWrapper<>(userMapper).eq(User::getName, "張三").one();

5. 自定義方法 SQL

5.1. 簡單自定義 

package com.example.mybatisplus.entity;

import lombok.Data;

@Data
public class ProductVO {
    private Integer category;
    private Integer count;
    private String description;
    private Integer userId;
    private String userName;
}
           
package com.example.mybatisplus.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.southwind.mybatisplus.entity.ProductVO;
import com.southwind.mybatisplus.entity.User;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface UserMapper extends BaseMapper<User> {
    @Select("select p.*, u.name as userName from product p, user u where p.user_id = u.id and u.id = #{id}")
    List<ProductVO> productList(Integer id);
}
           
List<ProductVO> productVOList = userMapper.productList(7);
productVOList.forEach(System.out::println);

5.2. 複雜自定義 

public interface UserMapper extends BaseMapper<User> {
    //自定義方法，可以直接使用注解，也可以xml裡配置，都是可以實作的
    //@Select("select * from user ${ew.customSqlSegment}")
    List<User> selectCustom(@Param(Constants.WRAPPER) Wrapper<User> wrapper);
}

xml 中配置 SQL：

<select id="selectCustom" resultType="com.example.demo.entity.User">
    select * from user ${ew.customSqlSegment}
</select>
           
LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.likeRight(User::getName, "王").and(
        lwq -> lwq.lt(User::getAge, 40).or().isNotNull(User::getEmail)
);
//select * from user WHERE (name LIKE '王%' AND (age < 40 OR email IS NOT NULL))
List<User> userList = userMapper.selectCustom(lambdaQueryWrapper);
userList.forEach(System.out::println);

5.3. 自定義分頁 

public interface UserMapper extends BaseMapper<User> {
    //自定義分頁
    IPage<User> selectUserPage(Page<User> page, @Param(Constants.WRAPPER) Wrapper<User> wrapper);

    //自定義分頁
    IPage<User> selectUserPage1(Page<User> page, @Param("user") User user);
}

xml 中配置 SQL：

<select id="selectUserPage" resultType="com.example.demo.entity.User">
    select * FROM user ${ew.customSqlSegment}
</select>

<select id="selectUserPage1" resultType="com.example.demo.entity.User">
    select * FROM user
    <where>
        <if test="user.id != null">
            and id &gt; #{user.id}
        </if>
        <if test="user.name != null">
            and name = #{user.name}
        </if>
    </where>
</select>
           
@Test
public void selectPageCustom() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.ge("age", 26);

    Page<User> page = new Page<>(1, 2);
    IPage<User> iPage = userMapper.selectUserPage(page, queryWrapper);
    //SELECT COUNT(1) FROM user WHERE (age >= 26)
    //select * FROM user WHERE (age >= 26) LIMIT 2
    System.out.println("總頁數："+ iPage.getPages());
    System.out.println("總記錄數：" + iPage.getTotal());
    List<User> userList = iPage.getRecords();
    userList.forEach(System.out::println);
}

@Test
public void selectPageCustom1() {
    User user = new User();
    user.setId(2L);
    //user.setName("王伯當");

    Page<User> page = new Page<>(3, 2);
    IPage<User> iPage = userMapper.selectUserPage1(page, user);
    //SELECT COUNT(1) FROM user WHERE (age >= 26)
    //select * FROM user WHERE (age >= 26) LIMIT 2
    System.out.println("總頁數："+ iPage.getPages());
    System.out.println("總記錄數：" + iPage.getTotal());
    List<User> userList = iPage.getRecords();
    userList.forEach(System.out::println);
}

6. service 方法調用

和直接調用 mapper 的方法有略微的差别。

package com.example.demo.mp;

import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.example.demo.entity.User;
import com.example.demo.service.UserService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.Arrays;
import java.util.List;

/**
 * @auther wangbo
 * @date 2020-11-03 16:50
 */
@SpringBootTest
public class ServiceTest {
    @Autowired
    private UserService userService;

	//隻查詢一個
    @Test
    public void select(){
        User one = userService.getOne(Wrappers.<User>lambdaQuery().gt(User::getAge, 25),false);
        //SELECT * FROM user WHERE age > 25
        System.out.println(one);
    }

	//批量插入
    @Test
    public void batch(){
        User user1 = new User();
        user1.setName("張三1");
        user1.setAge(28);

        User user2 = new User();
        user2.setName("張三2");
        user2.setAge(29);

        List<User> list = Arrays.asList(user1, user2);
        boolean savebatch = userService.saveBatch(list);
        System.out.println("批量插入是否成功："+ savebatch);
    }


    //鍊式操作
    //查詢
    @Test
    public void chain(){
        List<User> list = userService.lambdaQuery().gt(User::getAge, 25).like(User::getName, "雨").list();
        list.forEach(System.out::println);
    }
    
    //更新
    @Test
    public void chain1(){
        boolean update = userService.lambdaUpdate().gt(User::getAge, 25).like(User::getName, "雨").set(User::getAge, 20).update();
        System.out.println("更新是否成功："+ update);
        //UPDATE user SET age=? WHERE (age > ? AND name LIKE ?)
    }
    //删除
    @Test
    public void chain2(){
        boolean update = userService.lambdaUpdate().gt(User::getAge, 25).like(User::getName, "雨").remove();
        System.out.println("删除是否成功："+ update);
        //DELETE FROM user WHERE (age > ? AND name LIKE ?)
    }
}

7. MyBatis-Plus 自動生成

根據資料庫表自動生成實體類、Mapper、Service、ServiceImpl、Controller。

1、pom.xml 導入 MyBatis Plus Generator 依賴和模闆依賴。

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-generator</artifactId>
    <version>3.4.1</version>
</dependency>
<dependency>
    <groupId>org.apache.velocity</groupId>
    <artifactId>velocity-engine-core</artifactId>
    <version>2.3</version>
</dependency>

三種模闆：Velocity（預設）、Freemarker、Beetl。

2、啟動類，配置相關項後直接執行 main 方法即可生成對應的檔案。

package com.example.mybatisplus;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.generator.AutoGenerator;
import com.baomidou.mybatisplus.generator.config.DataSourceConfig;
import com.baomidou.mybatisplus.generator.config.GlobalConfig;
import com.baomidou.mybatisplus.generator.config.PackageConfig;
import com.baomidou.mybatisplus.generator.config.StrategyConfig;
import com.baomidou.mybatisplus.generator.config.rules.NamingStrategy;

public class Main {
    public static void main(String[] args) {
        //建立 Generator對象
        AutoGenerator autoGenerator = new AutoGenerator();
        //資料源
        DataSourceConfig dataSourceConfig = new DataSourceConfig();
        dataSourceConfig.setDbType(DbType.MYSQL);
        dataSourceConfig.setUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8");
        dataSourceConfig.setUsername("root");
        dataSourceConfig.setPassword("123456789");
        dataSourceConfig.setDriverName("com.mysql.cj.jdbc.Driver");
        autoGenerator.setDataSource(dataSourceConfig);
        //全局配置
        GlobalConfig globalConfig = new GlobalConfig();
        globalConfig.setOutputDir(System.getProperty("user.dir")+"/src/main/java");
        globalConfig.setOpen(false);
        globalConfig.setAuthor("wangbo");//設定作者
        globalConfig.setServiceName("%sService");//指定Service名字，不指定前面會有I
        autoGenerator.setGlobalConfig(globalConfig);
        //包資訊
        PackageConfig packageConfig = new PackageConfig();
        packageConfig.setParent("com.example.mybatisplus");
        packageConfig.setModuleName("generator");//生成的檔案所在的包目錄，直接在父包com.example.mybatisplus下就不用寫了
        packageConfig.setController("controller");
        packageConfig.setService("service");
        packageConfig.setServiceImpl("service.impl");
        packageConfig.setMapper("mapper");
        packageConfig.setEntity("entity");
        autoGenerator.setPackageInfo(packageConfig);
        //配置政策
        StrategyConfig strategyConfig = new StrategyConfig();
        strategyConfig.setInclude("product");//這裡配置需要生成的目标表名，不配置則預設生成全部。
        strategyConfig.setEntityLombokModel(true);
        strategyConfig.setRestControllerStyle(true);
        strategyConfig.setNaming(NamingStrategy.underline_to_camel);//下劃線轉駝峰
        strategyConfig.setColumnNaming(NamingStrategy.underline_to_camel);//下劃線轉駝峰
        autoGenerator.setStrategy(strategyConfig);

        autoGenerator.execute();
    }
}

8. 其他配置 

# 配置mybatis-plus自定義SQL的xml檔案位置
mybatis-plus.mapper-locations=classpath:com/example/demo/mapper/*.xml
# 配置mybatis-plus的全局主鍵政策，這裡設定為資料庫自增。
# 實體類上的局部配置優先級高于這裡的全局配置。
mybatis-plus.global-config.db-config.id-type=auto
# 在自定義SQL的xml檔案中resultType直接寫實體的簡寫，可省略包名
mybatis-plus.type-aliases-package=com.example.demo.entity

9. 注意事項

在 XML 中使用自定義 SQL，注意要在配置檔案中配置 xml 檔案的位置：

mybatis-plus:
  mapper-locations: classpath:com/hny/pspp/**/mapper/xml/*.xml

否則在調用自定義SQL的時候會出現以下錯誤：

org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.hny.pspp.album.mapper.CloudAlbumMapper.pageUserIdList

這樣配置的話正常啟動項目調用自定義SQL沒有問題了，但是在使用單元測試調用自定義SQL的時候還是會出現上述異常：

package com.hny.pspp.album.scheduled;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class ClearTaskTest {

    @Autowired
    private ClearTask clearTask;
    
    @Test
    void clear() {
        clearTask.clear();
    }
}

實測可以通過補全

locations

路徑解決問題：

mybatis-plus:
  mapper-locations: classpath:com/hny/pspp/album/mapper/xml/*.xml
mybatis MyBatisPlus
上一篇: Python-OpenCV-基于GrabCut算法的互動式可選區域前景提取
下一篇: opencv學習筆記五十八：grabCut摳圖

繼續閱讀