MyBatis一对多问题记录

MyBatis一对多。

需要cglig.jar和asm.jar的支持。

有三张表，通过User表查询User_Role中核角色的对应关系，然后通过这个对应关系查询到用户所属角色。

上代码：

user.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.yrsoft.dao.UserDao">
	<resultMap type="com.yrsoft.entity.User" id="User" >
		<id column="id" property="id"/>
		<result column="username" property="username"/>
		<result column="password" property="password"/>
		<result column="isDelete" property="isDelete"/>
		<result column="createDate" property="createDate"/>
		<collection property="userRoles" ofType="com.yrsoft.dao.UserRoleDao.UserRole" select="com.yrsoft.dao.UserRoleDao.findByUserId" column="id">
		</collection>
	</resultMap>
	
	<select id="findUserByUserName" resultMap="User">
		select * from t_user where username = #{username}
	</select>
	<select id="findAll" resultMap="User">
		select * from t_user
	</select>
</mapper>

userRole.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.yrsoft.dao.UserRoleDao">
	<resultMap type="com.yrsoft.entity.UserRole" id="UserRole">
		<id column="id" property="id"/>
		<result column="user" property="userId" javaType="int" jdbcType="INTEGER"/>
		<result column="role" property="roleId" javaType="int" jdbcType="INTEGER"/>
		<association property="role" column="role" javaType="com.yrsoft.entity.Role" select="com.yrsoft.dao.RoleDao.findById"/> 
	</resultMap>
	
	<select id="findById" resultMap="UserRole">
		select * from t_user_role where id = #{id}
	</select>
	<select id="findAll" resultMap="UserRole">
		select * from t_user_role
	</select>
	<select id="findByUserId" parameterType="int" resultMap="UserRole">
		select * from t_user_role where user=#{id}
	</select>
</mapper>

role.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.yrsoft.dao.RoleDao">
	<resultMap type="com.yrsoft.entity.Role" id="Role">
		<id column="id" property="id"/>
		<result column="name" property="name"/>
		<result column="description" property="description"/>
	</resultMap>
	
	<select id="findById" resultMap="Role">
		select * from t_role where id = #{id}
	</select>
	<select id="findAll" resultMap="Role">
		select * from t_user_role
	</select>
</mapper>

User.java

package com.yrsoft.entity;

import java.sql.Timestamp;
import java.util.List;

/***
 * 用户表
 */
public class User {
	Integer id;
	/** 用户名 **/
	String username;
	/** 密码 **/
	String password;
	/** 是否删除 **/
	Integer isDelete;
	/** 创建时间 **/
	Timestamp createDate;
	// 多对多用户权限表
	List<UserRole> userRoles;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public Integer getIsDelete() {
		return isDelete;
	}

	public void setIsDelete(Integer isDelete) {
		this.isDelete = isDelete;
	}

	public Timestamp getCreateDate() {
		return createDate;
	}

	public void setCreateDate(Timestamp createDate) {
		this.createDate = createDate;
	}

	public List<UserRole> getUserRoles() {
		return userRoles;
	}

	public void setUserRoles(List<UserRole> userRoles) {
		this.userRoles = userRoles;
	}

	@Override
	public String toString() {
		return "\"User\":{\"id\":" + id + ",\"username\":" + username + ",\"password\":"
				+ password + ",\"isDelete\":" + isDelete + ",\"createDate\":"
				+ createDate + ",\"userRoles\":" + userRoles + "}";
	}

	
}

UserRole.java

package com.yrsoft.entity;

/***
 * 用户角色表
 */
public class UserRole {
	Integer id;
	int userId;
	int roleId;
	/*User user;*/
	Role role;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public int getUserId() {
		return userId;
	}

	public void setUserId(int userId) {
		this.userId = userId;
	}

	public int getRoleId() {
		return roleId;
	}

	public void setRoleId(int roleId) {
		this.roleId = roleId;
	}

	public Role getRole() {
		return role;
	}

	public void setRole(Role role) {
		this.role = role;
	}

	@Override
	public String toString() {
		return "\"UserRole\":{\"id\":" + id + ",\"userId\":" + userId + ",\"roleId\":"
				+ roleId + ",\"Role\":"+role+"}";
	}

}

Role.java

package com.yrsoft.entity;

/****
 * 角色表
 */
public class Role {
	Integer id;
	/** 角色名 **/
	String name;
	/** 角色说明 **/
	String description;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}
	@Override
	public String toString() {
		return "\"Role\":{\"id\":" + id + ",\"name\":" + name + ",\"description\":"
				+ description + "}";
	}
	
	

}

关键点：

一：一对一使用<association>标签，一对多直接使用<collection>标签
二：association标签中的column为要传进去的列名，是数据库中的列名，不是bean中的属性名。collection中的column同理

三：collection中，一定要写ofType属性，告诉系统你这个集合的泛型，有点像resultMap

参考文章：孤傲苍狼的博客