子查询的几种映射方法

原创
2022/03/17 20:53
阅读数 417

要问孔乙己茴字有几种写法,他会告诉你有四种。

要问我子查询语句有几种映射方法,我也给你列个三五种吧。

业务背景

比如说我们有这样一个菜单表,每条菜单记录都有一个parentId指向它的上级菜单的id,Entity对象定义如下:

@Table(name = "menu")
public class MenuEntity {
    @Id
    @GeneratedValue
    private Integer id;
    private Integer parentId;
    private String menuName;
    // getters and setters
}

现在我们想要查询所有的父菜单可以使用这条SQL:

SELECT * FROM menu WHERE id IN (SELECT parent_id FROM menu)

那么这种子查询如何通过字段来映射呢?

方法一:@QueryField

直接使用通用注解@QueryField,将查询语句原样定义到注解里。

public class MenuQuery extends PageQuery {
    @QueryField(and = "id IN (SELECT parent_id FROM menu)")
    private boolean onlyParent;
}

onlyParent字段赋值为true时,从注解中即可得到对应的查询语句为

id IN (SELECT parent_id FROM menu)

拼接SELECT语句即得:

SELECT * FROM menu WHERE id IN (SELECT parent_id FROM menu)

方法二:@SubQuery

我们接着来进行一些优化,将子查询语句的一些变量提取出来,定义一个新的注解@SubQuery

@Target(FIELD)
@Retention(RUNTIME)
public @interface SubQuery {
    String column() default "id";
    String op() default "IN";
    String select();
    String from();
}

然后使用@SubQuery注解重新定义字段:

public class MenuQuery extends PageQuery {
    @SubQuery(select = "parent_id", from = "menu")
    private boolean onlyParent;
}

@SubQuery注解对应的子查询语句的格式为:

#{column} #{op} (SELECT #{select} FROM #{from})

将定义注解时所赋的值id, IN, parent_id, menu代入即得:

id IN (SELECT parent_id FROM menu)

方法三:@NestedQueries/@NestedQuery

子查询往往需要多层嵌套,一个@SubQuery通常是不够用的,而Java的注解又不支持自嵌套,所以我们需要定义两个注解@NestedQueries@NestedQuery,利用数组来表达嵌套逻辑。


@Target({})
public @interface NestedQuery {
    String select();
    String from();
    /**
     * Will use next @NestedQuery.select() as column if empty.
     *
     * @return custom column for next nested query.
     */
    String where() default "";
    String op() default "IN";
}

@Target(FIELD)
@Retention(RUNTIME)
public @interface NestedQueries {
    String column() default "id";
    String op() default "IN";
    NestedQuery[] value();
    boolean appendWhere() default true;
}

@NestedQueries注解对应的子查询语句的格式为:

#{column} #{op} (
  SELECT #{select} FROM #{from} [#{where} #{op} (
     SELECT #{select} FROM #{from} ...)]
)

改用@NestedQueries来定义子查询

public class MenuQuery extends PageQuery {
    @NestedQueries({
            @NestedQuery(select = "parent_id", from = "menu")
    })
    private boolean onlyParent;
}

同样可以得到:

id IN (SELECT parent_id FROM menu)

拓展一:为最后一个子查询添加查询条件

假设我想按菜单名称查询它的父菜单,SQL语句可以是这样:

SELECT * FROM menu WHERE id IN (SELECT parent_id FROM menu WHERE menu_name = ?)

我们只需要把boolean类型的onlyParent字段改为String类型的menuName即可。

public class MenuQuery extends PageQuery {
    @NestedQueries({
            @NestedQuery(select = "parent_id", from = "menu")
    })
    private String menuName;
}

但是这里用于子查询定义的menuName会和普通查询的menuName字段冲突,于是有:

拓展二:将子查询的字段定义为Query对象

MenuQuery中将@NestedQueries注解到新添加的MenuQuery类型的menu字段上用于子查询的映射,再添加一个String类型的menuName,用于子查询的查询条件。

public class MenuQuery extends PageQuery {
    @NestedQueries({
            @NestedQuery(select = "parent_id", from = "menu")
    })
    private MenuQuery menu;
    
    private String menuName;
}

再这样构造一个MenuQuery对象即可:

MenuQuery parentBySubMenu = MenuQuery.builder().menu(MenuQuery.builder().menuName("test").build()).build();

解答

现在回到《基于查询对象字段的后缀推导》这篇文章最后提出的问题,这种复杂的嵌套查询如何映射呢?

SELECT * FROM t_perm WHERE id IN (
    SELECT permId FROM t_role_and_perm WHERE roleId IN (
        SELECT roleId FROM t_user_and_role WHERE userId IN (
            SELECT id FROM t_user WHERE username = ?
)))

根据上面所讲的注解和方法,对应的查询字段可以定义如下:

public class PermQuery extends PageQuery {
    @NestedQueries({
            @NestedQuery(select = "permId", from = "t_role_and_perm"),
            @NestedQuery(select = "roleId", from = "t_user_and_role", where = "userId"),
            @NestedQuery(select = "id", from = "t_user")
    })
    private String username;
}

小结

本篇主要讲解了子查询映射的优化过程,目前的最佳方案为使用@NestedQueries注解以实现子查询的映射,字段的类型可以选择boolean类型,普通类型或者Query类型。

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部