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