CODEWITHSUNDEEP
javaspringjpa
Jeff
Jeff

Reputation: 8411

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

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?

Upvotes: 16

Views: 14135

Answers (4)

Imran
Imran

Reputation: 39

The problem seems to be here

 @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);
}

I added @Transactional & @Modifying to my interface like this and it worked

 @Transactional
 @Repository
 public interface PurchaseOrderRepository extends JpaRepository<PurchaseOrder, PurchaseOrderID> {
    @Modifying
    @Query(value ="update PURCHASE_ORDER   set status='REJECTED'   where id=?1", nativeQuery = true)
    void RejectPO(Long id);
}

Hope it should help

Upvotes: 0

keemsisi
keemsisi

Reputation: 419

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);

Upvotes: 1

Vikrant Kashyap
Vikrant Kashyap

Reputation: 6836

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);

Upvotes: 2

Laurenzo
Laurenzo

Reputation: 575

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.

Upvotes: 25

Related Questions