How to run queries like "SET SQL_SAFE_UPDATES = 0" with JPA

Question

I wish to run this query in my spring desktop application

SET SQL_SAFE_UPDATES = 0;

Should it be a GET request or a PUT request ? I have tried both but no success. Currently I am trying

@GetMapping("/orderbook/safeMode")
    private void setSafeMode() {
        orderBookRepository.setSafeMode();
    }

@Query(value = "SET SQL_SAFE_UPDATES = 0;", nativeQuery = true)
    void setSafeMode();

But it gives an error

2019-10-18 11:38:37.844 ERROR 12112 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : ResultSet is from UPDATE. No Data.
2019-10-18 11:38:37.851 ERROR 12112 --- [nio-8080-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.orm.jpa.JpaSystemException: could not execute query; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query] with root cause

java.sql.SQLException: ResultSet is from UPDATE. No Data.

any suggestions please

it says "message": "Executing an update/delete query; nested exception is javax.persistence.TransactionRequiredException: Executing an update/delete query" — Nitin Nanda, Oct 18 '19 at 17:52

Nitin Nanda · Accepted Answer · 2019-10-18T18:01:58.730

It worked by adding @Modifying and @Transactional annotations to the repository method

@Modifying
@Transactional
@Query(value = "SET SQL_SAFE_UPDATES = 0;", nativeQuery = true)
void setSafeMode();

Thanks to Nikolay Shevchenko for suggesting @Modifying annotation

Important links to learn more

Do we need both @Transaction and @Modifying annotation in Spring?

why do we have to use @Modifying annotation for queries in Data Jpa

sovannarith cheav · Answer 2 · 2019-10-18T16:16:33.667

-1

Here is right flow to update an object in DB using Spring Data:

// first go get that object by id, ex :
OrderBook orderBook = orderBookRepository.findById(1);
// 
if (orderBook != null) {
    orderBook.setSafeMode(0); // setter
    orderBookRepository.save(orderBook); // updating if object already have id and the id is existed in DB or will save new object if there are no id existed in DB.
}

EDIT If you find way to lock object for update with Spring Data JPA

PESSIMISTIC_READ – allows us to obtain a shared lock and prevent the data from being updated or deleted

@Lock(LockModeType.PESSIMISTIC_READ)
public Optional<OrderBook> findById(Long id); // override

When you call orderBookRepository.findById(1) first, then other can't update it until you already committed that object.

edited Oct 18 '19 at 16:16

answered Oct 18 '19 at 16:02

sovannarith cheav

743
1
6
19

but i am not trying to update an orderbook object here. I want to run the query 'SET SQL_SAFE_UPDATES = 0;' and asking how to do it because we do not have an execute query method in JPA – Nitin Nanda Oct 18 '19 at 16:08
1

Ahh, I see. you need something like `Select * from table for update;` ? thing like that? – sovannarith cheav Oct 18 '19 at 16:10
2

not really !! I am precisely trying to run the query(not update anything) I mentioned. I know how to update an object through spring framework using JPA. – Nitin Nanda Oct 18 '19 at 16:18
I haven't mentioned it anywhere that I want to get an orderbook object by id or update or save it. – Nitin Nanda Oct 18 '19 at 16:20
1

Okay lah :), maybe I don't understand your question. – sovannarith cheav Oct 18 '19 at 16:20

How to run queries like "SET SQL_SAFE_UPDATES = 0" with JPA

2 Answers2