mybatis的4种查询

1.通过传统多条select方式实现
2.通过子查询方式实现
3.通过mybaits的配置Join连接查询方式实现
4.通过mybatis配置多条select方式实现(验证lazy加载)

1.通过传统多条select方式实现

每个mapper分别执行各种的sql语句，最后把数据合并到一起

2.通过子查询方式实现

 SELECT  xxxx，xxxx,xxxx FROM xxxx表A WHERE
    invid=( SELECT id FROM xxx表B WHERE id=#{id})         根据数据库优化原则SELECT后面最好不要跟*

3.通过mybaits的配置Join连接查询方式实现
先设置resultMap

 <id column="id" property="id"></id>
        <result column="title" property="title"></result>
        <result column="summary" property="summary"></result>
        <result column="author" property="author"></result>
        <result column="createdate" property="createdate"></result>
        <collection property="list" ofType="Reply">         ///list是pojo的属性
        <id column="rid" property="id"></id>                  /内连接，左连接，右连接俩表列名相同一定要起别名否则冲突！！！！
        <result column="content" property="content"></result>
        <result column="rauthor" property="author"></result>
        <result column="rcreatedate" property="createdate"></result>
        </collection>

对应sql别名（左连接）

 <select id="queryAllById" resultMap="baseResultMap">
        SELECT invitation.*,reply_detail.id as rid,reply_detail.author as rauthor,reply_detail.content,reply_detail.createdate  as   rcreatedate FROM
        invitation LEFT JOIN reply_detail on invitation.id=reply_detail.invid WHERE  invitation.id=#{id}
    </select>

自连接

 <select id="queryAllById" resultMap="baseResultMap">
    SELECT invitation.*,reply_detail.id as rid,reply_detail.author as rauthor,reply_detail.content,reply_detail.createdate  as   rcreatedate FROM
        invitation , reply_detail WHERE invitation.id=reply_detail.invid and  invitation.id=#{id}
  </select>

内连接

 <select id="queryAllById" resultMap="baseResultMap">
        SELECT invitation.*,reply_detail.id as rid,reply_detail.author as rauthor,reply_detail.content,reply_detail.createdate  as   rcreatedate FROM
        invitation  JOIN reply_detail on invitation.id=reply_detail.invid WHERE  invitation.id=#{id}
    </select>

4.通过mybatis配置多条select方式实现(验证lazy加载)
先设置resultMap

 <id column="id" property="id"></id>
        <result column="title" property="title"></result>
        <result column="summary" property="summary"></result>
        <result column="author" property="author"></result>
        <result column="createdate" property="createdate"></result>

        <collection property="list" ofType="Reply" select="com.sy.ReplyMapper.queryByInvId" column="id"></collection>
        //lazy加载原理是方法一的多条查询，表知己并没有关联所以不需要取别名
        //

    <!--关联一方数据,对POJO属性加以说明
        javaType:说明POJO类型的属性
        -->
        <association property="address" javaType="Address">
            <id column="AID" property="id"></id>
            <result column="addrname" property="addrname"></result>
        </association>

        <!--关联多方集合属性的说明
        ofType:说明集合中的元素类型,并对该类型的映射关系加以说明
        -->
        <collection property="list" ofType="Orders">
            <id column="OID" property="id"></id>
            <result column="ONAME" property="oname"></result>
        </collection>

seting设置

 <!--设置mybatis的全局行为-->
    <settings>
        <setting name="logImpl" value="LOG4J"/>

        <!--lazy加载-->
        <setting name="lazyLoadingEnabled" value="true"></setting>
    </settings>

因为invitation属性有reply对象的list属性所以Test1调用俩个sql语句

 @Test
    public void Test1() {
        Invitation invitation=service.queryById(3);
        System.out.println("");
        System.out.println(invitation);
        System.out.println("");
    }

因为直接输出invitation的.getAuthor()属性所以Test2调用一个sql语句

  @Test
    public void Test2() {
        Invitation invitation=service.queryById(3);
        System.out.println(invitation);
        System.out.println("");
        System.out.println(invitation.getAuthor());
        System.out.println("");
        
    }