Although there is a Composite Primary Key, Hibernate gives an error when only one key is unique

Question

I'm facing an Hibernate error which says More than one row with the given identifier was found and I'm stuck with it. I would really appreciate any help on this.

• I want to create a table as orderLine that contains product code, quantity .etc for a particular sales order.

• A SalesOrder can contain many orderLines.

• The composite key for the orderLine table is productCode + OrderNumber. ProductCode is the primary key of the Product table and OrderNumber is the primary key of the SalesOrder table.

• In a single SalesOrder there should be only one order line for a particular product.

• The composite key is getting generated correctly and I get the following sql statement logged by hibernate.

  Hibernate: create table orderLine (orderNumber varchar(255) not null, productCode varchar(255) not null, status varchar(255) not null, quantity integer not null, totalPrice double precision not null, unitPrice double precision not null, primary key (orderNumber, productCode))

• When the OrderLine table contains data as below, I Successfully insert a new record to OrderLine table with the OrderNumber ORD001 & ProductCode BIS1003

• Immediately after when I try to fetch records from the OrderLine, I'm getting the following error.

  Caused by: org.hibernate.HibernateException: More than one row with the given identifier was found: BIS1003, for class: com.salesOrder_ws.entity.OrderLine

• Since there is a composite key as the primary, why is hibernate throwing an exception, when only one key of the composite key is not unique?

The Code is below.

OrderLine Entity:

  @Entity
    @Table(name = "orderLine")
    public class OrderLine implements Serializable{

    private static final long serialVersionUID = -851110991599534263L;


    @AttributeOverrides(value = 
            {@AttributeOverride(column = @Column(name="productCode"), name = "productCode"),
            @AttributeOverride(column = @Column(name="orderNumber"), name = "orderNumber")})
    @EmbeddedId
    private LineID pk;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "productCode", insertable = false, updatable = false)
    private Product product;

    private int quantity;

    private double unitPrice;

    private double totalPrice;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "orderNumber", nullable=false, insertable=false, updatable=false)
    private SalesOrder salesOrder;


    @Override
    public boolean equals(Object obj) {

        try {
            LineID line = (LineID) obj;
            return (this.getSalesOrder().getOrderNumber()
                    .equals(line.getOrderNumber()) && this.getProduct()
                    .getCode().equals(line.getProductCode()));
        } catch (Exception e) {
            return false;
        }
    }

    @Override
    public int hashCode() {
        return (this.getProduct().getCode() + "" + this.getProduct().getCode()).hashCode();
    }
}

SalesOrder Entity

@Entity
@Table(name = "salesOrder")
public class SalesOrder extends BaseEntity{

    @Id
    private String orderNumber;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "customerCode", nullable = false)
    private Customer customer;

    private double totalPrice;

    @OneToMany(fetch = FetchType.EAGER, mappedBy = "salesOrder", cascade = CascadeType.ALL)
    private List<OrderLine> lines;


    @Override
    public boolean equals(Object obj) {

        try {
            SalesOrder so = (SalesOrder) obj;
            if (this.getOrderNumber().equals(so.getOrderNumber())) {
                return true;
            }
        } catch (Exception e) {
            return false;
        }
        return false;
    }

    @Override
    public int hashCode() {
        return this.getOrderNumber().hashCode();
    }
}

Embeddable Class

@Embeddable
public class LineID implements Serializable{

    private static final long serialVersionUID = -4478828739881744452L;
    @Basic(optional = false)
    private String productCode;
    @Basic(optional = false)
    private String orderNumber;

    @Override
    public boolean equals(Object obj) {
        try {
            LineID l = (LineID) obj;
            return this.productCode.equals(l.getProductCode()) && this.orderNumber.equals(l.getOrderNumber());
        } catch (Exception e) {
            return false;
        }
    }

    @Override
    public int hashCode() {
        return (this.getOrderNumber() + "" + this.getProductCode()).hashCode();
    }
}

UPDATE

SQL generated by Hibernate :

Hibernate: create table customer (code varchar(255) not null, status varchar(255) not null, address varchar(255), creditLimit double precision not null, currentCredit double precision not null, name varchar(255), phone1 varchar(255), phone2 varchar(255), primary key (code))
Hibernate: create table orderLine (orderNumber varchar(255), productCode varchar(255), status varchar(255) not null, quantity integer not null, totalPrice double precision not null, unitPrice double precision not null, primary key (orderNumber, productCode))
Hibernate: create table product (code varchar(255) not null, status varchar(255) not null, description varchar(255), price double precision not null, quantity integer not null, primary key (code))
Hibernate: create table salesOrder (orderNumber varchar(255) not null, status varchar(255) not null, totalPrice double precision not null, customerCode varchar(255) not null, primary key (orderNumber))
Hibernate: alter table orderLine add constraint UK_9gf3j9l0n1w7d2h4sso3voc77 unique (productCode)
Hibernate: alter table orderLine add index FK_9gf3j9l0n1w7d2h4sso3voc77 (productCode), add constraint FK_9gf3j9l0n1w7d2h4sso3voc77 foreign key (productCode) references product (code)
Hibernate: alter table orderLine add index FK_ojvge4lucwf2gtihxtmnav3u2 (orderNumber), add constraint FK_ojvge4lucwf2gtihxtmnav3u2 foreign key (orderNumber) references salesOrder (orderNumber)
Hibernate: alter table salesOrder add index FK_4lq8ynumala22y9t17ceawo81 (customerCode), add constraint FK_4lq8ynumala22y9t17ceawo81 foreign key (customerCode) references customer (code)

Hibernate: alter table orderLine add constraint UK_9gf3j9l0n1w7d2h4sso3voc77 unique (productCode)

The above SQL is not intended to generate. If I could avoid this unique constraint, the problem will be solved.

Appreciate any help to resolve this issue.

Answer 1

I think you might be missing the @MapsId annotation:

@Entity
@Table(name = "orderLine")
public class OrderLine implements Serializable{

    private static final long serialVersionUID = -851110991599534263L;

    @AttributeOverrides(value = 
            {@AttributeOverride(column = @Column(name="productCode"), name = "productCode"),
            @AttributeOverride(column = @Column(name="orderNumber"), name = "orderNumber")})
    @EmbeddedId
    private LineID pk;

    @ManyToOne(cascade = CascadeType.ALL)
    @MapsId("productCode")
    private Product product;

    private int quantity;

    private double unitPrice;

    private double totalPrice;

    @ManyToOne(fetch = FetchType.EAGER)
    @MapsId("orderNumber")
    private SalesOrder salesOrder;


    @Override
    public boolean equals(Object obj) {

        try {
            LineID line = (LineID) obj;
            return (this.getSalesOrder().getOrderNumber()
                    .equals(line.getOrderNumber()) && this.getProduct()
                    .getCode().equals(line.getProductCode()));
        } catch (Exception e) {
            return false;
        }
    }

    @Override
    public int hashCode() {
        return (this.getProduct().getCode() + "" + this.getProduct().getCode()).hashCode();
    }
}