Mybatis通用Join的实现（最终版）
2024-09-07 20:19:13
你是否还在为mybatis的多表关联查询而写xml烦恼，是否还在为动态组装查询条件烦恼，是否还在为此没有合适的解决方案烦恼？
mybatis-extension插件，解决开发过程中需要多表关联时需手写xml的烦恼，同样支持通过传入sql返回结果集。
纯mybatis原生支持，轻量级无侵入，可用于辅助mybatis-plus、tk.mybatis或者mybatis-generator使用。
1.运行依赖:
* mybatis>=3.5.2
* jdk>=1.8
2.特性：
2.1. 支持多表自定义join关联查询
2.2. 支持自定义AND/OR混合条件，排序，分页等
2.3. 支持GROUPBY/HAVING聚合查询
2.4. 支持自定义sql查询
2.5. 所有的列名选择功能支持lambda写法和字符串输入两种方式
2.6. 自动判断表名、列名与实体类名、字段名对应，多表关联时自动给表名起别名
2.7. 内置多种mybatis generator常用插件，例如批量新增、分页等
3.性能：
比mybatis-generator性能提高约30%，甚至比navicat中直接执行查询还快，与其他插件的对比待测试。
4.不足：
不支持单表多次重复关联
不支持复杂的三层嵌套AND/OR
不支持多表UNION
5.最佳实践：
轻量级（jar包小于100KB）无其他依赖，可辅助其他mybatis插件使用，为解决JOIN关联为生，并持续提供各种特性
6.使用说明（springboot示例，代码在本文下方下载)
6.1pom.xml中引入mybatis-extension的依赖（目前未放到maven中央仓库，可参考下方本地引入或者放入离线仓库）
<dependency>
<groupId>priv.rexsheng</groupId>
<artifactId>mybatis-extension</artifactId>
<version>1.0.0</version>
<scope>system</scope>
<systemPath>${project.basedir}/src/main/resources/templates/mybatis-extension-1.0.0.jar</systemPath>
</dependency>
6.2 在启动类或者配置类上加入扫描mapper包
@MapperScan(basePackages = {"priv.rexsheng.mybatis.mapper"})
6.3配置mybatis拦截器
import priv.rexsheng.mybatis.interceptor.ResultTypeInterceptor; @Configuration
public class InterceptorConfig {
@Bean
public ResultTypeInterceptor resultTypeInterceptor() {
return new ResultTypeInterceptor();
}
}
6.4正常使用mapper接口查询即可
import priv.rexsheng.mybatis.extension.TableQueryBuilder;
import priv.rexsheng.mybatis.mapper.DynamicMapper;
import priv.rexsheng.mybatis.test.dto.UserRoleQueryDto;
import priv.rexsheng.mybatis.test.entity.TUser;
import priv.rexsheng.mybatis.test.entity.UserRole; @SpringBootTest
public class MapperTest {
@Autowired
private DynamicMapper dao; /**
* 单表简单查询
*/
@Test
public void simpleSelect() {
//定义要查询的表的构建器
TableQueryBuilder<TUser> userQuery=TableQueryBuilder.from(TUser.class);
//定义要查询的字段
userQuery.select(TUser::getUserId,TUser::getUserName).and().like(TUser::getUserName, "%王二小%");
//执行查询
List<TUser> userList=dao.selectByBuilder(userQuery.build());
log.info("用户列表:{}",userList);
}
}
7. 各种查询使用说明
7.1单表查询
@Test
public void simpleUserList() {
TableQueryBuilder<TUser> userQuery=TableQueryBuilder.from(TUser.class);
//只查询user_id与user_name两个字段,
userQuery.select(TUser::getUserId,TUser::getUserName)
.where().gt(TUser::getUserId, 3)
.or().isNotNull(TUser::getUpdateUser).isNotNull(TUser::getCreateTime);
//按照创建时间倒序取前10条
userQuery.orderByDesc(TUser::getCreateTime).take(10);
List<TUser> userList=dao.selectByBuilder(userQuery.build());
logger.info("用户列表:{}",userList);
}
-- ::32.272 DEBUG --- [ main] p.r.m.m.D.selectByBuilder_TUser : ==> Preparing: SELECT user_id AS userId, user_name AS userName FROM t_user WHERE (user_id > ?) AND (update_user IS NOT NULL OR create_time IS NOT NULL) ORDER BY create_time DESC LIMIT ?
-- ::32.293 DEBUG --- [ main] p.r.m.m.D.selectByBuilder_TUser : ==> Parameters: (Integer), (Integer)
-- ::32.315 DEBUG --- [ main] p.r.m.m.D.selectByBuilder_TUser : <== Total:
7.2 单表分组聚合查询
@Test
public void simpleUserGroup() throws ParseException {
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
Date minDate=sdf.parse("2000-10-01"); TableQueryBuilder<TUser> userQuery=TableQueryBuilder.from(TUser.class);
//select create_user,max(create_time)
userQuery.select(TUser::getCreateUser).selectMax(TUser::getCreateTime, "createTime")
.where().gt(TUser::getUserId, 3);
//group by create_user having count(*)>1 and min(create_time)>='2020-10-01 00:00:00'
userQuery.groupBy(TUser::getCreateUser).havingCount(a->a.gt("*", 1)).havingMin(a->a.gte(TUser::getCreateTime,minDate));
List<TUser> userList=dao.selectByBuilder(userQuery.build());
logger.info("用户列表:{}",userList);
}
-- ::27.178 DEBUG --- [ main] p.r.m.m.D.selectByBuilder_TUser : ==> Preparing: SELECT create_user AS createUser, MAX(create_time) AS createTime FROM t_user WHERE (user_id > ?) GROUP BY create_user HAVING (COUNT(*) > ? AND MIN(create_time) >= ?)
-- ::27.204 DEBUG --- [ main] p.r.m.m.D.selectByBuilder_TUser : ==> Parameters: (Integer), (Integer), -- ::00.0(Timestamp)
-- ::27.242 DEBUG --- [ main] p.r.m.m.D.selectByBuilder_TUser : <== Total:
-- ::27.250 INFO --- [ main] priv.rexsheng.mybatis.test.JoinTest : 用户列表:[TUser [userId=null, userName=null, createTime=Mon Aug :: CST , createUser=, updateTime=null, updateUser=null], TUser [userId=null, userName=null, createTime=Mon Aug :: CST , createUser=, updateTime=null, updateUser=null], TUser [userId=null, userName=null, createTime=Mon Aug :: CST , createUser=, updateTime=null, updateUser=null], TUser [userId=null, userName=null, createTime=Mon Aug :: CST , createUser=, updateTime=null, updateUser=null], TUser [userId=null, userName=null, createTime=Mon Aug :: CST , createUser=, updateTime=null, updateUser=null], TUser [userId=null, userName=null, createTime=Mon Aug :: CST , createUser=, updateTime=null, updateUser=null], TUser [userId=null, userName=null, createTime=Mon Aug :: CST , createUser=, updateTime=null, updateUser=null], TUser [userId=null, userName=null, createTime=Mon Aug :: CST , createUser=, updateTime=null, updateUser=null], TUser [userId=null, userName=null, createTime=Mon Aug :: CST , createUser=, updateTime=null, updateUser=null], TUser [userId=null, userName=null, createTime=Mon Aug :: CST , createUser=, updateTime=null, updateUser=null]]
7.3单表分页查询并转换返回类型
@Test
public void simpleUserPageWithConvert() throws ParseException {
TableQueryBuilder<TUser> userQuery=TableQueryBuilder.from(TUser.class);
//查询第4页数据,并将查询的结果转换为实体类UserInfoDto
userQuery.select(TUser::getUserId,TUser::getCreateTime)
.selectAs(TUser::getUserName, "trueName")
.page(4, 10);
List<UserInfoDto> userPagedList=dao.selectByBuilder(userQuery.build(UserInfoDto.class));
logger.info("用户列表:{}",userPagedList);
}
-- ::13.542 DEBUG --- [ main] p.r.m.m.D.selectByBuilder_UserInfoDto : ==> Preparing: SELECT user_id AS userId, create_time AS createTime, user_name AS trueName FROM t_user LIMIT ? OFFSET ?
-- ::13.565 DEBUG --- [ main] p.r.m.m.D.selectByBuilder_UserInfoDto : ==> Parameters: (Integer), (Integer)
-- ::13.587 DEBUG --- [ main] p.r.m.m.D.selectByBuilder_UserInfoDto : <== Total:
-- ::13.596 INFO --- [ main] priv.rexsheng.mybatis.test.JoinTest : 用户列表:[UserInfoDto [userId=, trueName=用户31, createTime=--31T15::], UserInfoDto [userId=, trueName=用户32, createTime=--31T15::], UserInfoDto [userId=, trueName=用户33, createTime=--31T15::], UserInfoDto [userId=, trueName=用户34, createTime=--31T15::], UserInfoDto [userId=, trueName=用户35, createTime=--31T15::], UserInfoDto [userId=, trueName=用户36, createTime=--31T15::], UserInfoDto [userId=, trueName=用户37, createTime=--31T15::], UserInfoDto [userId=, trueName=用户38, createTime=--31T15::], UserInfoDto [userId=, trueName=用户39, createTime=--31T15::], UserInfoDto [userId=, trueName=用户40, createTime=--31T15::]]
public class UserInfoDto {
private Integer userId;
private String trueName;
private LocalDateTime createTime;
}
7.4多个表的关联查询
@Test
public void joinAndGroup() {
long startTime=System.currentTimeMillis();
TableQueryBuilder<TUser> userQuery=TableQueryBuilder.from(TUser.class);
userQuery.selectAs(TUser::getCreateUser,"userId").selectCount("*","count")
.groupBy(TUser::getCreateUser).havingCount(a->a.gt("*", 0)).havingMin(a->a.gte(TUser::getUserId, 1));
userQuery.where().gt(TUser::getUserId, 3).or().isNotNull(TUser::getCreateTime).isNotNull(TUser::getCreateUser); TableQueryBuilder<TRole> roleQuery=TableQueryBuilder.from(TRole.class);
roleQuery.selectMax(TRole::getRoleId, "roleId")
.where().isNull(TRole::getUpdateTime).and().isNotNull(TRole::getCreateUser);
roleQuery.groupBy(TRole::getRoleId).havingMax(a->a.gt(TRole::getRoleId, -1)); TableQueryBuilder<UserRole> userRoleQuery=TableQueryBuilder.from(UserRole.class);
userRoleQuery.and().isNotNull("user_id").isNotNull("role_id");
userRoleQuery.leftJoin(roleQuery).on(UserRole::getRoleId, TRole::getRoleId); userQuery.orderByCount("*").leftJoin(userRoleQuery).on(TUser::getUserId, UserRole::getUserId); List<UserCountDto> userList=dao.selectByBuilder(userQuery.build(UserCountDto.class));
long endTime=System.currentTimeMillis();
logger.info("复杂聚合:{}ms,data:{}",endTime-startTime,userList);
TestCase.assertNotNull(userList);
TestCase.assertTrue(userList.size()>0);
TestCase.assertNotNull(userList.get(0));
}
-- ::52.626 DEBUG --- [ main] p.r.m.m.D.selectByBuilder_UserCountDto : ==> Preparing: SELECT a.create_user AS userId, COUNT(*) AS count, MAX(c.role_id) AS roleId FROM t_user AS a LEFT OUTER JOIN t_user_role AS b on a.user_id=b.user_id LEFT OUTER JOIN t_role AS c on b.role_id=c.role_id WHERE (a.user_id > ?) AND (a.create_time IS NOT NULL OR a.create_user IS NOT NULL) AND (b.user_id IS NOT NULL AND b.role_id IS NOT NULL) AND (c.update_time IS NULL) AND (c.create_user IS NOT NULL) GROUP BY a.create_user, c.role_id HAVING (COUNT(*) > ? AND MIN(a.user_id) >= ?) AND (MAX(c.role_id) > ?) ORDER BY COUNT(*)
-- ::52.627 DEBUG --- [ main] p.r.m.m.D.selectByBuilder_UserCountDto : ==> Parameters: (Integer), (Integer), (Integer), -(Integer)
-- ::52.630 DEBUG --- [ main] p.r.m.m.D.selectByBuilder_UserCountDto : <== Total:
-- ::52.631 INFO --- [ main] priv.rexsheng.mybatis.test.JoinTest : 复杂聚合:6ms,data:[UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=], UserCountDto [userId=, count=, roleId=]]
7.5 sql查询
@Test
public void testCountSql() {
List<Long> countList = dao.selectBySql("Select Count(*) from t_user", Long.class); logger.info("countList:{}", countList);
TestCase.assertNotNull(countList);
TestCase.assertTrue(countList.get(0)>0);
logger.info("countList.0:{}", countList.get(0));
} @Test
public void testSelectSql() {
List<TUser> userList = dao.selectBySql("Select user_id as userId,user_name as userName,create_time as createTime from t_user where user_id>10 limit 3", TUser.class); logger.info("userList:{}", userList);
TestCase.assertNotNull(userList);
TestCase.assertNotNull(userList.get(0).getCreateTime());
logger.info("userList.0:{}", userList.get(0));
}
-- ::33.860 DEBUG --- [ main] p.r.m.m.DynamicMapper.selectBySql_TUser : ==> Preparing: Select user_id as userId,user_name as userName,create_time as createTime from t_user where user_id> limit
-- ::33.884 DEBUG --- [ main] p.r.m.m.DynamicMapper.selectBySql_TUser : ==> Parameters:
-- ::33.912 DEBUG --- [ main] p.r.m.m.DynamicMapper.selectBySql_TUser : <== Total:
-- ::33.921 INFO --- [ main] priv.rexsheng.mybatis.test.SqlTest : userList:[TUser [userId=, userName=用户11, createTime=Mon Aug :: CST , createUser=null, updateTime=null, updateUser=null], TUser [userId=, userName=用户12, createTime=Mon Aug :: CST , createUser=null, updateTime=null, updateUser=null], TUser [userId=, userName=用户13, createTime=Mon Aug :: CST , createUser=null, updateTime=null, updateUser=null]]
-- ::33.924 INFO --- [ main] priv.rexsheng.mybatis.test.SqlTest : userList.:TUser [userId=, userName=用户11, createTime=Mon Aug :: CST , createUser=null, updateTime=null, updateUser=null]
-- ::33.930 DEBUG --- [ main] p.r.m.m.DynamicMapper.selectBySql_Long : ==> Preparing: Select Count(*) from t_user
-- ::33.930 DEBUG --- [ main] p.r.m.m.DynamicMapper.selectBySql_Long : ==> Parameters:
-- ::33.935 DEBUG --- [ main] p.r.m.m.DynamicMapper.selectBySql_Long : <== Total:
-- ::33.936 INFO --- [ main] priv.rexsheng.mybatis.test.SqlTest : countList:[]
-- ::33.936 INFO --- [ main] priv.rexsheng.mybatis.test.SqlTest : countList.:
7.6 实体类结构
public class TUser {
private Integer userId;
private String userName;
private Date createTime;
private Integer createUser;
private Date updateTime;
@ColumnName("update_user")
private Integer updateUser;
}
public class TRole {
private Integer roleId;
private String roleName;
private String roleRemark;
private LocalDateTime createTime;
private Integer createUser;
private Date updateTime;
private Integer updateUser;
}
@TableName("t_user_role")
public class UserRole {
private Integer Id;
private Integer userId;
private Integer roleId;
}
public class UserInfoDto {
private Integer userId;
private String trueName;
private LocalDateTime createTime;
}
public class UserCountDto {
private Integer userId;
private Long count;
private Integer roleId;
}
最新文章
- HTML---用记事本写html
- 【小白的CFD之旅】07 CFD常识
- c语言冒泡排序
- Win7任务栏图标大小调整为等宽
- Android -- 分享功能和打开指定程序
- wpf datagrid 行双击事件
- nginx性能配置参数说明：
- SSL双向认证（高清版）
- Commix命令注入漏洞利用
- ubuntu下如何安装及使用 pysvn-workbench
- 防火墙之netfailt、iptables详解
- iOS tableViewCell 在cell赋值、网络加载照片位置偏移大小错乱，做一个类似qq列表的tableview 更新3
- 【POJ2182】Lost Cows 树状数组+二分
- [Day9]面向对象
- 常用 blas 函数
- python学习记录
- 强制禁用gitlab的双因子认证：Two-Factor Authentication
- java多重转型问题
- Winform 出现“Win已停止工作”解决方法
- imperva 非交互式导入导出配置
热门文章
- python的单下划线和双下划线
- Python爬虫教程：验证码的爬取和识别详解
- 极简 Node.js 入门 - 1.3 调试
- Flink中的window、watermark和ProcessFunction
- 【Python笔记】2020年7月22日练习=[定义一个函数quadratic(a, b, c)，接收3个参数，返回一元二次方程的两个解]
- Codechef June Challenge 2020 Division 1 记录
- 2020-04-06：为什么HashMap不一直使用红黑树？
- angular中常用内置过滤器
- 解决"$ is not defined" 亲自体验
- 报错：org.apache.ibatis.type.TypeException: Could not resolve type alias 'com.deppon.gis.module.job.server.server.impl.HaoDuanEntity'.