Running a native insert query in spring boot

Question

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().

Your insertattributes method in repository should be void. It is inappropriate to expect List<Local> during insert — Prasad Kothavale
– Prasad Kothavale, Commented Apr 6, 2018 at 6:20
I want to cache the response.That is why I was trying to return a List. — Sr7
– Sr7, Commented Apr 6, 2018 at 6:21

Christian Triebstein · Accepted Answer · 2018-04-06 07:06:36Z

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

edited Apr 6, 2018 at 7:06

answered Apr 6, 2018 at 7:00

Christian Triebstein

4243 gold badges7 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sr7 Over a year ago

Is there a way to fetch a list?

Sr7 Over a year ago

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?

Christian Triebstein Over a year ago

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 spring.io/blog/2011/02/10/getting-started-with-spring-data-jpa for reference

Collectives™ on Stack Overflow

Running a native insert query in spring boot

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related