mybatis一对多查询，list中的泛型是包装类

假如有两个实体类，用户和角色，其中用户可以有多个实体类，要使用到association标签

property代表属性名称
column 代表关联查询时要传入的key
javaType代表代表属性的数据类型
ofType 标明此时如果具体的泛型，如果没有可以不写
用户实体类

import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import javax.persistence.*;
import javax.persistence.Column;
import java.io.Serializable;
import java.util.*;

/**
 * @Description  
 * @Author  理想
 * @Date 2024-01-15 
 */

@Data
@Table( name ="t_sys_user" )
@ApiModel
public class SysUser implements Serializable {
   

	private static final long serialVersionUID =  8808084812889674919L;

	/**
	 * 用户ID
	 */
	@ApiModelProperty("用户ID")
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Column(name = "user_id" )
	private Long userId;

	/**
	 * 部门ID
	 */
	@ApiModelProperty("部门ID")
	@Column(name = "dept_id" )
	private Long deptId;

	@ApiModelProperty("部门名称")
	@Transient
	private String deptName;

	/**
	 * 用户账号
	 */
	@ApiModelProperty("登录名称")
	@Column(name = "user_name" )
	private String userName;

	/**
	 * 用户昵称
	 */
	@ApiModelProperty("用户名称")
	@Column(name = "nick_name" )

	private String nickName;

	/**
	 * 用户类型（00系统用户）
	 */
	@Column(name = "user_type" )
	private String userType;

	/**
	 * 用户邮箱
	 */
	@Column(name = "email" )
	@ApiModelProperty("用户邮箱")
	private String email;

	/**
	 * 手机号码
	 */
	@Column(name = "phonenumber" )
	@ApiModelProperty("用户手机号码")
	private String phonenumber;

	/**
	 * 用户性别（0男 1女 2未知）
	 */
	@ApiModelProperty("性别")
	@Column(name = "sex" )
	private String sex;

	/**
	 * 头像地址
	 */
	@ApiModelProperty("头像地址")
	@Column(name = "avatar" )
	private String avatar;

	/**
	 * 密码
	 */
	@ApiModelProperty("用户密码")
	@Column(name = "password" )
	private String password;

	/**
	 * 帐号状态（0正常 1停用）
	 */
	@ApiModelProperty("账号")
	@Column(name = "status" )
	private Integer status;

	/**
	 * 删除标志（0代表存在 2代表删除）
	 */
	@Column(name = "del_flag" )
	private String delFlag;

	/**
	 * 最后登录IP
	 */
	@ApiModelProperty("最后登录IP")
	@Column(name = "login_ip" )
	private String loginIp;

	/**
	 * 最后登录时间
	 */
	@ApiModelProperty("最后登录时间")
	@Column(name = "login_date" )
	@JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
	private Date loginDate;

	/**
	 * 创建者
	 */
	@ApiModelProperty("创建者")
	@Column(name = "create_by" )
	private String createBy;

	/**
	 * 创建时间
	 */
	@ApiModelProperty("创建时间")
	@Column(name = "create_time" )
	@JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
	private Date createTime;

	/**
	 * 更新者
	 */
	@Column(name = "update_by" )
	private String updateBy;

	/**
	 * 更新时间
	 */
	@ApiModelProperty("更新时间")
	@Column(name = "update_time" )
	@JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
	private Date updateTime;

	@ApiModelProperty("组织机构id")
	@Column(name = "organizational_structure_id" )
	private Integer organizationalStructureId;
	@Transient
	@ApiModelProperty("组织机构名称")
	private String organizationalStructureName;

	/**
	 * 加密因子
	 */
	@ApiModelProperty("加密因子")
	@Column(name = "salt" )
	private String salt;

	/**
	 * 备注
	 */
	@ApiModelProperty("备注")
	@Column(name = "remark" )
	private String remark;

	@ApiModelProperty("角色id集合")
	@Transient
	private List<Integer> sysRoleIds = new ArrayList<>();

	@ApiModelProperty("角色集合")
	@Transient
	private List<SysRole> sysRoles = new ArrayList<>();


	public Map<Integer,String> getStatusDic() {
   
		Map<Integer,String> map = new HashMap<>();
		map.put(0,"正常");
		map.put(1,"停用");
		return map;
	}

	public String getStatusName() {
   
		return getStatusDic().get(status);
	}

	/**
	 * 用户性别（0男 1女 2未知）
	 */
	public Map<String,String> getSexDic() {
   
		Map<String,String> map = new HashMap<>();
		map.put("0","男");
		map.put("1","女");
		map.put("2","未知");
		return map;
	}

	public String getSexName() {
   
		return getSexDic().get(sex);
	}



}

角色实体类



import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import javax.persistence.*;
import javax.persistence.Column;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

@Data
@ApiModel
@Table( name ="t_sys_role" )
public class SysRole {
   
    @ApiModelProperty("角色id")
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "role_id" )
    private Integer roleId;

    @Column(name = "role_name" )
    @ApiModelProperty("角色名称")
    private String roleName;

    @Column(name = "role_key" )
    @ApiModelProperty("角色标识")
    private String roleKey;

    @Column(name = "description" )
    @ApiModelProperty("角色角色描述")
    private String description;

    @Column(name = "status" )
    @ApiModelProperty("角色状态")
    private Integer status;

    @Column(name = "create_by" )
    @ApiModelProperty("角色创建者")
    private String createBy;

    @Column(name = "create_time" )
    @ApiModelProperty("创建时间")
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date createTime;

    @Column(name = "update_by" )
    @ApiModelProperty("更新者")
    private String updateBy;

    @Column(name = "update_time" )
    @ApiModelProperty("更新时间")
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date updateTime;

    public Map<Integer,String> getStatusDic() {
   
        Map<Integer,String> map = new HashMap<>();
        map.put(0,"正常");
        map.put(1,"停用");
        return map;
    }

    public String getStatusName() {
   
        return getStatusDic().get(status);
    }


}

