springboot mybatis 多对多查询

对于 Spring Boot 和 MyBatis 的多对多查询，可以通过中间表来实现。首先，在中间表中定义两个外键，分别指向两个表的主键，并将这两个外键设为联合主键。然后再分别定义两个对应实体类，使用 @ManyToMany 注解来映射关系。最后，在查询时使用 MyBatis 的动态 SQL，通过多次嵌套查询语句来实现多对多的查询。

具体步骤如下：
1. 在中间表中定义两个外键，并将这两个外键设为联合主键：

CREATE TABLE `table_A_table_B` (
  `table_A_id` int(11) NOT NULL,
  `table_B_id` int(11) NOT NULL,
  PRIMARY KEY (`table_A_id`,`table_B_id`),
  KEY `fk_table_A_table_B_table_A_idx` (`table_A_id`),
  KEY `fk_table_A_table_B_table_B_idx` (`table_B_id`),
  CONSTRAINT `fk_table_A_table_B_table_A` FOREIGN KEY (`table_A_id`) REFERENCES `table_A` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_table_A_table_B_table_B` FOREIGN KEY (`table_B_id`) REFERENCES `table_B` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2. 分别定义两个对应实体类，使用 @ManyToMany 注解来映射关系：

public class TableA {
    private Integer id;
    private String name;
    private List<TableB> tableBs;
    // getter and setter
}

public class TableB {
    private Integer id;
    private String name;
    private List<TableA> tableAs;
    // getter and setter
}

<!-- TableA Mapper -->
<mapper namespace="com.example.mapper.TableAMapper">
    <resultMap id="TableAResultMap" type="com.example.entity.TableA">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <collection property="tableBs" ofType="com.example.entity.TableB" resultMap="TableBResultMap">
            <selectKey keyProperty="id" order="BEFORE" resultType="java.lang.Integer">
                SELECT LAST_INSERT_ID()
            </selectKey>
            SELECT tb.*
            FROM table_A_table_B tatb
            INNER JOIN table_B tb ON tatb.table_B_id = tb.id
            WHERE tatb.table_A_id = #{id}
        </collection>
    </resultMap>
    <select id="findById" resultMap="TableAResultMap">
        SELECT *
        FROM table_A
        WHERE id = #{id}
    </select>
</mapper>

<!-- TableB Mapper -->
<mapper namespace="com.example.mapper.TableBMapper">
    <resultMap id="TableBResultMap" type="com.example.entity.TableB">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <collection property="tableAs" ofType="com.example.entity.TableA" resultMap="TableAResultMap">
            <selectKey keyProperty="id" order="BEFORE" resultType="java.lang.Integer">
                SELECT LAST_INSERT_ID()
            </selectKey>
            SELECT ta.*
            FROM table_A_table_B tatb
            INNER JOIN table_A ta ON tatb.table_A_id = ta.id
            WHERE tatb.table_B_id = #{id}
        </collection>
    </resultMap>
    <select id="findById" resultMap="TableBResultMap">
        SELECT *
        FROM table_B
        WHERE id = #{id}
    </select>
</mapper>

3. 使用 MyBatis 的动态 SQL，通过多次嵌套查询语句来实现多对多的查询：

public interface TableAMapper {
    @Select("SELECT * FROM table_A WHERE id IN (SELECT table_A_id FROM table_A_table_B WHERE table_B_id = #{tableBId})")
    List<TableA> findByTableBId(Integer tableBId);
}

public interface TableBMapper {
    @Select("SELECT * FROM table_B WHERE id IN (SELECT table_B_id FROM table_A_table_B WHERE table_A_id = #{tableAId})")
    List<TableB> findByTableAId(Integer tableAId);
}