CODEWITHSUNDEEP
javamysqldatabasehibernatehql
Maria Laura Bennato
Maria Laura Bennato

Reputation: 49

Hibernate: how get attributes from a query's result?

I am trying to execute the below mentioned query, which works in mysql but not on Hibernate(hql) the result isn't correct. The result of my query must be one record but in Hibernate I get two records. Is it possible that Hibernate does not execute the group by (in my query) and so the result is made up by two ids?

Query query = session.createQuery("select avg(valore)as punteggio,id,  from Voto  where STR_TO_DATE(datav,'%Y-%m-%d')> CURRENT_DATE - 7 group by id order by punteggio DESC");

List<Object[]> list = query.list();
if(list.size()> 0)
{ 
    System.out.println(list.size());
    for (Object[] aRow : list) {
        Integer sum = (Integer) aRow[0];
        Integer category = (Integer) aRow[1];
        System.out.println(category +" - " + sum);
    }
}

This is my Hibernate code

@Entity
@Table(name="Voto")

public class Voto{
    @Id
     @GeneratedValue
    @Column(name = "idv")
    private int idv;
    @Column(name = "valore")
    private int valore;
    @Column(name = "datav")
    private String datav;
     @ManyToOne
        @JoinColumn(name = "idp")
        private Pubblico pubblico;
     @ManyToOne
        @JoinColumn(name = "id")
        private Artista artista;
    public Voto(int idv,int valore,String datav,Pubblico pubblico, Artista artista){
        this.idv=idv;
        this.valore=valore;
        this.datav=datav;
        this.pubblico=pubblico;
        this.artista=artista;
    }

    public Voto() {
        // TODO Auto-generated constructor stub
    }

    public int getidv() {
        return idv;
    }
    public void setidv(int idv) {
        this.idv = idv;
    }
    public int getvalaore() {
        return valore;
    }
    public void setvalore(int valore) {
        this.valore = valore;
    }
    public String getdatav() {
        return datav;
    }
    public void setdatav(String datav) {
        this.datav= datav;
    }
    public Pubblico getPubblico() {
        return pubblico;
    }
    public void setPubblico(Pubblico pubblico) {
        this.pubblico = pubblico;
    }
    public Artista getArtista() {
        return artista;
    }
    public void setArtista(Artista artista) {
        this.artista = artista;
    }


}

Please help me.

Upvotes: 0

Views: 711

Answers (1)

Dean Clark
Dean Clark

Reputation: 3878

I would add the id column to your Entity object

@Column(name = "id", insertable=false, updateable=false)
private String id;

And then use a Criteria like this one:

List result = session.createCriteria(Voto.class)       
              .setProjection(Projections.projectionList()
                      .add(Projections.groupProperty("id"))
                      .add(Projections.avg("valore"))
              ).list();

Upvotes: 1

Related Questions