mybatis 多层(二层)级联查询

一、java实体

/**
 * <p>
 * 菜单表
 * </p>
 *
 * @author lohas
 */
@Data
@EqualsAndHashCode(callSuper = true)
@TableName("t_menu")
@ApiModel(value = "MenuEntity对象", description = "菜单表")
public class MenuEntity extends Model {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "主键")
    @TableId(value = "id", type = IdType.INPUT)
    private String id;

    @ApiModelProperty(value = "菜单路由路径")
    private String path;

    @ApiModelProperty(value = "图标路径")
    private String icon;

    @ApiModelProperty(value = "上级菜单ID")
    private String parentId;

    @ApiModelProperty(value = "菜单等级")
    private Integer level;

    @ApiModelProperty(value = "菜单排序权重")
    private Integer sortWeight;

    @ApiModelProperty(value = "菜单标识(只能由字母数字下划线组成)")
    private String menuId;

    @ApiModelProperty(value = "菜单名称")
    private String menuName;

    @ApiModelProperty(value = "状态(1、启用,0、禁用)")
    private String status;

    @ApiModelProperty(value = "创建人ID")
    private String creatorId;

    @ApiModelProperty(value = "创建时间")
    @JsonDeserialize(using = LocalDateTimeSerializerConfig.LocalDateTimeDeserializer.class)
    @JsonSerialize(using = LocalDateTimeSerializerConfig.LocalDateTimeSerializer.class)
    private LocalDateTime createTime;

    @ApiModelProperty(value = "更新人ID")
    private String updateId;

    @ApiModelProperty(value = "更新时间")
    @JsonDeserialize(using = LocalDateTimeSerializerConfig.LocalDateTimeDeserializer.class)
    @JsonSerialize(using = LocalDateTimeSerializerConfig.LocalDateTimeSerializer.class)
    private LocalDateTime updateTime;

    @ApiModelProperty(value = "删除状态(0:删除、1: 正常)")
    private String deleteFlag;

    @ApiModelProperty(value = "租户ID")
    private String tenantId;
}

/**
 * @author lohas
 * @description
 */
@Data
@ApiModel(value = "MenuVo对象", description = "菜单包含子菜单实体")
public class MenuVo extends MenuEntity {

    @ApiModelProperty(value = "子菜单列表")
    private List<MenuEntity> children;
}

二、mybatis xml

<?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.lohas.mapper.MenuMapper">
    <resultMap type="com.lohas.vo.MenuVo" id="menuVoList">
        <id column="id" jdbcType="VARCHAR" property="id"/>
        <result column="path" jdbcType="VARCHAR" property="path"/>
        <result column="icon" jdbcType="VARCHAR" property="icon"/>
        <result column="parent_id" jdbcType="VARCHAR" property="parentId"/>
        <result column="level" jdbcType="INTEGER" property="level"/>
        <result column="sort_weight" jdbcType="INTEGER" property="sortWeight"/>
        <result column="menu_id" jdbcType="VARCHAR" property="menuId"/>
        <result column="menu_name" jdbcType="VARCHAR" property="menuName"/>
        <result column="status" jdbcType="CHAR" property="status"/>
        <result column="creator_id" jdbcType="VARCHAR" property="creatorId"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="update_id" jdbcType="VARCHAR" property="updateId"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
        <result column="delete_flag" jdbcType="VARCHAR" property="deleteFlag"/>
        <result column="tenant_id" jdbcType="VARCHAR" property="tenantId"/>
        <collection property="children" ofType="com.lohas.entity.MenuEntity"
                    column="id" select="getMenuByParentId"/>
    </resultMap>

    <select id="getMenuByParentId" resultType="com.lohas.entity.MenuEntity">
        select *
        from t_menu
        where parent_id = #{parentId}
    </select>

    <select id="getMenuList" resultMap="menuVoList">
        select * from t_menu
        where parent_id = '0'
        <if test="menuId != null and menuId !=''">
            and menu_id = #{menuId}
        </if>
        <if test="menuName != null and menuName !=''">
            and menu_name = #{menuName}
        </if>
        <if test="tenantId != null and tenantId !=''">
            and tenant_id = #{tenantId}
        </if>
    </select>
</mapper>

三、mybatis mapper

/**
 * <p>
 * 菜单表 Mapper 接口
 * </p>
 *
 * @author lohas
 */
public interface MenuMapper extends BaseMapper<MenuEntity> {

    /**
     * 根据菜单父ID查询菜单
     *
     * @param parentId
     * @return
     */
    MenuEntity getMenuByParentId(@Param("parentId") String parentId);

    /**
     * 分页查询菜单(包含子菜单)
     *
     * @param page
     * @param menuId
     * @param menuName
     * @param tenantId
     * @return
     */
    Page<MenuVo> getMenuList(Page<MenuVo> page, @Param("menuId") String menuId, @Param("menuName") String menuName, @Param("tenantId") String tenantId);
}

四、调用方法

Page<MenuVo> page = this.baseMapper.getMenuList(new Page<>(param.getPage(), param.getPageSize()), param.getMenuId(), param.getMenuName(), param.getTenantId());

五、总结

<collection property="children" ofType="com.lohas.entity.MenuEntity"
            column="id" select="getMenuByParentId"/>

column="id" 是要传递到子查询(select ==>>getMenuByParentId)的参数

<select id="getMenuByParentId" resultType="com.lohas.entity.MenuEntity">
    select *
    from t_menu
    where parent_id = #{parentId}
</select>
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值