mybatis多表查询的三种方式

MyBatis 多表查询的三种方式 这里用员工和部门表举例子 1、association 查询

<resultMap id="selectemp1" type="hashmap"> <result column="name" property="name"></result> <result column="job" property="job"></result> <result column="mgrid" property="mgrid"></result> <result column="salary" property="salary"></result> <result column="deptid" property="deptid"></result> <result column="intime" property="intime"></result> </resultMap> <select id="listemp2" resultMap="selectemp1" > SELECT id, name, job, mgrid, salary, deptid, intime FROM empinfo </select>

接口里自定义方法， 方法名和select的id对应 resultMap自定义结果集的id和select的resultType对应 然后就可以测试了

//结果集查询部分 List<HashMap<String, String>> hashMaps = mapper.listemp3(); for (int i=0;i<hashMaps.size();i++){ System.out.println(hashMaps.get(i)); } 2、级联查询 可以用懒加载 要在mybatis-config.xml里打开懒加载 <settings> <setting name="cacheEnabled" value="true"/> <!-- 开启延迟加载 --> <setting name="lazyLoadingEnabled" value="true"/> <!-- 设置加载的数据是按需还是全部 --> <setting name="aggressiveLazyLoading" value="false"/> </settings>

然后就可以在接口同级的xml里编写

//根据员工查询部门 <resultMap id="selectempb" type="empinfo"> <id column="id" property="id"></id> <result column="name" property="name"></result> <result column="salary" property="salary"></result> <association property="dept" select="mapper.EmpMapper1.deptby" column="deptid" fetchType="lazy"> </association> </resultMap> <select id="listempall" resultMap="selectempb"> SELECT name, salary, deptid FROM empinfo </select> <select id="deptby" resultType="Dept"> SELECT deptname FROM dept WHERE deptno=#{deptid} </select>

测试： 可以查询全部属性 也可以查询部分属性 （sql语句会不同）

//用类级联多表查询 public void test2(){ EmpMapper1 mapper = session.getMapper(EmpMapper1.class); List<Empinfo> listempall = mapper.listempall(); for (int i=0;i<listempall.size();i++){ Empinfo empinfo = listempall.get(i); System.out.println(empinfo.getName()+"\t"+empinfo.getSalary()+"\t"+empinfo.getDept().getDeptname()); } }

3、association 分步查询

在实体类里添加一个集合属性

private int deptno; private String deptname; private List<Empinfo> empinfos;

然后到接口同级的xml里

<resultMap id="dselecte" type="dept"> <id column="deptno" property="deptno"></id> <result column="deptname" property="deptname"></result> <collection property="empinfos" ofType="pojo.Empinfo"> <id column="id" property="id"></id> <result column="name" property="name"></result> <result column="salary" property="salary"></result> </collection> </resultMap> <select id="listdept" resultMap="dselecte"> SELECT d.deptno, d.deptname, e.id, e.name, e.salary FROM dept d,empinfo e WHERE e.deptid=d.deptno </select>

测试：

public void test3(){ EmpMapper1 mapper = session.getMapper(EmpMapper1.class); List<Dept> listdept = mapper.listdept(); for (int i=0;i<listdept.size();i++){ Dept dept = listdept.get(i); System.out.println("部门 \t ："+dept.getDeptname()); for (int j=0;j<dept.getEmpinfos().size();j++){ Empinfo empinfo = dept.getEmpinfos().get(j); System.out.println("员工 ："+empinfo.getName()+"\t"+empinfo.getSalary()); } } }