0

I am trying to run a simple insert query in spring boot but I am unable to do so.

Query:

@Modifying
    @Query(value = "insert into Local(userId,name,address,pin) VALUES (:userId,:name,:address,:pin)", nativeQuery = true)

    List < Local > insertattributes(@Param("userId")String userId,@Param("address") String address ,@Param("name")String name,@Param("pin") String pin);

Controller:

@RequestMapping("insertattributes/{userId}/{name}/{address}/{pin}")
    @ResponseBody
    public List < Local > insertAttributes(@PathVariable String userId, @PathVariable String name, @PathVariable String address, @PathVariable String pin) {

        return localService.insertattributes(userId,name,address,pin);
    }

Error:

o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: S1009
o.h.engine.jdbc.spi.SqlExceptionHelper   : Can not issue data manipulation statements with executeQuery().
[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().
Patrick
  • 12,336
  • 15
  • 73
  • 115
Sr7
  • 86
  • 1
  • 9

1 Answers1

1

An insert statement doesn't return the inserted record. Since you're expecting a list to be returned executeQuery() is executed by Spring. What you want is the executiong of executeUpdate() which is only executed for void methods.

See also Cannot issue data manipulation statements with executeQuery()

Christian Triebstein
  • 385
  • 2
  • 5
  • 14
  • Is there a way to fetch a list? – Sr7 Apr 06 '18 at 07:06
  • I found the same issue with the update queries too.I understand what you are saying but is there a workaround so that I can get a list or an object? – Sr7 Apr 06 '18 at 07:07
  • 2
    The question here is what you actually want to do. Is there a need for excuting a native query for your insert? Why don't you create an instance of your `Local` class and call `repository.save()` in your JpaRepository? That would also return the saved record for you. See also https://spring.io/blog/2011/02/10/getting-started-with-spring-data-jpa/ for reference – Christian Triebstein Apr 06 '18 at 07:09