LigurUi + spring mybatis mysql 菜单递归查询

原创于 2017-11-30 17:26:05 发布 · 784 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #mybatis #spring #ligerui框架

mybatis 同时被 2 个专栏收录

1 篇文章

订阅专栏

spring

1 篇文章

订阅专栏

本文介绍如何在MyBatis中使用子查询映射实现菜单树形结构的数据获取，包括配置文件mapper.xml中的resultMap元素及collection标签的详细设置，并提供具体的Java实体类和Controller层实现代码。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

原理解释：
1.在mapper.xml中，使用resultMap指定映射到实体
2.实体中有一个属性为childern泛型为其本身的list集合
3.在resultMap中使用collection标签映射集合
4.collection标签中通过select属性调用查询语句获取list集合的值，调用时会使用属性column指定传入的参数！
在父子关系查询中，先查询出父，然后再根据parent_id去查询子

<collection 
           property="children"  
           column="sid" 
           ofType="com.wuxing.loan.utils.MenuTree" 
           javaType="java.util.ArrayList" 
           select="findChildren"> 
</collection>

详细介绍
一、项目环境

  <spring.version>4.3.10.RELEASE</spring.version> 
  <mybatis.version>3.2.2</mybatis.version>
  <mybatis-spring-version>1.2.0</mybatis-spring-version>
  <mysql-connector-java-version>5.1.38</mysql-connector-java-version>

二、菜单表

