MyBatis-Flex（CRUD入门教程）

彩虹、

已于 2024-05-11 14:41:31 修改

阅读量1k

点赞数 2

分类专栏：数据库文章标签： mybatis java

于 2023-11-13 16:45:33 首次发布

本文链接：https://blog.csdn.net/qq_56996565/article/details/134379364

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MyBatis-Flex 是一个优雅的 MyBatis 增强框架，它非常轻量、同时拥有极高的性能与灵活性。

轻量：除了 MyBatis，没有任何第三方依赖。
灵活：内置的 QueryWrapper 可以轻易的实现多表查询、链接查询、子查询。
强大：支持多（复合）主键、逻辑删除、乐观锁配置、数据脱敏、数据审计、数据填充等等功能。

依赖

<dependency>
    <groupId>com.mybatis-flex</groupId>
    <artifactId>mybatis-flex-spring-boot-starter</artifactId>
    <version>1.7.3</version>
</dependency>
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
</dependency>
<!-- 代码生成器 -->
<dependency>
    <groupId>com.mybatis-flex</groupId>
    <artifactId>mybatis-flex-codegen</artifactId>
    <version>1.7.3</version>
</dependency>

配置文件

application.yml

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/flex_demo?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
    username: root
    password: 111111

代码生成器

运行main方法即可生成实体类，对应的Tables(QueryWrapper使用字段什么的时候用)，mapper，xml映射文件。

package com.example.demo.generator;

import com.mybatisflex.codegen.Generator;
import com.mybatisflex.codegen.config.ColumnConfig;
import com.mybatisflex.codegen.config.GlobalConfig;
import com.mybatisflex.codegen.config.TableDefConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @author ***
 * @date 2023/11/09 0009 16:58
 */
public class Codegen {
    public static void main(String[] args) {
        //配置数据源
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/flex_demo?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC");
        dataSource.setUsername("root");
        dataSource.setPassword("111111");

        //创建配置内容
        GlobalConfig globalConfig = createGlobalConfigUseStyle1();
        //通过 datasource 和 globalConfig 创建代码生成器
        Generator generator = new Generator(dataSource, globalConfig);
        //生成代码
        generator.generate();
    }

    public static GlobalConfig createGlobalConfigUseStyle1() {
        //创建配置内容
        GlobalConfig globalConfig = new GlobalConfig();
        //设置根包
        globalConfig.setBasePackage("com.example.demo");
        // 类doc注释
        globalConfig.getJavadocConfig()
                .setAuthor("***")
                .setSince(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));

        // TableDef
        globalConfig.enableTableDef();
        // 覆盖旧文件
        globalConfig.setTableDefOverwriteEnable(true);
        globalConfig.getTableDefConfig()
                // 小写风格
                // .setPropertiesNameStyle(TableDefConfig.NameStyle.LOWER_CAMEL_CASE)
                .setClassSuffix("Def");

        //指定哪个表，不指定会执行所有的表
        globalConfig.setGenerateTable("account");

        //entity 并启用 Lombok
        globalConfig.setEntityGenerateEnable(true);
        globalConfig.setEntityOverwriteEnable(true);
        globalConfig.setEntityWithLombok(true);

        // mapper
        globalConfig.setMapperGenerateEnable(true);
        // globalConfig.setMapperOverwriteEnable(true);
        // 添加@Mapper注解
        globalConfig.setMapperAnnotation(true);

        //可以单独配置某个列
        // ColumnConfig columnConfig = new ColumnConfig();
        // columnConfig.setColumnName("money");
        // // 乐观锁字段
        // columnConfig.setVersion(true);
        // // 类型
        // columnConfig.setPropertyType("java.math.BigDecimal");
        // globalConfig.setColumnConfig("account", columnConfig);

        // xml
        globalConfig.enableMapperXml();
        // globalConfig.setMapperXmlOverwriteEnable(true);
        return globalConfig;
    }
}

生成的AccountDef文件如下

package com.example.demo.entity.table;

import com.mybatisflex.core.query.QueryColumn;
import com.mybatisflex.core.table.TableDef;

/**
 * 账户表 表定义层。
 *
 * @author ***
 * @since 2023-11-10 14:45:39
 */
public class AccountDef extends TableDef {

    /**
     * 账户表
     */
    public static final AccountDef ACCOUNT = new AccountDef();

    public final QueryColumn ID = new QueryColumn(this, "id");

    /**
     * 年龄
     */
    public final QueryColumn AGE = new QueryColumn(this, "age");

