Mybatis03关联表查询、动态SQL与模糊查询

实现关联表查询

一对一关联

例：一个班级有一个班主任

定义实体类

//Teacher实体类
public class Teacher {
	private int id;
	private String name;
}
//Class实体类
public class Classes {
	private int id;
	private String name;
	private Teacher teacher;
}

定义sql映射文件ClassMapper.xml

<!--
方式一：嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集
封装联表查询的数据(去除重复的数据)
select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=1
-->
<!--column是数据库表字段  property是实体类字段-->
<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 property="id" column="c_id"/>
	<result property="name" column="c_name"/>
	<!--association是一对一使用关联   column:通过那个字段关联  javaType:对应实体类的全路径类名(这里是使用了别名) 方式2的select:关联的select标签id-->
	<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="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>

测试

@Test
public void testOO() {
	Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
    SqlSession session = sqlSessionFactory.openSession();
	Classes c = session .selectOne("com.mybatis.dao.ClassMapper.getClass", 1);
	System.out.println(c);
}

@Test
public void testOO2() {
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
    SqlSession session = sqlSessionFactory.openSession();
	Classes c = sqlSession.selectOne("com.mybatis.dao.ClassMapper.getClass", 1);
	System.out.println(c);
}

一对多关联

例子：一个班级包含一个班主任和多个同学

定义实体类：

//Student类
public class Student {
	private int id;
	private String name;
}
//Class类
public class Classes {
	private int id;
	private String name;
	private Teacher teacher;
	private List<Student> students;
}

4). 定义 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   ofType:指定 students 集合中的对象类型-->
	<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>

测试：

@Test
public void testOM() {
SqlSession sqlSession = factory.openSession();
Classes c = sqlSession.selectOne("com.ykq.day03_mybatis.test5.OOMapper.getClass3",
1);
System.out.println(c);
}
@Test
public void testOM2() {
SqlSession sqlSession = factory.openSession();
Classes c = sqlSession.selectOne("com.ykq.day03_mybatis.test5.OOMapper.getClass4",
1);
System.out.println(c);
}

动态SQL与模糊查询

MyBatis动态语句元素:

1. if元素

作用：可以用来判断参数值，非空判断（如果参数是对象，会根据参数名找对象中的变量）
注意：若有多个if标签(且只有if标签)时，需要在SQL语句添加where 1=1

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

2. choose元素

<choose>相当于Java中的switch，包含的子标签有<when test="">(case)和<otherwise>(default),若有一个满足<when>中的条件，就会结束整个choose，不会拼接<otherwise>中的内容；
若没有匹配的<when>则会拼接<otherwise>中的SQL

<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>

3.where元素

<!--若前两个标签不写where 1 = 1则可能出现下面情况：-->
select id,name,created,updated,money from account where and name like concat('%',#{name},'%')

作用：给SQL拼接一个where，并且会去掉第一个and

<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>

4. trim元素

作用：可以帮助我们去掉一下and、or和逗号等，prefix代表语句前缀，suffix代表语句后缀，prefixOverrides代表要去掉前面多余的字符串，suffixOverrides代表去掉后面多余的字符串；可以搭配<if> <choose>使用

<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>

5. set元素

作用：可以给SQL拼接set，通常用于修改语句，可以去掉前后多余的逗号，搭配<if>和<trim>使用

<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>

6. foreach元素

作用：是一个循环语句，用于遍历集合，支持数组、List、set集合，不可用于map

常用属性：  collection：集合名             item：用于存放遍历的单个数据
					open：前缀字符串             close：后缀字符串
					separator：分隔符             index：下标

<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>