JCH
Java Community | Help. Code. Learn.•2mo ago
circle

Bypass SQLRestriction to achieve SoftDelete with "hard delete"

I have this entity: 
@Entity
@SQLRestriction("deleted = false")
@Getter
@Setter
@EqualsAndHashCode(callSuper = true)
@ToString(callSuper = true)
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class Product extends Auditables {

    @Id
    @SnowFlakeIdValue(name = "product_id")
    @Column(name = "product_id", columnDefinition = "BIGINT", updatable = false, nullable = false)
    private Long productId;

    @Column
    private String name;

        ...

    @Column(name = "deleted", nullable = false)
    private boolean deleted = false;

    @Version
    private Long version;

    @OneToMany(mappedBy = "product", fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)
    @JsonManagedReference
    private Set<Variant> variants = new HashSet<>();

    @OneToMany(mappedBy = "product", fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)
    @JsonManagedReference
    private Set<ProductImage> images = new HashSet<>();

        ...
}
@Entity
@SQLRestriction("deleted = false")
@Getter
@Setter
@EqualsAndHashCode(callSuper = true)
@ToString(callSuper = true)
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class Product extends Auditables {

    @Id
    @SnowFlakeIdValue(name = "product_id")
    @Column(name = "product_id", columnDefinition = "BIGINT", updatable = false, nullable = false)
    private Long productId;

    @Column
    private String name;

        ...

    @Column(name = "deleted", nullable = false)
    private boolean deleted = false;

    @Version
    private Long version;

    @OneToMany(mappedBy = "product", fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)
    @JsonManagedReference
    private Set<Variant> variants = new HashSet<>();

    @OneToMany(mappedBy = "product", fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)
    @JsonManagedReference
    private Set<ProductImage> images = new HashSet<>();

        ...
}
11 Replies
JavaBot
JavaBot•2mo ago
⌛ This post has been reserved for your question.
Hey @circle! Please use /close or the Close Post button above when your problem is solved. Please remember to follow the help guidelines. This post will be automatically marked as dormant after 300 minutes of inactivity.
TIP: Narrow down your issue to simple and precise questions to maximize the chance that others will reply in here.
circle
circleOP•2mo ago
And in the product service layer: 
    @Override
    public void hardDeleteProduct(Long productId) {
        Product product = productRepository.findByIdUnfiltered(productId)
            .orElseThrow(() -> new ResourceNotFoundException("Product with ID '" + productId + "' not found"));

        productRepository.deleteProductPermanently(product.getProductId());
    }

    @Override
    public void hardDeleteAllProducts() {
        productRepository.deleteAllProductsPermanently();
    }

    @Override
    public void softDeleteProduct(Long productId) {
        Product product = productRepository.findById(productId)
            .orElseThrow(() -> new ResourceNotFoundException("Product with ID '" + productId + "' not found"));

        product.setDeleted(true);
        productRepository.save(product);
    }

    @Override
    public void softDeleteAllProducts() {
        List<Product> products = productRepository.findAll();
        products.forEach(product -> {
            product.setDeleted(true);
            productRepository.save(product);
        });
    }
    @Override
    public void hardDeleteProduct(Long productId) {
        Product product = productRepository.findByIdUnfiltered(productId)
            .orElseThrow(() -> new ResourceNotFoundException("Product with ID '" + productId + "' not found"));

        productRepository.deleteProductPermanently(product.getProductId());
    }

    @Override
    public void hardDeleteAllProducts() {
        productRepository.deleteAllProductsPermanently();
    }

    @Override
    public void softDeleteProduct(Long productId) {
        Product product = productRepository.findById(productId)
            .orElseThrow(() -> new ResourceNotFoundException("Product with ID '" + productId + "' not found"));

        product.setDeleted(true);
        productRepository.save(product);
    }

    @Override
    public void softDeleteAllProducts() {
        List<Product> products = productRepository.findAll();
        products.forEach(product -> {
            product.setDeleted(true);
            productRepository.save(product);
        });
    }
Tried deleting with this repository: 
public interface ProductRepository extends JpaRepository<Product, Long> {

    List<Product> findAllByNameLikeIgnoreCase(String name);

    List<Product> findAllByCategoryIs(Product.Category category);

    boolean existsByNameIgnoreCase(String name);

    @Query("SELECT p FROM Product p WHERE p.productId = :productId")
    @SQLRestriction("")
    Optional<Product> findByIdUnfiltered(@Param("productId") Long id);

    @Query("SELECT p FROM Product p")
    @SQLRestriction("")
    List<Product> findAllUnfiltered();

    @Modifying @Transactional
    @Query("DELETE FROM Product p WHERE p.productId = :productId AND p.deleted IN (true, false)")
    void deleteProductPermanently(@Param("productId") Long id);

    @Modifying @Transactional
    @Query("DELETE FROM Product p WHERE p.deleted IN (true, false)")
    void deleteAllProductsPermanently();
}
public interface ProductRepository extends JpaRepository<Product, Long> {

    List<Product> findAllByNameLikeIgnoreCase(String name);

    List<Product> findAllByCategoryIs(Product.Category category);

    boolean existsByNameIgnoreCase(String name);

