"More than one row with the given identifier was found". The problem: it's not an identifier

Issue

Note. It's a heavily trimmed down version of the problematic code. I spent a lot of time making it. I believe it's pretty MREish. I do hope for a helpful answer

Here's my test

package by.afinny.credit.unit.repository;

import by.afinny.credit.entity.Card;
import by.afinny.credit.enumeration.CardStatus;
import by.afinny.credit.repository.CardRepository;
import org.assertj.core.api.SoftAssertions;
import org.junit.jupiter.params.ParameterizedTest;
import org.junit.jupiter.params.provider.EnumSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.test.context.ActiveProfiles;
import org.springframework.test.context.jdbc.Sql;

import java.util.List;
import java.util.UUID;

@DataJpaTest
@ActiveProfiles("test")
@Sql(
        executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD,
        scripts = {"/schema-h2.sql", "/data-h2.sql"}
)
class CardRepositoryTest {
    @Autowired
    private CardRepository cardRepository;
    private final static UUID ACCOUNT_ID = UUID.fromString("00000000-0000-0000-0000-000000000ac1");


    @ParameterizedTest
    @EnumSource(CardStatus.class)
    void testFindByAccountIdAndCardStatusNot(CardStatus excludedCardStatus) {
        List<Card> cards = cardRepository.findByAccountIdAndCardStatusNot(ACCOUNT_ID, excludedCardStatus); // never gets beyond this line, though
        cards.forEach(card -> verifyProperties(card, excludedCardStatus));
    }

    private void verifyProperties(Card card, CardStatus excludedCardStatus) {
        SoftAssertions.assertSoftly(softAssertions -> {
            softAssertions.assertThat(card.getAccount().getId()).isEqualTo(ACCOUNT_ID);
            softAssertions.assertThat(card.getCardStatus()).isNotEqualTo(excludedCardStatus);
        });
    }
}

Here are my scripts:

CREATE TABLE IF NOT EXISTS PUBLIC.account
(
    id                        UUID           PRIMARY KEY
);
CREATE TABLE IF NOT EXISTS PUBLIC.card
(
    id                UUID             PRIMARY KEY,
    account_id        UUID             NOT NULL REFERENCES account (id),
    status            VARCHAR(30)      NOT NULL
);

CREATE TABLE IF NOT EXISTS PUBLIC.account
(
    id                        UUID           PRIMARY KEY
);
CREATE TABLE IF NOT EXISTS PUBLIC.card
(
    id                UUID             PRIMARY KEY,
    account_id        UUID             NOT NULL REFERENCES account (id),
    status            VARCHAR(30)      NOT NULL
);

My entities:

package by.afinny.credit.entity;

import by.afinny.credit.enumeration.CardStatus;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EnumType;
import javax.persistence.Enumerated;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToOne;
import javax.persistence.Table;
import java.util.UUID;

@Entity
@Table(name = "card")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Card {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private UUID id;
    @Enumerated(EnumType.STRING)
    @Column(name = "status", nullable = false, length = 30)
    private CardStatus cardStatus;
    @OneToOne(cascade = CascadeType.MERGE)
    @JoinColumn(name = "account_id", referencedColumnName = "id")
    private Account account;
}

package by.afinny.credit.entity;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToOne;
import javax.persistence.Table;
import java.util.UUID;

@Entity
@Table(name = "account")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Account {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private UUID id;
    @OneToOne(mappedBy = "account")
    private Card card;
}

package by.afinny.credit.enumeration;

public enum CardStatus {
    BLOCKED,
    ACTIVE,
    CLOSED,
    NON_ACTIVE
}

package by.afinny.credit.repository;

import by.afinny.credit.entity.Card;
import by.afinny.credit.enumeration.CardStatus;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.UUID;

@Repository
public interface CardRepository extends JpaRepository<Card, UUID> {
    List<Card> findByAccountIdAndCardStatusNot(UUID accountId, CardStatus excludedStatus);
}

Properties:

spring:
  datasource:
    url: jdbc:h2:mem:db;DB_CLOSE_DELAY=-1;MODE=MySQL;DATABASE_TO_LOWER=TRUE
    driver-class-name: org.h2.Driver
    username: sa
    password:
  jpa:
    hibernate:
      ddl-auto: none
    show-sql: true
    database-platform: org.hibernate.dialect.H2Dialect
    properties:
      hibernate:
        format_sql: true
  h2:
    console:
      enabled: false
      settings:
        web-allow-others: true
  config:
    activate:
      on-profile: test

logging:
  level:
    org:
      hibernate:
        type: TRACE
      springframework:
          jdbc:
            core: TRACE
            datasource:
              init: DEBUG
          test:
            context:
              jdbc: DEBUG

I can include the pom as well if it's necessary, but I don't think so (basically all it has is starter-data-jpa, starter-test, lombok, h2)

The problem:

Caused by: org.hibernate.HibernateException: More than one row with the given identifier was found: 00000000-0000-0000-0000-000000000ac1, for class: by.afinny.credit.entity.Card
    at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:104)
    at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:66)
    at org.hibernate.persister.entity.AbstractEntityPersister.loadByUniqueKey(AbstractEntityPersister.java:2486)

Why does it see the foreign key to the account table as an identifier? I can't wrap my head around it. It should be valid for a table to refer to the same primary key of another table

Interestingly, it runs fine with ACTIVE, but fails in all other cases

What's the root of this problem? How do I fix it?

Solution

The error occurs because there are two candidates for the card field in Account entity.

It should be valid for a table to refer to the same primary key of another table

It is valid if you use @ManyToOne association type. For @OneToOne it's invalid.

Answered By - Anton Kozub