SpringBoot学习之旅(八)---JPA进阶篇之联表操作

阳光穿透心脏的1/2处 2022-03-06 03:03 320阅读 0赞

### 文章目录 ###

*   *   *   *  前言
             *  源码下载
             *  其他文章
             *  数据关联关系映射
             *  数据库创建
             *  数据库表映射对象
             *  操作及测试
             *   *  学生操作及测试
                 *  其他功能测试
             *  常见问题
             *  总结

#### 前言 ####

前两节有说道JPA的基础操作及JPA自定义查询、修改、分页等操作，有兴趣的可以去看看，前面两章的所有操作都只是在操作一张表，但是真实的项目中，不可能所有的业务都只去做单标的操作，因为这不符合数据库表的设计法则，因此，各个表之间就总会发生那么点正当、或者不正当的关系；那么我们就得去理清各个要素之间的关系；

#### 源码下载 ####

[点击开源中国下载源码][Link 1]

> 由于代码内容比较多多，建议优先下载代码，对着代码读更加快捷

#### 其他文章 ####

[SpringBoot学习之旅(七)—JPA进阶篇之自定义查询、修改、分页][SpringBoot_JPA]

--------------------

#### 数据关联关系映射 ####

<table> 
 <thead> 
  <tr> 
   <th>关系类型</th> 
   <th>Owning-Side</th> 
   <th>Inverse-Side</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>one-to-one</td> 
   <td>@OneToOne</td> 
   <td>@OneToOne(mappedBy=“othersideName”)</td> 
  </tr> 
  <tr> 
   <td>one-to-many / many-to-one</td> 
   <td>@ManyToOne</td> 
   <td>@OneToMany(mappedBy=“xxx”)</td> 
  </tr> 
  <tr> 
   <td>many-to-many</td> 
   <td>@ManyToMany</td> 
   <td>@ManyToMany(mappedBy =“xxx”)</td> 
  </tr> 
 </tbody> 
</table>

针对以上关系，举一下常见例子

*  一对一  
    一个学生只能对应一个档案编号及学号
 *  一对多/多对一  
    一个班级可以容纳多个学生
 *  多对多  
    一个学生可以选择多门学科，一个学科允许有多个学生学习

#### 数据库创建 ####

