Mybatis03
实现关联表查询
一对一关联
例:一个班级有一个班主任
定义实体类
//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>