可以看到用户实体类中分别有这两个属性

    @ApiModelProperty("角色id集合")
	@Transient
	private List<Integer> sysRoleIds = new ArrayList<>();

	@ApiModelProperty("角色集合")
	@Transient
	private List<SysRole> sysRoles = new ArrayList<>();

例子1：一对多查询用户的角色，，对应用户中的List sysRoles属性

 <resultMap id="BaseResultMap2" type="com.cn.dao.SysUser">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    <id column="user_id" jdbcType="BIGINT" property="userId" />
    <result column="dept_id" jdbcType="BIGINT" property="deptId" />
    <result column="user_name" jdbcType="VARCHAR" property="userName" />
    <result column="nick_name" jdbcType="VARCHAR" property="nickName" />
    <result column="user_type" jdbcType="VARCHAR" property="userType" />
    <result column="email" jdbcType="VARCHAR" property="email" />
    <result column="phonenumber" jdbcType="VARCHAR" property="phonenumber" />
    <result column="sex" jdbcType="CHAR" property="sex" />
    <result column="avatar" jdbcType="VARCHAR" property="avatar" />
    <result column="password" jdbcType="VARCHAR" property="password" />
    <result column="status" jdbcType="INTEGER" property="status" />
    <result column="del_flag" jdbcType="CHAR" property="delFlag" />
    <result column="login_ip" jdbcType="VARCHAR" property="loginIp" />
    <result column="login_date" jdbcType="TIMESTAMP" property="loginDate" />
    <result column="create_by" jdbcType="VARCHAR" property="createBy" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="update_by" jdbcType="VARCHAR" property="updateBy" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
    <result column="remark" jdbcType="VARCHAR" property="remark" />
    <result column="organizational_structure_id" jdbcType="INTEGER" property="organizationalStructureId" />
    <result column="salt" jdbcType="VARCHAR" property="salt" />
    <collection property="sysRoles" column="user_id" javaType="java.util.List" ofType="com.cn.dao.SysRole" select="selectUserRoles">
      <id column="role_id" jdbcType="INTEGER" property="roleId" />
      <result column="role_name" jdbcType="VARCHAR" property="roleName" />
      <result column="description" jdbcType="VARCHAR" property="description" />
      <result column="status" jdbcType="INTEGER" property="status" />
      <result column="create_by" jdbcType="VARCHAR" property="createBy" />
      <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
      <result column="update_by" jdbcType="VARCHAR" property="updateBy" />
      <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
    </collection>

  </resultMap>

 <select id="selectUserRoles" resultType="com.cn.dao.SysRole" parameterType="java.lang.Integer" >
    select
    tsr.* from
    t_sys_user_relation_sys_role tsursr ,t_sys_role tsr
    where tsursr.sys_user_id = #{
   user_id} and tsr.role_id = tsursr.sys_role_id
  </select>

例子1：一对多查询用户的角色id的 list，对应List sysRoleIds属性

<resultMap id="BaseResultMap" type="com.cn.dao.SysUser">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    <id column="user_id" jdbcType="BIGINT" property="userId" />
    <result column="dept_id" jdbcType="BIGINT" property="deptId" />
    <result column="user_name" jdbcType="VARCHAR" property="userName" />
    <result column="nick_name" jdbcType="VARCHAR" property="nickName" />
    <result column="user_type" jdbcType="VARCHAR" property="userType" />
    <result column="email" jdbcType="VARCHAR" property="email" />
    <result column="phonenumber" jdbcType="VARCHAR" property="phonenumber" />
    <result column="sex" jdbcType="CHAR" property="sex" />
    <result column="avatar" jdbcType="VARCHAR" property="avatar" />
    <result column="password" jdbcType="VARCHAR" property="password" />
    <result column="status" jdbcType="INTEGER" property="status" />
    <result column="del_flag" jdbcType="CHAR" property="delFlag" />
    <result column="login_ip" jdbcType="VARCHAR" property="loginIp" />
    <result column="login_date" jdbcType="TIMESTAMP" property="loginDate" />
    <result column="create_by" jdbcType="VARCHAR" property="createBy" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="update_by" jdbcType="VARCHAR" property="updateBy" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
    <result column="remark" jdbcType="VARCHAR" property="remark" />
    <result column="organizational_structure_id" jdbcType="INTEGER" property="organizationalStructureId" />
    <result column="salt" jdbcType="VARCHAR" property="salt" />
    <collection property="sysRoleIds" column="user_id" javaType="java.util.List" ofType="Integer" select="selectUserRolesId"/>
  </resultMap>

<select id="selectUserRolesId" resultType="java.lang.Integer" parameterType="java.lang.Integer" >
    select
    sys_role_id from
    t_sys_user_relation_sys_role
    where sys_user_id = #{user_id}
  </select>

菜鸟记录，如有错误请指出，十分感谢

mybatis一对多查询，list中的泛型是包装类

相关推荐

最近更新

热门阅读