CREATE TABLE person
(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32) NOT NULL DEFAULT '',
card_id INT ,
FOREIGN KEY (card_id) REFERENCES idencard(id)
)CHARSET utf8;

-- 創(chuàng)建 mybatis_idencard 表
CREATE TABLE idencard
(
id INT PRIMARY KEY AUTO_INCREMENT,
card_sn VARCHAR(32) NOT NULL DEFAULT '' )CHARSET utf8 ;
INSERT INTO idencard VALUES(1,'111111111111110');
INSERT INTO person VALUES(1,'張三',1);
SELECT * FROM person;
SELECT * FROM idencard

public interface IdenCardMapper {
    //根據(jù)id獲取到身份證序列號(hào)
    public IdenCard getIdenCardById(Integer id);

 <!--
        1、配置/實(shí)現(xiàn)//根據(jù)id獲取到身份證序列號(hào)
        2、public IdenCard getIdenCardById(Integer id);
    -->
    <select id="getIdenCardById" parameterType="Integer"
            resultType="IdenCard">
        SELECT * FROM `idencard` WHERE `id` = #{id}
    </select>

    //通過Person的id獲取到Person,包括這個(gè)Person關(guān)聯(lián)的IdenCard對(duì)象[級(jí)聯(lián)查詢]
    public Person getPersonById(Integer id);

<mapper namespace="com.hong.mapper.PersonMapper">
    <!--
        1、配置/實(shí)現(xiàn)public Person getPersonById(Integer id);
        2、完成通過Person的id獲取到Person,包括這個(gè)Person關(guān)聯(lián)的IdenCard對(duì)象[級(jí)聯(lián)查詢]
        3. 為了讓小伙伴們理解的更加深刻一點(diǎn)，先用大家容易想到的方式-分析問題-解決問題
        4. 看到如果配置成簡(jiǎn)單 resultType="Person" 問題就是沒有實(shí)現(xiàn)級(jí)聯(lián)查詢
        5. 自定義resultMap 搞定 映射返回的結(jié)果
        6. 因?yàn)?getPersonById 最終返回的是 Person對(duì)象[只是有級(jí)聯(lián)的對(duì)象屬性], type仍然配置"Person"
    -->
    <resultMap id="PersonResultMap" type="Person">
        <!--<result property="id" column="id"/>-->
        <!--id – 一個(gè) ID 結(jié)果；標(biāo)記出作為 ID 的結(jié)果可以幫助提高整體性能
            1.property="id" 表示person 屬性 id ,通常是主鍵
            2.column="id" 表示對(duì)應(yīng)表的字段
        -->
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <!--association – 一個(gè)復(fù)雜類型的關(guān)聯(lián)
        1. property="card" 表示 Person對(duì)象的 card 屬性
        2. javaType="IdenCard" 表示card 屬性 的類型
        3. column="id" 是從我們的 下面這個(gè)語句查詢后返回的字段
        SELECT *  FROM `person`,`idencard` WHERE `person`.id=1
        AND `person`.card_id = `idencard`.id
        -->
        <association property="card" javaType="IdenCard">
            <result property="id" column="id"/>
            <result property="card_sn" column="card_sn"/>
        </association>
    </resultMap>
    <select id="getPersonById" parameterType="Integer"
            resultMap="PersonResultMap">
        SELECT *  FROM `person`,`idencard` WHERE `person`.id = #{id}
        AND `person`.card_id = `idencard`.id
    </select>
</mapper>

 //通過Person的id獲取到Person,包括這個(gè)Person關(guān)聯(lián)的IdenCard對(duì)象,方式2
    public Person getPersonById2(Integer id);
 
    //編寫方法,通過card_id 查詢得到person對(duì)象/數(shù)據(jù)
    public  Person getPersonByCardId(Integer cardId);

 <!--
        1、通過Person的id獲取到Person,包括這個(gè)Person關(guān)聯(lián)的IdenCard對(duì)象,方式2
        2、public Person getPersonById2(Integer id);
        3. 這里的方式和前面不同.
        1) 先通過 SELECT * FROM `person` WHERE `id` = #{id} 返回 person信息
        2) 再通過 返回的card_id 值，再執(zhí)行操作，得到IdenCard 數(shù)據(jù)
    -->
    <resultMap id="PersonResultMap2" type="Person">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <association property="card" column="card_id"
                     select="com.hong.mapper.IdenCardMapper.getIdenCardById" />
    </resultMap>
    <select id="getPersonById2" parameterType="Integer" resultMap="PersonResultMap2">
        SELECT * FROM `person` WHERE `id` = #{id}
    </select>
</mapper>

