春季数据JDBC – 多对多示例

川长思鸟来 2023-09-25 12:19 34阅读 0赞

在本教程中，您将看到使用 **Spring 数据 JDBC** 示例的**多对多**数据库表关系的实体映射。

### 1. 多对多表关系 ###

让我们来看看以下数据库表**，这些数据库**表与一个学期中每个**工程分支**的**学科**之间的关系。在工程学院中，每个**分支**都有许多**科目**，每个**科目**可能会在学期内出现在许多**工程分支**中。

![741e38be2163ce7f422530e4ef93d91a.png][]

CREATE TABLE `BRANCH` (
      `BRANCH_ID` int(11) NOT NULL AUTO_INCREMENT,
      `BRANCH_SHORT_NAME` varchar(45) NOT NULL,
      `BRANCH_NAME` varchar(100) NOT NULL,
      `DESCRIPTION` varchar(200) DEFAULT NULL,
      PRIMARY KEY (`BRANCH_ID`)
    );
    
    CREATE TABLE `SUBJECT` (
      `SUBJECT_ID` int(11) NOT NULL AUTO_INCREMENT,
      `SUBJECT_NAME` varchar(100) NOT NULL,
      `SUBJECT_DESC` varchar(200) DEFAULT NULL,
      PRIMARY KEY (`SUBJECT_ID`)
    );
    
    CREATE TABLE `BRANCH_SUBJECT` (
      `BRANCH_ID` int(11),
      `SUBJECT_ID` int(11),
      PRIMARY KEY (`BRANCH_ID`,`SUBJECT_ID`),
      CONSTRAINT `BRANCH_SUB_ID_FK` 
         FOREIGN KEY (`BRANCH_ID`) 
         REFERENCES `BRANCH` (`BRANCH_ID`),
      CONSTRAINT `SUBJECT_SUB_ID_FK` 
         FOREIGN KEY (`SUBJECT_ID`) 
         REFERENCES `SUBJECT` (`SUBJECT_ID`)
    );

### 2. 项目配置 ###

1.1. **使用的技术** ：

1.  Spring Boot 2.3.0.RELEASE
2.  Spring Data JDBC 2.0.0.RELEASE
3.  Spring Framework 5.2.6.RELEASE
4.  H2 / MySql DB
5.  Lombok 1.18.12
6.  JUnit 5

1.2. **Maven 依赖关系** ：

要开始使用弹簧启动与**弹簧数据JDBC，**您需要依赖项。使用[龙目岛][Link 1]来避免样板代码。下面是示例应用程序中使用的完整依赖项列表。`spring-boot-starter-data-jdbc`

<dependencies>
        <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>
        <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-validation</artifactId>
        </dependency>
        <dependency>
          <groupId>com.h2database</groupId>
          <artifactId>h2</artifactId>
        </dependency>
        <dependency>
          <groupId>org.projectlombok</groupId>
          <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <scope>runtime</scope>
        </dependency>
        <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-test</artifactId>
          <scope>test</scope>
        </dependency>
      </dependencies>

### 3. 映射实体 ###

在春季数据 JDBC 中，要创建实体，类应遵循**域驱动**设计 （[DDD][]） 的聚合设计。一个实体只能是 1 个聚合的一部分。

Spring 数据 JDBC 不支持多对多关系，因为在多对多关系中，实体映射中必须有两个聚合根。但是我们可以使用 ID 引用实现多对多实体映射。为了更清楚地了解一下上述多对多表设计的以下实体映射。

3.1. **主题实体** ：

@Data // lombok
    public class Subject {
    
      @Id
      private Long subjectId;
    
      private String subjectDesc;
      private String subjectName;
    
    }

3.2**. 主题引用实体** ：

**主题引用**实体仅用于保存**主题**实体 ID。

@Table("BRANCH_SUBJECT")
    @Data
    @AllArgsConstructor
    public class SubjectRef {
    
      Long subjectId;
    }

3.3. **分支实体映射** ：

@Data
    public class Branch {
    
      @Id
      private Long branchId;
    
      private String branchName;
      @Column("BRANCH_SHORT_NAME")
      private String branchShortName;
      private String description;
    
      @MappedCollection(idColumn = "BRANCH_ID")
      private Set<SubjectRef> subjects = new HashSet<>();
    
      public void addSubject(Subject subject) {
        subjects.add(new SubjectRef(subject.getSubjectId()));
      }
    
    }

### 4. 测试映射 ###

4.1. 为**分支**和**主题**实体创建 JDBC 存储库以执行基本的数据库操作。

@Repository
    public interface SubjectTestRepository extends CrudRepository<Subject, Long>{
    
    }

@Repository
    public interface BranchTestRepository extends CrudRepository<Branch, Long>{
    
    }

4.2. **测试用例**

