Mybatis进阶学习(一对一,一对多,动态sql)

Mybatis

三、复杂查询(一对一,一对多,Mybatis的多对多可以看成双端一对多)

结构类型:

​ 一个老师对应多个学生,一个学生对应一个老师

一对一与一对多这里就写一起了

3.1实体:

package com.springbootdemo.controller.Mybatis.entity;


import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import java.util.List;

/**
 * 与学生一对多的老师类
 */
@Data
@AllArgsConstructor
@ToString
@NoArgsConstructor
public class MultiTableTeacher {
    private int id;
    private String name;
    //一个老师拥有多个学生
    List<MultiTableStudent> multiTableStudents;
}

package com.springbootdemo.controller.Mybatis.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

/**
 * 每个学生都存在一个教师Id,以此来标记是哪个老师
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class MultiTableStudent {

    private int id;
    private String name;
    private int tid;

    //每个学生都存在一个教师属性,
    private MultiTableTeacher  teacher;
}

3.2Dao层接口

package com.springbootdemo.controller.Mybatis.mapper;


import com.springbootdemo.controller.Mybatis.entity.MultiTableStudent;

import java.util.List;

/**
 * 多表查询的教师Mapper
 */
public interface MultiTableStudentMapper {
    //查询所有的学生信息,以及对应的老师的信息,多表管理,
    // 一个老师对应一个学生
    //变相的一对一
    //1.子查询
    public List<MultiTableStudent> getStudent();

    //按照结果嵌套映射
    public List<MultiTableStudent> getStudentByResult();




}

package com.springbootdemo.controller.Mybatis.mapper;

import com.springbootdemo.controller.Mybatis.entity.MultiTableTeacher;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface MultiTableTeacherMapper {

    //根据id获取老师及其所有学生 @param可以将形参传递给xml,xml使用#{}取出,key是@parm规定的键值
    MultiTableTeacher getTeahcer(@Param("tid") int id);


    MultiTableTeacher getTeahcerByQuery(@Param("tid") int id);
}

3.3Mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.springbootdemo.controller.Mybatis.mapper.MultiTableStudentMapper">

<!--一对一-->
<!--    思路1:(两个sql),子查询-->
<!--        ①查询出所有的学生信息-->
<!--        ②根据查询出来的学生tid,寻找对应的老师-->

<!--    查询出所有学生等信息包括tid-->
    <select id="getStudent" resultMap="StudentTeacher">
        select * from student
    </select>

<!--    建立查询结果的映射关系,执行子查询-->
    <resultMap id="StudentTeacher" type="com.springbootdemo.controller.Mybatis.entity.MultiTableStudent">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
<!--        复杂的属性,我们需要单独处理 对象:association 集合:collection-->
<!--        根据查出来的tid,执行嵌套在里面的子查询-->
        <association property="teacher" column="tid" javaType="com.springbootdemo.controller.Mybatis.entity.MultiTableTeacher" select="getTeacher"/>
    </resultMap>

<!--    根据教师ID查询教师,通过上面的映射关系掺入参数    -->
    <select id="getTeacher" resultType="com.springbootdemo.controller.Mybatis.entity.MultiTableTeacher">
        select
        *
        FROM
        teacher
        where
        id =#{id}
    </select>


    <!--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++-->
<!--二、按照结果嵌套处理(常用)-->
    <select id="getStudentByResult" resultMap="StudentTeacherByResutlt">
        select
        s.id sid,
        s.name sname,
        t.name tname
        FROM
        student s,
        teacher t
        where s.tid=t.id;
    </select>

    <resultMap id="StudentTeacherByResutlt" type="com.springbootdemo.controller.Mybatis.entity.MultiTableStudent">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="com.springbootdemo.controller.Mybatis.entity.MultiTableTeacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>



</mapper>

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.springbootdemo.controller.Mybatis.mapper.MultiTableTeacherMapper">
<!--一对多-->
<!--  1.  按结果嵌套查询-->
    <select id="getTeahcer" resultMap="TeacherStudentList">
        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>

