获取JPA错误完整性约束(FK_XXXXX)违反-找不到父键

Question

我有两张桌子

CREATE TABLE stripe_product (
    id NUMBER NOT NULL PRIMARY KEY,
    product_id VARCHAR2(256) NOT NULL,
    name VARCHAR2(256) NOT NULL,
    description VARCHAR2(256),
    active NUMBER(1,0),
    deleted NUMBER(1,0),
    created_at TIMESTAMP,
    created_by NUMBER,
    updated_at TIMESTAMP,
    updated_by NUMBER,
    deleted_at TIMESTAMP,
    CONSTRAINT UC_stripe_product_id_product_id UNIQUE (id, product_id),
    CONSTRAINT UC_stripe_product_product_id UNIQUE (product_id)
);

和

CREATE TABLE stripe_price (
    id NUMBER NOT NULL PRIMARY KEY,
    price_id VARCHAR2(256) NOT NULL,
    stripe_product_product_id VARCHAR2(256) NOT NULL,
    active NUMBER(1,0),
    deleted NUMBER(1,0),
    currency VARCHAR2(10) NOT NULL,
    billing_scheme VARCHAR2(50) NOT NULL,
    unit_amount NUMBER NOT NULL,
    type VARCHAR2(50) NOT NULL,
    recurring_aggregate_usage VARCHAR2(50),
    recurring_interval VARCHAR2(50),
    recurring_interval_count NUMBER,
    recurring_usage_type VARCHAR2(50),
    created_at TIMESTAMP,
    created_by NUMBER,
    updated_at TIMESTAMP,
    updated_by NUMBER,
    deleted_at TIMESTAMP,
    CONSTRAINT UC_stripe_price_id_price_id_stripe_product_product_id UNIQUE (id, price_id, stripe_product_product_id),
    CONSTRAINT UC_stripe_price_price_id UNIQUE (price_id),
    CONSTRAINT FK_stripe_price_stripe_product_product_id FOREIGN KEY (stripe_product_product_id) 
    REFERENCES stripe_product(product_id) ON DELETE CASCADE
);

我使用以下类映射了这些表

@Entity
@EntityListeners(AuditingEntityListener.class)
@Table(name = "stripe_product")
public class StripeProduct {
    @Id
    @SequenceGenerator(name = "stripe_product_seq", sequenceName = "stripe_product_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "stripe_product_seq")
    private Long id;

    @Column(name = "product_id", unique = true)
    private String productId;

    @Column(nullable = false)
    private String name;
    private String description;
    private Boolean active;
    private Boolean deleted;

    @Embedded
    private Audit audit = new Audit();

    @Column(name = "deleted_at")
    private Instant deletedAt;

    public StripeProduct() {

    }

    public StripeProduct(Product product) {
        this.productId = product.getId();
        this.name = product.getName();
        this.description = product.getDescription();
        this.active = product.getActive();
        this.deleted = product.getDeleted();
    }
    
    // getters and setter

}

另一个

@Entity
@EntityListeners(AuditingEntityListener.class)
@Table(name = "stripe_price")
public class StripePrice {
    @Id
    @SequenceGenerator(name = "stripe_price_seq", sequenceName = "stripe_price_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "stripe_price_seq")
    private Long id;

    @Column(name = "price_id", unique = true)
    private String priceId;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "stripe_product_product_id")
    private StripeProduct stripeProduct;

    private Boolean active;
    private Boolean deleted;
    private String currency;

    ....

    @Embedded
    private Audit audit = new Audit();

    @Column(name = "deleted_at")
    private Instant deletedAt;

    public StripePrice() {
    
    }

