mybatis关联映射（表的三种关系，含具体代码）

向右看齐 2023-10-06 21:51 7阅读 0赞

**目录**

1、数据库表与表之间的关系

2、项目运用

一、一对一 多对一

二、一对多

--------------------

### 1、数据库表与表之间的关系 ###

![ce85f766e69f4b56b9d8afd6bcf25ff6.png][]

**一对一**：在任意一个表中设置一个外键

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y-r5oiR6ICB5Lyv_size_20_color_FFFFFF_t_70_g_se_x_16][]

**一对多：**

在多的表中设置外键，标记少的表中id

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y-r5oiR6ICB5Lyv_size_20_color_FFFFFF_t_70_g_se_x_16 1][]

**多对多：（学生--选课）**

建立关系表

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y-r5oiR6ICB5Lyv_size_20_color_FFFFFF_t_70_g_se_x_16 2][]

--------------------

### 2、项目运用 ###

#### 一、一对一 多对一 ####

查询每个学生的对应的老师

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y-r5oiR6ICB5Lyv_size_20_color_FFFFFF_t_70_g_se_x_16 3][]

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y-r5oiR6ICB5Lyv_size_20_color_FFFFFF_t_70_g_se_x_16 4][]

> 查询每个学生的对应的老师

SELECT s.id,s.Sname,t.Tname FROM student s,teacher t where s.t_id = t.id

在项目中，建立两个javaBeen，一个是Student.java，另一个是Teacher.java文件

将数据库中的字段设置为私有，并把teacher字段设置到Student.java中。设置Get()、Set()、ToString()方法。

StudentMapper.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.qcby.dao.StudentDao">
        
        <select id="getStudent" resultMap="StudentTeacher">
            select * from student;
        </select>
        <resultMap id="StudentTeacher" type="com.qcby.enty.Student">
            <id property="id" column="id"/>
            <result property="Sname" column="Sname"/>
            <result property="sex" column="sex"/>
            <result property="age" column="age"/>
            <result property="t_id" column="t_id"/>
            
            
            
            
            <association property="teacher" column="t_id" javaType="com.qcby.enty.Teacher" select="getTeacher"/>
        </resultMap>
        <select id="getTeacher" resultType="com.qcby.enty.Teacher">
            select * from teacher where id = #{t_id}
        </select>
    
    
        
        
        <select id="getStudent1" resultMap="StudentTeacher1">
       SELECT  student.id,student.Sname,teacher.Tname FROM student  LEFT JOIN teacher  on student.t_id = teacher.id
    </select>
        <resultMap id="StudentTeacher1" type="com.qcby.enty.Student">
            <result property="id" column="id"/>
            <result property="Sname" column="Sname"/>
            <result property="sex" column="sex"/>
            <result property="age" column="age"/>
            <result property="t_id" column="t_id"/>
            <association property="teacher" javaType="com.qcby.enty.Teacher">
                <result property="id" column="id"/>
                <result property="Tname" column="Tname"/>
            </association>
        </resultMap>
    
    </mapper>

StudentTest.java

package com.qcby.test;
    
    import com.qcby.dao.StudentDao;
    import com.qcby.dao.UserDao;
    import com.qcby.enty.Student;
    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.After;
    import org.junit.Before;
    import org.junit.Test;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.List;
    
    public class StudentTest {
    
        private InputStream in = null;
        private SqlSession session = null;
        private StudentDao mapper = null;
    
        @Before  //前置通知, 在方法执行之前执行
        public void init() throws IOException {
            //加载主配置文件，目的是为了构建SqlSessionFactory对象
            in = Resources.getResourceAsStream("SqlMapConfig.xml");
            //创建SqlSessionFactory对象
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
            //通过SqlSessionFactory工厂对象创建SqlSesssion对象
            session = factory.openSession();
            //通过Session创建UserDao接口代理对象
            mapper = session.getMapper(StudentDao.class);
        }
    
        @After  //@After: 后置通知, 在方法执行之后执行 。
        public void destory() throws IOException {
            //释放资源
            session.close();
            in.close();
        }
        @Test
        public  void getStudent(){
            List<Student> students = mapper.getStudent();
            for (Student student:students){
                System.out.println(student.toString());
            }
        }
        @Test
        public  void getStudent1(){
            List<Student> students = mapper.getStudent();
            for (Student student:students){
                System.out.println(student.toString());
            }
        }
    
    }

StudentDao.java

package com.qcby.dao;
    
    import com.qcby.enty.Student;
    
    import java.util.List;
    
    public interface StudentDao {
    
        List<Student> getStudent();
        List<Student> getStudent1();
    }

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y-r5oiR6ICB5Lyv_size_20_color_FFFFFF_t_70_g_se_x_16 5][]

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y-r5oiR6ICB5Lyv_size_20_color_FFFFFF_t_70_g_se_x_16 6][]

