CODEWITHSUNDEEP
javahibernatejpa
V1234
V1234

Reputation: 21

JPA query not accepting - " ' " [single quotes]

I need your help and I am stuck for last 2 days.Tried looking for solution everywhere but no luck.

I am trying to convert the below query to JPA query.

delete from the_table
where the_timestamp < now() - interval '7 days'

JPA query is

EntityManagerFactory em = tx.getEntityManagerFactory();
em.createEntityManager().createNamedQuery("delete from the_table where the_timestamp < now() - interval ?").setParameter(1, "'" +"7 days"+ "'").executeUpdate();

And I am getting the below error:-

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute statement

Also tried with the parameter based binding, but the issue remained same .below is the snippet

EntityManagerFactory em = tx.getEntityManagerFactory();
em.createEntityManager().createNamedQuery("delete from the_table where the_timestamp < now() - interval :deleteOlderRecords").setParameter("deleteOlderRecords", "'" +"7 days"+ "'").executeUpdate();

Could someone please point out what is the problem here?

Upvotes: 0

Views: 3449

Answers (2)

Neil Stockton
Neil Stockton

Reputation: 11531

Your query is invalid (there is no keyword "interval" in JPQL - read any decent JPQL reference). If you want to put in a timestamp then you are best to use a parameter. Alternatively you can use JDBC escape syntax like this link shows http://www.datanucleus.org/products/accessplatform_4_1/jpa/jpql.html#literals

Upvotes: 0

user330315
user330315

Reputation:

Don't know about JPA but in JDBC you cannot pass an interval like that. You need to use something like this:

"delete from the_table where the_timestamp < now() - interval '1' day * ?"

And then pass the number of days as an integer

Upvotes: 1

Related Questions