Hibernate criteria issue : could not resolve property:

Question

I'm facing a little issue : I want to select the campaigns where the average of campaignProgression equals 100 using criteria :

here is the query in SQL :

    SELECT * FROM campagne as camp, progression_campagne as campProg 
where camp.id = campProg.current_campaign and 
(SELECT avg(campaign_progression) FROM progression_campagne)=100 group by camp.id;

here is the translation in criteria :

Criteria crit = getSessionFactory().getCurrentSession().createCriteria(Campagne.class);
        crit.createAlias("progressionCampagnes", "prog");
        crit.setProjection(Projections.projectionList().add(Projections.groupProperty("prog.campagne")).add(Projections.avg("prog.campaignProgression"),"moy"));
        crit.add(Restrictions.eq("moy", new Float(100)));

But I'm getting this error :

org.hibernate.QueryException: could not resolve property: moy of: ma.dataprotect.sensipro.model.Campagne

Edited :

Here are the model classes of Campaign and ProgressionCampaign :

Campagne.java :

    package ma.dataprotect.sensipro.model;

// Generated 10 aoï¿½t 2015 14:36:11 by Hibernate Tools 3.4.0.CR1

import java.util.Date;
import java.util.HashSet;
import java.util.Set;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;

import static javax.persistence.GenerationType.IDENTITY;

import org.hibernate.Hibernate;
import javax.persistence.CascadeType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

/**
 * Campagne generated by hbm2java
 */
@SuppressWarnings("serial")
@Entity
@Table(name = "campagne")
public class Campagne implements java.io.Serializable {

    private Long id;
    private String name;
    private Organism organism;
    private byte[] image;
    private Date launchDate;
    private Date endDate;
    private String description;
    private Set<User> users = new HashSet<User>();
    private Set<ProgressionCampagne> progressionCampagnes = new HashSet<ProgressionCampagne>();
    private Set<ProgressionCours> progressionCourses = new HashSet<ProgressionCours>();
    private Long notificationid;
    private Boolean isStdr;
    private Set<Cours> courses = new HashSet<Cours>();

    public Campagne() {
    }

    public Campagne(String name, Date launchDate) {
        this.name = name;
        this.launchDate = launchDate;
    }

    public Campagne(String name, String description) {
        this.name = name;
        this.description = description;
    }

    public Campagne(String name, String description, byte[] image, Organism org) {
        this.name = name;
        this.description = description;
        this.image = image;
        this.organism = org;
    }

