用jOOQ找到即将到来的生日 [英] Finding upcoming birthdays with jOOQ

问题描述

我正在尝试转换查找即将到来的生日的现有查询以使用jOOQ。我的原始查询 - 使用MySQL，有点简化 - 是

I'm trying to convert an existing query which looks for upcoming birthdays to use jOOQ. My original query - using MySQL, and a bit simplified - is

SELECT COUNT(*) 
FROM people 
WHERE
    DATE_ADD(people_dob, INTERVAL YEAR(CURDATE()) - YEAR(people_dob) YEAR) 
    BETWEEN CURDATE() and DATE_ADD( CURDATE(), INTERVAL 7 DAY)

我试图用jOOQ来表达它但是失败了。我尽可能接近

I tried to express it using jOOQ but failed. I got as close as

context
    .selectCount()
    .from(PEOPLE)
    .where(
        PEOPLE_DOB.add(year(currentTimestamp()).minus(year(PEOPLE_DOB)))
        .between(currentTimestamp()).and(currentTimestamp().add(7)));

不幸的是，这转换为

select count(*) 
from `people` 
where 
    date_add(`people`.`people_dob`, interval (extract(year from current_timestamp()) - extract(year from `people`.`people_dob`)) day) 
    between current_timestamp() and date_add(current_timestamp(), interval 7 day)

这里的查询中断了[DATE_ADD] [date_add]的 expr_unit 参数，这是我原来的查询是年，但在jOOQ呈现的那个是 DAY 。

What breaks the query here is the expr_unit argument for [DATE_ADD][date_add], which is my original query is YEAR, but in the one rendered by jOOQ is DAY.

如何将此查询翻译成jOOQ？我不太关心当前的格式，我只是想了解如何得到相同的结果。

How can I translate this query into jOOQ? I don't care much about the current format, I just want to understand how to get the same result.

推荐答案

jOOQ的< a href =http://www.jooq.org/javadoc/latest/org/jooq/Field.html#add%28org.jooq.Field%29 =nofollow> 字段。 add（） 方法的灵感来自Oracle的解释

jOOQ's Field.add() method is inspired by Oracle's interpretation of

DATE + NUMBER

...其中 NUMBER （如果整数或 Double ）是几天。你想要的是相当于在给定日期添加SQL标准 INTERVAL YEAR TO MONTH 。这可以通过使用 jOOQ的 YearToMonth <来实现。 / code>间隔类型，如果要添加常量间隔。 YearToMonth 类型还扩展 java.lang.Number ，因此也可以与 Field.add（） ，直观地说。

... where NUMBER (if an Integer or Double) is a number of days. What you want is the equivalent of adding a SQL standard INTERVAL YEAR TO MONTH to a given date. This could be achieved through using jOOQ's YearToMonth interval type, if you wanted to add a constant interval. The YearToMonth type also extends java.lang.Number, and can thus also be used with Field.add(), intuitively.

虽然有可能生成这样的字段< YearToMonth> 通过现有的jOOQ 3.2 API，我相信你最好只使用普通的SQL，可能是通过创建一个可重用的方法：

While it might be possible to generate such a Field<YearToMonth> through existing jOOQ 3.2 API, I believe that you will be better off to just resort to plain SQL, possibly by creating a reusable method:

public static <T extends java.util.Date> 
Field<T> dateInCurrentYear(Field<T> field) {
    return DSL.field("DATE_ADD({0}, INTERVAL YEAR(CURDATE()) - YEAR({0}) YEAR)",
                     field.getDataType(),
                     field);
}

这可能是＃2727 以及......

This might be a useful feature addition for #2727 as well...

不幸的是，各种SQL方言对日期时间算术的解释很难标准化。我们在那里不断改进，但通常，纯SQL是编写特定于方言的日期时间算术表达式的最佳方式。

Unfortunately, the various SQL dialects' interpretations of date time arithmetic is hard to standardise. We're constantly improving things there, but often, plain SQL is the best way to write dialect-specific date time arithmetic expressions.

这篇关于用jOOQ找到即将到来的生日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！