java - jOOQ - 重用 SelectConditionStep

Question

我有一些这样的代码:

var step =
    db.select(T1.C1).
        from(T1).
        where(T1.C2.eq(v1));

var result = step.
    and(T1.C3.eq(v2)).
    fetchOne();

if(result == null) 
    result = step.
        and(T1.C3.eq(v3)).
        fetchOne();

它工作正常，但我想知道由于 jOOQ 的内部结构，是否应该避免这种重用。

Answer 1

由于历史原因，DSL API 的某些元素是可变的，这意味着您不应在代码中重复使用对中间“步骤”类型的任何引用。每个“步骤”类型的 Javadoc 中都提到了这一点:

Referencing XYZ*Step types directly from client code

It is usually not recommended to reference any XYZ*Step types directly from client code, or assign them to local variables. When writing dynamic SQL, creating a statement's components dynamically, and passing them to the DSL API statically is usually a better choice. See the manual's section about dynamic SQL for details: https://www.jooq.org/doc/latest/manual/sql-building/dynamic-sql.

Drawbacks of referencing the XYZ*Step types directly:

• They're operating on mutable implementations (as of jOOQ 3.x)
• They're less composable and not easy to get right when dynamic SQL gets complex
• They're less readable
• They might have binary incompatible changes between minor releases

我建议您选择functional approach to writing dynamic SQL ，而是:

Select<Result1<Integer>> fetchOne(Condition condition) {
    return db.select(T1.C1)
             .from(T1)
             .where(T1.C2.eq(v1))
             .and(condition)
             .fetchOne();
}

var result = fetchOne(T1.C3.eq(v2));

if (result == null)
    result = fetchOne(T1.C3.eq(v3));

或者，全部用 SQL 完成以防止额外的往返:

var result =
db.select(T1.C1)
  .from(T1)
  .where(T1.C2.eq(v1))
  .and(T1.C3.in(v2, v3))
  .orderBy(T1.C3.sortAsc(v2, v3))
  .limit(1)
  .fetchOne()

这是使用Field.sortAsc()方便的方法。

See also this blog post for more insight on why not to reference the XYZStep types directly.