Mybatis之多对一处理

Mybatis

10、多对一处理

10.1、数据库

• USE ibatis;
  CREATE TABLE teacher(
  teacher_id INT(10) NOT NULL,
  teacher_name VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY(teacher_id)
  )ENGINE = INNOBASE DEFAULT CHARSET=utf8;
  INSERT INTO teacher(teacher_id,teacher_name) VALUES (1,'jcooling');
  
  CREATE TABLE student(
  student_id INT(10) NOT NULL,
  student_NAME VARCHAR(30) DEFAULT NULL,
  teacher_id INT(10) DEFAULT NULL,
  PRIMARY KEY(student_id),
  KEY fkteacher_id (teacher_id),
  CONSTRAINT fkteacher_id FOREIGN KEY(teacher_id) REFERENCES teacher(teacher_id)
  )ENGINE = INNOBASE DEFAULT CHARSET=utf8;
  
  INSERT INTO student(student_id,student_NAME,teacher_id) VALUES(1, '小明', '1');
  INSERT INTO student(student_id,student_NAME,teacher_id) VALUES(2, '小红', '1');
  INSERT INTO student(student_id,student_NAME,teacher_id) VALUES(3, '小张', '1');
  INSERT INTO student(student_id,student_NAME,teacher_id) VALUES(4, '小李', '1');
  INSERT INTO student(student_id,student_NAME,teacher_id) VALUES(5, '小王', '1');
  
  

10.2、环境搭建

• 导入lombok。

• 新建实体类Teacher，Student。

  • @NoArgsConstructor
    @AllArgsConstructor
    @Data
    @ToString
    public class Student {
        private int studentId;
        private String studentName;
        /*学生需要关联一个老师！*/
        private Teacher teacher;
    }
    

  • @NoArgsConstructor
    @AllArgsConstructor
    @Data
    @ToString
    public class Teacher {
        private Integer teacherId;
        private String teacherName;
    }
    

• 建立Mapper接口。

  • public interface StudentMapper {
    }
    

  • public interface TeacherMapper {
        Teacher getTeacher(@Param("teacherId") int teacherId);
    }
    

• 写*Mapper.xml。

  • <?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.jcooling.mapper.TeacherMapper">
         <select id="getTeacher" resultType="Teacher">
             select * from teacher where teacher_id= #{teacherId};
         </select>
    </mapper>
    

• 建立*Mapper.xml文件在核心配置文件中绑定注册我们的Mapper接口或者文件！

  • <!--每一个Mapper.XML都需要在Mybatis核心配置文件中注册！-->
        <mappers>
           <package name="com.jcooling.mapper"></package>
        </mappers>
    

• 测试查询是否能够成功！

  •  @Test
        public void testGetTeacher(){
            SqlSession sqlSession = MybatisUtils.getSqlSession();
            TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
            Teacher teacher = mapper.getTeacher(1);
            System.out.println(teacher);
        }
    

• 实体类属性使用驼峰命名,为了能使数据库字段与Java实体类属性一一映射,需要做一下配置。

  • <settings>
            <!--<setting name="logImpl" value="STDOUT_LOGGING"/>-->
            <setting name="mapUnderscoreToCamelCase" value="true"/>
     </settings>
    

10.3、按照查询嵌套处理

•   <!--思路：
      1.查询所有的学生信息。
      2.根据查询出来的学生的teacher_id，寻找对应的老师！-->
      <select id="getStudent" resultMap="StudentTeacher">
          select * from student
      </select>
      <resultMap id="StudentTeacher" type="Student">
          <!--<result property="studentId" column="student_id"></result>-->
          <!--<result property="studentName" column="student_name"></result>-->
          <!--复杂的属性，我们需要单独处理
          对象：association
          集合：colLection-->
          <association property="teacher" column="teacher_id" javaType="Teacher" select="getTeacher"></association>
      </resultMap>
    
      <select id="getTeacher" resultType="Teacher">
          select * from teacher where teacher_id=#{teacherId}
      </select>
  

10.4、按照结果嵌套处理

•  <!--按照结果嵌套处理-->
      <select id="getStudent2" resultMap="StudentTeacher2">
          select s.student_id sid,s.student_NAME sname,t.teacher_name tname from student s,teacher t where s.teacher_id=t.teacher_id;
      </select>
  <resultMap id="StudentTeacher2" type="Student">
      <result property="studentId" column="sid" ></result>
      <result property="studentName" column="sname" ></result>
      <association property="teacher" javaType="Teacher">
          <result property="teacherName" column="tname"></result>
      </association>
  </resultMap>
  

• 回顾Mysql

  • 多对一查询方式：
    • 子查询。
    • 联表查询。