[mybatis]mybatis多对一与一对多的复杂查询

多对一与一对多处理

• 多个学生，对应一个老师
• 对于学生而言，关联……一个老师
• 对于老师而言，集合……多个学生

先创建两张表，学生表中对应tid是老师表的属性

CREATE TABLE `teacher` (
`id`  int(10) NOT NULL ,
`name`  varchar(30) NULL ,
PRIMARY KEY (`id`)
)
INSERT INTO `teacher` (`id`, `name`) VALUES ('1', '王方')
INSERT INTO `teacher` (`id`, `name`) VALUES ('2', '秦明')

CREATE TABLE `student` (
`id`  int(10) NOT NULL ,
`name`  varchar(30) NULL ,
`tid`  int(10) NULL ,
PRIMARY KEY (`id`),
CONSTRAINT `tid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
)
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小米', '1')
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小镇', '2')
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', '小红', '2')
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('6', '小青', '1')

创建老师和学生实体类，学生实体类中老师的id必须先给一个老师对象，不能为int

package com.demo2.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @author panglili
 * @create 2022-07-22-20:01
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private int id;
    private String name;
    private Teacher tid;
}

package com.demo2.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @author panglili
 * @create 2022-07-22-20:01
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
    private int id;
    private String name;

}

报错处理

java.io.IOException: Could not find resource mybatis_config.xml

如果程序中报无法找到某个资源文件的错误：

解决方案：

（1）在引用的地方查看资源文件路径

（2）检查是否在maven配置文件中导入了静态资源文件过滤

<build>
    <resources>
        <resource>
            <directory>src/main/resources</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>true</filtering>
        </resource>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>true</filtering>
        </resource>
    </resources>
</build>

没有的话加上这段话！！！

（3）检查target生成文件是否有静态资源

没有的话手动复制一份过去

再次启动试试看！

查询所有的学生对应的老师的信息：

sql：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.demo2.mapper.StudentMapper">

<!--查询学生的信息和对应的老师名
     1.查询所有的学生
     2.查询学生的id对应的tid的老师
     3.根据tid找到老师的名字-->
    <!--方式一 子嵌套 -->
    <select id="getStudents" resultMap="student">
        select * from student
    </select>
    <resultMap id="student" type="com.demo2.pojo.Student">
        <result property="id" column="id"></result>
        <result property="name" column="name"></result>
        <!--当遇到属性是对象的时候用association -->
        <association property="teacher" column="tid" javaType="com.demo2.pojo.Teacher" select="getTeacher"></association>
    </resultMap>

    <!--方式二 -->
     <!-- 按照结果嵌套处理-->
    <select id="getStudents2" resultMap="studen2">
        select student.id sid,student.name sname,teacher.name tname
        from student,teacher
        where student.tid=teacher.id
    </select>
    <resultMap id="student2" type="com.demo2.pojo.Student">
        <result property="id" column="sid"></result>
        <result property="name" column="sname"></result>
        <association property="teacher" javaType="com.demo2.pojo.Teacher">
            <result property="name" column="tname"></result>
        </association>
    </resultMap>
    <select id="getTeacher" resultType="com.demo2.pojo.Teacher">
        select * from teacher where id=#{id}
    </select>
</mapper>

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.demo2.mapper.TeacherMapper">
<select id="getTeacher" resultType="Teacher">
    select * from teacher where id=#{id}
</select>

</mapper>

查询某个老师包含的所有的学生信息：

<select id="getTeacher" resultMap="Tea">
    select s.id sid ,s.name sname,t.name tname,t.id tid
    from student s,teacher t
    where s.tid=tid and tid=#{tid}   </select>
<resultMap id="Tea" type="com.demo2.pojo.Teacher">
    <result property="id" column="tid"></result>
    <result property="name" column="tname"></result>
    <collection property="students2" ofType="com.demo2.pojo.Student">
        <result property="id" column="sid"></result>
        <result property="name" column="sname"></result>
        <result property="tid" column="tid"></result>
    </collection>
</resultMap>