    public final QueryColumn MONEY = new QueryColumn(this, "money");

    /**
     * 生日
     */
    public final QueryColumn BIRTHDAY = new QueryColumn(this, "birthday");

    /**
     * 用户名
     */
    public final QueryColumn USER_NAME = new QueryColumn(this, "user_name");

    /**
     * 所有字段。
     */
    public final QueryColumn ALL_COLUMNS = new QueryColumn(this, "*");

    /**
     * 默认字段，不包含逻辑删除或者 large 等字段。
     */
    public final QueryColumn[] DEFAULT_COLUMNS = new QueryColumn[]{ID, USER_NAME, AGE, BIRTHDAY, MONEY};

    public AccountDef() {
        super("", "account");
    }
}

SpringBoot启动类添加注解

@MapperScan("com.example.demo.mapper")

配置sql执行日志打印

package com.example.demo.config;

import com.mybatisflex.core.audit.AuditManager;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Configuration;

/**
 * 配置sql打印
 *
 * @author ***
 * @date 2023/11/09 0009 17:35
 */
@Configuration
public class MyBatisFlexConfiguration {
    private static final Logger logger = LoggerFactory.getLogger("mybatis-flex-sql");

    public MyBatisFlexConfiguration() {
        //开启审计功能
        AuditManager.setAuditEnable(true);
        //设置 SQL 审计收集器
        AuditManager.setMessageCollector(
                auditMessage -> logger.info(">>>>>>SQL：{};  {}ms", auditMessage.getFullSql().toLowerCase(), auditMessage.getElapsedTime())
        );
    }
}

添加操作

单个添加

Account account = new Account();
account.setUserName("michael");
Account newAccount = UpdateWrapper.of(account)
        .setRaw(ACCOUNT.BIRTHDAY, "now()")
        .setRaw(ACCOUNT.MONEY, new BigDecimal("10000"))
        .toEntity();
accountMapper.insert(newAccount);

批量添加

Account account = new Account();
account.setUserName("王五");
account.setAge(22);
account.setMoney(new BigDecimal("1000"));
Account account1 = new Account();
account1.setUserName("赵六");
account1.setAge(22);
account1.setMoney(new BigDecimal("1000"));
Account account2 = new Account();
account2.setUserName("jack");
account2.setAge(22);
account2.setMoney(new BigDecimal("1000"));
List<Account> accountList = List.of(account, account1, account2);
// 每次插入2条，假设100条则执行50次sql的插入操作，适用于数据列大的情况，防止sql拼接过长
// >>>>>>SQL：insert into `account`(`user_name`, `age`, `birthday`, `money`) values ('王五', 22, null, 1000), ('赵六', 22, null, 1000);  10ms
// >>>>>>SQL：insert into `account`(`user_name`, `age`, `birthday`, `money`) values ('jack', 22, null, 1000);  3ms
accountMapper.insertBatch(accountList, 2);

更新操作

指定主键id update

Account account = UpdateEntity.of(Account.class, 6);
//Account account = UpdateEntity.of(Account.class);
//account.setId(100);
account.setUserName("小张");
account.setAge(30);
accountMapper.update(account);
// update `account` set `user_name` = '小张' , `age` = 30  where `id` = 6 ;

根据QueryWrapper进行update

Account account = UpdateEntity.of(Account.class, 6);
account.setMoney(new BigDecimal("200"));
UpdateWrapper<Account> updateWrapper = UpdateWrapper.of(account);
updateWrapper.setRaw(ACCOUNT.AGE, "age + 1");
accountMapper.update(account);
// update `account` set `money` = 200 , `age` = age + 1 where `id` = 6 ;


Account account = UpdateEntity.of(Account.class, 6);
UpdateWrapper<Account> updateWrapper = UpdateWrapper.of(account);
updateWrapper.setRaw(ACCOUNT.AGE, ACCOUNT.AGE.add(1));
accountMapper.update(account);
// update `account` set `age` = `age` + 1 where `id` = 6 ;

UpdateChain 是一个对 UpdateEntity、UpdateWrapper 等进行封装的一个工具类，方便用户用于进行链式操作。

set() 方法用于设置参数数据 UPDATE `account` SET `user_name` = ? WHERE `id` = 1
setRaw() 用于设置 SQL 拼接数据 UPDATE `account` SET `user_name` = 张三 WHERE `id` = 1

