Mybatis一对多，多对一

复杂查询环境搭建

创建一个数据库表

CREATE TABLE `teacher` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO teacher(`id`, `name`) VALUES (1, '秦老师'); 

CREATE TABLE `student` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  `tid` INT(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fktid` (`tid`),
  CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');

编写对应的实体类这里使用了Lombok插件
Student实体类

package com.wx.pojo;

import lombok.Data;

@Data
public class Student {
    private int id;
    private String name;
    //学生需要关联老师
    private Teacher teacher;
}

Teacher实体类

package com.wx.pojo;

import lombok.Data;

@Data
public class Teacher {
    private int id;
    private String name;
}

建立Mapper接口
StudentMapper

package com.wx.dao;

public interface StudentMapper {
}

TeacherMapper（等下用于测试）

import com.wx.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

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

建立Mapper.xml
在resource文件夹下创建与java对应的文件夹

这样就编译之后就可以在同一文件下，因为在我们之前的学习中使用包或者使用类来注册绑定都需要Mapper和Mapper.xml在同一个文件夹下，资源文件则不需要
StudentMapper.xml

DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wx.dao.StudentMapper">

mapper>

TeacherMapper.xml

DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wx.dao.TeacherMapper">

mapper>

在核心配置文件中使用包注册绑定Mapper

DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="db.properties">properties>
    <typeAliases>
        <package name="com.wx.pojo"/>
    typeAliases>
    <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>
    <mappers>
        <package name="com.wx.dao"/>
    mappers>
configuration>

测试（查询teacher）

import com.wx.dao.TeacherMapper;
import com.wx.pojo.Teacher;
import com.wx.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;

public class Test {
    public static void main(String[] args) {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);
        sqlSession.close();

    }
}

输出结果

到这里测试环境就配置好了

多对一处理

按照查询嵌套处理
这里在Mybatis的配置文件中使用了别名

DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wx.dao.StudentMapper">

    <select id="getStudent" resultMap="ST">
        select * from student
    select>
    <resultMap id="ST" type="student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        
        <association property="teacher" column="tid" javaType="teacher" select="getTeacher"/>
    resultMap>
    <select id="getTeacher" resultType="teacher">
        select * from teacher where id = #{id}
    select>

mapper>

测试结果

    @org.junit.Test
    public void getStudent(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudent();
        for (Student student : studentList) {
            System.out.println(student);
        }
    }
}

按照结果嵌套处理

    <select id="getStudent2" resultMap="ST2">
        select s.id sid,s.name sname,t.name tname
        from student s,teacher t
        where s.tid = t.id;
    select>
    <resultMap id="ST2" type="student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname"/>
        association>
    resultMap>

测试结果

    @org.junit.Test
    public void getStudent2(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudent2();
        for (Student student : studentList) {
            System.out.println(student);
        }
    }

第一种按照查询嵌套处理就好比查询数据库中的子查询，只不过后面的查询老师条件是的id是根据学生查询出来的tid定的，所以要进行一次查询学生

select * from student where tid = (select id from teacher where id =1)

第二种按照结果嵌套处理就好比数据库中联表查询

一对多处理

比如一个老师拥有对个学生
对于老师而言就是一对多的关系
环境搭建
实体类
Teacher

package com.wx.pojo;

import lombok.Data;

import java.util.List;

@Data
public class Teacher {
    private int id;
    private String name;
    private List<Student> student;
}

Student

package com.wx.pojo;

import lombok.Data;
@Data
public class Student {
    private int id;
    private String name;
    private int tid;
}

TeacherMapper

package com.wx.dao;


import com.wx.pojo.Teacher;

import java.util.List;

public interface TeacherMapper {
     List<Teacher> getTeacher();
}

StudentMapper

package com.wx.dao;
public interface StudentMapper {

}

TeacherMapper.xml

DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wx.dao.TeacherMapper">
    <select id="getTeacher" resultType="Teacher">
        select *from teacher
    select>
mapper>

按结果嵌套查询
TeacherMapper

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

TeacherMapper.xml

    <select id="getTeacher" resultMap="TS">
        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>
    <resultMap id="TS" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        
        <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        collection>
    resultMap>

测试输出

@Test
    public void test(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper =  sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher(1);

            System.out.println(teacher);


        sqlSession.close();
    }

按照查询嵌套处理

TeacherMapper

public interface TeacherMapper {
     //List getTeacher();
     //获取指定老师下的所有学生和老师的信息
     Teacher getTeacher2(@Param("tid") int id);
}

TeacherMapper.xml

<select id="getTeacher2" resultMap="TS2">
        select * from teacher where id = #{tid};
    select>
    <resultMap id="TS2" type="Teacher">
        <collection property="students" javaType="ArrayList" ofType="Student" select="getStudent" column="id"/>
                
    resultMap>
    <select id="getStudent" resultType="Student">
        select * from student where tid = #{tid}
    select>

测试输出

    @Test
    public void test(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper =  sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher2 = mapper.getTeacher2(1);
        System.out.println(teacher2);
        sqlSession.close();
    }