I am learning Spring Data JPA and I am trying to build ManyToMany relation between class Account and class Authority (role). Apart from that, i want to track the history when the authority (role) has been revoked, so by database will contain attributes like: assigned_at, revoked_at.

This is my Account class: it will implement UserDetails and CredentialsContainer

@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;
}

This is 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;
}

This is relation class AccountAuthority:

@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;
}

Now when i fetch the list of accounts using JpaRepository:

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

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

The Hibernate has generated 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=?

Correct me if I am wrong, the way I understand it that when account is fetched it will fetch the list of authorities.

My question is is it possible to prevent fetching the list of authorities when fetching account, for example so that only this query is executed:

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

I tried setting fetch = FetchType.LAZY on all the @OneToMany and @ManyToOne, but that did not work.

Also, the database will contain multiple records of same account_id and authority_id since I want to track the history of authority (role) being revoked:

IMAGE OF TABLE – open here

Is it possible to alter the authorities fetching so that only authorities where revoked_at is 9999-12-31 23:59:59 are fetched, something like this:

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 


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 revoked_at='9999-12-31 23:59:59'

I tried using JPA specifications, but didn’t work the way I wanted.