I want to delete the records from a PGSQL database table(ContainerTypeRule) whose id(rule_id) is greater than 10000. I am creating a query in JPA like as follows

public void deleteAllTestRules(long startID){
    em.createQuery("DELETE FROM ContainerTypeRule WHERE rule_id > ?startID", ContainerTypeRule.class)
            .setParameter("startID", startID)
            .executeUpdate();
}

But I am getting the error like this.

javax.ejb.TransactionRolledbackLocalException: Exception thrown from bean: java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: 
Exception Description: Syntax error parsing [DELETE FROM ContainerTypeRule WHERE rule_id > ?startID]. 
[46, 54] The positional input parameter ''{0}'' cannot use non-Integer characters.

How can I formulate correct query for 'id greater than 10000' condition in my JPA query.

CodePudding user response：

You must use : not ?

DELETE FROM ContainerTypeRule WHERE rule_id > :startID

CodePudding user response：

• The problem is in writing the query parameters.

1. You can either use positional parameters and use '?'. ex: DELETE FROM ContainerTypeRule WHERE rule_id > :startID

2. Or you can use named parameters and use ':'. ex: DELETE FROM ContainerTypeRule WHERE rule_id > ?1

• for more info: https://www.baeldung.com/jpa-query-parameters