I am learning Spring Data JPA and I am trying to build ManyToMany relation between class Account and class Authority (role). I also want to track the history of when the authority (role) has been revoked. Database will contain attributes like: assigned_at, revoked_at.

What are the possible ways to customize fetching of auhorities when the account is fetched?

Currently the Hibernate generates this two SQL queries:

Hibernate: 
    select
        a1_0.id,
        a1_0.created_at,
        a1_0.email,
        a1_0.first_name,
        a1_0.hash_identifier,
        a1_0.last_name,
        a1_0.locked,
        a1_0.password,
        a1_0.username,
        a1_0.verified 
    from
        accounts a1_0
Hibernate: 
    select
        aa1_0.account_id,
        aa1_0.id,
        aa1_0.assigned_at,
        a2_0.id,
        a2_0.authority_name,
        aa1_0.revoked_at 
    from
        accounts_authorities aa1_0 
    left join
        authorities a2_0 
            on a2_0.id=aa1_0.authority_id 
    where
        aa1_0.account_id=?

How to customize the fetching of authorities, so that only active authorities are fetched. Something like adding revoked_at is null in WHERE clause:

Hibernate: 
    select
        a1_0.id,
        a1_0.created_at,
        a1_0.email,
        a1_0.first_name,
        a1_0.hash_identifier,
        a1_0.last_name,
        a1_0.locked,
        a1_0.password,
        a1_0.username,
        a1_0.verified 
    from
        accounts a1_0
Hibernate: 
    select
        aa1_0.account_id,
        aa1_0.id,
        aa1_0.assigned_at,
        a2_0.id,
        a2_0.authority_name,
        aa1_0.revoked_at 
    from
        accounts_authorities aa1_0 
    left join
        authorities a2_0 
            on a2_0.id=aa1_0.authority_id 
    where
        aa1_0.account_id=?
    and
        aa1_0.revoked_at is null

I would like to use JPA Specifications and Criteria Builder if that is what I should do. Or is there a way to customize fetching of authorities with just @OneToMany / @ManyToOne if that makes sense. I can’t find similar solution in official documentation

Account class:

@Entity
@Table(name = "accounts")
public class Account implements UserDetails, CredentialsContainer {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private UUID id;

    private String firstName;

    private String lastName;

    private String username;

    private String email;

    private String password;

    private LocalDateTime createdAt;

    private Boolean verified;

    private Boolean locked;

    private String hashIdentifier;

    @OneToMany(mappedBy = "account")
    private Set<AccountAuthority> accountsAuthorities;
}

Authority class:

@Entity
@Table(name = "authorities")
public class Authority {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private UUID id;

    private String authorityName;

    @OneToMany(mappedBy = "authority")
    private Set<AccountAuthority> accountsAuthorities;
}

AccountAuthority class:

@Entity
@Table(name = "accounts_authorities")
public class AccountAuthority implements GrantedAuthority {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private UUID id;

    @ManyToOne
    @JoinColumn(name = "account_id")
    private Account account;

    @ManyToOne
    @JoinColumn(name = "authority_id")
    private Authority authority;

    private LocalDateTime assignedAt;

    private LocalDateTime revokedAt;
}

JpaRepository:

@Repository
public interface AccountRepository extends JpaRepository<Account, UUID>, JpaSpecificationExecutor<Account> {
    Optional<Account> findByUsername(String username);
}

@Override
    public List<Account> loadAllAccounts() {
        return accountRepository.findAll();
    }