使用的表
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}