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?

Answer 1

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.