为何列定义的unique关键字导致更新实体时触发IntegrityConstraintViolationException

项目实体类 #

Movie 实体类 #

@Entity
@Table(name = "MOVIES")
@NoArgsConstructor @RequiredArgsConstructor
@ToString(exclude = {"directors", "writers", "reviews"})
public class Movie extends BaseEntity{

    @Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "movie_id") @Getter
    private long movieID;
    @Column(name = "title", nullable = false) @Getter @Setter @NonNull
    private String title;
    @Column(name = "imdb_url", unique = true) @Getter @Setter
    private String imdbUrl;
    @Column(name = "cover_url") @Getter @Setter
    private String coverUrl;
    @Column(name = "runtime") @Getter @Setter
    private String runtime;
    @Column(name = "language") @Getter @Setter
    private String language;
    @Column(name = "release_date") @Getter @Setter
    private String releaseDate;

    @Getter @Setter
    @ManyToMany(cascade = {CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH})
    @JsonIgnoreProperties({"directors", "writers", "reviews"})
    @JoinTable(
            name = "MOVIE_DIRECTORS",
            joinColumns = @JoinColumn(name = "movie_id"),
            inverseJoinColumns = @JoinColumn(name = "director_id")
    )
    private Set<Director> directors;

    @Getter @Setter
    @ManyToMany(cascade = {CascadeType.MERGE, CascadeType.PERSIST})
    @JoinTable(
            name = "MOVIE_WRITERS",
            joinColumns = @JoinColumn(name = "movie_id"),
            inverseJoinColumns = @JoinColumn(name = "writer_id")
    )
    private Set<Writer> writers;

    @Getter @Setter
    @OneToMany(mappedBy = "reviewedMovie", cascade = {CascadeType.MERGE, CascadeType.PERSIST})
    private Set<Review> reviews;

    // Methods to add elements to directors etc...

}

Director 实体类 #

@Entity
@Table(name = "Directors")
@NoArgsConstructor @RequiredArgsConstructor
@ToString(includeFieldNames = false, onlyExplicitlyIncluded = true)
public class Director extends BaseEntity{

    @Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "director_id") @Getter
    private long directorID;

    @Column(name = "name", nullable = false, unique = true) @Getter @Setter @NonNull @ToString.Include
    private String name;

    @Setter @Getter
    @ManyToMany(mappedBy = "directors")
    private Set<Movie> directedMovies;
}

Writer 实体类 #

@Entity
@Table(name = "Writers")
@NoArgsConstructor @RequiredArgsConstructor
@ToString(includeFieldNames = false, onlyExplicitlyIncluded = true)
public class Writer extends BaseEntity{

    @Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "writer_id") @Getter
    private long writerID;

    @Column(name = "name", nullable = false, unique = true) @Getter @Setter @NonNull @ToString.Include
    private String name;

    @ManyToMany(mappedBy = "writers")
    private Set<Movie> writtenMovies;
}

Review 实体类 #

@Entity
@Table(name = "REVIEWS")
@NoArgsConstructor @RequiredArgsConstructor
@ToString
public class Review extends BaseEntity{

    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Setter @Getter
    @Column(name = "review_id")
    private long reviewID;

    @Getter @Setter @NonNull
    @Column(name = "rating", nullable = false)
    private int rating;

    @Getter @Setter @NonNull
    @Column(name = "review")
    private String review;

    @Getter @Setter @NonNull
    @Column(name = "creation_time")
    private ZonedDateTime creationData;

    @Getter @Setter @NonNull @ToString.Exclude
    @ManyToOne
    @JoinColumn(name = "movieID", nullable = false)
    private Movie reviewedMovie;

}

问题描述 #

首次持久化包含导演、编剧和评论的Movie实体后，应用抛出唯一约束异常：

Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: 
Unique index or primary key violation: "PUBLIC.UK_BAQ304EOVCBM0CVQ2QNDI1W2N_INDEX_8 ON PUBLIC.MOVIES(IMDB_URL NULLS FIRST) VALUES ( /* 1 */ 'xxx' )"; SQL statement:
insert into movies (movie_id, cover_url, imdb_url, language, release_date, runtime, title) values (default, ?, ?, ?, ?, ?, ?) [23505-214]

