mybatis入门之四：实现关联数据的查询

有了前面几章的基础，对一些简单的应用是可以处理的，但在实际项目中，经常是关联表的查询，比如最常见到的多对一，一对多等。这些查询是如何处理的呢，这一讲就讲这个问题。我们首先创建一个角色这个表，并初始化数据.

CREATE TABLE `roleinfo` (
  `roleId` int(11) NOT NULL AUTO_INCREMENT,
  `roleName` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`roleId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8


-- 添加几条测试数据

INSERT into roleinfo(roleId, roleName) values(1, "admin");
INSERT into roleinfo(roleId, roleName) values(2, "user");
INSERT into roleinfo(roleId, roleName) values(3, "customer");
INSERT into roleinfo(roleId, roleName) values(4, "other");

 

按照一般需求，一个用户可以拥有多个角色，因此产生第三张表userandrole

CREATE TABLE `userandrole` (
  `userAndRoleId` int(11) NOT NULL AUTO_INCREMENT,
  `adminId` int(11) DEFAULT NULL,
  `roleId` int(11) DEFAULT NULL,
  PRIMARY KEY (`userAndRoleId`),
  KEY `FK_Reference_25` (`adminId`),
  KEY `FK_Reference_26` (`roleId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8


-- 添加几条测试数据

INSERT into userandrole(userAndRoleId, adminId, roleId) values(1, 1, 1);
INSERT into userandrole(userAndRoleId, adminId, roleId) values(2, 1, 2);
INSERT into userandrole(userAndRoleId, adminId, roleId) values(3, 1, 3);

 

对应实体类

package com.handly.mybatis.model;

/**
 * 角色信息
 * @author handly
 */
public class RoleInfo {
	private Integer roleId;
	private String roleName;

	//省略get、set方法

}

 

package com.handly.mybatis.model;

import java.util.List;

public class UserAndRole {
	private Integer userAndRoleId;
	private Integer adminId;
	private Integer roleId;
	
	private User user;
	private List<RoleInfo> roles;
	
	public UserAndRole(){}
	
	@Override
	public String toString() {
		return "Classes [id=" + userAndRoleId + ", user=" + user + ", roles=" + roles + "]";
	}

	//省略get/set方法

}

 方式一：嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集 封装联表查询的数据(去除重复的数据)

<!-- 方式一：嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集 封装联表查询的数据(去除重复的数据) 
		SELECT * from user u, roleinfo r, userandrole ur where ur.adminId=u.id and ur.roleId=r.roleId and u.id=1 -->
	<select id="getRole1" parameterType="int" resultMap="UserRoleResultMap">
		SELECT * from user u, roleinfo r, userandrole ur where ur.adminId=u.id and ur.roleId=r.roleId and u.id=#{id}
	</select>
	<!-- 使用resultMap映射实体类和字段之间的一一对应关系 -->
	<resultMap type="com.handly.mybatis.model.UserAndRole" id="UserRoleResultMap">
		<id property="userAndRoleId" column="userAndRoleId" />
		<result property="adminId" column="adminId" />
		<result property="roleId" column="roleId" />
		<association property="user" javaType="com.handly.mybatis.model.User">
			<id property="id" column="id" />
			<result property="username" column="username" />
			<result property="password" column="password" />
			<result property="name" column="name" />
		</association>
		<!-- ofType指定students集合中的对象类型 -->
		<collection property="roles" ofType="com.handly.mybatis.model.RoleInfo">
			<id property="roleId" column="roleId" />
			<result property="roleName" column="roleName" />
		</collection>
	</resultMap>

 方式二：嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型

<!-- 方式二：嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型 SELECT * from userandrole ur where ur.adminId=1; 
		SELECT id, username from user where id=1 //1 是上一个查询得到的adminId的值
		SELECT roleId, roleName from roleinfo where roleId=2 //2是第一个查询得到的roleId字段的值 -->
	<select id="getRole2" parameterType="int" resultMap="UserRoleResultMap2">
		SELECT * from userandrole ur where ur.adminId=#{id}
	</select>
	<resultMap type="com.handly.mybatis.model.UserAndRole" id="UserRoleResultMap2">
		<id property="userAndRoleId" column="userAndRoleId" />
		<result property="adminId" column="adminId" />
		<result property="roleId" column="roleId" />
		<association property="user" column="adminId" javaType="com.handly.mybatis.model.User" select="getUser"></association>
		<collection property="roles" column="roleId" ofType="com.handly.mybatis.model.RoleInfo" select="getRoles"></collection>
	</resultMap>
	<select id="getUser" parameterType="int" resultType="com.handly.mybatis.model.User">
		SELECT id, username from user where id=#{id}
	</select>
	<select id="getRoles" parameterType="int" resultType="com.handly.mybatis.model.RoleInfo">
		SELECT roleId, roleName from roleinfo where roleId=#{id}
	</select>

 新加接口

package com.handly.mybatis.dao;

import java.util.List;
import com.handly.mybatis.model.UserAndRole;

public interface RoleInfoDao {
	public List<UserAndRole> getRole1(Integer id);
	
	public List<UserAndRole> getRole2(Integer id);
}

 测试代码

/**
	 * 获取用户角色列表 
	 */
	@Test
	public void testGetRoleListByUserId(){
		SqlSession session = sqlSessionFactory.openSession();
        try {
            RoleInfoDao roleDao = session.getMapper(RoleInfoDao.class);
//            List<UserAndRole> list = roleDao.getRole1(1);
            List<UserAndRole> list = roleDao.getRole2(1);
            if(null!=list && list.size()>0){
            	for(UserAndRole ur : list){
//            		System.out.println(ur.getDomainId());
            		System.out.println("u:" + ur.getUser().getUsername());
            		if(null!=ur.getRoles() && ur.getRoles().size()>0){
            			for(RoleInfo r : ur.getRoles()){
            				System.out.print(r.getRoleName() + ", ");
            			}
            		}
            	}
            }
        } finally {
            session.close();
        }
	}