实体类关联

(1/4)inner join

    //inner join
    @Test
    public void innerJoinTest() {
        // 查询ID=1160799039167057920的城市的省份名称
        HqlHelper helper = HqlHelper.queryFrom(City.class);
        helper.join(HqlHelper.currTable, "province", "p")
              .fetchOther("p", "name", "provinceName")
              .eq("id", "1160799039167057920");
        Record tempRecord = helperService.getRecord(helper);
        System.err.println(tempRecord);
    }

结果:

    select
        province1_.name as col_0_0_ 
    from
        dodo_city city0_ 
    inner join
        dodo_province province1_ 
            on city0_.province_id=province1_.id 
    where
        city0_.id=? limit ?

Record [rawData={provinceName=福建}]

(2/4)left join

测试数据:admin表

ID 管理员名称
A1 超级管理员
A2 码叔
A3 报表管理员

测试数据:role表

ID 角色名称 创建该角色的管理员ID
R1 超级角色
R2 总监 A1
R3 经理 A2
    //left join
    @Test
    public void leftJoinTest() {
        // 角色左关联查询创建该角色的管理员,返回角色名称和管理员名称
        // 因为'超级角色'这个角色没有创建人,因为是left join ,所以返回的adminName=null
        HqlHelper helper = HqlHelper.queryFrom(Role.class);
        helper.fetchOther(HqlHelper.currTable, "name", "roleName")
              .leftJoin(HqlHelper.currTable, "admin", "a")
              .fetchOther("a", "name", "adminName");
        Records tempRecords = helperService.getRecords(helper, false);
        System.err.println(tempRecords);
    }

结果:

    select
        admin1_.name as col_0_0_,
        role0_.name as col_1_0_ 
    from
        dodo_role role0_ 
    left outer join
        dodo_admin admin1_ 
            on role0_.admin_id=admin1_.id

Records [rawData=[
    {adminName=超级管理员, roleName=总监}, 
    {adminName=码叔, roleName=经理}, 
    {adminName=null, roleName=超级角色}]]

(3/4)right join

测试数据:admin表

ID 管理员名称
A1 超级管理员
A2 码叔
A3 报表管理员

测试数据:role表

ID 角色名称 创建该角色的管理员ID
R1 超级角色
R2 总监 A1
R3 经理 A2
//right join
    @Test
    public void rightJoinTest() {
        // 角色右关联查询创建该角色的管理员,返回角色名称和管理员名称
        // 因为'报表管理员'这个管理员没有创建过角色,因为是right join ,所以返回的roleName=null
        HqlHelper helper = HqlHelper.queryFrom(Role.class);
        helper.fetchOther(HqlHelper.currTable, "name", "roleName")
              .rightJoin(HqlHelper.currTable, "admin", "a")
              .fetchOther("a", "name", "adminName");
        Records tempRecords = helperService.getRecords(helper, false);
        System.err.println(tempRecords);
    }

结果:

    select
        admin1_.name as col_0_0_,
        role0_.name as col_1_0_ 
    from
        dodo_role role0_ 
    right outer join
        dodo_admin admin1_ 
            on role0_.admin_id=admin1_.id

Records [rawData=[
    {adminName=超级管理员, roleName=总监}, 
    {adminName=码叔, roleName=经理}, 
    {adminName=报表管理员, roleName=null}]]

(4/4)full join

    //full join
    @Test
    public void fullJoinTest() {
        // 查询具有相同名称的省份和城市,取一条记录
        HqlHelper helper = HqlHelper.queryFrom(City.class);
        helper.join(Province.class, "p")
              .fetchOther("p", "name", "provinceName")
              .eqProperty(HqlHelper.currTable, "name", "p", "name");
        Record tempRecord = helperService.getRecord(helper);
        System.err.println(tempRecord);
    }

结果:

    select
        province1_.name as col_0_0_ 
    from
        dodo_city city0_ cross 
    join
        dodo_province province1_ 
    where
        city0_.name=province1_.name limit ?

Record [rawData={provinceName=香港}]
Copyright © Dodo Framework 2019 all right reservedModify At: 2019-12-25 09:40:22