Mybatis many2one,one2Many和many2many增删改操作

叁歲伎倆 2022-03-18 12:48 278阅读 0赞

> **一、many2one（保存时，关系交给many方来进行处理）**

1.实体类（实体类属性满足驼峰匹配的话，如userName,resultMap中添加autoMapping='true',则里面只需要写id映射，其余的挥动匹配映射）

public class Department {
        private Long id;
        private String name;
    }
    public class Employee {
        private long id ;
        private String name;
        private Department dept;// 数据库中外键为dept_id
    }

2.Dao接口类

public interface IDepartmentMapper {
        void save(Department department);
        Department get(Long id);
    }
    public interface IEmployeeMapper {
        void save(Employee employee);
        Employee get(Long id);
        List<Employee> getList();
    }

3.XML（DepartmentMapper.xml和EmployeeMapper.xml）

1.在查询所有many记录,每个many对应的one都是不同,查询N个many对象,总共会发出N+1条SQL.(性能特别不友好) 使用leftJoin的方式来解决.

<?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.shenzhenair.mybatis.demo_02.many2one.IEmployeeMapper" >
    
        <resultMap id="base_employeeMap" type="Employee"  autoMapping="true" >
            <id column="id" property="id" ></id>
            <result column="name" property="name" ></result>
            
            <association property="dept" javaType="Department" columnPrefix="dept_" autoMapping="true"  >
                <id column="id" property="id"></id>
                <result column="name" property="name"></result>
            </association>
    
        </resultMap>
    
        <insert id="save" parameterType="Employee" keyColumn="id" keyProperty="id" useGeneratedKeys="true" >
            INSERT INTO employee (name,dept_id) VALUES (#{name},#{dept.id})
        </insert>
    
        <select id="get" parameterType="java.lang.Long" resultMap="base_employeeMap">
            SELECT id,name,dept_id from employee where id = #{id}
        </select>
    
        <select id="getList"  resultMap="base_employeeMap">
           /* SELECT id,name,dept_id from employee*/
           SELECT e.id,e.name,d.id AS dept_id,d.name AS dept_name FROM employee e LEFT JOIN
            department d ON e.dept_id = d.id
        </select>
    
    
    </mapper>

4.测试类

public class TestMany2One {
        @Test
        public void testSave(){
            SqlSession session = MybatisUtils.openSession();
            IDepartmentMapper deptMapper = session.getMapper(IDepartmentMapper.class);
            IEmployeeMapper empMapper = session.getMapper(IEmployeeMapper.class);
    
            Department department = new Department("IT部门");
            Employee employee = new Employee("tom");
            employee.setDept(department);
    
            // 先保存one方，再保存many方
            deptMapper.save(department);
            empMapper.save(employee);
    
            session.commit();
            session.close();
    
        }
        @Test
        public void testGetEmployee(){
            SqlSession session = MybatisUtils.openSession();
            IEmployeeMapper empMapper = session.getMapper(IEmployeeMapper.class);
    
            Employee employee = empMapper.get(1l);
            Department dept = employee.getDept();
            System.out.println(employee);
            System.out.println(dept);
    
            session.commit();
            session.close();
    
        }
    
    
        @Test
        public void testGetEmployeeList(){
            SqlSession session = MybatisUtils.openSession();
            IEmployeeMapper empMapper = session.getMapper(IEmployeeMapper.class);
    
            List<Employee> employee = empMapper.getList();
            for (Employee employee1 : employee) {
                System.out.println(employee1);
            }
            session.commit();
            session.close();
    
        }
    
    
        @Test
        public void testGetDepartment(){
            SqlSession session = MybatisUtils.openSession();
            IDepartmentMapper deptMapper = session.getMapper(IDepartmentMapper.class);
    
            Department department = deptMapper.get(2L);
            System.out.println(department);
    
            session.commit();
            session.close();
    
        }
    }

> **2.One2Many（one方要处理外键关系）**

1.实体类

public class Department {
        private Long id;
        private String name;
        private List<Employee> employees = new ArrayList<>();
    }
    public class Employee {
        private long id ;
        private String name;
    }

2.Dao接口

public interface IDepartmentMapper {
    
        void save(Department department);
    
        Department get(Long id);
    
        void handlerRelaotion(@Param("dept_id") Long deptID, @Param("emp_id") long empID);
    
        void  delete(Long id);
    
    }
    
    public interface IEmployeeMapper {
    
        void save(Employee employee);
    
        Employee get(Long id);
    
        Employee selectByDeptID(Long dept_id);
    
        void updateRelation(Long id);
    
    }

3.XML(DepartmentMapper.xml, EmployeeMapper.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.shenzhenair.mybatis.demo_02.one2many.IDepartmentMapper">
    
        <resultMap id="basic_map" type="Department">
            <id column="id" property="id" ></id>
            <result column="name" property="name" ></result>
            
            <collection    property="employees"  ofType="Employee" columnPrefix="emp_" >
                 <id column="id" property="id" ></id>
                <result column="name" property="name"></result>
            </collection>
    
        </resultMap>
    
       
        <select id="get" parameterType="java.lang.Long" resultMap="basic_map">
            SELECT d.id as id,d.name as name ,e.id as emp_id,e.name as emp_name from department d LEFT  JOIN  employee e on d.id=e.dept_id   WHERE  d.id = #{id}
        </select>
    
        <insert id="save" parameterType="Department" keyColumn="id" keyProperty="id" useGeneratedKeys="true" >
            INSERT INTO department (name) VALUES (#{name})
        </insert>
    
        <update id="handlerRelaotion" parameterType="java.lang.Long" >
            UPDATE  employee set dept_id=#{dept_id} WHERE  id = #{emp_id}
        </update>
    
        <delete id="delete">
            DELETE  FROM  department where id=#{id}
        </delete>
    
    
    
    </mapper>

4.测试类

public class TestOne2Many {
        @Test
        public void testSave(){
            SqlSession session = MybatisUtils.openSession();
            IDepartmentMapper deptMapper = session.getMapper(IDepartmentMapper.class);
            IEmployeeMapper empMapper = session.getMapper(IEmployeeMapper.class);
    
            Employee employee1 = new Employee("e1");
            Employee employee2 = new Employee("e2");
    
            Department department = new Department("dept1");
            department.getEmployees().add(employee1);
            department.getEmployees().add(employee2);
    
            deptMapper.save(department);
            empMapper.save(employee1);
            empMapper.save(employee2);
    
            // 需要one方处理外键的关系
            List<Employee> employees = department.getEmployees();
            for (Employee employee : employees) {
                deptMapper.handlerRelaotion(department.getId(),employee.getId());
            }
    
            session.commit();
            session.close();
    
        }
        @Test
        public void testGet(){
            SqlSession session = MybatisUtils.openSession();
            IDepartmentMapper deptMapper = session.getMapper(IDepartmentMapper.class);
            Department department = deptMapper.get(6L);
            System.out.println(department);
            List<Employee> employees = department.getEmployees();
            for (Employee employee : employees) {
                System.out.println(employee);
            }
            session.commit();
            session.close();
    
        }
    
    
    
        @Test
        public void testDeleteEmployee(){
            SqlSession session = MybatisUtils.openSession();
            IDepartmentMapper departmentMapper = session.getMapper(IDepartmentMapper.class);
            IEmployeeMapper employeeMapper = session.getMapper(IEmployeeMapper.class);
            // 打破外键约束关系
            Department dept = departmentMapper.get(6L);
            /*List<Employee> employees = dept.getEmployees();
            for (Employee employee : employees) {
                employeeMapper.updateRelation(employee.getId());
            }*/
            employeeMapper.updateRelation(dept.getId());
            departmentMapper.delete(6L);
    
            session.commit();
            session.close();
    
        }
    
    
    }

> 3.Many2Many

1. 实体类：

public class Student {
        private Long id;
        private String name;
    }
    public class Teacher {
        private Long id;
        private String name;
        private List<Student> stus = new ArrayList<>();
    }

2.Dao接口

public interface IStudentMapper {
        void save(Student student);
        Student get(Long id);
    }
    
    public interface ITeacherMapper {
        void save(Teacher teacher);
        Teacher getTeacher(Long id);
        Long handlerRelation (@Param("teaId") Long teaId, @Param("stuId") Long stuId);
    }

3.XML(StudentMapper.xml   TeacherMapper.xml)

<mapper namespace="com.shenzhenair.mybatis.demo_02.many2many.IStudentMapper">
    
        <select id="get" parameterType="java.lang.Long" resultType="Student">
          select id,name from student where id = #{id}
        </select>
    
        <insert id="save" parameterType="Student" keyColumn="id" keyProperty="id" useGeneratedKeys="true" >
            INSERT INTO student (name) VALUES (#{name})
        </insert>
    
    </mapper>

<mapper namespace="com.shenzhenair.mybatis.demo_02.many2many.ITeacherMapper" >
    
        <insert id="save" parameterType="Teacher" keyColumn="id" keyProperty="id" useGeneratedKeys="true" >
            INSERT INTO teacher (name) VALUES (#{name})
        </insert>
    
        <resultMap id="basic_map" type="Teacher">
            <id column="id" property="id"></id>
            <result column="name" property="name" ></result>
            
            <collection property="stus" ofType="Student" columnPrefix="stu_">
                <id property="id" column="id" ></id>
                <result property="name" column="name"></result>
            </collection>
        </resultMap>
        <select id="getTeacher" parameterType="java.lang.Long" resultMap="basic_map">
            SELECT t.id as id,t.NAME as name,s.id AS stu_id,s.NAME AS stu_name FROM teacher t
            LEFT JOIN tea_stu ts ON t.id = ts.t_id LEFT JOIN student s ON ts.s_id = s.id
            WHERE t.id =#{id}
        </select>
        
        <insert id="handlerRelation">
            INSERT  INTO tea_stu (s_id,t_id) VAsLUES (#{stuId},#{teaId})
        </insert>
    </mapper>

4.测试类

public class TestMany2Many {
        @Test
        public void testSave(){
            SqlSession session = MybatisUtils.openSession();
            IStudentMapper stuMapper = session.getMapper(IStudentMapper.class);
            ITeacherMapper teaMapper = session.getMapper(ITeacherMapper.class);
    
            Teacher t1 = new Teacher();
            t1.setName("t1");
            Teacher t2 =  new Teacher();
            t2.setName("t2");
    
            Student stu1 = new Student();
            stu1.setName("s1");
            Student stu2 = new Student();
            stu2.setName("s2");
    
            t1.getStus().add(stu1);
            t1.getStus().add(stu2);
            t2.getStus().add(stu1);
            t2.getStus().add(stu2);
    
            teaMapper.save(t1);
            teaMapper.save(t2);
            stuMapper.save(stu1);
            stuMapper.save(stu2);
    
            // 需要处理中间表的关系
           List<Student> stus1 =  t1.getStus();
            for (Student student : stus1) {
                teaMapper.handlerRelation(t1.getId(),student.getId());
            }
           List<Student> stus2 =  t1.getStus();
            for (Student student : stus2) {
                teaMapper.handlerRelation(t2.getId(),student.getId());
            }
    
    
            session.commit();
            session.close();
    
        }
        @Test
        public void testGet(){
            SqlSession session = MybatisUtils.openSession();
            ITeacherMapper teaMapper = session.getMapper(ITeacherMapper.class);
            Teacher teacher = teaMapper.getTeacher(2L);
            System.out.println(teacher);
            List<Student> stus = teacher.getStus();
            for (Student student : stus) {
                System.out.println(student);
            }
            session.commit();
            session.close();
    
        }