CODEWITHSUNDEEP
javasqlhqlcriteria
ZaoTaoBao
ZaoTaoBao

Reputation: 2615

Hql query to Criteria query

I have this sentence that's works correctly in HQL:

String queryString = 

"SELECT gmr.id.expedientId.idEns as idEns,"
            + " gmr.id.expedientId.anyExp as anyExp,"
            + " gmr.id.expedientId.numExp as numExp,"
            + " gmr.id.numOrdre as numOrdre,"
            + " gmr.idRecursRebuig.desRecursRebuig as descripcioRebuig,"
            + " gmr.desAmpliRebuig as observacionsRebuig,"
            + " gmr.tipusRebuig as tipusRebuig"
            + " FROM GirMotiuRebuig gmr"
            + " where gmr.id.expedientId.idEns =:idEns and"
            + " gmr.id.expedientId.anyExp =:anyExp and"
            + " gmr.id.expedientId.numExp =:numExp"
            + " ORDER BY gmr.id.numOrdre";

            Query queryObject = getSessionFactory().getCurrentSession()
                    .createQuery(queryString);

            queryObject.setResultTransformer(Transformers
                    .aliasToBean(MotiusRebuig.class));

            queryObject.setParameter("idEns",clauExpedient.getIdEns());
            queryObject.setParameter("anyExp",clauExpedient.getAnyExp());
            queryObject.setParameter("numExp",clauExpedient.getNumExp());

            llistaMotiusRebuig =queryObject.list();

and my criteria version: 

Criteria consulta = this.getSessionFactory().getCurrentSession()
    .createCriteria(GirMotiuRebuig.class)
    .add(Restrictions.eq("id.expedientId.idEns", clauExpedient.getIdEns()))
    .add(Restrictions.eq("id.expedientId.anyExp", clauExpedient.getAnyExp()))
    .add(Restrictions.eq("id.expedientId.numExp", clauExpedient.getNumExp()))
    .addOrder(Order.asc("id.numOrdre"))
    .setProjection(Projections.projectionList()
    .add(Projections.property("id.expedientId.idEns"),"idEns")
    .add(Projections.property("id.expedientId.anyExp"),"anyExp")
    .add(Projections.property("id.expedientId.numExp"),"numExp")
    .add(Projections.property("id.numOrdre"),"numOrdre")
        .add(Projections.property("idRecursRebuig.desRecursRebuig"),"descripcioRebuig")
    .add(Projections.property("desAmpliRebuig"),"observacionsRebuig")
    .add(Projections.property("tipusRebuig"),"tipusRebuig")
    .add(Projections.property("expedientId.numOrdre"),"numOrdre"))
                .setResultTransformer(
                Transformers.aliasToBean(MotiusRebuig.class));
    llistaMotiusRebuig =  consulta.list();

And exception:

Caused by: org.hibernate.QueryException: could not resolve property: idRecursRebuig.desRecursRebuig of: cat.base.gir.logica.domini.impl.GirMotiuRebuig
        at org.hibernate.persister.entity.AbstractPropertyMapping.propertyException(AbstractPropertyMapping.java:44)
        at org.hibernate.persister.entity.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:38)
        at org.hibernate.persister.entity.AbstractEntityPersister.toType(AbstractEntityPersister.java:1362)
        at org.hibernate.loader.criteria.CriteriaQueryTranslator.getType(CriteriaQueryTranslator.java:477)
        at org.hibernate.criterion.PropertyProjection.getTypes(PropertyProjection.java:36)
        at org.hibernate.criterion.AliasedProjection.getTypes(AliasedProjection.java:37)
        at org.hibernate.criterion.ProjectionList.getTypes(ProjectionList.java:38)
        at org.hibernate.loader.criteria.CriteriaQueryTranslator.getProjectedTypes(CriteriaQueryTranslator.java:318)
        at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:77)
        at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:59)
        at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:67)
        at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1550)
        at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
        at cat.base.gir.dao.GirMotiuRebuigDao.obtenirMotiusRebuig(GirMotiuRebuigDao.java:73)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
        at $Proxy1183.obtenirMotiusRebuig(Unknown Source)
        at cat.base.gir.serveis.GirRecursService.obtenirMotiusRebuig(GirRecursService.java:381)
        ... 127 more

