如何从SelectConditionStep

Question

我在继续我最后一个关于jOOQ的问题。在Hibernate模型中，使用了过滤器注释，我想将这个相同的“默认过滤器”应用于jOOQ查询。当我将jOOQ查询传递给nativeQuery（org.jOOQ.query query，Class

这就是我尝试过的：

private static <E> SelectConditionStep<Record> applyDefaultFilters(Class<E> type, SelectConditionStep<Record> query)
    {
      if (BaseOrganizationModel.class.isAssignableFrom(type)) {
        query
            .getQuery()
            .addConditions(
                query
                    .getQuery()
                    .asTable()
                    .field("organization_id", Long.class)
                    .eq(currentOrganization().id));
        if (SoftDeletableModel.class.isAssignableFrom(type)) {
          query
              .getQuery()
              .addConditions(query.getQuery().asTable().field("deleted", Boolean.class).eq(false));
        }
      }
      return query;
    }

结果就是这个SQL，不是我想要的，我要它过滤对应的表。

select distinct `EventGroup`.*
from `EventGroup`
where (
  ...
  and `alias_100341773`.`organization_id` = ?
  and `alias_17045196`.`deleted` = ?
)

我想要这个

select distinct `EventGroup`.*
from `EventGroup`
where (
  ...
  and `EventGroup`.`organization_id` = ?
  and `EventGroup`.`deleted` = ?
)

这有可能吗？如果没有，还有什么其他可能的路线？（除了明显的将表格传递给函数之外）

Answer 1

jOOQ 3.16引入了一个新的实验性（截至3.16）查询对象模型API，可以遍历。

在任何Select上，只需调用Select.$from（）即可访问包含的表列表的不可修改视图。

每次您尝试修改现有查询时，请问自己，是否有更优雅的方法使用更实用、更不可变的方法进行动态SQL？与其将额外的谓词附加到查询中，为什么不从函数中生成谓词？

private static Condition defaultFilters(Class<?> type, Table<?> table) {
    Condition result = noCondition();

    if (BaseOrganizationModel.class.isAssignableFrom(type)) {
        result = result.and(table.field("organization_id", Long.class)
                                 .eq(currentOrganization().id));

        if (SoftDeletableModel.class.isAssignableFrom(type))
            result = result.and(not(table.field("deleted", Boolean.class)))
    }

    return result;
}

现在，当您构建查询时，您可以添加过滤器：

ctx.select(T.A, T.B)
   .from(T)
   .where(T.X.eq(1))
   .and(defaultFilters(myType, T))
   .fetch();

如果你真的想改变你的查询（例如，在一个用于所有查询的实用程序中），那么转换方法可能更合适。有不同的方法来实现这一点。

使用视图

一些RDBMS可以访问视图中的会话变量。在Oracle中，您可以在视图内部的organization_id中设置一些SYS_CONTEXT变量，然后只查询（可能是可更新的）视图，而不是直接查询表。不幸的是，MySQL不能做等效的事情，请参阅MYSQL中是否有与ORACLESYS_CONTEXT（'USERENV'，'OS_USER'）等效的东西？

我在这篇博文中描述了这种方法。这种方法的优点是您永远不会忘记设置谓词（您可以使用CI/CD测试验证您的视图源代码），如果您忘记设置会话上下文变量，视图将不会返回任何数据，因此它是一种非常安全的方法。

与AND CHECK OPTION子句一起，您甚至可以防止插入错误的organization_id，从而提高安全性。

在jOOQ中使用VisitListener

这是在jOOQ中实现这一点的最强大的方法，这正是您想要的，但对于所有边缘情况来说，这也是一种非常棘手的方法。请参阅这篇关于在jOOQ中实现行级安全性的文章。从jOOQ 3.16开始，将有更好的方法通过https://github.com/jOOQ/jOOQ/issues/12425.

注意，它不适用于不使用任何jOOQ查询部分的普通SQL模板，也不适用于基于JDBC的查询或系统中可能存在的其他查询，因此请小心使用这种方法，因为可能会从其他组织泄漏数据。

当然，您也可以在JDBC层上实现这一步骤，使用jOOQ的ParsingConnection或ParsingDataSource，这样您还可以拦截第三方SQL并附加谓词。

这可以适用于所有DML语句，包括UPDATE，DELETE。对于INSERT来说有点困难，因为您必须将INSERT... VALUES转换为INSERT... SELECT，或者如果有人想插入错误的organization_id，则抛出异常。

在jOOQ中使用ExecuteListener

与上面的VisitListener方法相比有点黑客化，但通常更容易正确，只需正则表达式将所有语句的WHERE子句替换为WHERE organization\u id=…和ExecuteListener中的。

为了安全起见，您可以拒绝没有WHERE子句的所有查询，或者做一些额外的技巧，在正确的位置添加WHERE子句，以防没有。

使用jOOQ与Hibernate的过滤器等效

jOOQ与Hibernate的过滤器等价于表。其中（条件）子句。这不是完全等效的，您必须防止直接访问代码库中的T，并确保用户仅通过用T替换T的方法访问T。其中（defaultFilters（myType，T））。

这种方法目前失去了表的类型安全性，请参阅：https://github.com/jOOQ/jOOQ/issues/8012