建表语句：

 CREATE TABLE `XD_MENU_RESOURCE` (
  `SID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `PARENT_SID` int(11) DEFAULT NULL COMMENT '父类菜单主键',
  `RELATION_CODE` varchar(256) DEFAULT NULL,
  `MENU_TYPE` char(1) DEFAULT NULL COMMENT '0：前台菜单；1：后台菜单',
  `RES_TYPE` char(1) DEFAULT NULL COMMENT '资源类型  0：分隔线；1：菜单；2：按钮；3：无连接菜单',
  `RES_CODE` varchar(64) DEFAULT NULL COMMENT '菜单编号',
  `RES_NAME` varchar(128) DEFAULT NULL COMMENT '菜单名称',
  `RES_URL` varchar(256) DEFAULT NULL COMMENT '菜单请求url',
  `RES_ICO` varchar(128) DEFAULT NULL COMMENT '菜单图标',
  `RES_LEVEL` int(11) DEFAULT NULL COMMENT '1:一级菜单 2:二级菜单 ',
  `SERIAL_NUM` int(11) DEFAULT NULL COMMENT '显示顺序',
  `IS_SHOW` char(1) DEFAULT NULL COMMENT '0：不显示；1：显示',
  `STATUS` char(1) DEFAULT NULL COMMENT '0：不可用；1：可用',
  `CREATE_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CREATE_BY` varchar(32) DEFAULT NULL,
  `UPDATE_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATE_BY` varchar(32) DEFAULT NULL,
  `VERSIONES` int(11) DEFAULT NULL,
  PRIMARY KEY (`SID`)
) ENGINE=InnoDB AUTO_INCREMENT=2038 DEFAULT CHARSET=utf8 COMMENT='资源';

三、mapper.xml

<resultMap type="com.wuxing.loan.utils.MenuTree" id="menu_map">  
    <id column="sid" property="sid"/>  
    <result column="PARENT_SID" property="parentSid"/>  
    <result column="RELATION_CODE" property="relationCode"/>  
    <result column="MENU_TYPE" property="menuType" />
    <result column="RES_TYPE_DES" property="resTypeDes" />
    <result column="RES_CODE" property="resCode" />
    <result column="RES_NAME" property="resName" />
    <result column="RES_URL" property="resUrl" />
    <result column="RES_ICO" property="resIco" />
    <result column="RES_LEVEL" property="resLevel" />
    <result column="SERIAL_NUM" property="serialNum" />
    <result column="IS_SHOW_DES" property="isShowDes" />
    <result column="STATUS_DES" property="statusDes" />
    <result column="CREATE_DATE_DES" property="createDateDes" />
    <result column="CREATE_BY" property="createBy" />
    <result column="UPDATE_DATE" property="updateDate" />
    <result column="UPDATE_BY" property="updateBy" />
    <result column="VERSIONES" property="versions" />
    <collection 
         property="children"  
         column="sid" 
         ofType="com.wuxing.loan.utils.MenuTree" 
         javaType="java.util.ArrayList"  
         select="findChildren"> 
    </collection>  
  </resultMap>  
  <select id="findMenuByList" parameterType="Map" resultMap="menu_map">
    SELECT T.SID,
           T.PARENT_SID,
           T.RELATION_CODE,
           T.RES_TYPE,
           CASE T.RES_TYPE
             WHEN '0' THEN
              '分割线'
             WHEN '1' THEN
              '菜单'
             WHEN '2' THEN
              '按钮'
             WHEN '3' THEN
              '引导菜单'
             ELSE
              '引导菜单'
           END AS RES_TYPE_DES,
           T.RES_CODE,
           T.RES_NAME,
           T.RES_URL,
           T.RES_ICO,
           T.RES_LEVEL,
           T.SERIAL_NUM,
           T.IS_SHOW,
           CASE T.IS_SHOW
             WHEN '1' THEN
              '是'
             WHEN '0' THEN
              '否'
             ELSE
              '否'
           END AS IS_SHOW_DES,
           T.STATUS,
           CASE T.STATUS
             WHEN '1' THEN
              '有效'
             WHEN '0' THEN
              '无效'
             ELSE
              '无效'
           END AS STATUS_DES,
           T.CREATE_DATE,
           IFNULL(DATE_FORMAT(create_date, '%Y-%m-%d %T'),
            '') AS CREATE_DATE_DES,
           T.CREATE_BY
      FROM XD_MENU_RESOURCE T
      WHERE 1=1
      <if test="sid != null">
      AND T.PARENT_SID = 1
      </if>
      <if test ="resName != null">
      AND T.RES_NAME like concat(concat('%',#{resName}),'%')
      </if>
      <if test="resUrl != null">
      AND T.RES_URL like concat(concat('%',#{resUrl}),'%')
      </if>
      order by T.SERIAL_NUM ASC
  </select>

  <select id="findChildren" parameterType="int" resultMap="menu_map">
    SELECT T.SID,
           T.PARENT_SID,
           T.RELATION_CODE,
           T.RES_TYPE,
           CASE T.RES_TYPE
             WHEN '0' THEN
              '分割线'
             WHEN '1' THEN
              '菜单'
             WHEN '2' THEN
              '按钮'
             WHEN '3' THEN
              '引导菜单'
             ELSE
              '引导菜单'
           END AS RES_TYPE_DES,
           T.RES_CODE,
           T.RES_NAME,
           T.RES_URL,
           T.RES_ICO,
           T.RES_LEVEL,
           T.SERIAL_NUM,
           T.IS_SHOW,
           CASE T.IS_SHOW
             WHEN '1' THEN
              '是'
             WHEN '0' THEN
              '否'
             ELSE
              '否'
           END AS IS_SHOW_DES,
           T.STATUS,
           CASE T.STATUS
             WHEN '1' THEN
              '有效'
             WHEN '0' THEN
              '无效'
             ELSE
              '无效'
           END AS STATUS_DES,
           T.CREATE_DATE,
           IFNULL(DATE_FORMAT(create_date, '%Y-%m-%d %T'),
            '') AS CREATE_DATE_DES,
           T.CREATE_BY
      FROM XD_MENU_RESOURCE T
      WHERE 1=1
      AND T.PARENT_SID = #{sid} 
      order by T.SERIAL_NUM ASC

  </select>

四、java代码
1、实体

public class MenuTree {

    private int sid;
    private int parentSid;
    private String relationCode;
    private String menuType;
    private String resTypeDes;
    private String resCode;
    private String resName;
    private String resUrl;
    private String resIco;
    private int resLevel;
    private int serialNum;
    private String isShowDes;
    private String statusDes;
    private String createDateDes;
    private String createBy;
    private Date updateDate;
    private String updateBy;
    private int versions;
    //子菜单
    private List<MenuTree> children;
    //省略get／set
    }

2.controller层代码

@Controller
@RequestMapping("/menu")
public class MenuController extends BaseAction{
@RequestMapping(value = "/list", method = RequestMethod.POST, produces = { "application/json;charset=UTF-8" })
    @ResponseBody
    public String findPage(HttpServletRequest request,HttpServletResponse response){
        return findByPage(request, new DoPage(){
            @SuppressWarnings("rawtypes")
            public Page doInitPage(HttpServletRequest request, JSONObject jsonData, Page page) {
                Map<String, Object> params = CopyUtils.copyMap(request, "qm");
                if(params.isEmpty()){
                    params.put("sid", 1);
                }
                List menuList = superService.selectByPage("sqlmapper.XdMenuMapper.findMenuByList", params, page);
                jsonData.put(ROWS, menuList);
                return page;
            }
        });
    }
}

核心代码：

List menuList = superService.selectByPage("sqlmapper.XdMenuMapper.findMenuByList", params, page);

五、前台ligurUi

<script language="javascript" type="text/javascript">
 var grid = null;

 $(function() {
    grid = $("#maingrid").ligerGrid({
        url: "${webPath}/menu/list",// TODO 注意
        columns: [
            {display: "操作",minWidth:80,width:80,align: 'center',isSort: false,isAllowHide: false,
                render:function(row,index){
                    var hrd =  '<img class="grid_row_img" title="添加子菜单" onclick="addPage(null,'+row.sid+')" src="${webPath}/scripts/ligerUI/skins/icons/add.gif"/>';
                     hrd = hrd + '<img class="grid_row_img" title="修改"  onclick="updatePage(null,'+row.sid+')" src="${webPath}/scripts/ligerUI/skins/icons/modify.gif"/>';
                    hrd = hrd + '<img class="grid_row_img" title="删除" onclick="deleteAjax(null,'+row.sid+')" src="${webPath}/scripts/ligerUI/skins/icons/delete.gif"/>';
                    return hrd;
                }
            },                
            //{display: "菜单名称",name: "resName",id:"resNameId",minWidth: 180,align: 'left',isSort: false,isAllowHide: false},
            {display: "名称",id:"resNameId",minWidth:180,width:200,align: 'left',isSort: false,isAllowHide: false,
                render:function(row,index){
                    var hrd = '';
                    if(row.resIco && row.resIco.length>0){
                        hrd = '<img class="grid_row_img" style="margin:3px 0px -1px 0px;cursor: default;" src="${webPath}'+row.resIco+'"/>'+row.resName;
                    }else{
                        hrd = row.resName;
                    }
                    return hrd;
                }
            },
            {display: "编码",name: "resCode",minWidth:80,width:150,align: 'left',isSort: false,isAllowHide: false},
            {display: "类别",name: "resTypeDes",minWidth:80,width:100,align: 'center',isSort: false,isAllowHide: false},
            {display: "URL",name: "resUrl",minWidth:80,width:200,align: 'left',isSort: false,isAllowHide: false},
            {display: "级别",name: "resLevel",minWidth: 60,align: 'center',isSort: false,isAllowHide: false},
            {display: "序号",name: "serialNum",minWidth: 60,align: 'center',isSort: false,isAllowHide: false},
            {display: "状态",name: "statusDes",minWidth: 60,align: 'center',isSort: false,isAllowHide: false},
            {display: "创建人",name: "createBy",minWidth: 60,align: 'center',isSort: false,isAllowHide: false},
            {display: "创建日期",name: "createDateDes",minWidth: 160,align: 'center',isSort: false,isAllowHide: false}
        ],
        toolbar: {
            items: [
                    {text: "添加根菜单",click: addPage,icon: "add"},{line: true},
                    {text: "修改",click: updatePage,icon: "modify"},{line: true},
                    {text: "删除",click: deleteAjax,icon: "delete"},{line: true},
                    {text: "导出Excel",click: exportData,icon: "excl"},{line: true}
                    //{text: "打印",click: addPage,icon: "print"},{line: true}
            ] 
        }, 
        tree:{
            columnId:'resNameId',
            idField:'sid',
            parentIDField:'parentSid'
        },
        rowHeight:23,
        headerRowHeight:23,
        rownumbers:true, // 是否显示行序号
        //frozenRownumbers:false, // 行序号是否在固定列中 
        checkbox:true, // 是否显示复选框
        //checkboxColWidth:35,
        //isScroll:false, // 设置为false时将不会显示滚动条，高度自适应 
        selectRowButtonOnly:true, // 复选框模式时，是否只允许点击复选框才能选择行 
        allowAdjustColWidth:true,//  Int  是否允许调整列宽  
        //delayLoad:true,  // 初始化是是否不加载 
        //title:"信息列表：",
        //frozen:false,
        enabledSort:false,
        usePager:true,
        alternatingRow:true, // 是否附加奇偶行效果行 
        height:"98%",
        width:"100%",
        pageSize:100,
        pageSizeOptions: [5,10,15,20,30,100]
    });
 });
</scripts>
<table align="center" border="0" width="99%" id="listInfoId">
<tr>
<td><div id="maingrid" style="margin: 0px; padding: 0;"/></td>
</tr>
<tr>
</table>
</body>
</html>

这里写图片描述