Standard current time function using JDBC

Question

When we work with JPA and JPQL we can use some date/time expressions which make the query DMBS independent. For instance let's say if I want to set the ending time of a session in my database I could simply use CURRENT_TIMESTAMP expression as follows:

String jpql = "UPDATE SessionJpa s SET s.end = CURRENT_TIMESTAMP WHERE s.id = :id";

entityManager.getTransaction().begin();
Query query = entityManager.createQuery(jpql);
query.setParameter("id", someIdValue);
query.executeUpdate();
entityManager.getTransaction().commit();

This way the same JPQL should work with Oracle, MySQL, PostreSQL, etc as DBMS.

Now my question: Is there a way to achieve the same when using JDBC instead of JPA?

This is what I have so far:

String sql = "UPDATE Sessions SET end = SYSDATE WHERE id = ?";

try (Connection connection = dataSource.getConnection();
    PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setLong(1, someIdValue);
    int updatedRows = statement.executeUpdate();
    // ...
} catch(SQLException ex) {
    Logger.getLogger(SessionsBean.class.getName()).log(Level.SEVERE, null, ex);
}

But of course SYSDATE is not a generic expression and it will work only with Oracle as DBMS most likely.

AFAIK most database engines have CURRENT_TIMESTAMP implemented, but not sure if is 100% generic. — Luiggi Mendoza, May 12 '14 at 14:20

score 6 · Answer 1 · answered Dec 22 '14 at 12:28

Apart from the fact that most databases have the SQL standard CURRENT_TIMESTAMP, JDBC drivers might support the JDBC escape functions and translate those to the database specific variant. These escapes are called using {fn <function>}, and are listed in Appendix C of the JDBC 4.2 specification.

Specifically (from C.3):

CURRENT_DATE[()] Synonym for CURDATE()
CURRENT_TIME[()] Synonym for CURTIME()
CURRENT_TIMESTAMP[()] Synonym for NOW()
CURDATE() The current date as a date value
CURTIME() The current local time as a time value
NOW() A timestamp value representing the current date and time

So the JDBC escape equivalent would be:

String sql = "UPDATE Sessions SET end = {fn CURRENT_TIMESTAMP} WHERE id = ?";

(or {fn NOW()})

Note that although JDBC drivers are required to support the escape syntax, they are not actually required to support all functions. Check the result of DatabaseMetaData.getTimeDateFunctions() for your driver.

score 2 · Accepted Answer · edited Jul 21 '17 at 13:50

2

You can find a related discussion here - Is Oracle's CURRENT_TIMESTAMP function really a function?.

Summary is that - CURRENT_TIMESTAMP is defined by the SQL standard and any compliant database system should recognize it.

edited Jul 21 '17 at 13:50

Mark Rotteveel

100,966
191
140
197

answered May 12 '14 at 14:40

Bhesh Gurung

50,430
22
93
142

score 0 · Answer 3 · edited Dec 22 '14 at 12:28

0

You can get the same by defining another parameter for the date, such as:

String sql = "UPDATE Sessions SET end = ? WHERE id = ?";
...
statement.setTimestamp(1, new java.sql.Timestamp(new java.util.Date().getTime()));
statement.setLong(2, sesion.getId());

I hope this works

edited Dec 22 '14 at 12:28

Mark Rotteveel

100,966
191
140
197

answered May 12 '14 at 14:22

Jorge_B

9,712
2
17
22

You mean `new java.sql.Timestamp`. `java.sql.Date` only stores date, not date and time. – Luiggi Mendoza May 12 '14 at 14:25
1

This is not exactly the same as doing current_timestamp on the db server. Plus `java.sql.Date` is not the equivalent of the timestamp. – Bhesh Gurung May 12 '14 at 14:25
Thanks for your answer @Jorge_B, I certainly tried it successfully but I'd prefer to rely on db server (not the bean container) setting the appropriate timestamp. – dic19 May 12 '14 at 14:40

Standard current time function using JDBC

3 Answers3

Linked