CODEWITHSUNDEEP
javaoracle-databasejpa
SaintLike
SaintLike

Reputation: 9579

Deleting rows from DB by date

so I have the following entity:

@Entity
@Customizer(GridCacheCustomizer.class)
public class TrxIdList implements Serializable {

private static final long serialVersionUID = 9086928639117607157L;
    @Id
    private String Id;

    @Version
    protected int version;

    private Timestamp creationTs;

...

/*geters and setters go here */

And I'm inserting several rows with random dates from 1-10-2014 to 06-10-2014 And I'm trying to delete all rows with a date lower than 05-10-2014 (yesterday). When querying the DB I shoul have only 05 and 06 dates. I'm trying to delete the rows like this:

  int deletedCount = em.createQuery("DELETE FROM TrxIdList where (trunc(creationTs) < trunc(SYSDATE-1))").executeUpdate();

However, I keep getting the following error:

Exception in thread "Thread-3" java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: 
Exception Description: Syntax error parsing [DELETE FROM TrxIdList where (trunc(creationTs) < trunc(SYSDATE-1))]. 
[28, 68] The expression is not a valid conditional expression.
[55, 62] The left expression is not an arithmetic expression.
    at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1605)
    at main.TrxJanitor.run(TrxJanitor.java:43)
    at java.lang.Thread.run(Thread.java:744)
Caused by: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.5.1.v20130918-f2b9fc5): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing [DELETE FROM TrxIdList where (trunc(creationTs) < trunc(SYSDATE-1))]. 
[28, 68] The expression is not a valid conditional expression.
[55, 62] The left expression is not an arithmetic expression.
    at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildException(HermesParser.java:155)
    at org.eclipse.persistence.internal.jpa.jpql.HermesParser.validate(HermesParser.java:334)
    at org.eclipse.persistence.internal.jpa.jpql.HermesParser.populateQueryImp(HermesParser.java:278)
    at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildQuery(HermesParser.java:163)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:142)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:116)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:102)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:86)
    at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1603)
    ... 2 more

Why can't I delete the rows like this? What's the correct way to do so?

Upvotes: 0

Views: 244

Answers (2)

Ramesh
Ramesh

Reputation: 31

I guess there might be some issues with the query you have formed.Check with the below snippet.

    Query qry = session.createQuery("DELETE FROM TrxIdList where creationTs < trunc(SYSDATE-1)");
    int deletedRecords = qry.executeUpdate();

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 181027

DELETE FROM TrxIdList where (trunc(creationTs) < trunc(SYSDATE-1))

...does not look like a Java persistence query.

I suspect you're meaning to use createNativeQuery to create a native SQL query instead of using createQuery to create a persistence query.

Upvotes: 2

Related Questions