MyBatis3.X复杂Sql查询的语句

<resultMap id= "VideoResultMap" type= "Video" >

     <! --

     id 指定查询列列的唯⼀一标示

     column 数据库字段的名称

     property pojo类的名称

     -->

     <id column = "id" property= "id" jdbcType= "INTEGER" />

     <result column = "video_tile" property= "title" jdbcType= "VARCHAR" />

     <result column = "summary" property= "summary" jdbcType= "VARCHAR" />

     <result column = "cover_img" property= "coverImg" jdbcType= "VARCHAR" />

</resultMap>

< select id= "selectBaseFieldByIdWithResultMap" resultMap= "VideoResultMap" >

     select id , title as video_tile, summary, cover_img from video where id = #{video_id}

</ select >

<!-- 名称空间，需要保存全局唯一,最好是和dao层的Java接口一致

    可以映射sql语句到对应的方法名参数和返回值

    mybatis是使用接口动态代理

  -->

<mapper namespace= "net.xiaotiancai.online_class.dao.VideoOrderMapper" >

   <resultMap id= "VideoOrderResultMap" type= "VideoOrder" >

     <id column= "id" property= "id" ></id>

     <result column= "user_id" property= "userId" ></result>

     <result column= "out_trade_no" property= "outTradeNo" ></result>

     <result column= "state" property= "state" ></result>

     <result column= "total_fee" property= "totalFee" ></result>

     <result column= "video_id" property= "videoId" ></result>

     <result column= "video_title" property= "videoTitle" ></result>

     <!--

     配置属性一对一

     property对应videoOrder里面的User

     javaType对应这个属性的类型

     -->

     <association property= "user" javaType= "User" >

       <id column= "user_id" property= "id" ></id>

       <result column= "name" property= "name" ></result>

       <result column= "head_img" property= "headImg" ></result>

       <result column= "phone" property= "phone" ></result>

     </association>

   </resultMap>

   <!--一对一订单查询，订单内部包含用户属性-->

   <select id= "queryVideoOrderList" resultMap= "VideoOrderResultMap" >

     select

     o.id id,

     o.user_id,

     o.out_trade_no,

     o.state,

     o.total_fee,

     o.video_id,

     o.video_title,

     u.name,

     u.head_img,

     u.phone

     from video_order o left join user u on o.user_id = u.id

   </select>

</mapper>

// resultmap association关联查询

VideoOrderMapper videoOrderMapper =sqlSession.getMapper(VideoOrderMapper. class );

List<VideoOrder> videoOrderList = videoOrderMapper.queryVideoOrderList();

System.out.println(videoOrderList.toString());

<resultMap id= "UserOrderResultMap" type= "User" >

    <id property= "id" column= "id" />

    <result property= "name" column= "name" />

    <result property= "headImg" column= "head_img" />

    <result property= "phone" column= "phone" />

    <!--

    property 填写pojo类中集合类属性的名称

    ofType 集合⾥里里⾯面的pojo对象

    -->

    <collection property= "videoOrderList" ofType= "VideoOrder" >

      <!--配置主键，管理理order的唯⼀一标识-->

      <id column= "order_id" property= "id" />

      <result column= "user_id" property= "userId" />

      <result column= "out_trade_no" property= "outTradeNo" />

      <result column= "state" property= "state" />

      <result column= "total_fee" property= "totalFee" />

      <result column= "video_id" property= "videoId" />

      <result column= "video_title" property= "videoTitle" />

      <result column= "video_img" property= "videoImg" />

    </collection>

  </resultMap>

  <select id= "queryUserOrder" resultMap= "UserOrderResultMap" >

    select

    u.id,

    u.name,

    u.head_img,

    u.phone,

    o.id order_id,

    o.out_trade_no,

    o.user_id,

    o.state,

    o.total_fee,

    o.video_id,

    o.video_title

    from user u left join video_order o on u.id = o.user_id

  </select>

// resultmap association关联查询

VideoOrderMapper videoOrderMapper =sqlSession.getMapper(VideoOrderMapper. class );

//resultmap collection测试

List<User> userList = videoOrderMapper.queryUserOrder();

System.out.println(userList.toString());

<！--column不做限制，可以为任意表的字段，而property须为type定义的pojo属性-->

<resultMap id=[唯一的标识]type=[映射的pojo对象">

     <id column= "表的主键字段，或查询语句中的别名字段]jdbcrype=" 字段类型]property=[映射pojo对象的主键属性"/>

     <result column= "表的一个字段]jdbcrype=" 字段类型]property=[映射到pojo对象的一个属性"/>

     <association property= "pojo的一个对象属性]javarype=" pojo关联的pojo对象">

   <id column= "关联pojo对象对应表的主键字段]jdbcrype=" 字段类型]property= "关联pojo对象的属性" />

     <result column= "表的字段]jdbcrype=" 字段类型]property= "关联pojo对象的属性" />

</association>

<！--集合中的property 需要为oftype定义的pojo对象的属性-->

<collection property= "pojo的集合属性名称]ofType=" 集合中单个的pojo对象类型">

     <id column= "集合中pojo对象对应在表的主键字段]jdbcrype=" 字段类型]property=[集合中pojo对象的主键属性]/>

     <result column= "任意表的字段]jdbcrype=" 字段类型]property="集合中的pojo对象的属性]/>

</collection>

</resultMap>