UpdateChain.of(Account.class)
        .set(Account::getUserName, "test")
        .setRaw(Account::getAge, "age + 1")
        .where(Account::getId).eq(6)
        .update();
// update `account` set `user_name` = 'test' , `age` = age + 1 where `id` = 6;

执行函数，名字转大写

UpdateChain.of(Account.class)
        .setRaw(Account::getUserName, "UPPER(user_name)")
        .where(Account::getId).eq(6)
        .update();
//update account set user_name = upper(user_name) where id = 6;

删除操作

// 方式一:
// accountMapper.deleteById(6);
// 方式二:
QueryWrapper queryWrapper = QueryWrapper.create();
queryWrapper.where(ACCOUNT.ID.eq(6));
// 此查询到多条则批量删除
accountMapper.deleteByQuery(queryWrapper);
// 方式三:
// accountMapper.deleteByCondition(ACCOUNT.ID.eq(6));
// delete from `account` where `id` = 6;
// 根据id集合批量删除
List<Integer> integers = List.of(6, 7);
int i = accountMapper.deleteBatchByIds(integers);
// delete from `account` where `id` = 6  or `id` = 7 ;
System.out.println(i);//2

查询操作

基本查询

// 查询所有
List<Account> accounts = accountMapper.selectAll();

// 根据id查询一个
Account account1 = accountMapper.selectOneById(1);

// 查询id为1的一条记录，存在多条会报错
QueryWrapper queryWrapper = QueryWrapper.create()
        .select()
        .where(ACCOUNT.ID.eq(1));
Account account = accountMapper.selectOneByQuery(queryWrapper);

// 查询所有记录总数量
QueryWrapper queryWrapper1 = QueryWrapper.create();
long l = accountMapper.selectCountByQuery(queryWrapper1);
System.out.println(l);

模糊查询

// id大于3,用户名模糊查询
QueryWrapper queryWrapper = QueryWrapper.create()
        .select()
        .from(ACCOUNT)
        .where(ACCOUNT.ID.ge(3))
        // like全模糊，likeLef右百分号，likeRight左百分号
        .and(ACCOUNT.USER_NAME.like("micha"));
List<Account> accounts = accountMapper.selectListByQuery(queryWrapper);
// select `id`, `user_name`, `age`, `birthday`, `money` from `account` where `id` >= 3 and `user_name` like '%micha%';

where条件查询

boolean flag = false;
QueryWrapper queryWrapper = QueryWrapper.create()
        .select().from(ACCOUNT)
        // 为true时where条件查询，为false时不需要where
        .where(flag ? ACCOUNT.ID.ge(100) : noCondition());
List<Account> accounts = accountMapper.selectListByQuery(queryWrapper);
// select `id`, `user_name`, `age`, `birthday`, `money` from `account`;

QueryWrapper queryWrapper1 = QueryWrapper.create()
        .select().from(ACCOUNT)
        // false表示不拼接where条件查询，等同于 .where(ACCOUNT.ID.ge(100).when(false))
        .where(ACCOUNT.ID.ge(100, false));
List<Account> accounts1 = accountMapper.selectListByQuery(queryWrapper1);

String name = "123";
QueryWrapper queryWrapper2 = QueryWrapper.create()
        .select().from(ACCOUNT)
        .where(ACCOUNT.ID.ge(100))
        // 当name为null时不拼接模糊查询
        .and(ACCOUNT.USER_NAME.like(name, StringUtil::isNotBlank));
List<Account> accounts2 = accountMapper.selectListByQuery(queryWrapper2);

String age = "";
BigDecimal bigDecimal = new BigDecimal("1000");
QueryWrapper queryWrapper3 = QueryWrapper.create()
        .select().from(ACCOUNT)
        // 当name为null时不拼接模糊查询
        .and(ACCOUNT.AGE.eq(age, StringUtil::isNotBlank))
        .and(ACCOUNT.MONEY.eq(bigDecimal, ObjectUtil.areNotNull(bigDecimal)));
List<Account> accounts3 = accountMapper.selectListByQuery(queryWrapper3);
// select `id`, `user_name`, `age`, `birthday`, `money` from `account` where `money` = 1000;

QueryWrapper queryWrapper4 = QueryWrapper.create();
// 指定查询哪些字段，字段重命名
queryWrapper4.select(
                ACCOUNT.ID,
                ACCOUNT.AGE,
                ACCOUNT.MONEY,
                ACCOUNT.USER_NAME.as("userName")
                // id的绝对值为 1，QueryMethods中有mysql的函数
        ).where(QueryMethods.abs(ACCOUNT.ID).eq(1))
        .from(ACCOUNT);
