Mybatis Dynamic SQL 一对多关联查询

June 20, 2023

Java, 数据库

Mybatis, 动态SQL, 一对多, 关联查询

Mybaits 一对多查询结果映射总结

1. 借助 XML Mapper 实现一对多的结果映射#

参考： https://mybatis.org/mybatis-dynamic-sql/docs/select.html （ XML Mapper for Join Statements 章节）

由于注解的限制，一对多的结果映射需要借助 XML 的 mapper 完成。

1.1 方法一#

在 Mybatis Generator (MBG) 的运行时配置为 MyBatis3DynamicSql 时，需要在生成的代码基础上做如下工作：

添加一个 mapper 类。不要修改生成的 mapper 文件，避免下次自动生成时被覆盖或者重新修改的麻烦。
添加一个 model 类。生成的 model 不包含对象之间的关联关系。
在 service 添加查询方法，通过 QueryExpressionDSL 提供的 join 等函数做关联查询。如果关联表有字段相同则需要提供别名。
添加 XML mapper 文件。文件内容是 resultMap 的定义。注意字段别名。

1.1.1 优缺点#

优点：只需要一次查询，效率高。

缺点：在分页的情况下会有问题。实际结果可能会少于分页参数的值。

1.1.2 示例代码#

mapper

/**
 * 自定义角色权限关联查询 mapper
 */
@Mapper
public interface CustomRoleMapper {

    @SelectProvider(type= SqlProviderAdapter.class, method="select")
    @ResultMap("RolePermissionResult")
    List<RolePermission> selectMany(SelectStatementProvider selectStatement);

    @SelectProvider(type=SqlProviderAdapter.class, method="select")
    @ResultMap("RolePermissionResult")
    Optional<RolePermission> selectOne(SelectStatementProvider selectStatement);
}

model

public class RolePermission extends SysRole{
    
    public List<SysPermission> permissionList;
}

service

@Override
public PageVO<RolePermissionVO> queryRolePermissionPage(QueryRolePageVO queryVO) {
    
    SelectStatementProvider selectStatementProvider = select(sysRole.allColumns(), sysPermission.permissionId, sysPermission.parentId,
                                                             sysPermission.name.as("p_name"), sysPermission.displayName.as("p_display_name"), sysPermission.description.as("p_description"),
                                                             sysPermission.enabled.as("p_enabled"), sysPermission.sort.as("p_sort"), sysPermission.createTime.as("p_create_time"))
        .from(sysRolePermission)
        .leftJoin(sysRole)
        .on(sysRolePermission.roleId, equalTo(sysRole.roleId))
        .leftJoin(sysPermission)
        .on(sysRolePermission.permissionId, equalTo(sysPermission.permissionId))
        .where(sysRole.name, isEqualToWhenPresent(queryVO.getSearchString()))
        .or(sysRole.displayName, isEqualToWhenPresent(queryVO.getSearchString()))
        .build()
        .render(RenderingStrategies.MYBATIS3);
    
    return PageUtil.queryPage(queryVO, () -> customRoleMapper.selectMany(selectStatementProvider), new TypeReference<>() {});
}

@Override
public RolePermissionVO queryRolePermissionInfo(Integer roleId) {
    SelectStatementProvider selectStatementProvider = select(sysRole.allColumns(), sysPermission.permissionId, sysPermission.parentId,
                                                             sysPermission.name.as("p_name"), sysPermission.displayName.as("p_display_name"), sysPermission.description.as("p_description"),
                                                             sysPermission.enabled.as("p_enabled"), sysPermission.sort.as("p_sort"), sysPermission.createTime.as("p_create_time"))
        .from(sysRolePermission)
        .leftJoin(sysRole)
        .on(sysRolePermission.roleId, equalTo(sysRole.roleId))
        .leftJoin(sysPermission)
        .on(sysRolePermission.permissionId, equalTo(sysPermission.permissionId))
        .where(sysRole.roleId, isEqualTo(roleId))
        .build()
        .render(RenderingStrategies.MYBATIS3);
    
    Optional<RolePermission> rolePermission = customRoleMapper.selectOne(selectStatementProvider);
    if (rolePermission.isEmpty()) {
        throw new UpmsException(UpmsExceptionEnum.ROLE_RETRIEVAL_FAILED);
    }
    
    return BeanUtil.copyProperties(rolePermission.get(), RolePermissionVO.class);
}

XML mapper

