MyBatis学习存档（5）——联表查询
之前的数据库操作都是基于一张表进行操作的，若一次查询涉及到多张表，那该如何进行操作呢？
首先明确联表查询的几个关系，大体可以分为一对一和一对多这两种情况，接下来对这两种情况进行分析：
一、建立表、添加数据、反向生成
建立teacher、classes、student三张表，并添加几条数据用于后续操作
进行反向生成
二、一对一联表查询
一个班级（classes）只有一个班主任（teacher），即一对一的关系
仅仅通过反向生成的文件是无法进行联表查询的，因此需要进行一些改动
package com.pojo; public class Teacher { private Integer id; private String name; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name == null ? null : name.trim(); } @Override public String toString() { return "Teacher [id=" + id + ", name=" + name + "]"; } }
在Classes类中添加一个teacher的属性，删除多余的teacher_id：
package com.pojo; public class Classes { private Integer classId; private String className; private Teacher teacher; //一对一对应的teacher public Integer getClassId() { return classId; } public void setClassId(Integer classId) { this.classId = classId; } public String getClassName() { return className; } public void setClassName(String className) { this.className = className; } public Teacher getTeacher() { return teacher; } public void setTeacher(Teacher teacher) { this.teacher = teacher; } @Override public String toString() { return "Classes [classId=" + classId + ", className=" + className + ", teacher=" + teacher + "]"; } }
修改接口及mapper映射文件：此处有2种方法进行查询
在resultMap节点下添加一个association节点，其配置与resultMap类似，该节点可以用于处理“一对一“的类型关系
package com.mapper; import com.pojo.Teacher; public interface TeacherMapper { Teacher selectTeacherById(Integer id); }
<?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="com.mapper.TeacherMapper" > <resultMap id="BaseResultMap" type="com.pojo.Teacher" > <id column="id" property="id" jdbcType="INTEGER" /> <result column="name" property="name" jdbcType="VARCHAR" /> </resultMap> <sql id="Base_Column_List" > id, name </sql> <select id="selectTeacherById" parameterType="java.lang.Integer" resultMap="BaseResultMap"> select <include refid="Base_Column_List"/> from teacher where id=#{id} </select> </mapper>
package com.mapper; import com.pojo.Classes; public interface ClassesMapper { //方法一 Classes selectByClassId1(Integer classId); //方法二 Classes selectByClassId2(Integer classId); }
<?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="com.mapper.ClassesMapper" > <sql id="Base_Column_List"> class_id,class_name,teacher_id </sql> <sql id="classesAndTeacher"> classes.class_id,classes.class_name,teacher.id,teacher.name </sql> <!-- 方法一：直接嵌套查询 --> <resultMap id="ClassesMap1" type="com.pojo.Classes" > <id column="class_id" property="classId" jdbcType="INTEGER" /> <result column="class_name" property="className" jdbcType="VARCHAR" /> <association property="teacher" javaType="com.pojo.Teacher"> <id column="id" property="id" jdbcType="INTEGER"/> <result column="name" property="name" jdbcType="VARCHAR"/> </association> </resultMap> <select id="selectByClassId1" resultMap="ClassesMap1" parameterType="java.lang.Integer" > select <include refid="classesAndTeacher"/> from classes LEFT JOIN teacher ON classes.teacher_id=teacher.id where classes.class_id=#{classId} </select> <!-- 方法二：进行2次查询（可使用懒加载） --> <resultMap id="ClassesMap2" type="com.pojo.Classes" > <id column="class_id" property="classId" jdbcType="INTEGER" /> <result column="class_name" property="className" jdbcType="VARCHAR" /> <association property="teacher" javaType="com.pojo.Teacher" column="teacher_id" select="com.mapper.TeacherMapper.selectTeacherById"/> <!-- 若需设置懒加载，需在mybatis-config.xml中添加下列节点（开启懒加载，关闭急加载） <settings> 开启懒加载 <setting name="lazyLoadingEnabled" value="true"/> 关闭急加载 <setting name="aggressiveLazyLoading" value="false"/> </settings> 并在association节点中添加fetchType属性并设置为lazy，如下 --> <!-- <association property="teacher" javaType="com.pojo.Teacher" column="teacher_id" select="com.mapper.TeacherMapper.selectTeacherById" fetchType="lazy"/> --> </resultMap> <select id="selectByClassId2" resultMap="ClassesMap2" parameterType="java.lang.Integer"> select <include refid="Base_Column_List"/> from classes where class_id=#{id} </select> </mapper>
编写测试类
package com.test; import java.io.InputStream; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import com.mapper.ClassesMapper; import com.pojo.Classes; public class TestHasOne { public static void main(String[] args) throws Exception { //mybatis的核心配置文件 String resource = "mybatis-config.xml"; //使用MyBatis提供的Resources类加载mybatis的配置文件 InputStream is = Resources.getResourceAsStream(resource); //构建SqlSession的工厂 SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is); //开启SqlSession SqlSession session = factory.openSession(); //通过映射接口执行操作 ClassesMapper mapper = session.getMapper(ClassesMapper.class); Classes clazz1 = mapper.selectByClassId1(1); System.out.println("方法一：" + clazz1); Classes clazz2 = mapper.selectByClassId2(1); System.out.println("方法二：" + clazz2); session.close(); } }
输出结果如下：
可从日志输出中看出，方法一仅执行了1条查询语句，方法二执行了2条查询语句
三、一对多联表查询
一个班级（classes）中有许多学生（student），即一对多的关系
修改反向生成的文件
删除Student类中多余的cId属性
package com.pojo; public class Student { private Integer id; private String name; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name == null ? null : name.trim(); } @Override public String toString() { return "Student [id=" + id + ", name=" + name + "]"; } }
在Classes类中添加一个List<Student>类型的students集合
package com.pojo; import java.util.List; public class Classes { private Integer classId; private String className; private Teacher teacher; //一对一对应的teacher private List<Student> students; //一对多对应的students public Integer getClassId() { return classId; } public void setClassId(Integer classId) { this.classId = classId; } public String getClassName() { return className; } public void setClassName(String className) { this.className = className; } public Teacher getTeacher() { return teacher; } public void setTeacher(Teacher teacher) { this.teacher = teacher; } public List<Student> getStudents() { return students; } public void setStudents(List<Student> students) { this.students = students; } @Override public String toString() { return "Classes [classId=" + classId + ", className=" + className + ", teacher=" + teacher + ", students=" + students + "]"; } }
修改接口及mapper映射文件：
在resultMap节点下添加一个association节点，其配置与resultMap类似，该节点可以用于处理“一对多“的类型关系
package com.mapper; import com.pojo.Student; public interface StudentMapper { Student selectStudentById(Integer cId); }
<?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="com.mapper.StudentMapper" > <resultMap id="BaseResultMap" type="com.pojo.Student" > <id column="id" property="id" jdbcType="INTEGER" /> <result column="name" property="name" jdbcType="VARCHAR" /> </resultMap> <sql id="Base_Column_List" > id, name </sql> <select id="selectStudentById" resultMap="BaseResultMap" parameterType="java.lang.Integer"> select <include refid="Base_Column_List"/> from student where c_id=#{cId} </select> </mapper>
package com.mapper; import com.pojo.Classes; public interface ClassesMapper { //方法一 Classes selectByClassId1(Integer classId); //方法二 Classes selectByClassId2(Integer classId); //一对多查询 Classes selectByClassId3(Integer classId); }
<?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="com.mapper.ClassesMapper" > <sql id="Base_Column_List"> class_id,class_name,teacher_id </sql> <sql id="classesAndTeacher"> classes.class_id,classes.class_name,teacher.id,teacher.name </sql> <sql id="classesAndStudent"> classes.class_id,classes.class_name,student.id,student.name </sql> <!-- 方法一：直接嵌套查询 --> <resultMap id="ClassesMap1" type="com.pojo.Classes" > <id column="class_id" property="classId" jdbcType="INTEGER" /> <result column="class_name" property="className" jdbcType="VARCHAR" /> <association property="teacher" javaType="com.pojo.Teacher"> <id column="id" property="id" jdbcType="INTEGER"/> <result column="name" property="name" jdbcType="VARCHAR"/> </association> </resultMap> <select id="selectByClassId1" resultMap="ClassesMap1" parameterType="java.lang.Integer" > select <include refid="classesAndTeacher"/> from classes LEFT JOIN teacher ON classes.teacher_id=teacher.id where classes.class_id=#{classId} </select> <!-- 方法二：进行2次查询（可使用懒加载） --> <resultMap id="ClassesMap2" type="com.pojo.Classes" > <id column="class_id" property="classId" jdbcType="INTEGER" /> <result column="class_name" property="className" jdbcType="VARCHAR" /> <association property="teacher" javaType="com.pojo.Teacher" column="teacher_id" select="com.mapper.TeacherMapper.selectTeacherById"/> <!-- 若需设置懒加载，需在mybatis-config.xml中添加下列节点（开启懒加载，关闭急加载） <settings> 开启懒加载 <setting name="lazyLoadingEnabled" value="true"/> 关闭急加载 <setting name="aggressiveLazyLoading" value="false"/> </settings> 并在association节点中添加fetchType属性并设置为lazy，如下 --> <!-- <association property="teacher" javaType="com.pojo.Teacher" column="teacher_id" select="com.mapper.TeacherMapper.selectTeacherById" fetchType="lazy"/> --> </resultMap> <select id="selectByClassId2" resultMap="ClassesMap2" parameterType="java.lang.Integer"> select <include refid="Base_Column_List"/> from classes where class_id=#{id} </select> <!-- 一对多查询 --> <resultMap id="ClassesMap3" type="com.pojo.Classes" > <id column="class_id" property="classId" jdbcType="INTEGER" /> <result column="class_name" property="className" jdbcType="VARCHAR" /> <association property="teacher" javaType="com.pojo.Teacher" column="teacher_id" select="com.mapper.TeacherMapper.selectTeacherById"/> <collection property="students" column="class_id" ofType="com.pojo.Student" select="com.mapper.StudentMapper.selectStudentById"/> </resultMap> <select id="selectByClassId3" resultMap="ClassesMap3" parameterType="java.lang.Integer"> select <include refid="Base_Column_List"/> from classes where class_id=#{id} </select> </mapper>
编写测试类
package com.test; import java.io.InputStream; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import com.mapper.ClassesMapper; import com.pojo.Classes; public class TestHasMany { public static void main(String[] args) throws Exception{ //mybatis的核心配置文件 String resource = "mybatis-config.xml"; //使用MyBatis提供的Resources类加载mybatis的配置文件 InputStream is = Resources.getResourceAsStream(resource); //构建SqlSession的工厂 SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is); //开启SqlSession SqlSession session = factory.openSession(); //通过映射接口执行操作 ClassesMapper mapper = session.getMapper(ClassesMapper.class); Classes clazz = mapper.selectByClassId3(1); System.out.println(clazz); } }
输出结果如下
Classes [classId=1, className=一年1班, teacher=Teacher [id=2, name=教师B], students=[Student [id=1, name=张三], Student [id=2, name=李四], Student [id=3, name=王五]]]
