spring-data-jpa Spring JPA中的多级条件一对多Map

我需要在Spring JPA中有一个如下所示的Map

Teacher -> Class -> Subject

老师有课，课应该有课。
我正在开发一个学校管理系统。
我有以下表格

MariaDB [school-mgmt-sys]> select * from subject;
+----+---------+----------------------------+----------------------------+---------+
| id | code    | date_created               | last_updated               | name    |
+----+---------+----------------------------+----------------------------+---------+
|  1 | MATHS   | 2022-07-20 14:21:20.000000 | 2022-07-20 14:21:20.000000 | Maths   |
|  2 | SCIENCE | 2022-07-20 14:21:34.000000 | 2022-07-20 14:21:34.000000 | Science |
|  3 | HINDI   | 2022-07-20 14:21:59.000000 | 2022-07-20 14:21:59.000000 | Hindi   |
|  4 | SOCIAL  | 2022-07-20 14:22:10.000000 | 2022-07-20 14:22:10.000000 | Social  |
|  5 | ENGLISH | 2022-07-20 14:22:16.000000 | 2022-07-20 14:22:16.000000 | English |
+----+---------+----------------------------+----------------------------+---------+
5 rows in set (0.00 sec)

MariaDB [school-mgmt-sys]> select * from class;
+----+---------+----------------------------+----------------------------+---------+
| id | code    | date_created               | last_updated               | name    |
+----+---------+----------------------------+----------------------------+---------+
|  1 | GRADE_1 | 2022-07-20 14:23:49.000000 | 2022-07-20 14:23:49.000000 | Grade 1 |
|  2 | GRADE_2 | 2022-07-20 14:23:56.000000 | 2022-07-20 14:23:56.000000 | Grade 2 |
+----+---------+----------------------------+----------------------------+---------+
2 rows in set (0.00 sec)

MariaDB [school-mgmt-sys]> select * from teacher;
+----+----------------------------+--------------+------------+-----------+----------------------------+------------+
| id | date_created               | email        | first_name | last_name | last_updated               | phone_no   |
+----+----------------------------+--------------+------------+-----------+----------------------------+------------+
|  1 | 2022-07-20 14:41:18.000000 | abdm@g.com   | Abdul      | Mannan    | 2022-07-20 14:41:18.000000 | 9911223344 |
|  2 | 2022-07-20 14:41:39.000000 | anjgk@g.com  | Anji       | G Konda   | 2022-07-20 14:41:39.000000 | 9911223355 |
|  3 | 2022-07-20 14:42:04.000000 | nvdShk@g.com | Naveed     | Shaik     | 2022-07-20 14:42:04.000000 | 9911223366 |
+----+----------------------------+--------------+------------+-----------+----------------------------+------------+
3 rows in set (0.00 sec)

我想创建一个如下所示的Map

Teacher -> Set<Class> -> Set<Subject>

老师将教多个班级。在每一个班级里，他将教不同的科目。
例如：

Teacher1 - Class1 -> Sub1, Sub2
         - Class2 -> Sub1, Sub4

因此，我想有一个Map，当我获取一个教师，它必须有所有的类与各自的科目，他的教师。输出预期是

teacher {
    "name" : "Abdul",
    "classes" [
        {
            "id": "Class1",
            "name": "Class 1"
            "subjects": [
                {
                    "id" : "SUB1",
                    "name" "MAths"
                },
                {
                    "id" : "SUB2",
                    "name" "HIndi"              
                }
            ]
        },
        {
            "id": "Class2",
            "name": "Class 2",
            "subjects": [
                {
                    "id" : "SUB1",
                    "name" "MAths"
                },
                {
                    "id" : "SUB4",
                    "name" "English"                
                }
            ]           
        }
    ]
}

我在下面试了试。

@Entity
@Table(name = "teacher")
public class SMSTeacher {

    ...

    @OneToMany(mappedBy = "teacher")
    private Set<SMSClass> classes;

}

@Entity
@Table(name = "class")
public class SMSClass {

    ...

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "smsClass")
    private Set<SMSSubject> subjects;

    @ManyToOne
    private SMSTeacher teacher;

}

@Entity
@Table(name = "subject")
public class SMSSubject {

    ...

    @ManyToOne
    private SMSClass smsClass;
}

它已经创建了下面的表结构，我插入了数据

MariaDB [school-mgmt-sys]> select * from teacher;
+----+----------------------------+--------------+------------+-----------+----------------------------+------------+
| id | date_created               | email        | first_name | last_name | last_updated               | phone_no   |
+----+----------------------------+--------------+------------+-----------+----------------------------+------------+
|  1 | 2022-07-20 17:40:57.000000 | nvdshk@g.com | Navid      | Shaik     | 2022-07-20 17:40:57.000000 | 9911223366 |
|  2 | 2022-07-20 17:41:21.000000 | abdMn@g.com  | Abdul      | Mannna    | 2022-07-20 17:41:21.000000 | 9911223355 |
|  4 | 2022-07-20 17:41:50.000000 | anjgkn@g.com | Anji       | GK        | 2022-07-20 17:41:50.000000 | 9911223377 |
+----+----------------------------+--------------+------------+-----------+----------------------------+------------+
3 rows in set (0.01 sec)