--------------------

#### 二、一对多 ####

查询每个老师有多少学生

在Teacher.java中加入 private List<Student> student;并生成Get()、Set()、ToString()方法。

建立一个TeacherMapper.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.qcby.dao.TeacherDao">
        <select id="getTeacher" resultMap="TeacherStudent">
            select * from teacher;
        </select>
        <resultMap id="TeacherStudent" type="com.qcby.enty.Teacher">
            <id property="id" column="id"/>
            <result property="Tname" column="Tname"/>
            
            
            
            
            
            
            <collection property="student" column="id" javaType="ArrayList" ofType="com.qcby.enty.Student"
               select="getStudentByTeacherTd"/>
        </resultMap>
        <select id="getStudentByTeacherTd" resultType="com.qcby.enty.Student">
            select  * from student where t_id = #{t_id}
        </select>
    </mapper>

注意：这个时候要在SqlMapConfig.xml中加入关联的语句

TeacherDao.java

package com.qcby.dao;
    
    import com.qcby.enty.Teacher;
    
    import java.util.List;
    
    public interface TeacherDao {
        List<Teacher> getTeacher();
    }

TeacherTest.java

package com.qcby.test;
    
    import com.qcby.dao.StudentDao;
    import com.qcby.dao.TeacherDao;
    import com.qcby.enty.Student;
    import com.qcby.enty.Teacher;
    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.After;
    import org.junit.Before;
    import org.junit.Test;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.List;
    
    public class TeacherTest {
    
            private InputStream in = null;
            private SqlSession session = null;
            private TeacherDao mapper = null;
    
            @Before  //前置通知, 在方法执行之前执行
            public void init() throws IOException {
                //加载主配置文件，目的是为了构建SqlSessionFactory对象
                in = Resources.getResourceAsStream("SqlMapConfig.xml");
                //创建SqlSessionFactory对象
                SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
                //通过SqlSessionFactory工厂对象创建SqlSesssion对象
                session = factory.openSession();
                //通过Session创建UserDao接口代理对象
                mapper = session.getMapper(TeacherDao.class);
            }
    
            @After  //@After: 后置通知, 在方法执行之后执行 。
            public void destory() throws IOException {
                //释放资源
                session.close();
                in.close();
            }
    
    
            @Test
            public void getTeacher(){
                List<Teacher> teachers = mapper.getTeacher();
                for (Teacher teacher:teachers
                     ) {
                    System.out.println(teacher.toString());
    
                }
            }
    
        }

**运行结果：**

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y-r5oiR6ICB5Lyv_size_20_color_FFFFFF_t_70_g_se_x_16 7][]

[ce85f766e69f4b56b9d8afd6bcf25ff6.png]: https://img-blog.csdnimg.cn/ce85f766e69f4b56b9d8afd6bcf25ff6.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y-r5oiR6ICB5Lyv_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/1f00370dc79045198911af99199f3d13.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Y-r5oiR6ICB5Lyv,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y-r5oiR6ICB5Lyv_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/d1f214846c004216a1268d60406ca5bc.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Y-r5oiR6ICB5Lyv,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y-r5oiR6ICB5Lyv_size_20_color_FFFFFF_t_70_g_se_x_16 2]: https://img-blog.csdnimg.cn/8a7c21e4e7a84b8ba41a421f15843a67.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Y-r5oiR6ICB5Lyv,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y-r5oiR6ICB5Lyv_size_20_color_FFFFFF_t_70_g_se_x_16 3]: https://img-blog.csdnimg.cn/5856f842f1ba4e1fa72b23c50aad0ec9.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Y-r5oiR6ICB5Lyv,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y-r5oiR6ICB5Lyv_size_20_color_FFFFFF_t_70_g_se_x_16 4]: https://img-blog.csdnimg.cn/9f8943072fa94bc19d1cc336c1bb02bb.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Y-r5oiR6ICB5Lyv,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y-r5oiR6ICB5Lyv_size_20_color_FFFFFF_t_70_g_se_x_16 5]: https://img-blog.csdnimg.cn/272a7d51690c4c31b7767ff41a89538e.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Y-r5oiR6ICB5Lyv,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y-r5oiR6ICB5Lyv_size_20_color_FFFFFF_t_70_g_se_x_16 6]: https://img-blog.csdnimg.cn/21521deaace340a6a35015dd4f3eea21.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Y-r5oiR6ICB5Lyv,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y-r5oiR6ICB5Lyv_size_20_color_FFFFFF_t_70_g_se_x_16 7]: https://img-blog.csdnimg.cn/e1795e30ecbe4d518cecfb192513cb86.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Y-r5oiR6ICB5Lyv,size_20,color_FFFFFF,t_70,g_se,x_16