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.
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.
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.
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
