mybatis之关联查询

一、简单一对一级联查询
二、嵌套结果与嵌套查询
三、练习
- - ①创表数据：
  - ②实体类的创建
  - - ①Resource实体类：
    - ②Role实体类
    - ③RoleResource实体类
    - ④User实体类
  - ③mapper文件夹下面的UserMapper接口
  - ④resources下创建mapper文件夹，在mapper文件下创建userMapper.xml文件
  - - ①userMapper.xml文件内容
  - ⑤创建配置文件：mybatis-conf.xml
  - ⑥Test类里面测试结果是否和预期的一样
四、练习结果

一、简单一对一级联查询

创建两张表，假设一个老师对应一个班级

CREATE TABLE `teacher` (
  `t_id` int(11) NOT NULL AUTO_INCREMENT,
  `t_name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

insert  into `teacher`(`t_id`,`t_name`) values (1,'张三');
insert  into `teacher`(`t_id`,`t_name`) values (2,'李四');

CREATE TABLE `class` (
  `c_id` int(11) NOT NULL AUTO_INCREMENT,
  `c_name` varchar(20) DEFAULT NULL,
  `teacher_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`c_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

insert  into `class`(`c_id`,`c_name`,`teacher_id`) values (1,'Java',1);
insert  into `class`(`c_id`,`c_name`,`teacher_id`) values (2,'UI',2);

CREATE TABLE `student` (
  `s_id` int(11) NOT NULL AUTO_INCREMENT,
  `s_name` varchar(20) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
insert  into `student`(`s_id`,`s_name`,`class_id`) values (1,'AA',1);
insert  into `student`(`s_id`,`s_name`,`class_id`) values (2,'BB',1);
insert  into `student`(`s_id`,`s_name`,`class_id`) values (3,'CC',1);
insert  into `student`(`s_id`,`s_name`,`class_id`) values (4,'DD',2);
insert  into `student`(`s_id`,`s_name`,`class_id`) values (5,'EE',2);
insert  into `student`(`s_id`,`s_name`,`class_id`) values (6,'FF',2);

创建实体
Teacher 实体

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

    @Override
    public String toString() {
        return "Teacher{" +
                "id=" + id +
                ", name='" + name + ''' +
                '}';
    }
}

Classes实体

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

    @Override
    public String toString() {
        return "Classes{" +
                "id=" + id +
                ", name='" + name + ''' +
                ", teacher=" + teacher +
                '}';
    }
}

ClassesMapper.xml 查询将出现两种方式

方式一：级联查询


    
    
    
    



    SELECT * FROM `class` c LEFT JOIN `teacher` t ON t.`t_id`=c.`teacher_id`

二、嵌套结果与嵌套查询

方式二：嵌套结果，使用嵌套结果映射来处理重复的联合结果的子集封装联表查询的数据(去除重复的数据)


    
    
    
        
        
    



    select * from class 



    
    
    



        select * from user u left join role r on u.role_id=r.id where u.id=#{id}
    


    
        
        
        
        
        
        
        
        
    
    
        select id, name, remark, create_time createTime, update_time updateTime from role where id=#{id}

⑤创建配置文件：mybatis-conf.xml

⑥Test类里面测试结果是否和预期的一样

AppTest测试类

package org.example;

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.example.entity.User;
import org.example.mapper.UserMapper;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.Reader;

public class AppTest {
    SqlSession session;

    @Before
    public void init() throws IOException {
        Reader reader = Resources.getResourceAsReader("mybatis-conf.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        session = sessionFactory.openSession(true);
    }

    @Test
    public void getById1() {
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = mapper.findById1(88);
        System.out.println(user);
    }

    @Test
    public void getById2() {
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = mapper.findById2(87);
        System.out.println(user);
    }

    @After
    public void close() {
        session.close();
    }
}

四、练习结果

mybatis之关联查询

Java相关栏目本月热门文章