List<Account> accounts4 = accountMapper.selectListByQuery(queryWrapper4);

子查询

QueryWrapper queryWrapper = QueryWrapper.create()
        .select()
        .from(ACCOUNT)
        .where(
                ACCOUNT.ID.ge(
                        // 年龄等于18岁的id，需只能找到唯一的一条，否则more than 1 row
                        select(ACCOUNT.ID).from(ACCOUNT).where(ACCOUNT.AGE.eq(18))
                )
        );
List<Account> accounts = accountMapper.selectListByQuery(queryWrapper);
// select `id`, `user_name`, `age`, `birthday`, `money` from `account`
// where `id` >= (select `id` from `account` where `age` = 18);

QueryWrapper queryWrapper1 = QueryWrapper.create()
        .select()
        .from(ACCOUNT.as("a"))
        .and(
                exists(
                        select().from(ACCOUNT.as("b")).where(ACCOUNT.AGE.ge(20))
                )
        ).and(ACCOUNT.AGE.ge(20));
List<Account> accountList = accountMapper.selectListByQuery(queryWrapper1);
// select `id`, `user_name`, `age`, `birthday`, `money` from `account` as `a` 
// where exists (select * from `account` as `b` where `age` >= 20) 
// and `age` >= 20;

and (...) or (...)

QueryWrapper queryWrapper = QueryWrapper.create()
        .select()
        .from(ACCOUNT)
        // id大于1
        .where(ACCOUNT.ID.ge(1))
        // 金额为1000或者10000
        .and(ACCOUNT.MONEY.eq(1000).or(ACCOUNT.MONEY.eq(10000)))
        // 年龄为18、19、20的并且名字模糊匹配 %王% 的
        .or(ACCOUNT.AGE.in(18, 19, 20).and(ACCOUNT.USER_NAME.like("王")));
List<Account> accountList = accountMapper.selectListByQuery(queryWrapper);
// select `id`, `user_name`, `age`, `birthday`, `money`
// from `account`
// where `id` >= 1 and (`money` = 1000 or `money` = 10000)
// or (`age` in (18, 19, 20) and `user_name` like '%王%');

QueryWrapper queryWrapper1 = QueryWrapper.create()
        .select()
        .from(ACCOUNT)
        // 金额 >= 1000
        .where(ACCOUNT.MONEY.ge(1000))
        .and(
                ACCOUNT.ID.ge(2).and(
                        new Brackets(ACCOUNT.AGE.ge(18).or(ACCOUNT.USER_NAME.eq("王五")))
                )
        )
        .or(ACCOUNT.BIRTHDAY.le(new Date()));
List<Account> accountList1 = accountMapper.selectListByQuery(queryWrapper1);
// select `id`, `user_name`, `age`, `birthday`, `money`
// from `account`
// where `money` >= 1000 and (`id` >= 2 and (`age` >= 18 or `user_name` = '王五'))
// or `birthday` <= '2023-11-12 16:22:11';

group by分组查询

QueryWrapper queryWrapper = QueryWrapper.create()
        .select()
        .from(ACCOUNT)
        .groupBy(ACCOUNT.MONEY);
List<Account> accountList1 = accountMapper.selectListByQuery(queryWrapper);
// select `id`, `user_name`, `age`, `birthday`, `money` from `account` group by `money`;

having过滤查询

QueryWrapper queryWrapper = QueryWrapper.create()
        .select()
        .from(ACCOUNT)
        .groupBy(ACCOUNT.USER_NAME)
        .having(ACCOUNT.AGE.between(20, 25));
List<Account> accountList1 = accountMapper.selectListByQuery(queryWrapper);
// select `id`, `user_name`, `age`, `birthday`, `money` from `account`
// group by `user_name`
// having `age` between  20 and 25 ;

order by排序

QueryWrapper queryWrapper = QueryWrapper.create()
        .select()
        .from(ACCOUNT)
        .orderBy(ACCOUNT.AGE.asc(), ACCOUNT.USER_NAME.desc());
List<Account> accountList1 = accountMapper.selectListByQuery(queryWrapper);
//select `id`, `user_name`, `age`, `birthday`, `money` from `account` order by `age` asc, `user_name` desc;

