Mybatis学习总结之表的一对多关联查询问题



说完一对一的表连接的ORM映射，再说说一对多的情况。这里有人就有疑问，表的一对多我可以理解，但它映射到类的定义中应该如何表示呢。答案是：“一”的类增加一个类型为List属性，List中的每个item的类型为“多”的类型。

我们队上次那个例子再做一个拓展，增加一个Student类和student表，并且与class表和Classes类是多对一的关系。这里，我们需要对原有的Classes类进行拓展，增加一个students属性，类型为List<Student>。我们可以应用类继承，创建一个子类，将这个新属性加进去，这样原有的程序就不需要修改了。

package com.happyBKs.mybatis.C1_1.beans;
 
public class Student {
    private int id;
    private String name;
    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 Student(int id, String name) {
        super();
        this.id = id;
        this.name = name;
    }
    public Student() {
        super();
    }
    @Override
    public String toString() {
        return "Student [id=" + id + ", name=" + name + "]";
    }
 
}

package com.happyBKs.mybatis.C1_1.beans;
 
public class Classes {
    private int id;
    private String name;
    private Teacher teacher;
    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 Teacher getTeacher() {
        return teacher;
    }
    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }
    public Classes(int id, String name, Teacher teacher) {
        super();
        this.id = id;
        this.name = name;
        this.teacher = teacher;
    }
    public Classes() {
        super();
    }
    @Override
    public String toString() {
        return "Classes [id=" + id + ", name=" + name + ", teacher=" + teacher
                + "]";
    }
 
}

package com.happyBKs.mybatis.C1_1.beans;
 
import java.util.List;
 
public class ClassesSuc extends Classes {
    private List<Student> students;
 
    public List<Student> getStudents() {
        return students;
    }
 
    public void setStudents(List<Student> students) {
        this.students = students;
    }
 
    public ClassesSuc(int id, String name, Teacher teacher,
            List<Student> students) {
        super(id, name, teacher);
        this.students = students;
    }
 
    public ClassesSuc(int id, String name, Teacher teacher) {
        super(id, name, teacher);
    }
 
    public ClassesSuc() {
        super();
    }
 
    @Override
    public String toString() {
        return "ClassesSuc [students=" + students + ", getId()=" + getId()
                + ", getName()=" + getName() + ", getTeacher()=" + getTeacher()
                + "]";
    }
     
}

package com.happyBKs.mybatis.C1_1.beans;
 
public class Teacher {
    private int id;
    private String name;
    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 Teacher(int id, String name) {
        super();
        this.id = id;
        this.name = name;
    }
    public Teacher() {
        super();
    }
    @Override
    public String toString() {
        return "Teacher [id=" + id + ", name=" + name + "]";
    }
 
}

数据库增加student表：

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);

接下来建立映射关系，方法依旧有两种：

方式1：classMapper3.xml

这里仍然是对返回结果类型的映射，这里注意collection标签，用来做一对多的关联查询。property="students" 代表的是“一”即Classes类中的students属性，因为这个属性类型为List，所以这里需要collection标签，并且需要指明List中的每个元素的类型是什么，即ofType="com.happyBKs.mybatis.C1_1.beans.Student"

然后在collection中再给出Student类与student表的属性与字段的映射关系。

<?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.happyBKs.mybatis.C1_1.classMapper3">
 
<!-- 
方式一: 嵌套结果: 使用嵌套结果映射来处理重复的联合结果的子集
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.happyBKs.mybatis.C1_1.beans.ClassesSuc" id="ClassResultMap3">
    <id property="id" column="c_id"/>
    <result property="name" column="c_name"/>
    <association property="teacher" column="teacher_id" javaType="com.happyBKs.mybatis.C1_1.beans.Teacher">
        <id property="id" column="t_id"/>
        <result property="name" column="t_name"/>
    </association>
    <!-- ofType指定students集合中的对象类型 -->
    <collection property="students" ofType="com.happyBKs.mybatis.C1_1.beans.Student">
        <id property="id" column="s_id"/>
        <result property="name" column="s_name"/>
    </collection>
</resultMap>
 
 
 
 
</mapper>

方式2：

与之前的association的情况相似，我们可以不再association中指定类的属性映射。collection也是一样。可以用一个select标签属性指定另一个select标签，即嵌套查询来完成一对多的映射。

