MyBatis学习（三）

一对多和多对一处理

数据库设计

CREATE TABLE `mybatis`.`teacher` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

INSERT INTO teacher(name) VALUES ('秦老师');
INSERT INTO teacher(name) VALUES ('王老师');


CREATE TABLE `mybatis`.`student` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL DEFAULT NULL,
  `tid` INT NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

核心配置文件

核心配置文件添加mapper

    <mappers>
        <!--mappers配置方式一-->
        <mapper class="com.wangqi.dao.TeacherMapper"/>
        <mapper class="com.wangqi.dao.StudentMapper"/>
    </mappers>

多对一处理

bean文件（省略get,set,constuctor,tostring函数）

Teacher.java

package com.wangqi.bean;


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

Student.java

package com.wangqi.bean;

public class Student {
    private int id;
    private String name;
    private Teacher teacher;
}

dao接口

StudentMapper.java

package com.wangqi.dao;

import com.wangqi.bean.Student;

import java.util.List;

public interface StudentMapper {

    List<Student> getStudentList();
}

解决方式

StudentMapper .xml

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

<mapper namespace="com.wangqi.dao.StudentMapper">

    <!--方法一-->
<!--    <select id="getStudentList" resultMap="StudentTeacher">
        select * from student;
    </select>
    <resultMap id="StudentTeacher" type="com.wangqi.bean.Student">
        &lt;!&ndash;association关联属性 property属性名 javaType属性类型 column在多的一方的表中的列名&ndash;&gt;
        <association property="teacher"  column="tid" javaType="com.wangqi.bean.Teacher" select="getTeacher"/>
    </resultMap>

    <select id="getTeacher" resultType="com.wangqi.bean.Teacher">
        select * from teacher where id = #{id}
    </select>-->

    <!--方法二-->
    <select id="getStudentList" resultMap="StudentTeacher" >
        select s.id sid, s.name sname, s.tid stid, t.name tname
        from student s,teacher t
        where s.tid = t.id
    </select>

    <resultMap id="StudentTeacher" type="com.wangqi.bean.Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <!--关联对象property 关联对象在Student实体类中的属性-->
        <association property="teacher" javaType="com.wangqi.bean.Teacher">
            <result property="id" column="stid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>

</mapper>

一对多处理

bean文件（省略get,set,constuctor,tostring函数）

Student.java

package com.wangqi.bean;

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

Teacher.java

package com.wangqi.bean;


import java.util.List;

public class Teacher {
    private int id;
    private String name;
    private List<Student> studentList;

dao接口

TeacherMapper.java

package com.wangqi.dao;

import com.wangqi.bean.Teacher;
import org.apache.ibatis.annotations.Param;


public interface TeacherMapper {

    Teacher getTeacher(@Param("tid") int id);
}

解决方式

TeacherMapper.xml

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

<mapper namespace="com.wangqi.dao.TeacherMapper">
    <select id="getTeacher" resultMap="getTeacherMap">
        select t.id tid, t.name tname, s.id sid, s.name sname
        from student s, teacher t
        where t.id = s.tid and t.id = #{tid}
    </select>

    <resultMap id="getTeacherMap" type="com.wangqi.bean.Teacher">
        <result column="tid" property="id"/>
        <result column="tname" property="name"/>
        <collection property="studentList" ofType="com.wangqi.bean.Student">
            <result column="sid" property="id"/>
            <result column="sname" property="name"/>
            <result column="tid" property="tid"/>
        </collection>
    </resultMap>
</mapper>

总结

在用的时候感觉联合查询明显比嵌套查询清晰多了，还方便调试。
注意 assosition 和 collection 标签的区别。
一个是关联对象，一个是容器类型。