No matter what I try, I can't seem to get a particular Many to One relationship working with EJB 3. I have gotten this working with Hibernate in the past. Any help would be appreciated. When I print the LineItems, the product prints Null. When I do an update, the product part if stripped from the where causing it to return row count is wrong error.


import java.io.Serializable;

import javax.persistence.*;

@Entity
@Table(name="LINEITEM")
@NamedQuery(
name="findLineItemForProduct",
queryString="SELECT OBJECT(l) FROM LineItem as l INNER JOIN l.product as p WHERE l.orderId = :orderId AND p.sku = :productId")
public class LineItem implements Serializable {

private Product product;
private Integer orderId;
private Integer quantity;
private Integer total;




@Id
@Column(name="ORDER_ID")
public Integer getOrderId() {
return orderId;
}
public void setOrderId(Integer orderId) {
this.orderId = orderId;
}



@Column(name="QUANTITY")
public Integer getQuantity() {
return quantity;
}
public void setQuantity(Integer quantity) {
this.quantity = quantity;
}

@Column(name="AMOUNT")
public Integer getTotal() {
return total;
}
public void setTotal(Integer total) {
this.total = total;
}

@Id
@ManyToOne(fetch=FetchType.EAGER,optional=false,targetEntity=Product.class)
@JoinColumn(name="PRODUCT_ID", referencedColumnName="SKU",insertable=false,updatable=false, nullable=false,unique=true)
public Product getProduct() {
return product;
}
public void setProduct(Product product) {
this.product = product;
}

}


Other class:



import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

@Entity
@NamedQuery(
name="findAllProducts",
queryString="SELECT OBJECT(product) FROM Product product")
@Table(name="PRODUCT")
public class Product implements Serializable {
private Integer sku;
private String description;
private Integer cost;

@Column(name="PRICE")
public Integer getCost() {
return cost;
}
public void setCost(Integer cost) {
this.cost = cost;
}

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

@Id
@Column(name="SKU")
public Integer getSku() {
return sku;
}
public void setSku(Integer sku) {
this.sku = sku;
}

}


Relevant portion of existing DDL:

CREATE TABLE CUSTOMER_USER.PRODUCT (SKU INTEGER NOT NULL, DESC VARCHAR(250),PRICE INTEGER);
ALTER TABLE CUSTOMER_USER.PRODUCT ADD CONSTRAINT PK_PRODUCT PRIMARY KEY (SKU);

CREATE TABLE CUSTOMER_USER.CUSTOMER (NAME VARCHAR (250), CUST_ID INTEGER NOT NULL, OPEN_ORDER_ID INTEGER);
ALTER TABLE CUSTOMER_USER.CUSTOMER ADD CONSTRAINT PK_CUSTOMER PRIMARY KEY (CUST_ID);

CREATE TABLE CUSTOMER_USER.ORDER(ORDER_ID INTEGER NOT NULL, STATUS VARCHAR (250), TOTAL INTEGER, CUSTOMER_ID INTEGER NOT NULL);
ALTER TABLE CUSTOMER_USER.ORDER ADD CONSTRAINT PK_ORDER PRIMARY KEY (ORDER_ID);

CREATE TABLE CUSTOMER_USER.LINEITEM (ORDER_ID INTEGER NOT NULL, PRODUCT_ID INTEGER NOT NULL, QUANTITY INTEGER, AMOUNT INTEGER);
ALTER TABLE CUSTOMER_USER.LINEITEM ADD CONSTRAINT PK_ITEM PRIMARY KEY (ORDER_ID, PRODUCT_ID);

ALTER TABLE CUSTOMER_USER.CUSTOMER ADD CONSTRAINT FK_ORDER FOREIGN KEY (OPEN_ORDER_ID) REFERENCES CUSTOMER_USER.ORDER (ORDER_ID);
ALTER TABLE CUSTOMER_USER.ORDER ADD CONSTRAINT FK_CUSTOMER FOREIGN KEY (CUSTOMER_ID)REFERENCES CUSTOMER_USER.CUSTOMER (CUST_ID);
ALTER TABLE CUSTOMER_USER.LINEITEM ADD CONSTRAINT FK_PRODUCT FOREIGN KEY (PRODUCT_ID)REFERENCES CUSTOMER_USER.PRODUCT (SKU);
ALTER TABLE CUSTOMER_USER.LINEITEM ADD CONSTRAINT FK_ORDER FOREIGN KEY (ORDER_ID) REFERENCES CUSTOMER_USER.ORDER (ORDER_ID);

