valepu February 2016

How to execute Native SQL server query without resultset using Spring Data JPA

I am using Spring Data JPA and Hibernate to create a repository in which, among the other things, I have added two queries to enable or disable insert identity so that i can add values with a specified ID. I thought that it was correct to create a native query in the interface

   public interface PartRepo extends  PagingAndSortingRepository <Part,Long > {

        //other methods

        @Query(value = "SET IDENTITY_INSERT PART ON", nativeQuery = true)
        public void enableInsertIdentity();

        @Query(value = "SET IDENTITY_INSERT PART OFF", nativeQuery = true)
        public void disableInsertIdentity();

    }

but when hibernate executes one of the SET IDENTITY_INSERT queries i receive this error:

com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.

This is the full stacktrace:

Hibernate: SET IDENTITY_INSERT PART ON
2016-02-08 15:20:06.069  WARN 7580 --- [nio-8081-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: null
2016-02-08 15:20:06.069 ERROR 7580 --- [nio-8081-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : The statement did not return a result set.
2016-02-08 15:20:06.527 ERROR 7580 --- [nio-8081-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not extract ResultSet] with root cause

com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:191)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:450)
    at com.microsoft.sqlserver.jdbc.SQLServerPrepa        

Answers


Dragan Bozanovic February 2016

By default @Query is considered to be a select query. To make it be treated as a modifying query, use @Modifying annotation:

Indicates a method should be regarded as modifying query.

So, you should write your repository the following way:

public interface PartRepo extends PagingAndSortingRepository<Part,Long> {
    @Modifying
    @Query(value = "SET IDENTITY_INSERT PART ON", nativeQuery = true)
    public void enableInsertIdentity();

    @Modifying
    @Query(value = "SET IDENTITY_INSERT PART OFF", nativeQuery = true)
    public void disableInsertIdentity();
}

Post Status

Asked in February 2016
Viewed 3,307 times
Voted 14
Answered 1 times

Search




Leave an answer