How to get the database time with JPQL?

Question

Hi, 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 of the last two lines?

thanks,

Answer 1

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