mybatis一对多对多查询

MyBatis中一对多和多对多查询可以通过嵌套查询和关联表查询来实现。

一对多查询可以通过在主表中定义一个集合属性，然后在映射文件中使用collection标签来映射子表数据。例如：

<resultMap id="userMap" type="User">
  <id property="id" column="id"/>
  <result property="name" column="name"/>
  <collection property="orders" ofType="Order">
    <id property="id" column="order_id"/>
    <result property="name" column="order_name"/>
  </collection>
</resultMap>

<select id="getUser" resultMap="userMap">
  select u.id, u.name, o.id as order_id, o.name as order_name
  from user u left join order o on u.id = o.user_id
  where u.id = #{id}
</select>

多对多查询可以通过中间表来实现，需要在映射文件中定义两个集合属性，分别映射两个子表数据。例如：

<resultMap id="userMap" type="User">
  <id property="id" column="id"/>
  <result property="name" column="name"/>
  <collection property="roles" ofType="Role">
    <id property="id" column="role_id"/>
    <result property="name" column="role_name"/>
  </collection>
</resultMap>

<select id="getUser" resultMap="userMap">
  select u.id, u.name, r.id as role_id, r.name as role_name
  from user u left join user_role ur on u.id = ur.user_id
             left join role r on ur.role_id = r.id
  where u.id = #{id}
</select>