数据库
create table employee(
id int primary key,
name varchar(20),
salary int,
dep_id int,
foreign key(dep_id) references department(id)
);
create talbe department(
id int primary key,
name varchar(20)
);
pojo
// pojo
public class Employee {
private Long id;
private String name;
private Integer salary;
private Integer depId;
// 通过子查询获得的属性
private String depName;
}
需要实现的sql语句
select id,name,salary,
(select name from department where id=dep_id) depName
from employee;
实现步骤
1. 子查询:方法参数由父查询提供
@Select("select name from department where id = #{id}")
String nameById(Integer id);
2. 父查询:将需要的数据都查询出来,注意子查询的查询条件dep_id也需要查询
@Select("select id,name,salary,dep_id from tbl_employee")
List<Employee> getAll();
3. 子查询映射
one: 配置子查询路径 (找到子查询方法)
column: 给子查询设置参数 (父查询查到的dep_id作为子查询的参数)
property: 将查询结果封装到对应的pojo属性中 (子查询中的name ——> depName)
@Select("select id,name,salary,dep_id from tbl_employee")
@Results(value = {
@Result(
property = "depName", column = "dep_id",
one = @One(select = "com.hrm.dao.DepartmentDao.nameById"))})
List<Employee> getAll();