Problem creating query involving a composite key

dm_tim · **Joined:** Sat Jan 24, 2009 1:32 pm **Posts:** 9

Hello,

I'm having trouble writing a simple join query between two tables. Note that I inherited a db with composite keys on all tables that include:
int id
in classId

So all of my entity classes start out like this:

Code:

@Entity
@Table(name = "rp.batch")
public class HBatch  {
   private HBatchId key;
   public HBatch() {

   }

   // Property accessors
   @EmbeddedId

   @AttributeOverrides( {
      @AttributeOverride(name="id", column=@Column(name="id", unique=false, nullable=false, insertable=true, updatable=true) ), 
      @AttributeOverride(name="classId", column=@Column(name="classId", unique=false, nullable=false, insertable=true, updatable=true) ) } )
      public HBatchId getKey() {
      return this.key;
   }
   public void setKey(HBatchId key) {
      this.key = key;
   }
...

With of course, the requisite key class:

Code:

@Embeddable
public class HBatchId implements java.io.Serializable {
   // Fields    
   private int id;
   private int classId;
...

I have two classes in particular, HBatch and HTransfer. I need to write a query in hibernate that looks like this in normal SQL:

Code:

select b.* from rp.batch b, rp.transfer t where t.batchId = b.id and t.batchClassId = b.classId and t.invoiceId = 109

First of all, the table transfer has both parts of the composite key that belongs to batch, transfer.batchId and transfer.batchClassId both are foreign keys to the batch.id and batch.classId columns, though I do not know how or if that should be represented in the transfer entity class. Secondly that the last value (109) actually should be a variable.

I could handle the simple join IF it weren't for the fact that this was a friggin' composite key. Could someone please help me with a hibernate query of any sort that will produce the same results as the sql mentioned above?

Setup Info:
Hibernate version: Core 3.3.1 GA, Annotations 3.4.0

Mapping documents: hibernate.cfg.xml

Regards,

Tim

littypreethkr · **Posted:** Sun Feb 08, 2009 4:23 am

Did you try this one -

Code:

select HBatch from HBatch inner join HTransfer on HBatch.key.id = HTransfer.batchId.key.id AND HBatch.key.classId = HTransfer.batchId.key.classId AND HTransfer.invoiceId  = 109

dm_tim · **Joined:** Sat Jan 24, 2009 1:32 pm **Posts:** 9

littypreethkr wrote:

Did you try this one -

Code:

select HBatch from HBatch inner join HTransfer on HBatch.key.id = HTransfer.batchId.key.id AND HBatch.key.classId = HTransfer.batchId.key.classId AND HTransfer.invoiceId  = 109

Thank you but it's not quite right. Here's a snippet from HTransfer:

Code:

   @Column(name = "batchId")
   private Long batchId;

   @Column(name = "batchClassId")
   private Long batchClassId;

When I use the suggested query I get the following:

Code:

Exception in thread "main" org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 59 [from com.rentpayment.entities.HBatch inner join HTransfer on HBatch.key.id = HTransfer.batchId AND HBatch.key.classId = HTransfer.batchClassId AND HTransfer.invoiceId  =:id]
   at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)

Could you suggest something else?

Regards,

Tim

littypreethkr · **Posted:** Mon Feb 09, 2009 3:40 am

Hi,

As per the hibernate reference pdf any foreign keys referencing the HBactch table must also be composite.
I dont know which one is ur Many to one relation, im assuming Transfer to Btach is a many to one; so the HTransfer table referencing the HBatch table should be like:

Code:

<many-to-one name="batch" class="HBatch"
         cascade="none" fetch="join" lazy="false" access="property">
         <column name="BATCH_ID" />
         <column name="BATCH_CLASS_ID" />
</many-to-one>

Please convert it to applicable annotations.

Then you can write the query as:

Code:

select batch from HTransfer where invoiceId=109

dm_tim · **Joined:** Sat Jan 24, 2009 1:32 pm **Posts:** 9

Thanks, where can I find some documentation on how to convert your XML to annotations?

Regards,

Tim

littypreethkr · **Posted:** Mon Feb 09, 2009 4:35 am

Well I dont hav much idea on annotations... So may be you ll have to check the annotation reference for converting this. Or may be some one else can help you here.

dm_tim · **Joined:** Sat Jan 24, 2009 1:32 pm **Posts:** 9

OK, I added a new 'column' in my HTransfer class to represent the HBatch class:

Code:

   @Column(name = "hBatch")
   private HBatch hBatch;
   
   @Column(name = "batchId")
   private Integer batchId;

   @Column(name = "batchClassId")
   private Integer batchClassId;

Note I'm showing the batchId and batchClassId just to see the 3 together.

Then I added the getter and setter:

Code:

   @OneToOne(cascade = CascadeType.ALL)
   @PrimaryKeyJoinColumn
   public HBatch getHBatch() {
       return hBatch;
   }

   public void setHBatch(HBatch hBatch) {
        this.hBatch = hBatch;
   }

Then I attempt the following query:

Code:

   HBatch hBatch = (HBatch)session.createQuery("select HBatch from HTransfer t where t.invoiceId = :invoiceId").setParameter("invoiceId", invoiceId).uniqueResult();

Which returns a null HBatch object. The parameter I send in for invoiceId = 221741 which should work since the following query on the db returns valid data:

Code:

select batchId, batchClassId from rp.transfer t where t.invoiceId = 221741

So, can anyone determine what I am doing wrong here?

Regards,

Tim

littypreethkr · **Posted:** Tue Feb 10, 2009 12:42 am

Quote:

Code:

@Column(name = "hBatch")
   private HBatch hBatch;

I referred the annotations reference document for you and it is not that way how you map Many to one columns. You will need something like:

Code:

@ManyToOne
    @JoinColumns ({
        @JoinColumn(name="batchId", referencedColumnName = "id"),
        @JoinColumn(name="batchClassId", referencedColumnName = "classId")
    })
private HBatch hBatch;

Please refer this for more info:
http://www.hibernate.org/hib_docs/annot ... ml#d0e1700