mybatis-实现关联表查询

太过爱你忘了你带给我的痛 2022-07-28 01:45 252阅读 0赞

## 一、一对一关联 ##

###  1.1、提出需求 ###

根据班级id查询班级信息(带老师的信息)

### 1.2、创建表和数据 ###

创建一张教师表和班级表，这里我们假设一个老师只负责教一个班，那么老师和班级之间的关系就是一种一对一的关系。

CREATE TABLE teacher(
        t_id INT PRIMARY KEY AUTO_INCREMENT, 
        t_name VARCHAR(20)
    );
    CREATE TABLE class(
        c_id INT PRIMARY KEY AUTO_INCREMENT, 
        c_name VARCHAR(20), 
        teacher_id INT
    );
    ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher(t_id);    
    
    INSERT INTO teacher(t_name) VALUES('teacher1');
    INSERT INTO teacher(t_name) VALUES('teacher2');
    
    INSERT INTO class(c_name, teacher_id) VALUES('class_a', 1);
    INSERT INTO class(c_name, teacher_id) VALUES('class_b', 2);

表之间的关系如下：

![011119505663513.png][]

### 1.3、定义实体类 ###

1、Teacher类，Teacher类是teacher表对应的实体类。

package me.gacl.domain;
    
    /**
     * @author gacl
     * 定义teacher表对应的实体类
     */
    public class Teacher {
    
        //定义实体类的属性，与teacher表中的字段对应
        private int id;            //id===>t_id
        private String name;    //name===>t_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;
        }
    
        @Override
        public String toString() {
            return "Teacher [id=" + id + ", name=" + name + "]";
        }
    }

2、Classes类，Classes类是class表对应的实体类

package me.gacl.domain;
    
    /**
     * @author gacl
     * 定义class表对应的实体类
     */
    public class Classes {
    
        //定义实体类的属性，与class表中的字段对应
        private int id;            //id===>c_id
        private String name;    //name===>c_name
        
        /**
         * class表中有一个teacher_id字段，所以在Classes类中定义一个teacher属性，
         * 用于维护teacher和class之间的一对一关系，通过这个teacher属性就可以知道这个班级是由哪个老师负责的
         */
        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;
        }
    
        @Override
        public String toString() {
            return "Classes [id=" + id + ", name=" + name + ", teacher=" + teacher+ "]";
        }
    }

### 1.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="me.gacl.mapping.classMapper">
    
        
    
        
        <select id="getClass" parameterType="int" resultMap="ClassResultMap">
            select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
        </select>
        
        <resultMap type="me.gacl.domain.Classes" id="ClassResultMap">
            <id property="id" column="c_id"/>
            <result property="name" column="c_name"/>
            <association property="teacher" javaType="me.gacl.domain.Teacher">
                <id property="id" column="t_id"/>
                <result property="name" column="t_name"/>
            </association>
        </resultMap>
        
        
         <select id="getClass2" parameterType="int" resultMap="ClassResultMap2">
            select * from class where c_id=#{id}
         </select>
         
         <resultMap type="me.gacl.domain.Classes" id="ClassResultMap2">
            <id property="id" column="c_id"/>
            <result property="name" column="c_name"/>
            <association property="teacher" column="teacher_id" select="getTeacher"/>
         </resultMap>
         
         <select id="getTeacher" parameterType="int" resultType="me.gacl.domain.Teacher">
            SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
         </select>
    
    </mapper>

在conf.xml文件中注册classMapper.xml

### 1.5、编写单元测试代码 ###

package me.gacl.test;
    
    import me.gacl.domain.Classes;
    import me.gacl.util.MyBatisUtil;
    import org.apache.ibatis.session.SqlSession;
    import org.junit.Test;
    
    public class Test3 {
        
        @Test
        public void testGetClass(){
            SqlSession sqlSession = MyBatisUtil.getSqlSession();
            /**
             * 映射sql的标识字符串，
             * me.gacl.mapping.classMapper是classMapper.xml文件中mapper标签的namespace属性的值，
             * getClass是select标签的id属性值，通过select标签的id属性值就可以找到要执行的SQL
             */
            String statement = "me.gacl.mapping.classMapper.getClass";//映射sql的标识字符串
            //执行查询操作，将查询结果自动封装成Classes对象返回
            Classes clazz = sqlSession.selectOne(statement,1);//查询class表中id为1的记录
            //使用SqlSession执行完SQL之后需要关闭SqlSession
            sqlSession.close();
            System.out.println(clazz);//打印结果：Classes [id=1, name=class_a, teacher=Teacher [id=1, name=teacher1]]
        }
        
        @Test
        public void testGetClass2(){
            SqlSession sqlSession = MyBatisUtil.getSqlSession();
            /**
             * 映射sql的标识字符串，
             * me.gacl.mapping.classMapper是classMapper.xml文件中mapper标签的namespace属性的值，
             * getClass2是select标签的id属性值，通过select标签的id属性值就可以找到要执行的SQL
             */
            String statement = "me.gacl.mapping.classMapper.getClass2";//映射sql的标识字符串
            //执行查询操作，将查询结果自动封装成Classes对象返回
            Classes clazz = sqlSession.selectOne(statement,1);//查询class表中id为1的记录
            //使用SqlSession执行完SQL之后需要关闭SqlSession
            sqlSession.close();
            System.out.println(clazz);//打印结果：Classes [id=1, name=class_a, teacher=Teacher [id=1, name=teacher1]]
        }
    }

