多表一个XML即可构建树

上一回写过一次构建树的方法，我提到过代码复用性太高了，因为六张表，差不多的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进入

多表一个XML即可构建树

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

多表一个XML即可构建树

相关推荐