新工具-mybatis-flex学习及应用
MyBatis-Flex 是一个基于 MyBatis 的、轻量级但功能强大的 Java 持久层框架。它的核心目标是轻量、高效、灵活。它并非 MyBatis-Plus 的一个分支,而是一个独立的项目,从头开始设计和实现,在许多方面提供了更优雅和高效的解决方案。
核心特性与优势
1.轻量级
- 框架本身除了 MyBatis 外没有任何第三方依赖,部署包仅 ~700KB。
- 启动时消耗的资源非常少。
2.强大的查询功能(核心亮点) - 友好的 QueryWrapper:提供了非常流畅的链式 API 来构建复杂查询条件,支持 and/or 嵌套、子查询、多表联查等,代码可读性极高。
- 关系查询:支持 1->1,1->N,N->1 的关系映射,无需编写 SQL 即可轻松完成关联查询。
- 多数据源支持:支持多种数据源和动态表名,易于实现分库分表。
3.极高的性能 - 框架在查询过程中进行了大量优化,性能远超同类框架。
- 支持 SQL 审计,可以帮助开发者发现和优化慢 SQL。
4.丰富的 SQL 生成 - 根据 QueryWrapper 可以自动生成不同数据库方言的 SQL,支持多种主流数据库(MySQL, PostgreSQL, Oracle, SQLServer, SQLite 等)。
5.代码生成 - 内置功能强大的代码生成器,可以快速生成 Entity、Mapper、Service、Controller 等代码,极大提升开发效率。
6.Kotlin 支持 - 对 Kotlin 语言有非常好的支持,提供了扩展函数等,使得在 Kotlin 中的使用体验非常流畅。
Mybatis-Flex实践
1.依赖
依赖方面,若当前框架为springboot项目,主要是包含数据库驱动依赖,mybatis-flex以及扩展依赖,如下:
<dependency><groupId>com.mybatis-flex</groupId><artifactId>mybatis-flex-spring-boot-starter</artifactId><version>1.8.6</version> <!-- 该版本不能使用低于2.0版本的springboot,否则会报某些依赖查找不到的错误 -->
</dependency>
<dependency><groupId>com.mybatis-flex</groupId><artifactId>mybatis-flex-processor</artifactId><version>1.8.6</version><!-- 用于增删改查,生成映射类 --><type>pom</type>
</dependency>
<dependency><groupId>com.mysql</groupId><artifactId>mysql-connector-j</artifactId><version>8.0.32</version><!-- 数据库驱动 --><scope>runtime</scope>
</dependency>
2.配置
a.application.yml
spring:# Druid 连接池配置druid:datasource:url: jdbc:mysql://localhost:3306/user_management?serverTimezone=UTC&characterEncoding=utf8username: rootpassword: 12345678driver-class-name: com.mysql.cj.jdbc.Drivertype: com.alibaba.druid.pool.DruidDataSource
MyBatis-Flex 配置
mybatis-flex:# 配置Mapper扫描路径mapper-locations: classpath*:mapper/user/*.xml# 打印SQL日志configuration:log-impl: org.apache.ibatis.logging.stdout.StdOutImpl# 全局配置global-config:# 打印Bannerprint-banner: true# 逻辑删除配置logic-delete:enable: false # 禁用逻辑删除,使用物理删除
b.新增初始化数据库源代码
package com.example.myproject.config;import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;import javax.sql.DataSource;/*** @author zhouxy* @date 2025年05月19日 18:53*/
@Configuration
public class DataSourceConfig {@Bean@ConfigurationProperties(prefix = "spring.druid.datasource")public DataSource druidDataSource() {return new DruidDataSource();}
}
3.新增实体类等
注意:新增实体类后,MyBatis-Flex Processor注解处理器,需要编译之后才能生成对应的映射类。执行命令:
mvn clean compile
a.t_user实体,新增数据库实体类,需要添加@Table注解,并且指定当前主键id的生成逻辑,支持自增,序列,自定义生成器,无自动生成等方式。
package com.example.myproject.domain.user;import com.mybatisflex.annotation.Id;
import com.mybatisflex.annotation.KeyType;
import com.mybatisflex.annotation.Table;
import lombok.Data;import java.util.Date;/*** 用户信息实体类*/
@Data
@Table("t_user")//添加table表映射
public class User {/*** 用户ID*/@Id(keyType = KeyType.Auto)//指定主键,类型自增private Long id;/*** 用户姓名*/private String userName;/*** 证件号*/private String idNumber;/*** 联系方式*/private String phone;/*** 邮箱*/private String email;/*** 创建时间*/private Date createdAt;/*** 更新时间*/private Date updatedAt;
}
b.t_user_list
package com.example.myproject.domain.user;import com.mybatisflex.annotation.Id;
import com.mybatisflex.annotation.KeyType;
import com.mybatisflex.annotation.Table;
import lombok.Data;import java.util.Date;/*** 用户名单实体类*/
@Data
@Table("t_user_list")
public class UserList {/*** 名单ID*/@Id(keyType = KeyType.Auto)private Long id;/*** 用户ID*/private Long userId;/*** 是否为黑名单用户,0-否;1-是*/private Boolean isBlacklist;/*** 创建时间*/private Date createdAt;/*** 更新时间*/private Date updatedAt;
}
4.添加对应mapper,添加@Mapper注解,即支持所有Mybatis-flex的方法
a.UserMapper
package com.example.myproject.mapper.user;import com.example.myproject.domain.user.User;
import com.mybatisflex.core.BaseMapper;
import org.apache.ibatis.annotations.Mapper;@Mapper
public interface UserMapper extends BaseMapper<User> {
}b.UserListMapper
package com.example.myproject.mapper.user;import com.example.myproject.domain.user.UserList;
import com.mybatisflex.core.BaseMapper;
import org.springframework.stereotype.Repository;@Repository
public interface UserListMapper extends BaseMapper<UserList> {
}
5.使用mybatis-flex方法进行增删改查
首先,先注入当前的UserMapper,下面的增删改查都是在此类中进行的操作
@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest(classes = DemoApplication.class)
public class DBTest {@Autowiredprivate UserMapper userMapper;
}
a.新增
i.单个新增
@Test
public void insert() {User user = new User();user.setUserName("张三");user.setPhone("23432432");user.setIdNumber("idNumber");user.setEmail("124@qq.com");userMapper.insertSelective(user);//可以返回当前的主键log.info("{}", user.getId());
}
ii.批量新增
@Test
public void insertBatch() {//新增List<User> users = new ArrayList<>();Date now = new Date();for (int i = 0; i < 50; i++) {User user = new User();user.setUserName(String.format("张三_%s", i));user.setPhone(String.format("123432432_%s", i));user.setIdNumber(String.format("2222222222333l_%s", i));user.setEmail(String.format("223%s@qq.com", i));user.setCreatedAt(now);user.setUpdatedAt(now);users.add(user);}userMapper.insertBatch(users);
}
效果图:
b.修改
@Test
public void update() {User user = new User();user.setUserName("里斯");//使用mybatis-flex自带的方法,进行更新userMapper.updateByCondition(user, true,QueryCondition.create(USER.USER_NAME, "张三"));//使用QueryCondition+mybatis flex processor设置更新条件
}
sql日志如下:
c.删除
mybatis-flex支持逻辑删除和物理删除
i.逻辑删除
逻辑删除的全局yml配置如下,也可以单独执行某个删除sql时执行物理删除:
MyBatis-Flex 全局配置
mybatis-flex:# 逻辑删除配置logic-delete:# 逻辑删除的字段名(默认值)column: is_deleted# 逻辑已删除值(默认值)logic-deleted-value: 1# 逻辑未删除值(默认值)logic-not-deleted-value: 0
同时,需要在对应数据表中新增删除标识位的字段属性,默认是“is_deleted”
ddl的sql如下:
ALTER TABLE t_user ADD is_deleted TINYINT DEFAULT 0 NULL COMMENT '逻辑删除标识位';
比如,现在要删除username="里斯"的数据,代码如下:
@Test
public void logicDelete() {userMapper.deleteByCondition(QueryCondition.create(USER.USER_NAME, "里斯"));
}
虽然执行 的底层方法是deleteByCondition,但是实际上执行的sql却是update方法,将is_deleted标识位置为1,如下所示:
之后看数据表的数据,里斯的删除标识位变成1:
比如现在有个需求,是查询email='223@qq.com’的数据,数据库里面,“里斯”和“王五”都符合这个查询条件,但是里斯的删除位是1,那么查询时,会不会查出来这条数据呢?查询代码如下:
@Test
public void query() {List<User> users = userMapper.selectListByQuery(QueryWrapper.create().select().from(USER).where(USER.EMAIL.eq("223@qq.com")));//使用QueryWrapper设置查询数据表和条件log.info("{}", JSON.toJSONString(users));
}
查询sql:
==> Preparing: SELECT `id`, `user_name`, `id_number`, `phone`, `email`, `created_at`, `updated_at`, `is_deleted` FROM `t_user` WHERE (`email` = ?) AND `is_deleted` = ?
==> Parameters: 223@qq.com(String), 0(Integer)
<== Columns: id, user_name, id_number, phone, email, created_at, updated_at, is_deleted
<== Row: 2, 王五, 2222222222333l_0, 123432432_0, 223@qq.com, 2025-08-21 02:44:55, 2025-08-21 18:49:23, 0
<== Total: 1
通过sql可以看出来,在查询条件里,默认加上了is_deleted
= 0的条件,排除了is_deleted=1的已删除数据。因此,最终查询结果只查询出来王五这条数据。
ii.物理删除
物理删除,即直接将库中数据删除,yml配置:
MyBatis-Flex 配置
mybatis-flex:# 禁用逻辑删除logic-delete:enable: false
同时,需要删除之前User类中配置的这段代码
@Column(isLogicDelete = true)
private Integer isDeleted;
还是删除“里斯”这个数据,代码如下:
@Test
public void physicsDelete() {userMapper.deleteByCondition(QueryCondition.create(USER.USER_NAME, "里斯"));
}
sql执行情况,很明显直接执行了delete语句,那么这条数据就彻底不存在了:
d.查询
i.简单查询
1.查询username='王五’的数据
@Test
public void query() {List<User> users = userMapper.selectListByQuery(QueryWrapper.create().select().from(USER).where(USER.USER_NAME.eq("王五")));log.info("{}", JSON.toJSONString(users));
}
查询结果:
2.查询现有的部门的人数。
新增部门表,user表新增部门字段,sql如下:
-- user_management.t_dept definitionCREATE TABLE `t_dept` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`name` varchar(100) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ALTER TABLE t_user ADD dept_id BIGINT NULL COMMENT '部门ID';
初始化当前用户表的deptId
实现代码:
@Test
public void query() {QueryWrapper queryWrapper = QueryWrapper.create().select(USER.DEPT_ID, count(USER.ID).as("count")).from(USER).groupBy(USER.DEPT_ID);List<UserDeptCount> userDeptCounts = userMapper.selectListByQueryAs(queryWrapper, UserDeptCount.class);log.info("{}", JSON.toJSONString(userDeptCounts));
}
执行sql以及查询结果如下:
==> Preparing: SELECT `dept_id`, COUNT(`id`) AS `count` FROM `t_user` GROUP BY `dept_id`
==> Parameters:
<== Columns: dept_id, count
<== Row: 1, 41
<== Row: 2, 3
<== Row: 3, 6
<== Total: 3
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1dcedc93]
结果:
[{"count":41,"deptId":1},{"count":3,"deptId":2},{"count":6,"deptId":3}]
3.动态sql查询
比如,当前传进来username和email,需要根据传进来的参数动态查询。功能:支持根据用户名模糊匹配,邮件准确匹配查询列表。代码:
@Test
public void queryComplex() {selectUsers("王","223@qq.com");
}private void selectUsers(String username, String email) {QueryWrapper wrapper = QueryWrapper.create().where(USER.USER_NAME.like(username).when(Strings.isNotEmpty(username))).and(USER.EMAIL.eq(email).when(Strings.isNotEmpty(email)));List<User> users = userMapper.selectListByQuery(wrapper);log.info("{}",JSON.toJSONString(users));
}
执行sql如下:
==> Preparing: SELECT id, user_name, id_number, phone, email, created_at, updated_at, dept_id, gender, birthday FROM t_user WHERE user_name LIKE ? AND email = ?
==> Parameters: %王%(String), 223@qq.com(String)
<== Columns: id, user_name, id_number, phone, email, created_at, updated_at, dept_id, gender, birthday
<== Row: 2, 王五, 2222222222333l_0, 123432432_0, 223@qq.com, 2025-08-21 02:44:55, 2025-08-22 17:04:11, 1, 0, 2020-01-01
<== Total: 1
查询结果:
[{"birthday":"2020-01-01","createdAt":1755744295000,"deptId":1,"email":"223@qq.com","gender":0,"id":2,"idNumber":"2222222222333l_0","phone":"123432432_0","updatedAt":1755882251000,"userName":"王五"}]
4.嵌套查询
比如根据这个sql查询:
select * from t_user where dept_id = 1 and (user_name = '王五' or email = '223@qq.com');
使用mybatis-flex查询代码
@Test
public void queryComplex() {nestedQuery("王", "223@qq.com", 1l);
}private void nestedQuery(String username, String email, Long deptId) {QueryWrapper wrapper = QueryWrapper.create().where(USER.USER_NAME.like(username).when(Strings.isNotEmpty(username)).or(USER.EMAIL.eq(email).when(Strings.isNotEmpty(email)))).and(USER.DEPT_ID.eq(deptId).when(deptId != null));List<User> users = userMapper.selectListByQuery(wrapper);log.info("{}", JSON.toJSONString(users));
}
执行的sql,可以看到sql与上面提供的sql一致:
==> Preparing: SELECT `id`, `user_name`, `id_number`, `phone`, `email`, `created_at`, `updated_at`, `dept_id`, `gender`, `birthday` FROM `t_user`
WHERE (`user_name` LIKE ? OR `email` = ?) AND `dept_id` = ?
==> Parameters: %王%(String), 223@qq.com(String), 1(Long)
<== Columns: id, user_name, id_number, phone, email, created_at, updated_at, dept_id, gender, birthday
<== Row: 2, 王五, 2222222222333l_0, 123432432_0, 223@qq.com, 2025-08-21 02:44:55, 2025-08-22 17:04:11, 1, 0, 2020-01-01
<== Total: 1
查询结果:
[{"birthday":"2020-01-01","createdAt":1755744295000,"deptId":1,"email":"223@qq.com","gender":0,"id":2,"idNumber":"2222222222333l_0","phone":"123432432_0","updatedAt":1755882251000,"userName":"王五"}]
5.分页查询
比如查询user表,查询条件是所有女性用户的第2页的10条数据,实现代码如下:
@Test
public void selectByPage() {Page<User> page = Page.of(2, 10);QueryWrapper qw = QueryWrapper.create().where(USER.GENDER.eq(0));Page<User> usersPage = userMapper.paginate(page, qw);log.info("{}", JSON.toJSONString(usersPage));
}
查询结果的sql如下,可以看到,查询逻辑是先查询所有数据,再查询具体的分页数据:
==> Preparing: SELECT COUNT(*) AS `total` FROM `t_user` WHERE `gender` = ?
==> Parameters: 0(Integer)
<== Columns: total
<== Row: 45
<== Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6f6efa4f]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7ac48e10] was not registered for synchronization because synchronization is not active
==> Preparing: SELECT `id`, `user_name`, `id_number`, `phone`, `email`, `created_at`, `updated_at`, `dept_id`, `gender`, `birthday` FROM `t_user` WHERE `gender` = ? LIMIT 10, 10
==> Parameters: 0(Integer)
<== Columns: id, user_name, id_number, phone, email, created_at, updated_at, dept_id, gender, birthday
<== Row: 13, 张三_11, 2222222222333l_11, 123432432_11, 22311@qq.com, 2025-08-21 02:44:55, 2025-08-22 17:04:11, 1, 0, 2020-01-01
<== Row: 14, 张三_12, 2222222222333l_12, 123432432_12, 22312@qq.com, 2025-08-21 02:44:55, 2025-08-22 17:04:11, 1, 0, 1990-01-01
<== Row: 15, 张三_13, 2222222222333l_13, 123432432_13, 22313@qq.com, 2025-08-21 02:44:55, 2025-08-22 17:04:11, 1, 0, 2020-01-01
<== Row: 16, 张三_14, 2222222222333l_14, 123432432_14, 22314@qq.com, 2025-08-21 02:44:55, 2025-08-22 17:04:11, 1, 0, 2020-01-01
<== Row: 18, 张三_16, 2222222222333l_16, 123432432_16, 22316@qq.com, 2025-08-21 02:44:55, 2025-08-22 17:04:11, 1, 0, 1980-01-01
<== Row: 19, 张三_17, 2222222222333l_17, 123432432_17, 22317@qq.com, 2025-08-21 02:44:55, 2025-08-22 17:04:11, 1, 0, 2020-01-01
<== Row: 20, 张三_18, 2222222222333l_18, 123432432_18, 22318@qq.com, 2025-08-21 02:44:55, 2025-08-22 17:04:11, 1, 0, 2020-01-01
<== Row: 22, 张三_20, 2222222222333l_20, 123432432_20, 22320@qq.com, 2025-08-21 02:44:55, 2025-08-22 17:04:11, 1, 0, 2020-01-01
<== Row: 23, 张三_21, 2222222222333l_21, 123432432_21, 22321@qq.com, 2025-08-21 02:44:55, 2025-08-22 17:04:11, 1, 0, 2020-01-01
<== Row: 24, 张三_22, 2222222222333l_22, 123432432_22, 22322@qq.com, 2025-08-21 02:44:55, 2025-08-22 17:04:32, 3, 0, 1991-01-01
<== Total: 10
查询结果的截图如下,可以获取到当前的总页数,总条数,当前页的所有数据信息
ii.复杂查询
联表查询,mybatis-flex也适配的非常好,不需要新增手写sql,使用Java代码即可按照业务处理数据逻辑。
比如,现在要查询查询所有男性的绩效信息。
新增员工绩效表t_user_performance
-- user_management.t_user_performance definitionCREATE TABLE `t_user_performance` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`date` varchar(100) DEFAULT NULL COMMENT '绩效记录时间',`score` tinyint(4) DEFAULT NULL COMMENT '分数',`user_id` bigint(20) NOT NULL COMMENT '用户ID',PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='员工绩效表';
查询sql如下,需要根据user,user_performance表进行联表查询:
select * from t_user_performance p left join t_user u on p.user_id = u.id where u.gender = 1;
mybatis-flex代码实现sql如下:
@Test
public void leftJoin() {QueryWrapper wrapper = QueryWrapper.create().select().from(USER_PERFORMANCE).leftJoin(USER).on(USER_PERFORMANCE.USER_ID.eq(USER.ID)).where(USER.GENDER.eq(1));List<UserPerformance> performances = userPerformanceMapper.selectListByQuery(wrapper);log.info("{}", JSON.toJSONString(performances));
}
sql打印日志如下,通过日志打印可以看出,代码实现了左连接查询:
==> Preparing: SELECT * FROM `t_user_performance` LEFT JOIN `t_user` ON `t_user_performance`.`user_id` = `t_user`.`id` WHERE `t_user`.`gender` = ?
==> Parameters: 1(Integer)
<== Columns: id, date, score, user_id, id, user_name, id_number, phone, email, created_at, updated_at, is_deleted, dept_id, gender, birthday
<== Row: 11, 2025-07-01, 69, 11, 11, 张三_9, 2222222222333l_9, 123432432_9, 2239@qq.com, 2025-08-21 02:44:55, 2025-08-22 17:04:11, 0, 1, 1, 2023-01-01
<== Row: 17, 2025-07-01, 75, 17, 17, 张三_15, 2222222222333l_15, 123432432_15, 22315@qq.com, 2025-08-21 02:44:55, 2025-08-22 17:04:32, 0, 2, 1, 2020-01-01
<== Row: 21, 2025-07-01, 79, 21, 21, 张三_19, 2222222222333l_19, 123432432_19, 22319@qq.com, 2025-08-21 02:44:55, 2025-08-22 17:04:11, 0, 1, 1, 2020-01-01
<== Row: 26, 2025-07-01, 84, 26, 26, 张三_24, 2222222222333l_24, 123432432_24, 22324@qq.com, 2025-08-21 02:44:55, 2025-08-22 17:04:11, 0, 1, 1, 2020-01-01
<== Row: 30, 2025-07-01, 88, 30, 30, 张三_28, 2222222222333l_28, 123432432_28, 22328@qq.com, 2025-08-21 02:44:55, 2025-08-22 17:04:11, 0, 1, 1, 2020-01-01
<== Total: 5
得到的结果如下:
[{"date":"2025-07-01","id":11,"score":69,"userId":11},{"date":"2025-07-01","id":17,"score":75,"userId":17},{"date":"2025-07-01","id":21,"score":79,"userId":21},{"date":"2025-07-01","id":26,"score":84,"userId":26},{"date":"2025-07-01","id":30,"score":88,"userId":30}]
也可以指定某些字段返回,比如需要返回绩效信息,以及用户名信息,代码实现如下:
@Test
public void leftJoinWithUserName() {QueryWrapper wrapper = QueryWrapper.create().select(USER_PERFORMANCE.ALL_COLUMNS,USER.USER_NAME).from(USER_PERFORMANCE).leftJoin(USER).on(USER_PERFORMANCE.USER_ID.eq(USER.ID)).where(USER.GENDER.eq(1));List<UserPerformanceVO> performances = userPerformanceMapper.selectListByQueryAs(wrapper, UserPerformanceVO.class);log.info("{}", JSON.toJSONString(performances));
}
执行sql以及结果如下,可以看到,查询结果新增了user_name字段,返回结果也显示了当前用户信息
==> Preparing: SELECT `t_user_performance`.*, `t_user`.`user_name` FROM `t_user_performance` LEFT JOIN `t_user` ON `t_user_performance`.`user_id` = `t_user`.`id` WHERE `t_user`.`gender` = ?
==> Parameters: 1(Integer)
<== Columns: id, date, score, user_id, user_name
<== Row: 11, 2025-07-01, 69, 11, 张三_9
<== Row: 17, 2025-07-01, 75, 17, 张三_15
<== Row: 21, 2025-07-01, 79, 21, 张三_19
<== Row: 26, 2025-07-01, 84, 26, 张三_24
<== Row: 30, 2025-07-01, 88, 30, 张三_28
<== Total: 5
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3bfae028][{"date":"2025-07-01","id":11,"score":69,"userId":11,"userName":"张三_9"},{"date":"2025-07-01","id":17,"score":75,"userId":17,"userName":"张三_15"},{"date":"2025-07-01","id":21,"score":79,"userId":21,"userName":"张三_19"},{"date":"2025-07-01","id":26,"score":84,"userId":26,"userName":"张三_24"},{"date":"2025-07-01","id":30,"score":88,"userId":30,"userName":"张三_28"}]
上面就是列出的mybatis-flex的示例,根据示例可以举一反三,从而获取其他复杂查询的思路。支持左右连接查询,联合查询,子查询,分页查询,几乎所有平时用到的查询都支持。