    public StripePrice(Price price, StripeProduct stripeProduct) {
    
        Assert.notNull(price, "price cannot be null");
    
        this.priceId = price.getId();
        this.stripeProduct = stripeProduct;
        this.active = price.getActive();
        this.currency = price.getCurrency();
        this.billingScheme = price.getBillingScheme();
        this.unitAmount = price.getUnitAmount();
        this.type = price.getType();
        Recurring recurring = price.getRecurring();
        if (recurring != null) {
            this.recurringAggregateUsage = recurring.getAggregateUsage();
            this.recurringInterval = recurring.getInterval();
            this.recurringIntervalCount = recurring.getIntervalCount();
            this.recurringUsageType = recurring.getUsageType();
        }
    
        this.deleted = price.getDeleted();
    }
    // getters and setters
}

在数据库中，我有以下记录

​

​

现在，如果我使用下面的sql直接在数据库中插入记录，它就可以工作

insert into stripe_price (active, created_by, created_at, updated_by, updated_at, billing_scheme, 
currency, deleted, deleted_at, price_id, recurring_aggregate_usage, recurring_interval, 
recurring_interval_count, recurring_usage_type, stripe_product_product_id, type, unit_amount, id) 
values (1, 0, SYSDATE, 0, SYSDATE, 'Billing scheme', 'usd', 0, null, 'adsad', 'hjgjh', 'sfsad', 1, 
'asdsad', 'prod_Io2qV0NPORZhnX', 'adsad', 100, 33);

insert into stripe_price (active, created_by, created_at, updated_by, updated_at, billing_scheme, 
currency, deleted, deleted_at, price_id, recurring_aggregate_usage, recurring_interval, 
recurring_interval_count, recurring_usage_type, stripe_product_product_id, type, unit_amount, id) 
values (1, 0, SYSDATE, 0, SYSDATE, 'Billing scheme', 'usd', 0, null, 'price_id-2', 'hjgjh', 'sfsad', 
1, 'asdsadxzcxzc', 'prod_Io2qV0NPORZhnX', 'asd1234', 100, 34)

但是现在使用JPA我得到了错误

Caused by: java.sql.BatchUpdateException: ORA-02291: integrity constraint (BUILDADMIN.FK_STRIPE_PRICE_STRIPE_PRODUCT_PRODUCT_ID) violated - parent key not found

以下是我的代码

List<Price> prices = priceCollection.getData();
if (!CollectionUtils.isEmpty(prices)) {
    prices.forEach(price -> {
        String productId = price.getProduct();
        StripeProduct managedStripeProduct =  stripeProductRepository.findByProductId(productId).orElse(null);
        if (managedStripeProduct != null) {
                StripePrice newStripePrice = new StripePrice(price, managedStripeProduct);
                StripePrice managedStripePrice =  stripePriceRepository.save(newStripePrice);
            }
        });
}

在debug中，我发现下面的SQL

Hibernate: select stripe_price_seq.nextval from dual
Hibernate: insert into stripe_price (active, created_by, created_at, updated_by, updated_at, billing_scheme, currency, deleted, deleted_at, price_id, recurring_aggregate_usage, recurring_interval, recurring_interval_count, recurring_usage_type, stripe_product_product_id, type, unit_amount, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
 binding parameter [1] as [BIT] - [true]
binding parameter [2] as [BIGINT] - [0]
binding parameter [3] as [TIMESTAMP] - [2021-01-25T23:18:11.104Z]
binding parameter [4] as [BIGINT] - [0]
binding parameter [5] as [TIMESTAMP] - [2021-01-25T23:18:11.104Z]
binding parameter [6] as [VARCHAR] - [per_unit]
binding parameter [7] as [VARCHAR] - [usd]
binding parameter [8] as [BIT] - [null]
binding parameter [9] as [TIMESTAMP] - [null]
binding parameter [10] as [VARCHAR] - [price_1ICQl8JOji9YLkEKmju4jUmu]
binding parameter [11] as [VARCHAR] - [null]
binding parameter [12] as [VARCHAR] - [month]
binding parameter [13] as [BIGINT] - [1]
binding parameter [14] as [VARCHAR] - [licensed]
binding parameter [15] as [BIGINT] - [30]
binding parameter [16] as [VARCHAR] - [recurring]
binding parameter [17] as [BIGINT] - [100000]
binding parameter [18] as [BIGINT] - [80]

正如您可以注意到的，当hibernate执行SQL时，没有stripe_product_product_id。我认为这就是它产生错误的原因。

虽然我在StripePrice上设置了它，但是找不到为什么我下注错误。谁能解释一下我做错了什么？我该如何解决这个问题呢？

谢谢

Answer 1

我已经解决了这个问题。实际上，问题是我对列有不同的名称。在stripe_product表中，列名为product_id。而在stripe_price表中，列名是stripe_product_product_id。因此，我必须在映射中使用以下内容

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "stripe_product_product_id", referencedColumnName = "product_id", nullable = false)
private StripeProduct stripeProduct;

因此，从根本上说，referencedColumnName = "product_id"是缺失的，这就是为什么JPA无法从stripe_product表中找到product_id值。希望它也能对其他人有用。