    @Query("SELECT p FROM Product p WHERE p.productId = :productId")
    @SQLRestriction("")
    Optional<Product> findByIdUnfiltered(@Param("productId") Long id);

    @Query("SELECT p FROM Product p")
    @SQLRestriction("")
    List<Product> findAllUnfiltered();

    @Modifying @Transactional
    @Query("DELETE FROM Product p WHERE p.productId = :productId AND p.deleted IN (true, false)")
    void deleteProductPermanently(@Param("productId") Long id);

    @Modifying @Transactional
    @Query("DELETE FROM Product p WHERE p.deleted IN (true, false)")
    void deleteAllProductsPermanently();
}
Repository's .delete and .deleteAll only works with the filtered SQLRestriction. Thus, it will only delete products where deleted = false. That is not what I want. So I added custom queries: - deleteProductPermanently - deleteAllProductsPermanently , but it also throws: 
org.springframework.dao.DataIntegrityViolationException: JDBC exception executing SQL [delete from product p1_0 where p1_0.deleted in (true,false) and (p1_0.deleted = false)] [Referential integrity constraint violation: "FKJJPLLNLN6HK6HJ98UESGXNO00: PUBLIC.VARIANT FOREIGN KEY(PRODUCT_ID) REFERENCES PUBLIC.PRODUCT(PRODUCT_ID) (7388622269448192)"; SQL statement:
delete from product p1_0 where p1_0.deleted in (true,false) and (p1_0.deleted = false) [23503-224]] [n/a]; SQL [n/a]; constraint [FKJJPLLNLN6HK6HJ98UESGXNO00: PUBLIC.VARIANT FOREIGN KEY(PRODUCT_ID) REFERENCES PUBLIC.PRODUCT(PRODUCT_ID) (7388622269448192); SQL statement:
org.springframework.dao.DataIntegrityViolationException: JDBC exception executing SQL [delete from product p1_0 where p1_0.deleted in (true,false) and (p1_0.deleted = false)] [Referential integrity constraint violation: "FKJJPLLNLN6HK6HJ98UESGXNO00: PUBLIC.VARIANT FOREIGN KEY(PRODUCT_ID) REFERENCES PUBLIC.PRODUCT(PRODUCT_ID) (7388622269448192)"; SQL statement:
delete from product p1_0 where p1_0.deleted in (true,false) and (p1_0.deleted = false) [23503-224]] [n/a]; SQL [n/a]; constraint [FKJJPLLNLN6HK6HJ98UESGXNO00: PUBLIC.VARIANT FOREIGN KEY(PRODUCT_ID) REFERENCES PUBLIC.PRODUCT(PRODUCT_ID) (7388622269448192); SQL statement:
Or basically DataIntegrityViolationException that I thought it shouldn't happen since I've already used CascadeType.ALL and orphanRemoval in one-to-many relations within Product entity. This is just like using SoftDelete where I can't bypass it and Hard Delete the soft-deleted ones Is it the trade-off? The other solution would probably updating each products, or the deleted ones with soft-deletion = false so that it will also be picked up by the native .delete and .deleteAll THAT ALSO DOES NOT WORK I think it's the SQLRestriction making it hard I might need to manually do all the filtrations 😭
ayylmao123xdd
ayylmao123xdd•2mo ago
did you try writing a query without the query annotation like deletedWhereDeletedTrue so jpa generates it automatically or deleteByIdAndDeleted i think the latter is the correct format and if u dont wanna do by id do deleteByDeleted
circle
circleOP•2mo ago
Let me try For some cases yes
circle
circleOP•2mo ago
I think the SQLRestriction still restricts any query coming in :/ Tried deleteByProductIdAndDeletedTrue. I have yet found any way to bypass the SQLRestriction
No description
Virtual
Virtual•2mo ago
So in a nutshell when you try to delete product, you get a foreign key violation because variant for some reason is not being deleted?
ayylmao123xdd
ayylmao123xdd•2mo ago
did you try using the @where annotation instead of sql restriction also you should probably mark the deleting method with transactional
JavaBot
JavaBot•2mo ago
💤 Post marked as dormant
This post has been inactive for over 300 minutes, thus, it has been archived. If your question was not answered yet, feel free to re-open this post or create a new one. In case your post is not getting any attention, you can try to use /help ping. Warning: abusing this will result in moderative actions taken against you.
circle
circleOP•2mo ago
It does happen yes It is deprecated since JPA hibernate 6.4 Also did that, I put @Transactional on the service layer
circle
circleOP•2mo ago
Added the question to stackoverflow: https://stackoverflow.com/questions/79175803/bypassing-sqlrestriction-on-entities-for-jpa-queries
Stack Overflow
'Bypassing' @SQLRestriction on entities for JPA queries
Background I'm implementing both soft and hard delete functionality in a JPA/Hibernate application. Given an entity with SQLRestriction defined to restrict every query within the JPA to return non ...
JavaBot
JavaBot•2mo ago
💤 Post marked as dormant
This post has been inactive for over 300 minutes, thus, it has been archived. If your question was not answered yet, feel free to re-open this post or create a new one. In case your post is not getting any attention, you can try to use /help ping. Warning: abusing this will result in moderative actions taken against you.

Did you find this page helpful?