①普通一对多子查询
一个用户对应多个角色
RoleMapper类:
@Mapper
public interface RoleMapper extends BaseMapper<Role> {
List<Role> getRolesByHrId(Integer hrId);
}
RoleMapper.xml:
根据用户id通过中间表查询该用户的所有角色
<resultMap id="BaseResultMap" type="com.***.entity.Role">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="nameZh" property="nameZh"/>
</resultMap>
<select id="getRolesByHrId" parameterType="Integer" resultMap="BaseResultMap">
SELECT r.*
FROM hr_role hrr
LEFT JOIN role r ON hrr.rid = r.id
WHERE hrr.hrid = #{hrId}
</select>
Hr类:
在实体类中添加一对多的字段属性,由于该属性在数据库中并不存在,故用注解标记。
@TableField(exist = false)
private List<Role> roles;
HrMapper类:
根据用户名查询角色
@Mapper
public interface HrMapper extends BaseMapper<Hr> {
Hr loadUserByUsername(String username);
}
HrMapper.xml
id为hrWithRoles的resultMap中添加collection标签,其中select属性为getRolesByHrId这个方法的引用路径,column属性为这个方法的参数,若该方法不需要参数则可以不写。
<resultMap id="BaseResultMap" type="com.***.entity.Hr">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="phone" property="phone"/>
<result column="telephone" property="telephone"/>
<result column="address" property="address"/>
<result column="enabled" property="enabled"/>
<result column="username" property="username"/>
<result column="password" property="password"/>
<result column="userface" property="userface"/>
<result column="remark" property="remark"/>
</resultMap>
<resultMap id="hrWithRoles" type="hr" extends="BaseResultMap">
<collection property="roles" ofType="role" javaType="list" column="id" select="com.***.mapper.RoleMapper.getRolesByHrId"/>
</resultMap>
<select id="loadUserByUsername" parameterType="String" resultMap="hrWithRoles">
SELECT
<include refid="Base_Column_List"/>
FROM hr WHERE hr.username=#{username}
</select>
更多推荐
mybatis子查询
发布评论