通常, 我们使用 Mybatis 实现 join 表关联的时候, 一般都是通过在 xml 或注解里写自定义 sql 实现.
本文通过 Mybatis Generator 的插件功能新增一个 JoinPlugin 插件, 只要在配置文件里加上该插件就可以使用. 无其他第三方依赖. 如下图:
该插件符合 mbg plugin 即插即用的特点, 不影响生成的实体类, 只对生成的 Example 文件做少量变动 (新增一个内部类).
首选我们看一下使用效果, 如果符合你的要求, 请关注支持.
- /**
- * 简单 join 查询示例
- * select t0.user_id as t0_user_id,t0.user_name as t0_user_name,t0.login_account as t0_login_account,t0.login_password as t0_login_password,
- * t0.user_sex as t0_user_sex,t0.user_email as t0_user_email,t0.user_mobile as t0_user_mobile,t0.user_avatar as t0_user_avatar,
- * t0.user_company as t0_user_company,t0.user_dept as t0_user_dept,t0.is_del as t0_is_del,t0.is_admin as t0_is_admin,
- * t0.system_type as t0_system_type,t0.last_login_time as t0_last_login_time,t0.create_time as t0_create_time,t0.create_user as t0_create_user,
- * t0.update_time as t0_update_time,t0.update_user as t0_update_user,
- * t2.role_id as t2_role_id,t2.role_name as t2_role_name
- * from auth_user as t0
- * inner join auth_user_role as t1 on t0.user_id = t1.user_id
- * left join auth_role as t2 on t2.role_id = t1.role_id
- * where ( ( t0.user_id is not null and ( t0.create_time is not null or t0.create_user is not null ) ) ) and ( t1.user_id <>999999 and t1.role_id>=1 )
- * order by t2.role_id desc,t0.create_time
- * limit 10,100
- */
- public void joinTable() {
- AuthUserExample authUserExample=new AuthUserExample();
- AuthUserExample.Criteria criteria=authUserExample.createCriteria();
- //where 条件
- criteria.andUserIdIsNotNull();
- criteria.createOrCriteria().andCreateTimeIsNotNull().andCreateUserIsNotNull();
- // 关联 user_role
- AuthUserRoleExample urExample=new AuthUserRoleExample();
- //where 中 user_role 表的条件
- urExample.createCriteria().andUserIdNotEqualTo(999999).andRoleIdGreaterThanOrEqualTo(1);
- criteria.createJoinCriteria()
- .innerJoinTable(urExample)
- // 使用 User 表的 user_id 关联 user_role 表的 user_id
- .on(a->a.getUserId(), a->a.equalTo(b->b.tableInfo.getUserId()));
- // 关联 role
- AuthRoleExample roleExample=new AuthRoleExample();
- criteria.createJoinCriteria()
- .leftJoinTable(roleExample)
- // 使用 role 表的 role_id 关联 user_role 表的 role_id
- .on(a->a.tableInfo.getRoleId(), a->a.equalTo(b->b.tableInfo.getRoleId()), urExample)
- // 先按照 role 表的 role_id 降序
- .orderByDesc(a->a.tableInfo.getRoleId())
- // 再按照 user 表的 create_time 升序
- .orderByFirstTable(a->a.getCreateTime())
- // 跳过前 10 条
- .skip(10)
- // 取 100 条记录
- .take(100)
- // 只查询 role 表的 role_id 和 role_name 两个字段
- .select(a->new String[] {a.tableInfo.getRoleId(),a.tableInfo.getRoleName()});
- // 执行查询
- List<Tuple> userList=userDao.selectJoinByExample(authUserExample);
- int totalItemCount=(int) userDao.countJoinByExample(authUserExample);
- System.out.println(userList.size()+",total:"+totalItemCount);
- for(Tuple tuple:userList) {
- AuthUser authUser=tuple.getObject(AuthUser.class);
- AuthUserRole authUserRole=tuple.getObject(AuthUserRole.class);
- AuthRole authRole=tuple.getObject(AuthRole.class);
- totalItemCount++;
- }
- }
控制台打印内容如下:
- 2020-04-23 23:09:16.326 INFO 10308 --- [nio-9000-exec-4] p.r.m.i.SqlStatementInterceptor : ===>sql:select t0.user_id as t0_user_id,t0.user_name as t0_user_name,t0.login_account as t0_login_account,t0.login_password as t0_login_password,t0.user_sex as t0_user_sex,t0.user_email as t0_user_email,t0.user_mobile as t0_user_mobile,t0.user_avatar as t0_user_avatar,t0.user_company as t0_user_company,t0.user_dept as t0_user_dept,t0.is_del as t0_is_del,t0.is_admin as t0_is_admin,t0.system_type as t0_system_type,t0.last_login_time as t0_last_login_time,t0.create_time as t0_create_time,t0.create_user as t0_create_user,t0.update_time as t0_update_time,t0.update_user as t0_update_user,t0.`type` as t0_type,t0.title as t0_title,t0.bz as t0_bz,t2.role_id as t2_role_id,t2.role_name as t2_role_name
- from auth_user as t0
- inner join auth_user_role as t1 on t0.user_id = t1.user_id
- left join auth_role as t2 on t2.role_id = t1.role_id
- where ( ( t0.user_id is not null and ( t0.create_time is not null or t0.create_user is not null ) ) ) and ( t1.user_id <>999999 and t1.role_id>=1 )
- order by t2.role_id desc,t0.create_time
- limit 10,100
- 2020-04-23 23:09:16.329 DEBUG 10308 --- [nio-9000-exec-4] c.r.m.d.A.selectJoinByExample : ==> Preparing: select t0.user_id as t0_user_id,t0.user_name as t0_user_name,t0.login_account as t0_login_account,t0.login_password as t0_login_password,t0.user_sex as t0_user_sex,t0.user_email as t0_user_email,t0.user_mobile as t0_user_mobile,t0.user_avatar as t0_user_avatar,t0.user_company as t0_user_company,t0.user_dept as t0_user_dept,t0.is_del as t0_is_del,t0.is_admin as t0_is_admin,t0.system_type as t0_system_type,t0.last_login_time as t0_last_login_time,t0.create_time as t0_create_time,t0.create_user as t0_create_user,t0.update_time as t0_update_time,t0.update_user as t0_update_user,t0.`type` as t0_type,t0.title as t0_title,t0.bz as t0_bz,t2.role_id as t2_role_id,t2.role_name as t2_role_name from auth_user as t0 inner join auth_user_role as t1 on t0.user_id = t1.user_id left join auth_role as t2 on t2.role_id = t1.role_id WHERE ( ( t0.user_id is not null and ( t0.create_time is not null or t0.create_user is not null ) ) ) and ( t1.user_id <>? and t1.role_id>=? ) order by t2.role_id desc,t0.create_time limit 10,100
- 2020-04-23 23:09:16.330 DEBUG 10308 --- [nio-9000-exec-4] c.r.m.d.A.selectJoinByExample : ==> Parameters: 999999(Integer), 1(Integer)
- 2020-04-23 23:09:16.361 DEBUG 10308 --- [nio-9000-exec-4] c.r.m.d.A.selectJoinByExample : <== Total: 100
- 2020-04-23 23:09:16.367 INFO 10308 --- [nio-9000-exec-4] p.r.m.i.SqlStatementInterceptor : c.r.m.d.AuthUserMapper.selectJoinByExample:41ms
- 2020-04-23 23:09:16.371 INFO 10308 --- [nio-9000-exec-4] p.r.m.i.SqlStatementInterceptor : ===>sql:select count(*)
- from auth_user as t0
- inner join auth_user_role as t1 on t0.user_id = t1.user_id
- left join auth_role as t2 on t2.role_id = t1.role_id
- where ( ( t0.user_id is not null and ( t0.create_time is not null or t0.create_user is not null ) ) ) and ( t1.user_id <>999999 and t1.role_id>=1 )
- 2020-04-23 23:09:16.375 DEBUG 10308 --- [nio-9000-exec-4] c.r.m.d.A.countJoinByExample : ==> Preparing: select count(*) from auth_user as t0 inner join auth_user_role as t1 on t0.user_id = t1.user_id left join auth_role as t2 on t2.role_id = t1.role_id WHERE ( ( t0.user_id is not null and ( t0.create_time is not null or t0.create_user is not null ) ) ) and ( t1.user_id <>? and t1.role_id>=? )
- 2020-04-23 23:09:16.376 DEBUG 10308 --- [nio-9000-exec-4] c.r.m.d.A.countJoinByExample : ==> Parameters: 999999(Integer), 1(Integer)
- 2020-04-23 23:09:16.385 DEBUG 10308 --- [nio-9000-exec-4] c.r.m.d.A.countJoinByExample : <== Total: 1
- 2020-04-23 23:09:16.385 INFO 10308 --- [nio-9000-exec-4] p.r.m.i.SqlStatementInterceptor : c.r.m.d.AuthUserMapper.countJoinByExample:14ms
- 100,total:5190
通过上面简单例子我们可以看到, 生成的 sql 是完全符合预期的, 并且不需要我们额外的再手写 sql 了.
该插件特点:
1. 可关联多表, on 多个字段关联, on 多种条件查询, 例如: left join t_role as t1 on t0.role_id=t1.role_id and t0.sys_type=t1.role_type and t1.is_del=0 and t1.role_type='T'
2. 可自定义表别名
3.sql 中的所有表都可自定义要查询的字段, 表字段多时, 指定字段查询明显提高效率
4. 分页, 多表排序支持, 函数式编程写法
5.mybatis 原生特性, 无其他依赖
该插件原理:
由于 mbg 每个表都生成一个 xml, 在 xml 里自动组装生成的 sql, 并返回 map 对象. 通过 mybatis 的拦截器拦截返回结果, 将 map 转换成不同表的实体对象然后组装返回到一个类 Tuple 中.
本插件初衷: 为了简化开发人员的数据库 sql 繁琐查询工作及改善 mybatis 写 join 关联时只能自定义的状况. 开发过程中当表结构改变时, 所有手写自定义的 sql 里的字段都要改一遍.
另外, mbg1.4.0 的 dynamic-sql 不怎么好用, 这么久了更新这样一个新版本很失望.
来源: https://www.cnblogs.com/RexSheng/p/12764426.html