MyBatis之多表关联查询

1使用resultType、ResultMap处理返回结果

处理返回结果

resultType:指定返回值结果的完全限定名,处理多表查询的结果。

多表查询需要定义vo封装查询的结果。

需求：查询部门和部门下对应的岗位

部门名称岗位名称

办公室职员

办公室主人

1.1 建立Post的实体类

 1 package org.guangsoft.entity;
 2 /**
 3  * 岗位的实体类
 4  * @author guanghe
 5  */
 6 public class Post
 7 {
 8     private Integer pid;
 9     private String pname;
10     private String pdesc;
11     public Integer getPid()
12     {
13         return pid;
14     }
15     public void setPid(Integer pid)
16     {
17         this.pid = pid;
18     }
19     public String getPname()
20     {
21         return pname;
22     }
23     public void setPname(String pname)
24     {
25         this.pname = pname;
26     }
27     public String getPdesc()
28     {
29         return pdesc;
30     }
31     public void setPdesc(String pdesc)
32     {
33         this.pdesc = pdesc;
34     }
35     
36 }

1.2DeptMapper接口添加方法

 1 package org.guangsoft.mapper;
 2 
 3 import java.util.List;
 4 
 5 import org.guangsoft.entity.Dept;
 6 import org.guangsoft.vo.DeptVo;
 7 
 8 public interface DeptMapper
 9 {
10     public List<DeptVo> getDeptPost();
11 
12     public List<Dept> getDeptPostList();
13 }

1.3DeptMapperImpl中重写方法

 1 package org.guangsoft.mapper.impl;
 2 
 3 import java.util.List;
 4 
 5 import org.apache.ibatis.session.SqlSession;
 6 import org.apache.ibatis.session.SqlSessionFactory;
 7 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 8 import org.guangsoft.entity.Dept;
 9 import org.guangsoft.mapper.DeptMapper;
10 import org.guangsoft.vo.DeptVo;
11 
12 public class DeptMapperImpl implements DeptMapper
13 {
14     
15     @Override
16     public List<DeptVo> getDeptPost()
17     {
18         //创建sqlSessionFactory对象
19         SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
20         SqlSessionFactory ssf = ssfb.build(DeptMapperImpl.class.
21                 getClassLoader().getResourceAsStream("MyBatis.xml"));
22         //产生SqlSesion兑现
23         SqlSession sqlSession = ssf.openSession();
24         //进行数据的crud操作
25         List<DeptVo> dlist = sqlSession.selectList("dept.getDeptPost");
26         //提交事务
27         sqlSession.commit();
28         //释放资源
29         sqlSession.close();
30         return dlist;
31     }
32 
33     @Override
34     public List<Dept> getDeptPostList()
35     {
36         SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
37         SqlSessionFactory ssf = ssfb.build(DeptMapperImpl.class.
38                 getClassLoader().getResourceAsStream("MyBatis.xml"));
39         //产生SqlSesion兑现
40         SqlSession sqlSession = ssf.openSession();
41         //进行数据的crud操作
42         List<Dept> dlist = sqlSession.selectList("dept.getDeptPostList");
43         //提交事务
44         sqlSession.commit();
45         //释放资源
46         sqlSession.close();
47         return dlist;        
48     }
49 
50 }

1.4建立Post岗位信息表

 1 /*
 2 Navicat MySQL Data Transfer
 3 
 4 Source Server         : MySQL
 5 Source Server Version : 50715
 6 Source Host           : localhost:3306
 7 Source Database       : test
 8 
 9 Target Server Type    : MYSQL
10 Target Server Version : 50715
11 File Encoding         : 65001
12 
13 Date: 2016-12-13 20:50:00
14 */
15 
16 SET FOREIGN_KEY_CHECKS=0;
17 
18 -- ----------------------------
19 -- Table structure for post
20 -- ----------------------------
21 DROP TABLE IF EXISTS `post`;
22 CREATE TABLE `post` (
23   `pid` int(11) NOT NULL AUTO_INCREMENT,
24   `pname` varchar(255) DEFAULT NULL,
25   `pdesc` varchar(255) DEFAULT NULL,
26   `did` int(11) DEFAULT NULL,
27   PRIMARY KEY (`pid`),
28   KEY `fk_did` (`did`),
29   CONSTRAINT `fk_did` FOREIGN KEY (`did`) REFERENCES `dept` (`did`)
30 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

1.5DeptMapper.xml定义操作

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 3 <mapper namespace="dept">
 4     <select id="getDeptPost" resultType="org.guangsoft.vo.DeptVo">
 5         select did,dname,pname from dept inner join post on dept.did = post.did
 6     </select>
 7     <resultMap type="org.guangsoft.entity.Dept" id="deptPost">
 8         <id property="did" column="did"></id>
 9         <result property="dname" column="dname" javaType="java.lang.String"></result>
10         <collection property="posts" ofType="org.guangsoft.entity.Post">
11             <id property="pid" column="pid"></id>
12             <result property="pname" column="pname" javaType="java.lang.String"></result>
13             <result property="pdesc" column="pdesc" javaType="java.lang.String"></result>
14         </collection>
15     </resultMap>
16     <select id="getDeptPostList" resultMap="deptPost">
17         select * from dept inner join post
18         on dept.did = post.did
19     </select>
20 </mapper>

1.6添加测试方法

 1 package org.guangsoft.test;
 2 
 3 import java.util.List;
 4 
 5 import org.guangsoft.entity.Dept;
 6 import org.guangsoft.entity.Post;
 7 import org.guangsoft.mapper.DeptMapper;
 8 import org.guangsoft.mapper.impl.DeptMapperImpl;
 9 import org.guangsoft.vo.DeptVo;
10 import org.junit.Test;
11 
12 public class TestDeptMapper
13 {
14     DeptMapper deptMapper = new DeptMapperImpl();
15     
16     @Test
17     public void testGetDeptVo()
18     {
19         List<DeptVo> dlist = deptMapper.getDeptPost();
20         for(DeptVo deptVo : dlist)
21         {
22             System.out.println(deptVo.getDid() + 
23                 " " + deptVo.getDname() + deptVo.getPname());
24         }
25     }
26     
27     @Test
28     public void testGetDeptList()
29     {
30         List<Dept> dlist = deptMapper.getDeptPostList();
31         for(Dept dept : dlist)
32         {
33             System.out.println(dept.getDname());
34             List<Post> postList = dept.getPosts();
35             for(Post post : postList)
36             {
37                 System.out.println(post.getPname());
38             }
39         }
40     }
41     
42 }

2返回值结果的处理总结

使用resultType:指定的是返回值类型的完全限定名，只能用在单表查询或者定义vo的情况，如果是定义vo不能将关联的数据封装为需要获得某个类的对象

使用resultMap：不需要定义vo类，将关联数据对应的类，作为另外一个类的属性。

第一步：定义resultMap

第二步：引用resultMap

使用resultMap：用来多表关联的复杂查询中，通过需要将关联查询的数据封装为某个实体类对象，如果有特殊业务需要或者说明需要将管理数据封装为实体类的对象，使用resultMap