[FIXED] JPA ManToMany association and insert, retrieve data with Spring Data Repository

Issue

I need to setup many to many relationship between 2 entities, namely Project and Articles. The use case is a project that can be linked to many articles which are related. So does each article will be linked to various projects. I am using Spring data repository in my project and having trouble persisting and retrieving this relationship with the project and articles.

@Entity @Table(name = "projects")
@Getter @Setter
public class Project {
    @Id
    String id;
    String name;

    @ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinTable(name = "project_articles",
        joinColumns = { @JoinColumn(name = "project_id", referencedColumnName = "id") },
        inverseJoinColumns = { @JoinColumn(name = "article_id", referencedColumnName = "id") })
    private List<Article> articles = new ArrayList<>();

    public Project addArticle(Article p) {
        if (articles == null) articles = new ArrayList<>();
        p.getProjects().add(this);
        articles.add(p);
        return this;
    }
}

Article entity

@Entity @Table(name = "articles")
@Getter @Setter
public class Article {

    @Id
    String id;
    String title;
    String author;
    String body;

    @ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinTable(name = "project_articles")
    List<Project> projects = new ArrayList<>();

}

ProjectArticles join table entity. I have created it to resolve table creation issue in hibernate.

@Entity(name = "ProjectArticles")
@Table(name = "project_articles")
@AssociationOverrides({
    @AssociationOverride(name = "key.project", joinColumns = @JoinColumn(name = "project_id")),
    @AssociationOverride(name = "key.article", joinColumns = @JoinColumn(name = "article_id")) }
)
@Getter @Setter
public class ProjectArticles {
    @EmbeddedId ProjectArticleId key;

    @Embeddable @Getter @Setter
    public static class ProjectArticleId {
        @Column(name = "project_id", columnDefinition = "varchar(255)")
        ProjectEntity project;

        @Column(name = "article_id", columnDefinition = "varchar(255)")
        Article article;
    }
}

Here are the spring data repositories.

public interface ProjectRepository extends JpaRepository<Project, String> {
    public Project findProjectByName(String name);
    public List<Project> findProjectByArticle(String articleId);
}

public interface ArticleRepository extends JpaRepository<Article, String> {
    public List<Article> findArticlesByTitle(String title);

    // NOTE: Does not work
    @Query("SELECT a from Article a JOIN FETCH a.projects LEFT JOIN ProjectEntity p ON p.id = :project")
    public List<Article> findArticlesByProject(@Param("project") String project);

    @Query("SELECT a from Article a LEFT JOIN ProjectEntity p ON p.id = :project")
    public List<Article> findArticleByProject(ProjectEntity project);
}

Ideally, I want to use repo in service like below.

Article ar1 = articleRepository.save(new Article("Article title", "Author 1"));
Article ar2 = articleRepository.save(new Article("Article title 2", "Author 2"));
Article ar3 = articleRepository.save(new Article("Article title 3", "Author 3"));

Project pr1 = projectRepository.save(new Project("Project name 1"));
Project pr2 = projectRepository.save(new Project("Project name 2"));
Project pr3 = projectRepository.save(new Project("Project name 3"));

pr1.addArticle(ar1);
pr1.addArticle(ar2);
pr2.addArticle(ar1);
pr2.addArticle(ar2);

ar3.addProject(pr3);
ar3.addProject(pr1);
ar3.addProject(pr2);

articleRepository.findArticlesByProject("project-2-id");
projectRepository.findProjectByArticle("article-3-id");

But things do not work. When I add an existing article to a project and save (update) it, in the relation table ("project_articles") new row tries to enter but without a project id and I get an exception.

What am I doing wrong here? Is it possible the way I designed the entities?

Solution

The main idea of CascadeType.PERSIST (involved in CascadeType.ALL you use) is to create entity instances, set necessary two-side relations (Project -> Articles, Article -> Projects) and only then save to a database. If you save all projects then articles are saved implicitly due to CascadeType.PERSIST and entity relations

Try this

@Transactional
public void save() {
    Article ar1 = new Article("Article title", "Author 1");
    Article ar2 = new Article("Article title 2", "Author 2");
    Article ar3 = new Article("Article title 3", "Author 3");

    Project pr1 = new Project("Project name 1");
    Project pr2 = new Project("Project name 2");
    Project pr3 = new Project("Project name 3");

    pr1.addArticle(ar1);
    pr1.addArticle(ar2);
    pr1.addArticle(ar3);

    pr2.addArticle(ar1);
    pr2.addArticle(ar2);
    pr2.addArticle(ar3);

    pr3.addArticle(ar3);

    projectRepository.saveAll(Arrays.asList(pr1, pr2, pr3));
}

Answered By - alex valuiskyi
Answer Checked By - Terry (JavaFixing Volunteer)