[FIXED] exception while using nativeJPQL delete Query

Issue

I tried to delete using nativeQuery but I have this exсeption:

org.springframework.dao.InvalidDataAccessApiUsageException: Executing an update/delete query; nested exception is javax.persistence.TransactionRequiredException: Executing an update/delete query
    at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:403)
    ...................
Caused by: javax.persistence.TransactionRequiredException: Executing an update/delete query
    at org.hibernate.internal.AbstractSharedSessionContract.checkTransactionNeededForUpdateOperation(AbstractSharedSessionContract.java:413)
    at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1668)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
...............

My Employe Entity:

@Entity
public class Employe implements Serializable{

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    
    private String prenom;
    
    private String nom;
    
    private String email;
    
    private int isActif;
    
    @Enumerated(EnumType.STRING)
    private Role role;

    @OneToOne(mappedBy ="employe", cascade = CascadeType.REMOVE)
    Contrat contrat;
    
    @ManyToMany( mappedBy="employes", fetch= FetchType.EAGER , cascade = {CascadeType.PERSIST })
    private List<Departement> departements;
    
    @ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE ,CascadeType.REMOVE})
    List<Mission> missions;
.........

My Department Entity:

@Entity
public class Departement implements Serializable{

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    
    private String name;
    
    @ManyToOne( cascade = {CascadeType.PERSIST, CascadeType.MERGE} )
    private Entreprise entreprise;
    
    @OneToMany( mappedBy="departement", cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    private List<Mission> missions;
    
    @ManyToMany(fetch = FetchType.EAGER, cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    private List<Employe> employes;
    

My Service :

@Autowired IEmployeRepository empRep; ...... @Override public void deleteEmployerById(int id) { Employe e = empRep.findById(id).orElse(null); // remove all associations for this author empRep.deleteEmployerDepartementsAssociations(id); empRep.delete(e); }

My EmployeRepository:

@Repository public interface IEmployeRepository extends CrudRepository<Employe, Integer>{

 @Modifying
 @Query(value = "DELETE FROM departement_employes WHERE employes_id = :id ", nativeQuery = true)
 void deleteEmployerDepartementsAssociations(@Param("id") int id );
 

}

Only the delete Query do not work , I am trying to delete all the departments that are associated with a certain employe.

The association for the employe and the departments is many to many bidirectional association.

I am trying to delete the associations before deleting the employe

Solution

Try to correct your query in this way:

@Modifying
@Query(value = "DELETE FROM departement_employes WHERE employes_id = :id ", nativeQuery = true)
void deleteEmployerDepartementsAssociations(@Param("id") int id );

You have to wrap any database-modifying operation in a transaction. Look for example to this article.

So, as one of possible approaches you can annotate your service method by @Transactional annotation:

@Override
@Transactional
public void deleteEmployerById(int id) {
   // ...
}

Answered By - SternK