I am using spring boot 3 with Jpa Specifications for build a dynamic query, Paging and Fetch Join together in order to avoid N+1 query problem. But pagination is applied at application level in memory and not a database level with query as it should be.
My User entity is
@Entity
@Table(name = "users")
public class User implements UserDetails, Serializable {
@Id
@GeneratedValue(strategy = GenerationType.UUID)
private UUID id;
@Column(name = "names")
private String names;
@ManyToMany(cascade = {
CascadeType.PERSIST,
CascadeType.MERGE
})
@JoinTable(name = "users_roles",
joinColumns = @JoinColumn(name = "user_id"),
inverseJoinColumns = @JoinColumn(name = "role_id"))
private Set<Role> roles = new HashSet<>();
...
}
My User repository is
@Repository
public interface UserRepository extends
JpaRepository<User, UUID>, JpaSpecificationExecutor<User> { ... }
My UserGateway uses UserRepository.findAll(Specification<T> spec, Pageable pageable)
@Repository
@RequiredArgsConstructor
public class UserGatewayImpl implements UserGateway {
private final UserRepository userRepository;
@Override
public Page<User> findByParams(UserSearchCommand searchCriteria, Pageable pageable) {
var userSpecification = buildCriteria(searchCriteria);
return userRepository.findAll(userSpecification, pageable);
}
}
My build criteria. In order to avoid "org.hibernate.query.SemanticException: query specified join fetching, but the owner of the fetched association was not present in the select list" exception because JPA executes 2 queries, I check resultType to know if it is the count query. Like in this answer Fetch join with paging solution
private Specification<User> buildCriteria(UserSearchCommand queryCriteria) {
return (root, criteriaQuery, criteriaBuilder) -> {
List<Predicate> predicates = new ArrayList<>();
if (Long.class != criteriaQuery.getResultType() &&
long.class != criteriaQuery.getResultType()) {
root.fetch("roles", JoinType.LEFT);
} else {
root.join("roles", JoinType.LEFT);
}
if (nonNull(queryCriteria.getNames())) {
predicates
.add(criteriaBuilder.and(
criteriaBuilder.equal(root.get("names"), queryCriteria.getNames())));
}
return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
};
}
That seems to work. The pagination "works". But when we take a look at the console and check the query generated by hibernate I see that the pagination is not being applied at the database level in query. Pagination is applied at application level in memory.
Generated query is
...
from
users u1_0
left join
(users_roles r1_0
join
roles r1_1
on r1_1.id=r1_0.role_id)
on u1_0.id=r1_0.user_id
where
1=1
order by
u1_0.id desc
I want to use JPA specifications, paging and FETCH JOIN together but paging is applied at application level in memory. Paging should be applied at database level in query.
