联表查询
-- 创建表和数据: create database mybatis; use mybatis; CREATE TABLE users(id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), age INT); INSERT INTO users(NAME, age) VALUES('Tom', 12); INSERT INTO users(NAME, age) VALUES('Jack', 11); CREATE TABLE student( s_id INT PRIMARY KEY AUTO_INCREMENT, s_name VARCHAR(20), class_id INT );
需要被联表的类需要在主类中关联
例如老师学生,对于老师是一对多,而对于学生则是多对一,一名老师多个学生
//班级 public class Clazz { private int cId; private String cName; private String teacherId; private Teacher teacher; //将Teacher封装到班级里 //get,set } //老师 public class Teacher { private int id; private String name; //get,set }
一对一:(sql语句的位置无关紧要,可以在下面也可以在上面)
方式一:嵌套结果:使用嵌套结果映射来处理重复的联合结果的子集
封装联表查询的数据(去除重复的数据)
<select id="getUsers" resultMap="ClazzMapper"> select * from class c,teacher t where t.t_id=c.teacher_id and c.c_id=#{cid} </select> <resultMap type="com.zhiyou100.wyf.bean.Clazz" id="ClazzMapper"> <id column="c_id" property="cid"/> <result column="c_name" property="cname"/> <association property="teacher" column="teacher_id" javaType="com.zhiyou100.wyf.bean.Teacher"> <id property="tid" column="t_id"/> <result property="tname" column="t_name"/> </association> </resultMap>
方式二:嵌套查询:通过执行另外一个 SQL 映射语句来返回预期的复杂类型
SELECT * FROM class WHERE c_id=1;
SELECT * FROM teacher WHERE t_id=1 //1 是上一个查询得到的 teacher_id 的值
<resultMap type="com.zhiyou100.wyf.bean.Clazz" id="ClazzMapper"> <id column="c_id" property="cid"/> <result column="c_name" property="cname"/> <result column="teacher_id" property="teacher_id"/> <association property="teacher" column="teacher_id" select="com.zhiyou100.wyf.dao.TeacherDao.getTeacher" javaType="com.zhiyou100.wyf.bean.Teacher"> </association> </resultMap> <select id="getUsers" resultMap="ClazzMapper"> select * from class where c_id =#{cid} </select>
<select id="getTeacher" resultType="com.zhiyou100.wyf.bean.Teacher"> select t_id tid,t_name tname from teacher where t_id=#{tid} </select>
一对多:
创建表和数据
CREATE TABLE student( s_id INT PRIMARY KEY AUTO_INCREMENT, s_name VARCHAR(20), class_id INT ); INSERT INTO student(s_name, class_id) VALUES('xs_A', 1); INSERT INTO student(s_name, class_id) VALUES('xs_B', 1); INSERT INTO student(s_name, class_id) VALUES('xs_C', 1); INSERT INTO student(s_name, class_id) VALUES('xs_D', 2); INSERT INTO student(s_name, class_id) VALUES('xs_E', 2); INSERT INTO student(s_name, class_id) VALUES('xs_F', 2);
方式一: 嵌套结果: 使用嵌套结果映射来处理重复的联合结果的子集
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
<resultMap type="com.zhiyou100.wyf.bean.Clazz" id="ClazzMapper"> <id column="c_id" property="cid"/> <result column="c_name" property="cname"/> <result column="teacher_id" property="teacher_id"/> <association property="teacher" column="teacher_id" select="com.zhiyou100.wyf.dao.TeacherDao.getTeacher" javaType="com.zhiyou100.wyf.bean.Teacher"> </association> <collection property="student" ofType="com.zhiyou100.wyf.bean.Student"> <id property="s_id" column="s_id"/> <result property="s_name" column="s_name"/> </collection> </resultMap> <select id="getUsers" resultMap="ClazzMapper"> select * from class c,student s,teacher t where c.teacher_id=t.t_id and s.class_id=c.c_id and c.c_id=#{cid} </select>
方式二:嵌套查询:通过执行另外一个 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 字段的值
<mapper namespace="com.zhiyou100.wyf.dao.ClazzDao"> <select id="selectById" parameterType="int" resultMap="mymap"> select * from class where c_id=#{cid} </select> <select id="selectByTeacherId" resultType="com.zhiyou100.wyf.bean.Teacher"> select t_id tid,t_name tname from teacher where t_id=#{tid} </select> <select id="selectByClassId" resultType="com.zhiyou100.wyf.bean.Student"> select s_id id,s_name name from student where class_id=#{classid} </select> <resultMap type="com.zhiyou100.wyf.bean.Clazz" id="mymap"> <id column="c_id" property="cId"/> <result column="c_name" property="cName"/> <result column="teacher_id" property="teacherId"/> <association property="teacher" javaType="com.zhiyou100.wyf.bean.Teacher" column="teacher_id" select="selectByTeacherId"></association> <collection property="students" ofType="com.zhiyou100.wyf.bean.Student" select="selectByClassId" column="c_id"></collection> </resultMap> </mapper>
注意:Mybatis中$和#的区别
1.#{}是预编译处理,${}是字符串替换。
2.Mybatis在处理#{}时,会将sql中的#{}替换为?号,调用PreparedStatement的set方法来赋值;
3.Mybatis在处理${}时,就是把${}替换成变量的值。
4.使用#{}可以有效的防止SQL注入,提高系统安全性。