java - DAO 在数据库中创建记录时崩溃 - Spring Boot

Question

每当我将表单数据发送到我的其余 Controller ，并将其发送到我的 DAO 类时，我都会收到此错误:

org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException:违反引用完整性约束

<小时/>

2019-06-29 20:27:20.730 ERROR 5000 --- [nio-8080-exec-3] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: StatementCallback;SQL [INSERT INTO Foo(name, typeOfPlan, email, website, phoneNum, username, password) VALUES('Test', '2 Year', 'example@gmail.com', 'test.org', '123456789', 'Master', '1234');]; Referential integrity constraint violation: "CONSTRAINT_B3: PUBLIC.FOO FOREIGN KEY(ID) REFERENCES PUBLIC.Foo(ID) (33)"; SQL statement:
INSERT INTO Foo(name, typeOfPlan, email, website, phoneNum, username, password) VALUES('Test', '2 Year', 'example@gmail.com', 'test.org', '123456789', 'Master', '1234'); [23506-199]; nested exception is org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Referential integrity constraint violation: "CONSTRAINT_B3: PUBLIC.Foo FOREIGN KEY(ID) REFERENCES PUBLIC.Foo(ID) (33)"; SQL statement:
INSERT INTO Foo(name, typeOfPlan, email, website, phoneNum, username, password) VALUES('Test', '2 Year', 'example@gmail.com', 'test.org', '123456789', 'Master', '1234'); [23506-199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:457)
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:427)
    at org.h2.message.DbException.get(DbException.java:205)
    at org.h2.message.DbException.get(DbException.java:181)
    at org.h2.constraint.ConstraintReferential.checkRowOwnTable(ConstraintReferential.java:319)
    at org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:261)
    at org.h2.table.Table.fireConstraints(Table.java:1020)
    at org.h2.table.Table.fireAfterRow(Table.java:1038)
    at org.h2.command.dml.Insert.insertRows(Insert.java:194)
    at org.h2.command.dml.Insert.update(Insert.java:132)
    at org.h2.command.CommandContainer.updatmmandContainer.java:133)
    at org.h2.command.Command.executeUpdate(Command.java:267)
    at org.h2.server.TcpServerThread.process(TcpServerThread.java:398)
    at org.h2.server.TcpServerThread.run(TcpServerThread.java:175)
    at java.lang.Thread.run(Thread.java:748)
] with root cause

org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Referential integrity constraint violation: "CONSTRAINT_B3: PUBLIC.Foo FOREIGN KEY(ID) REFERENCES PUBLIC.Foo(ID) (33)"; SQL statement:
INSERT INTO Foo(name, typeOfPlan, email, website, phoneNum, username, password) VALUES('Test', '2 Year', 'example@gmail.com', 'test.org', '123456789', 'Master', '1234'); [23506-199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:457) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.engine.SessionRemote.done(SessionRemote.java:607) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.command.CommandRemote.executeUpdate(CommandRemote.java:237) ~
....

其余部分没有用，并且出于隐私原因，未显示。我向您保证这就是您需要的所有数据，并且它包含所有嵌套的异常/错误。

DAO

@Repository
public class SomeDAOImpl implements ISomeDAO {

    @Autowired
    private JdbcTemplate temp;

public void createRecord(Foo foo) {

        // SQL insert statement
        String sql = "INSERT INTO Foo(name, typeOfPlan, email, website, phoneNum, username, password) " +
                "VALUES(" + "\'" + foo.getName() + "\'" + ", " + "\'" + foo.getTypeOfPlan() + "\'" + ", " + "\'" + foo.getEmail() + "\'" + ", " +
                "\'" + foo.getWebsite() + "\'" + ", " + "\'" + foo.getPhoneNum() + "\'" + ", " + "\'" + foo.getUsername() + "\'" +
                ", " + "\'" + foo.getPassword() + "\'" + ");";

        // Updates the table with the new record
        // Error here
        temp.update(sql);
    }
}

POJO

@JsonIgnoreProperties(ignoreUnknown = true)
public class Foo {

    private String name;
    private String typeOfPlan;
    private String email;
    private String website;
    private String phoneNum;
    private String username;
    private String password;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getTypeOfPlan() {
        return typeOfPlan;
    }

    public void setTypeOfPlan(String typeOfPlan) {
        this.typeOfPlan= typeOfPlan;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getWebsite() {
        return website;
    }

    public void setWebsite(String website) {
        this.website = website;
    }

    public String getPhoneNum() {
        return phoneNum;
    }

    public void setPhoneNum(String phoneNum) {
        this.phoneNum = phoneNum;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

如何解决这个问题并防止这种情况发生？

编辑:

我的表的 DDL:

create table Foo3 (
    ID INT AUTO_INCREMENT,
    foo1 VARCHAR(30) NOT NULL,
    foo2 VARCHAR(10) NOT NULL,
    foo3 VARCHAR(50) NOT NULL,
    PRIMARY KEY(ID)
);

create table Foo2 (
    ID INT AUTO_INCREMENT,
    name VARCHAR(64) NOT NULL,
    PRIMARY KEY(ID),
    FOREIGN KEY (ID) REFERENCES Foo3(ID)
);

create table Foo (
    ID INT AUTO_INCREMENT,
    name VARCHAR(64) NOT NULL,
    typeOfPlan VARCHAR(30) NOT NULL,
    email VARCHAR(35) NOT NULL,
    website VARCHAR(40),
    phoneNum VARCHAR(35) NOT NULL,
    username VARCHAR(35) NOT NULL,
    password VARCHAR(35) NOT NULL,
    PRIMARY KEY(ID),
    FOREIGN KEY (ID) REFERENCES Foo2(ID)
);

Answer 1

您正在尝试将新记录插入表 FOO，例如INSERT INTO Foo，但根据您的表定义，您引用了表 Foo2 中的现有记录(并且表 Foo2 引用了表 Foo3 中的现有记录)...因此，您必须首先按以下顺序在这些表中创建引用记录:

1. 在表 Foo3 中创建一条记录
2. 在表 Foo2 中创建一条引用 Foo3 记录的记录
3. 在表 Foo 中创建一条引用 Foo2 记录的记录

重要提示:请检查您的表关系设计。您似乎在名为“ID”的同一表列中混合了主键和外键。理想情况下，每个表都应在单独的列中拥有自己的主键列和外键。