Problem in Soft Deleting child entity in Spring Boot in @OneToMany relation

Question

I have started learning Spring Boot recently. I am trying to Soft delete the user. I want to soft delete all the Notes of the user when I soft delete the User. But my code is only soft deleting the user, not its notes. When I am doing soft delete only on notes table, it's working fine but on User Entity, it is only deleting the user.

Notes

package com.we.springmvcboot.Model;

import java.sql.Date;
import java.util.ArrayList;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.Table;

import org.hibernate.annotations.SQLDelete;
import org.hibernate.annotations.Where;

import com.fasterxml.jackson.annotation.JsonIgnore;
    
@Entity
@Table(name="Notes")
@Where(clause = "deleted = 'false'")//FALSE
public class Notes {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="NotesID")
    private long NotesID;
        
    @Column(name="Title")
    private String Title;
        
    @Column(name="Message")
    private String Message;
        
    @Column(name="Date")
    private String date;
        
    @Column(name="deleted")
    private String deleted="false";

    @Column(name="label")
    private int label=1;
    
    @ManyToOne()
    @JoinColumn(name = "UserID", nullable = false)

    private User user;
        
    public Notes() {}

    public String getDeleted() {
        return deleted;
    }    

    public void setDeleted(String deleted) {
        this.deleted = deleted;
    }    

    public Notes(String title, String message, String date, User user, int label) {
        super();
        Title = title;
        Message = message;
        this.date = date;
        this.user = user;
        this.label=label;
    }    

    public Notes(long notesID, String title, String message, String date, int label) {
        super();
        NotesID = notesID;
        Title = title;
        Message = message;
        this.date = date;
        this.label=label;
    }

    public int getLabel() {
        return label;
    }

    public void setLabel(int label) {
        this.label = label;
    }

    public long getNotesID() {
        return NotesID;
    }    

    public void setNotesID(long notesID) {
        NotesID = notesID;
    }    

    public String getTitle() {
        return Title;
    }    

    public void setTitle(String title) {
        Title = title;
    }    

    public String getMessage() {
        return Message;
    }    

    public void setMessage(String message) {
        Message = message;
    }    

    public String getDate() {
        return date;
    }    

    public void setDate(String date) {
        this.date = date;
    }

    public void setUser(User user) {
        this.user = user;
    }               
}

User

package com.we.springmvcboot.Model;

import java.util.ArrayList;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

import org.hibernate.annotations.Where;

import antlr.collections.List;

@Entity
@Table(name="User")
@Where(clause = "deleted = 'false'")//FALSE
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long UserID;
    
    @Column(name="emailid")
    private String emailID;

    @Column(name="deleted")
    private String deleted="false";
    
    @OneToMany(mappedBy="user", fetch = FetchType.EAGER,cascade=CascadeType.ALL, orphanRemoval=true)
    private Set<Notes> usernotes;
    
    public User() {}

    public User(String emailID) {
        super();
        this.emailID = emailID;
    }

    public long getUserID() {
        return UserID;
    }

    public void setUserID(long userID) {
        UserID = userID;
    }

    public String getemailID() {
        return emailID;
    }

    public void setemailID(String emailID) {
        emailID = emailID;
    }

    public Set<Notes> getUsernotes() {
        return usernotes;
    }

    public void setUsernotes(Set<Notes> usernotes) {
        this.usernotes = usernotes;
    }

}

NotesRepository

package com.we.springmvcboot.Repository;

import java.sql.Date;
import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import com.we.springmvcboot.Model.Notes;

@Repository
public interface NotesRepository extends JpaRepository<Notes, Long> {

    @Query("update Notes e set e.deleted='true' where e.NotesID=?1")
    @Transactional
    @Modifying
    public void softDelete(long id);
}

UserRepository

package com.we.springmvcboot.Repository;
    
import java.sql.Date;
import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import com.we.springmvcboot.Model.Notes;
import com.we.springmvcboot.Model.User;

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    List<User> findByEmailID(String email);

    @Query("update User e set e.deleted='true', where e.UserID=?1")
    @Transactional
    @Modifying
    public void softDelete(long id);
}

TodoService

package com.we.springmvcboot.Service;

import com.we.springmvcboot.Model.*;
import com.we.springmvcboot.exception.*;

import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.RequestBody;

import com.we.springmvcboot.Repository.NotesRepository;
import com.we.springmvcboot.Repository.UserRepository;

@Service
public class TodoService {

    @Autowired
    UserRepository userrepo;

    @Autowired
    NotesRepository notesrepo;


    public Object deleteUser(Map<String, Object> input) {
        long userID;
        userID = ((Number) input.get("userID")).longValue();
        userrepo.softDelete(userID);
        return null;
    }
}

Answer 1

I would suggest that you read this post for the simplest approach to soft delete. You should arrive at sth like the following:

@SQLDelete("UPDATE User SET deleted = TRUE WHERE id = ?")
@Where(clause = "deleted = FALSE")
public class User {

    @OneToMany(mappedBy = "user", fetch = EAGER, cascade=ALL, orphanRemoval = true)
    private Set<Notes> usernotes;

...
}

@SQLDelete("UPDATE Note SET deleted = TRUE WHERE id = ?")
@Where(clause = "deleted = FALSE")
public class Note {...}

The above will work if you use the following code to delete a User:

public Object deleteUser(Map<String, Object> input) {
        long userID;
        userID = ((Number) input.get("userID")).longValue();
        User user = userrepo.deleteById(userID);
        return null;
    }

If you want to keep using the custom query, though, you still need to call a separate query to delete Notes by userId, because Cascade.REMOVE will not be triggered in this case. In other words, you'll want a method like:

public interface NotesRepository extends JpaRepository<Notes, Long> {

   @Query("UPDATE Notes n SET n.deleted = TRUE WHERE n.user.id = :id")
   public void deleteByUserId(long id);
}

which you then call from deleteUser:

long userID;
userID = ((Number) input.get("userID")).longValue();
userrepo.softDelete(userID);
noteRepo.deleteByUserId(userID);
return null;