Search code examples
javasqldatabasehibernatehql

Output of one user with two roles

I have two entities User and Role. They have a unidirectional ManyToMany connection. But when calling the list of Users, for some reason the User is returned several times with several roles.

Below I have provided the code and the result.

Result

new pic

Дебаг

new pic

Code with HQL queries

 public List<User> listUsers() {
     List resultList = manager.createQuery("SELECT u FROM User u LEFT JOIN FETCH u.roles").getResultList();
     return resultList;
 }

UPD:

I found this solution:

public List<User> listUsers() {
    List resultList = manager.createQuery("SELECT u FROM User u LEFT JOIN FETCH u.roles")
            .unwrap(org.hibernate.Query.class).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
            .getResultList();
    return resultList;
}

But the criteria API is slow, it is not recommended to use it, and it is deprecated.

How do I display one user with two roles???

Solution

  • Need to add a distinct statement in your query to avoid multiple entries

     public List<User> listUsers() {
     List resultList = manager.createQuery("SELECT distinct u FROM User u LEFT JOIN FETCH u.roles").getResultList();
     return resultList;
 }