Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

thanks,

like image 627
Max Avatar asked Nov 02 '09 01:11

Max


People also ask

Is Hql and JPQL same?

The Hibernate Query Language (HQL) and Java Persistence Query Language (JPQL) are both object model focused query languages similar in nature to SQL. JPQL is a heavily-inspired-by subset of HQL. A JPQL query is always a valid HQL query, the reverse is not true however.

What is the difference between JPQL and SQL?

SQL works directly against relational database tables, records and fields, whereas JPQL works with Java classes and instances. For example, a JPQL query can retrieve an entity object rather than field result set from database, as with SQL.


1 Answers

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();
}
like image 119
Pascal Thivent Avatar answered Oct 11 '22 06:10

Pascal Thivent