MariaDB [school-mgmt-sys]> select * from class;
+----+---------+----------------------------+----------------------------+---------+------------+
| id | code    | date_created               | last_updated               | name    | teacher_id |
+----+---------+----------------------------+----------------------------+---------+------------+
|  1 | GRADE_1 | 2022-07-20 17:42:46.000000 | 2022-07-20 17:42:46.000000 | Grade 1 |          1 |
|  2 | GRADE_1 | 2022-07-20 17:43:28.000000 | 2022-07-20 17:43:28.000000 | Grade 1 |          2 |
|  3 | GRADE_2 | 2022-07-20 17:44:20.000000 | 2022-07-20 17:44:20.000000 | Grade 2 |          2 |
|  4 | GRADE_2 | 2022-07-20 17:49:21.000000 | 2022-07-20 17:49:21.000000 | Grade 2 |          2 |
+----+---------+----------------------------+----------------------------+---------+------------+
4 rows in set (0.01 sec)

MariaDB [school-mgmt-sys]> select * from subject;
+----+------+----------------------------+----------------------------+---------+--------------+
| id | code | date_created               | last_updated               | name    | sms_class_id |
+----+------+----------------------------+----------------------------+---------+--------------+
|  1 | ENG  | 2022-07-20 17:46:32.000000 | 2022-07-20 17:46:32.000000 | English |            3 |
|  2 | HIN  | 2022-07-20 17:46:40.000000 | 2022-07-20 17:46:40.000000 | Hindi   |            4 |
|  3 | MATH | 2022-07-20 17:46:52.000000 | 2022-07-20 17:46:52.000000 | Maths   |            2 |
|  4 | MATH | 2022-07-20 17:46:59.000000 | 2022-07-20 17:46:59.000000 | Maths   |            1 |
+----+------+----------------------------+----------------------------+---------+--------------+
4 rows in set (0.01 sec)

为了获取上述数据，我使用Spring Data Rest API，当我获取教师时，它将有一个链接导航到类，从那里我可以转到主题。
但是不能做我想要的事情。请帮助。

我尝试了几个解决方案。最后我找到了下面的解决方案。
我已经使用连接表在实体之间创建了一对多Map，以维护表中的唯一数据。
实体：

@Entity
@Table(name = "teacher")
public class SMSTeacher {

    ...

    @OneToMany
    @JoinTable(name = "teacher_class", joinColumns = @JoinColumn(name = "teacher_id"), inverseJoinColumns = @JoinColumn(name = "class_id"))
    private Set<SMSClass> classes = new HashSet<>();

}

@Entity
@Table(name = "class")
public class SMSClass {

    ...

    @OneToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "class_subject", joinColumns = @JoinColumn(name = "class_id"), inverseJoinColumns = @JoinColumn(name = "subject_id"))
    private Set<SMSSubject> subjects = new HashSet<>();;

}

@Entity
@Table(name = "subject")
public class SMSSubject {

    ...
    //No mapping here, Configured only uni directional mapping.
}

第三个表用于维护教师-〉班级-〉科目关系数据。

+----+----------+------------+------------+
| id | class_id | subject_id | teacher_id |
+----+----------+------------+------------+
|  1 |        1 |          3 |          1 |
|  2 |        1 |          5 |          1 |
|  3 |        2 |          2 |          1 |
|  4 |        2 |          4 |          1 |
|  5 |        1 |          3 |          2 |
|  6 |        2 |          3 |          3 |
+----+----------+------------+------------+

创建了一个JPA本机SQL以根据需要提取结果。

public interface TeacherClassSubjectRepo extends JpaRepository<TeacherClassSubject, Long> {

    @Query(value = "select t.id T_ID, t.first_name fName, t.last_name lName, t.phone_no phone, t.email email, c.id C_ID, c.name C_NAME, c.code C_CODE, s.id S_ID, s.name S_NAME, s.code S_CODE from teacher t, class c,subject s, teacher_class_subject tcs where tcs.teacher_id=t.id and tcs.class_id = c.id and tcs.subject_id = s.id and t.id=?1 order by t.id, c.id", nativeQuery = true)
    List<Tuple> findAllByTeacherId(Long teacherId);

}

下面是我的服务Impl方法，用于根据需要生成输出。

@Override
public ResponseEntity<Object> getTeacher(Long teacherId) {

    List<Tuple> teacherClassSubjects = teacherClassSubjectRepo
            .findAllByTeacherId(teacherId);

    if (teacherClassSubjects.size() == 0)
        throw new ResponseStatusException(HttpStatus.NOT_FOUND);

    SMSTeacherDTO smsTeacherDTO = null;

    String fName = "", lName = "", email = "", currCName = "", currCCode = "";
    BigInteger phone = null;
    Set<SMSSubjectDTO> subjectDTOs = new HashSet<>();
    Set<SMSClassDTO> classDTOs = new HashSet<>();

    for (Tuple tuple : teacherClassSubjects) {

        ...
        //Logic to fetch SMSTeacherDTO fields and Logic to construct classDTOs

    }

    smsTeacherDTO = new SMSTeacherDTO(fName, lName, email, phone, classDTOs);

    return new ResponseEntity<>(smsTeacherDTO, HttpStatus.OK);
}

输出量：

{
    "firstName": "Anji",
    "lastName": "GK",
    "email": "anjgkn@g.com",
    "phoneNo": 9911223311,
    "classes": [
        {
            "name": "Grade 2",
            "code": "GRADE_2",
            "subjects": [
                {
                    "name": "Science",
                    "code": "SCIENCE"
                },
                {
                    "name": "Social",
                    "code": "SOCIAL"
                }
            ]
        },
        {
            "name": "Grade 1",
            "code": "GRADE_1",
            "subjects": [
                {
                    "name": "Hindi",
                    "code": "HINDI"
                },
                {
                    "name": "English",
                    "code": "ENGLISH"
                }
            ]
        }
    ]
}

请评论此解决方案是否好，或者是否有更好的解决方案可用于此问题。

谢谢-谢谢

spring-data-jpa Spring JPA中的多级条件一对多Map

1条答案

相关问题

热门标签

最新问答