Update Query with JPA and Java

Question

Im trying to update my jpa repository

@Transactional
public interface UserRepository extends JpaRepository<User, Integer> {
User findByUsername(String username);
User findById(Long id);
@Query(value = "update user t set t.rule_id = NULL  where t.rule_id = :id", nativeQuery = true)
List<User> setNUll(@Param("id") String id);}

This is a part of my controller:

@RequestMapping(value = "/admin/rule/{id}/edit", method = RequestMethod.GET)
public String editRule(@PathVariable Integer id, Model model)
{
    userService.setNUll(Integer.toString(id));
    model.addAttribute("rule", ruleCrudService.getRuleById(id));
    updateUserData();
    return "ruleForm";
}

And this error apears in my browser:

There was an unexpected error (type=Internal Server Error, status=500). could not extract ResultSet; nested exception is org.hibernate.exception.GenericJDBCException: could not extract ResultSet

The server also says:

SQL Error: 0, SQLState: S1009 2017-06-18 12:51:15.778 ERROR 10388 --- [nio-8080-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper : Can not issue data manipulation statements with executeQuery(). 2017-06-18 12:51:15.844 ERROR 10388 --- [nio-8080-exec-4] 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 extract ResultSet; nested exception is org.hibernate.exception.GenericJDBCException: could not extract ResultSet] with root cause

java.sql.SQLException: Can not issue data manipulation statements with executeQuery().

Anshul Sharma Anshul Sharma · Accepted Answer · 2017-06-18T17:11:36

6

votes

you need to use @Modifying annotation above of @Query to exicute update query using JPA.

Update Query with JPA and Java

2 Answers