Generate query error table name not replaced by alias

Hibernate ORM
1

Hi i wrote a user role entities with @ManyToMany with soft delete like this:

@Entity
@Audited(targetAuditMode = NOT_AUDITED)
@Table(name = "users")
@Where(clause = "deleted = false")
@SQLDelete(sql = "UPDATE users SET deleted = true WHERE id = ? and version = ?")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ToString
public class UserEntity extends BaseEntity {
    @Column(length = 100)
    @NotNull
    @NotEmpty
    private String firstName;
    @Column(length = 100)
    @NotNull
    @NotEmpty
    private String lastName;
    @Column(length = 100,unique=true)
    @NotNull
    @NotEmpty
    private String email;
    @Column(length = 100)
    @NotNull
    @NotEmpty
    private String password;
    @Builder.Default
    private boolean enabled = false;
    @Builder.Default
    private boolean changePassword = true;
    private boolean tokenExpired;

    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(
        name = "users_roles",
        joinColumns = @JoinColumn( name = "user_id", referencedColumnName = "id"),
        inverseJoinColumns = @JoinColumn( name = "role_id", referencedColumnName = "id"))
    private Collection<RoleEntity> roles;
}

@Entity
@Table(name = "role")
@Where(clause = "deleted= false")
@SQLDelete(sql = "UPDATE role SET deleted = true WHERE id = ? and version = ?")
@Getter
@Setter
@Builder
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class RoleEntity extends BaseEntity {
    @NotNull
    @NotEmpty
    @Column(length=25,unique=true)
    private String name;
    @ManyToMany(mappedBy = "roles")
    private Collection<UserEntity> users;
}

All works until i try to hard delete a user (i want to keep soft delete for some times 5years and hard delete for RGPD purpose).

    @Transactional
    @Modifying
    @Query("delete from UserEntity where id =?1")
    void deleteRgpd(Long id);

Spring try to generate a query to delete user_roles in cascade and the query is:

delete from users_roles where users_roles.user_id in(select u1_0.id from users u1_0 where u1_0.id=? and (users.deleted = false))

JPA put “users.deleted” instead of “u1_0.deleted”

I also try with query:

        EntityManager em = entityManagerFactory.createEntityManager();
        em.getTransaction().begin();
        em.flush();
        em.clear();
        Query deleteUser = em.createQuery("DELETE FROM UserEntity WHERE id = :id");
        deleteUser.setParameter("id", user.getId());
        deleteUser.executeUpdate();

        em.getTransaction().commit();
        em.close();

Same issue
Do you have a workaround?

For information the select for user with roles:

select r1_0.user_id,r1_1.id,r1_1.created_by,r1_1.created_date,r1_1.deleted,r1_1.last_modified_by,r1_1.last_modified_date,r1_1.name,r1_1.version from users_roles r1_0 join role r1_1 on r1_1.id=r1_0.role_id where r1_0.user_id=? and ( r1_1.deleted= false)

is ok

And if possible a way to hard delete users_role as well?
If i not found any solution i will wrote a store procedure

I use Spring boot 3.0.2 Spring Cloud 2022.0.1 And spring-jpa (include hibernate)

2

Please create an issue in the issue tracker(https://hibernate.atlassian.net) with a test case(hibernate-test-case-templates/JPAUnitTestCase.java at main · hibernate/hibernate-test-case-templates · GitHub) that reproduces the issue.