db2 QueryDSL:舍入期间语法错误解析

vulvrdjw  于 8个月前  发布在  DB2
关注(0)|答案(2)|浏览(73)
@Embeddable
public class Birthday {

    private LocalDate value;

    // Constructors, getters and setters
}

实体:

@Entity
public class Person {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private Birthday birthday;

     // Constructors, getters and setters
}

除以3的查询:

List<Person> sortedPersons = query.selectFrom(person)
                               .orderBy(person.birthday.value.month().divide(3).ceil().asc())
                                .fetch();

使用模板查询:

List<Person> sortedPersons = query.selectFrom(person)
                                   .orderBy(Expressions.dateTemplate(Integer.class, "QUARTER({0})", person.birthday.value).asc())
                                    .fetch();

错误代码:

Caused by: org.eclipse.persistence.exceptions.JPQLException:
Exception Description: Syntax error parsing [select 
from Person person
order by ceil(extract(month from person.birthday.value) / 3) asc].
[3165, 3254] The order by item is not a valid expression.
    at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildException(HermesParser.java:157) ~[com.ibm.websphere.appserver.thirdparty.eclipselink.2.7_1.0.76.jar:?]
    at [internal classes]
    at com.querydsl.jpa.impl.AbstractJPAQuery.createQuery(AbstractJPAQuery.java:101) ~[querydsl-jpa-4.2.2.HL-20230109.202119-8.jar:?]
    at com.querydsl.jpa.impl.AbstractJPAQuery.createQuery(AbstractJPAQuery.java:94) ~[querydsl-jpa-4.2.2.HL-20230109.202119-8.jar:?]
    at com.querydsl.jpa.impl.AbstractJPAQuery.fetch(AbstractJPAQuery.java:201) ~[querydsl-jpa-4.2.2.HL-20230109.202119-8.jar:?]

生日字段在数据库中存储为DATE
问题是,如果我删除方法.ceil(),那么一切都很好,但四舍五入对我来说很重要,而不是四舍五入我的查询返回不正确的数据(我需要按季度排序)。我尝试使用模板为这一个,但我没有帮助。
如果我不使用.ceil,我有这样的顺序的数据。

  1. 2023年11月1日
  2. 2019 - 03 - 23 00:00:00
  3. 2019年10月29日星期一
  4. 2019 - 03 - 23 01:00:00
  5. 2019 - 03 - 23 00:00:00
  6. 2019 - 08 - 18 00:00:00
tv6aics1

tv6aics11#

我现在看到问题了,我用纯Java做了一个演示,使用 * 你的 * 日期。这是因为java.time.Month * 不是 * 零索引。运行以下程序,并注意校正(减法,而不是四舍五入)使结果符合预期:

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Map;
import java.util.Comparator;
import java.util.Arrays;
import java.util.List;
import static java.util.stream.Collectors.*;

public class BirthdayQuartiles {
    record Birthday(String name, String date) {
        static DateTimeFormatter dtf = DateTimeFormatter.ofPattern("MM/dd/uuuu");
        final static String bformat = "%s : %s (%s)";
        LocalDate getAsLocalDate() {
            return LocalDate.parse(date, dtf);
        }
        @Override
        public String toString() {
            return bformat.formatted(name, date, getAsLocalDate().getMonth());
        }
    }
    public static void main(String[] args) throws Exception {
        Birthday[] birthdays = {
            new Birthday("Charles", "01/11/2023"),
            new Birthday("Diana", "03/30/2023"),
            new Birthday("Margaret", "04/29/2023"),
            new Birthday("Edward", "03/31/2023"),
            new Birthday("Beatrice", "03/30/2023"),
            new Birthday("William", "08/18/2023")
        };
        Map<Integer, List<Birthday>> quartiles = Arrays.stream(birthdays)
            .sorted(Comparator.comparing(Birthday::getAsLocalDate))
            // Adjust key as java.time.Month is NOT zero-indexed
            .collect(groupingBy(b ->(b.getAsLocalDate().getMonth().getValue()-1)/3, toList()));

        final String quartileFormat = "Birthday(s) in quartile %d: %s%n";
        quartiles.entrySet().stream()
            .forEach(e ->System.out.println(quartileFormat.formatted(e.getKey(), e.getValue())));  
    }
}
ccrfmcuu

ccrfmcuu2#

已更改SQL模板。事实证明,您需要在模板本身中取出一个变量:)

List<Person> sortedPersons = query.selectFrom(person)
                                   .orderBy(Expressions.dateTemplate(Integer.class, "SQL('QUARTER(?)', {0})", person.birthday.value).asc())
                                    .fetch();

相关问题