CODEWITHSUNDEEP
javahibernatelistduplicates
yann
yann

Reputation: 57

getresultlist - query ok but data duplicate in List

I have a problem with getResultList(). My query is OK when it's executed and return 700 results. In the return List, I had 700 results but the list contains duplicate data. So I do not have all results.

    public List<EscaleCatalogueKaravel> obtenirListeEscaleKaravelSelonMarche(Integer refMarche, Integer refLangue) {
    List<EscaleCatalogueKaravel> listeEscales = entityManager.createQuery("select distinct  p from EscaleCatalogueKaravel p " +
            "where p.refMarche=:refMarche and p.refLangue=:refLangue group by idEscale  ")
            .setParameter("refMarche", refMarche)
            .setParameter("refLangue", refLangue)
            .getResultList();

    if (listeEscales == null || listeEscales.size() == 0) {
        return null;
    }
    return listeEscales;

}

Have you got an idea ?

Upvotes: 0

Views: 1359

Answers (1)

Johanna
Johanna

Reputation: 5303

You're using MySQL, right? Oracle would not execute the query but throw an error instead.

For correct usage of the group by clause you're only allowed to select that rows (or expressions) which are also mentioned in the group by clause. If you select a row which is not in the group by clause, this row might have different values for the members of one group. Which of these values the database should return? MySQL arbitrarily returns one of the possible values, but that is not correct.

In your query you either only do select distinct idEscale from ... or you group by all necessary columns and only select that ones or you drop your group by clause. By the way, distinct also can be used without group by, and distinct only should be used if really necessary because it makes the query slow.

Upvotes: 1

Related Questions