Entity: GirMotiuRebuig

@Entity
@Table(name = "gir_exp_motiu_rebuig")
public class GirMotiuRebuig implements IGirMotiuRebuig {

    /**
     * 
     */
    private static final long serialVersionUID = -5587115692447683913L;

    private ExpedientIdMultiple id;
    private MRecursRebuig idRecursRebuig;
    private String desAmpliRebuig;
    private GirExpedient expedientGir;
    protected Integer tipusRebuig;

    public GirMotiuRebuig() {
    }

    public GirMotiuRebuig(ExpedientIdMultiple id, MRecursRebuig idRecursRebuig, String desAmpliRebuig,
            GirExpedient expedientGir) {
        this.id = id;
        this.idRecursRebuig = idRecursRebuig;
        this.desAmpliRebuig = desAmpliRebuig;
        this.expedientGir = expedientGir;
    }

    @EmbeddedId
    @AttributeOverrides( {
            @AttributeOverride(name = "expedientId.idEns", column = @Column(name = "id_ens", nullable = false, precision = 5, scale = 0)),
            @AttributeOverride(name = "expedientId.anyExp", column = @Column(name = "any_exp", nullable = false, precision = 4, scale = 0)),
            @AttributeOverride(name = "expedientId.numExp", column = @Column(name = "num_exp", nullable = false, precision = 10, scale = 0)),
            @AttributeOverride(name = "numOrdre", column = @Column(name = "num_ordre", nullable = false, precision = 10, scale = 0)) })
    public ExpedientIdMultiple getId() {
        return id;
    }

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

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "id_recurs_rebuig")
    public MRecursRebuig getIdRecursRebuig() {
        return idRecursRebuig;
    }

    public void setIdRecursRebuig(MRecursRebuig idRecursRebuig) {
        this.idRecursRebuig = idRecursRebuig;
    }

    @Column(name = "des_ampli_rebuig", nullable = false, length = 250)
    public String getDesAmpliRebuig() {
        return desAmpliRebuig;
    }

    public void setDesAmpliRebuig(String desAmpliRebuig) {
        this.desAmpliRebuig = desAmpliRebuig;
    }

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns( {
            @JoinColumn(name = "id_ens", referencedColumnName = "id_ens", nullable = false, insertable = false, updatable = false),
            @JoinColumn(name = "any_exp", referencedColumnName = "any_exp", nullable = false, insertable = false, updatable = false),
            @JoinColumn(name = "num_exp", referencedColumnName = "num_exp", nullable = false, insertable = false, updatable = false) })
    public GirExpedient getExpedientGir() {
        return expedientGir;
    }

    public void setExpedientGir(GirExpedient expedientGir) {
        this.expedientGir = expedientGir;
    }

    @Column(name = "tipus_rebuig", nullable = false)
    public Integer getTipusRebuig() {
        return tipusRebuig;
    }

    public void setTipusRebuig(Integer tipusRebuig) {
        this.tipusRebuig = tipusRebuig;
    }

well, this criteria works:

Criteria consulta = this.getSessionFactory().getCurrentSession()
                .createCriteria(GirMotiuRebuig.class, "g")
                    .add(Restrictions.eq("g.id.expedientId.idEns", clauExpedient.getIdEns()))
                    .add(Restrictions.eq("g.id.expedientId.anyExp", clauExpedient.getAnyExp()))
                    .add(Restrictions.eq("g.id.expedientId.numExp", clauExpedient.getNumExp()))
                    .addOrder(Order.asc("g.id.numOrdre"))
                .setProjection(Projections.projectionList()
                        .add(Projections.property("g.id.expedientId.idEns"),"idEns")
                        .add(Projections.property("g.id.expedientId.anyExp"),"anyExp")
                        .add(Projections.property("g.id.expedientId.numExp"),"numExp")
                        .add(Projections.property("g.desAmpliRebuig"),"observacionsRebuig")
                        .add(Projections.property("g.id.numOrdre"),"numOrdre")
                        .add(Projections.property("g.tipusRebuig"),"tipusRebuig")
                        )
                .setResultTransformer(
                        Transformers.aliasToBean(MotiusRebuig.class));
        llistaMotiusRebuig =  consulta.list();

