I have a problem with Jpa Specification.
My specification looks like this:
public static Specification<PersonView> getByFilter(PersonViewFilter filter) {
return (root, query, criteriaBuilder) -> {
List<Predicate> predicates = new ArrayList<>();
SetJoin<PersonView, PersonDetailsView> personDetailsJoin =
root.join(PersonView_.personDetails);
Path<String> namePath = personDetailsJoin.get(PersonDetailsView_.name);
Path<String> surnamePath = personDetailsJoin.get(PersonDetailsView_.surname);
predicates.add(namePredicate(personDetailsJoin, criteriaBuilder, filter.getName()));
predicates.add(surnamePredicate(personDetailsJoin, criteriaBuilder, filter.getSurname()));
predicates.add(peselPredicate(personDetailsJoin, criteriaBuilder, filter.getPesel()));
predicates.removeAll(Collections.singleton(EMPTY_PREDICATE));
query.orderBy(List.of(criteriaBuilder.asc(namePath), criteriaBuilder.asc(surnamePath))).distinct(true);
return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
};
}
When I try search results I can see an error: ORA-01791: not a SELECTed expression
The problem is related to generated sql by JpaSpecificationExecutor. It looks like this:
select
*
from
( select distinct pv.person_id
from
personview pv
inner join
persondetailsview pdv on pv.person_id=pdv.person_id
where
1=1
order by
pdv.name asc,
pdv.surname asc )
where
rownum <= ?
In Select clausule should be added name and surname then it would work, but I don't know how to do this in Specification. Without distinct query works, but i don't have duplicates. Please for help.