Hibernate: More than one row with the given identifier was found error

I got bit by the org.hibernate.HibernateException: More than one row with the given identifier was found: problem and found no help on StackOverflow. It took awhile to sort out the issue so I’m documenting the solution here. I was using JPA/Hibernate and Spring Data.

First off, this was not caused by duplicate rows in the database, as it’s obviously not possible to have duplicate primary keys. Instead this was caused by Hibernate looking up an object, and eagerly filling in a One-to-one relationship with a LEFT OUTER JOIN. Hibernate assumed a single row would come back, but two came back because there were two objects associated with the one-to-one relationship.

Here’s a simplified version of my objects:

@Entity
@Table(name = "plate")
public class Plate {
    private static final long serialVersionUID = 1L;

    @Id
    @SequenceGenerator(name="test_seq", sequenceName="test_seq")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "test_seq")
    @Column(name = "id")
    private Long id;

    @Version
    @Column(name = "object_version")
    private long objectVersion;

    @Column(name = "name")
    private String name;

    @OneToOne(mappedBy = "plate")
    private Sheet sheet;

    public Sheet getSheet() {
        return sheet;
    }

    public void setSheet(Sheet sheet) {
        if (this.sheet != null) {
            this.sheet.setPlate(null);
        }

        this.sheet = sheet;
        sheet.setPlate(this);
    }
}


@Entity
@Table(name = "sheet")
public class Sheet {
    private static final long serialVersionUID = 1L;

    @Id
    @SequenceGenerator(name="test_seq", sequenceName="test_seq")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "test_seq")
    @Column(name = "id")
    private Long id;

    @Version
    @Column(name = "object_version")
    private long objectVersion;

    @Column(name = "sheet_name")
    private String sheetName;

    @OneToOne
    @JoinColumn(name = "plate_id")
    private Plate plate;

    public Plate getPlate() {
        return plate;
    }

    // Do not use. Use Plate.setSheet() instead
    void setPlate(Plate plate) {
        this.plate = plate;
    }
}

The problem is with the @OneToOne relationship between Plate and Sheet. I initially did not want to delete orphan Sheets. In my code I looked up a Plate by id and added a new Sheet. This removed the Plate-to-Sheet relationship from the original Sheet. When I commit the transaction I assumed JPA would save all modified objects (Plate, the original Sheet, and the new Sheet).

This was not true! JPA apparently finds modified objects by walking down the hierarchy from the object that was originally loaded (Plate), and it misses the fact that the original Sheet, which is now orphaned, was modified. This meant the original Sheet’s sheet.plate_id column was not cleared in the database. In other words, I broke their relationship in the data model, but it failed to save to the database. So the next time I try to load the plate Hibernate runs a query like this:

select
    plate1_.id as id1_19_12_, 
    plate1_.object_version as object_v2_19_12_, 
    plate1_.name as name3_19_12_, 
    sheet2_.id as id1_39_12_, 
    sheet2_.object_version as object_v2_39_12_, 
    sheet2_.sheet_name as sheet_nam2_39_12_, 
    sheet2_.plate_id as plate_id4_39_12_, 
from 
    plate plate1_
    left outer join sheet sheet2_ on plate1_.id = sheet2_.plate_id
where 
    plate1_.id=?

Which brings back 2 rows in the result set and produces this error:

org.hibernate.HibernateException: More than one row with the given identifier was found: 10045, for class: com.example.Plate

This is deceptive: there is only one Plate row with that id in the database, but there are two Sheets linked to it.

The solution:
Seems I have two choices: either cascade all and delete orphan on the one-to-one relationship, or explicitly call my repository class to look up and save the original Sheet every time I remove its relationship with Plate. I opted for the first choice and added this to the plate class:

@OneToOne(mappedBy = "plate", cascade = CascadeType.ALL, orphanRemoval = true)
private Sheet sheet;

This fixed the issue.