关闭

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

标签: mybatis
462人阅读 评论(0) 收藏 举报
分类:


说完一对一的表连接的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]]


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:269808次
    • 积分:4934
    • 等级:
    • 排名:第5951名
    • 原创:226篇
    • 转载:52篇
    • 译文:2篇
    • 评论:16条
    最新评论