Mybatis中的复杂查询

1.多对一和一对多的处理
- 1.1多对一（多个学生对一个老师-association）
- 1.2一对多（一个老师对多个学生-collection）
2.动态SQL

1.多对一和一对多的处理

1.1多对一（多个学生对一个老师-association）

按照查询嵌套处理

<select id="getStudent" resultMap="StudentTeacher">
    select * from student
</select>
<resultMap id="StudentTeacher" type="Student">
    <result property="id" column="id"/>
    <result property="name" column="name"/>

    <!--对复杂属性teacher的处理:对象使用association，集合使用collection-->

    <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>

</resultMap>

<select id="getTeacher" resultType="Teacher">
    select * from teacher where id = #{id}
</select>

按照结果嵌套处理

<select id="getStudent1" resultMap="StudentTeacher1">
    select s.id sid, s.name sname, t.name tname
    from student s, teacher t
    where s.tid=t.id
</select>
<resultMap id="StudentTeacher1" type="Student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacher" javaType="Teacher">
        <result property="name" column="tname"/>
    </association>
</resultMap>

1.2一对多（一个老师对多个学生-collection）

按照结果嵌套处理

只需要将查询出来的结果映射到实体类中即可

<select id="getTeacher" resultMap="TeacherStudent">
    select s.id sid, s.name sname, t.name tname, t.id tid
    from student s, teacher t
    where s.tid=t.id and t.id=#{tid}
</select>

<resultMap id="TeacherStudent" type="Teacher">
    <result property="id" column="tid"/>
    <result property="name" column="tname"/>
    <collection property="students" ofType="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <result property="tid" column="tid"/>
    </collection>
</resultMap>

按照查询嵌套处理

需要边查边映射，因此需要指定在java中的类型为ArrayList

<select id="getTeacher2" resultMap="TeacherStudent2">
    select * from teacher where id=#{tid}
</select>
<resultMap id="TeacherStudent2" type="Teacher">

    <collection property="students" javaType="ArrayList" ofType="Student" select="getStudent2" column="id"/>

</resultMap>


<select id="getStudent2" resultType="Student">
    select * from student where tid=#{tid}
</select>

javaType & ofType

1.前者用来指定实体类中属性的类型

2.后者用来指定映射到List或者集合中的pojo类型，泛型中的约束类型

回到顶部

2.动态SQL

动态sql就是在拼接SQL语句，根据不同的条件生成不同的sql语句，只要保证sql的正确性，按照sql的格式去排列即可

if语句

<if test = "[if条件]">
	[执行语句]
</if>
<!--eg.-->
<!--可以消除在java中的拼接问题-->
<select id="queryBlogIf" parameterType="map" resultType="blog">
    select * from blog where 1=1
    <if test="title != null">
        and title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</select>

choose(when/otherwise)

相当于java的switch-case

<!--when满足一个就退出，when都不满足时则otherwise-->

<select id="queryBlogChoose" parameterType="map" resultType="blog">
    select * from blog
    <!--使用where标签解决是否加and的问题--> 
    <where>
        <choose>
            <when test="tittle != null">
                tittle = #{tittle}
            </when>
            <when test="author != null">
                and author = #{author}
            </when>
            <otherwise>
                and view = #{view}
            </otherwise>
        </choose>


    </where>

</select>

trim(where/set)

<!--trim的两种格式-->

<!--perfix表示范围，Override表示覆盖的位置处于句子的位置（前缀（prefix）还是后缀（suffix））-->
<trim perfix = "where" prefixOverride = "and | OR">
...
</trim>

<trim perfix = "set" suffixOverrides = ",">
...
</trim>



<update id="updateBlog" parameterType="map">
   update blog
   <set>
       <if test="tittle != null">
           tittle = #{tittle},
       </if>
       <if test="author != null">
           author = #{author}
       </if>
   </set>
   where id = #{id}
</update>

sql片段

使用sql标签抽取方法中的相同部分,实现代码复用

<!--编写sql片段-->
<sql id="sql-title-author">
    <if test="title != null">
        and title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</sql>

<!--调用sql片段-->
<include refid="sql-title-author"></include>

foreach语句

利用循环来方便sql语句的拼接

<select id="queryBlogForeach" parameterType="map" resultType="blog">
    
    <!--原语句为:select * from blog where (id = #{ids[1]} or id = #{ids[2]} or id = #{ids[3]})-->
    <!--其中ids是从java程序中传进的数据集，用于遍历访问
		id为设置的遍历的变量名，也就是ids里面的某一项的内容
		open、separator、close用于拼接sql，open表示foreach的开始、close表示结束
	-->
    
    select * from blog
    <where>
        <foreach collection="ids" item="id" open="(" separator="or" close=")">
            id = #{id}
        </foreach>
    </where>
</select>