springboot多表查询映射

• Z时代
• 2024-01-10
• 分类：综合

//实体类

public class Account implements Serializable {
    private Integer id;

    private String loginName;

    private String password;

    private String nickName;

    private Integer age;

    private String location;

    private String role;
    
    // 角色
    private List<Role> roleList;
    
    // 具体的权限
    private List<Permission> permissionList;

}

//xml中的sql语句 

<!--  自定义： 包含一对多关系 包含两个集合   roleList  permissionList-->
  <resultMap type="com.springboot.entity.Account" id="selectByPermissionResultMap">
  
    <id column="aid" jdbcType="INTEGER" property="id" />
    <result column="login_name" jdbcType="VARCHAR" property="loginName" />
    <result column="password" jdbcType="VARCHAR" property="password" />
    <result column="nick_name" jdbcType="VARCHAR" property="nickName" />
    <result column="age" jdbcType="INTEGER" property="age" />
    <result column="location" jdbcType="VARCHAR" property="location" />
    <result column="role" jdbcType="VARCHAR" property="role" />
  
      <collection property="roleList" ofType="com.springboot.entity.Role">
          <id column="rid" jdbcType="INTEGER" property="id" />
          <result column="rname" jdbcType="VARCHAR" property="name" />
      </collection>

      <collection property="permissionList" ofType="com.springboot.entity.Permission">
        <id column="pid" jdbcType="INTEGER" property="id" />
        <result column="uri" jdbcType="VARCHAR" property="uri" />
        <result column="pname" jdbcType="VARCHAR" property="name" />
        <result column="c" jdbcType="BIT" property="c" />
        <result column="r" jdbcType="BIT" property="r" />
        <result column="u" jdbcType="BIT" property="u" />
        <result column="d" jdbcType="BIT" property="d" />
      </collection>
  </resultMap>

  <select id="findByLoginNameAndPassword"  resultMap="selectByPermissionResultMap">
  SELECT
    a.id as aid, 
    a.login_name ,
    a.password,
    a.location,
    r.id as rid,
    r.name as rname,
    p.id as pid,
    p.uri,
    p.c,
    p.u,
    p.d,
    p.r,
    p.name as pname
FROM account as a 
    inner join account_role as ar 
        on a.id = ar.account_id
    inner join role as r 
        on ar.role_id = r.id
        
    left join role_permission rp 
        on r.id =rp.role_id
    left join permission p 
        on p.id = rp.permission_id
where a.login_name = #{loginName} and a.password = #{password}
  </select>

以上是 springboot多表查询映射 的全部内容， 来源链接： utcz.com/z/517908.html