关联查询（JOIN）

ModelWrapper 支持丰富的关联查询方式，包括 LEFT JOIN、RIGHT JOIN、INNER JOIN 和自定义 JOIN。

实体定义

示例实体：

@ModelName("shop_user")
public class User { ... }

@ModelName("shop_user_third")
public class UserThird { ... }

@ModelName("shop_user_third")   // 同一张表的不同实体
public class UserThird1 { ... }

@ModelName("shop_user_third")
public class UserThird2 { ... }

// 中间表示例
@ModelName("sys_role")
public class Role {
    private Long id;
    private String code;
}

@ModelName("sys_role_res")
public class RoleRes {
    private Long id;
    private Long roleId;
    private Long resId;
}

LEFT JOIN

字段对形式

// 关联方式：UserThird.userId = User.id
new ModelWrapper<>(User.class)
    .leftJoin(UserThird.class, UserThird::getUserId, User::getId)
    .eq(User::getStatus, "0")
    .field(User::getId, User::getNickName)
    .field(User::getThird, UserThird.class)
    .pagination();

带别名

new ModelWrapper<>(User.class)
    .leftJoin(UserThird.class, "ut", UserThird::getUserId, User::getId)
    .eq(User::getStatus, "0")
    .eq(UserThird.class, "ut", UserThird::getPlatform, 1)
    .field(User::getId, User::getNickName)
    .pagination();

ON 闭包形式

new ModelWrapper<>(User.class)
    .leftJoin(UserThird.class, on -> on
        .eq(UserThird::getUserId, User::getId)
        .eq(UserThird::getPlatform, 1)
        .in(UserThird::getGender, "1", "2"))
    .eq(User::getStatus, "0")
    .field(User::getId, User::getNickName)
    .pagination();

ON 闭包 + 别名

new ModelWrapper<>(User.class)
    .leftJoin(UserThird.class, "ut", on -> on
        .eq(UserThird::getUserId, User::getId)
        .neq(UserThird::getPlatform, 0))
    .eq(User::getStatus, "0")
    .field(User::getId, User::getNickName)
    .pagination();

ON 闭包 + MFun 字段作为别名

new ModelWrapper<>(User.class)
    .leftJoin(UserThird.class, User::getSex, on -> on
        .eq(UserThird::getUserId, User::getId))
    .eq(User::getStatus, "0")
    .field(User::getId, User::getNickName)
    .pagination();

复杂 ON 闭包（AND/OR 嵌套）

new ModelWrapper<>(User.class)
    .leftJoin(UserThird.class, on -> on
        .eq(UserThird::getUserId, User::getId)
        .or(o -> o
            .eq(UserThird::getPlatform, 1)
            .eq(UserThird::getPlatform, 2))
        .and(a -> a
            .neq(UserThird::getCity, "")
            .isNotNull(UserThird::getOpenid)))
    .eq(User::getStatus, "0")
    .field(User::getId, User::getNickName, User::getEmail)
    .pagination();

RIGHT JOIN

字段对形式

new ModelWrapper<>(User.class)
    .rightJoin(UserThird1.class, UserThird1::getUserId, User::getId)
    .field(User::getId, User::getNickName)
    .pagination();

别名形式

new ModelWrapper<>(User.class)
    .rightJoin(UserThird1.class, "ut1", UserThird1::getUserId, User::getId)
    .field(User::getId, User::getNickName)
    .pagination();

ON 闭包形式

new ModelWrapper<>(User.class)
    .rightJoin(UserThird1.class, "ut1", on -> on
        .eq(UserThird1::getUserId, User::getId)
        .gt(UserThird1::getUserId, 0))
    .field(User::getId, User::getNickName)
    .pagination();

ON 闭包 change() 用法

// change() 将 ON 条件从「关联条件」切换为「过滤条件」
new ModelWrapper<>(User.class)
    .rightJoin(UserThird1.class, "ut1", on -> on
        .eq(UserThird1::getUserId, User::getId)
        .change()
        .eq(User::getAvatar, 1)
        .gt(User::getId, 0))
    .field(User::getId, User::getNickName)
    .pagination();

INNER JOIN

字段对

new ModelWrapper<>(User.class)
    .innerJoin(UserThird2.class, UserThird2::getUserId, User::getId)
    .field(User::getId, User::getNickName)
    .pagination();

别名

new ModelWrapper<>(User.class)
    .innerJoin(UserThird2.class, "ut2", UserThird2::getUserId, User::getId)
    .field(User::getId, User::getNickName)
    .pagination();

ON 闭包

