mybatis各种小知识点二：嵌套查询之一对多，多对一

使用的表
CREATE TABLE t_departments (
id int NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL,
location varchar(100) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE t_employees (
id int NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL,
salary double DEFAULT NULL,
dept_id int DEFAULT NULL,
PRIMARY KEY (id),
KEY dept_id (dept_id),
CONSTRAINT t_employees_ibfk_1 FOREIGN KEY (dept_id) REFERENCES t_departments (id)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

嵌套查询一对多映射：定义接口

/*查询部门信息*/
    Department queryDepartmentById(Integer integer);

原始的写法：定义sql语句

<resultMap id="department_employees" type="Department">
<id column="id" property="id"></id>
   <result column="name" property="name"></result>
   <result column="location" property="location"></result>
   <!--emp_id emp_name salary employees-->
   <collection property="employees" ofType="Employee">
       <id column="empId" property="id"></id>
       <result column="name" property="name"></result>
       <result column="salary" property="salary"></result>
   </collection>
</resultMap>
<select id="queryDepartmentById" resultMap="department_employees">
   select t_departments.id,t_departments.name,t_departments.location,
          t_employees.id as empId,t_employees.name,t_employees.salary
   from t_departments join t_employees on t_departments.id = t_employees.dept_id
   where t_departments.id = #{arg0}
</select>

使用嵌套写法：定义sql语句，此处的queryEmployeeByDepId方法须在employees的mapper文件中定义一下如下

employees的mapper文件
<!--嵌套查询-->
<select id="queryEmployeeByDepId" resultType="Employee">
    select * from t_employees where dept_id=#{id}
</select>

<!--嵌套查询-->
<resultMap id="department_employees" type="Department">
    <id column="id" property="id"></id>
    <result column="name" property="name"></result>
    <result column="location" property="location"></result>
    <!--emp_id emp_name salary employees-->
<!--        <collection property="employees" ofType="Employee">-->
<!--            <id column="empId" property="id"></id>-->
<!--            <result column="name" property="name"></result>-->
<!--            <result column="salary" property="salary"></result>-->
<!--        </collection>-->
    <collection property="employees" ofType="Employees" select="com.qf.dao.EmployeeDao.queryEmployeeByDepId" column="id">

    </collection>
</resultMap>
<select id="queryDepartmentById" resultMap="department_employees">
    select t_departments.id,t_departments.name,t_departments.location
    from t_departments
    where t_departments.id = #{arg0}
</select>

测试

System.out.println("一对多映射关系");
Department department = departmentDao.queryDepartmentById(1);
System.out.println(department);
List<Employee> employees = department.getEmployees();
for(Employee employee:employees){
    System.out.println(employee);
}
sqlSession.close();

结果

DEBUG 2021-09-21 15:31:18,487 com.qf.dao.DepartmentDao.queryDepartmentById: ==>  Preparing: select t_departments.id,t_departments.name,t_departments.location from t_departments where t_departments.id = ? 
DEBUG 2021-09-21 15:31:18,519 com.qf.dao.DepartmentDao.queryDepartmentById: ==> Parameters: 1(Integer)
DEBUG 2021-09-21 15:31:18,545 com.qf.dao.EmployeeDao.queryEmployeeByDepId: ====>  Preparing: select * from t_employees where dept_id=? 
DEBUG 2021-09-21 15:31:18,545 com.qf.dao.EmployeeDao.queryEmployeeByDepId: ====> Parameters: 1(Integer)
DEBUG 2021-09-21 15:31:18,548 com.qf.dao.EmployeeDao.queryEmployeeByDepId: <====      Total: 3
DEBUG 2021-09-21 15:31:18,550 com.qf.dao.DepartmentDao.queryDepartmentById: <==      Total: 1
Department{id=1, name='教学部', location='北京', employees=[Employee{id=1, name='shine01', salary='1000.5', department=null}, Employee{id=2, name='shine02', salary='2000.5', department=null}, Employee{id=5, name='shine03', salary='3000.6', department=null}]}
Employee{id=1, name='shine01', salary='1000.5', department=null}
Employee{id=2, name='shine02', salary='2000.5', department=null}
Employee{id=5, name='shine03', salary='3000.6', department=null}

嵌套查询多对一映射：定义接口

/*嵌套查询*/
List<Employee> queryEmployeeByDepId(@Param("id") Integer id);

定义sql语句

<resultMap id="Employee_Department" type="Employee">
    <id column="empId" property="id"></id>
    <result column="name" property="name"></result>
    <result column="salary" property="salary"></result>
    <association property="department" javaType="Department">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <result column="location" property="location"></result>
    </association>
</resultMap>
<select id="queryEmployeeById" resultMap="Employee_Department">
    select t_employees.id as empId,t_employees.name,t_employees.salary,
           t_departments.id,t_departments.name,t_departments.location
    from t_employees join t_departments on t_employees.dept_id = t_departments.id
    where t_employees.id = #{arg0};
</select>

测试

/*一对多映射  passenger_passport*/
EmployeeDao employeeDao = sqlSession.getMapper(EmployeeDao.class);
//5.执行sql语句
System.out.println("多对一映射关系---------------------------------------");
Employee employee1 = employeeDao.queryEmployeeById(3);
System.out.println(employee1);
Department departments = employee1.getDepartment();
System.out.println(departments);
sqlSession.close();

结果

DEBUG 2021-09-21 15:48:03,963 com.qf.dao.EmployeeDao.queryEmployeeById: ==>  Preparing: select t_employees.id as empId,t_employees.name,t_employees.salary, t_departments.id,t_departments.name,t_departments.location from t_employees join t_departments on t_employees.dept_id = t_departments.id where t_employees.id = ?; 
DEBUG 2021-09-21 15:48:03,990 com.qf.dao.EmployeeDao.queryEmployeeById: ==> Parameters: 3(Integer)
DEBUG 2021-09-21 15:48:04,014 com.qf.dao.EmployeeDao.queryEmployeeById: <==      Total: 1
Employee{id=3, name='张三', salary='9000.5', department=Department{id=2, name='张三', location='上海', employees=null}}
Department{id=2, name='张三', location='上海', employees=null}