How to add distinct property to query with jpa specification

Question

I am using jhipster criteria and jpa specification to implement an endpoint for making research.

Well it is working, but keep sending me duplicates.

There is prestations with this model

public class Prestation extends AbstractAuditingEntity implements Serializable {

private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
@SequenceGenerator(name = "sequenceGenerator")
private Long id;

@NotNull
@Column(name = "jhi_label", nullable = false)
private String label;

@Column(name = "description")
private String description;

@Column(name = "unit")
private String unit;

@NotNull
@Column(name = "activated", nullable = false)
private boolean activated;

@ManyToOne(optional = false)
@NotNull
@JsonIgnoreProperties("prestations")
private SubCategory subCategory;

@OneToMany(mappedBy = "prestation", cascade = CascadeType.ALL, orphanRemoval = true)
private List<CompanyPrestation> companies = new ArrayList<>();

And relation between company and prestations

@OneToMany(mappedBy = "company", cascade = CascadeType.ALL, orphanRemoval = true)
@LazyCollection(LazyCollectionOption.FALSE)
private List<CompanyPrestation> prestations = new ArrayList<>();

And here is the CompanySpecification class that i used to create specification that i give to the repository

public class CompanySpecification extends QueryService<Company> implements Specification<Company> {

private static final long serialVersionUID = 1L;
private CompanyCriteria criteria;

public CompanySpecification(CompanyCriteria criteria) {
    this.criteria = criteria;
}

@Override
public Predicate toPredicate(Root<Company> roots, CriteriaQuery<?> query, CriteriaBuilder builder) {

    Specification<Company> specification = Specification.where(null);
    if (criteria != null) {
        if (criteria.getName() != null) {
            specification = specification.or(buildStringSpecification(criteria.getName(), Company_.name));
        }
        if (criteria.getSiret() != null) {
            specification = specification.or(buildStringSpecification(criteria.getSiret(), Company_.siret));
        }
        if (criteria.getCodeAPE() != null) {
            specification = specification.or(buildStringSpecification(criteria.getCodeAPE(), Company_.codeAPE));
        }
        if (criteria.getLegalForm() != null) {
            specification = specification.or(buildStringSpecification(criteria.getLegalForm(), Company_.legalForm));
        }
        if (criteria.getVille() != null) {
            specification = specification
                    .and(buildReferringEntitySpecification(criteria.getVille(), Company_.address, Address_.ville));
        }
        if (criteria.getExercicePlace() != null && !criteria.getExercicePlace().getIn().isEmpty()) {
            Filter<ExercicePlace> exercicePlaceFilter = new Filter<>();
            exercicePlaceFilter.setIn(criteria.getExercicePlace().getIn().stream().map(ExercicePlace::valueOf)
                    .collect(Collectors.toList()));
            specification = specification.and(buildSpecification(exercicePlaceFilter, Company_.exercicePlace));
        }
        if (criteria.getCodePostal() != null) {
            specification = specification.and(buildReferringEntitySpecification(criteria.getCodePostal(),
                    Company_.address, Address_.codePostal));
        }
        if (criteria.getPrestationsId() != null) {
            specification = specification.and(buildSpecification(criteria.getPrestationsId(),
                    root -> root.join(Company_.prestations, JoinType.LEFT)
                            .join(CompanyPrestation_.prestation, JoinType.LEFT).get(Prestation_.id)));
        }
        if (criteria.getCatId() != null) {
            specification = specification.and(buildSpecification(criteria.getCatId(),
                    root -> root.join(Company_.prestations, JoinType.LEFT)
                            .join(CompanyPrestation_.prestation, JoinType.LEFT)
                            .join(Prestation_.subCategory, JoinType.LEFT).join(SubCategory_.category, JoinType.LEFT)
                            .get(Category_.id)));
        }
        if (criteria.getSubCatId() != null) {
            specification = specification.and(buildSpecification(criteria.getSubCatId(),
                    root -> root.join(Company_.prestations, JoinType.LEFT)
                            .join(CompanyPrestation_.prestation, JoinType.LEFT)
                            .join(Prestation_.subCategory, JoinType.LEFT).get(SubCategory_.id)));
        }
        if (criteria.getPrestationName() != null) {
            specification = specification.or(buildSpecification(criteria.getPrestationName(),
                    root -> root.join(Company_.prestations, JoinType.LEFT)
                            .join(CompanyPrestation_.prestation, JoinType.LEFT).get(Prestation_.label)));
        }
        if (criteria.getPriceMinimum() != null || criteria.getPriceMaximum() != null) {
            specification = specification.and((lroot, lquery, lcriteriaBuilder) -> {
                ListJoin<Company, CompanyPrestation> joinCompnayToCompanyPrestations = lroot
                        .join(Company_.prestations, JoinType.LEFT);
                if (criteria.getPriceMinimum() != null && criteria.getPriceMaximum() != null) {
                    return lcriteriaBuilder.between(
                            joinCompnayToCompanyPrestations.get(CompanyPrestation_.pricePerUnit),
                            criteria.getPriceMinimum().getGreaterOrEqualThan(),
                            criteria.getPriceMaximum().getLessOrEqualThan()
                    );
                } else if (criteria.getPriceMinimum() != null) {
                    return lcriteriaBuilder.greaterThanOrEqualTo(
                            joinCompnayToCompanyPrestations.get(CompanyPrestation_.pricePerUnit),
                            criteria.getPriceMinimum().getGreaterOrEqualThan());
                } else {
                    return lcriteriaBuilder.lessThanOrEqualTo(
                            joinCompnayToCompanyPrestations.get(CompanyPrestation_.pricePerUnit),
                            criteria.getPriceMaximum().getLessOrEqualThan());
                }
            });
        }
    }
    return specification.toPredicate(roots, query, builder);
}

And here is my service method using it

@Transactional(readOnly = true)
public Page<CompanyDTO> findByCriteria(CompanyCriteria criteria, Pageable page) {
    log.debug("find by criteria : {}, page: {}", criteria, page);
    Specification<Company> spec = Specification.where(null);
    CompanySpecification specification =  new CompanySpecification(criteria);
    spec = (Specification<Company>) specification;
    Page<Company> vitrinesPage = companyRepository.findAll(spec, page);
    List<CompanyDTO> list = companyMapper.toDto(vitrinesPage.getContent());
    Page<CompanyDTO> listPage = new PageImpl<>(list, page, vitrinesPage.getTotalElements());
    return listPage;
}

I found a method distinct in CriteriaQuery but i dont really know how to add it in my specification.

Some help please!

buræquete · Accepted Answer · 2019-02-28T08:28:34.197

5

Inside of your toPredicate method, can you do;

@Override
public Predicate toPredicate(Root<Company> roots, CriteriaQuery<?> query, CriteriaBuilder builder) {
    ....
    query.distinct(true);
    return ...;
}

From > CriteriaQuery#distinct

Though what you are trying to do might not be possible, it is said to be a limitation of JPA

What you can try to do, is either remove duplicates within the code after fetch, or try to override equals & hashCode methods to try to hack the distinct definition maybe?

edited Feb 28 '19 at 08:28

answered Feb 26 '19 at 06:34

buræquete

14,226
4
44
89

Thanks! I did'nt thought about doing it there! Well i tried that but i keep getting duplicates! I copied the hibernate sql request and execute it in my pgAdmin and understood that the distinct is working but what i think are duplicates row are not! There is column like **price_per_unit** and **prestation_id** from companyPrestations which make the rows distinct – ouatrahim Feb 26 '19 at 07:54
@ouatrahim Oh of course, if you apply the distinct to a `SELECT * .` query, it wouldn't work since all rows have to be unique to begin with in a DB. Can you explain what do you need & what are the duplicates, with examples? Do you have multiple rows with same (`price_per_unit` & `prestation_id`) couples? And you want only one of them? Maybe you can try to select only those columns, & apply distinct to that result? Then you'd eliminate the duplicates. Or try to override `equals` & `hashCode` methods to use only your unique columns so if any two entity has same couple, they'd count as duplicate. – buræquete Feb 26 '19 at 08:04
You were right! I tried in pgAdmin with **select distinct company.* ** to eliminate potential problems columns and no more duplicate in the result from directly the db! I don't know yet how to translate this with criteriaQuery and specification and if the prestations of companies will still be mapped as i only select company column – ouatrahim Feb 26 '19 at 08:56
@ouatrahim it seems that is a limitation of specification feature in JPA, [check here](https://stackoverflow.com/questions/18300465/spring-data-jpa-and-querydsl-to-fetch-subset-of-columns-using-bean-constructor-p), can you try to override `equals` & `hashCode` methods as I said before? Maybe you can trick JPA to assume entities as duplicate with that? I am not sure how it check the equality, maybe tomorrow I can check the source code. – buræquete Feb 26 '19 at 17:46
Oh we finally found a solution but really not sexy! We take results with duplicates and use Stream groupingBy to eliminate duplicates! But it broke pagination so we trick it with some kind of custom pagination implementation using requested pagination option! Thanks again @buræquete ! – ouatrahim Feb 28 '19 at 02:27
@ouatrahim Oh, so not a really good SQL based solution? That must've been the worst case scenario, my suggestions did not work at all?? =( I thought method override might work as a hack... Thought to be honest, that would've been a non-SQL solution isn't it?... Difficult, unless you change the way you fetch, or how you store the data maybe. Keep the duplicate data in a separate table, and reference the same row from the currently duplicate containing rows. – buræquete Feb 28 '19 at 02:29
i could'nt make the overriding of equals ahd hashcode works :'( And yeah we tricked the system with a non sql solution. We could have write a solution with a sql query with parameters but well ... Thanks again! – ouatrahim Feb 28 '19 at 23:52

How to add distinct property to query with jpa specification

1 Answers1

Linked