MyBatis 多表联合查询

1. 一对一查询

1.1. 表结构

person表

字段	类型
person_id	bigint
name	varchar
first_name	varchar
birthday	datetime
card_id	bigint

card表

字段	类型
card_id	bigint
card_name	varchar

1.2. 实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Person {
    private Long personId;
    private String name;
    private String firstName;
    private Date birthday;
    private Card card;
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Card {
    private Long cardId;
    private String cardName;
}

1.3. Mapper

public interface PersonMapper {
    Person findById(Long personId);
}

1.4. 映射文件

方式1：级联属性封装

<!--
    type: 指定为哪个POJO自定义封装规则
    id: 取个标识，可以被引用
-->
<resultMap id="person" type="demo.mybatis.entity.Person">
    <!--
    已开启mapUnderscoreToCamelCase，可省略该配置。但是MyBatis官方説，指出ID有利于提高性能
        <id column="person_id" property="personId"/>
    -->
    <id column="person_id" property="personId"/>
    <!--
     <result column="name" property="name"/> column与property相同，可以省略该配置
     <result column="birthday" property="birthday"/> column与property相同，可以省略该配置
     <result column="first_name" property="firstName"/> 已开启mapUnderscoreToCamelCase，可省略该配置
     -->
    
    <!-- 级联属性封装 -->
    <result column="card_id" property="card.cardId"/>
    <result column="card_name" property="card.cardName"/>
</resultMap>
<!-- resultMap: 通过ID引用自定义的resultMap -->
<select id="findById" resultMap="person">
    SELECT p.*, c.card_name FROM person p
    LEFT JOIN card c ON p.card_id = c.card_id
    WHERE p.person_id = #{personId}
</select>

方式2：使用association。要注意的是，使用了association之后，<id>和<result>都不要省略，即使column和property同名。如果省略，字段就不会被赋值

<resultMap id="person" type="demo.mybatis.entity.Person">
    <id column="person_id" property="personId"/>
    <result column="name" property="name"/>
    <result column="birthday" property="birthday"/>
    <result column="first_name" property="firstName"/>
    <association property="card" javaType="demo.mybatis.entity.Card">
        <id column="card_id" property="cardId"/>
        <result column="card_name" property="cardName"/>
    </association>
</resultMap>
<!-- resultMap: 通过ID引用自定义的resultMap -->
<select id="findById" resultMap="person">
    SELECT p.*, c.card_name FROM person p
    LEFT JOIN card c ON p.card_id = c.card_id
    WHERE p.person_id = #{personId}
</select>

1.5. 测试

运行测试方法，查询Person时，Card也会被封装

@Test
public void test() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
        PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);
        Person person = mapper.findById(3L);
        System.out.println("person = " + person);
    } finally {
        sqlSession.close();
    }
}

2. 多对一查询

多对一和一对一是一样的

2.1. 表结构

one表

字段	描述
one_id	bigint
one_name	varchar

many表

字段	描述
many_id	bigint
many_name	varchar
one_id	bigint

2.2. 实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class One {
    private Long oneId;
    private String oneName;
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Many {
    private Long manyId;
    private String manyName;
    private One one;
}

2.3. Mapper

public interface ManyMapper {
    Many findById(Long manyId);
}

2.4. 映射文件

<resultMap id="many" type="demo.mybatis.entity.Many">
    <id column="many_id" property="manyId"/>
    <result column="many_name" property="manyName"/>
    <association property="one" javaType="demo.mybatis.entity.One">
        <id column="one_id" property="oneId"/>
        <result column="one_name" property="oneName"/>
    </association>
</resultMap>
<select id="findById" resultMap="many">
  SELECT many_id, many_name, o.* FROM many m
  LEFT JOIN one o ON m.one_id = o.one_id
  WHERE many_id = #{manyId} 
</select>

2.5. 测试

@Test
public void test() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
        ManyMapper mapper = sqlSession.getMapper(ManyMapper.class);
        Many many = mapper.findById(1L);
        System.out.println("many = " + many);
    } finally {
        sqlSession.close();
    }
}

3. 一对多查询

3.1. 表结构

同多对一

3.2. 实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Many {
    private Long manyId;
    private String manyName;
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class One {
    private Long oneId;
    private String oneName;
    private List<Many> manyList;
}

3.3. Mapper

public interface OneMapper {
    One findById(Long oneId);
}

3.4. 映射文件

<resultMap id="one" type="demo.mybatis.entity.One">
    <id column="one_id" property="oneId"/>
    <result column="one_name" property="oneName"/>
    <!--
        property: 指定集合属性
        ofType: 指定集合元素类型
    -->
    <collection property="manyList" ofType="demo.mybatis.entity.Many">
        <id column="many_id" property="manyId"/>
        <result column="many_name" property="manyName"/>
    </collection>
</resultMap>
<select id="findById" resultMap="one">
    SELECT many_id, many_name, o.* FROM one o
    LEFT JOIN many m ON o.one_id = m.one_id
    WHERE o.one_id = #{oneId}
</select>

3.5. 测试

@Test
public void test() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
        OneMapper mapper = sqlSession.getMapper(OneMapper.class);
        One one = mapper.findById(1L);
        System.out.println("one = " + one);
    } finally {
        sqlSession.close();
    }
}