JPA ManyToMany duplicate key issue

Question

I have the following issue. I have 2 entities, Movie and Actor.

Relation

A movie has multiple actors but an actor can be in multiple movies. Now the problem occurs when I'm trying to add 2 movies that have an actor in common.

Let's say I'm adding deadpool and after that I'm adding deadpool 2. When I'm adding the second movie I get the following error :

The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint

Now this is valid because jpa is inserting an actor to the database that already exists. I don't know how I can solve this, I get the id's from TheMovieDatabase and I don't want to have duplicate Actors in my database.

MOVIE:

@Entity(name = "Movie")
public class Movie {

//instance variables
@Positive
@Id
private long tmdbId;

...

@ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE})
@JoinTable(name = "movie_actor",
        joinColumns = @JoinColumn(name = "movie_tmdbid"),
        inverseJoinColumns = @JoinColumn(name = "actor_id")
)
private Set<Actor> actors = new HashSet<>();

...

Actor:

@Entity(name = "Actor")
public class Actor {

@Id
private long id;

...

@ManyToMany(mappedBy = "actors")
private Set<Movie> movies = new HashSet<>();

Jpa add method

    public void addMovie(Movie movie) {
    try {
        //open connection
        openConnection();

        //check
        if (movie == null) {
            throw new DatabaseException("You cannot add an empty movie");
        }

        //contains only looks in the current persistence context
        if (entityManager.find(Movie.class,movie.getTmdbId()) != null) {
            throw new MovieAlreadyExistsException("This movie already exists!");
        }

        //begin transaction
        entityManager.getTransaction().begin();

        //add movie & commit
        entityManager.persist(movie);
        entityManager.getTransaction().commit();

    }catch (MovieAlreadyExistsException error){
        throw new MovieAlreadyExistsException(error.getMessage(),error);
    } catch (Exception error) {
        throw new DatabaseException(error.getMessage(), error);
    } finally {
        closeConnection();
    }
}

Method that set's the actors: It will take the actors from TheMovieDatabase and the addMovie is the one from jpa behind a facade.

//add a movie
@PostMapping(value = "/add")
public String saveMovie(@ModelAttribute("movie") @Valid Movie movie, BindingResult result) {
    ...

    try{
            //set actors
        Set<Actor> actors = new HashSet<>(mediaService.getMovieActors(movie.getTmdbId()));
        movie.setActors(actors);

        //add movie with actors
        movieService.addMovie(movie);

StackTrace:

Internal Exception: org.apache.derby.shared.common.error.DerbySQLIntegrityConstraintViolationException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL180530224955890' defined on 'ACTOR'. Error Code: 20000 Call: INSERT INTO ACTOR (ID, MOVIECHARACTER, NAME, PROFILEPICTURE) VALUES (?, ?, ?, ?) bind => [4 parameters bound] Query: InsertObjectQuery(model.movie.actor.Actor@d681)] with root cause ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL180530224955890' defined on 'ACTOR'. at org.apache.derby.client.am.ClientStatement.completeExecute(Unknown Source) at org.apache.derby.client.net.NetStatementReply.parseEXCSQLSTTreply(Unknown Source) at org.apache.derby.client.net.NetStatementReply.readExecute(Unknown Source) at org.apache.derby.client.net.StatementReply.readExecute(Unknown Source) at org.apache.derby.client.net.NetPreparedStatement.readExecute_(Unknown Source) at org.apache.derby.client.am.ClientPreparedStatement.readExecute(Unknown Source) at org.apache.derby.client.am.ClientPreparedStatement.flowExecute(Unknown Source) at org.apache.derby.client.am.ClientPreparedStatement.executeUpdateX(Unknown Source) at org.apache.derby.client.am.ClientPreparedStatement.executeUpdate(Unknown Source) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:895) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:967) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:637) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:564) at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2093) at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:309) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:270) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:256) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.insertObject(DatasourceCallQueryMechanism.java:405) at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:165) at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:180) at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:502) at org.eclipse.persistence.queries.InsertObjectQuery.executeCommit(InsertObjectQuery.java:80) at org.eclipse.persistence.queries.InsertObjectQuery.executeCommitWithChangeSet(InsertObjectQuery.java:90) at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:314) at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:58) at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:911) at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:810) at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:108) at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:85) at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2979) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1892) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1874) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1824) at org.eclipse.persistence.internal.sessions.CommitManager.commitNewObjectsForClassWithChangeSet(CommitManager.java:227) at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsForClassWithChangeSet(CommitManager.java:194) at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:139) at org.eclipse.persistence.internal.sessions.AbstractSession.writeAllObjectsWithChangeSet(AbstractSession.java:4384) at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1491) at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1581) at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitRootUnitOfWork(RepeatableWriteUnitOfWork.java:278) at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitAndResume(UnitOfWorkImpl.java:1218) at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:134) at model.db.types.MovieDatabaseJpa.addMovie(MovieDatabaseJpa.java:66) at model.MovieService.addMovie(MovieService.java:19) at web.controller.MovieController.saveMovie(MovieController.java:72) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:209) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:870) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:776) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:991) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:925) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:978) at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:881)

Can anyone point me in the right direction? Thank you!

Answer 1

I solved the issue:

I have used merge instead of persist because if the entity exists it will update it otherwise insert it.