QueryWrapper queryWrapper1 = QueryWrapper.create()
        .select()
        .from(ACCOUNT)
        // 动态条件取值：true 升序 false 降序 null 不排序。
        .orderBy(ACCOUNT.ID, true)
        .orderBy(ACCOUNT.BIRTHDAY, false)
        .orderBy(ACCOUNT.USER_NAME, null);
List<Account> accountList2 = accountMapper.selectListByQuery(queryWrapper1);
// select `id`, `user_name`, `age`, `birthday`, `money` from `account` order by `id` asc, `birthday` desc;

分页查询

QueryWrapper queryWrapper = QueryWrapper.create()
        .select()
        .from(ACCOUNT);
Page<Account> paginate = accountMapper.paginate(1, 2, queryWrapper);
// 第几页，这里是第1页
System.out.println(paginate.getPageNumber());
// 每页大小  2条
System.out.println(paginate.getPageSize());
// 总页数    3页
System.out.println(paginate.getTotalPage());
// 总记录数  5条
System.out.println(paginate.getTotalRow());
// 第一页的2条记录
List<Account> records = paginate.getRecords();

join联表查询

List<ArticleVO> articleVOList = QueryChain.of(articleMapper)
        .select(ARTICLE.ID.as("articleId"),
                ARTICLE.ACCOUNT_ID,
                ACCOUNT.USER_NAME,
                ARTICLE.TITLE,
                ARTICLE.CONTENT)
        .from(ARTICLE)
        .leftJoin(ACCOUNT).on(
                ACCOUNT.ID.eq(ARTICLE.ACCOUNT_ID)
        ).listAs(ArticleVO.class);
//  select `article`.`id` as `articleid`,
// `article`.`account_id`,
// `account`.`user_name`,
// `article`.`title`,
// `article`.`content`
//  from `article`
//  left join `account` on `account`.`id` = `article`.`account_id`;

@Data
public class ArticleVO {
    private Integer articleId;
    /**
     * 账户id
     */
    private Integer accountId;
    /**
     * 标题
     */
    private String title;
    /**
     * 内容
     */
    private String content;
    /**
     * 用户名
     */
    private String userName;
}

复杂查询

List<SysUserVo> sysUserVos = sysUserMapper.selectListByQueryAs(
        QueryWrapper.create()
                .select(SYS_USER.ALL_COLUMNS,
                        SYS_ROLE.ALL_COLUMNS)
                .from(SysUser.class)
                .leftJoin(SysUserRole.class)
                .on(SYS_USER.ID.eq(SYS_USER_ROLE.USER_ID))
                .leftJoin(SysRole.class)
                .on(SYS_USER_ROLE.ROLE_ID.eq(SYS_ROLE.ID))
                .where(SYS_USER.ID.gt(1))
                .groupBy(SYS_USER.USERNAME)
                .having(SYS_USER.USERNAME.eq("李四")),
        SysUserVo.class
);
sysUserVos.forEach(System.out::println);

@Data
public class SysUserVo {
    private Integer id;
    /**
     * 用户名
     */
    private String username;
    /**
     * 密码
     */
    private String password;
    /**
     * 角色id
     */
    private Integer roleId;
    /**
     * 角色名
     */
    private String roleName;
}

select `sys_user`.*,
       `sys_role`.*
from `sys_user`
         left join `sys_user_role` on `sys_user`.`id` = `sys_user_role`.`user_id`
         left join `sys_role` on `sys_user_role`.`role_id` = `sys_role`.`id`
where `sys_user`.`id` > 1
group by `sys_user`.`username`
having `sys_user`.`username` = '李四';

兼容Mybatis的查询方式

建表语句

create table account
(
    id        int auto_increment
        primary key,
    user_name varchar(100)   null comment '用户名',
    age       int            null comment '年龄',
    birthday  datetime       null comment '生日',
    money     decimal(18, 2) null
) comment '账户表';

create table article
(
    id         int auto_increment
        primary key,
    account_id int          null comment '账户id',
    title      varchar(100) null comment '标题',
    content    text         null comment '内容'
) comment '文章表';
    
create table sys_role
(
    id        int auto_increment
        primary key,
    role_name varchar(20) null comment '角色名'
);

create table sys_user
(
    id       int auto_increment
        primary key,
    username varchar(20)  null comment '用户名',
    password varchar(255) null comment '密码'
);

create table sys_user_role
(
    id      int auto_increment
        primary key,
    user_id int null comment '用户id',
    role_id int null comment '角色id'
);

更多操作见官方文档：MyBatis-Flex - MyBatis-Flex 官方网站