在MyBatis中,如果我们想对一对一或者一对多的多表进行查询,该如何处理呢?
MyBatis提供了下面两个标签来处理一对一、多对一、一对多的映射关系:
association: 处理一对一、多对一
collection: 处理一对多
一对一
每个人都有身份证,人和身份证的关系是一对一的。假设我们有下面两个class:
public class Card implements Serializable {
private Integer id;
private String code;
// 省略get、set方法
}
public class User implements Serializable {
private Integer id;
private String name;
private Integer age;
private Card card;
//省略get、set方法
}
下面是Mapper和接口的实现:
package com.yrk.mapper;
import com.yrk.pojo.Card;
public interface CardMapper {
Card selectCardById(Integer id);
}
<?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.yrk.mapper.CardMapper">
<select id="selectCardById" parameterType="int" resultType="com.yrk.pojo.Card">
select * from tb_card where id = #{id}
</select>
</mapper>
package com.yrk.mapper;
import com.yrk.pojo.Person;
public interface PersonMapper {
Person selectPersonById(Integer id);
}
<?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.yrk.mapper.PersonMapper">
<resultMap type="com.yrk.pojo.Person" id="personMapper">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<association property="card" column="card_id"
select="com.yrk.mapper.CardMapper.selectCardById"
javaType="com.yrk.pojo.Card">
</association>
</resultMap>
<select id="selectPersonById" parameterType="int" resultMap="personMapper">
select * from tb_person where id = #{id}
</select>
</mapper>
一对多
假设我们有班级和学生两个实体类,一个班级有多个学生,一个学生只会在一个班级:
package com.yrk.pojo;
import java.io.Serializable;
import java.util.List;
public class Clazz implements Serializable{
private Integer id;
private String code;
private String name;
//班级与学生是一对多的关系
private List<Student> students;
//省略set/get方法
}
public class Student implements Serializable {
private Integer id;
private String name;
private Clazz clazz;
//省略set/get方法
}
班级和学生是一对多的关系,在MyBatis中我们是用collection来实现一对多的映射:
<mapper namespace="com.yrk.mapper.ClazzMapper">
<select id="selectClazzById" parameterType="int" resultMap="clazzResultMap">
select * from tb_clazz where id = #{id}
</select>
<resultMap type="com.yrk.pojo.Clazz" id="clazzResultMap">
<id property="id" column="id"/>
<result property="code" column="code"/>
<result property="name" column="name"/>
<!-- property: 指的是集合属性的值, ofType:指的是集合中元素的类型 -->
<collection property="students" ofType="com.yrk.pojo.Student"
column="id" javaType="ArrayList"
fetchType="lazy" select="com.yrk.mapper.StudentMapper.selectStudentByClazzId">
<id property="id" column="id"/>
<result property="name" column="name"/>
</collection>
</resultMap>
</mapper>
package com.yrk.mapper;
import com.yrk.pojo.Clazz;
public interface ClazzMapper {
Clazz selectClazzById(Integer id);
}
<mapper namespace="com.yrk.mapper.StudentMapper">
<select id="selectStudentById" parameterType="int" resultMap="studentResultMap">
select * from tb_clazz c,tb_student s where c.id = s.id and s.id = #{id}
</select>
<select id="selectStudentByClazzId" parameterType="int" resultMap="studentResultMap">
select * from tb_student where clazz_id = #{id}
</select>
<resultMap type="com.yrk.pojo.Student" id="studentResultMap">
<id property="id" column="id"/>
<result property="name" column="name"/>
<association property="clazz" javaType="com.yrk.pojo.Clazz">
<id property="id" column="id"/>
<result property="code" column="code"/>
<result property="name" column="name"/>
</association>
</resultMap>
</mapper>
package com.yrk.mapper;
import com.yrk.pojo.Student;
public interface StudentMapper {
Student selectStudentById(Integer id);
}