springboot：整合mybatis-plus

springboot：整合mybatis-plus

一、项目搭建

创建数据库

CREATE TABLE `company` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(255) DEFAULT NULL COMMENT '名称',
  `contact` varchar(50) DEFAULT NULL COMMENT '联系人',
  `contactType` varchar(50) DEFAULT NULL COMMENT '联系方式',
  `createTime` bigint(20) DEFAULT NULL COMMENT '创建时间',
  `updateTime` bigint(20) DEFAULT NULL COMMENT '修改时间',
  `removed` int(2) DEFAULT NULL COMMENT '是否删除(0:存在，-1:删除)',
  `deleteTime` bigint(20) DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT='公司单位';
1
2
3
4
5
6
7
8
9
10
11

导入依赖

        <dependency>
            <groupId>com.baomidougroupId>
            <artifactId>mybatis-plus-boot-starterartifactId>
            <version>3.4.1version>
        dependency>
1
2
3
4
5

注意：这里不要同事导入mybatis和mybatis-plus的依赖，会出现依赖冲突，本人在上面踩雷，排插错误很久

填写配置

package com.mye.cloudboxdcim.configuration;

import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.core.config.GlobalConfig;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.mye.cloudboxdcim.common.GlobalConstant;
import com.mye.cloudboxdcim.framework.engine.mybatisplus.MyMetaObjectHandler;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.util.StringUtils;
import javax.sql.DataSource;

/**
 * @ClassName MysqlConfiguration
 * @Description mysql配置类
 * @Author hl
 * @Date 2022/10/31 15:50
 * @Version 1.0
 */
@Configuration
@EnableTransactionManagement
@MapperScan(basePackages = {"com.mye.cloudboxdcim.framework.api.mapper"}, sqlSessionFactoryRef = "mybatisSqlSession")
public class MysqlConfiguration {

    @Bean(name = "dataSource")
    @Primary
    public DataSource dataSource() throws Exception {
        String mysqlHost = System.getenv(GlobalConstant.MYSQL_HOST);
        mysqlHost = "61.184.241.171";
        if (StringUtils.isEmpty(mysqlHost)) {
            throw new Exception("env MYSQL_HOST do not set!");
        }
        String mysqlPort = System.getenv(GlobalConstant.MYSQL_PORT);
        if (StringUtils.isEmpty(mysqlPort)) {
            mysqlPort = "3306";
        }
        String mysqlUsername = System.getenv(GlobalConstant.MYSQL_USERNAME);
        if (StringUtils.isEmpty(mysqlUsername)) {
            mysqlUsername = "username";
        }
        String mysqlPassword = System.getenv(GlobalConstant.MYSQL_PASSWORD);
        if (StringUtils.isEmpty(mysqlPassword)) {
            mysqlPassword = "password";
        }
        String mysqlDB = System.getenv(GlobalConstant.MYSQL_DB);
        if (StringUtils.isEmpty(mysqlDB)) {
            mysqlDB = "dbname";
        }

        mysqlHost = "172.100.20.23";
        mysqlPort = "3306";
        mysqlUsername = "root";
        mysqlPassword = "root";
        mysqlDB = "cloud_box_dcim";

        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl("jdbc:mysql://" + mysqlHost + ":" + mysqlPort + "/" + mysqlDB
                + "?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&allowMultiQueries=true");
        dataSource.setPassword(mysqlPassword);
        dataSource.setUsername(mysqlUsername);
        dataSource.setMaxActive(100);
        dataSource.setMaxWait(60000);
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setMinEvictableIdleTimeMillis(300000);
        dataSource.setTimeBetweenEvictionRunsMillis(60000);
        dataSource.setInitialSize(5);
        dataSource.setMinIdle(1);
        return dataSource;
    }

    @Bean(name = "transactionManager")
    @Primary
    public DataSourceTransactionManager transactionManager() throws Exception {
        return new DataSourceTransactionManager(dataSource());
    }


    @Bean("mybatisSqlSession")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource")DataSource dataSource,@Qualifier("mybatisPlusInterceptor")MybatisPlusInterceptor mybatisPlusInterceptor) throws Exception {
        MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
        /* 数据源 */
        sqlSessionFactory.setDataSource(dataSource);
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setJdbcTypeForNull(JdbcType.NULL);
        /* 驼峰转下划线 */
        configuration.setMapUnderscoreToCamelCase(false);
        //日志打印
//        configuration.setLogImpl(org.apache.ibatis.logging.stdout.StdOutImpl.class);