new ModelWrapper<>(User.class)
    .innerJoin(UserThird2.class, on -> on
        .eq(UserThird2::getUserId, User::getId))
    .field(User::getId, User::getNickName)
    .pagination();

// ON 闭包 + 别名 + 额外条件
new ModelWrapper<>(User.class)
    .innerJoin(UserThird2.class, "ut2", on -> on
        .eq(UserThird2::getUserId, User::getId)
        .neq(UserThird2::getPlatform, ""))
    .field(User::getId, User::getNickName)
    .pagination();

JOIN（默认 INNER）

// 默认 INNER JOIN
new ModelWrapper<>(User.class)
    .join(UserThird.class, UserThird::getUserId, User::getId)
    .field(User::getId, User::getNickName)
    .pagination();

// 带别名
new ModelWrapper<>(User.class)
    .join(UserThird.class, "ut", UserThird::getUserId, User::getId)
    .field(User::getId, User::getNickName)
    .pagination();

JOIN 指定类型

// LEFT
new ModelWrapper<>(User.class)
    .join(UserThird.class, UserThird::getUserId, User::getId, JoinType.LEFT)
    .field(User::getId, User::getNickName)
    .pagination();

// RIGHT
new ModelWrapper<>(User.class)
    .join(UserThird1.class, UserThird1::getUserId, User::getId, JoinType.RIGHT)
    .field(User::getId, User::getNickName)
    .pagination();

// INNER
new ModelWrapper<>(User.class)
    .join(UserThird2.class, UserThird2::getUserId, User::getId, JoinType.INNER)
    .field(User::getId, User::getNickName)
    .pagination();

中间表关联

关联两张不直接相关的表：

// User -- left join RoleRes on User.id == RoleRes.roleId
new ModelWrapper<>(User.class)
    .leftJoin(RoleRes.class, RoleRes::getRoleId, User::getId)
    .eq(User::getStatus, "0")
    .field(User::getId, User::getUserName)
    .pagination();

// 也可以 RIGHT / INNER
new ModelWrapper<>(User.class)
    .rightJoin(RoleRes.class, RoleRes::getRoleId, User::getId)
    .pagination();

new ModelWrapper<>(User.class)
    .innerJoin(RoleRes.class, RoleRes::getRoleId, User::getId)
    .pagination();

多表关联（链式 JOIN）

同表多次 JOIN（不同别名）

new ModelWrapper<>(User.class)
    .leftJoin(UserThird.class, "t1", UserThird::getUserId, User::getId)
    .leftJoin(UserThird.class, "t2", UserThird::getUserId, User::getId)
    .eq(User::getStatus, "0")
    .eq(UserThird.class, "t1", UserThird::getPlatform, 1)
    .eq(UserThird.class, "t2", UserThird::getPlatform, 2)
    .field(User::getId, User::getNickName)
    .field(User::getThird, UserThird.class)
    .pagination();

三表不同实体

// SysRes → RoleRes → Role
new ModelWrapper<>(SysRes.class)
    .leftJoin(RoleRes.class, RoleRes::getResId, SysRes::getId)
    .rightJoin(Role.class, RoleRes.class, Role::getId, RoleRes::getRoleId, JoinType.RIGHT)
    .eq(SysRes::getStatus, 1)
    .eq(Role.class, Role::getCode, "admin")
    .field(SysRes.class)
    .pagination();

全部 LEFT JOIN

new ModelWrapper<>(SysRes.class)
    .leftJoin(RoleRes.class, RoleRes::getResId, SysRes::getId)
    .leftJoin(Role.class, RoleRes.class, Role::getId, RoleRes::getRoleId, JoinType.LEFT)
    .eq(SysRes::getStatus, 1)
    .field(SysRes.class)
    .pagination();

关联表条件

等值条件

wrapper
    .eq(UserThird.class, UserThird::getPlatform, 1)
    .neq(UserThird.class, UserThird::getPlatform, 0)

比较条件

wrapper
    .gt(UserThird.class, UserThird::getUserId, 0)
    .gte(UserThird.class, UserThird::getUserId, 1)
    .lt(UserThird.class, UserThird::getUserId, 999999)
    .lte(UserThird.class, UserThird::getUserId, 999999)

模糊匹配

wrapper
    .like(UserThird.class, UserThird::getCity, "%广州%")
    .notLike(UserThird.class, UserThird::getCity, "%test%")

范围条件

wrapper
    .in(UserThird.class, UserThird::getUserId, 1, 2, 3)
    .notIn(UserThird.class, UserThird::getUserId, 999, 1000)
    .between(UserThird.class, UserThird::getUserId, 1, 100)
    .notBetween(UserThird.class, UserThird::getUserId, -1, 0)

