多表一个XML即可构建树

迈不过友情╰ 2022-05-30 22:19 36阅读 0赞

上一回写过一次构建树的方法，我提到过代码复用性太高了，因为六张表，差不多的SQL语句，重复调用，代码也不好看，而且new了很多个TreeNode对象，臃肿，后来想到了在Mybatis XML映射中做修改，将表名和字段名都让自己来赋值，试了试，没想到这样的操作还真的有。

XML：

<select id="findTree" resultType="com.uhope.uip.item.core.TreeNode" parameterType="java.util.HashMap">
      SELECT
        ${columnName} AS name,
        ${columnCode} AS id
      FROM ${tableName}
           <where>
             <if test="column != null">AND ${column} = #{code}</if>
             <if test="state != null">AND state = #{state}</if>
           </where>
    </select>

最后总要塞到TreeNode类里，那就直接返回结果到这个类里好了，另外这样需要的值用HashMap传入，这就是标签里要做的事情，SQL语句里用AS来把数据转换成TreeNode类里的属性，不然返回存储字段不匹配，接着根据条件传参，例如我都只要返回name和id的，表字段虽然不一样，但是可以赋值，就用$符号，充分利用Mybatis XML的特性

TreeNode类：

public class TreeNode implements Serializable {
        private String name;
        private String id ;
        private String pId;
        private String parantpaths;
    
        private Boolean checked = false;
        private List<TreeNode> children;
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public String getId() {
            return id;
        }
    
        public void setId(String id) {
            this.id = id;
        }
    
        public String getpId() {
            return pId;
        }
    
        public void setpId(String pId) {
            this.pId = pId;
        }
    
        public String getParantpaths() {
            return parantpaths;
        }
    
        public void setParantpaths(String parantpaths) {
            this.parantpaths = parantpaths;
        }
    
        public List<TreeNode> getChildren() {
            return children;
        }
    
        public void setChildren(List<TreeNode> children) {
            this.children = children;
        }
    
        public void addChild(TreeNode child) {
            if (children == null) {
                children = Lists.newArrayList();
            }
            children.add(child);
        }
    
        public Boolean getChecked() {
            return checked;
        }
    
        public void setChecked(Boolean checked) {
            this.checked = checked;
        }
    
        
    }

setChildren和addchild使用时要根据需要来操作

看实现的代码：

public Result treeOne() {
        Long startTime = System.currentTimeMillis();
        HashMap<String,Object> basinMap = new HashMap<>();
        basinMap.put("columnName","basin_name");
        basinMap.put("columnCode","basin_code");
        basinMap.put("tableName","md_drainage_basin");
        basinMap.put("state",Constant.STATE_RUN);
        List<TreeNode> basin = mdDrainageBasinMapper.findTree(basinMap);
    
        for (int i = 0; i < basin.size(); i++) {
            HashMap<String,Object> waterMap = new HashMap<>();
            waterMap.put("columnName","water_name");
            waterMap.put("columnCode","water_code");
            waterMap.put("column","basin_code");
            waterMap.put("code",basin.get(i).getId());
            waterMap.put("tableName","md_water_system");
            waterMap.put("state",Constant.STATE_RUN);
            List<TreeNode> water = mdDrainageBasinMapper.findTree(waterMap);
            basin.get(i).setChildren(water);
    
            for (int j = 0; j < water.size(); j++) {
                HashMap<String,Object> riverMap = new HashMap<>();
                riverMap.put("columnName","river_name");
                riverMap.put("columnCode","river_code");
                riverMap.put("column","water_code");
                riverMap.put("code",water.get(j).getId());
                riverMap.put("tableName","md_river");
                riverMap.put("state",Constant.STATE_RUN);
                List<TreeNode> river = mdDrainageBasinMapper.findTree(riverMap);
    
                HashMap<String,Object> lakesMap = new HashMap<>();
                lakesMap.put("columnName","lakes_name");
                lakesMap.put("columnCode","lakes_code");
                lakesMap.put("column","water_code");
                lakesMap.put("code",water.get(j).getId());
                lakesMap.put("tableName","md_lakes");
                lakesMap.put("state",Constant.STATE_RUN);
                List<TreeNode> lakes = mdDrainageBasinMapper.findTree(lakesMap);
    
                HashMap<String,Object> reservoirMap = new HashMap<>();
                reservoirMap.put("columnName","reservoir_name");
                reservoirMap.put("columnCode","reservoir_code");
                reservoirMap.put("column","water_code");
                reservoirMap.put("code",water.get(j).getId());
                reservoirMap.put("tableName","md_reservoir");
                reservoirMap.put("state",Constant.STATE_RUN);
                List<TreeNode> reservoir = mdDrainageBasinMapper.findTree(reservoirMap);
                reservoir.addAll(lakes);
                reservoir.addAll(river);
                water.get(j).setChildren(reservoir);
            }
        }
        Long endTime = System.currentTimeMillis();
        System.out.println(endTime - startTime);
        return ResponseMsgUtil.success(basin);
    }

虽然多创建了几个Map，和上一回多创建了几个TreeNode对象没多少区别，但是只用到了一个Mapper，减少了复用性，少写了几个XML文件的查询，在每个Map中，put自己需要的参数给mybatis去数据库中查询，值不对，那就查不到了，所以一定要和XML对应上，循环的方式还是一层一层的树形查询，不同的是addAll这个方法，由于这次是没有用到TreeNode对象，而是直接装入List中的数据对象中setChildren方法，河湖库又是同级，要是这样set的话上次的就会被覆盖，所以用到了List中的addAll方法，先查出河湖库数据，而后三者装入一个list中，再由上一级set进入

![SouthEast][]

这是最后修改的结果，用setChildren一级塞二级，二级塞三级的方式，最后返回第一级，这比上一回塞进的方式简便代码量少，少了几个getChildren再addChild的量，六张表都是加了字段索引，查询起来也相对比较快了。

[SouthEast]: /images/20220531/9952572bef7542fcbefca93ed4b18e9f.png