1、搭建数据库
tbl_employee表【d_id 是外键 对应 tbl_dept表】
id last_name gender email d_id 1 mike 0 [email protected] 1 2 book 0 [email protected] 2 3 tom 1 [email protected] 2 4 jerry 1 [email protected] 1
tbl_dept表：
id dept_name 1 开发部 2 测试部
2、编辑 javabean
Employee.java
package com.mybatis.bean;
public class Employee {
private int id;
private String lastName;
private String email;
private String gender;
private Department department;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public Department getDepartment() {
return department;
}
public void setDepartment(Department department) {
this.department = department;
}
@Override
public String toString() {
return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + "]";
}
}
Department.java
package com.mybatis.bean;
import java.util.List;
public class Department {
private Integer id;
private String departmentName;
private List<Employee> employees;
public List<Employee> getEmployees() {
return employees;
}
public void setEmployees(List<Employee> employees) {
this.employees = employees;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getDepartmentName() {
return departmentName;
}
public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}
@Override
public String toString() {
return "Department [id=" + id + ", departmentName=" + departmentName
+ "]";
}
}
3、编辑接口文件
DepartmentMapper.java
package com.mybatis.mapper;
import com.mybatis.bean.Department;
public interface DepartmentMapper {
//分步查询
public Department getDeptByIdStep(Integer id);
}
EmployeeMapper.java
package com.mybatis.mapper;
import java.util.List;
import com.mybatis.bean.Employee;
public interface EmployeeMapperPlus {
public List<Employee> getEmpsByDeptId(Integer deptId);
}
4、编辑sql映射文件
DepartmentMapper.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.mybatis.mapper.DepartmentMapper">
<!-- collection：分步查询 -->
<resultMap type="com.mybatis.bean.Department" id="MyDeptStep">
<id column="id" property="id"/>
<id column="dept_name" property="departmentName"/>
<collection property="employees"
select="com.mybatis.mapper.EmployeeMapperPlus.getEmpsByDeptId"
column="id" fetchType="lazy"></collection>
</resultMap>
<!-- public Department getDeptByIdStep(Integer id); -->
<select id="getDeptByIdStep" resultMap="MyDeptStep">
select id,dept_name from tbl_dept where id=#{id}
</select>
<!-- 扩展：多列的值传递过去：
将多列的值封装map传递；
column="{key1=column1,key2=column2}"
fetchType="lazy"：表示使用延迟加载；
- lazy：延迟
- eager：立即
-->
</mapper>
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.mybatis.mapper.EmployeeMapperPlus">
<!--自定义某个javaBean的封装规则
type：自定义规则的Java类型
id:唯一id方便引用
-->
<!-- public List<Employee> getEmpsByDeptId(Integer deptId); -->
<select id="getEmpsByDeptId" resultType="com.mybatis.bean.Employee">
select * from tbl_employee where d_id=#{deptId}
</select>
</mapper>
5、编辑全局配置文件：
mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <environments default="development"> <environment id="development"> <transactionManager type="JDBC" /> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost:3306/mybatis" /> <property name="username" value="root" /> <property name="password" value="Kitty521!" /> </dataSource> </environment> </environments> <!-- 将我们写好的sql映射文件（EmployeeMapper.xml）一定要注册到全局配置文件（mybatis-config.xml）中 如果 数据库全局文件 和 子配置文件 不在同一个目录 ，就需要 /目录/目录/.../EmployeeMapper_old.xml --> <mappers> <!-- 新方法操作mybatis 需要 的配置文件 --> <mapper resource="EmployeeMapperPlus.xml" /> <mapper resource="DepartmentMapper.xml" /> </mappers> </configuration>
特别注意：分步查询需要 调用两句sql语句。因为这两句sql语句分布在两个不同的sql映射文件，所以两个sql映射文件都需要注册到 全局配置文件中。
6、补充说明
分步查询中：子句查询时（无论是association 还是 collection 标签），传入多个参数问题，延迟加载设置：
### 举例：
### fetchType="lazy" 代表子句执行时，用的是延迟加载。
### - lazy：延迟
### - eager：立即
### 子查询 多个入参问题
### column="{deptId=id}" 代表 sql字段的 id ，传入给 子句查询中的 deptId 入参
### 将多列的值封装成map；column="{key1=column1,key2=column2}" 来传给子查询的入参
<collection property="employees"
select="com.mybatis.mapper.EmployeeMapperPlus.getEmpsByDeptId"
column="{deptId=id}" fetchType="lazy">
</collection>