一.多对一
案例:多个员工对象同属于同个部门对象
模型对象设计
表设计:
1.保存操作
DepartmentMapper.xml
<!-- 添加 -->
<insert id="save" useGeneratedKeys="true" keyProperty="id" >
insert into department (name) values (#{name})
</insert>
EmployeeMapper.xml
<!-- 添加 -->
<insert id="save" useGeneratedKeys="true" keyProperty="id" >
insert into employee_new (name,dept_id) values (#{name},#{dept.id})
</insert>
测试代码
Department department = new Department();
department.setName("开发部");
Employee user1 = new Employee();
user1.setName("王小子2");
user1.setDept(department);//维护对象关系
Employee user2 = new Employee();
user2.setName("兰兰");
user2.setDept(department);//维护对象关系
SqlSession session = MybatisUtil.getSession();
DepartmentMapper departmentMapper = session.getMapper(DepartmentMapper.class);
EmployeeMapper userMapper = session.getMapper(EmployeeMapper.class);
departmentMapper.save(department);
userMapper.save(user1);
userMapper.save(user2);
//提交事务
session.commit();
session.close();
System.out.println(department);
System.out.println(user1);
System.out.println(user2);
2.内联映射
association元素:
property属性:关联对象属性名
javaType属性:关联对象属性类型
多表连接查询的SQL:
<select id="get" resultMap="BaseResultMap" >
select e.id,e.name,d.id as d_id,d.name as d_name from employee_new e join department d on e.dept_id = d.id where e.id = #{id}
</select>
方式一,使用级联方式来封装对象(不用)。
<resultMap id="BaseResultMap" type="Employee" >
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="d_id" property="dept.id"/>
<result column="d_name" property="dept.name"/>
</resultMap>
方式二,使用association元素(常用)
<resultMap id="BaseResultMap" type="Employee" >
<id column="id" property="id"/>
<result column="name" property="name"/>
<!-- 处理关联对象 -->
<!-- 方法一:额外SQL语句 -->
<!-- 方法二:内联关系
<result column="d_id" property="dept.id"/>
<result column="d_name" property="dept.name"/>
-->
<!-- 方法三
可以给association添加属性columnPrefix="d_",下边字段可以统一少写"d_"
-->
<association property="dept" javaType="Department" >
<id column="d_id" property="id"/>
<result column="d_name" property="name"/>
</association>
</resultMap>
3.额外SQL
association元素:
select属性:发送的额外SQL语句
column属性:将指定列的值传递给额外SQL
<resultMap id="BaseResultMap" type="Employee" >
<id column="id" property="id"/>
<result column="name" property="name"/>
<association property="dept"
select="com.bigfong.mybatis.many2one.mapper.EmployeeMapper.get"
column="dept_id">
</association>
</resultMap>
使用额外SQL语句,在查询多对一下,会产生N+1问题
内联映射和额外SQL的选择
在开发中,多对一的关系,一般的都是在列表中显示,通常直播使用多表查询,也就是内联查询处理
如果在当前页面不显示数据,需要进入另一个页面才显示的数据,此时选用额外SQL方式
二.一对多
模型对象设计:
表设计(外键在many方)
1.额外SQL
DepartmentMapper.xml
<resultMap type="Department" id="BaseResultMap">
<id column="id" property="id"/>
<result column="name" property="name"/>
<!-- 针对单一对象的属性,使用association -->
<!-- 针对集合类型的属性,使用collection,这里:List<Employee> emps -->
<!--
ofType:表示集合中泛型的类型
-->
<collection property="emps"
ofType="Employee"
select="com.bigfong.mybatis.one2many.mapper.EmployeeMapper.selectByDeptId"
column="id"
/>
</resultMap>
<select id="get" resultMap="BaseResultMap">
SELECT id,name FROM department WHERE id = #{id}
</select>
EmployeeMapper.xml
<select id="selectByDeptId" resultType="Employee">
select id,name,dept_id as deptId from employee_new where dept_id = #{deptId}
</select>
2.内联映射
使用一条语句查询出部门和该部门对应的咒工(一般不用)
<resultMap type="Department" id="BaseResultMap2">
<id column="id" property="id"/>
<result column="name" property="name"/>
<!-- 针对单一对象的属性,使用association -->
<!-- 针对集合类型的属性,使用collection,这里:List<Employee> emps
<collection property="emps"
ofType="Employee"
select="com.bigfong.mybatis.one2many.mapper.EmployeeMapper.selectByDeptId"
column="id"
/>-->
<collection property="emps" ofType="Employee">
<id column="e_id" property="id"/>
<result column="e_name" property="name"/>
<result column="id" property="deptId"/>
</collection>
</resultMap>
内联映射和额外SQL的选择:
内联映射:使用多表查询,一次性查询出所有数据,在列表中一起显示的数据
额外SQL: 分步查询出所有数据,在另一个页面单独显示的数据
三.延迟加载
配置细节:
1.Mybatis缺省情况下,禁用了延迟加载
2.Mybatis会很积极地去查询关联对象
3.Mybatis中缺省情况下,调用equals,clone,hashCode,toString都会触发延迟加载,一般我们保留clone就可以了,也就是说调用many方对象的toString,hashCode,equals方法依然不会去发送查询one方的SQL
mybatis-config.xml中的配置
<settings>
<!-- 开启延迟加载 对于关联对象查询为: 额外SQL的配置方式 有效 -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 设置不要积极的去查询关联对象 -->
<setting name="aggressiveLazyLoading" value="false"/>
<!-- 延迟加载的触发的方法 -->
<setting name="lazyLoadTriggerMethods" value="clone"/>
</settings>
四.关联对象配置选择
在开发中,
1)针对单属性对象,使用association元素,通常直接 使用多表查询操作,即使用内联处理
2)针对集合属性对象,使用collection元素,通常使用延迟加载,即额外SQL查询处理
五.多对多
一个A对象属于多个B对象,一个B对象属性多个A对象(单向关系)
模型对象设计
表设计:
中间表的主键设计:
方式一.中间表不设置主键
方式二.把student_id和teach_id列设计为联合主键
1.保存操作
因为存在中间表的缘故,所以必须发送额外的SQL去维护中间表的关系
StudentMapper.xml:
<insert id="insertRelationWithTeacher">
insert into student_teacher (student_id,teacher_id) values (#{studentId},#{teacherId})
</insert>
测试代码:
Teacher t1 = new Teacher();
t1.setName("老师1");
Teacher t2 = new Teacher();
t2.setName("老师2");
Student s1 = new Student();
s1.setName("小七");
Student s2 = new Student();
s2.setName("花花");
//维护对象之间关系
s1.getTeachers().add(t1);
s1.getTeachers().add(t2);
s2.getTeachers().add(t1);
s2.getTeachers().add(t2);
SqlSession session = MybatisUtil.getSession();
TeacherMapper teacherMapper = session.getMapper(TeacherMapper.class);
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
teacherMapper.save(t1);
teacherMapper.save(t2);
studentMapper.save(s1);
studentMapper.save(s2);
//维护学生和老师关系的中间表的数据
for(Teacher t:s1.getTeachers()) {
studentMapper.insertRelationWithTeacher(s1.getId(), t.getId());
}
for(Teacher t:s2.getTeachers()) {
studentMapper.insertRelationWithTeacher(s2.getId(), t.getId());
}
//提交事务
session.commit();
session.close();
2.查询操作
因为此时teahcers属性是集合类型,所以使用额外SQL是合理的,使用内联查询时不合理
StudentMapper.xml:
<resultMap type="Student" id="BaseResultMap">
<id column="id" property="id"/>
<result column="name" property="name"/>
<collection property="teachers" ofType="Teacher"
select="com.bigfong.mybatis.many2many.mapper.TeacherMapper.selectByStudentId"
column="id"
/>
</resultMap>
<select id="get" resultMap="BaseResultMap">
select id,name from student where id = #{id}
</select>
TeacherMapper.xml:
<select id="selectByStudentId" resultType="Teacher">
select t.id,t.name from teacher t join student_teacher st on t.id=st.teacher_id where st.student_id = #{id}
</select>
3.删除操作之前,必须先删除中间表中关联的数据
StudentMapper.xml
<delete id="delete">
delete from student where id =#{id}
</delete>
<delete id="deleteRelationWithTeacher">
delete from student_teacher where student_id =#{studentId}
</delete>
下一篇: MyBatis3.x整理:(五)缓存机制