        /* 分页插件 */
        configuration.addInterceptor(mybatisPlusInterceptor);
        sqlSessionFactory.setConfiguration(configuration);

        /* 全局设置 */
        GlobalConfig globalConfig = new GlobalConfig();
        globalConfig.setBanner(false);
        //配置自动填充功能
        globalConfig.setMetaObjectHandler(new MyMetaObjectHandler());
        GlobalConfig.DbConfig dbConfig = new GlobalConfig.DbConfig();
        /* id自增 */
        dbConfig.setIdType(IdType.AUTO);
        //全局逻辑删除的实体字段名(since 3.3.0,配置后可以不加步骤2的注解)
        dbConfig.setLogicDeleteField("removed");
        //逻辑已删除值(默认为 -1)
        dbConfig.setLogicDeleteValue("-1");
        //逻辑未删除值(默认为 0)
        dbConfig.setLogicNotDeleteValue("0");
        globalConfig.setDbConfig(dbConfig);



        sqlSessionFactory.setGlobalConfig(globalConfig);

        return sqlSessionFactory.getObject();
    }

    /**
     * 3.4.0之后提供的拦截器的配置方式
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return mybatisPlusInterceptor;
    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144

相关类的创建

实体类

@TableName("company")
public class Company {
    private Integer id;
    private String name;
    private String contact;
    private String contactType;
    private Long createTime;
    private Long updateTime;
    private Long deleteTime;
    private int removed;
    //省略get、set方法
}
1
2
3
4
5
6
7
8
9
10
11
12

mapper：这里要使用mybatis-plus，需要在mapper继承BaseMapper

@Repository
@Mapper
public interface CompanyMapper extends BaseMapper<Company> {
    
}
1
2
3
4
5

二、测试CURD

新增

int insert(T entity);
1

@SpringBootTest(classes = CloudBoxDcimApplication.class)
@RunWith(SpringRunner.class)
public class MybatisPlusTest {

    @Autowired
    private CompanyMapper companyMapper;

    @Test
    public void insertTest(){
        Company company = new Company();
        company.setName("阿里");
        company.setContact("张三");
        company.setContactType("17683723698");
        company.setCreateTime(System.currentTimeMillis());
        company.setRemoved(0);
        companyMapper.insert(company);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xLToAcDp-

更新

    int updateById(@Param("et") T entity);

    int update(@Param("et") T entity, @Param("ew") Wrapper<T> updateWrapper);
1
2
3

根据id进行更新

    @Test
    public void updateTest1(){
        Company company = new Company();
        company.setId(6);
        company.setName("阿里");
        company.setContact("张三111");
        company.setContactType("17683723698");
        company.setCreateTime(System.currentTimeMillis());
        company.setRemoved(0);
        companyMapper.updateById(company);
    }
1
2
3
4
5
6
7
8
9
10
11

根据条件更新update

第一种

    @Test
    public void updateTest2(){
        //第一种
        Company company = new Company();
        company.setContact("张三222"); //需要更新的字段
        //queryWrapper对象，用于设置条件
        QueryWrapper<Company> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("id",6);//设置查询条件
        companyMapper.update(company,queryWrapper);
    }
1
2
3
4
5
6
7
8
9
10

第二种：推荐

    @Test
    public void updateTest3(){
        //UpdateWrapper更新操作
        UpdateWrapper<Company> warp = new UpdateWrapper<>();
        //通过set设置需要修改的内容，eq设置条件
        warp.set("name","阿里111").set("contact","zhansgan3333").eq("id",6);
        companyMapper.update(null,warp);
    }
1
2
3
4
5
6
7
8

查询

    T selectById(Serializable id);

    List<T> selectBatchIds(@Param("coll") Collection<? extends Serializable> idList);

    List<T> selectByMap(@Param("cm") Map<String, Object> columnMap);

    T selectOne(@Param("ew") Wrapper<T> queryWrapper);

    Integer selectCount(@Param("ew") Wrapper<T> queryWrapper);

    List<T> selectList(@Param("ew") Wrapper<T> queryWrapper);
1
2
3
4
5
6
7
8
9
10
11

 @Test
    public void selectTest(){
        //根据id查询
        Company company = companyMapper.selectById(6);
        System.out.println(company);
        
        //根据id集合查询
        List<Company> companyList = companyMapper.selectBatchIds(ListUtil.of(6, 5, 4));
        System.out.println(companyList);
        
        //根据条件查询一个
        QueryWrapper<Company> query = new QueryWrapper<>();
        query.eq("name","华为");
        Company company1 = companyMapper.selectOne(query);
        System.out.println(company1);
        
        //根据map查询
        Map<String, Object> map = new HashMap<>();
        map.put("contact","张三");
        List<Company> companyList1 = companyMapper.selectByMap(map);
        System.out.println(companyList1);
        
        //根据条件查询个数
        QueryWrapper<Company> query1 = new QueryWrapper<>();
        query1.eq("contact","张三");
        Integer integer = companyMapper.selectCount(query1);
        System.out.println(integer);
        //根据条件查询多个
        List<Company> companyList2 = companyMapper.selectList(query1);
        System.out.println(companyList2);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

分页查询

> E selectPage(E page, @Param("ew") Wrapper queryWrapper);
1

不带条件的分页查询

带条件的分页查询

    @Test
    public void pageTest2(){
        IPage<Company> page = new Page<>(1, 2);

        QueryWrapper<Company> query = new QueryWrapper<>();
        query.eq("name","华为");

        IPage<Company> companyIPage = companyMapper.selectPage(page, query);
        System.out.println(companyIPage);
    }
1
2
3
4
5
6
7
8
9
10

删除

    int deleteById(Serializable id);

    int deleteByMap(@Param("cm") Map<String, Object> columnMap);

    int delete(@Param("ew") Wrapper<T> queryWrapper);

    int deleteBatchIds(@Param("coll") Collection<? extends Serializable> idList);
1
2
3
4
5
6
7

    @Test
    public void deleteTest2() {
        companyMapper.deleteById(6);

        Map<String, Object> map = new HashMap<>();
        map.put("contact", "李四");
        companyMapper.deleteByMap(map);

        QueryWrapper<Company> query = new QueryWrapper<>();
        query.eq("name", "迈异2");
        companyMapper.delete(query);

        companyMapper.deleteBatchIds(ListUtil.of(4));
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14

三、逻辑删除配置

 @Test
    public void deleteTest2() {
        companyMapper.deleteById(2);
    }
1
2
3
4

这里可以看到已经逻辑删除成功

四、自动填充配置

自定义填充类

package com.mye.cloudboxdcim.framework.engine.mybatisplus;

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

@Slf4j
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {

    @Override
    public void insertFill(MetaObject metaObject) {
        this.strictInsertFill(metaObject, "createTime", Long.class, System.currentTimeMillis()); // 起始版本 3.3.0(推荐使用)
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        this.strictUpdateFill(metaObject, "updateTime",Long.class, System.currentTimeMillis()); // 起始版本 3.3.0(推荐)
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

配置自定义填充

在实体类上添加注释

public class Company {

    /**
     * 创建时间
     */
    @TableField(fill = FieldFill.INSERT)
    private Long createTime;
    /**
     * 修改时间
     */
    @TableField(fill = FieldFill.UPDATE)
    private Long updateTime;
}
1
2
3
4
5
6
7
8
9
10
11
12
13

