MyBatis中一对多关联查询

1、一对一关联查询的案例

  1.1  需求

         根据classId查询对应的班级信息,包括学生,老师

     1.2  创建表和数据

           在上面的一对一关联查询演示中，我们已经创建了班级表和教师表，因此这里再创建一张学生表

 

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('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、定义实体类

      2.1 Student实体类

public class Student {// 定义属性，和student表中的字段对应private int id; // id===>s_idprivate String name; // name===>s_namepublic int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}@Overridepublic String toString() {return "Student [id=" + id + ", name=" + name + "]";}}

      2.2修改Classes类，添加一个List<Student> students属性，使用一个List<Student>集合属性表示班级拥有的学生，如下：

public class Classes {// 定义实体类的属性，与class表中的字段对应private int id; // id===>c_idprivate String name; // name===>c_name/** * class表中有一个teacher_id字段，所以在Classes类中定义一个teacher属性， * 用于维护teacher和class之间的一对一关系，通过这个teacher属性就可以知道这个班级是由哪个老师负责的 */private Teacher teacher;// 使用一个List<Student>集合属性表示班级拥有的学生private List<Student> students = new ArrayList<Student>();public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public List<Student> getStudents() {return students;}public void setStudents(List<Student> students) {this.students = students;}public Teacher getTeacher() {return teacher;}public void setTeacher(Teacher teacher) {this.teacher = teacher;}@Overridepublic String toString() {return "Classes [id=" + id + ", name=" + name + ", teacher=" + teacher+ ", students=" + students + "]";}}

3、定义Mapper对象ClassMapper

import com.baowei.entity.Classes;public interface ClassMapper {/** * 测试一对多的非懒加载方式 *  * @param id * @return */public Classes getClass3(int id);/** * 测试一对多的懒加载方式 *  * @param id * @return */public Classes getClass4(int id);}

4、定义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="com.baowei.mapper.ClassMapper"><!--         根据classId查询对应的班级信息,包括学生,老师     -->    <!--                          方式一: (非懒加载方式)                          嵌套结果: 使用嵌套结果映射来处理重复的联合结果的子集         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="com.baowei.entity.Classes" id="ClassResultMap3">        <id property="id" column="c_id"/>        <result property="name" column="c_name"/>        <association property="teacher" column="teacher_id" javaType="com.baowei.entity.Teacher">            <id property="id" column="t_id"/>            <result property="name" column="t_name"/>        </association>        <!-- ofType指定students集合中的对象类型 -->        <collection property="students" ofType="com.baowei.entity.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="com.baowei.entity.Classes" id="ClassResultMap4">        <id property="id" column="c_id"/>        <result property="name" column="c_name"/>        <association property="teacher" column="teacher_id" javaType="com.baowei.entity.Teacher" select="getTeacher2"></association>        <collection property="students" ofType="com.baowei.entity.Student" column="c_id" select="getStudent"></collection>     </resultMap>          <select id="getTeacher2" parameterType="int" resultType="com.baowei.entity.Teacher">        SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}     </select>          <select id="getStudent" parameterType="int" resultType="com.baowei.entity.Student">        SELECT s_id id, s_name name FROM student WHERE class_id=#{id}     </select></mapper

5、Mybatis核心配置文件SqlMapConfig.xml的配置

 5.1  打开Mybatis的懒加载功能

<settings><!-- 打开延迟加载的开关 --><setting name="lazyLoadingEnabled" value="true" /><!-- 将积极加载改为消息加载即按需加载 --><setting name="aggressiveLazyLoading" value="false" /></settings>

 5.2  完整的SqlMapConfig.xml配置

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration><properties resource="db.properties"></properties><settings><!-- 打开延迟加载的开关 --><setting name="lazyLoadingEnabled" value="true" /><!-- 将积极加载改为消息加载即按需加载 --><setting name="aggressiveLazyLoading" value="false" /></settings><environments default="development"><environment id="development"><!-- 使用jdbc事务管理，事务控制由mybatis --><transactionManager type="JDBC" /><!-- 数据库连接池，由mybatis管理 --><dataSource type="POOLED"><property name="driver" value="${jdbc.driver}" /><property name="url" value="${jdbc.url}" /><property name="username" value="${jdbc.username}" /><property name="password" value="${jdbc.password}" /></dataSource></environment></environments><!-- 加载 映射文件 --><mappers><package name="com.baowei.mapper" /></mappers></configuration>

6、MyBatisUtil.Java工具的使用

import java.io.IOException;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;public class MyBatisUtil {/** * 获取SqlSessionFactory *  * @return SqlSessionFactory */public static SqlSessionFactory getSqlSessionFactory() {String resource = "SqlMapConfig.xml";InputStream inputStream = null;SqlSessionFactory sqlSessionFactory = null;try {inputStream = Resources.getResourceAsStream(resource);sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);} catch (IOException e) {e.printStackTrace();}return sqlSessionFactory;}/** * 获取SqlSession *  * @return SqlSession */public static SqlSession getSqlSession() {return getSqlSessionFactory().openSession();}/** * 获取SqlSession *  * @param isAutoCommit *            true 表示创建的SqlSession对象在执行完SQL之后会自动提交事务 false *            表示创建的SqlSession对象在执行完SQL之后不会自动提交事务 *            ，这时就需要我们手动调用sqlSession.commit()提交事务 * @return SqlSession */public static SqlSession getSqlSession(boolean isAutoCommit) {return getSqlSessionFactory().openSession(isAutoCommit);}public static void main(String[] args) {System.out.println(getSqlSessionFactory());}}

7、one2many关联查询的测试代码

import org.apache.ibatis.session.SqlSession;import org.junit.Test;import com.baowei.entity.Classes;import com.baowei.mapper.ClassMapper;import com.baowei.utils.MyBatisUtil;public class TestOne2Many {/** * 非懒加载的one2many的测试 */@Testpublic void testGetClass() {SqlSession sqlSession = MyBatisUtil.getSqlSession();ClassMapper mapper = sqlSession.getMapper(ClassMapper.class);Classes clazz = mapper.getClass3(1);// 使用SqlSession执行完SQL之后需要关闭SqlSessionsqlSession.close();System.out.println(clazz);}/** * 懒加载的one2many的测试 */@Testpublic void testGetClass2() {SqlSession sqlSession = MyBatisUtil.getSqlSession();ClassMapper mapper = sqlSession.getMapper(ClassMapper.class);Classes clazz = mapper.getClass4(1);// 使用SqlSession执行完SQL之后需要关闭SqlSessionsqlSession.close();System.out.println(clazz.getId());// 可用于测试懒加载(查看日志的sql输出结果，就可以发现使用了懒加载)System.out.println(clazz.getTeacher());System.out.println(clazz.getStudents());}}

8、Mybatis一对多关联查询的总结

      MyBatis中使用collection标签来解决一对多的关联查询，ofType属性指定集合中元素的对象类型。

9、参考的博客地址

http://www.cnblogs.com/xdp-gacl/p/4264440.html

10、源码下载