but when i try to attack "idRecursRebuig.desRecursRebuig as descripcioRebuig" like:

Criteria consulta = this.getSessionFactory().getCurrentSession()
                    .createCriteria(GirMotiuRebuig.class, "g")
                        .add(Restrictions.eq("g.id.expedientId.idEns", clauExpedient.getIdEns()))
                        .add(Restrictions.eq("g.id.expedientId.anyExp", clauExpedient.getAnyExp()))
                        .add(Restrictions.eq("g.id.expedientId.numExp", clauExpedient.getNumExp()))
                        .addOrder(Order.asc("g.id.numOrdre"))
                    .setProjection(Projections.projectionList()
                            .add(Projections.property("g.id.expedientId.idEns"),"idEns")
                            .add(Projections.property("g.id.expedientId.anyExp"),"anyExp")
                            .add(Projections.property("g.id.expedientId.numExp"),"numExp")
                            .add(Projections.property("g.desAmpliRebuig"),"observacionsRebuig")
                            .add(Projections.property("g.id.numOrdre"),"numOrdre")
                            .add(Projections.property("g.tipusRebuig"),"tipusRebuig")
                            **.add(Projections.property("g.idRecursRebuig.desRecursRebuig"),"descripcioRebuig")**
                            )
                    .setResultTransformer(
                            Transformers.aliasToBean(MotiusRebuig.class));
            llistaMotiusRebuig =  consulta.list();

the query fails... 

nested exception is org.hibernate.QueryException: could not resolve property: idRecursRebuig.desRecursRebuig of: cat.base.gir.logica.domini.impl.GirMotiuRebuig

so:the problem is when i try to recuperate idRecurs...so..i need extra cafein...

this is the mapping of MRecursRebuig:

@Entity
@Table(name = "MRecursRebuig")
public class MRecursRebuig implements IMRecursRebuig {

    /**
     * 
     */
    private static final long serialVersionUID = 7439723582297629986L;

    private Integer idRecursRebuig;
    private String desRecursRebuig;
    private Integer tipusRebuig;

    public MRecursRebuig() {
    }

    public MRecursRebuig(Integer idRecursRebuig, String desRecursRebuig) {
        this.idRecursRebuig = idRecursRebuig;
        this.desRecursRebuig = desRecursRebuig;
    }

    @Id
    @Column(name = "id_recurs_rebuig", unique = true, nullable = false, precision = 22, scale = 0)
    public Integer getIdRecursRebuig() {
        return idRecursRebuig;
    }

    public void setIdRecursRebuig(Integer idRecursRebuig) {
        this.idRecursRebuig = idRecursRebuig;
    }

    @Column(name = "des_recurs_rebuig", nullable = false, length = 100)
    public String getDesRecursRebuig() {
        return desRecursRebuig;
    }

    public void setDesRecursRebuig(String desRecursRebuig) {
        this.desRecursRebuig = desRecursRebuig;
    }