<?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.gitee.amos18284.upms.mapper.CustomRoleMapper">
  <resultMap id="RolePermissionResult" type="com.gitee.amos18284.upms.model.RolePermission">
    <id column="role_id" jdbcType="INTEGER" property="roleId" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="display_name" property="displayName" jdbcType="VARCHAR" />
    <result column="description" property="description" jdbcType="VARCHAR" />
    <result column="enabled" property="enabled" jdbcType="BIT" />
    <result column="sort" property="sort" jdbcType="TINYINT" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    <collection property="permissionList" ofType="com.gitee.amos18284.upms.model.SysPermission">
      <id column="permission_id" property="permissionId" jdbcType="INTEGER" />
      <result column="parent_id" property="parentId" jdbcType="INTEGER" />
      <result column="p_name" property="name" jdbcType="VARCHAR" />
      <result column="p_display_name" property="displayName" jdbcType="VARCHAR" />
      <result column="p_description" property="description" jdbcType="VARCHAR" />
      <result column="p_enabled" property="enabled" jdbcType="BIT" />
      <result column="p_sort" property="sort" jdbcType="TINYINT" />
      <result column="p_create_time" property="createTime" jdbcType="TIMESTAMP" />
    </collection>
  </resultMap>
</mapper>

1.2 方法二#

另一种实现方式可以解决分页问题，但是会有 1+N 问题。实现如下：

需要新建两个 mapper 类
新建一个 model 类
在 service 调用新建的 mapper 方法
新建两个 XML mapper 文件，一个 resultMap，一个 select

1.2.1 优缺点#

优点：分页查询正确，Java 代码量少
缺点：1+N 问题，查询 10 条数据，会查询 11 次数据库。需要手写 SQL。

1.2.2 示例代码#

mapper

/**
* 自定义角色权限关联查询 mapper
*/
@Mapper
public interface CustomRoleMapper {
    
    @SelectProvider(type= SqlProviderAdapter.class, method="select")
    @ResultMap("RolePermissionResult")
    List<RolePermission> selectMany(SelectStatementProvider selectStatement);
    
    @SelectProvider(type=SqlProviderAdapter.class, method="select")
    @ResultMap("RolePermissionResult")
    Optional<RolePermission> selectOne(SelectStatementProvider selectStatement);
}

@Mapper
public interface CustomPermissionMapper {
    
    Optional<SysPermission> selectByPrimaryKey(Integer roleId);
}

model

public class RolePermission extends SysRole{
    
    public List<SysPermission> permissionList;
}

service

@Override
public PageVO<RolePermissionVO> queryRolePermissionPage(QueryRolePageVO queryVO) {
    
    SelectStatementProvider selectStatementProvider = select(sysRole.allColumns())
        .from(sysRole)
        .where(sysRole.name, isEqualToWhenPresent(queryVO.getSearchString()))
        .or(sysRole.displayName, isEqualToWhenPresent(queryVO.getSearchString()))
        .build()
        .render(RenderingStrategies.MYBATIS3);
    
    return PageUtil.queryPage(queryVO, () -> customRoleMapper.selectMany(selectStatementProvider), new TypeReference<>() {});
}

XML mapper

<?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.gitee.amos18284.upms.mapper.CustomRoleMapper">
  <resultMap id="RolePermissionResult" type="com.gitee.amos18284.upms.model.RolePermission">
    <id column="role_id" jdbcType="INTEGER" property="roleId" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="display_name" property="displayName" jdbcType="VARCHAR" />
    <result column="description" property="description" jdbcType="VARCHAR" />
    <result column="enabled" property="enabled" jdbcType="BIT" />
    <result column="sort" property="sort" jdbcType="TINYINT" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    <collection property="permissionList" ofType="com.gitee.amos18284.upms.model.SysPermission"
                select="com.gitee.amos18284.upms.mapper.CustomPermissionMapper.selectByPrimaryKey" column="role_id">
    </collection>
  </resultMap>
</mapper>

<?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.gitee.amos18284.upms.mapper.CustomPermissionMapper">
  <select id="selectByPrimaryKey" resultType="com.gitee.amos18284.upms.model.SysPermission">
    select
    p.permission_id as permissionId, p.parent_id as parentId, p.name, p.display_name as displayName, p.description, p.enabled, p.sort, p.create_time as createTime
    from
    sys_permission as p
    left join
    sys_role_permission as srp
    on
    p.permission_id = srp.permission_id
    where
    srp.role_id = #{roleId}
  </select>
</mapper>

2. Java 代码实现一对多结果映射#

2.1 优缺点#

相较与 XML mapper，Java 代码实现一对多结果映射有如下优点：

保证分页的准确性
减少对数据库的请求次数
无需新增 mapper 及 XML mapper 文件
无需手写 SQL