public interface PersonMapperAnnotation {
    //這里注解實(shí)現(xiàn)方法
    //說明： 注解的形式就是對(duì)前面xml配置方式的體現(xiàn)
    //這里同學(xué)們可以結(jié)合前面講解的xml配置時(shí)，加入的注釋來理解
 
    @Select("SELECT * FROM `person` WHERE `id` = #{id}")
    @Results({
          @Result(id = true, property = "id", column = "id"),
          @Result(property = "name", column = "name"),
          @Result(property = "card", column = "card_id",
                  one = @One(select = "com.hong.mapper.IdenCardMapper.getIdenCardById"))
    })
    public Person getPersonById(Integer id);
}

public class PersonMapperAnnotationTest {
 
    //屬性
    private SqlSession sqlSession;
    private PersonMapperAnnotation personMapperAnnotation;
 
    //初始化
    @Before
    public void init() {
        //獲取到sqlSession
        sqlSession = MyBatisUtils.getSqlSession();
        personMapperAnnotation = sqlSession.getMapper(PersonMapperAnnotation.class);
    }
 
    @Test
    public void getPersonById() {
 
        Person person = personMapperAnnotation.getPersonById(1);
        System.out.println("person----" + person);
        if(sqlSession != null) {
            sqlSession.close();
        }
 
    }
}

CREATE TABLE mybatis_user
(id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32) NOT NULL DEFAULT '' )CHARSET=utf8 ;
CREATE TABLE mybatis_pet
(id INT PRIMARY KEY AUTO_INCREMENT,
nickname VARCHAR(32) NOT NULL DEFAULT '',
user_id INT ,
FOREIGN KEY (user_id) REFERENCES mybatis_user(id)
)CHARSET=utf8 ;
INSERT INTO mybatis_user
VALUES(NULL,'宋江'),(NULL,'張飛');
INSERT INTO mybatis_pet
VALUES(1,'黑背',1),(2,'小哈',1);
INSERT INTO mybatis_pet
VALUES(3,'波斯貓',2),(4,'貴妃貓',2);
SELECT * FROM mybatis_user;
SELECT * FROM mybatis_pet;

<mapper namespace="com.hong.mapper.UserMapper">
 
    <!--解讀
    1、一定要想一想我們前面1-1是如何實(shí)現(xiàn)
    2、配置/實(shí)現(xiàn) public User getUserById(Integer id);
    3、思路(1) 先通過user-id 查詢得到user信息 (2) 再根據(jù)user-id查詢對(duì)應(yīng)的pet信息
      并映射到User-List<Pet> pets
    -->
    <resultMap id="UserResultMap" type="User">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <!--解讀：因?yàn)閜ets屬性是集合，因此這里需要是collection標(biāo)簽來處理
        1. ofType="Pet" 指定返回的集合中存放的數(shù)據(jù)類型Pet
        2. collection 表示 pets 是一個(gè)集合
        3. property="pets" 是返回的user對(duì)象的屬性 pets
        4. column="id" SELECT * FROM `mybatis_user` WHERE `id` = #{id} 返回的id字段對(duì)應(yīng)的值
        -->
        <collection property="pets" column="id" ofType="Pet"
                    select="com.hong.mapper.PetMapper.getPetByUserId"/>
    </resultMap>
    <select id="getUserById" parameterType="Integer" resultMap="UserResultMap">
        SELECT * FROM `mybatis_user` WHERE `id` = #{id}
    </select>
 
</mapper>

<mapper namespace="com.hong.mapper.PetMapper">
 
    <!--
        1、通過User的id來獲取pet對(duì)象，可能有多個(gè)，因此使用List接收
        2、public List<Pet> getPetByUserId(Integer userId);
        3. 完成的思路和前面大體相同.
    -->
    <resultMap id="PetResultMap" type="Pet">
        <id property="id" column="id"/>
        <result property="nickname" column="nickname"/>
        <association property="user" column="user_id"
                     select="com.hong.mapper.UserMapper.getUserById" />
    </resultMap>
    <select id="getPetByUserId" parameterType="Integer" resultMap="PetResultMap">
        SELECT * FROM `mybatis_pet` WHERE `user_id` = #{userId}
    </select>
 
    <!--說明
        1. 注意體會(huì)resultMap帶來好處， 直接復(fù)用
        2. 實(shí)現(xiàn)/配置public Pet getPetById(Integer id);
        3. 通過pet的id獲取Pet對(duì)象
    -->
    <select id="getPetById"
            parameterType="Integer"
            resultMap="PetResultMap">
        SELECT * FROM `mybatis_pet` WHERE `id` = #{id}
    </select>
</mapper>

public interface UserMapperAnnotation {
 
    //通過id獲取User對(duì)象
 
