如何使用 JPQL 获取数据库时间? [英] How to get the database time with JPQL?

问题描述

使用本机 SQL，我使用如下语句获取数据库时间:

with native SQL I get the database time with a statement like:

SELECT CURRENT_TIMESTAMP

使用 JPQL 我得到相同的结果:

with JPQL I get the same result with:

SELECT CURRENT_TIMESTAMP
FROM Customer c
WHERE c.id=1

有没有办法去掉最后两行?

Is there a way to get rid of the last two lines?

谢谢，

推荐答案

根据 JSR220:企业 JavaBeans 3.0 规范:

Java 持久化查询语言包括以下内置函数，可用于查询的 WHERE 或 HAVING 子句.

4.6.16 Functional Expressions

The Java Persistence query language includes the following built-in functions, which may be used in the WHERE or HAVING clause of a query.

如果任何参数的值函数表达式为空或未知，函数值表达未知.

If the value of any argument to a functional expression is null or unknown, the value of the functional expression is unknown.

[...]

4.6.16.3 日期时间函数

functions_returning_datetime:=
             CURRENT_DATE |
             CURRENT_TIME |
             CURRENT_TIMESTAMP

日期时间函数返回当前日期、时间和数据库服务器上的时间戳.

The datetime functions return the value of current date, time, and timestamp on the database server.

所以我已经很惊讶您可以编写根据规范不正确的第二种形式，因此可能无法移植.

So I'm already surprised you can write the 2nd form that is not correct per specification and might thus not be portable.

对我来说，执行此操作的正确"方法是创建一个具有 java.util.Date 类型的日期字段的类，并使用本机查询填充它.类似的东西:

To me, the "right" way to do this would be to create a class with a date field of type java.util.Date and to populate it with a native query. Something like that:

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

@Entity
public class DateItem {
    private Date date;

    /**
     * @return the date
     */
    @Id
    @Column(name = "DATE_VALUE")
    @Temporal(TemporalType.TIMESTAMP)
    public Date getDate() {
        return date;
    }

    /**
     * @param date
     *            the date to set
     */
    public void setDate(Date date) {
        this.date = date;
    }
}

然后:

@PersistenceContext
EntityManager em;

/**
 * @return System date on DB server
 */
public Date getSystemDate() {
    Query query = em.createNativeQuery(
            "SELECT CURRENT_TIMESTAMP", DateItem.class);
    DateItem dateItem = (DateItem) query.getSingleResult();
    return dateItem.getDate();
}

这篇关于如何使用 JPQL 获取数据库时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！