具体实现如下：

新建一个 model
新建一个关联关系查询的 service
在 service 查询方法中，调用上一步 service 的方法，获取到关联的 id 集合，再通过 id 集合查询实体

2.2 示例代码#

model

public class RolePermission extends SysRole{
    
    public List<SysPermission> permissionList;
}

role service

@Override
public PageVO<RolePermissionVO> queryRolePermissionPage(QueryRolePageVO queryVO) {
    
    PageVO<RoleVO> page = roleService.queryPage(queryVO);
    
    List<RoleVO> data = page.getData();
    Set<Integer> roleIdSet = data.stream().map(RoleVO::getRoleId).collect(Collectors.toSet());
    
    List<RolePermissionVO> newData = getRolePermissionListByRoleSet(roleIdSet, data);
    
    return PageUtil.createPage(page, newData);
}

private Map<Integer, Set<Integer>> mapPermissionSetByRoleSet(Set<Integer> roleIdSet) {
    
    Set<Integer> collect = roleIdSet.stream().filter(Objects::nonNull).collect(Collectors.toSet());
    if (collect.isEmpty()) {
        return new HashMap<>();
    }
    
    SelectStatementProvider selectStatementProvider = selectDistinct(roleId, permissionId)
        .from(sysRolePermission)
        .where(roleId, isIn(collect))
        .build()
        .render(RenderingStrategies.MYBATIS3);
    
    Map<Integer, Set<Integer>> rolePermissionMap = new HashMap<>();
    rolePermissionMapper.selectMany(selectStatementProvider).forEach(r -> {
        Integer roleId = r.getRoleId();
        Integer permissionId = r.getPermissionId();
        if (rolePermissionMap.containsKey(roleId)) {
            rolePermissionMap.get(roleId).add(permissionId);
        } else {
            rolePermissionMap.put(roleId, new LinkedHashSet<>(Collections.singleton(permissionId)));
        }
    });
    
    return rolePermissionMap;
}

private List<RolePermissionVO> getRolePermissionListByRoleSet(Set<Integer> roleIdSet, List<RoleVO> data) {
    Map<Integer, Set<Integer>> rolePermissionMap = mapPermissionSetByRoleSet(roleIdSet);
    
    Set<Integer> permissionIdSet = new HashSet<>();
    rolePermissionMap.values().forEach(permissionIdSet::addAll);
    
    List<PermissionVO> permissionList = permissionService.listPermissionByIdSet(permissionIdSet);
    
    Map<Integer, PermissionVO> permissionMap = permissionList.stream().collect(Collectors.toMap(PermissionVO::getPermissionId, p -> p));
    
    List<RolePermissionVO> newData = Lists.newArrayList();
    data.forEach(roleVO -> {
        Integer roleId = roleVO.getRoleId();
        
        RolePermissionVO rolePermissionVO = BeanUtil.copyProperties(roleVO, RolePermissionVO.class);
        
        if (rolePermissionMap.containsKey(roleId)) {
            Set<Integer> idSet = rolePermissionMap.get(roleId);
            List<PermissionVO> permissionVOList = Lists.newArrayList();
            idSet.forEach(id -> {
                if (permissionMap.containsKey(id)) {
                    permissionVOList.add(permissionMap.get(id));
                }
            });
            
            rolePermissionVO.setPermissionList(permissionVOList);
        }
        
        newData.add(rolePermissionVO);
    });
    return newData;
}

@Override
public RolePermissionVO queryRolePermissionInfo(Integer roleId) {
    
    RoleVO roleVO = roleService.queryInfo(roleId);
    RolePermissionVO rolePermissionVO = BeanUtil.copyProperties(roleVO, RolePermissionVO.class);
    
    List<RolePermissionVO> data = getRolePermissionListByRoleSet(new HashSet<>(Collections.singleton(roleId)), Lists.newArrayList(rolePermissionVO));
    
    return data.get(0);
}

permission service

@Override
public List<PermissionVO> listPermissionByIdSet(Set<Integer> permissionIdSet) {
    
    Set<Integer> collect = permissionIdSet.stream().filter(Objects::nonNull).collect(Collectors.toSet());
    if (collect.isEmpty()) {
        return Lists.newArrayList();
    }
    
    SelectStatementProvider selectStatementProvider = select(sysPermission.allColumns())
        .from(sysPermission)
        .where(permissionId, isIn(collect))
        .build()
        .render(RenderingStrategies.MYBATIS3);
    
    return Convert.convert(new TypeReference<>() {}, mapper.selectMany(selectStatementProvider));
}