    /**
     * 1. 注解的配置就是對(duì)應(yīng)的Mapper.xml文件配置的，改寫
     * 2.
     *     1、一定要想一想我們前面1-1是如何實(shí)現(xiàn)
     *     2、配置/實(shí)現(xiàn) public User getUserById(Integer id);
     *     3、思路(1) 先通過user-id 查詢得到user信息 (2) 再根據(jù)user-id查詢對(duì)應(yīng)的pet信息
     *       并映射到User-List<Pet> pets
     *     <resultMap id="UserResultMap" type="User">
     *         <id property="id" column="id"/>
     *         <result property="name" column="name"/>
     *         1. ofType="Pet" 指定返回的集合中存放的數(shù)據(jù)類型Pet
     *         2. collection 表示 pets 是一個(gè)集合
     *         3. property="pets" 是返回的user對(duì)象的屬性 pets
     *         4. column="id" SELECT * FROM `mybatis_user` WHERE `id` = #{id} 返回的id字段對(duì)應(yīng)的值
     *         -->
     *         <collection property="pets" column="id" ofType="Pet"
     *                     select="com.hong.mapper.PetMapper.getPetByUserId"/>
     *     </resultMap>
     *     <select id="getUserById" parameterType="Integer" resultMap="UserResultMap">
     *         SELECT * FROM `mybatis_user` WHERE `id` = #{id}
     *     </select>
     */
 
    @Select("SELECT * FROM `mybatis_user` WHERE `id` = #{id}")
    @Results({
          @Result(id = true, property = "id", column = "id"),
          @Result(property = "name", column = "name"),
          //這里請(qǐng)小伙伴注意，pets屬性對(duì)應(yīng)的是集合
          @Result(property = "pets",
                  column = "id",
                  many = @Many(select = "com.hong.mapper.PetMapperAnnotation.getPetByUserId"))
    })
    public User getUserById(Integer id);
}

public interface PersonMapperAnnotation {
    //這里注解實(shí)現(xiàn)方法
    //說明： 注解的形式就是對(duì)前面xml配置方式的體現(xiàn)
    //這里同學(xué)們可以結(jié)合前面老師講解的xml配置時(shí)，加入的注釋來理解
 
    @Select("SELECT * FROM `person` WHERE `id` = #{id}")
    @Results({
          @Result(id = true, property = "id", column = "id"),
          @Result(property = "name", column = "name"),
          @Result(property = "card", column = "card_id",
                  one = @One(select = "com.hong.mapper.IdenCardMapper.getIdenCardById"))
    })
    public Person getPersonById(Integer id);
}

public interface PetMapperAnnotation {
 
    //通過User的id來獲取pet對(duì)象，可能有多個(gè)，因此使用List接收
 
    /**
     * 1、通過User的id來獲取pet對(duì)象，可能有多個(gè)，因此使用List接收
     * 2、public List<Pet> getPetByUserId(Integer userId);
     * 3. 完成的思路和前面大體相同.
     * <resultMap id="PetResultMap" type="Pet">
     * <id property="id" column="id"/>
     * <result property="nickname" column="nickname"/>
     * <association property="user" column="user_id"
     * select="com.hong.mapper.UserMapper.getUserById" />
     * </resultMap>
     * <select id="getPetByUserId" parameterType="Integer" resultMap="PetResultMap">
     * SELECT * FROM `mybatis_pet` WHERE `user_id` = #{userId}
     * </select>
     */
 
    //id = "PetResultMap" 就是給我們的Results[Result Map] 指定一個(gè)名字
    //,目的是為了后面復(fù)用
    @Select("SELECT * FROM `mybatis_pet` WHERE `user_id` = #{userId}")
    @Results(id = "PetResultMap", value = {
            @Result(id = true, property = "id", column = "id"),
            @Result(property = "nickname", column = "nickname"),
            @Result(property = "user",
                    column = "user_id",
                    one = @One(select = "com.hong.mapper.UserMapperAnnotation.getUserById"))
    })
    public List<Pet> getPetByUserId(Integer userId);
 
 
    //通過pet的id獲取Pet對(duì)象, 同時(shí)會(huì)查詢到pet對(duì)象關(guān)聯(lián)的user對(duì)象
 
    /**
     * <select id="getPetById"
     * parameterType="Integer"
     * resultMap="PetResultMap">
     * SELECT * FROM `mybatis_pet` WHERE `id` = #{id}
     * </select>
     *
     * @ResultMap("PetResultMap") 使用/引用我們上面定義的 Results[ResultMap]
     */
    @Select("SELECT * FROM `mybatis_pet` WHERE `id` = #{id}")
    @ResultMap("PetResultMap")
    public Pet getPetById(Integer id);
}