*  表结构
    
        -- 学生信息表
        DROP TABLE IF EXISTS `student_info`;
        CREATE TABLE `student_info` (
          `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '索引ID',
          `stu_name` varchar(20) NOT NULL COMMENT '学生姓名',
          `stu_sex` int(2) NOT NULL COMMENT '学生性别',
          `stu_age` int(3) NOT NULL COMMENT '学生年龄',
          `stu_class_id` int(5) NOT NULL COMMENT '学生班级ID',
          `stu_file_id` int(5) NOT NULL COMMENT '学生档案ID',
          PRIMARY KEY (`id`),
          KEY `student_info_class_id` (`stu_class_id`),
          KEY `student_info_file_id` (`stu_file_id`),
          CONSTRAINT `student_info_class_id` FOREIGN KEY (`stu_class_id`) REFERENCES `class_info` (`id`),
          CONSTRAINT `student_info_file_id` FOREIGN KEY (`stu_file_id`) REFERENCES `file_info` (`id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
        
        -- ----------------------------
        -- Records of student_info
        -- ----------------------------
        INSERT INTO `student_info` VALUES ('1', '张三', '0', '7', '1', '1');
        INSERT INTO `student_info` VALUES ('2', '李四', '1', '7', '1', '2');
        INSERT INTO `student_info` VALUES ('3', '王五', '2', '7', '2', '3');
        
        -- 班级表
        DROP TABLE IF EXISTS `class_info`;
        CREATE TABLE `class_info` (
          `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '班级ID',
          `class_name` varchar(255) DEFAULT NULL COMMENT '班级名称',
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
        
        -- ----------------------------
        -- Records of class_info
        -- ----------------------------
        INSERT INTO `class_info` VALUES ('1', '一年级一班');
        INSERT INTO `class_info` VALUES ('2', '一年级二班');
        INSERT INTO `class_info` VALUES ('3', '一年级三班');
        INSERT INTO `class_info` VALUES ('4', '二年级一班');
        
        --档案表
        DROP TABLE IF EXISTS `file_info`;
        CREATE TABLE `file_info` (
          `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '索引ID',
          `file_id` int(11) DEFAULT NULL COMMENT '档案编号',
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
        
        -- ----------------------------
        -- Records of file_info
        -- ----------------------------
        INSERT INTO `file_info` VALUES ('1', '100001');
        INSERT INTO `file_info` VALUES ('2', '100002');
        INSERT INTO `file_info` VALUES ('3', '100003');
        
        -- 科目表
        DROP TABLE IF EXISTS `subject_info`;
        CREATE TABLE `subject_info` (
          `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '索引ID',
          `subject_name` varchar(50) DEFAULT NULL COMMENT '科目名称',
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
        
        -- ----------------------------
        -- Records of subject_info
        -- ----------------------------
        INSERT INTO `subject_info` VALUES ('1', '语文');
        INSERT INTO `subject_info` VALUES ('2', '数学');
        INSERT INTO `subject_info` VALUES ('3', '英语');
        INSERT INTO `subject_info` VALUES ('4', '体育');
        INSERT INTO `subject_info` VALUES ('5', '音乐');
        
        -- 学生与科目关联表
        DROP TABLE IF EXISTS `subject_selection_info`;
        CREATE TABLE `subject_selection_info` (
          `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '索引ID',
          `student_id` int(11) NOT NULL COMMENT '学生ID',
          `subject_id` int(11) NOT NULL COMMENT '科目ID',
          PRIMARY KEY (`id`),
          KEY `subject_selection_info_stu_id` (`student_id`),
          KEY `subject_selection_info_sub_id` (`subject_id`),
          CONSTRAINT `subject_selection_info_stu_id` FOREIGN KEY (`student_id`) REFERENCES `student_info` (`id`),
          CONSTRAINT `subject_selection_info_sub_id` FOREIGN KEY (`subject_id`) REFERENCES `subject_info` (`id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
        
        -- ----------------------------
        -- Records of subject_selection_info
        -- ----------------------------
        INSERT INTO `subject_selection_info` VALUES ('1', '1', '1');
        INSERT INTO `subject_selection_info` VALUES ('2', '1', '2');
        INSERT INTO `subject_selection_info` VALUES ('3', '1', '3');
        INSERT INTO `subject_selection_info` VALUES ('4', '1', '4');
        INSERT INTO `subject_selection_info` VALUES ('5', '2', '1');
        INSERT INTO `subject_selection_info` VALUES ('6', '2', '2');
        INSERT INTO `subject_selection_info` VALUES ('7', '2', '3');
        INSERT INTO `subject_selection_info` VALUES ('8', '2', '5');
        INSERT INTO `subject_selection_info` VALUES ('9', '3', '1');
        INSERT INTO `subject_selection_info` VALUES ('10', '3', '2');
        INSERT INTO `subject_selection_info` VALUES ('11', '3', '3');
 *  数据库ER图表  
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x1cGVuZ2ZlaTEwMDk_size_16_color_FFFFFF_t_70][]

#### 数据库表映射对象 ####

本文的所有对象均使用Lombok生成对象get set方法

*  **学生对象**
    
        package com.lupf.springbootjpa.dbobject;
        
        import lombok.Data;
        import lombok.ToString;
        
        import javax.persistence.*;
        import java.io.Serializable;
        import java.util.List;
        
        @Data
        @Entity
        public class StudentInfo implements Serializable {
            @Id
            @GeneratedValue(strategy = GenerationType.IDENTITY)
            private Integer id;
            private String stuName;
            private Integer stuSex;
            private Integer stuAge;
        
            //---------以下配置是为了方便查询学生信息的时候一并查询出其关联的信息
        
            //由于学号及档案信息是一对一的关系，因此这里是一对一的
            @OneToOne(cascade = CascadeType.ALL)
            //name为当前表中列的名称  referencedColumnName标表示外键关联了对方的表内容
            @JoinColumn(name = "stu_file_id", referencedColumnName = "id")
            private FileInfo fileInfo;
        
            //对于学生来说，是多个学生对应一个班级，因此这里是一对多的关系
            //设置为CascadeType.MERGE时，当添加数据的时候，会自动插入class表中的数据
            @ManyToOne(cascade = CascadeType.MERGE)
            //name在表中的列
            @JoinColumn(name = "stu_class_id", referencedColumnName = "id")
            private ClassInfo classInfo;
        
            //这里的fetch要使用EAGER(全部抓取)  LAZY懒加载会报错
            //由于一个学生可以选择多个科目 因此这里相对与科目来说是一对多的关系
            //设置为CascadeType.MERGE时，当添加数据的时候，会自动插入subject_selection_info表中的数据
            @ManyToMany(cascade = CascadeType.MERGE, fetch = FetchType.EAGER)
            //由于学生表和所选的科目表是使用subject_selection_info关联的  因此这里需要设置他们的关联关系
            @JoinTable(name = "subject_selection_info", joinColumns = @JoinColumn(name = "student_id"), inverseJoinColumns = @JoinColumn(name = "subject_id"))
            private List<SubjectInfo> subjectInfos;
        }
 *  **科目对象**
    
        package com.lupf.springbootjpa.dbobject;
        
        import com.alibaba.fastjson.annotation.JSONField;
        import lombok.Data;
        
        import javax.persistence.*;
        import java.io.Serializable;
        import java.util.List;
        
        @Data
        @Entity
        public class SubjectInfo implements Serializable {
            @Id
            @GeneratedValue(strategy = GenerationType.IDENTITY)
            private Integer id;
            private String subjectName;
        
            //这里需要加上反序列化忽略，因为StudentInfo关联了SubjectInfo 这里又关联了StudentInfo
            // 就出现了你中有我，我中有你的问题，从未导致反序列话的时候堆栈溢出(死循环)的问题
            @JSONField(serialize = false)
            //这里关联是为了满足，删除这里面的数据的时候，自动删除subject_selection_info并在学生与科目之间的关系
            @ManyToMany(cascade = CascadeType.MERGE, fetch = FetchType.EAGER)
            @JoinTable(name = "subject_selection_info", joinColumns = @JoinColumn(name = "subject_id"), inverseJoinColumns = @JoinColumn(name = "student_id"))
            private List<StudentInfo> studentInfos;
        }
 *  **班级对象**
    
        package com.lupf.springbootjpa.dbobject;
        
        import lombok.Data;
        
        import javax.persistence.*;
        import java.util.List;
        import java.util.Objects;
        import java.util.Set;
        
        @Data
        @Entity
        public class ClassInfo {
            @Id
            @GeneratedValue(strategy = GenerationType.IDENTITY)
            private Integer id;
            @Column(insertable = false, updatable = false)
            private String className;
        
        //    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
        //    @JoinColumn(name = "id")
        //    private List<StudentInfo> studentInfos;
        }
 *  **档案对象**
    
        package com.lupf.springbootjpa.dbobject;
        
        import lombok.Data;
        
        import javax.persistence.*;
        import java.util.Objects;
        
        @Data
        @Entity
        public class FileInfo {
            @Id
            @GeneratedValue(strategy = GenerationType.IDENTITY)
            private Integer id;
            private Integer fileId;
        }
 *  **学生及科目关系映射对象**
    
        package com.lupf.springbootjpa.dbobject;
        
        import lombok.Data;
        
        import javax.persistence.*;
        
        @Data
        @Entity
        public class SubjectSelectionInfo {
            @Id
            @GeneratedValue(strategy = GenerationType.IDENTITY)
            private Integer id;
        
            @Column(name = "student_id")
            private Integer studentId;
        
            @Column(name = "subject_id")
            private Integer subjectId;
        
            @ManyToOne(cascade = CascadeType.ALL)
            //name在表中的列
            @JoinColumn(name = "student_id", referencedColumnName = "id", insertable = false, updatable = false)
            private StudentInfo studentInfo;
        
            @ManyToOne(cascade = CascadeType.ALL)
            //name在表中的列
            @JoinColumn(name = "subject_id", referencedColumnName = "id", insertable = false, updatable = false)
            private SubjectInfo subjectInfo;
        }

#### 操作及测试 ####

##### 学生操作及测试 #####

*  **StudentRepository**
    
        @Repository
        public interface StudentRepository extends JpaRepository<StudentInfo, Integer> {
        
            //根据班级ID查询学生信息
            @Query(value = "select a.* from student_info as a where stu_class_id = ?1", nativeQuery = true)
            List<StudentInfo> findByStuClassId(Integer classId);
        }
 *  **StudentService**
    
        package com.lupf.springbootjpa.service;
        
        import com.lupf.springbootjpa.dbobject.StudentInfo;
        
        import java.util.List;
        
        /**
         * 学生的Service
         */
        public interface StudentService {
        
            //添加学生 并添加学生的档案信息、分班信息以及学生选择的科目
            StudentInfo save(StudentInfo studentInfo);
        
            //查询某个学生的信息
            StudentInfo findById(Integer id);
        
            //删除学生信息
            void deleteById(Integer id);
        
            //修改信息
            StudentInfo update(StudentInfo studentInfo);
        
            //根据班级名称查询学生信息
            List<StudentInfo> findByStuClassId(Integer classId);
        }
 *  **StudentServiceImpl**
    
        package com.lupf.springbootjpa.service.impl;
        
        import com.lupf.springbootjpa.dbobject.StudentInfo;
        import com.lupf.springbootjpa.repository.StudentRepository;
        import com.lupf.springbootjpa.service.StudentService;
        import org.springframework.beans.factory.annotation.Autowired;
        import org.springframework.stereotype.Service;
        
        import java.util.List;
        import java.util.Optional;
        
        @Service
        public class StudentServiceImpl implements StudentService {
        
            @Autowired
            StudentRepository studentRepository;
        
            @Override
            public StudentInfo findById(Integer id) {
                Optional<StudentInfo> studentInfoOptional = studentRepository.findById(id);
                if (null != studentInfoOptional && studentInfoOptional.isPresent())
                    return studentInfoOptional.get();
                return null;
            }
        
            @Override
            public void deleteById(Integer id) {
                studentRepository.deleteById(id);
            }
        
            @Override
            public StudentInfo update(StudentInfo studentInfo) {
                return studentRepository.save(studentInfo);
            }
        
            @Override
            public List<StudentInfo> findByStuClassId(Integer classId) {
                List<StudentInfo> studentInfos = studentRepository.findByStuClassId(classId);
                return studentInfos;
            }
        
            @Override
            public StudentInfo save(StudentInfo studentInfo) {
                StudentInfo saveStudentInfo = studentRepository.save(studentInfo);
                return saveStudentInfo;
            }
        }
 *  **学生测试用例**
    
        @RunWith(SpringRunner.class)
        @SpringBootTest
        @Slf4j
        public class StudentServiceImplTest {
            @Autowired
            StudentService studentService;
        
            @Test
            public void findById() {
                //查询学生信息会一并查处用户所属的班级信息及选择的科目信息
                StudentInfo studentInfo = studentService.findById(1);
                log.info(JSON.toJSONString(studentInfo));
            }
        
        
            @Test
            public void deleteById() {
                //用户时会级联删除学生信息  学生档案信息  学生科目选择信息
                studentService.deleteById(16);
            }
        
            @Test
            public void update() {
                StudentInfo studentInfo = studentService.findById(20);
                studentInfo.setStuAge(8);
                ClassInfo classInfo = new ClassInfo();
                //这个classID必须在class_info表中存在，否则这里会报错
                classInfo.setId(2);
                studentInfo.setClassInfo(classInfo);
                studentService.update(studentInfo);
            }
        
            @Test
            public void save() {
                StudentInfo studentInfo = new StudentInfo();
                studentInfo.setStuAge(8);
                studentInfo.setStuName("唐七");
                studentInfo.setStuSex(1);
        
                //级联更新 这里设置的id属性在添加学生信息的时候会一并将这部分数据添加到class_info表
                ClassInfo classInfo = new ClassInfo();
                //这个classID必须在class_info表中存在，否则这里会报错
                classInfo.setId(2);
                studentInfo.setClassInfo(classInfo);
        
                //绑定并插入学生的用户信息
                //这里是级联插入 添加的时候自动插入相关的档案信息
                FileInfo fileInfo = new FileInfo();
                fileInfo.setFileId(100004);
                studentInfo.setFileInfo(fileInfo);
        
                //级联更新学生选择的科目信息
                //语文
                SubjectInfo subjectInfo = new SubjectInfo();
                //这里的ID必须在subject_info表中存在，否则这里会报错
                subjectInfo.setId(1);
                //数学
                SubjectInfo subjectInfo2 = new SubjectInfo();
                //这里的ID必须在subject_info表中存在，否则这里会报错
                subjectInfo2.setId(2);
                //英语
                SubjectInfo subjectInfo3 = new SubjectInfo();
                //这里的ID必须在subject_info表中存在，否则这里会报错
                subjectInfo3.setId(3);
        
                List<SubjectInfo> subjectInfos = new ArrayList<>();
                subjectInfos.add(subjectInfo);
                subjectInfos.add(subjectInfo2);
                subjectInfos.add(subjectInfo3);
        
                studentInfo.setSubjectInfos(subjectInfos);
                studentService.save(studentInfo);
            }
        
            @Test
            public void findByStuClassId() {
                List<StudentInfo> s = studentService.findByStuClassId(2);
                String va = JSON.toJSONString(s, SerializerFeature.DisableCircularReferenceDetect);
                log.info(va);
            }
        }
 *  测试详情
    
     *  根据ID查询用户  
        ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x1cGVuZ2ZlaTEwMDk_size_16_color_FFFFFF_t_70 1][]
     *  插入测试  
        下图可见，插入学生信息的时候，同步插入了档案信息、班级信息及选择科目的关联关系![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x1cGVuZ2ZlaTEwMDk_size_16_color_FFFFFF_t_70 2][]  
        ![20190319153642420.][]  
        ![20190319153724670.][]  
        ![20190319154030301.][]
 *  修改测试  
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x1cGVuZ2ZlaTEwMDk_size_16_color_FFFFFF_t_70 3][]  
    ![20190319160204742.][]
 *  学生信息删除测试  
    删除学生的时候会同步删除档案信息及学生管理的科目信息  
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x1cGVuZ2ZlaTEwMDk_size_16_color_FFFFFF_t_70 4][]

##### 其他功能测试 #####

其他对象为一些基础性的操作，没有什么特殊性，因此，这里就不占用篇幅，需要了解的可以下载源码查看。

#### 常见问题 ####

*  could not initialize proxy - no Session  
    ![20190319160858885.][]  
    由于对象中管理了其他的对象，因此这里使用懒加载会出现未找到session的问题，需要将LAZY改为EAGER  
    ![20190319161025122.][]
 *  java.lang.StackOverflowError  
    为Json反序列化导致的堆栈溢出  
    一般出现ManyToMany下，因为两个对象是你中有我，我中有你的关系，从而反序列化时导致递归堆栈溢出；  
    解决方案：可以使用@JSONField(serialize = false)注解忽略对应对象的反序列化  
    ![20190319161647563.][]
 *  Multiple representations of the same entity \[…\] are being merged. Detached: \[…\]; Detached: \[…\]; nested exception is java.lang.IllegalStateException: Multiple representations of the same entity \[…\] are being merged. Detached: \[…\]; Detached: \[…\]  
    更新修改的时候，合并对象的问题  
    ![20190319162159804.][]  
    以上示例为将用户从其他班调整到id为3的班级，可能会报以上的错  
    解决方案:  
    在application.yml中添加以下配置
    
        spring:
          jpa:
            show-sql: true
            properties:
              hibernate:
                event:
                  merge:
                    entity_copy_observer: allow

#### 总结 ####

到此，使用JPA操作关联表已经完了，可以看出，使用JPA，我们只需要关注于业务流程，几乎不用、或者很少去关注数据库那边的操作，从手动档直接换成了自动档，从而让我们有更多的精力去写业务层那一块儿的代码。

[Link 1]: https://gitee.com/pengfeilu/spring-boot-jpa/tree/master/src/main/java/com/lupf/springbootjpa
[SpringBoot_JPA]: https://blog.csdn.net/lupengfei1009/article/details/88579877
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x1cGVuZ2ZlaTEwMDk_size_16_color_FFFFFF_t_70]: /images/20220306/8bb3f25af90b40e2ab737fd063dbee2d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x1cGVuZ2ZlaTEwMDk_size_16_color_FFFFFF_t_70 1]: /images/20220306/62a19b99b5c544f2b5e353b86a428a2b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x1cGVuZ2ZlaTEwMDk_size_16_color_FFFFFF_t_70 2]: /images/20220306/a97fedb06fcb4ae28ca0d5ad65a67591.png
[20190319153642420.]: /images/20220306/8905d93d6431458aa04aa870e68bfae3.png
[20190319153724670.]: /images/20220306/d9efadfecded4cc78380447b8f954f4c.png
[20190319154030301.]: /images/20220306/5ca0aa4453744ae2a0a792979bdaea61.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x1cGVuZ2ZlaTEwMDk_size_16_color_FFFFFF_t_70 3]: /images/20220306/22d3a6d8d01c48d1920a549f0acbf009.png
[20190319160204742.]: /images/20220306/656a28db835e48fe9c651f6dcb41e8a5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x1cGVuZ2ZlaTEwMDk_size_16_color_FFFFFF_t_70 4]: /images/20220306/1608dcd6e2e642fe97766daf121304b4.png
[20190319160858885.]: /images/20220306/d9f466f5a7ff49bc8a6d41207b34858c.png
[20190319161025122.]: /images/20220306/c28afa20504540ffb04ddf8a68e91e44.png
[20190319161647563.]: /images/20220306/9cddddcc0c764f9f951601257400d294.png
[20190319162159804.]: /images/20220306/50116090f28e45d59c5834c93235d5ab.png