org.h2.jdbc.JdbcSQLException: Method is only allowed for a query

Question

I have used the following code to run a query on my database.

@Repository
public interface PurchaseOrderRepository extends JpaRepository<PurchaseOrder, PurchaseOrderID> {

    @Query(value ="update PURCHASE_ORDER   set status='REJECTED'   where id=?1", nativeQuery = true)
    void RejectPO(Long id);
}

and then i simply call this method in a service

@Service
public class SalesService {

    @Autowired
    PurchaseOrderRepository purchaseOrderRepository;
public void RejectPurchaseOrder(Long of) {

        purchaseOrderRepository.RejectPO(of);
    }
}

but i face with an error:

org.h2.jdbc.JdbcSQLException: Method is only allowed for a query. Use execute or executeUpdate instead of executeQuery; SQL statement:
update PURCHASE_ORDER   set status='REJECTED'   where id=? [90002-191]

The problem is, i have never called executeQuery, i just ask to run it using jpa. So how can i fix it?

Answer 1

In order for JPA to actually run custom @Query which modifies state of the database, the method has to be annotated with @Modifying to tell JPA to use executeUpdate etc.

Instead of

@Query(value ="update PURCHASE_ORDER   set status='REJECTED'   where id=?1", nativeQuery = true)
void RejectPO(Long id);

Try

@Modifying
@Query(value ="update PURCHASE_ORDER   set status='REJECTED'   where id=?1", nativeQuery = true)
void RejectPO(Long id);

See http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.modifying-queries for details.

Answer 2

The problem is with this line:

 // Used only when select statement is used .
 @Query(value ="update PURCHASE_ORDER   set status='REJECTED'   where id=?1", nativeQuery = true)
    void RejectPO(Long id);

You are trying to update the schema, but you're using only the @Query annotation.

When a query method is annotated with @Query only, internally Spring calls executeQuery() which is not used to modify the values in a Schema. executeQuery() is only responsible for executing select queries.

Add one more annotation which enables your @Query to update:

 @Modifying  // add this annotation
 @Query(value ="update PURCHASE_ORDER   set status='REJECTED'   where id=?1", nativeQuery = true)
    void RejectPO(Long id);

Answer 3

You are getting org.h2.jdbc.JdbcSQLException message because you are not applying the @Modifying annotation to the repository method void RejectPO(Long id);. This should definitely fix the issue :

@Modifying
@Query(value ="update PURCHASE_ORDER   set status='REJECTED' where id=?1", nativeQuery = true)
void RejectPO(Long id);