'How to delete records in JPA with greater than condition?

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.

javapostgresqljpaejb


Solution 1:[1]

You must use : not ?

DELETE FROM ContainerTypeRule WHERE rule_id > :startID

Solution 2:[2]

  • 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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Simon Martinelli
Solution 2 zahraa

Related Questions