Hibernate do not save entity with last-available ID - ConstraintViolationException

Question

I am having a problem with my code. I have created an entity "Comment" with relations Many-To-Many to entities Movie and User (User may comment many movies, and movie can be commented by many users). When I am saving new Comment entity, Hibernate is saving it with ID = 1 (when in a table is already a row with id=1. It should generate ID=1001 because I have 1000 rows of test data). Using CrudRepository

Here is the table generation file for Liquibase:

    <databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.2.xsd">

    <changeSet id="0005-1" author="Krystian">

        <createTable tableName="movie_comment">

            <column name="id" type="BIGINT" autoIncrement="true">
                <constraints nullable="false" primaryKey="true"/>
            </column>

            <column name="user_id" type="BIGINT">
                <constraints nullable="false"
                             foreignKeyName="fk_movie_comment_user_id"
                             referencedTableName="users"
                             referencedColumnNames="id"
                             unique="false"
                />
            </column>

            <column name="movie_id" type="BIGINT">
                <constraints nullable="false"
                             foreignKeyName="fk_movie_comment_movie_id"
                             referencedTableName="movie"
                             referencedColumnNames="id"
                             unique="false"
                />
            </column>

            <column name="comment" type="VARCHAR(2000)">
                <constraints nullable="false"/>
            </column>

            <column name="date_added" type="DATETIME">
                <constraints nullable="false"/>
            </column>

        </createTable>

    </changeSet>

</databaseChangeLog>

Here is the Comment entity class:

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "movie_comment")
public class Comment {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long Id;

    @ManyToOne()
    @JoinColumn(name = "movie_id")
    private Movie movie;

    @ManyToOne
    @JoinColumn(name = "user_id")
    private User user;

    private String comment;
    private LocalDateTime dateAdded;
}

And here is the service method to save new comment:

        public void addNewComment(Long movieId, String comment, String email) {
        User author = userRepository.findByEmail(email).orElseThrow();
        Movie commentedMovie = movieRepository.findById(movieId).orElseThrow();
        Comment newComment = createNewComment(author, commentedMovie, comment);
        commentRepository.save(newComment);
    }

    Comment createNewComment(User author, Movie movie, String comment){
        Comment newComment = new Comment();
        newComment.setUser(author);
        newComment.setMovie(movie);
        newComment.setComment(comment);
        newComment.setDateAdded(LocalDateTime.now());
        return newComment;
    }
}

Where can be the problem?

Which DB are you using? How did you insert the initial 1000 records? — Stanislav Bashkyrtsev, Jul 03 '22 at 11:13
I am using MySQL. Initial 1000 records generated using mockaroo website (only for development purposes). All test data are inserted using: ''' ''' Example from the SQL file: ''' insert into movie_comment (id, movie_id, user_id, comment, date_added) values (1, 23, 2, 'Duis mattis egestas metus.', '2021-12-26'); ''' — krystian14d, Jul 03 '22 at 11:41

score 0 · Accepted Answer · answered Jul 03 '22 at 12:49

0

When inserting values into auto-incrementing columns you don't leverage the actual auto-increment. Meaning, MySQL will let you insert the value but the counter will not be updated. That's why it stays at 0. And the next insert (by Hibernate) assigns 1.

According to these answers, you need to:

either not fill the field at all
or use null
or use 0
or use default

PS: usually it's wiser to fill test data from your code (e.g. by using randomization or some other value generation) instead of preparing special SQL files. This way if your table schema changes, you won't have to modify all 1000 rows of the test data.

answered Jul 03 '22 at 12:49

Stanislav Bashkyrtsev

14,470
7
42
45

Ok, so - if my understanding is correct - I should prepare test data in SQL file without entering "id", right? Then MySQL should assign automatically the ID and auto-increment it. – krystian14d Jul 03 '22 at 12:57
That's right, just skip the id – Stanislav Bashkyrtsev Jul 03 '22 at 14:06

Hibernate do not save entity with last-available ID - ConstraintViolationException

1 Answers1