CODEWITHSUNDEEP
javaspring-booth2daospring-jdbc
Compiler v2
Compiler v2

Reputation: 3605

Crash with DAO creating a record in DB - Spring Boot

Whenever I send my form data to my rest controller, and it goes to my DAO class, I get this error:

org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Referential integrity constraint violation

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', '[email protected]', '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', '[email protected]', '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', '[email protected]', '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', '[email protected]', '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) ~
....

The rest is not useful, and for privacy reasons, is not shown. I assure you this is all the data you need, and it has all the nested exceptions/errors.

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;
    }
}

How do you fix this and prevent this from happening?

EDIT:

My DDL for my tables:

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)
);

Upvotes: 1

Views: 736

Answers (2)

Compiler v2
Compiler v2

Reputation: 3605

The fix is to update my DDL, I found that I have done the FOREIGN KEY incorrectly.

Here is the updated version:

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

create table Foo2 (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(64) NOT NULL,
    Foo3_ID INT NULL,
    CONSTRAINT FK_FOO2_FOO3_ID FOREIGN KEY(FOO3_ID) REFERENCES FOO3(ID)
);

create table Foo (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    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,
    Foo2_ID INT NULL,
    CONSTRAINT FK_FOO_FOO2_ID FOREIGN KEY(FOO2_ID) REFERENCES Foo2(ID)
);

I needed to add the NULL property to allow for null FK references, and I had to add the CONSTRAINT to make the FOREIGN KEY usable.

Upvotes: 1

user1697575
user1697575

Reputation: 2848

You are trying to insert a new record into table FOO e.g. INSERT INTO Foo, but as per your tables definition you have a reference to an existing record in table Foo2 (and table Foo2 has a reference to an existing record in table Foo3)... So you will have to first create referenced records in these tables in the following order:

  1. Create a record in table Foo3
  2. Create a record in table Foo2 that references to Foo3 record
  3. Create a record in table Foo that references to Foo2 record

IMPORTANT NOTE: Please review your tables relational design. It seems that you are mixing PRIMARY KEY and the FOREIGN KEY in the same table column called "ID". Ideally each table should have its own primary key column and foreign key(s) in a separate column(s).

Upvotes: 1

Related Questions