org.hibernate.hql.ast.QuerySyntaxException: Unable to locate appropriate constructor

Question

I have 2 tables mapped to entities User and Group.

@Entity
@Table(name = "T_COM_USER")
public class User {
    private String userName;
    @Column(name="F_USERNAME", length=60)
    public String getUserName() {
        return userName;
    }
    public void setUserName(String userName) {
        this.userName = userName;
    }
}

@Entity
@Table(name="T_COM_USERGROUP")
public class UserGroup{
    private String groupName;
    @Column(name="F_GROUPNAME", length=60)
    public String getGroupName() {
        return groupName;
    }
    public void setGroupName(String groupName) {
        this.groupName = groupName;
    }
}

One User can have many Groups. I need to model them in order to display the users with all their groups in a table in the frontend like below:

+-------+----------------------+
| Users |        Groups        |
+-------+----------------------+
| User1 | Group1,Group2,Group3 |
| User2 | Group1,Group2        |
| User3 | Group2,Group4        |
+-------+----------------------+

So I created this DTO:

public class UserGroupsBean {
    private List<String> groupName;
    private String userName;
    public UserGroupsBean(String userName, List<String> groupName) {
        this.userName = userName;
        this.groupName = groupName;
    }
    // Getters
    public List<String> getGroupName() { return groupName; }
    public String getUserName() { return userName; }            
    // Setters
    public void setGroupName(List<String> groupName) { this.groupName = groupName; }
    public void setUserName(String userName) { this.userName = userName;}
}

I used this query to return all the groups for each user:

String hql = "select new odatabase.service.beans.UserGroupsBean(userName,(select groupName from UserGroup) ) from User";

But I got:

org.hibernate.hql.ast.QuerySyntaxException: Unable to locate appropriate constructor on class [UserGroupsBean]

Although I have a constructor UserGroupsBean(String userName, List < String > groupName)

How is this caused and how can I solve it ?

Answer 1

The JPQL constructor expression only supports flat results, so you are out of luck. You could use a query like SELECT u.userName, g.groupName FROM User u JOIN u.userGroups g but then you'd have to reduce the result set on your own.

What you want to do can be nicely done with Blaze-Persistence Entity Views. Here some example code.

@EntityView(User.class)
public interface UserGroupsBean {
    // The id of the user
    @IdMapping("id") int getId();

    String getUserName();

    @Mapping("userGroups.name")
    List<String> getGroupNames();
}

This is essentially a DTO with some metadata. And here is the code for querying:

EntityManager entityManager = // jpa entity manager
CriteriaBuilderFactory cbf = // query builder from Blaze-Persistence
EntityViewManager evm = // manager that can apply entity views to query builders

CriteriaBuilder<User> builder = cbf.create(entityManager, User.class);
List<UserGroupsBean> result = evm.applySetting(
    builder, 
    EntityViewSetting.create(UserGroupsBean.class)
).getResultList();

This will generate a query similar to this

SELECT u.id, u.userName, g.groupName FROM User u JOIN u.userGroups g

and will map the result for you in the DTO automatically.