I have several records on my database table Foo and I need to aggregate a column to sum its values.
The entity that represents the table is this:
@Entity
@Table(name = "FOO")
@NamedQueries({
@NamedQuery(name = "Foo.total", query = "SELECT f.id, sum(f.someInt) AS
SOME_INT, f.parentId FROM Foo f where f.parentId = 'root' group by
p.parentId"),
@NamedQuery(name = "Foo.findAll", query = "SELECT f from Foo f") })
public class Foo {
@Id
@Column(name = "ID", insertable = false, updatable = false)
private String id;
@Column(name = "PARENT_ID", insertable = false, updatable = false)
private String parentId;
@Column(name = "SOME_INT", insertable = false, updatable = false)
private Integer someInt;
public Foo() {
}
}
And if I call this on my dao service bean:
entityManager.createNamedQuery("Foo.total", Foo.class).getResultList();
I get this error:
Caused by: java.lang.IllegalArgumentException: Cannot create TypedQuery for query with more than one return using requested result type [Foo]
It seems that JPA cannot match the return values to the object Foo. If is that so, how can I make this to work?
If I call the other @NamedQuery "Foo.findAll" it works fine.
SELECT f.id, sum(f.someInt) AS SOME_INT, f.parentId FROM Foo f
where f.parentId = 'root' group by p.parentId"
This query doesn't return a Foo entity but a projection instead.
You might want to create a custom POJO with the attributes you are selecting and use that as a return type when creating the query.