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?
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.