spring-mybatis多表查询

spring-mybatie 多表查询

• 根据表的字段，按表名创建java实体类，然后把查询条件的主表中，按照查询的逻辑把从表的实体类当做属性写进上一级的主表中

• 然后在mybaisDAO接口的实现类中写多表查询语句（如下，所有的表名写上别名，然后为了防止字段冲突，给字段也写上字段别名\\注意sql执行顺序，写where条件的时候分清是写字段名还是字段别名）

  <select id="getQuestionAndEvaluate" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
  <!--
    WARNING - @mbggenerated
    This element is automatically generated by MyBatis Generator, do not modify.
    This element was generated on Fri Sep 01 21:17:01 CST 2017.
  -->
  select 
  q.*,
  qe.evaluate_contennt qec,qe.User_id qui,qe.time qt,
  qk.knowledge_id qki,
  k.name kn,
  su.user_name sun,
  sc.class_name sccn,
  ss.sort_name sssn
  from 
  question q
  join question_evaluate qe on(qe.question_id=q.question_id)
  join question_knowledge qk on(qk.question_id=q.question_id)
  join knowledge k on(k.knowledge_id=qk.knowledge_id)
  join study_user su on(su.user_id=qe.user_id)
  join study_class sc on(sc.class_id=k.class_id)
  join study_sort ss on(ss.sort_id=k.sort_id)
  where q.question_id = #{question_id,jdbcType=INTEGER}
  

  \

• 根据查询语句的逻辑顺序搭建实体类，储存在当前页面中\ 注意标签名

• 下列代码仅供参考与上列不符合

• 最外层写查询条件所在的表的实体类，然后其中的基本数据类型写成

• 如果是非基本数据类型而是自己定义的java对象就使用标签

• 如果是集合就使用标签

• 子表的实体对象也遵循这个规则

  <resultMap type="cn.xdl.studyonline.common.entity.Course" 
  
      id="resultMap1">
  <id column="id" property="id" jdbcType="INTEGER" />
  <result column="name" property="name" jdbcType="VARCHAR" />
  <result column="intro" property="intro" jdbcType="VARCHAR" />
  <result column="learn_count" property="learnCount" jdbcType="INTEGER" />
  <result column="difficulty" property="difficulty" jdbcType="VARCHAR" />
  <result column="score" property="score" jdbcType="INTEGER" />
  <result column="image" property="image" jdbcType="VARCHAR" />
  <result column="publish_time" property="publishTime" jdbcType="TIMESTAMP" />
  <result column="price" property="price" jdbcType="INTEGER" />
  <result column="subject_id" property="subjectId" jdbcType="INTEGER" />
  <result column="direction_id" property="directionId" jdbcType="INTEGER" />
  <!-- 关联direction的对象属性 -->
  <association property="direction"
      javaType="cn.xdl.studyonline.common.entity.Direction">
      <id column="did" property="id"/>
      <result column="dname" property="name"/>
  </association>
  <!-- 关联subject的对象属性 -->
  <association property="subject"
      javaType="cn.xdl.studyonline.common.entity.Subject">
      <id column="sid" property="id"/>
      <result column="sname" property="name"/>
  </association>
  <!-- 关联chapter的集合属性 -->
  <collection property="chapters" javaType="java.util.List" 
      ofType="cn.xdl.studyonline.common.entity.Chapter">
      <id column="pid" property="id" jdbcType="INTEGER"/>
      <result column="pname" property="name" jdbcType="VARCHAR"/>
      <result column="course_id" property="courseId" jdbcType="INTEGER"/>
      <!-- 关联video的集合属性 -->
      <collection property="videos" javaType="java.util.List"
          ofType="cn.xdl.studyonline.common.entity.Video">
          <id column="vid" property="id" jdbcType="INTEGER"/>
          <result column="vname" property="name" jdbcType="VARCHAR"/>
          <result column="vurl" property="url" jdbcType="VARCHAR"/>
          <result column="chapter_id" property="chapterId" jdbcType="INTEGER"/>
      </collection>
  </collection>
  </resultMap>