MyBatis一对多collection用法

比如一个用户对应多个角色

首先用户表

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(64) DEFAULT NULL,
  `nickname` varchar(64) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `enabled` tinyint(1) DEFAULT 1,
  `email` varchar(64) DEFAULT NULL,
  `userface` varchar(255) DEFAULT NULL,
  `reg_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

然后角色表

CREATE TABLE `roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `role_code` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

用户与角色的关联表

CREATE TABLE `roles_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rid` int(11) DEFAULT 2,
  `uid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `rid` (`rid`),
  KEY `roles_user_ibfk_2` (`uid`),
  CONSTRAINT `roles_user_ibfk_1` FOREIGN KEY (`rid`) REFERENCES `roles` (`id`),
  CONSTRAINT `roles_user_ibfk_2` FOREIGN KEY (`uid`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=131 DEFAULT CHARSET=utf8;

user实体

package com.pwl.vcloud.common.entity;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author 
 * @date 2019/7/15 16:25
 * @description: 接口.
 */
public class UserVO implements Serializable {

    private Integer id;
    private String username;
    private String nickname;
    private String password;
    private Integer enabled;
    private String email;
    private String userface;
    private Date regTime;
    /**
     * 角色列表
     */
    private List<Roles> roleList = new ArrayList<>();

    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 getNickname() {
        return nickname;
    }

    public void setNickname(String nickname) {
        this.nickname = nickname;
    }

    public String getPassword() {
        return password;
    }

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

    public Integer getEnabled() {
        return enabled;
    }

    public void setEnabled(Integer enabled) {
        this.enabled = enabled;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getUserface() {
        return userface;
    }

    public void setUserface(String userface) {
        this.userface = userface;
    }

    public Date getRegTime() {
        return regTime;
    }

    public void setRegTime(Date regTime) {
        this.regTime = regTime;
    }

    public List<Roles> getRoleList() {
        return roleList;
    }

    public void setRoleList(List<Roles> roleList) {
        this.roleList = roleList;
    }
}

 

角色实体

package com.pwl.vcloud.common.entity;


import java.io.Serializable;

/**
 * <p>
 *
 * </p>
 *
 * @author pwl
 * @since 2019-07-14
 */
public class Roles  implements Serializable {

    private static final long serialVersionUID = 1L;

    private Integer id;
    private String name;
    private String roleCode;


    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 getRoleCode() {
        return roleCode;
    }

    public void setRoleCode(String roleCode) {
        this.roleCode = roleCode;
    }
}

 

userMapper

<?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.pwl.vcloud.user.mapper.UserMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.pwl.vcloud.user.entity.User">
        <id column="id" property="id" />
        <result column="username" property="username" />
        <result column="nickname" property="nickname" />
        <result column="password" property="password" />
        <result column="enabled" property="enabled" />
        <result column="email" property="email" />
        <result column="userface" property="userface" />
        <result column="reg_time" property="regTime" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, username, nickname, password, enabled, email, userface, regTime AS regTime
    </sql>

    <resultMap id="selectMap" type="com.pwl.vcloud.common.entity.UserVO">
        <id column="id" property="id" />
        <result column="username" property="username" />
        <result column="nickname" property="nickname" />
        <result column="password" property="password" />
        <result column="enabled" property="enabled" />
        <result column="email" property="email" />
        <result column="userface" property="userface" />
        <result column="reg_time" property="regTime" />
        <collection property="roleList" ofType="com.pwl.vcloud.common.entity.Roles">
            <id column="r_id" property="id"></id>
            <result column="role_name" property="name"></result>
            <result column="role_code" property="roleCode"></result>
        </collection>
    </resultMap>

    <select id="getUserByUsername" resultMap="selectMap">
      SELECT
        a.*,
        c.id r_id,
        c.name role_name,
        c.role_code  role_code
    FROM
        USER a
        LEFT JOIN roles_user b ON a.id = b.uid
        LEFT JOIN roles c ON b.rid = c.id
    WHERE
        a.username=#{username}
    </select>

</mapper>

通过传入的username来查询对应的角色

查询到有两个角色，通过字段映射到相关的字段

<collection property="roleList" ofType="com.pwl.vcloud.common.entity.Roles">
            <id column="r_id" property="id"></id>
            <result column="role_name" property="name"></result>
            <result column="role_code" property="roleCode"></result>
        </collection>

上面的collection中property的roleList 对应的是user实体中的属性

ofType:这个属性必须要，指映射到哪个实体类型上

<result>中的column：从数据库查询出的字段名或者别名

<result>中的property：对应实体的字段名，映射值

注意：

如果一对多查询时，上面的这种查询分页会有问题，分页不对。

 

第二种:(会出现N+1的问题,影响效率)

<resultMap id="selectMap" type="com.pwl.vcloud.common.entity.UserVO">
        <id column="id" property="id" />
        <result column="username" property="username" />
        <result column="nickname" property="nickname" />
        <result column="password" property="password" />
        <result column="enabled" property="enabled" />
        <result column="email" property="email" />
        <result column="userface" property="userface" />
        <result column="reg_time" property="regTime" />
        <collection property="roleList" column="id" select="getRoles" />
    </resultMap>

    <select id="getRoles" resultType="com.pwl.vcloud.common.entity.Roles">
      select * from roles a LEFT JOIN roles_user b on a.id = b.rid where b.uid= #{id}
    </select>

    <select id="getUserByUsername" resultMap="selectMap">
      SELECT
	    a.*
      FROM
	    USER a
      WHERE
	    a.username = #{username}
    </select>

在collection中使用select

coloumn 是传进去的值

一样也可以查询出来