- 员工表,字段是员工id,姓名,部门号
- 部门表,字段分别是部门号,部门名称
- 由于多表查询最重要的就是mapper配置了,所以这里我们只说mapper,其他的配置就不多赘述
多对一
-
任务:查询每个员工的信息和部门信息
-
自定义员工类,这里将dept的类型改为了Dept对象
public class Emp {
private String id;
private String name;
private Dept dept;
public Emp() {
}
public Emp(String id, String name, Dept dept, String deptname) {
this.id = id;
this.name = name;
this.dept = dept;
}
@Override
public String toString() {
return "Emp{" +
"id='" + id + '\'' +
", name='" + name + '\'' +
", dept='" + dept + '\'' +
'}';
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Dept getDept() {
return dept;
}
public void setDept(Dept dept) {
this.dept = dept;
}
}
- 自定义部门类
public class Dept {
private String id;
private String name;
public Dept(String id, String name) {
this.id = id;
this.name = name;
}
public Dept() {
}
@Override
public String toString() {
return "Dept{" +
"id='" + id + '\'' +
", name='" + name + '\'' +
'}';
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
联合查询
- DAO接口
List<Emp> getAllEmp2();
<select id="getAllEmp2" resultMap="Emp_Dept2">
select e.id eid, e.name ename, d.name dname
from emp e,
dept d
where e.dept = d.id
</select>
<resultMap id="Emp_Dept2" type="bean.Emp">
<result column="eid" property="id"/>
<result column="ename" property="name"/>
<association property="dept" javaType="bean.Dept">
<result property="dname" column="name"/>
<result property="d.id" column="id"/>
</association>
</resultMap>
这就是一个多表查询的语句,但是要将要用的属性都取别名,返回类型是自己自定义包装的
这是在包装上述SQL语句的返回类型,其中使用association是因为返回的结果是对象,property属性的值时Java类的属性名,column是在表里的列名,javaType是类的所限定名称
嵌套查询
- DAO接口
List<Emp> getAllEmp();
<select id="getAllEmp" resultMap="Emp_Dept">
select *
from emp
</select>
<select id="getDeptById" resultType="bean.Dept">
select *
from dept
where id = #{id};
</select>
<resultMap id="Emp_Dept" type="bean.Emp">
<result property="id" column="id"/>
<result property="name" column="name"/>
<association property="dept" column="dept" select="getDeptById" javaType="bean.Dept"/>
</resultMap>
- 这是为了查找emp表里的所有数据
- 这是为了通过id查找dept表数据
- 将俩表连接,id和name分别就是属性名和列名对应,association标签下的property是说明java类的属性,column表的列名,select表示引用id为getDeptById的sql语句,javaType属性类型
- 这里就可以理解为,先执行id为getAllEmp的SQL语句,找出执行后结果中dept列的值传给id为getDeptById的sql语句,再将最后的结果存储到属性名dept的属性上
一对多
-
查询指定的部门里的员工信息
-
自定义Dept类
import java.util.List;
public class Dept2 {
private String id;
private String name;
private List<Emp2> emp2List;
public Dept2(String id, String name, List<Emp2> emp2List) {
this.id = id;
this.name = name;
this.emp2List = emp2List;
}
public Dept2() {
}
@Override
public String toString() {
return "Dept2{" +
"id='" + id + '\'' +
", name='" + name + '\'' +
", emp2List=" + emp2List +
'}';
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<Emp2> getEmp2List() {
return emp2List;
}
public void setEmp2List(List<Emp2> emp2List) {
this.emp2List = emp2List;
}
}
- 自定义Emp类
public class Emp2 {
private String id;
private String name;
private String dept;
public Emp2(String id, String name, String dept) {
this.id = id;
this.name = name;
this.dept = dept;
}
public Emp2() {
}
@Override
public String toString() {
return "Emp2{" +
"id='" + id + '\'' +
", name='" + name + '\'' +
", dept='" + dept + '\'' +
'}';
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getDept() {
return dept;
}
public void setDept(String dept) {
this.dept = dept;
}
}
联合查询
- DAO接口
Dept2 getEmpByDeptId(@Param("id") String id);
<select id="getEmpByDeptId" resultMap="Dept_Emp">
select d.id did, d.name dname, e.id eid, e.name ename, e.dept edept
from emp e,
dept d
where d.id = #{id}
and d.id = e.id
</select>
<resultMap id="Dept_Emp" type="bean.Dept2">
<result column="did" property="id"/>
<result column="dname" property="name"/>
<collection property="emp2List" ofType="bean.Emp2">
<result property="id" column="eid"/>
<result property="name" column="ename"/>
<result property="dept" column="edept"/>
</collection>
</resultMap>
- 多表查询,先判断找出那个指定的部门
- 一对多的时候使用collection
嵌套查询
<select id="getEmpByDeptId2" resultMap="Dept_Emp2">
select *
from dept
where id = #{id}
</select>
<select id="getEmpId" resultType="bean.Emp2">
select *
from emp
where dept = #{id}
</select>
<resultMap id="Dept_Emp2" type="bean.Dept2">
<result column="id" property="id"/>
<result column="name" property="name"/>
<collection property="emp2List" ofType="bean.Emp2" javaType="ArrayList" select="getEmpId" column="id"/>
</resultMap>
-
查询指定部门表的全部信息,返回的自己自定义的
-
查询员工表的所有信息
- emp2List是因为自定义的Dept类型中的集合名称就叫emp2List,ofType就是集合里面的类型,也可以说是集合的约束,javaType是返回的数据类型是集合,select引用的SQL语句,column对应表里的列名
- 可以理解为先执行id为getEmpByDeptId2的sql语句,然后将getEmpByDeptId2语句执行结果的id列的值传给id为getEmpId的SQL语句,然后将getEmpId语句的执行结果存储到集合里