2

I have these three beans:

User.java

@Entity
@Table(name = "usuario")
public class User implements Serializable {

    private static final long serialVersionUID = 1L;

    private Set<Role> roles = new HashSet<Role>(0);
    private String login;
    private String password;
    private Integer id;
    private DestinationGroup destinationGroup;
    ....
    // Getters and setters

DestinationGroup.java

@Entity
@Table (name = "grupo_destinatario")
public class DestinationGroup implements Serializable {

    private static final long serialVersionUID = 1L;

    private String name;
    private Integer id;
    private Set<User> users = new HashSet<User>();
    ....
    // Getters and setters

Role.java

@Entity
@Table (name = "perfil")
public class Role extends AbstractModel implements Serializable {

    private static final long serialVersionUID = 1L;

    private String name;
    private Integer id;
    ....
    // Getters and setters

I want to get all users who don't have role "admin", ordered by the name of their destinationGroup. So I use this code:

UserTest.java

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<User> criteriaQuery = criteriaBuilder.createQuery(User.class);
Root<User> root = criteriaQuery.from(User.class);
Join<User, Role> path = root.join("roles", JoinType.LEFT);
Expression<Object> filter = path.get("name");
Predicate localPredicate = criteriaBuilder.notEqual(filter, "admin");
criteriaQuery.where(localPredicate);
Join<User, DestinationGroup> path2 = root.join("destinationGroup", JoinType.LEFT);
Expression<Object> sort = path2.get("name");
List<Order> orders = new ArrayList<Order>();
orders.add(criteriaBuilder.asc(sort));
criteriaQuery.orderBy(orders);
TypedQuery<User> query = entityManager.createQuery(criteriaQuery);
List<User> users = query.getResultList();

But that returns duplicated results if any user has multiple roles. So I add in the second line of the test code: ".distinct(true)" to retrieve only one result of each user:

CriteriaQuery<User> criteriaQuery = criteriaBuilder.createQuery(User.class).distinct(true);

Then an exception is thrown: "for SELECT DISTINCT, ORDER BY expressions must appear in select list". I can't remove the orderBy and I want to avoid duplicated results. How can I solve this? Can I add relationships to distinct?

I tried this solution but with JoinType.INNER I get duplicated results too.

Juan López Corominas
  • 21
  • 3
  • Distinct should fix your problem in general yes. What database are you using? Though I think I know what could work: Try to include the distinct inside the sql statement rather than using the method. There would be another way but that would be a serious case of "bad programming", what I mean with that you could put a filtering algorithm onto the arraylist you get from the bean. – Alexander Heim Sep 11 '17 at 09:50
  • Thnx for your response, but I prefer to use only criteria api. Anyway I learned that this can be done by changing the line 8 to: Join, ?> path2 = (Join,?) root.fetch("destinationGroup", JoinType.LEFT); – Juan López Corominas Sep 11 '17 at 16:21

0 Answers0