MyBatis一对一以及一对多关联表查询

hibernate的一对一，一对多，多对多之前在慕课网学过。现在学习MyBatis这个ORM框架时，总是感觉它们的核心思想都是一样的，上一篇文章中讲到SQL语言是最基础的数据库操作，理解SQL对理解整个ORM框架具有重大的意义。

当前数据库中存在三个表clazz和teacher以及student，数据字典如下：

clazz(c_id,c_name,teacher_id)

teacher(t_id,t_name)

student(s_id,s_name,class_id)

三个表对应的实体类如下:

public class Clazz {
	private int id;
	private String name;
	private Teacher teacher;
	private List<Student> students;
	// 省略set，get以及toString方法
}

 

public class Teacher {
	private int id;
	private String name;
	// 省略set，get以及toString方法
}

 

 

public class Student {
	private int id;
	private String name;
	// 省略set，get以及toString方法
}

 

 

 

配置文件如下，需要解释的地方都在代码中做了相应的解释：

<?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="clazzMapper">
	<!-- 第一种方式，联表查询 -->
	<select id="getClazz" parameterType="int" resultMap="getClazzMap">
		SELECT * FROM clazz c,teacher t,student s WHERE c.teacher_id = t.t_id AND s.class_id = c.c_id AND c.c_id = #{id}
	</select>
	
	<resultMap type="_clazz" id="getClazzMap">
		<id property="id" column="c_id"/>
		<result property="name" column="c_name"/>
		<!-- 最容易忽略的地方就是javaType属性的添加 -->
		<association property="teacher" javaType="_teacher">
			<id property="id" column="t_id"/>
			<result property="name" column="t_name"/>
		</association>
		<collection property="students" ofType="_student">
			<id property="id" column="s_id"/>
			<result property="name" column="s_name"/>
		</collection>
	</resultMap>
	
	<!-- 第二种方式嵌套查询 -->
	<select id="getClazz2" parameterType="int" resultMap="getClazz2Map">
		SELECT * FROM clazz WHERE c_id = #{id};
	</select>
	<select id="getTeacher" 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>
	<resultMap type="_clazz" id="getClazz2Map">
		<id property="id" column="c_id"/>
		<result property="name" column="c_name"/>
		<!-- 一定要注意column属性的添加，此属性可以将查询得到的值再次传入下一层查询 -->
		<association property="teacher" column="teacher_id" select="getTeacher"></association>
		<collection property="students" column="c_id" select="getStudent"></collection>
	</resultMap>
</mapper>

 

测试类如下：

public class ClazzCRUD {
	@Test
	public void getClazz(){
		SqlSession session = MapperUtils.getSqlSession();
		String statement = "clazzMapper.getClazz";
		Clazz clazz = session.selectOne(statement, 2);
		session.close();
		System.err.println("联表查询"+clazz);
		}
	
	@Test
	public void getClazz2(){
		SqlSession session = MapperUtils.getSqlSession();
		String statement = "clazzMapper.getClazz2";
		Clazz clazz = session.selectOne(statement, 1);
		session.close();
		System.err.println("两次查询"+clazz);
		}
}