Hi everybody,
I'm having trouble creating a query using criteria, when there is an entity which connects two other entities in two ManyToOne relantionships, just like shown on this image (I've changed tha name of the original tables and entities):
My criteria looks like that:
Code:
DetachedCriteria criIndf = DetachedCriteria.forClass(ItemAItemB.class,"IAB");
ProjectionList p = Projections.projectionList();
p.add(Projections.property("IAB." + SceInrIdf.Fields.QUANTIDADE.toString()));
criIndf.setProjection(p);
criIndf.createAlias("IAB." + ItemAItemB.Fields.ItemA.toString(), "IA", Criteria.INNER_JOIN);
criIndf.createAlias("IA." + ItemA.Fields.A.toString(), "A", Criteria.INNER_JOIN);
criIndf.createAlias("IAB." + ItemAItemB.Fields.ItemB.toString(), "IB", Criteria.INNER_JOIN);
criIndf.createAlias("IB." + ItemB.Fields.B.toString(), "B", Criteria.INNER_JOIN);
criIndf.add(Restrictions.eqProperty("B." + B.Fields.SEQ.toString(),seqB));
criIndf.add(Restrictions.eqProperty("A." + A.Fields.SEQ.toString(),seqA));
Executing this generates a query without the joins:
Code:
SELECT this_.quantidade
FROM ItemAItemB this_
WHERE B.seq= seqB
AND A.seq = seqA
Notice that there are no join clause on that query but the conditions on where clause references fields of those other entities. This query executed throws an exception:
Quote:
ERROR: missing FROM-clause entry for table "B"
Entities:
Code:
public class A implements Serializable {
[...]
@Id
@SequenceGenerator(name="A_SEQ_GENERATOR", sequenceName="SCE_DFE_SQ1")
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="A_SEQ_GENERATOR")
public Integer getSeq() {
return this.seq;
}
[...]
//bi-directional many-to-one association to ItemA
@OneToMany(mappedBy="id.a")
public Set<A> getA() {
return this.a;
}
}
public class ItemA implements Serializable {
@EmbeddedId
public ItemAId getId() {
return this.id;
}
}
@Embeddable
public class ItemAId implements Serializable {
//bi-directional many-to-one association to A
@ManyToOne
@JoinColumn(name="dfs_seq")
public A getA() {
return this.a;
}
public Integer numero(){
return this.numero;
}
}
public class B implements Serializable {
@Id
@SequenceGenerator(name="B_SEQ_GENERATOR", sequenceName="SCE_NRS_SQ1")
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="B_SEQ_GENERATOR")
public Integer getSeq() {
return this.seq;
}
@OneToMany(mappedBy="id.b", cascade={CascadeType.ALL})
public Set<ItemB> getItemB() {
return itemB;
}
}
public class ItemB implements Serializable {
@EmbeddedId
public ItemBId getId() {
return this.id;
}
public Integer numero(){
return this.numero;
}
}
@Embeddable
public class ItemBId implements Serializable {
//bi-directional many-to-one association to B
@ManyToOne
@JoinColumn(name="nrs_seq")
public B getB() {
return this.b;
}
}
public class ItemAItemB implements Serializable {
public Integer getQuantidade() {
return this.quantidade;
}
@EmbeddedId
public ItemAItemBId getId() {
return this.id;
}
}
@Embeddable
public class ItemAItemBId implements Serializable {
//bi-directional many-to-one association to B
@ManyToOne
@JoinColumns({
@JoinColumn(name="nrs_seq", referencedColumnName="iaf_afn_numero", nullable=false, insertable=false, updatable=false),
@JoinColumn(name="b_numero", referencedColumnName="iaf_numero", nullable=false, insertable=false, updatable=false)
})
public ItemB getItemB() {
return this.itemB;
}
//bi-directional many-to-one association to A
@ManyToOne
@JoinColumns({
@JoinColumn(name="dfs_seq", referencedColumnName="iaf_afn_numero", nullable=false, insertable=false, updatable=false),
@JoinColumn(name="a_numero", referencedColumnName="iaf_numero", nullable=false, insertable=false, updatable=false)
})
public A getA() {
return this.a;
}
}
I believe that the problems is related to the composite-id of ItemAItemBId entity, cause I have no problem to generate a query where only A and ItemA or B and ItemB are involved.
Does anyone has ever been in this situation and found a solution to that?
Thank you.