When using the annotation {{@BatchSize(size = 10)}} for {{@OneToMany}} collection , we need to specify the size parameter, and if, when loading the collections of the passed ids, more than the specified size, then several queries will be executed.
{noformat}@Entity
@Table(name = "CLIENT")
class ClientEntity(
@Id
@Column(name = "ID")
var id: Long? = null,
@OneToMany(mappedBy = "client")
@BatchSize(size = 10)
var accounts: Set<AccountEntity> = mutableSetOf()
){noformat}
Select clients:
{noformat}fun getAllBy(pageable: Pageable): Page<ClientEntity>{noformat}
Result in logs:
{code:sql}Hibernate: select * from client c offset ? rows fetch first ? rows only
Hibernate: select * from account a where a.client_id in (?, ?, …)
Hibernate: select * from account a where a.client_id in (?, ?, …){code}
To improve performance, it would be better if additional queries were not created. For this, the best option would be if additional requests request with received ids returned all the required result. How about creating an annotation of similar functionality (example {{ @AllLoadable }} ) that would not accept a without size parameter and the request could be like:
{code:sql}select * from client c where c.id in (select unnest(array [1, 2, 3, 4])) // Works in postgresql for example {code}
In some databases there is a limitation in the size of the in clause, passing an array as one parameter should help to the in clause should help to work around this limitation.
[https://hibernate.atlassian.net/browse/HHH-1123|https://hibernate.atlassian.net/browse/HHH-1123|smart-link] |
|
