如何在Jooq处理日期？ [英] How to Handle Date in Jooq?

问题描述

当我们使用普通JDBC连接时，我们使用下面的代码来格式化日期或转换日期

When we are using plain JDBC connection we are using below code to format date or convert date

 if(argDB.equals("Oracle")){
       sb.append(" AND TO_CHAR(PaymentDate, 'YYYY-MM-DD') <= TO_CHAR(SYSDATE,'YYYY-MM-DD')");
          }
 else {
       sb.append(" AND CONVERT(VARCHAR(8), PaymentDate, 112) <= CONVERT(varchar(8), dbo.getdate(), 112)");
           }

现在我们使用 JOOQ 你认为我们必须像以前那样转换日期，或者 JOOQ 可以在内部处理这类问题。正如我现在检查的那样 JOOQ 不支持 TO_CHAR Oracle和Lukas的方法给出了一些替代这里

Now we are using JOOQ Do you think we have to convert date like we did previously or JOOQ can handle this type of problem internally.? As i checked right now JOOQ not supported TO_CHAR method of Oracle and Lukas given some alternative of this Here

推荐答案

编写谓词的更好方法

由于您只是将日期格式化以比较它们，所以您应该只是比较日期值本身，因为你的数据库能够使用索引会更快：

A better way to write your predicate

Since you're formatting dates only to compare them, you should probably just compare the date values themselves, which will be much faster as your database will be able to use an index:

-- In SQL
ACCOUNT_PAYMENT.PAYMENT_DATE <= SYSDATE

// In jOOQ
ACCOUNT_PAYMENT.PAYMENT_DATE.le(DSL.currentDate())

实施与方言无关的自定义 TO_CHAR（）功能。

你应该创建一个 CustomField 用于此目的。这将允许您与jOOQ的查询呈现和变量绑定生命周期进行交互，以便根据 RenderContext 的基础 SQLDialect 。基本上这归结为写作（假设jOOQ 3.2 API）：

Implementing a dialect-independent custom TO_CHAR() function.

You should create a CustomField for this purpose. This will allow you to interact with jOOQ's query rendering and variable binding lifecycle in order to render SQL dialect specific clauses depending on the RenderContext's underlying SQLDialect. Essentially this boils down to writing (assuming jOOQ 3.2 API):

class ToChar extends CustomField<String> {
    final Field<?> arg0;
    final Field<?> arg1;
    ToChar(Field<?> arg0, Field<?> arg1) {
        super("to_char", SQLDataType.VARCHAR);
        this.arg0 = arg0;
        this.arg1 = arg1;
    }
    @Override
    public void toSQL(RenderContext context) {
        context.visit(delegate(context.configuration()));
    }
    @Override
    public void bind(BindContext context) {
        context.visit(delegate(context.configuration()));
    }
    private QueryPart delegate(Configuration configuration) {
        switch (configuration.dialect().family()) {
            case ORACLE:
                return DSL.field("TO_CHAR({0}, {1})", 
                    String.class, arg0, arg1);

            case SQLSERVER:
                return DSL.field("CONVERT(VARCHAR(8), {0}, {1})", 
                    String.class, arg0, arg1);

            default:
                throw new UnsupportedOperationException("Dialect not supported");
        }
    }
}

然后，你可以写您自己的静态实用程序方法：

And then, you can write your own static utility methods as such:

public class MyDSL {
    public static Field<String> toChar(Field<?> field, String format) {
        return new ToChar(field, DSL.inline(format));
    }
}

这篇关于如何在Jooq处理日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！