Spring JPA将具有复杂关系M：N和1：N的嵌套表相乘 [英] Spring JPA multiply nested tables with complicated relationship M:N and 1:N

问题描述

我正在尝试构建一个简单的API，该API向前端提供一些数据。

我正在努力在Spring中与数据库以及与实体的关联。让我们逐步进行操作，以便您清楚地了解我的需求。

我的示例与本课程非常相似：

模式2

我在架构2的 rating 表中缺少FK定义（请忽略

我是Spring JPA的初学者，所以我缺少一些经验，这可能是一个简单的问题。在本地，我的数据库具有完全不同的名称，更多的列和绝对不同的用途。但是我想让这个例子尽可能的简单。

现在，让我们假设我为每个表（除了连接表）都有实体，并且我已经正确配置了M： N与@ManyToMany和@Jointable装饰器的关系

Edit

我想使用Schema 2.问题是，我是否可以创建一个方法名称，该名称将自动创建具有所需结果的查询？ （第3段）。如果是这样，要求是什么？我必须为连接表创建实体，还是为复合键创建可嵌入实体？以及我如何精确地将 rating 表与此联接表联接？

EDIT 2

我创建了一个新的问题，该问题已简化并且应该

解决方案

如果选择 Schema 2
，那么您将无法获得理想的结果，因为学生不得访问该课程来对课程进行评分。您希望获得这些课程的评分，但是它们是独立的。

I am trying to build a simple API which provides some data to the frontend.

I am struggling with database and association to the entities in Spring. Let's go step by step so you understand clearly what I need.

My example is very similar to this course: https://www.baeldung.com/jpa-many-to-many. Look at the 3.1 section.

1) Let's say, we have a student table and course table. Between these, there is a join table (let's call it for example studentCourseJoin). Ok, this seems to be nearly same as presented on Baeldung site. But in my case I need to have additional table ratings which is separated from join table. And assume that Student can rate course multiple times (let's say once a year)

2) My goal is to have data structure like this:

{
  students: [
    {
      studentID: 1,
      courses: [
        {
          courseID: 1,
          ratings: [{
            ratingID: 1,
            studentID: 1
          }]
        }
      ]
    }
  ]
}

So, if I have a student with ID 1, I want to list all his courses and all his ratings he made to the given course (as presented in the example).

I am having problem with ratings. Because I always get all ratings to the given course no matter who made the rating. Basically I am missing just one WHERE clause.

Example result:

{
  students: [
    {
      studentID: 1,
      courses: [
        {
          courseID: 1,
          ratings: [{
            ratingID: 1,
            studentID: 1
          },{
            ratingID: 2,
            studentID: 5
          }]
        }
      ]
    }
  ]

Making a custom query isn't hard.. It's just about 3 JOINs. But that is not what I want. I want to set up all entities right, so Hibernate can automatically make queries, delete orphans etc...

3) Input data: studentID: 1 (and another scenario: studentID: 1, yearRated: 2020)

4) My question? How can I integrate ratings table so it applies filter WHERE studentId == 1. In seconds scenario WHERE studentId == 1 AND yearRated == 2020

What schema would I choose between these 2 ? Or do you propose different?

Schema 1

Schema 2

I am missing FK definition in rating table in Schema 2 (just ignore it)

I am a beginner with Spring JPA so I am missing some experience and this could be an easy question. Locally my database has quite different names and much more columns and absolutely different purpose. But I wanted to make this example as easy as possible.

For now, let's assume I have entity for each table (except join table) and I have correctly configured M:N relationship with @ManyToMany and @Jointable decorators

Edit

Let's say, that I would like to use Schema 2. Question is, am I able to create a method name which will automatically create query with required result? (Paragraph 3). If so, what are the requirements? Do I have to create entity for join table and also embeddable entity for composite key ? and how exactly do I join rating table with this join table?

EDIT 2

I created a new question which is simplified and should be more understandable.

解决方案

If you choose Schema 2 Than you can not get the desired result, because a student must not visit the course to rate the course. You expect the ratings under the courses, but there are independent.

这篇关于Spring JPA将具有复杂关系M：N和1：N的嵌套表相乘的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！