Primary key violation when inserting child entity-CodePudding

DB Schema (H2 database):

create table book (
    id_book bigint auto_increment not null primary key,
    title varchar(255) not null,
    id_author bigint not null,
    id_genre bigint not null
);

    
create table comment (
    id_comment bigint auto_increment not null primary key,
    id_book bigint not null,
    comment_text varchar(255) not null
);

Domain classes:

public class Book {
    @Id
    @Column("id_book")
    private Long id;
    
    private String title;
    
    @Column("id_author")
    AggregateReference<Author, Long> author;
 
    @Column("id_genre")
    AggregateReference<Genre, Long> genre;
    
    @MappedCollection(idColumn = "id_book", keyColumn = "id_comment")
    List<Comment> comments = new ArrayList<>();
    
    public void addComment(String commentText) {
        comments.add(new Comment(commentText));
    }
       //getters and setters
}

public class Comment {
    
    @Column("id_comment")
    private Long id;
    
    @Column("comment_text")
    private String text;
    
    public Comment(String text) {
        this.text = text;
    }
    
    public Comment() {
    }
        //getters and setters
}

I have the problem when I add a comment to the book. 

```java
@Override
@Transactional
public String addComment(long bookId, String commentText) {
    var book = bookRepository.findById(bookId);
    return book.map(b -> {
        b.addComment(commentText);
        b = bookRepository.save(b);
        return bookConverter.convertToString(b);
    }).orElse("Book not found!");
}

It generates SQL like this...

    Executing SQL batch update [INSERT INTO "COMMENT" ("comment_text", "id_book", "id_comment") VALUES (?, ?, ?)]

... adds values for id_comment field like 0, 1, 2, 3 and these values intersect with existing ones. So I get Primary Key Violation. Why it adds id_comment field to the INSERT expression?

CodePudding user response：

Why it adds id_comment field to the INSERT expression?

Because you told it to.

The following annotation tells Spring Data JDBC to store the index of the list in the id_comment column.

@MappedCollection(idColumn = "id_book", keyColumn = "id_comment")

Your data model is missing a column for the list index. Add that column and use it as keyColumn in the @MappedCollection annotation