测试代码仅能运行一次就报错，我知道是imdb_url字段的unique约束导致的问题，但移除该约束会导致数据库出现重复条目，不符合需求。

测试代码 #

@Component
public class DataInitializer implements CommandLineRunner {

    private final MovieService movieService;
    private final ReviewService reviewService;
    private final DirectorService directorService;

    public DataInitializer(MovieService movieService, ReviewService reviewService, DirectorService directorService) {
        this.movieService = movieService;
        this.reviewService = reviewService;
        this.directorService = directorService;
    }

    @Override
    public void run(String... args) throws Exception {
        addRandomMovies(2);

        try{
            Thread.sleep(5000);
        } catch (InterruptedException ex) {
            ex.printStackTrace();
        }

        fetchMovies();
    }

    private void addRandomMovies(int amount) {
        for(int i = 1; i <= amount; i++) {
            Movie m = new MovieBuilderImplementation().withTitle("Test Movie: " + (i)).withReleaseDate("" + (1970+i))
                            .withRuntime("1h " + i + "m " + i + "s").withLanguage("en")
                            .withImdbUrl("www.imdb.com/" + i).withCoverUrl("www.img.com/" + i).build();
            m.addDirector(new Director("a" + i + " b"));
            m.addWriter(new Writer("c" + i + " d"));
            Review review = new Review(55, "xxx" + i + "!!", ZonedDateTime.now(), m);
            Review review2 = new Review(55, "xxx" + i+1 + "!!", ZonedDateTime.now(), m);
            Review review3 = new Review(55, "xxx" + i+2 + "!!", ZonedDateTime.now(), m);
            m.addReview(review);
            m.addReview(review2);
            m.addReview(review3);
            movieService.save(m); // <-- 问题出在这里
        }
    }
}

尝试的解决方案 #

我修改了Movie的Repository和Service代码，但问题依然存在：

MovieRepository #

@Repository
public interface MovieRepository extends JpaRepository<Movie, Long> {

    public Movie getMovieByImdbUrlEqualsIgnoreCase(String imdbUrl);

    @Query("SELECT m FROM Movie m " +
            "JOIN FETCH m.directors " +
            "JOIN FETCH m.writers " +
            "JOIN FETCH m.reviews " +
            "WHERE m.movieID = :id")
    public Movie getMovieByIdWithAll(@Param("id") long id);

}

MovieService #

package com.souryuu.catalogit.service;

import com.souryuu.catalogit.entity.Movie;
import com.souryuu.catalogit.repository.MovieRepository;
import jakarta.transaction.Transactional;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
@Transactional
public class MovieService {

    private final MovieRepository repository;

    public MovieService(MovieRepository repository, DirectorService directorService, WriterService writerService, ReviewService reviewService) {
        this.repository = repository;
    }

    // 这个方法是修改后的，最初只有this.repository.save(...)
    public Movie save(Movie movie) {
        // 持久化Movie
        Movie fetchedMovie = getMovieByImdbUrl(movie.getImdbUrl());
        if(fetchedMovie == null || !fetchedMovie.equals(movie)) {
            return this.repository.save(movie);
        } else {
            return fetchedMovie;
        }
    }

    public List<Movie> findAll() {
        return this.repository.findAll();
    }

    public Movie getMovieWithAll(long movieId) {
        return this.repository.getMovieByIdWithAll(movieId);
    }

    public Movie getMovieByImdbUrl(String imdbUrl) {
        return this.repository.getMovieByImdbUrlEqualsIgnoreCase(imdbUrl);
    }

}

疑问 #

理论上调用save方法应该尝试更新已持久化的实体，但实际它却执行了insert操作，我实在不知道下一步该怎么做了。

内容的提问来源于stack exchange，提问作者Souryuusen