Menu
I want to use Hibernate current_timestamp() to write a timestamp based on the database time to a column. I want to use the database time, not the system time of the system of my service that uses the database. I am using the database time for two queries, Lock and Unlock.
Lock query
UPDATE MyEntity e SET e.lock = current_timestamp() WHERE e.id = :id
Unlock query
UPDATE MyEntity e SET e.lock = (current_timestamp() - "30 seconds") WHERE e.id = :id
My last resort is to use a native query for PostgreSQL like this:
SELECT CURRENT_TIMESTAMP < (CURRENT_TIMESTAMP + INTERVAL '30 second')
I am unable to locate the documentation on how to add/subtract time,if it is even supported. How can I build the Unlock query?
644
At runtime, Spring Data JPA then generates a class that implements that interface. You can then use that interface as the return type of a repository method. To assign a native query to that method, you need to annotate it with @Query, provide the native SQL statement, and set the native attribute to true.
Of course not! JPA has its own query language but it also supports native SQL. You can create these queries in a very similar way as JPQL queries and they can even return managed entities if you want. Creating a dynamic native query is quite simple.
JPQL and native SQL queries use the same Query interface which provides a setParameter method for positional and named parameter bindings. But the use of named parameter bindings for native queries is not defined by the JPA specification. Positional parameters are referenced as “ ?” in your native Query and their numbering starts at 1.
You can reference an @SqlResultSetMapping in the definition of your @NamedNativeQuery. The mapping gets automatically applied to the result set when you instantiate and execute the query. That’s also the case when Spring Data JPA executes this query when you call the corresponding repository method.
set the default value of the column to the (your database specific) currenttime value. With MySql, the column would look like this:
column_name DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
Edit
Here is an example (MySql) that will update the datetime column every time the row is updated:
last_update DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