空值判断

wrapper
    .isNull(UserThird.class, UserThird::getCity)
    .isNotNull(UserThird.class, UserThird::getOpenid)

关联表条件（带别名）

wrapper
    .eq(UserThird.class, "ut", UserThird::getPlatform, 1)
    .neq(UserThird.class, "ut", UserThird::getPlatform, 0)
    .gt(UserThird.class, "ut", UserThird::getUserId, 0)
    .gte(UserThird.class, "ut", UserThird::getUserId, 1)
    .lt(UserThird.class, "ut", UserThird::getUserId, 999999)
    .lte(UserThird.class, "ut", UserThird::getUserId, 999999)
    .like(UserThird.class, "ut", UserThird::getCity, "%广州%")
    .notLike(UserThird.class, "ut", UserThird::getCity, "%test%")
    .in(UserThird.class, "ut", UserThird::getUserId, 1, 2, 3)
    .notIn(UserThird.class, "ut", UserThird::getUserId, 999, 1000)
    .isNull(UserThird.class, "ut", UserThird::getProvince)
    .isNotNull(UserThird.class, "ut", UserThird::getOpenid)
    .between(UserThird.class, "ut", UserThird::getUserId, 1, 100)
    .notBetween(UserThird.class, "ut", UserThird::getUserId, -1, 0)

关联表条件（闭包形式）

// where(Class, closure)
wrapper
    .where(UserThird.class, w -> w
        .eq(UserThird::getPlatform, 1)
        .neq(UserThird::getPlatform, 0))

// where(Class, alias, closure)
wrapper
    .where(UserThird.class, "ut", w -> w
        .eq(UserThird::getPlatform, 1)
        .neq(UserThird::getPlatform, 0))

// 闭包中 AND/OR 嵌套
wrapper
    .where(UserThird.class, w -> w
        .neq(UserThird::getPlatform, 0)
        .or(UserThird.class, o -> o
            .eq(UserThird::getPlatform, 1)
            .eq(UserThird::getPlatform, 2))
        .and(UserThird.class, a -> a
            .neq(UserThird::getCity, "")
            .neq(UserThird::getGender, "")))

关联查询 AND/OR 嵌套（混用主表和关联表条件）

基本嵌套

new ModelWrapper<>(User.class)
    .leftJoin(UserThird.class, UserThird::getUserId, User::getId)
    .eq(User::getStatus, "0")
    .and(w -> w
        .neq(User::getId, 0)
        .neq(User::getStatus, "0")
        .neq(UserThird.class, UserThird::getGender, "1")
        .or(UserThird.class, tor -> tor
            .neq(UserThird::getGender, "0")))
    .or(w -> w
        .neq(User::getId, 0)
        .neq(UserThird.class, UserThird::getGender, "1"))
    .field(User::getId, User::getNickName)
    .pagination();

深层嵌套

// 4 层嵌套：主表和关联表条件混用
new ModelWrapper<>(User.class)
    .leftJoin(UserThird.class, UserThird::getUserId, User::getId)
    .eq(User::getStatus, "0")
    .and(w -> w
        .neq(User::getId, 0)
        .or(wr -> wr
            .neq(User::getId, 0)
            .neq(UserThird.class, UserThird::getCity, "")
            .or(o -> o
                .neq(User::getId, 0)
                .neq(User::getStatus, "0")
                .or(o2 -> o2
                    .neq(UserThird.class, UserThird::getCity, "")
                    .neq(UserThird::class, UserThird::getGender, "")))))
    .field(User::getId, User::getNickName)
    .pagination();

多别名嵌套

new ModelWrapper<>(User.class)
    .leftJoin(UserThird.class, "a", UserThird::getUserId, User::getId)
    .leftJoin(UserThird.class, "b", UserThird::getUserId, User::getId)
    .eq(User::getStatus, "0")
    .and(w -> w
        .eq(UserThird.class, "a", UserThird::getPlatform, 1)
        .or(o -> o
            .eq(UserThird.class, "b", UserThird::getPlatform, 2)
            .and(wa -> wa
                .neq(UserThird.class, "a", UserThird::getCity, "")
                .neq(UserThird.class, "b", UserThird::getCity, ""))))
    .field(User::getId, User::getNickName)
    .pagination();

关联排序

