关联查询
当查询结果涉及到多张表是，就需要使用关联查询 ：一对多 ，多对一 ，自关联 ，多对多
一对多：一的一方可以看到多方，就是在一的一方植入多的一方的属性
单条SQL:两表联查------直接到内存
多条SQl:每次都只关注一张表，到内存投影再去调度另一个SQL
根据部门编号查询部门对象，一个部门下有多个员工
public class DeptOneToMany { private Integer deptno; private String deptname; //在一的一方植入多的一方的属性 private List<UserOneToMany> list=new ArrayList<UserOneToMany>();
public interface IDeptOneToManyDao { //根据id 获取到一个 对象 public DeptOneToMany getDempById(int deptno);
两表联查 ----------单条SQL
<?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="cn.kitty.dao.IDeptOneToManyDao"> <resultMap id="DeptMapper" type="DeptOneToMany" > <id property="deptno" column="deptno"></id> <result property="deptname" column="deptname"></result> <collection property="list" ofType="UserOneToMany"> <id column="uid" property="uid" ></id> <result column="uname" property="uname"></result> </collection> </resultMap> <select id="getDempById" resultMap="DeptMapper"> SELECT dept.`deptno` ,dept.`deptname` ,user.`uid` ,user.`uname` FROM `dept`,`user` WHERE dept.`deptno`=`user`.`deptno` AND dept.`deptno`=#{deptno} </select>
public class Test2017921 { @Test//一对多单条SQL的实现 public void DeptOneToMay(){ SqlSession session = MybatisUtil.getSession(); IDeptOneToManyDao dao = session.getMapper(IDeptOneToManyDao.class); DeptOneToMany demp = dao.getDempById(1); System.out.println(demp.getDeptname()); System.out.println( "ok"); session.close(); }
多条SQL-------- 每次都只关注一张表，到内存投影再去调度另一个SQL
public interface IDeptOneToManyDao { //根据id 获取到一个 对象 public DeptOneToMany getDempById(int deptno); //根据id 获取到一个 对象 public DeptOneToMany getDeptByDeptNoMultiSQL(int deptno); }
<resultMap id="DeptMultiSQLMapper" type="DeptOneToMany">
<id column="deptno" property="deptno"></id>
<result column="deptname" property="deptname"></result>
<!-- collection:集合 一对多 property:一的一方植入多的一方集合的名字 ofType:集合中每个元素的类型 select="一个新的sql语句" column="sql语句需要的入参" -->
<collection property="list" column="deptno" ofType="UserOneToMany" select="selectUserById"></collection>
</resultMap>
<select id="selectUserById" resultType="UserOneToMany" >
select * from user where deptno=#{XXXXXXXXXXXXXXXXXX}
</select>
<!--d-多条SQL一对多的查询部门中的user-->
<select id="getDeptByDeptNoMultiSQL" resultMap="DeptMultiSQLMapper">
select * from dept where deptno=#{deptno}
</select>
</mapper>
@Test//一对多多条SQL的实现 public void DempOneToManyMutil(){ SqlSession session = MybatisUtil.getSession(); IDeptOneToManyDao dao = session.getMapper(IDeptOneToManyDao.class); DeptOneToMany dept = dao.getDeptByDeptNoMultiSQL(1); System.out.println(dept.getDeptname()); for (UserOneToMany emp:dept.getList()) { System.out.println(emp.getUname()+"hahha"); } session.close(); }
多对一：在多的一方记录一的一方的属性
根据员工编号检索员工对象同时需要检索出员工所属部门
public class UserOneToMany { private Integer uid; private String uname; private Integer deptno; //多的一方，需要植入一的一方对象 private DeptOneToMany dept;//多对一
public interface IUserManyToOneDao { public UserOneToMany getUserByUid(int uid); }
<?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="cn.kitty.dao.IUserManyToOneDao"> <resultMap id="UserOneToManyMapper" type="UserOneToMany"> <id column="uid" property="uid"></id> <result column="uname" property="uname"></result> <association property="dept" javaType="DeptOneToMany" > <id column="deptno" property="deptno"></id> <result column="deptname" property="deptname"></result> </association> </resultMap> <select id="getUserByUid" resultMap="UserOneToManyMapper"> SELECT dept.`deptno`,`deptname`,user.`uid`,user.`uname` FROM dept,`user` WHERE dept.`deptno`=user.`deptno` AND uid=#{uid} </select> </mapper>
@Test//多对一 public void UserManyToOne(){ SqlSession session = MybatisUtil.getSession(); IUserManyToOneDao dao=session.getMapper(IUserManyToOneDao.class); System.out.println( "多对一"); UserOneToMany user = dao.getUserByUid(1); System.out.println(user+"多对一"); session.close(); }
自关联：
所谓自关联是指，自己既充当一方，又充当多方，是1:n或n:1的变型。例如，对于商品分类，可以充当一方，即父分类，也可以充当多方，即子分类。而反映到DB表中。只有 一 张表，这张表中具有一个外键，用于表示该分类的父分类。一级分类没有父分类，所以可以将其外键设置为0，而子分类则具有外键值。
为了便于理解，将自关联分为两种情况来讲解。一种是当做1：n讲解，即当前类作为一方，其包含多方的集合域属性。一种是当做n：1讲解。即当前类作为多方，其包含一方的域属性。
public class Category { private Integer cid; private String cname; private Integer pid; private List<Category> list=new ArrayList<Category>();
package cn.kitty.dao; import cn.kitty.bean.Category; import java.util.List; public interface ICategoryDao { //根据pid 查询分类 自联接 public List<Category> getChildrenByPid(int pid); }
<?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="cn.kitty.dao.ICategoryDao"> <resultMap id="CategoryMapper" type="Category"> <id property="cid" column="cid"></id> <result property="cname" column="cname"></result> <collection property="list" ofType="Category" select="getChildrenByPid" column="cid"> </collection> </resultMap> <select id="getChildrenByPid" resultMap="CategoryMapper" > select * from category where pid=#{pid} </select> </mapper>
package usertest; import cn.kitty.bean.Category; import cn.kitty.dao.ICategoryDao; import cn.kitty.uitl.MybatisUtil; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.junit.Test; import java.util.List; public class CategoryTest { @Test public void CategoryConnection(){ SqlSession session = MybatisUtil.getSession(); ICategoryDao dao = session.getMapper(ICategoryDao.class); int pid=0; List<Category> list = dao.getChildrenByPid(pid); for (Category cate:list) { System.out.println(cate+"-----------------"); System.out.println( ); session.close(); } } }
多对多：
学生和老师的关系是多对多的关系
一个学生可以有多个老师，一个老师也可以教多个学生
要用到：
学生表，老师表和 连接他们的中间表
根据老师的编号查询 老师所教的学生
package cn.kitty.bean; public class Student { private Integer sid; private String sname;
package cn.kitty.bean; import java.util.ArrayList; import java.util.List; public class Teacher { private Integer tid ; private String tname; private List<Student> stus=new ArrayList<Student>();
package cn.kitty.dao; import cn.kitty.bean.Teacher; public interface ITeacherDao { //根据老师id获取学生 public Teacher getTeacherById (int tid); }
<?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="cn.kitty.dao.ITeacherDao"> <resultMap id="TeacherMapper" type="Teacher"> <result property="tname" column="tname"/> <collection property="stus" ofType="Student"> <result column="sname" property="sname"/> </collection> </resultMap> <select id="getTeacherById" resultMap="TeacherMapper"> SELECT tname,sname FROM `studentt14`,`teachert14`,`teacher_studentt14` WHERE `studentt14`.`sid`=`teacher_studentt14`.`sid` AND `teachert14`.`tid`=`teacher_studentt14`.`tid` AND `teacher_studentt14`.`tid`=#{tid} </select> </mapper>
@Test public void Student_Teacher() { SqlSession session = MybatisUtil.getSession(); ITeacherDao dao = session.getMapper(ITeacherDao.class); Teacher teacher = dao.getTeacherById(2); System.out.println(teacher.getTname()); System.out.println(teacher.getStus()); session.close(); }
