Mybatis Generator通用Join的實現

通常，我們使用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不怎麼好用，這麼久了更新這樣一個新版本很失望。

Tags:

VirMach 便宜 VPS