接上文,我们来演示在实际开发中,如何配置和使用resultMap实现一对多查询。
准备工作:
a.操作系统 :win7 x64
b.基本软件:MySQL,Mybatis,spring,SQLyog,Tomcat,web基础
特别的,作为演示程序,还请各位看官不要纠结数据库的细节内容
-------------------------------------------------------------------------------------------------------------------------------------
1.首先,复制上文工程,重命名为Mybatis05,工程结构图如下:
2.上文中,我们演示了:一个人对应一条个人信息。但是,如果我们根据部门来查询用户的话,就是典型的一对多的关系了。为了演示一对多关系查询的实现,我们需要现在数据库中,创建一个部门表。所有表结构如下:
各表中的数据如下:
3.鉴于篇幅的关系,重复的文件请各位看官参照上文创建,这里我们只说明有修改的文件。
4.新增Departments.Java文件,具体内容如下:
- package com.csdn.ingo.entity;
-
- import java.io.Serializable;
- import java.util.List;
-
-
-
-
-
-
- @SuppressWarnings("serial")
- public class Departments implements Serializable{
-
- private String id;
- private String departmentName;
- private List<UserInfo> userInfos;
- public String getId() {
- return id;
- }
- public void setId(String id) {
- this.id = id;
- }
- public String getDepartmentName() {
- return departmentName;
- }
- public void setDepartmentName(String departmentName) {
- this.departmentName = departmentName;
- }
- public List<UserInfo> getUserInfos() {
- return userInfos;
- }
- public void setUserInfos(List<UserInfo> userInfos) {
- this.userInfos = userInfos;
- }
- public Departments(String id, String departmentName, List<UserInfo> userInfos) {
- super();
- this.id = id;
- this.departmentName = departmentName;
- this.userInfos = userInfos;
- }
- public Departments() {
- super();
-
- }
- @Override
- public String toString() {
- return "Departments [id=" + id + ", departmentName=" + departmentName + ", userInfos=" + userInfos.toString() + "]";
- }
-
- }
5.新增DepartmentsDao.java文件,具体内容如下:
- package com.csdn.ingo.dao;
-
- import com.csdn.ingo.entity.Departments;
-
-
-
-
-
-
- public interface DepartmentsDao {
- Departments findDepartmentById(String id);
- }
6.新增DepartmentsDaoMapper.xml文件,具体内容如下:
- <?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="com.csdn.ingo.dao.DepartmentsDao">
- <resultMap type="Departments" id="DepartmentsResult">
- <id property="id" column="id" />
- <result property="departmentName" column="departmentName" />
- <collection property="userInfos" column="id" select="com.csdn.ingo.dao.UserInfoDao.findUserInfoByDepartmentId"></collection>
- </resultMap>
- <select id="findDepartmentById" parameterType="String" resultMap="DepartmentsResult">
- select * from departments where id=#{id}
- </select>
- </mapper>
7.修改UserInfoDao.java,具体内容如下:
- public interface UserInfoDao {
- UserInfo findUserInfoById(String id);
-
- UserInfo findUserInfoByDepartmentId(String id);
- }
8.在UserInfoMapper.xml,新增如下内容:
- <select id="findUserInfoByDepartmentId" parameterType="String" resultMap="UserInfoResult">
- select * from userinfo where department=#{id}
- </select>
9.新增单元测试方法:
- @Test
- public void testSeletDepartment() {
- DepartmentsDao userDao = sqlSession.getMapper(DepartmentsDao.class);
- String id = "2";
- Departments curUser = userDao.findDepartmentById(id);
- if(curUser!=null){
- log.info("成功找到用户:"+curUser.toString());
- }
10.测试方法,运行单元测试方法,观察控制台有如下输出:
- 成功找到用户:Departments [id=2, departmentName=销售部, userInfos=[UserInfo [userid=customer, department=2, position=售前, mobile=33334444, gender=1, email=customer@email.com], UserInfo [userid=customer2, department=2, position=售后, mobile=55556666, gender=1, email=customer@email.com]]]
- INFO [main] - 方法执行后调用
鉴于截图大小的关系,我们单独给出运行结果,请各位读者做参考。
--------------------------------------------------------------------------------------------------------------------------------------------------------
特别的:请读者特别关注表之间的关系,resultMap的书写方式,collection的书写方式,column的配置,如有疑问,请参考前面《Mybatis最入门---ResultMaps高级用法(上)》中的内容
--------------------------------------------------------------------------------------------------------------------------------------------------------
如果又细心的读者,或许已经发现,我们设计的表结构关系是user表--->userinfo表--->departments表。换句话说,我们使用user表中的变量是无法查到部门表的信息的。另外,我们实际也遇到这样的例子,人员的属性中存在多个集合属性,如,基本信息是一个集合,兴趣爱好是一个集合等等。有时为了完整的描述一个对象,需要使用多个集合属性来描述。因此,我们在这里在演示一个对象中多个集合的查询操作的实现。
1.首先,我们要在User中增加department属性,如下:【注意:我们只修改对象,并没有修改数据库】
- @SuppressWarnings("serial")
- public class User implements Serializable{
-
- private String id;
- private String password;
- private UserInfo userInfo;
- private Departments department;
- //....set
- //...get
- //...构造函数
- //..toString()
- }
2.修改UserMapper.xml中的resultMap,修改之后的内容为:
- <resultMap type="user" id="UserResult">
- <id property="id" column="id" />
- <result property="password" column="password" />
- <association property="userInfo" column="userid"
- select="com.csdn.ingo.dao.UserInfoDao.findUserInfoById"></association>
- <association property="department" column="department"
- select="com.csdn.ingo.dao.DepartmentsDao.findDepartmentById"></association>
- </resultMap>
3.新建单元测试方法,如下:
- @Test
- public void testSeletDepartmentAndInfo() {
- try{
- UserDao userDao = sqlSession.getMapper(UserDao.class);
- String id = "admin";
- User curUser = userDao.findUserInfoById(id);
- if(curUser!=null){
- log.info("成功找到用户:"+curUser.toString());
- }
- }catch(Exception e){
- e.printStackTrace();
- }
- }
4.测试方法:运行单元测试,观察控制台输出,有如下结果;
完整输出为:
- 成功找到用户:User [id=admin, password=admin, userInfo=UserInfo [userid=admin, department=1, position=工程师, mobile=11112222, gender=1, email=admin@email.com], department=Departments [id=1, departmentName=开发部, userInfos=[UserInfo [userid=admin, department=1, position=工程师, mobile=11112222, gender=1, email=admin@email.com]]]]
- -
输出内容仅供参考,各位看官只要看到类似输出即可。
-------------------------------------------------------------------------------------------------------------------------------------
至此,Mybatis最入门---ResultMaps实例篇(一对多查询)结束
特别备注:
这两篇的实例,请各位看官一定手动敲一遍,遇到不懂配置的地方请查看前文详细说明。