    public Campagne(String name, Date launchDate, Date endDate,
            String description, Set<User> users,
            Set<ProgressionCampagne> progressionCampagnes,
            Set<ProgressionCours> progressionCourses) {
        this.name = name;
        this.launchDate = launchDate;
        this.endDate = endDate;
        this.description = description;
        this.users = users;
        this.progressionCampagnes = progressionCampagnes;
        this.progressionCourses = progressionCourses;
    }

    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(name = "id", unique = true, nullable = false)
    public Long getId() {
        return this.id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    @Column(name = "name", nullable = false, length = 100)
    public String getName() {
        return this.name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Temporal(TemporalType.DATE)
    @Column(name = "launchDate", length = 10)
    public Date getLaunchDate() {
        return this.launchDate;
    }

    public void setLaunchDate(Date launchDate) {
        this.launchDate = launchDate;
    }

    @Temporal(TemporalType.DATE)
    @Column(name = "endDate", length = 10)
    public Date getEndDate() {
        return this.endDate;
    }

    public void setEndDate(Date endDate) {
        this.endDate = endDate;
    }

    @Column(name = "description")
    public String getDescription() {
        return this.description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    @Column(name = "notificationid")
    public Long getNotificationid() {
        return notificationid;
    }

    public void setNotificationid(Long notificationid) {
        this.notificationid = notificationid;
    }

    @ManyToMany
    @JoinTable(name = "user_campagne", joinColumns = { @JoinColumn(name = "campagneid", nullable = false, updatable = true) }, inverseJoinColumns = { @JoinColumn(name = "userid", nullable = false, updatable = true) })
    public Set<User> getUsers() {
//      Hibernate.initialize(users);
        return this.users;
    }

    public void setUsers(Set<User> users) {
        this.users = users;
    }

    @OneToMany(mappedBy = "campagne")
    public Set<ProgressionCampagne> getProgressionCampagnes() {
        return this.progressionCampagnes;
    }

    public void setProgressionCampagnes(
            Set<ProgressionCampagne> progressionCampagnes) {
        this.progressionCampagnes = progressionCampagnes;
    }

    @OneToMany(mappedBy = "campagne")
    public Set<ProgressionCours> getProgressionCourses() {
        return this.progressionCourses;
    }

    public void setProgressionCourses(Set<ProgressionCours> progressionCourses) {
        this.progressionCourses = progressionCourses;
    }

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "organismsid", nullable = false)
    public Organism getOrganism() {
        return this.organism;
    }

    public void setOrganism(Organism organism) {
        this.organism = organism;
    }

    @Column(name = "image", length = 5242880)
    public byte[] getImage() {
        return this.image;
    }

    public void setImage(byte[] image) {
        this.image = image;
    }

    @Column(name = "isStdr", nullable = false, length = 0, columnDefinition = "bit")
    public Boolean getIsStdr() {
        return isStdr;
    }

    public void setIsStdr(Boolean isStdr) {
        this.isStdr = isStdr;
    }

    @ManyToMany(cascade = { CascadeType.MERGE }, fetch = FetchType.LAZY)
    @JoinTable(name = "campagne_cours", joinColumns = { @JoinColumn(name = "campagne_id") }, inverseJoinColumns = { @JoinColumn(name = "cours_id") })
    public Set<Cours> getCourses() {
        return courses;
    }

    public void setCourses(Set<Cours> courses) {
        this.courses = courses;
    }


    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((id == null) ? 0 : id.hashCode());
        return result;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        Campagne other = (Campagne) obj;
        if (id == null) {
            if (other.id != null)
                return false;
        } else if (!id.equals(other.id))
            return false;
        return true;
    }

}

ProgressionCampagne.java

    package ma.dataprotect.sensipro.model;

// Generated 10 aoï¿½t 2015 14:36:11 by Hibernate Tools 3.4.0.CR1

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;

import static javax.persistence.GenerationType.IDENTITY;

import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

/**
 * ProgressionCampagne generated by hbm2java
 */
@SuppressWarnings("serial")
@Entity
@Table(name = "progression_campagne")
public class ProgressionCampagne implements java.io.Serializable {

    private Long id;
    private Campagne campagne;
    private User user;
    private Cours cours;
    private float campaignProgression;
    private float campaignScore;
    private Float campagneCorrectAnswer;
    private Long campagneNbrEssai;
    private Date dateDebutCamp;
    private Date dateFinCamp;

    public ProgressionCampagne() {

    }

    public ProgressionCampagne(Campagne campagne, User user, Cours cours,
             float campaignProgression, float campaignScore,
            Date dateDebutCamp, Date dateFinCamp) {
        super();
        this.campagne = campagne;
        this.user = user;
        this.cours = cours;
        this.campaignProgression = campaignProgression;
        this.campaignScore = campaignScore;
        this.dateDebutCamp = dateDebutCamp;
        this.dateFinCamp = dateFinCamp;
    }

    public ProgressionCampagne(Campagne campagne, User user, Cours cours,
             int campaignProgression) {
        this.campagne = campagne;
        this.user = user;
        this.cours = cours;
        this.campaignProgression = campaignProgression;
    }

    public ProgressionCampagne(Campagne campagne, User user, Cours cours,
             int campaignProgression,Date dateDebut) {
        this.campagne = campagne;
        this.user = user;
        this.cours = cours;
        this.campaignProgression = campaignProgression;
        this.dateDebutCamp=dateDebut;
    }

