10，复杂查询的处理(一对多，多对一)

1.复杂环境的搭建

（1）配置文件

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=UTF-8
username=root
password=123456

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--引入外部配置文件-->
    <properties resource="db.properties"/>

    <!--配置日志工厂-->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

    <!--设置别名-->
    <typeAliases >
        <typeAlias type="com.zuo.entity.Student" alias="Student"/>
        <typeAlias type="com.zuo.entity.Teacher" alias="Teacher"/>
    </typeAliases>

    <environments default="development2">
        <environment id="development2">
            <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>
    <!--每一个Mapper.xml都需要注册-->
    <mappers>
        <mapper class="com.zuo.dao.StudentMapper"/>
        <mapper class="com.zuo.dao.TeacherMapper"/>
    </mappers>
</configuration>

（2）编写工具类

MyBatis.java

public class MyBatisUtils {
    private static SqlSessionFactory sqlSessionFactory;
    static {
        //使用MyBatis第一步获取sqlSessionFactory对象
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //既然有了 SqlSessionFactory，顾名思义，我们可以从中获得 SqlSession 的实例。
    // SqlSession 提供了在数据库执行 SQL 命令所需的所有方法。
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession(true);
    }
}

（3）编写实体类

（4）dao持久层的搭建

注意：

  Mapper和Mapper.xml最好写在同一包名下，也可以卸载resources下但是必须在相同的包名下

  Mapper接口和Mapper.xml的名称必须相同！！！！！

（5）测试

2.多对一的处理（按照学生的角度）

（1）创建表结构

CREATE TABLE `student`  (
  `id` int(10) NOT NULL,
  `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `tid` int(10) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fid`(`tid`) USING BTREE,
  CONSTRAINT `fid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

CREATE TABLE `teacher`  (
  `id` int(10) NOT NULL,
  `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

（2）创建实体类

student

@Data
public class Student {
    private int id;
    private String name;
    //外键关联
    private Teacher teacher;
}

teacher

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

（3）根据结果嵌套-----sql中的关联查询

注意：

  sql语句中要查询的字段的名称必须起别名！！

<!--根据结果嵌套-->
<select id="getListStudentInfo" resultMap="StudentTeacher">
    select s.id sid,s.name sname,t.name tname
    from Student as s,Teacher as t
    where s.tid = t.id;
</select>

<resultMap id="StudentTeacher" type="Student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <!--复杂对象需要单独处理  association:对象  collection:集合-->
    <association property="teacher" javaType="Teacher">
        <result property="name" column="tname"/>
    </association>
</resultMap>

 根据查询嵌套----sql中的子查询

<!--根据查询嵌套-->
<select id="getListStudentInfo2" resultMap="StudentTeacher2">
    select * from student
</select>

<resultMap id="StudentTeacher2" type="Student">
    <result property="id" column="id"/>
    <result property="name" column="name"/>
    <!--复杂对象需要单独处理  association:对象  collection:集合-->
    <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>

<select id="getTeacher" resultType="Teacher">
    select * from teacher where id = #{tid}
</select>

 3.一对多处理（在老师角度）

实体类

 student

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

 teacher

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

<!--根据结果嵌套查询-->
<select id="getTeacher" resultMap="TeacherMap">
    select s.id sid,s.name sname,t.id tid,t.name tname
    from student s ,teacher t
    where s.tid = t.id
    and t.id = #{tid};
</select>

<resultMap id="TeacherMap" type="Teacher">
    <result property="id" column="tid" />
    <result property="name" column="tname" />
    <!---->
    <collection property="StudentList" ofType="Student" column="">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <result property="tid" column="tid"/>
    </collection>
</resultMap>

<!--根据查询嵌套查询-->
<select id="getTeacher2" resultMap="TeacherMap2">
    select * from teacher where id = #{tid};
</select>

<resultMap id="TeacherMap2" type="Teacher">
    <collection property="StudentList" javaType="ArrayList" ofType="Student" select="getStudent" column="id" />
</resultMap>

<select id="getStudent" resultType="Student">
    select * from student where tid = #{tid}
</select>