java多对多查询

先建立关系表 这里要用到自查询

mybatis里面

 <!-- 获取监测点列表-->
  <resultMap id="waterSensorMap" type="com.swcote.water.entity.WaterSensor">
        <id property="id" column="id"/>
        <result property="orderNo" column="order_no"/>
        <result property="name" column="name"/>
        <result property="remarks" column="remarks"/>
        <result property="evalScope" column="eval_scope"/>
        <result property="type" column="type"/>
        <result property="lat" column="lat"/>
        <result property="lng" column="lng"/>
        <result property="riverId" column="river_id"/>
        <result property="districtCode" column="district_code"/>
        <collection property="parentPaths" javaType="ArrayList" ofType="com.swcote.water.entity.WaterSersorRelation">
            <id property="sersorId" column="parent_id"/>
            <result property="path" column="parent_path"/>
        </collection>
        <collection property="childPaths" javaType="ArrayList" ofType="com.swcote.water.entity.WaterSersorRelation">
            <id property="sersorId" column="child_id"/>
            <result property="path" column="child_path"/>
        </collection>
    </resultMap>

    <select id="listWaterSensors" resultMap="waterSensorMap">
        select
            s.id,
            s.order_no as "order_no",
            s.name,
            s.remarks,
            s.eval_scope as "eval_scope",
            s.type,
            s.district_code as "district_code",
            r.id as "river_id",
            s.lat,
            s.lng,
            p.parent_id as "parent_id",
            p.path as "parent_path",
            c.child_id as "child_id",
            c.path as "child_path"
        from water_sersor s
        left join river r on s.river_id = r.id
        left join water_sersor_rel p on s.id = p.child_id
        left join water_sersor_rel c on s.id = c.parent_id
        order by s.order_no
    </select>

实体类

public class WaterSensor {
    //TODO: SN
    /**
     * id
     */
    private Integer id;
    /**
     * 点位名称
     */
    private String name;
    /**
     * 点位情况
     */
    private String remarks;
    /**
     * 点位纬度
     */
    private BigDecimal lat;
    /**
     * 点位经度
     */
    private BigDecimal lng;
    /**
     * 点位范围
     */
    private EvalScope evalScope;
    /**
     * 点位类型
     */
    private WaterType type;
    /**
     * 行政区编码
     */
    private String districtCode;
    /**
     * 河流外键
     */
    private int riverId;

    /**
     * 排序
     */
    private Integer orderNo;

    /**
     * 上游
     */
    private List<Integer> parents;

    /**
     * 下游
     */
    private List<Integer> children;

    /**
     * 上流对应点流向集合
     * @return
     */
    private List<WaterSersorRelation> parentPaths;
    /**
     * 下流对应点流向集合
     * @return
     */
    private List<WaterSersorRelation> childPaths;



    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getRemarks() {
        return remarks;
    }

    public void setRemarks(String remarks) {
        this.remarks = remarks;
    }

    public BigDecimal getLat() {
        return lat;
    }

    public void setLat(BigDecimal lat) {
        this.lat = lat;
    }

    public BigDecimal getLng() {
        return lng;
    }

    public void setLng(BigDecimal lng) {
        this.lng = lng;
    }

    public EvalScope getEvalScope() {
        return evalScope;
    }

    public void setEvalScope(EvalScope evalScope) {
        this.evalScope = evalScope;
    }

    public WaterType getType() {
        return type;
    }

    public void setType(WaterType type) {
        this.type = type;
    }

    public List<Integer> getParents() {
        List<Integer> parents = new ArrayList<>();
        for(WaterSersorRelation panent :parentPaths){
            parents.add(panent.getSersorId());
        }
        return parents;
    }

//    public void setParents(List<Integer> parents) { this.parents = parents; }

    public List<Integer> getChildren() {

        List<Integer> children = new ArrayList<>();
        for(WaterSersorRelation child :childPaths){
            children.add(child.getSersorId());
        }
        return children; }

//    public void setChildren(List<Integer> children) { this.children = children; }

    public String getDistrictCode() {
        return districtCode;
    }

    public void setDistrictCode(String districtCode) {
        this.districtCode = districtCode;
    }

    public Integer getOrderNo() {
        return orderNo;
    }

    public void setOrderNo(Integer orderNo) {
        this.orderNo = orderNo;
    }

    public int getRiverId() {
        return riverId;
    }

    public void setRiverId(int riverId) {
        this.riverId = riverId;
    }

    public List<WaterSersorRelation> getParentPaths() {
        return parentPaths;
    }

    public void setParentPaths(List<WaterSersorRelation> parentPaths) {
        this.parentPaths = parentPaths;
    }

    public List<WaterSersorRelation> getChildPaths() {
        return childPaths;
    }

    public void setChildPaths(List<WaterSersorRelation> childPaths) {
        this.childPaths = childPaths;
    }
}