mybatis关联查询

笔记

多对一的处理

创建数据库表

create table mybatis.teacher
(
    id   int         not null
        primary key,
    name varchar(30) null
)
    charset = utf8;

create table mybatis.student
(
    id   int         not null
        primary key,
    name varchar(30) null,
    tid  int         null,
    constraint fktid
        foreign key (tid) references mybatis.teacher (id)
)
    charset = utf8;

INSERT INTO mybatis.teacher (id, name) VALUES (1, '陈老师');
INSERT INTO mybatis.teacher (id, name) VALUES (2, 'kk');

INSERT INTO mybatis.student (id, name, tid) VALUES (1, '小明', 1);
INSERT INTO mybatis.student (id, name, tid) VALUES (2, '小x', 1);
INSERT INTO mybatis.student (id, name, tid) VALUES (3, '小a', 1);
INSERT INTO mybatis.student (id, name, tid) VALUES (4, '小b', 1);
INSERT INTO mybatis.student (id, name, tid) VALUES (5, '小c', 1);
INSERT INTO mybatis.student (id, name, tid) VALUES (6, 'ii', 2);

1.创建maven项目导入依赖

        
            mysql
            mysql-connector-java
            8.0.12
        
        
            org.mybatis
            mybatis
            3.5.2
        
        
            junit
            junit
            4.12
            test
        

        
            org.springframework
            spring-web
            5.1.9.RELEASE
        

    
        
            
                src/main/resources
                
                    **/*.properties
                    **/*.xml
                
            
            
                src/main/java
                
                    **/*.properties
                    **/*.xml
                
                true
            
        
    

2.mybatis工具类

public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession(true);
    }
}

mybatis-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">

    
    

    
        
        
        
    

    
    
        <package name="com.gg.pojo"/>
    

    default="development">
        
            
            
                
                
                
                
            

        
    

    
    
        
        
        
        
    

db.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT
username=root
pwd=123456

3.实体类

package com.gg.pojo;

import org.apache.ibatis.type.Alias;

//老师实体类
@Alias("teacher")
public class Teacher {
    private int id;
    private String name;

    public Teacher() {
    }

    public Teacher(int id, String name) {
        this.id = id;
        this.name = 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 + '\'' +
                '}';
    }
}



========================================================================




//学生实体类
@Alias("student")
public class Student {
    private int id;
    private String name;
    private Teacher teacher;

    public Student() {
    }

    public Student(int id, String name, Teacher teacher) {
        this.id = id;
        this.name = name;
        this.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 "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", teacher=" + teacher +
                '}';
    }
}

4.mapper

public interface StudentMapper {

    //查询所有的学生信息，以及对应的老师信息
    public List getStudentList();

    public List getStudentList2();

}


=========================================


public interface TeacherMapper {
    @Select("select * from teacher where id=#{tid}")
    Teacher getTeacher(@Param("tid") int id);

}

5.mapper.xml

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">


    
    
        select s.id sid,s.name sname,t.name tname,t.id tid from student s,teacher t
        where s.tid=t.id
    

    
        
        
        
            
            
        
    


    
    
    
        select * from student
    

    
        
        
        
    

    
        select * from teacher where id=#{tid}
    

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">

6.测试

public class ggTest {
    @Test
    public void getTeacher() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = teacherMapper.getTeacher(1);
        System.out.println(teacher);

        sqlSession.close();
    }

    @Test
    public void getStudentList() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        List studentList = studentMapper.getStudentList();

        for (Student student : studentList) {
            System.out.println(student);
        }

        sqlSession.close();
    }

    @Test
    public void getStudentList2() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        List studentList = studentMapper.getStudentList2();

        for (Student student : studentList) {
            System.out.println(student);
        }

        sqlSession.close();
    }

}

7.输出结果

Opening JDBC Connection
Created connection 1074593562.
==>  Preparing: select * from student 
==> Parameters: 
<==    Columns: id, name, tid
<==        Row: 1, 小明, 1
====>  Preparing: select * from teacher where id=? 
====> Parameters: 1(Integer)
<====    Columns: id, name
<====        Row: 1, 陈老师
<====      Total: 1
<==        Row: 2, 小x, 1
<==        Row: 3, 小a, 1
<==        Row: 4, 小b, 1
<==        Row: 5, 小c, 1
<==        Row: 6, ii, 2
====>  Preparing: select * from teacher where id=? 
====> Parameters: 2(Integer)
<====    Columns: id, name
<====        Row: 2, kk
<====      Total: 1
<==      Total: 6
Student{id=1, name='小明', teacher=Teacher{id=1, name='陈老师'}}
Student{id=2, name='小x', teacher=Teacher{id=1, name='陈老师'}}
Student{id=3, name='小a', teacher=Teacher{id=1, name='陈老师'}}
Student{id=4, name='小b', teacher=Teacher{id=1, name='陈老师'}}
Student{id=5, name='小c', teacher=Teacher{id=1, name='陈老师'}}
Student{id=6, name='ii', teacher=Teacher{id=2, name='kk'}}
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@400cff1a]
Returned connection 1074593562 to pool.

一对多

1.创建一个新的项目,修改以下

2.mapper

public interface StudentMapper {


}


public interface TeacherMapper {
    //获取老师
//    List getTeacher();

    //获取指定老师下的所有学生及老师的信息
    Teacher getTeacher1(@Param("tid") int id);

    Teacher getTeacher2(@Param("tid") int id);
}

3.mapper.xml

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">

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">


    
        select s.id sid,s.name sname,t.name tname,t.id tid
        from student s,teacher t
        where s.tid=t.id and t.id=#{tid}
    

    
        
        
        
            
            
            
        
    

    

    
        select * from teacher where id=#{tid}
    

    
        
        
                    column="id"/>
    

    
        select * from student where tid=#{tid}