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 官方网站