    public ProgressionCampagne(Campagne campagne, User user, 
             int campaignProgression,Date dateDebut) {
        this.campagne = campagne;
        this.user = user;
        this.campaignProgression = campaignProgression;
        this.dateDebutCamp=dateDebut;
    }

    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(name = "id", unique = true, nullable = false)
    public Long getId() {
        return this.id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    @ManyToOne
    @JoinColumn(name = "current_campaign", nullable = false)
    public Campagne getCampagne() {
        return this.campagne;
    }

    public void setCampagne(Campagne campagne) {
        this.campagne = campagne;
    }

    @ManyToOne
    @JoinColumn(name = "usersid", nullable = false)
    public User getUser() {
        return this.user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    @ManyToOne
    @JoinColumn(name = "current_course", nullable = true)
    // false
    public Cours getCours() {
        return this.cours;
    }

    public void setCours(Cours cours) {
        this.cours = cours;
    }



    @Column(name = "campaign_progression", nullable = false)
    public float getCampaignProgression() {
        return this.campaignProgression;
    }

    public void setCampaignProgression(float campaignProgression) {
        this.campaignProgression = campaignProgression;
    }

    @Column(name = "campaign_score", nullable = true)
    public float getCampaignScore() {
        return campaignScore;
    }

    public void setCampaignScore(float campaignScore) {
        this.campaignScore = campaignScore;
    }

    @Column(name = "date_debut_camp", nullable = true)
    public Date getDateDebutCamp() {
        return dateDebutCamp;
    }

    public void setDateDebutCamp(Date dateDebutCamp) {
        this.dateDebutCamp = dateDebutCamp;
    }

    @Column(name = "date_fin_camp", nullable = true)
    public Date getDateFinCamp() {
        return dateFinCamp;
    }

    public void setDateFinCamp(Date dateFinCamp) {
        this.dateFinCamp = dateFinCamp;
    }

    @Column(name = "campagne_correct_answer", nullable = true)
    public Float getCampagneCorrectAnswer() {
        return campagneCorrectAnswer;
    }

    public void setCampagneCorrectAnswer(Float campagneCorrectAnswer) {
        this.campagneCorrectAnswer = campagneCorrectAnswer;
    }

    @Column(name = "campagne_nbr_essai", nullable = true)
    public Long getCampagneNbrEssai() {
        return campagneNbrEssai;
    }

    public void setCampagneNbrEssai(Long campagneNbrEssai) {
        this.campagneNbrEssai = campagneNbrEssai;
    }

}

Please add Campagne and ProgressionCampagne entity classes, at least the referenced parts — Maciej Kowalski
– Maciej Kowalski, Commented Jan 21, 2017 at 15:00

Maciej Kowalski · Accepted Answer · 2017-01-23 14:01:36Z

1

Ok, in your criteria you have to use a subquery in order to achieve the HAVING feature. This is the code:

        DetachedCriteria innerCrit = DetachedCriteria.forClass(Campagne.class);
        innerCrit.createAlias("progressionCampagnes", "prog");
        innerCrit.setProjection(Projections.avg("prog.campaignProgression");
        innerCrit.add(Restrictions.eqProperty("id", "campOuter.id"));

        DetachedCriteriaouterCrit = DetachedCriteria.forClass(Campagne.class, "campOuter");
        outerCrit.add(Subqueries.eq(100, innerCrit));

This should get you the original sql result.

edited Jan 23, 2017 at 14:01

answered Jan 23, 2017 at 12:59

Maciej Kowalski

26.7k12 gold badges59 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

simoi chigo Over a year ago

hi, thank you for your answer .Firstly i think that innerCrit should be DetachedCriteria and secondly the innerCrit.add(Restrictions.eq("id", "campOuter.id")); is giving me a casting probleme [ cannot cast String to Long] do you have any idea why ?

Maciej Kowalski Over a year ago

Yes it may be a DetachedCrtieria. REgarding cast.. the you should use Restrictions.eqProperty instead of eq.. try now i have updated the post

simoi chigo Over a year ago

sorry to bother you again where to put the group by clause in the criteria it seems that you forget about it because the current critera give the AVG of all the rows in progressionCampagnes i need it to group by campaign if you know what i mean and thank you again !!!

Maciej Kowalski Over a year ago

add(Projections.groupProperty("prog.campagne"). Add this to the outer criteria

Collectives™ on Stack Overflow

Hibernate criteria issue : could not resolve property:

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related