mybatis关系
关系应用
需要解决的问题:
- 怎么使用数据库表设计表示数据之间的关系
- 怎么使用java类设计来表示对象之间的关系
- 怎么通过mybatis来映射上面两者
关联关系分类
导航性:通过A对象的某一个属性可以访问到B,就说A可以导航到B
- 单向:通过A属性导航到B,B不能导航到B
- 双向:A,B可以互相导航
多重性:
- 一多一
- 一对多
- 多对一
- 多对多
实现
1.明确需求
2.表设计
3.实体类设计
一般看需求,决定关联书记邢定义睡那边,什么类型
4.Mapper接口和Mapper.xml
单向多对一
多个员工对应一个部门
需求:根据员工查询员工,并知道该员工所在部门
- 定义好类Employee, Department
@Data
public class Employee {
private Long id;
private String name;
private Department dept; //关联属性}
---------------------------------------------------
@Data
public class Department {
private Long id;
private String name;}
- 定义EmployeeMapper,DepartmentMapper接口方法
public interface EmployeeMapper {
Employee get(Long id);}
public interface DepartmentMapper {
Department get(Long id);}
- EmployeeMapper.xml
<resultMap id="baseResultMap" type="Employee">
<!--自定义封装规则id主键,result非主键-->
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="dept_id" property="dept.id"/>
<!--告诉mybatis 帮我们发送sql 封装数据
select 指定发送什么sql
column 发送SQL的参数值来源于上一条sql执行结果的哪个列的值
property 指定封装到员工对象的哪个属性
-->
<association select="cn.k.mapper.DepartmentMapper.get" column="dept_id" property="dept"/>
</resultMap>
<select id="get" resultMap="baseResultMap">
SELECT id, name, dept_id FROM employee WHERE id = #{id}
</select>
- DepartmentMapper.xml
<select id="get" resultType="cn.k.domain.Department">
SELECT id, name FROM department WHERE id = #{id}
</select>
返回值不使用resultTpye的原因:数据库表中并没有dept这个列,dept是部门表封装的对象,使用resultType是告诉mybatis用什么类型对象来封装查询的数据,结果集的列名要与对象属性名一直,类型也要匹配
所以,当查询的结果集的列名与封装对象属性名不一致的时候,使用resultMap来解决
使用association可以帮我们发送额外sql,用于关联属性非集合类型
Collection ,用于关联属性集合类型
- 测试类
//手动
不使用association加入额外sql
@Test
public void get1(){
SqlSession session = MyBatisUtil.getSession();
DepartmentMapper departmentMapper = session.getMapper(DepartmentMapper.class);
EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
Employee employee = employeeMapper.get1(1L);
//1.先获取员工部门的id
Long deptId = employee.getDept().getId();
//发送sql,拿到这个员工id去查询对应的部门,利用mybatis帮我们封装成一个部门对象
Department dept = departmentMapper.get(deptId);
System.out.println(dept);
//这个部门对象,设置员工对象的dept部门属性上
employee.setDept(dept);
System.out.println(employee);
session.close();
}
//自动,mybatis帮我们封装,使用association
@Test
public void get() {
SqlSession session = MyBatisUtil.getSession();
EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
Employee employee = employeeMapper.get(1L);
System.out.println(employee);
session.close();
}
补充: 查询: 额外SQL,在resultMap配置
<association select="命名空间.SQLid值" column="上一条sql执行结果的哪个列的值" property="指定封装到员工对象的哪个属性"/>
额外sql查询N+1问题
如果要查询所有员工以及其部门,那么在查询时,会发送N+1条语句,这样大大降低了查询的速度与性能
改进:写查询所有对应表的sql
单向一对多
从部门角度,一个部门对应多个员工
需求:根据部门id,来查询好对应部门id的员工信息
- 定义实体类,找出一对多的关系
public class Department {
private Long id;
private String name;
//关联属性 几何 对象直接new 畜类,能够避免空指针
private List<Employee> employees=new ArrayList<>();
}
----------------------------------------------------------------
public class Employee {
private Long id;
private String name;
private Long deptId; //该属性用来封装员工部门的id值,不是关联属性
}
- 定义mapper接口
public interface DepartmentMapper {
Department get(Long id);}
---------------------------------------------
public interface EmployeeMapper {
List<Employee> queryByDeptId(Long id);}
- mapper.xml
<resultMap id="baseResultMap" type="Department">
<id column="id" property="id"/>
<result column="name" property="name"/>
<collection select="cn.k.mapper.EmployeeMapper.queryByDeptId" column="id" property="employees"/>
</resultMap>
<select id="get" resultMap="baseResultMap">
select id, name
from department
where id = #{id}
</select>
-------------------------------------------------------------------
<select id="queryByDeptId" resultType="Employee">
select id,name,dept_id deptId from employee where dept_id=#{deptId}
</select>
- 测试类
@Test
public void testGet(){
SqlSession session = MyBatisUtil.getSession();
DepartmentMapper departmentMapper = session.getMapper(DepartmentMapper.class);
EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
Department department = departmentMapper.get(1L);
System.out.println(department);
/*Long departmentId = department.getId();
List<Employee> employee = employeeMapper.queryByDeptId(departmentId);
department.setEmployees(employee);
System.out.println(department);*/
session.close();
}
单向多对多
需求:根据id查询学生,并查询老师
多对多关系,需要建立中间表
- 定义实体类,并找出关联关系
@Data
public class Student {
private Long id;
private String name;
//关联属性
//需求:根据id查询学生并查询其老师
private List<Teacher> teachers =new ArrayList<>();
}
-------------------------------------------------------
@Data
public class Teacher {
private Long id;
private String name;
}
- 定义StudentMapper,TeacherMapper接口
public interface StudentMapper {
//保存
void save(Student student);
//保存关系数据
void insertRelation(@Param("teacherId") Long teacherId, @Param("studentId") Long studentId);
//查询
Student get(Long id);
}
--------------------------------------------------------
public interface TeacherMapper {
//保存
void save(Teacher teacher);
}
- Mapper.xml
StudentMapper.xml
<mapper namespace="cn.k.mapper.StudentMapper">
<insert id="save" useGeneratedKeys="true" keyProperty="id">
insert into student(name)
values (#{name})
</insert>
<insert id="insertRelation">
insert into teacher_student(student_id, teacher_id)
values (#{studentId}, #{teacherId})
</insert>
<resultMap id="bashResultMap" type="Student">
<id column="id" property="id"/>
<result column="name" property="name"/>
<!--关联属性,让mybatis额外发送sql-->
<collection select="cn.k.mapper.TeacherMapper.queryByStudentId"
property="teachers" column="id"/>
</resultMap>
<select id="get" resultMap="bashResultMap">
select id, name
from student
where id = #{id}
</select>
</mapper>
--------------------------------------------------------
TeacherMapper.xml
<mapper namespace="cn.k.mapper.TeacherMapper">
<insert id="save" useGeneratedKeys="true" keyProperty="id">
insert into teacher (name) values (#{name})
</insert>
<select id="queryByStudentId" resultType="Teacher">
select *from teacher_student ts join teacher t on ts.teacher_id = t.id where ts.student_id=#{studentId}
</select>
</mapper>
useGeneratedKeys=true 获取数据库保存数据的的主键值 keyProperty=“id” 主键设置设置对象的 id 属性
- 测试类
@Test
public void testSave() {
SqlSession session = MyBatisUtil.getSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
TeacherMapper teacherMapper = session.getMapper(TeacherMapper.class);
Teacher t1 = new Teacher();
t1.setName("貂蝉老师");
Teacher t2 = new Teacher();
t2.setName("大乔老师");
//设置
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);
//添加
teacherMapper.save(t1);
teacherMapper.save(t2);
studentMapper.save(s1);
studentMapper.save(s2);
//往中间表添加学生与老师间的关系
for (Teacher teacher:s1.getTeachers()) { studentMapper.insertRelation(teacher.getId(),s1.getId()); }
for (Teacher teacher:s2.getTeachers()) { studentMapper.insertRelation(teacher.getId(),s2.getId());}
session.commit();
session.close();
}
@Test //根据学生id得到了学生的对应老师是谁
public void testGet() {
SqlSession session = MyBatisUtil.getSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student student = studentMapper.get(4L);
System.out.println(student);
session.close();
}
多对多删除的删除要注意的是,删除数据的同时,需要把对应关系也删除掉 ,硬删除执行delete语句,软删除 执行update语句