    @Column(name = "tipus_rebuig", nullable = false)
    public Integer getTipusRebuig() {
        return tipusRebuig;
    }

and if i try this:

Criteria consulta = this.getSessionFactory().getCurrentSession()
                    .createCriteria(GirMotiuRebuig.class, "g")
                    .createAlias("g.idRecursRebuig", "idRecursRebuig")
                        .add(Restrictions.eq("g.id.expedientId.idEns", clauExpedient.getIdEns()))
                        .add(Restrictions.eq("g.id.expedientId.anyExp", clauExpedient.getAnyExp()))
                        .add(Restrictions.eq("g.id.expedientId.numExp", clauExpedient.getNumExp()))
                        .add(Restrictions.eq("g.idRecursRebuig", clauExpedient.getNumExp()))
                        .addOrder(Order.asc("g.id.numOrdre"))
                    .setProjection(Projections.projectionList()
                            .add(Projections.property("g.id.expedientId.idEns"),"idEns")
                            .add(Projections.property("g.id.expedientId.anyExp"),"anyExp")
                            .add(Projections.property("g.id.expedientId.numExp"),"numExp")
                            .add(Projections.property("g.desAmpliRebuig"),"observacionsRebuig")
                            .add(Projections.property("g.id.numOrdre"),"numOrdre")
                            .add(Projections.property("g.tipusRebuig"),"tipusRebuig")
                            **.add(Projections.property("idRecursRebuig.desRecursRebuig"),"descripcioRebuig")**
                            )
                    .setResultTransformer(
                            Transformers.aliasToBean(MotiusRebuig.class));
            llistaMotiusRebuig =  consulta.list();

I receive: 

nested exception is org.hibernate.PropertyAccessException: IllegalArgumentException occurred calling getter of cat.base.gir.logica.domini.impl.MRecursRebuig.idRecursRebuig
    cat.base.gir.serveis.GirRecursService.obtenirMotiusRebuig(GirRecursService.java:387)

Upvotes: 0

Views: 572

Answers (2)

ZaoTaoBao
ZaoTaoBao

Reputation: 2615

well I feel closer to monkeys every day, finally the solution is:

Criteria consulta = this.getSessionFactory().getCurrentSession()
                    .createCriteria(GirMotiuRebuig.class, "g")
                    .createAlias("idRecursRebuig", "i")
                        .add(Restrictions.eq("g.id.expedientId.idEns", clauExpedient.getIdEns()))
                        .add(Restrictions.eq("g.id.expedientId.anyExp", clauExpedient.getAnyExp()))
                        .add(Restrictions.eq("g.id.expedientId.numExp", clauExpedient.getNumExp()))
                        .addOrder(Order.asc("g.id.numOrdre"))
                    .setProjection(Projections.projectionList()
                            .add(Projections.property("g.id.expedientId.idEns"),"idEns")
                            .add(Projections.property("g.id.expedientId.anyExp"),"anyExp")
                            .add(Projections.property("g.id.expedientId.numExp"),"numExp")
                            .add(Projections.property("g.desAmpliRebuig"),"observacionsRebuig")
                            .add(Projections.property("g.id.numOrdre"),"numOrdre")
                            .add(Projections.property("g.tipusRebuig"),"tipusRebuig")
                            .add(Projections.property("i.desRecursRebuig"),"descripcioRebuig")
                            )
                    .setResultTransformer(
                            Transformers.aliasToBean(MotiusRebuig.class));
            llistaMotiusRebuig =  consulta.list();

Upvotes: 0

JB Nizet
JB Nizet

Reputation: 692181

In a HQL query, when you have something like 

select entityA.entityB.foo from EntityA entityA ...

you're in fact creating an implicit inner join between EntityA and EntityB. It's equivalent to

select entityB.foo from EntityA entityA
inner join entityA.entityB entityB ...

In Criteria queries, you can't chain properties like you're doing in HQL. Instead, each time a join is needed, you must create it explicitely, using a subCriteria or (my preference) an alias. So you need to do

Criteria consulta = this.getSessionFactory().getCurrentSession()
    .createCriteria(GirMotiuRebuig.class, "girMotiuRebuig")
    .createAlias("girMotiuRebuig.id.expedientId", "expedient") 
    .add(Restrictions.eq("expedient.idEns", clauExpedient.getIdEns()))
    ...

Note that your design and naming makes your code hard to understand. You should avoid composite keys by all means, and you shouldn't name idRecursRebuig a field which is actually an instance of MRecursRebuig, and is not an ID at all.

Upvotes: 2

Related Questions