Gerd Michalke

Greenhorn
+ Follow
since Sep 05, 2006
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by Gerd Michalke

Hi

I have been googling for some days, but without result

I have one table whose fields are

DATE (DATE)
PLACE (INTEGER)
AMOUNT (INTEGER)

In plain sql, I need SELECT date,place,sum(amount) FROM table GROUP BY date,place ORDER BY place,date;

I tried to make a native query

List l = em.createNativeQuery(zeQuery).getResultList();

This makes a list of Object whose size is the number of records which would be have been returned by the query.

How can I use this resultset ?

I have been searching for days, but have found nothing interesting (apart from query examples, but the query is ok; what I can't get is how to use the result)

Thanks in advance
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;
}

}