-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 
Author Message
 Post subject: Join between entities with composite-id using criteria
PostPosted: Wed Aug 24, 2011 10:07 am 
Newbie

Joined: Tue Aug 16, 2011 7:52 am
Posts: 3
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):
Image

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.


Top
 Profile  
 
 Post subject: Re: Join between entities with composite-id using criteria
PostPosted: Wed Aug 24, 2011 10:45 am 
Newbie

Joined: Thu Jul 14, 2011 11:36 am
Posts: 9
It looks like it is my case.

My workaround is creation a read-only property in the main bean.

For your case

Code:
public class ItemA implements Serializable {

   @EmbeddedId
   public ItemAId getId() {
      return this.id;
   }

   //bi-directional many-to-one association to A
   @ManyToOne(fetch=FetchType.LAZY)
   @JoinColumn(name="dfs_seq",insertable=false, updatable=false)
   public A getA() {
      return this.a;
   }
}

And use ItemA .a in all queries for mapping


Top
 Profile  
 
 Post subject: Re: Join between entities with composite-id using criteria
PostPosted: Thu Aug 25, 2011 8:41 am 
Newbie

Joined: Tue Aug 16, 2011 7:52 am
Posts: 3
Thank you Logger,

Your workaround really works but I had to change the EmbeddedId to not use any other entities as key, and mapped the ManyToOne with insertable=false, updatable=false just like on your post.

Code:
public class itemAId{

   [...]
   @Column(name="dfs_seq")
   public Integer getDfsSeq(){
      return this.dfsSeq;
   }

   [...]
   @Column(name="nrs_seq")
   public Integer getNrsSeq(){
      return this.nrsSeq;
   }

}


Thank you mate.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.