1. you have several @Id annotations per class, this is not (currently) allowed unless you define an @IdClass somewhere
2. You cannot combine @Id and @ManytoOne on the same property. I might relax that but the proper way for you is to define the PK as a regular property and map the @ManyToOne with @Joincolumn(insertable=false, updatable=false) with the same coluimn name

Thanks for the reply.

I made the change and I a sort of long generated SQL.


Here is the LineItem Key

import java.io.Serializable;


import javax.persistence.Embeddable;


@Embeddable
public class LineItemId implements Serializable {

private Integer orderId;
private Integer productId;
public LineItemId() {
super();
// TODO Auto-generated constructor stub
}
public Integer getOrderId() {
return orderId;
}
public void setOrderId(Integer orderId) {
this.orderId = orderId;
}

public Integer getProductId() {
return productId;
}
public void setProductId(Integer productId) {
this.productId = productId;
}
public boolean equals(Object arg0) {
if (arg0 == this) return true;
if (!(arg0 instanceof LineItemId)) return false;
LineItemId other = (LineItemId)arg0;
if(other.getOrderId().equals(orderId) && other.getProductId().equals(productId))
{
return true;
}
return false;

}
public int hashCode() {
return orderId + productId.hashCode();
}

}

here is the LineItem class


import java.io.Serializable;

import javax.persistence.*;

