先展示一个错误写法
public static String printGg_bysly0List() {// 外网 TransManager tm = new TransManager(); try { // 获取要更改的人员 String sql = "select t.yrk057,t.aac002,t.yr0560 from gg_bysly0 t where t.yr0560 like '2018%'"; List<Gg_bysly0> sybcbnryList = op.executeSQLQuery(sql, Gg_bysly0.class);// 获取到了要更改的list//一次查询oracle List xylist = op.executeSQLQuery("select * from xy_zlrxyxx t ");//二次查询oracle Map<String, String> xyMap = new HashMap<String, String>(); for (int y = 0; y < xylist.size(); y++) { BasicDynaBean bdbean = (BasicDynaBean) xylist.get(y); // xyMap.put(xylist.get(y).getYrk228(), // xylist.get(y).getQuerytime()); xyMap.put(bdbean.get("pid").toString(), bdbean.get("querytime").toString()); // System.out.println(bdbean.get("pid").toString()+" // "+bdbean.get("querytime").toString()); } for (int x = 0; x < sybcbnryList.size(); x++) { tm.begin(); try { Gg_bysly0 dto = sybcbnryList.get(x); String yrk057 = dto.getYrk057(); if (null == xyMap.get(yrk057)) {//查询完还要重新判断是否符合条件 // 调用信用接口 String messageString = null; Map<String, String> map = new LinkedHashMap<String, String>(); map.put("accessKey", "rs_jgpt_zwnw"); map.put("secretKey", "rs_jgpt_zwnw170911"); map.put("objectId", dto.getAac002()); String url = "http://172.18.1.150:9010/esb/esbproxy";// ESB核心地址 String user = "esb"; String pwd = "985984FA6B44CE2751075D37FF59EECD"; String sid = "gov.xm.wbjh.getPersonCreditBriefInfo"; // 自然人信用接口 try { messageString = EsbUtils.sendReq(url, user, pwd, sid, map); } catch (IOException e) { // TODO Auto-generated catch block throw new YLZCBPException("调动信用接口失败,请联系管理员!"); } Map resultsetMap = YlzesbResponseUtils.parseResponse(messageString); List dataList = (List) MapUtils.getObject(resultsetMap, "retrieve"); for (int j = 0; j < dataList.size(); j++) { Map map1 = (Map) dataList.get(j); Xy_zlrxyxx entity = new Xy_zlrxyxx(); if (StringUtils.isEmpty((String) map1.get("OBJECT_ID"))) { entity.setPid(yrk057); entity.setQuerytime(dto.getYr0560()); entity.setQueryresult("查无结果"); op.save(entity); break; } BeanHelper.copyProperties(map1, entity); entity.setPid(yrk057); entity.setOBJECT_ID(dto.getAac002()); entity.setQuerytime(dto.getYr0560()); op.save(entity); } } tm.commit(); } catch (Exception e) { log.info("错误", e); tm.rollback(); } } return "Success"; } catch (Exception e) { return e.getMessage(); } }
上面这种做法十分蠢,先是一次查完我们需要的数据,然后再转格式,再二次查完,再转,之后再判断,最后写入。难以阅读效率也非常低。
换一种做法
先写好要判断的视图,然后直接用语句。
create or replace view vw_xyzlrid as select sbsj,pid,aac002 from( select bys.yr0560 as sbsj,bys.yrk057 as pid,bys.aac002 as aac002 from GG_BYSLY0 bys where bys.yr0560 like '2018%' union all select sy.yr2290 as sbsj,sy.yrk208 as pid,sy.aac002 as aac002 from sy_bcbnry sy where sy.yr2290 like '2018%' and sy.yr2316 = '2') t where t.pid not in (select pid from xy_zlrxyxx);
public static String printSybcbnryList() {// 外网 TransManager tm = new TransManager(); try { String sql = "select t.pid,t.aac002,t.sbsj from vw_xyzlrid t "; List addList = op.executeSQLQuery(sql);// 获取到了要更改的list for (int x = 0; x < addList.size(); x++) { tm.begin(); try { BasicDynaBean bdbean = (BasicDynaBean) addList.get(x); String aac002 = bdbean.get("aac002").toString(); String pid = bdbean.get("pid").toString(); String sbsj = bdbean.get("sbsj").toString(); // 调用信用接口 String messageString = null; Map<String, String> map = new LinkedHashMap<String, String>(); map.put("accessKey", "rs_jgpt_zwnw"); map.put("secretKey", "rs_jgpt_zwnw170911"); map.put("objectId", aac002); String url = "http://172.18.1.150:9010/esb/esbproxy";// ESB核心地址 String user = "esb"; String pwd = "985984FA6B44CE2751075D37FF59EECD"; String sid = "gov.xm.wbjh.getPersonCreditBriefInfo"; // 自然人信用接口 try { messageString = EsbUtils.sendReq(url, user, pwd, sid, map); } catch (IOException e) { // TODO Auto-generated catch block throw new YLZCBPException("调动信用接口失败,请联系管理员!"); } Map resultsetMap = YlzesbResponseUtils.parseResponse(messageString); List dataList = (List) MapUtils.getObject(resultsetMap, "retrieve"); for (int j = 0; j < dataList.size(); j++) { Map map1 = (Map) dataList.get(j); Xy_zlrxyxx entity = new Xy_zlrxyxx(); if (StringUtils.isEmpty((String) map1.get("OBJECT_ID"))) { entity.setPid(pid); entity.setQuerytime(sbsj); entity.setQueryresult("查无结果"); op.save(entity); break; } BeanHelper.copyProperties(map1, entity); entity.setPid(pid); entity.setOBJECT_ID(aac002); entity.setQuerytime(sbsj); op.save(entity); } tm.commit(); } catch (Exception e) { log.info("错误", e); tm.rollback(); } } return "Success"; } catch (Exception e) { return e.getMessage(); } }