Mybatis中的连接查询与嵌套查询

1. sql准备

CREATE TABLE `address` (
  `a_id` int(11) NOT NULL AUTO_INCREMENT,
  `a_name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`a_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='地址表';

INSERT INTO `address` (`a_id`, `a_name`) VALUES ('1', '魏国');
INSERT INTO `address` (`a_id`, `a_name`) VALUES ('2', '蜀国');
INSERT INTO `address` (`a_id`, `a_name`) VALUES ('3', '吴国');

CREATE TABLE `person` (
  `p_id` int(20) NOT NULL AUTO_INCREMENT,
  `p_name` varchar(255) DEFAULT NULL,
  `p_aid` int(20) DEFAULT NULL,
  PRIMARY KEY (`p_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `person` (`p_id`, `p_name`, `p_aid`) VALUES ('1', '曹操', '1');
INSERT INTO `person` (`p_id`, `p_name`, `p_aid`) VALUES ('2', '荀彧', '1');
INSERT INTO `person` (`p_id`, `p_name`, `p_aid`) VALUES ('3', '张辽', '1');
INSERT INTO `person` (`p_id`, `p_name`, `p_aid`) VALUES ('4', '刘备', '2');
INSERT INTO `person` (`p_id`, `p_name`, `p_aid`) VALUES ('5', '关羽', '2');
INSERT INTO `person` (`p_id`, `p_name`, `p_aid`) VALUES ('6', '张飞', '2');
INSERT INTO `person` (`p_id`, `p_name`, `p_aid`) VALUES ('7', '诸葛亮', '2');
INSERT INTO `person` (`p_id`, `p_name`, `p_aid`) VALUES ('8', '孙权', '3');
INSERT INTO `person` (`p_id`, `p_name`, `p_aid`) VALUES ('9', '周瑜', '3');
INSERT INTO `person` (`p_id`, `p_name`, `p_aid`) VALUES ('10', '陆逊', '3');
INSERT INTO `person` (`p_id`, `p_name`, `p_aid`) VALUES ('11', '公孙瓒', NULL);

2. 实体类

2.1 Person

public class Person {
    private int pId;
    private String pName;
    private int pAid;
    private Address address;
    // getter/setter....
}

2.2 Address

public class Address {
    private int aId;
    private String aName;
    private List<Person> personList;
     // getter/setter....
}

3. 连接查询

连接查询使用时，使用偏向于a表所在方向的外连接，可获得a表所有信息，和对应的b表信息。该方式为饿汉式，内存占用较大，但对数据库访问次数较少而导致消耗时间少。

3.1 多对一/一对一

    <!--多对一(一对一)连接查询-->
    <resultMap id="map1" type="com.wyc.model.Person">
        <id column="p_id" property="pId"/>
        <result column="p_name" property="pName"/>
        <result column="p_aid" property="pAid"/>
        <association property="address" javaType="com.wyc.model.Address">
            <id column="a_id" property="aId"/>
            <result column="a_name" property="aName"/>
        </association>
    </resultMap>

    <select id="findAllPersonLianJie" resultMap="map1">
        select * from person p left join address a on p.p_aid = a.a_id
    </select>

dao

List<Person> findAllPersonLianJie();

3.2 一对多

    <!--一对多连接查询-->
    <resultMap id="map2" type="com.wyc.model.Address">
        <id column="a_id" property="aId"/>
        <result column="a_name" property="aName"/>
        <collection property="personList" ofType="com.wyc.model.Person">
            <id column="p_id" property="pId"/>
            <result column="p_name" property="pName"/>
            <result column="p_aid" property="pAid"/>
        </collection>
    </resultMap>

    <select id="findAllAddressLianJie" resultMap="map2">
        select * from address a left join person p on a.a_id = p.p_aid
    </select>

dao

 List<Address> findAllAddressLianJie();

4. 嵌套查询

嵌套查询使用时，先查询a表的信息，然后依赖a和b表的外键约束，利用in()，再次查询b表对应到a表上的信息。该方式可以改为饿汉式，内存使用较小，但需要多次访问数据库而导致消耗时间多。

4.1 多对一/一对一

    <!--多对一/一对一嵌套查询-->
    <resultMap id="map3" type="com.wyc.model.Person">
        <id column="p_id" property="pId"/>
        <result column="p_name" property="pName"/>
        <result column="p_aid" property="pAid"/>
        <!--<association property="address" column="p_aid" 中的p_aid 为Person中要去Address中查询的条件-->
        <association property="address" column="p_aid" javaType="com.wyc.model.Address" select="com.wyc.mapper.MybatisTeacherMapper.findAddressById">
            <id column="a_id" property="aId"/>
            <result column="a_name" property="aName"/>
        </association>
    </resultMap>

    <select id="findAllPersonQianTao" resultMap="map3">
        select * from person
    </select>

    <select id="findAddressById" resultType="com.wyc.model.Address">
          select a_id as aId, a_name as aName from address where a_id = #{aId}
    </select>

dao

    List<Person> findAllPersonQianTao();

    Address findAddressById(int aId);

4.2 一对多

    <!--一对多嵌套查询-->
    <resultMap id="map4" type="com.wyc.model.Address">
        <id column="a_id" property="aId"/>
        <result column="a_name" property="aName"/>
        <collection property="personList" column="a_id" select="com.wyc.mapper.MybatisTeacherMapper.findPersonByAId" ofType="com.wyc.model.Person">
        </collection>
    </resultMap>

    <select id="findAllAddressQianTao" resultMap="map4">
        select * from address
    </select>

    <select id="findPersonByAId" resultType="com.wyc.model.Person">
          select p_id as pId, p_name as pName, p_aid as pAid from person where p_aid = #{aId}
    </select>

dao

    List<Address> findAllAddressQianTao();

    List<Person> findPersonByAId(int aId);