关闭

MyBatis关联查询

标签: mybatis关联表查询
259人阅读 评论(0) 收藏 举报
分类:
一对一关联
一对一的场景:一个班主任只属于一个班级,一个班级也只能有一个班主任。
1、创建班级表和教师表语句

班级表

CREATE TABLE class
    (
        c_id INT NOT NULL AUTO_INCREMENT,
        c_name VARCHAR(20),
        teacher_id INT,
        PRIMARY KEY (c_id)
    )
ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO class(c_name, teacher_id) VALUES('class_a', 1);
INSERT INTO class(c_name, teacher_id) VALUES('class_b', 2);
教师表

CREATE TABLE teacher
    (
        t_id INT NOT NULL AUTO_INCREMENT,
        t_name VARCHAR(20),
        PRIMARY KEY (t_id)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO teacher(t_name) VALUES('teacher1');
INSERT INTO teacher(t_name) VALUES('teacher2');

2、创建实体

public class Classes {
	private int id;
	private String name;
	private Teacher teacher;
/* 省略Getter与Setter */
}

public class Teacher {
	private int id;
	private String name;
	/* 省略Getter与Setter */
}
3、编写sql映射ClassMapper.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,namespace的值习惯上设置成包名+sql映射文件名,这样就能够保证namespace的值是唯一的 -->
<mapper namespace="com.mahaochen.work.mapper.ClassMapper">
	<!-- 一对一联表查询  -->
	<!-- 方式一:嵌套结果,使用嵌套结果映射来处理重复的联合的子集,封装联表查询的数据(去除重复数据) -->
	<select id="SelectOneToOne1" parameterType="int" resultMap="ClassResultMap1">
		select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
	</select>
	<!-- 使用resultMap映射实体类和字段之间的一一对应关系 -->
	<resultMap type="com.mahaochen.work.domain.Classes" id="ClassResultMap1">
		<id property="id" column="c_id" />
		<result property="name" column="c_name" />
		<association property="teacher" javaType="com.mahaochen.work.domain.Teacher">
			<id property="id" column="t_id" />
			<result property="name" column="t_name" />
		</association>
	</resultMap>

	<!-- 方式二:嵌套查询,通过执行另外一个SQL映射语句来返回预期的复杂类型 -->
	<select id="SelectOneToOne2" parameterType="int" resultMap="ClassResultMap2">
		select * from class where c_id=#{id}
	</select>
	<!-- 使用resultMap映射实体类和字段之间的一一对应关系 -->
	<resultMap type="com.mahaochen.work.domain.Classes" id="ClassResultMap2">
		<id property="id" column="c_id" />
		<result property="name" column="c_name" />
		<association property="teacher" column="teacher_id" select="SelectTeacher" />
	</resultMap>
	<!-- 查询Teacher表语句 -->
	<select id="SelectTeacher" parameterType="int" resultType="com.mahaochen.work.domain.Teacher">
		SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
	</select>
</mapper>
        MyBatis使用association标签来解决一对一的关联查询,association标签可用的属性如下:
property 对象属性的名称
javaType 对象属性的类型
column 所对应的外键字段名称
select 使用另一个查询封装的结果

一对多关联
一对多的场景:根据班级,获取包括学生、教师、班级信息。一个班级包好多个学生。
1、创建学生表

CREATE TABLE student
    (
        s_id INT NOT NULL AUTO_INCREMENT,
        s_name VARCHAR(20),
        class_id INT,
        PRIMARY KEY (s_id)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO student(s_name, class_id) VALUES('student_A', 1);
INSERT INTO student(s_name, class_id) VALUES('student_B', 1);
INSERT INTO student(s_name, class_id) VALUES('student_C', 1);
INSERT INTO student(s_name, class_id) VALUES('student_D', 2);
INSERT INTO student(s_name, class_id) VALUES('student_E', 2);
INSERT INTO student(s_name, class_id) VALUES('student_F', 2);

2、创建实体

public class Student {

	private int id;
	private String name;
/* 省略Getter与Setter */
}

public class Classes {

	private int id;
	private String name;
	private Teacher teacher;
	//使用一个List<Student>集合属性表示班级拥有的学生
	private List<Student> students;
/* 省略Getter与Setter */
}

3、编写sql映射ClassMapper.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,namespace的值习惯上设置成包名+sql映射文件名,这样就能够保证namespace的值是唯一的 -->
<mapper namespace="com.mahaochen.work.mapper.ClassMapper">
<!-- 一对多联表查询  -->
	<!--方式一:嵌套结果,使用嵌套结果映射来处理重复的联合结果的子集 -->
	<select id="SelectOneToMany1" 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="com.mahaochen.work.domain.Classes" id="ClassResultMap3">
		<id property="id" column="c_id" />
		<result property="name" column="c_name" />
		<association property="teacher" column="teacher_id"
			javaType="com.mahaochen.work.domain.Teacher">
			<id property="id" column="t_id" />
			<result property="name" column="t_name" />
		</association>
		<!-- ofType指定students集合中的对象类型 -->
		<collection property="students" ofType="com.mahaochen.work.domain.Student">
			<id property="id" column="s_id" />
			<result property="name" column="s_name" />
		</collection>
	</resultMap>

	<!--方式二:嵌套查询,通过执行另外一个SQL映射语句来返回预期的复杂类型 -->
	<select id="SelectOneToMany2" parameterType="int" resultMap="ClassResultMap4">
		select * from class where c_id=#{id}
	</select>
	<resultMap type="com.mahaochen.work.domain.Classes" id="ClassResultMap4">
		<id property="id" column="c_id" />
		<result property="name" column="c_name" />
		<association property="teacher" column="teacher_id"
			javaType="com.mahaochen.work.domain.Teacher" select="SelectTeacher2"></association>
		<!-- collection标签来解决一对多的关联查询,ofType属性指定集合中元素的对象类型。 -->
		<collection property="students" ofType="com.mahaochen.work.domain.Student"
			column="c_id" select="SelectStudent2"></collection>
	</resultMap>

	<select id="SelectTeacher2" parameterType="int" resultType="com.mahaochen.work.domain.Teacher">
		SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
	</select>
	
	<select id="SelectStudent2" parameterType="int" resultType="com.mahaochen.work.domain.Student">
		SELECT s_id id, s_name name FROM student WHERE class_id=#{id}
	</select>
</mapper>

       MyBatis中使用collection标签来解决一对多的关联查询,ofType属性指定集合中元素的对象类型。collection标签可用的属性如下:
property 对象属性的名称
javaType 对象属性的类型
column 所对应的外键字段名称
select 使用另一个查询封装的结果







0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:244671次
    • 积分:3781
    • 等级:
    • 排名:第8467名
    • 原创:130篇
    • 转载:17篇
    • 译文:0篇
    • 评论:30条
    告诉自己
    It's not what you do once in a while, it's what you do day in and day out that makes the difference.
    博客专栏