Mybatis的多表联查和动态SQL详解及用法

Mybatis的多表联查

一对一关联
根据班级 id 查询班级信息(带老师的信息)

1. 定义实体类

public class Teacher {
private int id;
private String name;
}
public class Classes {
private int id;
private String name;
private Teacher teacher;
}

2. 定义 sql 映射文件 ClassMapper.xml

<!--
方式一：嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集
封装联表查询的数据(去除重复的数据)
select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=1
-->
<select id="getClass" parameterType="int" resultMap="ClassResultMap">
select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
</select>
<resultMap type="_Classes" id="ClassResultMap">
<!--id标签：指定主键对应的属性-->
<id property="id" column="c_id"/>
<!--result标签：指定非主键对应的属性-->
<result property="name" column="c_name"/>
<!-- association：指定字段是关联类的 property：属性名 column：数据库表字段名 javaType：指定具体关联的类-->
<association property="teacher" column="teacher_id" javaType="_Teacher">
<id property="id" column="t_id"/>
<result property="name" column="t_name"/>
</association>
</resultMap>
<!--
方式二：嵌套查询：通过执行另外一个 SQL 映射语句来返回预期的复杂类型
SELECT * FROM class WHERE c_id=1;
SELECT * FROM teacher WHERE t_id=1 //1 是上一个查询得到的 teacher_id 的值
-->
<select id="getClass2" parameterType="int" resultMap="ClassResultMap2">
select * from class where c_id=#{id}
</select>
<resultMap type="_Classes" id="ClassResultMap2">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<!-- association：指定字段是关联类的 property：属性名 column：数据库表字段名 javaType：指定具体关联的类-->
<association property="teacher" column="teacher_id" javaType="_Teacher"
select="getTeacher">
</association>
</resultMap>
<select id="getTeacher" parameterType="int" resultType="_Teacher">
SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
</select>

一对多关联
根据 classId 查询对应的班级信息,包括学生,老师

1. 定义实体类

public class Student {
private int id;
private String name;
}
public class Classes {
private int id;
private String name;
private Teacher teacher;
//一对多注入的不再是单一一个对象类型，返回多个要用list集合来接受
private List<Student> students;
}

2. 定义 sql 映射文件 ClassMapper.xml

<!--
方式一: 嵌套结果: 使用嵌套结果映射来处理重复的联合结果的子集
SELECT * FROM class c, teacher t,student s WHERE c.teacher_id=t.t_id AND c.C_id=s.class_id
AND c.c_id=1
-->
<select id="getClass3" parameterType="int" resultMap="ClassResultMap3">
select * from class c, teacher t,student s where c.teacher_id=t.t_id and c.C_id=s.class_id and
c.c_id=#{id}
</select>
<resultMap type="_Classes" id="ClassResultMap3">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<association property="teacher" column="teacher_id" javaType="_Teacher">
<id property="id" column="t_id"/>
<result property="name" column="t_name"/>
</association>
<!-- collection标签：指定该属性是list类型 ofType 指定 students 集合中的对象类型  ofType：指定list的泛型-->
<collection property="students" ofType="_Student">
<id property="id" column="s_id"/>
<result property="name" column="s_name"/>
</collection>
</resultMap>
<!--
方式二：嵌套查询：通过执行另外一个 SQL 映射语句来返回预期的复杂类型
SELECT * FROM class WHERE c_id=1;
SELECT * FROM teacher WHERE t_id=1 //1 是上一个查询得到的 teacher_id 的值
SELECT * FROM student WHERE class_id=1 //1 是第一个查询得到的 c_id 字段的值
-->
<select id="getClass4" parameterType="int" resultMap="ClassResultMap4">
select * from class where c_id=#{id}
</select>
<resultMap type="_Classes" id="ClassResultMap4">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<association property="teacher" column="teacher_id" javaType="_Teacher"
select="getTeacher2"></association>
<collection property="students" ofType="_Student" column="c_id"
select="getStudent"></collection>
</resultMap>
<select id="getTeacher2" parameterType="int" resultType="_Teacher">
SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
</select>
<select id="getStudent" parameterType="int" resultType="_Student">
SELECT s_id id, s_name name FROM student WHERE class_id=#{id}
</select>

动态SQL

if元素：条件判断

<select id="selByName" resultType="yuan.yuanmybatis.entity.Account">
       select id,name,created,updated from account where 1=1
       <!--test：判断条件-->
       <if test="name !=null and name !=''">
           and name like concat('%',#{name},'%')
       </if>
</select>

choose(when、otherwise)：条件选择，只会执行一条when，所有when都不满足就执行otherwise

<select id="selByChoose" resultType="yuan.yuanmybatis.entity.Account">
        select id,name,created,updated,money from account where 1=1
        <choose>
            <when test="name !=null and name !=''">
                and name like concat('%',#{name},'%')
            </when>
            <when test="money !=null and money !=''">
                and money =#{money}
            </when>
            <otherwise>
                and isdeleted=1
            </otherwise>
        </choose>
    </select>

where : 处理sql拼接，去掉第一个and或者or添加where

<select id="selByName" resultType="yuan.yuanmybatis.entity.Account">
    select id,name,created,updated from account
    <where>
	    <if test="name !=null and name !=''">
	        and name like concat('%',#{name},'%')
	    </if>
    </where>
 </select>

trim：添加前后缀，可以指定字符串覆盖前后缀

<select id="selByChoose" resultType="yuan.yuanmybatis.entity.Account">
        select id,name,created,updated,money from account
        <trim prefix="where" prefixOverrides="and">
        <choose>
            <when test="name !=null and name !=''">
                and name like concat('%',#{name},'%')
            </when>
            <when test="money !=null and money !=''">
                and money =#{money}
            </when>
            <otherwise>
                and isdeleted=1
            </otherwise>
        </choose>
        </trim>
    </select>

set：它可以在遇到逗号的时候，把对应的逗号去掉，会去掉前后的逗号

<update id="updateAccout" parameterType="yuan.yuanmybatis.entity.Account">
        update account
        <set>
            <if test="name !=null and name !=''">
               name=#{name},
            </if>
            <if test="money!=null and money!=''">
               money=#{money}
            </if>
        </set>
        where id=#{id}
    </update>

foreach元素，它时一个循环语句，作用时用来遍历集合，支持数组、List、Set接口集合

<select id="selIn" resultType="yuan.yuanmybatis.entity.Account">
        select id,name,created,updated from account where name in
        <foreach collection="nameList" index="index" item="name" open="(" separator="," close=")">
        #{name}
       </foreach>
   </select>