### 1.6、MyBatis一对一关联查询总结 ###

MyBatis中使用association标签来解决一对一的关联查询，association标签可用的属性如下：

*  property:对象属性的名称
 *  javaType:对象属性的类型
 *  column:所对应的外键字段名称
 *  select:使用另一个查询封装的结果

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

![011530596753478.png][]

### 2.3、定义实体类 ###

1、Student类

package me.gacl.domain;
    
    /**
     * @author gacl
     * 定义student表所对应的实体类
     */
    public class Student {
    
        //定义属性，和student表中的字段对应
        private int id;            //id===>s_id
        private String name;    //name===>s_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;
        }
    
        @Override
        public String toString() {
            return "Student [id=" + id + ", name=" + name + "]";
        }
    }

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

package me.gacl.domain;
    
    import java.util.List;
    
    /**
     * @author gacl
     * 定义class表对应的实体类
     */
    public class Classes {
    
        //定义实体类的属性，与class表中的字段对应
        private int id;            //id===>c_id
        private String name;    //name===>c_name
        
        /**
         * class表中有一个teacher_id字段，所以在Classes类中定义一个teacher属性，
         * 用于维护teacher和class之间的一对一关系，通过这个teacher属性就可以知道这个班级是由哪个老师负责的
         */
        private Teacher teacher;
        //使用一个List<Student>集合属性表示班级拥有的学生
        private List<Student> students;
    
        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 List<Student> getStudents() {
            return students;
        }
    
        public void setStudents(List<Student> students) {
            this.students = students;
        }
    
        @Override
        public String toString() {
            return "Classes [id=" + id + ", name=" + name + ", teacher=" + teacher
                    + ", students=" + students + "]";
        }
    }

### 2.4、修改sql映射文件classMapper.xml ###

添加如下的SQL映射信息

<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="me.gacl.domain.Classes" id="ClassResultMap3">
            <id property="id" column="c_id"/>
            <result property="name" column="c_name"/>
            <association property="teacher" column="teacher_id" javaType="me.gacl.domain.Teacher">
                <id property="id" column="t_id"/>
                <result property="name" column="t_name"/>
            </association>
            
            <collection property="students" ofType="me.gacl.domain.Student">
                <id property="id" column="s_id"/>
                <result property="name" column="s_name"/>
            </collection>
        </resultMap>
        
        
         <select id="getClass4" parameterType="int" resultMap="ClassResultMap4">
            select * from class where c_id=#{id}
         </select>
         <resultMap type="me.gacl.domain.Classes" id="ClassResultMap4">
            <id property="id" column="c_id"/>
            <result property="name" column="c_name"/>
            <association property="teacher" column="teacher_id" javaType="me.gacl.domain.Teacher" select="getTeacher2"></association>
            <collection property="students" ofType="me.gacl.domain.Student" column="c_id" select="getStudent"></collection>
         </resultMap>
         
         <select id="getTeacher2" parameterType="int" resultType="me.gacl.domain.Teacher">
            SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
         </select>
         
         <select id="getStudent" parameterType="int" resultType="me.gacl.domain.Student">
            SELECT s_id id, s_name name FROM student WHERE class_id=#{id}
         </select>

### 2.5、编写单元测试代码 ###