注意

• 填充原理是直接给entity的属性设置值
• 注解则是指定该属性在对应情况下必有值,如果无值则入库会是null
• MetaObjectHandler提供的默认方法的策略均为:如果属性有值则不覆盖,如果填充值为null则不填充
• 字段必须声明TableField注解,属性fill选择对应策略,该声明告知Mybatis-Plus需要预留注入SQL字段
• 填充处理器MyMetaObjectHandler在 Spring Boot 中需要声明@Component或@Bean注入
• update(T t,Wrapper updateWrapper)时t不能为空,否则自动填充失效

五、主键配置

自 3.3.0 开始,默认使用雪花算法+UUID(不含中划线)

字段上使用注释

在每一个实体类的id上添加注释

    @TableId(value = "id",type = IdType.AUTO)
    private Integer id;
1
2

配置全局主键自增

        GlobalConfig.DbConfig dbConfig = new GlobalConfig.DbConfig();
        /* id自增 */
        dbConfig.setIdType(IdType.AUTO);
1
2
3

六、注解

@TableName

表名注解，标识实体类对应的表

@TableName("sys_user")
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String email;
}
1
2
3
4
5
6
7

@TableId

主键注解，实体类主键字段

value 是主键字段名，type 是指定主键类型，默认值为 IdType.NONE

    @TableId(value = "id",type = IdType.AUTO)
    private Integer id;
1
2

@TableField

字段注解（非主键）

    // 指定数据库字段名
	@TableField(value = "name")
    private String name;

	// 指定自动填充类型
    @TableField(fill = FieldFill.INSERT)
    private Long createTime;
1
2
3
4
5
6
7