通过for each轮询，将子表相关数据写入对应主表

灰太狼 2022-06-01 13:12 81阅读 0赞

场景：父表有citycode字段，但其内容来自于子表，子表的数据量不确定，有可能为空，也有可能有N多行，鉴于系统已经运行有一定时间了，产生了一定的数据，需要将父表不同ID下的子表内容写入，用下列代码搞定：

@Override
    protected void process(JobExecutionContext context) {
    
        //轮询将子表city插入至父表
        String s = "select s.id,s.citycode from s left join se on(s.id = se.sid) WHERE se.citycode is not null";
        String se = "select DISTINCT sid,citycode from se where sid = ? ";
        
    
        List<Record> l_s = Db.use(zz.DS_MOS).find(s);//父表list
        for (Record r : l_s) {  //从父表list轮询 
             Integer sid = r.getInt("id");
            List<Record> l_se = Db.use(zz.DS_MOS).find(se, sid);//查出对应子表内容
            if (!xx.isEmpty(l_se)) {
                for (Record r_se : l_se) {//子表轮询
                    String citycode = r_se.getStr("citycode");
                    String oc = r.getStr("operation_citycode");
    
                    if (l_se.size() == 1) {
                        r.set("operation_citycode", citycode);//子表只有唯一值时，父=子
                    }
                    // StringBuilder sb = new StringBuilder(r.getStr("operation_citycode"));
                    if (l_se.size() > 1) {//子表有多条时
                        if (xx.isEmpty(oc)) {
                            r.set("operation_citycode", citycode + ",");//如父citycode为空，父=子
                        } else {
                            r.set("operation_citycode", (r.getStr("operation_citycode") + citycode + ","));//否则父=子citycode + ","
                        }
                    }
                }
    
                Db.use(zz.DS_MOS).update("s", r);
            }
    
    
        }
      
    
    }