八.MyBatis关联查询
1.一对多单条SQL
例如部门和员工,实体类如下
所属部门
//所属部门类
public class Dept {
public Integer deptNo;
public String deptName;
public List<Emp> emps=new ArrayList<Emp>();
public Integer getDeptNo() {
return deptNo;
}
public void setDeptNo(Integer deptNo) {
this.deptNo = deptNo;
}
public String getDeptName() {
return deptName;
}
public void setDeptName(String deptName) {
this.deptName = deptName;
}
public List<Emp> getEmps() {
return emps;
}
public void setEmps(List<Emp> emps) {
this.emps = emps;
}
}
员工
//员工类
public class Emp {
public Integer empNo;
public String empName;
public Integer deptNo;
public Integer getEmpNo() {
return empNo;
}
public void setEmpNo(Integer empNo) {
this.empNo = empNo;
}
public String getEmpName() {
return empName;
}
public void setEmpName(String empName) {
this.empName = empName;
}
public Integer getDeptNo() {
return deptNo;
}
public void setDeptNo(Integer deptNo) {
this.deptNo = deptNo;
}
}
先创建一个接口,定义如下方法
//一对多单条SQL
public Dept getDeptById(int id);
在配置文件中写入以下内容
<!--单条SQL-->
<resultMap id="deptMapper" type="Dept">
<result column="deptName" property="deptName"></result>
<collection property="emps" ofType="Emp">
<result column="empName" property="empName"></result>
</collection>
</resultMap>
<select id="getDeptById" resultMap="deptMapper">
SELECT deptName,empName from dept,emp
where dept.deptNo=emp.deptNo and
dept.deptNo=#{deptNo}
</select>
MyBatis中使用collection标签来解决一对多的关联查询,property属性指定集合名称,ofType属性指定集合中元素的对象类型。
2.一对多多条SQL
与单条SQL类似,需要改动的地方,在collection节点上添加两个属性,代码如下
<!--多条SQL-->
<resultMap id="empMapper" type="Dept">
<result column="deptName" property="deptName"></result>
<collection property="emps" ofType="Emp" select="selectMultSQL" column="deptNo">
</collection>
</resultMap>
<select id="getDeptByIdMultSQL" resultMap="empMapper">
SELECT * from dept
where deptNo=#{deptNo}
</select>
<select id="selectMultSQL" resultType="Emp">
SELECT * from emp
where deptNo=#{deptNo}
</select>
select属性指向一个新的select节点,column属性指向数据表中的列名。
3.多对一单条SQL
在员工实体类(Emp)中添加所属部门类(Dept)的对象
创建接口方法
//多对一 单条SQL
public Emp getEmpById(int empNo);
配置xml文件
<!--单条SQL-->
<resultMap id="EmpMapper" type="emp">
<result column="empName" property="empName"></result>
<association property="dept" javaType="Dept">
<result column="deptName" property="deptName"></result>
</association>
</resultMap>
<select id="getEmpById" resultMap="EmpMapper">
SELECT deptName,empName from dept,emp
where dept.deptNo=emp.deptNo and
empNo=#{deptNo}
</select>
MyBatis中使用association标签来解决多对一的关联查询,property属性指定对象名称,javaType属性指定对象类型。
4.多对一多条SQL
与一对多的实现方法相同
<!--多条SQL-->
<resultMap id="empMapper" type="Emp">
<result column="empName" property="empName"></result>
<association property="dept" javaType="Dept" select="selectMultSQL" column="deptNo">
</association>
</resultMap>
<select id="getEmpByIdMultSQL" resultMap="empMapper">
SELECT * from emp
where empNo=#{empNo}
</select>
<select id="selectMultSQL" resultType="Dept">
SELECT * from dept
where deptNo=#{deptNo}
</select>
5.多对多
//教师类
public class Teacher {
private Integer tid;
private String tname;
//植入学生集合
private List<Student> stus=new ArrayList<Student>();
public Integer getTid() {
return tid;
}
public void setTid(Integer tid) {
this.tid = tid;
}
public String getTname() {
return tname;
}
public void setTname(String tname) {
this.tname = tname;
}
public List<Student> getStus() {
return stus;
}
public void setStus(List<Student> stus) {
this.stus = stus;
}
}
//学生类
public class Student {
private Integer sid;
private String sname;
public Integer getSid() {
return sid;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
}
数据库结构
创建接口编写方法
//根据老师Id查询该老师所教的学员
public Teacher findAll(int tid);
配置xml
<!--多对多-->
<resultMap id="teacherMapper" type="Teacher">
<id column="tid" property="tid"></id>
<result column="tname" property="tname"></result>
<collection property="stus" ofType="Student">
<id column="sid" property="sid"></id>
<result column="sname" property="sname"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="teacherMapper">
SELECT * FROM student,teacher,Teacher_Student
WHERE Student.`sid`=Teacher_Student.`sid`
AND Teacher.`tid`=Teacher_Student.`tid`
AND Teacher_Student.`tid`=#{tid}
</select>
6.自连接
实体类
public class Category {
private Integer cid;
private String cname;
private Set<Category> cates;
@Override
public String toString() {
return "Category{" +
"cid=" + cid +
", cname='" + cname + '\'' +
", cates=" + cates +
'}';
}
public Integer getCid() {
return cid;
}
public void setCid(Integer cid) {
this.cid = cid;
}
public String getCname() {
return cname;
}
public void setCname(String cname) {
this.cname = cname;
}
public Set<Category> getCates() {
return cates;
}
public void setCates(Set<Category> cates) {
this.cates = cates;
}
}
数据库结构
配置xml文件
<!--自连接-->
<resultMap id="categoryMapper" type="Category">
<id column="tid" property="tid"></id>
<result column="tname" property="tname"></result>
<collection property="cates" ofType="Category" select="findAll" column="cid">
</collection>
</resultMap>
<select id="findAll" resultMap="categoryMapper">
SELECT * from category where pid=#{pid}
</select>
谢谢观看!