Java JPA外键参考未能保存

Question

我正在学习Springboot并尝试创建一个简单的API。使用H2作为数据层。

我的桌子模式如下

DROP TABLE IF EXISTS Users;
DROP TABLE IF EXISTS Cards;
CREATE TABLE Users(USER_ID INT PRIMARY KEY,
              NAME VARCHAR(255) NOT NULL,
              USERNAME VARCHAR(255) NOT NULL,
              PASSWORD VARCHAR(255) NOT NULL,
              IS_ADMIN BOOL);
insert into Users values(10001,'Administrator', 'admin', 'admin', true);
CREATE TABLE Cards(CARD_ID INT PRIMARY KEY,
              NUMBER VARCHAR(20) NOT NULL,
              EXPIRY VARCHAR(5) NOT NULL,
              CVV INT,
              USER INT,
              foreign key (USER) references Users(USER_ID)
);

我的信用卡实体看起来像遵循。

package sprintboot;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import javax.persistence.*;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;
@Data
@Entity
@Table(name = "CARDS")
@ApiModel(description = "the Credit Card object")
public class CreditCard {
    @ApiModelProperty(notes = "The DB ID of card")
    private @Column(name = "CARD_ID") @Id @GeneratedValue Long card_id;
    @NotBlank(message = "Credit card number is mandatory")
    @ApiModelProperty(notes = "The number of credit card", required = true)
    private @Column(name = "NUMBER") String number;
    @NotBlank(message = "Credit card expiry is mandatory")
    @ApiModelProperty(notes = "The expiry of credit card", required = true)
    private @Column(name = "EXPIRY") String expiry;
    @NotNull(message = "Credit card cvv is mandatory")
    @ApiModelProperty(notes = "The cvv of credit card", required = true)
    private @Column(name = "CVV") Integer cvv;
    @ApiModelProperty(notes = "The owner user of credit card")
    @ManyToOne
    @JoinColumn
    private User user;
    public CreditCard() {}
    public CreditCard(String number, String expiry, Integer cvv, User user) {
        this.number = number;
        this.expiry = expiry;
        this.cvv = cvv;
        this.user = user;
    }
    @Override
    public String toString() {
        return "{number: " + this.number + ", expiry: " + this.expiry + ", cvv: " + this.cvv + ", user: " + this.user + "}";
    }
}

我的用户实体看起来像

package springboot;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import javax.persistence.*;
import javax.validation.constraints.NotBlank;
import java.util.List;
@Data
@Entity
@Table(name = "USERS")
@ApiModel(description = "the USER object")
public class User {
    @ApiModelProperty(notes = "The id of User")
    private @Column(name = "USER_ID") @Id @GeneratedValue Long user_id;
    @ApiModelProperty(notes = "The name of User")
    private @Column(name = "NAME") String name;
    @ApiModelProperty(notes = "The username of User", required = true)
    @NotBlank(message = "Username is mandatory")
    private @Column(name = "USERNAME") String username;
    @ApiModelProperty(notes = "The password of User", required = true)
    @ValidPassword(message = "Password is mandatory")
    private @Column(name = "PASSWORD") String password;
    @ApiModelProperty(notes = "The admin status of User")
    private @Column(name = "IS_ADMIN") boolean isAdmin;
    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL)
    private List<CreditCard> cards;
    public User() {}
    public User(String name, String username, String password, boolean isAdmin) {
        this.name = name;
        this.username = username;
        this.password = password;
        this.isAdmin = isAdmin;
    }
    @Override
    public String toString() {
        return "{id: " + this.getUser_id() + ", username: " + this.getUsername() + ", isAdmin: " + this.isAdmin() + "}";
    }
}

因此，信用卡将包含用户。用户可以包含许多信用卡。我遇到的问题是，当我尝试保存信用卡记录时。它引发以下错误。

Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.In
validDataAccessResourceUsageException: could not prepare statement; SQL [insert into cards (cvv, expiry, number, user_user_id, card_id) values (?, ?, ?, ?, ?)]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement] with root

原因

org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "USER_USER_ID" not found; SQL statement:
insert into cards (cvv, expiry, number, user_user_id, card_id) values (?, ?, ?, ?, ?) [42122-199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:451) ~[h2-1.4.199.jar:1.4.199]

我试图搜索并遵循几个教程，但无法使其起作用。有人可以指出我错了的地方吗？

保存信用卡实体的方法如下。

public CreditCard upsertCC(
        CreditCard card,
        String jwtToken
) throws UnauthorizedException {
    User user = this.getUserFromJWTToken(jwtToken);
    log.info(user.toString());
    card.setUser(user);
    this.ccRepo.save(card);
    this.ccRepo.flush();
    card.setUser(null);
    return card;
}

getuserfromjwttoken方法从H2表返回用户对象。

任何建议

Answer 1

例外实际上是Column "USER_USER_ID" not found;

如果您看到查询， insert into cards (cvv, expiry, number, user_user_id, card_id) values (?, ?, ?, ?, ?)， user_user_id 是不是列名称

因此，问题是您尚未提到CreditCard实体中的user列名称。尝试下面可以使用

更改将要在creditcard.class

中完成

    @ApiModelProperty(notes = "The owner user of credit card")
    @ManyToOne
    @JoinColumn(name = "user") // Added the name attribute
    private User user;

如果您查看@JoinColumn's name attribute的源代码，他们已经清楚地提到了

• 如果加入是用于元素集合的
• 加入列名称为
• 以下串联：实体的名称；" _";
• 引用的主键列的名称。

编辑

对于第二部分，您可以直接使用JPA的功能。

List<CreditCard> findByNumberContainingAndUser(String number, User user);