Not supported for DML operations .Unable to update data in postgresql database using spring data

Question

Hi I am using spring boot and Spring data i want to fetch data from database on the basis of id but m not able to retreive it. M getting this error "exception":

"org.springframework.dao.InvalidDataAccessApiUsageException", "message": "org.hibernate.hql.internal.QueryExecutionRequestException: Not supported for DML operations [Update com.ge.health.poc.model.SpringModel SET name='sneha' where id=?]; nested exception is java.lang.IllegalStateException: org.hibernate.hql.internal.QueryExecutionRequestException: Not supported for DML operations [Update com.ge.health.poc.model.SpringModel SET name='sneha' where id=?]", "path": "/updatedata" }

Main Class

  package com.ge.health.poc;

  import org.springframework.boot.SpringApplication;
  import org.springframework.boot.autoconfigure.SpringBootApplication;

  @SpringBootApplication
  public class SpringDataApplication {

     public static void main(String[] args) {
         SpringApplication.run(SpringDataApplication.class, args);
     }
  }

Controller Class

    package com.ge.health.poc.controller;

    import java.io.IOException;
    import java.text.ParseException;

    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.PathVariable;
    import org.springframework.web.bind.annotation.RequestBody;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestMethod;
    import org.springframework.web.bind.annotation.ResponseBody;
    import org.springframework.web.bind.annotation.RestController;
    import org.springframework.web.servlet.mvc.support.RedirectAttributes;

    import com.fasterxml.jackson.core.JsonParseException;
    import com.fasterxml.jackson.databind.JsonMappingException;
    import com.fasterxml.jackson.databind.ObjectMapper;
    import com.ge.health.poc.model.SpringModel;
    import com.ge.health.poc.service.BookServiceImpl;

    @RestController
    public class SpringController {

        @Autowired
        BookServiceImpl bookserviceimpl;

        @RequestMapping(value = "/insertdata", method = RequestMethod.POST)
        @ResponseBody
        public void helloService(@RequestBody String input, final RedirectAttributes redirectAttributes)
                throws JsonParseException, JsonMappingException, IOException, ParseException {

            System.out.println(input);
            ObjectMapper mapper = new ObjectMapper();
            SpringModel pojodata = mapper.readValue(input, SpringModel.class);
            System.out.println(pojodata);
            System.out.println(pojodata.getAuthor());
            bookserviceimpl.save(pojodata);
        }

        @RequestMapping(value = "/getdata/{id}")
        @ResponseBody
        public void retreiveData(@PathVariable("id") int id)
                throws JsonParseException, JsonMappingException, IOException, ParseException {

            System.out.println("id is:" + id);
            bookserviceimpl.retreive(id);

        }

        @RequestMapping(value = "/deletedata", method = RequestMethod.DELETE)
        @ResponseBody
        public void deleteData(@RequestBody String id)
                throws JsonParseException, JsonMappingException, IOException, ParseException {

            System.out.println("M in delete");
            System.out.println(id);

            ObjectMapper mapper = new ObjectMapper();
            SpringModel pojodata = mapper.readValue(id, SpringModel.class);
            int idd = (pojodata.getId());
            System.out.println("value oof idd is:" + idd);
            System.out.println("M into delete method");

            bookserviceimpl.delete(idd);

        }

        @RequestMapping(value = "/updatedata", method = RequestMethod.PUT)
        @ResponseBody
        public void updateData(@RequestBody String id)
                throws JsonParseException, JsonMappingException, IOException, ParseException {

            System.out.println("M in update");
            System.out.println(id);

            ObjectMapper mapper = new ObjectMapper();
            SpringModel pojodata = mapper.readValue(id, SpringModel.class);
            int idd = (pojodata.getId());
            System.out.println("value oof idd is:" + idd);
            bookserviceimpl.update(idd);
        }

    }

Repository

    package com.ge.health.poc.interfac;

    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.jpa.repository.Query;
    import org.springframework.stereotype.Repository;
    import org.springframework.transaction.annotation.Transactional;

    import com.ge.health.poc.model.SpringModel;

    @Repository
    @Transactional
    public interface BookRepository extends JpaRepository<SpringModel, Long> {

        @Query("select author from SpringModel where id=?")
        String findName(int id);

        @Query("Update SpringModel SET name='sneha' where id=?")
        String UpdateByID(int id);

        @Query("delete from SpringModel where id=?")
        String deleteById(int id);

    }

BookServiceImpl.java

    package com.ge.health.poc.service;

    import javax.persistence.EntityManager;

    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Component;

    import com.ge.health.poc.interfac.BookRepository;
    import com.ge.health.poc.model.SpringModel;

    @Component
    public class BookServiceImpl implements BookService {

        @Autowired
        EntityManager entitymanager;

        @Autowired
        BookRepository bookrepo;

        @Override
        public void save(SpringModel bookdata) {

            bookrepo.save(bookdata);
        }

        public String retreive(int id) {
            String s = bookrepo.findName(id);
            System.out.println("Author name is:" + s);
            return null;

        }

        public void delete(int id) {

            System.out.println("M into service delete method");
            bookrepo.deleteById(id);
        }

        public void update(int id) {

            System.out.println("M in service update");
            bookrepo.UpdateByID(id);

        }

    }

this is model class

            package com.ge.health.poc.model;

            import javax.persistence.Column;
            import javax.persistence.Entity;
            import javax.persistence.Id;
            import javax.persistence.Table;

            @Entity
            @Table(name = "spring_model")
            public class SpringModel {

                @Id
                private Long id;

                @Column
                private String name;

                public Long getId() {
                    return id;
                }

                public void setId(Long id) {
                    this.id = id;
                }

                @Column
                private String isbn;

                @Override
                public String toString() {
                    return "SpringModel [id=" + id + ", name=" + name + ", isbn=" + isbn + ", author=" + author + ", pages=" + pages
                            + "]";
                }

                @Column
                private String author;

                @Column
                private String pages;

                public String getName() {
                    return name;
                }

                public void setName(String name) {
                    this.name = name;
                }

                public String getIsbn() {
                    return isbn;
                }

                public void setIsbn(String isbn) {
                    this.isbn = isbn;
                }

                public String getAuthor() {
                    return author;
                }

                public void setAuthor(String author) {
                    this.author = author;
                }

                public String getPages() {
                    return pages;
                }

                public void setPages(String pages) {
                    this.pages = pages;
                }
            }

Solution 1

Try the annotation @Modifying(org.springframework.data.jpa.repository.Modifying) on the repository methods and @Transactional(org.springframework.transaction.annotation.Transactional) in service implementation which does DML operation. please refer this answer for more information.

Solution 2

By Default spring jpa will think query is select query.So, To make sure the query is updating the existed row for a particular entity

add @modifying Annotation on the method which is updating the existed row

This might works for you

Comments

[Nikhil Sahu]

I already have the modifying annotation on delete query. It still throws the same error.

[tk_]

@NikhilSahu you might be missing the Transactional in the service implmentation, Please refer this answer : stackoverflow.com/a/47066201/3168721