<!--    一对多的结果集映射,coolection-->
    <resultMap id="TeacherStudentList" type="com.springbootdemo.controller.Mybatis.entity.MultiTableTeacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
<!--        复杂的属性,需要我们单独处理 对象:association 集合:collection-->
<!--        javaType: 制定的属性类型(对象的类型)-->
<!--        集合中的泛型信息,我们使用ofType-->
        <collection property="multiTableStudents" ofType="com.springbootdemo.controller.Mybatis.entity.MultiTableStudent">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>

    <!--    +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++-->
<!--  2.  通过子查询的方式映射-->
    <select id="getTeahcerByQuery" resultMap="TeacherStudentList2">
        select * from teacher where  id=#{tid};
    </select>

    <resultMap id="TeacherStudentList2" type="com.springbootdemo.controller.Mybatis.entity.MultiTableTeacher">
        <collection property="multiTableStudents"  ofType="com.springbootdemo.controller.Mybatis.entity.MultiTableStudent"
                    select="getStudentByTeacherId" column="id"/>
    </resultMap>
    <select id="getStudentByTeacherId" resultType="com.springbootdemo.controller.Mybatis.entity.MultiTableStudent">
        select  * FROM  student where tid= #{tid}
    </select>



</mapper>

四、动态sql(if(and),Choose(or),where,when(java中的while),set(动态更新),Foreach(java的for循环)

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.springbootdemo.controller.Mybatis.mapper.BlogMapper">


    <insert id="insertBlog" parameterType="com.springbootdemo.controller.Mybatis.entity.Blog">
        insert  into blog(id,title,author,create_time,views)
        values (#{id},#{title},#{author},#{creatTime},#{views});
    </insert>

<!--    通过在xml中写if标签,提高sql的复用性-->
<!--    这个select的意思是,如果前端传入了title就在查询的条件加上title条件-->
<!--    如果传入author,就在查询条件加入author条件-->
    <select id="queryBlogIf" resultType="com.springbootdemo.controller.Mybatis.entity.Blog">
        select * from blog where 1=1
        <if test="title !=null ">
            and title =#{title}
        </if>
        <if test="author !=null ">
            and author = #{author}
        </if>
    </select>



<!--    有时候我们并不想应用所有的条件，而只是想从多个选项中选择一个。-->
<!--    而使用if标签时，只要test中的表达式为 true，就会执行 if 标签中的条件。-->
<!--    MyBatis 提供了 choose 元素。if标签是与(and)的关系，而 choose 是或(or)的关系-->
    <select id="queryBlogChoose" parameterType="com.springbootdemo.controller.Mybatis.entity.Blog" resultType="com.springbootdemo.controller.Mybatis.entity.Blog">
        select * FROM  blog
        <where>
            <choose>
                <when test="author != null and author != ''">
                    and author=#{author}
                </when>
                <otherwise>
                    and views= #{views}
                </otherwise>
            </choose>
        </where>
    </select>



<!--    set标签会动态设置set关键字,同时也会删除无关逗号-->
<!--    动态更新,谁变更谁-->
    <update id="updateBlog" parameterType="com.springbootdemo.controller.Mybatis.entity.Blog"  >
        update bllog
        <set>
            <include refid="if-title-author"></include>
        </set>
        where id=#{id}
    </update>


<!--    将一部分sql提取出来,方便复用,使用include标签的refid进行复用-->
    <sql id="if-title-author">
        <if test="title != null and title != ''">
            title=#{title}
        </if>
        <if test="author != null and author != ''">
            author=#{author}
        </if>
    </sql>

<!--
    sql:select * from blog where 1=1 and (id=1 or id = 2 or id = 3)
        Foreach标签(For循环)
        批量增删改查,非全部
        -->
    <select id="queryBlogForeach" parameterType="java.util.Map"  resultType="com.springbootdemo.controller.Mybatis.entity.Blog">
        select * from blog
        <where>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>

</mapper>