Mybatis入门二（一对一映射的四种方式、一对多映射的两种方式）

一对一
一对多

一对一映射是指一个对象的成员变量中含有另一个对象的引用。这两个对象为关联关系。
映射的方式：通过自定义resultMap来映射
数据库中的数据表：
student表

mysql> select * from student;
+--------+--------+----------------+-------------+--------+
| stu_id | name   | email          | phone       | tea_id |
+--------+--------+----------------+-------------+--------+
|      1 | 张三   | asdu@163.com   | 1354641     |      3 |
|      2 | 李四   | askdja@163.com | 15286344    |      1 |
|      4 | lisi   | yz_ahha@qq.com | 15229219206 |      2 |
+--------+--------+----------------+-------------+--------+
3 rows in set (0.00 sec)

teacher表

mysql> select * from teacher;
+--------+-----------+-------------+
| tea_id | name      | tel         |
+--------+-----------+-------------+
|      1 | 杨老师    | 15229219206 |
|      2 | 宋老师    | 13201656250 |
|      3 | 李老师    | 13468667153 |
+--------+-----------+-------------+
3 rows in set (0.00 sec)

自定义的实体类中的属性：
Student的属性

private int stuId;
private String name;
private String email;
private String phone;
private Teacher tea;//Teacher类的引用 

Teacher类的属性

private  int teaId;
private String name;
private String tel;

1.通过引用.属性名的方式来映射关联对象的属性:

<resultMap id="studentWithTeacher" type="Student">
	  <id property="stuId" column="stu_id"/>
	  <result property="name" column="stu_name"/>
	  <result property="email" column="email"/>
	  <result property="phone" column="phone"/>
	  <result property="tea.teaId" column="tea_id"/>
	  <result property="tea.name" column="tea_name"/>
	  <result property="tea.tel" column="tel"/>
</resultMap>
<select id="selectStudentById" parameterType="int" resultMap="studentWithTeacher">
    select stu_id,student.name as stu_name,email,phone,teacher.tea_id,teacher.name as tea_name,tel
    from student
    left join teacher
    on student.tea_id=teacher.tea_id
    where stu_id=#{id}
</select>

在接口中定义方法后，在main中进行调用：

public class Main {
    public static void main(String[] args) throws Exception {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        System.out.println(studentMapper.selectStudentById(1));
    }
}

运行结果：

Student{stuId=1, name='张三', email='asdu@163.com', phone='1354641', tea=Teacher{tea_id=3, name='李老师', tel='13468667153'}}

2.定义两个resultMap，通过associate来关联：

<resultMap id="studentinfo" type="Student">
    <id property="stuId" column="stu_id"/>
    <result property="name" column="name"/>
    <result property="email" column="email"/>
    <result property="phone" column="phone"/>
    <association property="tea" resultMap="teacherinfo"/><!--在此进行关联-->
</resultMap>
<resultMap id="teacherinfo" type="teacher">
    <id property="teaId" column="tea_id"/>
    <result property="name" column="name"/>
    <result property="tel" column="tel"/>
</resultMap>
<select id="selectStudentById" parameterType="int" resultMap="studentinfo">
    select stu_id,student.name,email,phone,teacher.tea_id,teacher.name,tel
    from student inner join teacher
    on student.tea_id=teacher.tea_id
    where stu_id=#{id}
</select>

同样，在接口中定义方法，在main中运行，运行结果同上。
3.第二种方式属于外联，在一个resultMap的外部定义另外一个resultMap。第三种则是属于内联，在一个resultMap的内部定义另一个resultMap:

<resultMap id="studentbyinner" type="Student">
    <id property="stuId" column="stu_id"/>
    <result property="name" column="name"/>
    <result property="email" column="email"/>
    <result property="phone" column="phone"/>
    <association property="tea" javaType="Teacher"> <!--定义关联的对象的映射-->
        <id property="teaId" column="tea_id"/>
        <result property="name" column="name"/>
        <result property="tel" column="tel"/>
    </association>
</resultMap>
<select id="selectStudentById" parameterType="int" resultMap="studentbyinner">
    select stu_id,student.name,email,phone,teacher.tea_id,teacher.name,tel
    from student,teacher
    where student.tea_id=teacher.tea_id and stu_id=#{id}
</select>

4.第四种类似于嵌套查询：

<resultMap id="teacherinfo" type="teacher">
    <id property="teaId" column="tea_id"/>
    <result property="name" column="name"/>
    <result property="tel" column="tel"/>
</resultMap>
<select id="selectTeaById" parameterType="int" resultMap="teacherinfo">
    select * from teacher where tea_id=#{id}
</select>
<resultMap id="studentinfo" type="Student">
    <id property="stuId" column="stu_id"/>
    <result property="name" column="name"/>
    <result property="email" column="email"/>
    <result property="phone" column="phone"/>
    <association property="tea" column="tea_id" select="selectTeaById"/> <!--嵌套查询-->
</resultMap>
<select id="selectStudent" parameterType="int" resultMap="studentinfo">
    select * from student where stu_id=#{id}
</select>

