Mybatis知识小汇(9)——复杂查询环境搭建 (多对一处理)

文章目录

复杂查询环境搭建

多对一处理(比如多个学生对应一个老师)

sql:

CREATE TABLE teacher(
	id INT PRIMARY KEY,
	NAME VARBINARY(20) DEFAULT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO teacher VALUES(1,'卓中');
CREATE TABLE student(
	id INT PRIMARY KEY,
	NAME VARBINARY(20) DEFAULT NULL,
	tid INT,
	CONSTRAINT ftid FOREIGN KEY(tid) REFERENCES teacher(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO student VALUES(1,'张三' ,1),(2,'李四' ,1),(3,'王五' ,1),(4,'田七' ,1);
测试环境搭建
  1. 导入lombok

  2. 实体类Teacher、Student

    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class Student {
        private int id;
        private String name;
        //注意这里是Teacher对象
        private Teacher teacher;
    }
    
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class Teacher {
        private int id;
        private String name;
    }
    
  3. 接口TeacherMapper、StudentMapper

  4. 可以通过注解或者xml文件进行测试,看环境是否搭建成

按照查询嵌套处理
  1. StudentMapper

    public interface StudentMapper {
        //查询返回学生list
        List<Student> getStudentList();
    }
    
  2. StudentMapper.xml (记得在在核心配置中注册)

    思路:

    • 首先查询所有的学生
    • 再根据所有的学生表中的tid查找对应的老师(子查询)
    <?xml version="1.0" encoding="UTF8" ?>
    <!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.wjq.dao.StudentMapper">
        <select id="getStudentList" resultMap="StudentTeacher">
            select * from student
        </select>
        <resultMap id="StudentTeacher" type="Student">
    <!--        因为Student对象的第三个属性为Teacher类型,因此不能再用resultMap中的普通标签(result、id)-->
    <!--        要用复杂的处理例如collection集合(一对多,一个老师对应多个学生)  association对象(在多对一中使用,可以理解为多个学生关联一个老师)-->
    <!--                 对象中的字段        字段的类型         映射到数据库中的字段名    嵌套查询的id-->
            <association property="teacher" javaType="Teacher" column="tid" select="getTeacher"/>
        </resultMap>
        <select id="getTeacher" resultType="Teacher">
            select * from teacher where id = #{tid};
        </select>
    </mapper>
    
  3. 测试

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

Mybatis知识小汇(9)——复杂查询环境搭建 (多对一处理)

按照结果嵌套处理
  1. StudentMapper

    public interface StudentMapper {
        //查询返回学生list
        List<Student> getStudentList2();
    }
    
  2. StudentMapper.xml (记得在在核心配置中注册)

    思路:

    • 就直接连表查询
    <?xml version="1.0" encoding="UTF8" ?>
    <!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.wjq.dao.StudentMapper">
        <select id="getStudentList2" resultMap="studentTeacher2">
            select s.id sid, s.name sname, t.name tname from student s, teacher t where s.tid = t.id;
        </select>
        <resultMap id="studentTeacher2" type="student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <!--复杂字段用association      javaType指定属性的类型-->
            <association property="teacher" javaType="Teacher">
                <!--Teacher表中的属性-->
                <result property="name" column="tname"/>
            </association>
        </resultMap>
    </mapper>
    
  3. 测试

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

上一节—>使用lombok

如有不对的地方欢迎指出,共同进步!

上一篇:Mybatis-Spring(整合)


下一篇:谷粒 | 09 | 前端增删改查