EJB3: Using NativeNamedQueries and grouping functions

[Gerd Michalke]

Hi,

Using: JBoss 4.0.4GA, MySQL 5 on Linux

I need to make a native named query in order to use some MySQL specific stuff. As you can see from the query, I just need to sum up some records based on a grouping.

The result I expect is a List of objects with 4 attributes: Place(endroit), month, year and a quantity.

Hereunder, you see the entity class I created in order to store the info.

When I run this query, Hibernate/JBoss complains that no ID is defined, what is true, but I don't see how to define an ID for a record which does not exist.

Thanks in advance for any help.

package sli.formulaires.entity;

import javax.persistence.Entity;
import javax.persistence.EntityResult;
import javax.persistence.FieldResult;
import javax.persistence.Id;
import javax.persistence.NamedNativeQuery;
import javax.persistence.SqlResultSetMapping;

@Entity
@NamedNativeQuery(name="nbQuestionnairesParEndroitEtDate",
query="SELECT COUNT(*) AS nb, endroit AS endr, MONTH(dateeven) AS m, YEAR(dateeven) AS y from questionnaires group by m,y, endroit order by endroit,y,m",
resultSetMapping="NbQuestionnairesParEndroitEtDateMapping")

@SqlResultSetMapping(name="NbQuestionnairesParEndroitEtDateMapping", entities={
@EntityResult(entityClass=sli.formulaires.entity.NbQuestionnairesParEndroitEtDate.class, fields = {
@FieldResult(name="nombre", column="nb"),
@FieldResult(name="endroit", column="endr"),
@FieldResult(name="mois", column="m"),
@FieldResult(name="annee", column="a")
})
})

public class NbQuestionnairesParEndroitEtDate implements java.io.Serializable
{
private Integer nombre;
private Integer endroit;
private Integer mois;
private Integer annee;


/** Creates a new instance of NbQuestionnairesParEndroitEtDate */
public NbQuestionnairesParEndroitEtDate()
{

}

public Integer getNombre()
{
return nombre;
}

public void setNombre(Integer nombre)
{
this.nombre = nombre;
}

public Integer getEndroit()
{
return endroit;
}

public void setEndroit(Integer endroit)
{
this.endroit = endroit;
}

public Integer getMois()
{
return mois;
}

public void setMois(Integer mois)
{
this.mois = mois;
}

public Integer getAnnee()
{
return annee;
}

public void setAnnee(Integer annee)
{
this.annee = annee;
}

}