How to get the database time with JPQL?

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

SELECT CURRENT_TIMESTAMP

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 o开发者_运维技巧f the last two lines?

thanks,

---

According to the JSR 220: Enterprise JavaBeans 3.0 specifications:

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 Datetime Functions

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.

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;
    }
}

And then:

@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();
}

---

For java 8, one would update Date to Instant, so I changed @Pascal's code to:

import java.time.Instant;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
public class DateItem {
    private Instant date;

    /**
     * @return the date
     */
    @Id
    @Column(name = "DATE_VALUE")
    public Instant getDate() {
        return date;
    }

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

And then:

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

Note: The SELECT is changed as pointed by @Dominic to make it work. However, "SELECT CURRENT_TIMESTAMP AS DATE_VALUE FROM DUAL"will work for H2 but not postgres. "SELECT CURRENT_TIMESTAMP AS DATE_VALUE" works for both H2 and postgres "on my system"(TM)