根据tea_id进行查询。嵌套查询会先执行内层查询，然后将查询到的结果放在临时表中，然后通过临时表在进行外层查询。查询结束后会销毁临时表。因此效率相对较低，可以用连接查询来代替嵌套查询（子查询），也就是第二种方法，第二种相对第一种和第三种来说较为灵活。
补充
在mybatis_config.xml配置文件中遇到的坑：配置settings和typeAliases放的位置不对时，会报错。如果出现错误，尝试调整其位置。

<!--开启驼峰命名的映射，即从tea_id到teaId的映射-->
<settings >
    <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<!--定义类型别名，用可用Student来代替com.pojo.Studnet，可用teacher代替com.pojo.Teacher-->
<typeAliases>
    <typeAlias type="com.pojo.Student" alias="Student"/>
    <package name="com.pojo"/>
</typeAliases>

---

以上是一对一映射，接下来是一对多映射：一对多的写法类似于一对一的 第二种和 第四种。 首先，在原有的数据表中添加数据。一个老师可以带领多名学生。实体类老师中含有一个集合，集合中是学生实体类对象。老师类和学生类的这种关系被称为聚合。 接下来修改实体类：

teacher：

    private int teaId;
    private String name;
    private String tel;
    private List<Student> list;

student：

    private int stuId;
    private String name;
    private String email;
    private String phone;
    private int teaId;

映射文件第一种写法：

 <sql id="teaAndStu">
        teacher.tea_id,teacher.name as tname,tel,stu_id,student.name as sname,email,phone
    </sql>
    <resultMap id="teaWithStus" type="Teacher">
        <id property="teaId" column="tea_id"/>
        <result property="name" column="tname"/>
        <result property="tel" column="tel"/>
        <collection property="list" resultMap="stu"/>
    </resultMap>

    <resultMap id="stu" type="student">
        <id property="stuId" column="stu_id"/>
        <result property="name" column="sname"/>
        <result property="email" column="email"/>
        <result property="phone" column="phone"/>
        <result property="teaId" column="tea_id"/>
    </resultMap>

    <select id="selectTea" parameterType="int" resultMap="teaWithStus">
        select <include refid="teaAndStu"/>
        from teacher left join student
        on student.tea_id=teacher.tea_id
        where teacher.tea_id=#{id}
    </select>

通过内嵌结果来映射。
在数据库中查询的结果：

mysql> select teacher.tea_id,teacher.name,tel,stu_id,student.name,email,phone
    -> from student right join teacher
    -> on teacher.tea_id=student.tea_id
    -> where teacher.tea_id=2;
+--------+-----------+-------------+--------+------+----------------+-------------+
| tea_id | name      | tel         | stu_id | name | email          | phone       |
+--------+-----------+-------------+--------+------+----------------+-------------+
|      2 | 宋老师    | 13201656250 |      4 | lisi | yz_ahha@qq.com | 15229219206 |
|      2 | 宋老师    | 13201656250 |      5 | swj  | swj@163.com    | 132016562xx |
|      2 | 宋老师    | 13201656250 |      6 | mmq  | 123@qq.com     | 136987456   |
|      2 | 宋老师    | 13201656250 |      7 | lfl  | 456@163.com    | 178888880   |
+--------+-----------+-------------+--------+------+----------------+-------------+
4 rows in set (0.01 sec)

main方法：

TeacherMapper teacherMapper=sqlSession.getMapper(TeacherMapper.class);
Teacher teacher=teacherMapper.selectTea(2);
List<Student> list=teacher.getList();
for(Student stu:list)
{
    System.out.println(stu);
}

运行结果：

Student{stuId=4, name='lisi', email='yz_ahha@qq.com', phone='15229219206', teaId=2}
Student{stuId=5, name='swj', email='swj@163.com', phone='132016562xx', teaId=2}
Student{stuId=6, name='mmq', email='123@qq.com', phone='136987456', teaId=2}
Student{stuId=7, name='lfl', email='456@163.com', phone='178888880', teaId=2}

同理，也可以通过嵌套查询来过的：
映射文件：

<resultMap id="teaWithStus" type="Teacher">
    <id property="teaId" column="tea_id"/>
    <result property="name" column="tname"/>
    <result property="tel" column="tel"/>
    <collection property="list" column="tea_id" select="selectStu"/><!--进行内层查询，tea_id是参数-->
</resultMap>

<!--内层查询-->
<resultMap id="stu" type="student">
    <id property="stuId" column="stu_id"/>
    <result property="name" column="sname"/>
    <result property="email" column="email"/>
    <result property="phone" column="phone"/>
    <result property="teaId" column="tea_id"/>
</resultMap>
<select id="selectStu" parameterType="int" resultMap="stu">
    select stu_id,name as sname,email,phone,tea_id from student where tea_id=#{id}
</select>

<!--外层查询-->
<select id="selectTea" parameterType="int" resultMap="teaWithStus">
    select tea_id,name as tname,tel from teacher where tea_id=#{id}
</select>

总结：嵌套查询虽然效率低，但是其sql语句简单易懂；连接查询虽然效率较高，但是其sql语句较长。

上一篇

---The End---

下一篇