How to add date in mysql database from Hibernate/Spring Jpa-CodePudding

I use spring boot, and I want to add 1 year to a specific column in mysql database

String queryRecherche = "UPDATE myTable t SET t.dateDebut = DATE_ADD(t.dateDebut, INTERVAL 1 YEAR) WHERE.id = 3 ";
Query query = em.createQuery(queryRecherche);;
query.executeUpdate();

But I get the folowing error :

org.hibernate.query.sqm.ParsingException: line 1:66 no viable alternative at input 'DATE_ADD(t.dateDebut,INTERVAL1'

Have you please any suggestions to do this.

CodePudding user response：

You can use SQL directly, via createNativeQuery, or register a new function as shown in this example to call it from HQL

CodePudding user response：

You're using Hibernate 6 (I can tell by the error message), so the correct HQL syntax to use is:

UPDATE MyEntity t SET t.dateDebut = t.dateDebut   1 year WHERE t.id = 3

You had three errors in your query:

You referred to the name of a table instead of the name of an entity class in the UPDATE clause.
You used the unportable MySQL DATE_ADD function instead of the portable HQL date/time arithmetic described here.
The syntax of your WHERE clause was garbled.

Perhaps you meant for this to be a native SQL query, in which case you called the wrong method of Session. But there's no need to use native SQL for the above query. As you can see, HQL is perfectly capable of expressing that query.