package me.gacl.test;
    
    import me.gacl.domain.Classes;
    import me.gacl.util.MyBatisUtil;
    import org.apache.ibatis.session.SqlSession;
    import org.junit.Test;
    
    public class Test4 {
        
        @Test
        public void testGetClass3(){
            SqlSession sqlSession = MyBatisUtil.getSqlSession();
            /**
             * 映射sql的标识字符串，
             * me.gacl.mapping.classMapper是classMapper.xml文件中mapper标签的namespace属性的值，
             * getClass3是select标签的id属性值，通过select标签的id属性值就可以找到要执行的SQL
             */
            String statement = "me.gacl.mapping.classMapper.getClass3";//映射sql的标识字符串
            //执行查询操作，将查询结果自动封装成Classes对象返回
            Classes clazz = sqlSession.selectOne(statement,1);//查询class表中id为1的记录
            //使用SqlSession执行完SQL之后需要关闭SqlSession
            sqlSession.close();
            //打印结果：Classes [id=1, name=class_a, teacher=Teacher [id=1, name=teacher1], students=[Student [id=1, name=student_A], Student [id=2, name=student_B], Student [id=3, name=student_C]]]
            System.out.println(clazz);
        }
        
        @Test
        public void testGetClass4(){
            SqlSession sqlSession = MyBatisUtil.getSqlSession();
            /**
             * 映射sql的标识字符串，
             * me.gacl.mapping.classMapper是classMapper.xml文件中mapper标签的namespace属性的值，
             * getClass4是select标签的id属性值，通过select标签的id属性值就可以找到要执行的SQL
             */
            String statement = "me.gacl.mapping.classMapper.getClass4";//映射sql的标识字符串
            //执行查询操作，将查询结果自动封装成Classes对象返回
            Classes clazz = sqlSession.selectOne(statement,1);//查询class表中id为1的记录
            //使用SqlSession执行完SQL之后需要关闭SqlSession
            sqlSession.close();
            //打印结果：Classes [id=1, name=class_a, teacher=Teacher [id=1, name=teacher1], students=[Student [id=1, name=student_A], Student [id=2, name=student_B], Student [id=3, name=student_C]]]
            System.out.println(clazz);
        }
    }

###  2.6、MyBatis一对多关联查询总结 ###

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

到此结束

\---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
  
下面是多对多的一个简单写法，数据库和实体类仅供参考

## 四、多对多查询 ##

#### 4.1.需求：查询用户以及用户购买的商品信息 ####

#### 4.2.映射思路 ####

将用户信息映射到 user 中。

在 user 类中添加订单列表属性 List<Orders> orderslist ，将用户创建的订单映射到 orderslist;

在 Orders 中添加订单明细列表属性 List<OrderDetail>orderdetials ，将订单的明细映射到 orderdetials;

在 OrderDetail 中添加 Items 属性，将订单明细所对应的商品映射到 Item；

#### 4.3.OrdersCustomMapper.xml添加如下代码 ####

<resultMap type="com.mybatis.entity.User" id="userAndItemsResultMap">
        
        <id column="user_id" property="id"/>
        <result column="username" property="username"/>
        <result column="sex" property="sex"/>
        <result column="address" property="address"/>
      
        <collection property="ordersList" ofType="com.mybatis.entity.Orders">
           <id column="id" property="id"/>
           <result column="user_id" property="userid"/>
          <result column="number" property="number"/>
          <result column="createtime" property="createTime"/>
          <result column="note" property="note"/>
         
            <collection property="orderdetails" ofType="com.mybatis.entity.OrderDetail">
                <id column="orderdetail_id" property="id"/>
               <result column="items_id"   property="itemsId"/>
               <result column="items_num"  property="itemsNum"/>
               <result column="orders_id"  property="ordersId"/>
               
               <association property="items" javaType="com.mybatis.entity.Items">
                 <id column="items_id" property="id"/>
                 <result column="items_name" property="itemsName"/>
                 <result column="items_detail" property="detail"/>
                 <result column="items_price" property="price"/>
               </association>
            </collection>
          </collection>
      </resultMap>
    
    <select id="findUserAndItemsResultMap" resultMap="userAndItemsResultMap">
        SELECT 
             t1.*,
             t2.username,
             t2.sex,
             t2.address,
             t3.id orderdetail_id,
             t3.items_id,
             t3.items_num,
             t3.orders_id,
             t4.itemsname items_name,
             t4.detail items_detail,
             t4.price items_price
        FROM
            orders t1,
            t_user t2,
            orderdetail t3,
            items t4
        WHERE t1.user_id =  t2.id AND  t3.orders_id=t1.id AND t3.items_id = t4.id
    </select>

#### 4.4. 在OrderCustomMapper.java添加如下方法 ####

/** 查询用户及用户所购买的商品信息 */
        public List<User> findUserAndItemsResultMap();

4.5.在Junit测试类中添加测试方法

// 查询用户及用户购买的商品的信息
      @Test
      public void TestFindUserAndItemsResultMap() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 创建代理对象
        OrdersCustomMapper oc = sqlSession.getMapper(OrdersCustomMapper.class);
        // 调用mapper的方法
        List<User> list = oc.findUserAndItemsResultMap();
        System.out.println(list);
        sqlSession.close();
      }

[011119505663513.png]: /images/20220727/c8ca94a16c7d4ab88e86aa6189e4c0f9.png
[011530596753478.png]: /images/20220727/ddd004bb217d409a9db41414f8371130.png