@Entity
@Table(name="LINEITEM")
@IdClass(LineItemId.class)
public class LineItem implements Serializable {

private Product product;
private LineItemId lineItemId;
private Integer quantity;
private Integer total;




@Column(name="ORDER_ID")
public Integer getOrderId(){
return lineItemId.getOrderId();
}


public void setOrderId(Integer orderId) {
lineItemId.setOrderId(orderId);
}


@Column(name="QUANTITY")
public Integer getQuantity() {
return quantity;
}
public void setQuantity(Integer quantity) {
this.quantity = quantity;
}

@Column(name="AMOUNT")
public Integer getTotal() {
return total;
}
public void setTotal(Integer total) {
this.total = total;
}

@ManyToOne(fetch=FetchType.EAGER,optional=false)
@JoinColumn(name="PRODUCT_ID", referencedColumnName="SKU",insertable=false,updatable=false, nullable=false,unique=true)
public Product getProduct() {
return product;
}
public void setProduct(Product product) {
this.product = product;
}

@Column(name="PRODUCT_ID")
public Integer getProductId() {
return lineItemId.getProductId();
}

public void setProductId(Integer productId) {
lineItemId.setProductId(productId);
}

@EmbeddedId
@AttributeOverrides({
@AttributeOverride(name = "orderId",column=@Column(name="ORDER_ID")),
@AttributeOverride(name = "productId",column=@Column(name="PRODUCT_ID"))
})
public LineItemId getLineItemId() {
return lineItemId;
}
public void setLineItemId(LineItemId lineItemId) {
this.lineItemId = lineItemId;
}


It complained when I did not include setters and getters for orderId and productId. I treid mapping the fields and making them transient. Here is the SQL generated when I load the LineItems from the Order class.

select customer0_.CUST_ID as CUST1_3_, customer0_.NAME as NAME35_3_, customer0_.OPEN_ORDER_ID as OPEN3_35_3_, customeror1_.ORDER_ID as ORDER1_0_, customeror1_.TOTAL as TOTAL36_0_, customeror1_.STATUS as STATUS36_0_, customeror1_.CUSTOMER_ID as CUSTOMER4_36_0_, lineitems2_.ORDER_ID as ORDER3_5_, lineitems2_.orderId as orderId5_, lineitems2_.productId as productId5_, lineitems2_.orderId as orderId1_, lineitems2_.productId as productId1_, lineitems2_.orderId as orderId37_1_, lineitems2_.productId as productId37_1_, lineitems2_.ORDER_ID as ORDER3_37_1_, lineitems2_.PRODUCT_ID as PRODUCT4_37_1_, lineitems2_.AMOUNT as AMOUNT37_1_, lineitems2_.QUANTITY as QUANTITY37_1_, product3_.SKU as SKU2_, product3_.DESC as DESC39_2_, product3_.PRICE as PRICE39_2_ from CUSTOMER customer0_ left outer join ORDER customeror1_ on customer0_.OPEN_ORDER_ID=customeror1_.ORDER_ID left outer join LINEITEM lineitems2_ on customeror1_.ORDER_ID=lineitems2_.ORDER_ID left outer join PRODUCT product3_ on lineitems2_.PRODUCT_ID=product3_.SKU where customer0_.CUST_ID=?

Not sure why the orderId and productId props are becoming part of SQL.

Thanks for your help

have a look at the unit test suite, it shows an example of @IdClass use

This is the example I had previously used:

http://docs.jboss.org/ejb3/tutorial/com ... osite.html

I have tried several varations. What I keep getting is SQL below

select customer0_.CUST_ID as CUST1_3_, customer0_.NAME as NAME35_3_, customer0_.OPEN_ORDER_ID as OPEN3_35_3_, customeror1_.ORDER_ID as ORDER1_0_, customeror1_.TOTAL as TOTAL36_0_, customeror1_.STATUS as STATUS36_0_, customeror1_.CUSTOMER_ID as CUSTOMER4_36_0_, lineitems2_.ORDER_ID as ORDER3_5_, lineitems2_.orderId as orderId5_, lineitems2_.productId as productId5_, lineitems2_.orderId as orderId1_, lineitems2_.productId as productId1_, lineitems2_.orderId as orderId37_1_, lineitems2_.productId as productId37_1_, lineitems2_.ORDER_ID as ORDER3_37_1_, lineitems2_.PRODUCT_ID as PRODUCT4_37_1_, lineitems2_.AMOUNT as AMOUNT37_1_, lineitems2_.QUANTITY as QUANTITY37_1_, product3_.SKU as SKU2_, product3_.DESC as DESC39_2_, product3_.PRICE as PRICE39_2_ from CUSTOMER customer0_ left outer join ORDER customeror1_ on customer0_.OPEN_ORDER_ID=customeror1_.ORDER_ID left outer join LINEITEM lineitems2_ on customeror1_.ORDER_ID=lineitems2_.ORDER_ID left outer join PRODUCT product3_ on lineitems2_.PRODUCT_ID=product3_.SKU where customer0_.CUST_ID=?


If you look at it, it seems to be selecting the fields several times? And it it selecting them both by the variable name and the column name? This happens when I try to load the Lineitems from a CustomerOrder.

lineitems2_.orderId as orderId5_, lineitems2_.productId as productId5_, lineitems2_.orderId as orderId1_, lineitems2_.productId as productId1_, lineitems2_.orderId as orderId37_1_, lineitems2_.productId as productId37_1_, lineitems2_.ORDER_ID as ORDER3_37_1_, lineitems2_.PRODUCT_ID as PRODUCT4_37_1_

I have to map both the key class fields and the Entity class fields to the column. If I use the pk class and remove getters and setters from entity, it complains that it can't find the entity class.

package com.ibm.persistence.ejb3.order.entity;

import java.io.Serializable;

import javax.persistence.Column;


public class LineItemId implements Serializable {

private Integer orderId;
private Integer productId;
public LineItemId() {
super();
// TODO Auto-generated constructor stub
}
@Column(name="ORDER_ID")
public Integer getOrderId() {
return orderId;
}
public void setOrderId(Integer orderId) {
this.orderId = orderId;
}

@Column(name="PRODUCT_ID")
public Integer getProductId() {
return productId;
}
public void setProductId(Integer productId) {
this.productId = productId;
}
public boolean equals(Object arg0) {
if (arg0 == this) return true;
if (!(arg0 instanceof LineItemId)) return false;
LineItemId other = (LineItemId)arg0;
if(other.getOrderId().equals(orderId) && other.getProductId().equals(productId))
{
return true;
}
return false;

}
public int hashCode() {
return orderId + productId.hashCode();
}

}

Entity class

package com.ibm.persistence.ejb3.order.entity;

import java.io.Serializable;

import javax.persistence.*;

@Entity
@Table(name="LINEITEM")
@IdClass(LineItemId.class)
public class LineItem implements Serializable {

private Product product;
private Integer orderId;
private Integer productId;
private Integer quantity;
private Integer total;




@Column(name="QUANTITY")
public Integer getQuantity() {
return quantity;
}
public void setQuantity(Integer quantity) {
this.quantity = quantity;
}

@Column(name="AMOUNT")
public Integer getTotal() {
return total;
}
public void setTotal(Integer total) {
this.total = total;
}

@ManyToOne(fetch=FetchType.EAGER,optional=false)
@JoinColumn(name="PRODUCT_ID", referencedColumnName="SKU",insertable=false,updatable=false, nullable=false,unique=true)
public Product getProduct() {
return product;
}
public void setProduct(Product product) {
this.product = product;
}



@Column(name="ORDER_ID")
public Integer getOrderId() {
return orderId;
}
public void setOrderId(Integer orderId) {
this.orderId = orderId;
}

@Column(name="PRODUCT_ID")
public Integer getProductId() {
return productId;
}
public void setProductId(Integer productId) {
this.productId = productId;
}



}


The underlying SQL seems to be loading fields 3 times? If I have a pk class with the fields, then it should ignore the same fields in the Entity class. If i use an embedded Id, it should not force me to add the fields in addition to the the pk class embedded id?



select customer0_.CUST_ID as CUST1_3_, customer0_.NAME as NAME44_3_, customer0_.OPEN_ORDER_ID as OPEN3_44_3_, customeror1_.ORDER_ID as ORDER1_0_, customeror1_.TOTAL as TOTAL45_0_, customeror1_.STATUS as STATUS45_0_, customeror1_.CUSTOMER_ID as CUSTOMER4_45_0_, lineitems2_.ORDER_ID as ORDER1_5_, lineitems2_.PRODUCT_ID as PRODUCT2_5_, lineitems2_.ORDER_ID as ORDER1_1_, lineitems2_.PRODUCT_ID as PRODUCT2_1_, lineitems2_.ORDER_ID as ORDER1_46_1_, lineitems2_.PRODUCT_ID as PRODUCT2_46_1_, lineitems2_.AMOUNT as AMOUNT46_1_, lineitems2_.QUANTITY as QUANTITY46_1_, product3_.SKU as SKU2_, product3_.DESC as DESC48_2_, product3_.PRICE as PRICE48_2_ from CUSTOMER customer0_ left outer join ORDER customeror1_ on customer0_.OPEN_ORDER_ID=customeror1_.ORDER_ID left outer join LINEITEM lineitems2_ on customeror1_.ORDER_ID=lineitems2_.ORDER_ID left outer join PRODUCT product3_ on lineitems2_.PRODUCT_ID=product3_.SKU where customer0_.CUST_ID=?

Having the properties both in the PkClass and the Entity is the whole point of @IdClass as defined in the EJB3 spec.

About the column duplication in the SQL, I don't reproduce the behavior but, even if true is it really a problem to retrieve several time the column for such corner case? Usually the id columns are quite small.

The point is I have to add the column = for each pk field 2 times (In the Entity and the Pk class). Yes, I can do it and its a workaround but it does not seem right I have to do it twice.

If you mean once in the PkClass, and once on the manytoone assoc @JoinColumn(name="my_name"...), then this is the recommanded workaround. And the only one EJB3 spec compliant.

if you mean, that you have to define twice the column name for the properties (one in the pkclass, and once in the entity), then there is a problem (that I can't reproduce).

To make it work. I had to add column=".." properties in the PK class, and the same properties in the Entity class. (Nothing to do with the many to one)

Like this one
http://cvs.sourceforge.net/viewcvs.py/hibernate/HibernateExt/metadata/src/test/org/hibernate/test/annotations/id/Footballer.java?rev=1.1&view=auto
http://cvs.sourceforge.net/viewcvs.py/hibernate/HibernateExt/metadata/src/test/org/hibernate/test/annotations/id/FootballerPk.java?rev=1.2&view=auto

This is the expected behavior.

Hi,

I have a similar situation, with an addition that complexes stuff.


pretty much the same: "Product" and "LineItem" entities.
I did exactly as u wrote making the id of lineItem as a regular property,
and mapped the @ManyToOne with @JoinColumn as u wrote.

The difference is that In addition,
my "Product" entity also holds a list of LineItems,
with @OneToMany annot and mappedBy to the property name as usuall.
It deployes well,
but while running a very strange thing occur.
When I load a product entity, and he has for example 2 LineItems,
in fact in the list inside it there is a multiplicty of LineItems no according to how many rows there is in the LineItem table with the same product (depends on the "OrderId").

for example:
one product -> 2 lineItems - lineItem 1 has 3 rows cause has 3 orderId
lineItem 2 has 2 rows cause has 2 orderId

So in the "LineItem" list inside the "Product" entity I have 5 rows (3+2) of LineItems instead of 2.

How can I solve this one?

please read the collection part of hibernate annotation documentation this is explained