@SpringBootTest
    public class ManyToManyMappingTest extends BaseTest {
    
      @Autowired
      private BranchTestRepository branchRepository;
    
      @Autowired
      private SubjectTestRepository subjectRepository;
    
      @Test
      @DisplayName("many-to-many-mapping-test")
      @Sql(scripts = "/many-to-many-mapping.sql")
      void embeddedMappingTest() {
    
        Subject subj1 = subjectRepository.save(getSubject1());
        Subject subj2 = subjectRepository.save(getSubject2());
        Subject subj3 = subjectRepository.save(getSubject3());
    
        Branch branch1 = getBranch1();
        branch1.addSubject(subj1);
        branch1.addSubject(subj2);
    
        Branch createdBranch1 = branchRepository.save(branch1);
        Assert.assertTrue(createdBranch1 != null);
    
        Branch branch2 = getBranch2();
        branch2.addSubject(subj1);
        branch2.addSubject(subj3);
    
        Branch createdBranch2 = branchRepository.save(branch2);
        Assert.assertTrue(createdBranch2 != null);
    
        branchRepository.delete(branch1);
        Assert.assertTrue(!branchRepository.existsById(branch1.getBranchId()));
    
        branchRepository.findAll().forEach(b -> System.err.println(b));
      }
    
      private static Subject getSubject1() {
        Subject subject = new Subject();
        subject.setSubjectName("Software Engineering");
        subject.setSubjectDesc(
            "Apply key aspects of software engineering processes for the development of a complex software system");
    
        return subject;
      }
    
      private static Subject getSubject2() {
        Subject subject = new Subject();
        subject.setSubjectName("Distributed System");
        subject.setSubjectDesc("Explore recent advances in distributed computing systems");
    
        return subject;
      }
    
      private static Subject getSubject3() {
        Subject subject = new Subject();
        subject.setSubjectName("Business Analysis and Optimization");
        subject.setSubjectDesc("understand the Internal and external factors that impact the business strategy");
    
        return subject;
      }
    
      private static Branch getBranch1() {
        Branch branch = new Branch();
        branch.setBranchName("Computer Science and Engineering");
        branch.setBranchShortName("CSE");
        branch.setDescription(
            "CSE department offers courses under ambitious curricula in computer science and computer engineering..");
    
        return branch;
      }
    
      private static Branch getBranch2() {
        Branch branch = new Branch();
        branch.setBranchName("Information Technology");
        branch.setBranchShortName("IT");
        branch.setDescription(
            "IT is the business side of computers - usually dealing with databases, business, and accounting");
    
        return branch;
      }
    }

### 5. 结论 ###

在本教程中，我们介绍了如何在春季数据 JDBC 中映射多对多关联。

在 [Git 中心][Git]查看源代码。

**其他春季数据 JDBC 示例** ：

1.  [春季数据 JDBC – 嵌入式实体][JDBC _]
2.  [春季数据 JDBC – 分页][JDBC _ 1]
3.  [春季数据JDBC – 一对多][JDBC _ 2]
4.  [春季数据JDBC – 一对一][JDBC _ 3]
5.  [春季数据 JDBC – 查询派生][JDBC _ 4]
6.  [弹簧启动 – 加载初始数据][Link 2]

### 6. 参考资料 ###

1.  [春季文档][Link 3]
2.  [弹簧骨料][Link 4]
3.  [龙目岛@Data][Data]
4.  [JPA 多对多单向][JPA]
5.  [弹簧靴 H2][H2]
6.  [春季数据 JDBC 示例][JDBC]

[741e38be2163ce7f422530e4ef93d91a.png]: https://img-blog.csdnimg.cn/img_convert/741e38be2163ce7f422530e4ef93d91a.png
[Link 1]: https://javabydeveloper.com/project-lombok-tutorial/
[DDD]: https://docs.spring.io/spring-data/jdbc/docs/2.0.1.RELEASE/reference/html/#jdbc.domain-driven-design
[Git]: https://github.com/javabydeveloper/spring-boot/tree/master/spring-boot-data-jdbc-example/src/test/java/com/javabydeveloper/demo
[JDBC _]: https://javabydeveloper.com/spring-data-jdbc-embedded-entities-examples/
[JDBC _ 1]: https://javabydeveloper.com/spring-data-jdbc-pagination-and-sorting-example/
[JDBC _ 2]: https://javabydeveloper.com/spring-data-jdbc-one-to-many-example/
[JDBC _ 3]: https://javabydeveloper.com/spring-data-jdbc-one-to-one-example/
[JDBC _ 4]: https://javabydeveloper.com/spring-data-jdbc-query-derivation-examples/
[Link 2]: https://javabydeveloper.com/spring-boot-loading-initial-data/
[Link 3]: https://docs.spring.io/spring-data/jdbc/docs/current/reference/html/#jdbc.entity-persistence.custom-column-name
[Link 4]: https://spring.io/blog/2018/09/24/spring-data-jdbc-references-and-aggregates
[Data]: https://javabydeveloper.com/lombok-data-annotation/
[JPA]: https://javabydeveloper.com/many-many-unidirectional-association/
[H2]: https://javabydeveloper.com/spring-boot-h2-in-memory-database-example/
[JDBC]: https://javabydeveloper.com/spring-data-jdbc-2-0-with-spring-boot-example/