java 替代 jpa 中的月、年、日期函数

Subsitute for Month,year,date functions in jpa

提问by Amol Gupta

I want to execute this query in JPA.But i am getting error while executing this query.How to use Month(),Year() in JPA We can use these in native SQL Query.But how to use it? Please help me.Thanks in advance!!

我想在 JPA 中执行此查询。但是在执行此查询时出现错误。如何在 JPA 中使用 Month(),Year() 我们可以在本机 SQL 查询中使用这些。但是如何使用它？请帮助我。提前致谢！！

 select model from IptExchangeratelines model where model.iptExchangerate.id=:id and " +
                " month(model.currencyExchangeDate)=:month and year(model.currencyExchangeDate)=:year

回答by Nayan Wadekar

Few JPA implementations have built-in support for date/time functions.

很少有 JPA 实现具有对日期/时间函数的内置支持。

• EclipseLink

  EclipseLink supports EXTRACT allowing any database supported date/time part value to be extracted from the date/time. The EXTRACT function is database independent, but requires database support.

  EXTRACT(YEAR, model.currencyExchangeDate), but it requires EclipseLink 2.4.x

  FUNCallows for a database function to be call from JPQL. It allows calling any database functions not supported directly in JPQL.

  To call the DB function MyFunc(a, b, c)use FUNC('MyFunc', a, b, c).

  You can try FUNC('YEAR', currencyExchangeDate)to call 'YEAR' function.

• Hibernate

  In HQL, you can have date function YEAR(date), MONTH(date), DAY(date)to extract required details. Other time functions supported are HOUR(date), MINUTE(date), SECOND(date).

• Criteria API

  CriteriaBuilder#function() :Create an expression for the execution of a database function.

  CriteriaQuery<IptExchangeratelines> cq = cb.createQuery(IptExchangeratelines.class); Root<IptExchangeratelines> exRateLine = cq.from(IptExchangeratelines.class); cq.where(cb.equal(cb.function("year", Integer.class,
exRateLine.get(exRateLine_.currencyExchangeDate)), year) .and(cb.equal(cb.function("month", Integer.class, exRateLine.get(exRateLine_.currencyExchangeDate)), month)));

• Eclipse链接

  EclipseLink 支持 EXTRACT，允许从日期/时间中提取任何数据库支持的日期/时间部分值。EXTRACT 函数与数据库无关，但需要数据库支持。

  EXTRACT(YEAR, model.currencyExchangeDate), 但它需要 EclipseLink 2.4.x

  FUNC允许从 JPQL 调用数据库函数。它允许调用 JPQL 中不直接支持的任何数据库函数。

  要调用 DB 函数，请MyFunc(a, b, c)使用FUNC('MyFunc', a, b, c).

  您可以尝试FUNC('YEAR', currencyExchangeDate)调用“YEAR”函数。

• 休眠

  在HQL，你可以有日期功能YEAR(date)，MONTH(date)，DAY(date)提取所需的详细信息。支持的其他时间函数有HOUR(date)、MINUTE(date)、SECOND(date)。

• 标准API

  CriteriaBuilder#function() ：为数据库函数的执行创建一个表达式。

  CriteriaQuery<IptExchangeratelines> cq = cb.createQuery(IptExchangeratelines.class); Root<IptExchangeratelines> exRateLine = cq.from(IptExchangeratelines.class); cq.where(cb.equal(cb.function("year", Integer.class,
exRateLine.get(exRateLine_.currencyExchangeDate)), year) .and(cb.equal(cb.function("month", Integer.class, exRateLine.get(exRateLine_.currencyExchangeDate)), month)));

回答by Neil Stockton

DataNucleus JPA jhas built in support for YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, as well as the JPA 2.1 standard "FUNCTION" for invoking native functions in the RDBMS.

DataNucleus JPA jhas 内置了对 YEAR、MONTH、DAY、HOUR、MINUTE、SECOND 以及 JPA 2.1 标准“FUNCTION”的支持，用于在 RDBMS 中调用本机函数。

In my experience ALL JPA implementations support such things, unlike the impression given by the other answer

根据我的经验，所有 JPA 实现都支持这些东西，这与其他答案给出的印象不同

回答by Hazim

Dears,

亲爱的，

sometimes we have to think with different way, maybe this can help:

有时我们必须以不同的方式思考，也许这会有所帮助：

TypedQuery<Number> query = entityManager.createQuery(
                "SELECT SUM(COALESCE(p.amount, 0)) FROM Payment p WHERE p.paymentDate BETWEEN ?1 AND ?2", Number.class);
        Calendar calendar = Calendar.getInstance();
        calendar.set(Calendar.MILLISECOND, 0);
        calendar.set(Calendar.SECOND, 0);
        calendar.set(Calendar.MINUTE, 0);
        calendar.set(Calendar.HOUR, 0);
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        calendar.set(Calendar.MONTH, month -1);
        calendar.set(Calendar.YEAR, year);

        query.setParameter(1, calendar.getTime());
        //the location is very important
        calendar.add(Calendar.MONTH, 1);
        calendar.add(Calendar.MILLISECOND, -1);

        query.setParameter(2, calendar.getTime());
        return query.getSingleResult().doubleValue();