I am learning Spring Data JPA and I am trying to build ManyToMany relation between class Account and class Authority. I also want to track the history of when the authority has been revoked. Database table "accounts_authorities" 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 WHERE revoked_at is null 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
@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;
}
@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;
}
@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;
}
@Repository
public interface AccountRepository extends JpaRepository<Account, UUID>, JpaSpecificationExecutor<Account> {
}
@Override
public List<Account> loadAllAccounts() {
return accountRepository.findAll();
}
Last couple of days I was playing with JPA and Hibernate, and I managed to find a way on how to "alter" the fetching of authorities list for this specific case, without a single SQL code written, pure Hibernate.
Relation between this two classes (Account, Authority) is M:M, but I also want to track the history of authority being revoked. For that reason the AccountAuthority class is required with revokedAt and assignedAt fields.
In current state, when the list of accounts are fetched with JpaRepository<Account> findAll(), Hibernate will also fetch the authorities... but it will fetch all records of authority, and I need just the authorities where revokedAt is null (which indicates that authority is still active).
I was trying to find a better solution, and if someone know the better way, I would be glad to hear... I'm not sure that this is the proper way of using JPA and Hibernate.
PROBLEM:
When JpaRepository<Account> findAll() is called:
@Service
public class AccountServiceImpl implements AccountService {
private final AccountRepository accountRepository;
@Autowired
public AccountServiceImpl(
final AccountRepository accountRepository
) {
this.accountRepository = accountRepository;
}
@Override
public List<Account> loadAllAccounts() {
return accountRepository.findAll();
}
}
The Hibernate generates this SQL:
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,
aa1_0.authority_id,
aa1_0.revoked_at
from
accounts_authorities aa1_0
where
aa1_0.account_id=?
I tried implementing JPA specification and writing a specification like this:
@Repository
public interface AccountRepository extends JpaRepository<Account, UUID>, JpaSpecificationExecutor<Account> {
}
public class AccountSpecification {
public static Specification<Account> withActiveAuthorities() {
return (root, query, cb) -> {
Join<Account, AccountAuthority> accountsAuthorityJoin = root.join("accountsAuthorities", JoinType.INNER);
return cb.and(cb.isNull(accountsAuthorityJoin.get("revokedAt")));
};
}
}
@Service
public class AccountServiceImpl implements AccountService {
private final AccountRepository accountRepository;
@Autowired
public AccountServiceImpl(
final AccountRepository accountRepository
) {
this.accountRepository = accountRepository;
}
@Override
public List<Account> loadAllAccounts() {
return accountRepository.findAll(withActiveAuthorities());
}
}
And you can see that Hibernate is now on right track:
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
join
accounts_authorities aa1_0
on a1_0.id=aa1_0.account_id
where
aa1_0.revoked_at is null
Hibernate:
select
aa1_0.account_id,
aa1_0.id,
aa1_0.assigned_at,
aa1_0.authority_id,
aa1_0.revoked_at
from
accounts_authorities aa1_0
where
aa1_0.account_id=?
Hibernate really added the WHERE revokedAt is null clause, but it only fetched the Account class WHERE revokedAt is null in AccountAuthorities, and not the Authotities WHERE revokedAt is null. After that Hibernate additionally fetches authorities without the filter WHERE revoked_at is null, and it fetches all records in database for that user. Even non active (In this example "MANAGER is non active role, later shown on image)
[
{
"id": "4c24d539-061c-4c4a-9b50-9abfed966bb2",
"firstName": "firstName",
"lastName": "lastName",
"username": "username",
"email": "email@email.com",
"createdAt": "2024-12-27T23:04:03.630992",
"authorities": [
"ADMIN",
"USER",
"MANAGER",
]
}
]
**SOLUTION: **
I found out that @EntityGraph exists, and I can order Hibernate what to fetch when fetching the account. (correct me if I'm wrong):
I override findAll() JPA method by ordering Hibernate to fetch accountsAuthorities and accountsAuthorities.authority (look up at Account class and AccountAuthority class)...
@Repository
public interface AccountRepository extends JpaRepository<Account, UUID>, JpaSpecificationExecutor<Account> {
@EntityGraph(attributePaths = {
"accountsAuthorities",
"accountsAuthorities.authority"
})
List<Account> findAll(Specification specification);
I leave the specification as it is above:
@Service
public class AccountServiceImpl implements AccountService {
private final AccountRepository accountRepository;
@Autowired
public AccountServiceImpl(
final AccountRepository accountRepository
) {
this.accountRepository = accountRepository;
}
@Override
public List<Account> loadAllAccounts() {
return accountRepository.findAll(withActiveAuthorities());
}
}
And this is the result:
Hibernate:
select
a1_0.id,
aa1_0.account_id,
aa1_0.id,
aa1_0.assigned_at,
a2_0.id,
a2_0.authority_name,
aa1_0.revoked_at,
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
join
accounts_authorities aa1_0
on a1_0.id=aa1_0.account_id
left join
authorities a2_0
on a2_0.id=aa1_0.authority_id
where
aa1_0.revoked_at is null
There is no "MANAGER" authority in response
[
{
"id": "4c24d539-061c-4c4a-9b50-9abfed966bb2",
"firstName": "firstName",
"lastName": "lastName",
"username": "username",
"email": "email@email.com",
"createdAt": "2024-12-27T23:04:03.630992",
"authorities": [
"ADMIN",
"USER"
]
}
]
Here is the image of records in database table accounts_authorities, where you can se that "MANAGER" authority was twice revoked from user on specific ID:
I hope that this all makes sense... maybe this helps to someone wondering how to filter and fetch using "pure" Hibernate and not writing single SQL code.
If someone has better solution, and someone know how actually this is done, let me know.
This is solely for educational purpose. I wanted to learn more about JPA and Hibernate magic. I wanted to learn on how to "take control" of Hibernate.I like programmatic queries like JPA specification, they seem so clean and secure, avoiding strings... but good "old" SQL would have solved this problem much sooner. Let me know that you think
