CODEWITHSUNDEEP
springhibernatejakarta-eejpajpa-2.0
Achyut Paudel
Achyut Paudel

Reputation: 23

More than one row with the given identifier was found exception for composite key

I am developing a web application using JPA/Hibernate with a legacy database. I have the following table with underlying_id and value_date as composite keys where underlying_id is also a foreign key

CREATE TABLE `underlying_settle_value_table` (
  `underlying_id` varchar(250) NOT NULL,
  `value_date` date NOT NULL,
  `settle_value` double NOT NULL,
  `risk_free_rate` double NOT NULL,
  `div_yield` double NOT NULL,
  PRIMARY KEY (`underlying_id`,`value_date`),
  KEY `fk_underlying_settle_value_table_underlying_list_table1_idx` (`underlying_id`),
  CONSTRAINT `fk_underlying_settle_value_table_underlying_list_table1` FOREIGN KEY     (`underlying_id`) REFERENCES `underlying_list_table` (`underlying_id`) ON DELETE CASCADE ON     UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

I have created the following entity to represent that table:

@Entity
@Table(name="underlying_settle_value_table")
@IdClass(UnderlyingSettleValueTablePK.class)
public class UnderlyingSettleValueTableEntity implements Serializable {
    @Id
    @ManyToOne(targetEntity=UnderlyingListTableEntity.class)
    @JoinColumn(name="underlying_id",referencedColumnName="underlying_id")
    private UnderlyingListTableEntity underlyingId;

    @Id
    @Temporal(TemporalType.DATE)
    private Date valueDate;

    private Double settleValue;
    private Double riskFreeRate;
    @Column(name="div_yield")
    private Double divYield;

       .. setters and getters
 }

and the idclass as:

public class UnderlyingSettleValueTablePK implements Serializable {
    private UnderlyingListTableEntity underlyingId;
    @Temporal(TemporalType.DATE)    
    private Date valueDate;
..setters and getters
}

When I use findAll method provided by JPA or JQL to search the results, it throws me an error even though I have mapped underlyingid as many to one:

INFO: HHH000327: Error performing load command : org.hibernate.HibernateException: More than one row with the given identifier was found: OR N3, for class com.invenio.dao.entity.admin.UnderlyingListTableEntity

Any help will be appreciated... Thanks in advance

The sql query got from show_sql is as follows:

Hibernate:
   select
        underlying0_.underlying_id as underlyi5_36_,
        underlying0_.value_date as value1_36_,
        underlying0_.div_yield as div2_36_,
        underlying0_.risk_free_rate as risk3_36_,
        underlying0_.settle_value as settle4_36_
    from
        underlying_settle_value_table underlying0_
    where
        underlying0_.underlying_id in (
            'OR N3'
        )
Hibernate:
    select
        underlying0_.underlying_id as underlyi1_35_0_,
        underlying0_.bbg_underlying_id as bbg2_35_0_,
        underlying0_.currency as currency3_35_0_,
        underlying0_.invenio_product_code as invenio4_35_0_
    from
        underlying_list_table underlying0_
    where
        underlying0_.underlying_id=?

Upvotes: 1

Views: 19061

Answers (2)

Koitoer
Koitoer

Reputation: 19533

Try this one.

@Entity
@Table(name="underlying_settle_value_table")
public class UnderlyingSettleValueTableEntity implements Serializable {

    @EmbeededId
    private UnderLiyingKey underKey;

    @MapsId("underID")
    @ManyToOne(targetEntity=UnderlyingListTableEntity.class)
    @JoinColumn(name="underlying_id",referencedColumnName="underlying_id")
    private UnderlyingListTableEntity underlyingId;

And the embeddedKey

@Embeddable
public class UnderLying implements Serializable

private int underId;
private Date value_date;

But the big concern here is :

the PK of the table is composited by two elements and Id and a Date, but your foreign Key to the same table just only use the ID, the question is how you can refer to an entity that use a composite key just with one value, not sure but it sound a bad design.

Upvotes: 1

Charitha
Charitha

Reputation: 327

Seems like you are getting more than one value from the search. If you can clean the database n try your search again. i'm sure it will work..

Can you provide the table structure for underlying_settle_value_table more clearly .. you can use @Embeddable and @EmbeddedId to map composite key .Don't use @Id twice in the same class

Upvotes: 2

Related Questions