In hibernate I want to run this JPQL / HQL query:
select new org.test.userDTO( u.id, u.name, u.securityRoles)
FROM User u
WHERE u.name = :name
userDTO class:
public class UserDTO {
private Integer id;
private String name;
private List<SecurityRole> securityRoles;
public UserDTO(Integer id, String name, List<SecurityRole> securityRoles) {
this.id = id;
this.name = name;
this.securityRoles = securityRoles;
}
...getters and setters...
}
User Entity:
@Entity
public class User {
@id
private Integer id;
private String name;
@ManyToMany
@JoinTable(name = "user_has_role",
joinColumns = { @JoinColumn(name = "user_id") },
inverseJoinColumns = {@JoinColumn(name = "security_role_id") }
)
private List<SecurityRole> securityRoles;
...getters and setters...
}
But when Hibernate 3.5 (JPA 2) starts I get this error:
org.hibernate.hql.ast.QuerySyntaxException: Unable to locate appropriate
constructor on class [org.test.UserDTO] [SELECT NEW org.test.UserDTO (u.id,
u.name, u.securityRoles) FROM nl.test.User u WHERE u.name = :name ]
Is a select that includes a list (u.securityRoles) as a result not possible? Should I just create 2 seperate queries?
The query without the NEW
(selecting a scalar value and a collection-valued path expression) isn't valid so I don't think that adding a NEW
will make things work.
For the record, this is what the JPA 2.0 specification says in the section 4.8 SELECT Clause:
The SELECT clause has the following syntax:
select_clause ::= SELECT [DISTINCT] select_item {, select_item}* select_item ::= select_expression [ [AS] result_variable] select_expression ::= single_valued_path_expression | scalar_expression | aggregate_expression | identification_variable | OBJECT(identification_variable) | constructor_expression constructor_expression ::= NEW constructor_name ( constructor_item {, constructor_item}* ) constructor_item ::= single_valued_path_expression | scalar_expression | aggregate_expression | identification_variable aggregate_expression ::= { AVG | MAX | MIN | SUM } ([DISTINCT] state_field_path_expression) | COUNT ([DISTINCT] identification_variable | state_field_path_expression | single_valued_object_path_expression)