mybatis查询

一对多

接口类

Dog getDogAndMastersById(Integer dogId);

xml

<resultMap id="dogMap" type="Dog">
        <id column="id" property="id"></id>
<!--        column是查询字段，property是dog类里的字段-->
        <result column="name" property="name"></result>
        <result column="health" property="health"></result>
        <result column="love" property="love"></result>
        <result column="name" property="name"></result>
        <result column="strain" property="strain"></result>
        <result column="lytm" property="lytm"></result>
        <collection property="masters" ofType="Master">
            <id column="pid" property="pid"></id>
            <result column="uname" property="name"></result>
            <result column="age" property="age"></result>
            <result column="gender" property="gender"></result>
            <result column="yearnum" property="yearnum"></result>
            <result column="did" property="did"></result>
        </collection>
    </resultMap>

    <select id="getDogAndMastersById" resultMap="dogMap">
        select d.id,d.name,d.health,d.love, d.strain,d.lytm,
               m.pid, m.name uname, m.age, m.gender, m.yearnum, m.did
        from dog d left join master m on d.id = m.did
        where id = #{dogId}
    </select>
</mapper>

测试类

@Test
    public void testGetDogAndMastersById(){
        Dog dog = dogDao.getDogAndMastersById(21);
        System.out.println("打印狗狗信息：");
        System.out.println(dog.toString());
        System.out.println("打印主人信息：");
        List<Master> masters = dog.getMasters();
        for (Master master :
                masters) {
            System.out.println(master.toString());
        }
    }

小练习

//根据狗狗的名字（模糊查询）和亲密度,查询狗狗信息 可以自由选择降序和升序（1，升序 2，降序）
    List<Dog> getDogByNameAndLove(
            @Param("dogname") String dogName,//@Param是起别名，不起好像会报查不到异常
            @Param("doglove") Integer love,
            @Param("ordervalue") Integer orderValue);

xml

<select id="getDogByNameAndLove" resultType="Dog">
        select id,name,health,love,strain,lytm from dog
        <where>
            <if test="dogname != null and dogname != ''">
                name like concat('%',#{dogname},'%')
            </if>
            <if test="doglove != null">
                and love = #{doglove}
            </if>
        </where>
        <choose>
            <when test="ordervalue == 1">
                order by lytm asc
            </when>
            <when test="ordervalue == 2">
                order by lytm desc
            </when>
            <otherwise>
                order by id desc
            </otherwise>
        </choose>
    </select>

测试类

@Test
    public void getDogByNameAndLove(){
        //List<Dog> dogs = dogDao.getDogByNameAndLove(null,null,null);

        List<Dog> dogs = dogDao.getDogByNameAndLove("%日%",90,1);
        for (Dog dog : dogs) {
            System.out.println(dog.toString());
        }
    }

一对一

xml

<resultMap id="MasterMap" type="Master">
        <id column="pid" property="pid"></id>
        <result column="uname" property="name"></result>
        <result column="age" property="age"></result>
        <result column="gender" property="gender"></result>
        <result column="yearnum" property="yearnum"></result>
        <result column="did" property="did"></result>
        <association property="strainType" javaType="StrainType">
            <id column="sid" property="id"></id>
            <result column="sname" property="name"></result>
            <result column="describe" property="describe"></result>
        </association>
        <collection property="dog" ofType="Dog">
            <id column="id" property="id"></id>
            <result column="name" property="name"></result>
            <result column="health" property="health"></result>
            <result column="love" property="love"></result>
            <result column="name" property="name"></result>
            <result column="strain" property="strain"></result>
            <result column="lytm" property="lytm"></result>
        </collection>
    </resultMap>

    <select id="getMasterAndDogAndStrainTypeById" resultMap="MasterMap">
        select d.id,d.name,d.health,d.love, d.strain,d.lytm,
               s.id sid,s.name sname,s.describe,
               m.pid, m.name uname, m.age, m.gender, m.yearnum, m.did
        from master m
            left join dog d on m.did = d.id
            left join straininfo s on d.strain = s.name
        where m.pid = #{pid};
    </select>

测试类

@Test
    public void testGetMasterAndDogAndStrainTypeById(){
        Master master = masterDao.getMasterAndDogAndStrainTypeById(1);
        System.out.println("主人信息：");
        System.out.println(master.toString());
        System.out.println("狗狗信息：");
        System.out.println(master.getDog());
        System.out.println("狗狗品种信息：");
        System.out.println(master.getStrainType());
    }

结果