<?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.happyBKs.mybatis.C1_1.classMapper4">
 
<!-- 
    方式二：嵌套查询：通过执行另外一个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.happyBKs.mybatis.C1_1.beans.ClassesSuc" id="ClassResultMap4">
    <id property="id" column="c_id"/>
    <result property="name" column="c_name"/>
    <association property="teacher" column="teacher_id" javaType="com.happyBKs.mybatis.C1_1.beans.Teacher" select="getTeacher2"></association>
    <collection property="students" ofType="_Student" column="c_id" select="getStudent"></collection>
 </resultMap>
  
 <select id="getTeacher2" parameterType="int" resultType="com.happyBKs.mybatis.C1_1.beans.Teacher">
    SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
 </select>
  
 <select id="getStudent" parameterType="int" resultType="com.happyBKs.mybatis.C1_1.beans.Student">
    SELECT s_id id, s_name name FROM student WHERE class_id=#{id}
 </select>
 
 
 
 
 
</mapper>

在config.xml配置文件中注册映射文件：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
 
<properties resource="db.properties"/>
 
 
<!-- 
development: 开发模式
work: 工作模式
 -->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="${driver}" />
                <property name="url" value="${url}" />
                <property name="username" value="${username}" />
                <property name="password" value="${password}" />
            </dataSource>
        </environment>
    </environments>
     
    <!-- 在conf.xml文件中注册userMapper.xml文件 -->
    <mappers>
        <mapper resource="com/happyBKs/mybatis/C1_1/beans/orderMapper.xml"/>
        <mapper resource="com/happyBKs/mybatis/C1_1/beans/orderMapper2.xml"/>
        <mapper resource="com/happyBKs/mybatis/C1_1/beans/classMapper.xml"/> 
        <mapper resource="com/happyBKs/mybatis/C1_1/beans/classMapper2.xml"/>    
        <mapper resource="com/happyBKs/mybatis/C1_1/beans/classMapper3.xml"/>
        <mapper resource="com/happyBKs/mybatis/C1_1/beans/classMapper4.xml"/>    
    </mappers>
 
</configuration>

测试代码：

package com.happyBKs.mybatis.C1_1;
 
import java.io.IOException;
import java.io.Reader;
 
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 org.junit.Test;
 
import com.happyBKs.mybatis.C1_1.beans.Classes;
import com.happyBKs.mybatis.C1_1.beans.ClassesSuc;
import com.happyBKs.mybatis.C1_1.beans.Order;
 
public class TestOneToMulti {
 
    @Test
    public void test3()
    {
        String resource = "config.xml"; 
        //加载mybatis的配置文件（它也加载关联的映射文件）
        Reader reader=null;
        try {
            reader = Resources.getResourceAsReader(resource);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } 
        //构建sqlSession的工厂
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        //创建能执行映射文件中sql的sqlSession
        SqlSession session = sessionFactory.openSession(true);
        //映射sql的标识字符串
        String statement = "com.happyBKs.mybatis.C1_1.classMapper3"+".getClass3";//先找映射文件，后找标签
        //执行查询返回一个唯一user对象的sql
        ClassesSuc c = session.selectOne(statement, 1);
        System.out.println(c);
    }
     
    @Test
    public void test4()
    {
        String resource = "config.xml"; 
        //加载mybatis的配置文件（它也加载关联的映射文件）
        Reader reader=null;
        try {
            reader = Resources.getResourceAsReader(resource);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } 
        //构建sqlSession的工厂
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        //创建能执行映射文件中sql的sqlSession
        SqlSession session = sessionFactory.openSession(true);
        //映射sql的标识字符串
        String statement = "com.happyBKs.mybatis.C1_1.classMapper4"+".getClass4";//先找映射文件，后找标签
        //执行查询返回一个唯一user对象的sql
        ClassesSuc c = session.selectOne(statement, 1);
        System.out.println(c);
    }
}

结果打印：

ClassesSuc [students=[Student [id=1, name=xs_A], Student [id=2, name=xs_B], Student [id=3, name=xs_C]], getId()=1, getName()=bj_a, getTeacher()=Teacher [id=1, name=LS1]]