wrapper
    .order(User::getCreateTime)
    .order(UserThird.class, UserThird::getCreateTime, false)    // DESC
    .orderByAsc(UserThird.class, UserThird::getUserId)
    .orderByDesc(UserThird.class, UserThird::getUserId)
    .order(UserThird.class, UserThird::getUserId, OrderByType.DESC)
    .order(UserThird.class, UserThird::getUserId, OrderByType.ASC)

// 带别名
wrapper
    .order(UserThird.class, "t1", UserThird::getUserId, false)
    .order(UserThird.class, "t2", UserThird::getUserId, true)
    .orderByAsc(UserThird.class, "t1", UserThird::getCreateTime)
    .orderByDesc(UserThird.class, "t2", UserThird::getCreateTime)

关联分组聚合

// 分组
wrapper
    .group(User::getId, User::getSex)
    .group(UserThird.class, UserThird::getUserId, UserThird::getGender)

// 分组 + having
wrapper
    .group(User::getId, User::getSex)
    .group(UserThird.class, UserThird::getUserId, UserThird::getGender)
    .having(UserThird.class, UserThird::getUserId)

// 带别名
wrapper
    .group(UserThird.class, "ut", UserThird::getUserId, UserThird::getGender)
    .having(UserThird.class, "ut", UserThird::getUserId)

关联字段选择

// 选择关联表的部分字段
wrapper
    .field(User::getId, User::getNickName, User::getEmail)
    .field(User::getThird, UserThird.class,
          UserThird::getOpenid, UserThird::getAvatarUrl, UserThird::getNickName)

// 选择关联表全部字段
wrapper
    .field(User::getThird, UserThird.class)

// 带别名
wrapper
    .field(User::getThird, UserThird.class, "ut")

// 带路径和别名
wrapper
    .field("third", UserThird.class, "ut",
          UserThird::getOpenid, UserThird::getNickName)

关联分页 / Limit

// limit
List<User> list = wrapper
    .leftJoin(...)
    .limit(5)
    .select();

// limit + offset
wrapper
    .leftJoin(...)
    .limit(0, 5)
    .select();

// 分页
IPagination<User> page = wrapper
    .leftJoin(...)
    .page(1, 5)
    .pagination();

where 简写

wrapper
    .where(User::getStatus, "0")
    .where(UserThird.class, UserThird::getPlatform, 1)

Boolean 条件开关

boolean enabled = true;
new ModelWrapper<>(User.class)
    .leftJoin(UserThird.class, UserThird::getUserId, User::getId)
    .eq(enabled, User::getStatus, "0")
    .eq(!enabled, UserThird.class, UserThird::getPlatform, 999)  // 跳过
    .eq(enabled, UserThird.class, UserThird::getPlatform, 1)
    .like(enabled, UserThird.class, UserThird::getCity, "%广州%")
    .field(User::getId, User::getNickName)
    .pagination();

软删除（关联查询）

// includeSoftDelete() 包含已逻辑删除的记录
new ModelWrapper<>(User.class)
    .leftJoin(UserThird.class, UserThird::getUserId, User::getId)
    .includeSoftDelete()
    .eq(User::getId, 1)
    .field(User::getId, User::getNickName)
    .pagination();

关联 + 复杂组合示例

new ModelWrapper<>(User.class)
    .leftJoin(UserThird.class, "t1", UserThird::getUserId, User::getId)
    .leftJoin(UserThird.class, "t2", UserThird::getUserId, User::getId)
    .eq(User::getStatus, "0")
    .eq(UserThird.class, "t1", UserThird::getPlatform, 1)
    .neq(UserThird.class, "t1", UserThird::getCity, "")
    .gt(UserThird.class, "t2", UserThird::getUserId, 0)
    .in(UserThird.class, "t2", UserThird::getGender, "1", "2")
    .group(User::getId)
    .group(UserThird.class, "t1", UserThird::getUserId)
    .group(UserThird.class, "t2", UserThird::getUserId, UserThird::getGender)
    .orderByAsc(User::getId)
    .orderByDesc(UserThird.class, "t1", UserThird::getUserId)
    .order(UserThird.class, "t2", UserThird::getCreateTime, true)
    .field(User::getId, User::getNickName)
    .field(User::getThird, UserThird.class)
    .page(1, 5)
    .pagination();

注意事项

表名/别名冲突

同一张表关联多次时，必须使用不同的别名，否则报错：Not unique table/alias

中间表关联

leftJoin(rel, to, rf, tf) 签名用于关联中间表。当前 JDBC 驱动下部分重载返回 chain()，实际 JOIN 逻辑由闭包签名完成。

ON 闭包方向

JoinOnClosure 使用 isBase()/change() 切换条件方向。change() 后添加的条件转为过滤条件而非关联条件。