Java 接口开发

1、创建Controller类，编写方法

@ResponseBody
@RequestMapping("/queryAssetInComAndCostId")
public  String queryAssetInComAndCostId(String mchCodes){
   String[] aCodeArr = mchCodes.split(";");

   List<String> mchCodeList = new ArrayList<>();
   StringBuilder mchCodeBuf = new StringBuilder();
   for(int i=0;i<aCodeArr.length;i++){
      if(!StringUtils.isBlank(aCodeArr[i])){
         mchCodeList.add(aCodeArr[i]);
         mchCodeBuf.append("'"+aCodeArr[i]+"',");
      }
   }

   JSONObject result = new JSONObject();
   String mchCodeStr = mchCodeBuf.substring(0, mchCodeBuf.length()-1);
   List<Map<String, Object>> list = pd00016Service.queryAssetInComAndCostId(mchCodeStr);
   for(int i=0;i<list.size();i++){
      Map<String, Object> row = list.get(i);
      String mchCode = row.get("MCHCODE").toString();
      String inCompany = row.get("INCOMPANY") == null?"":row.get("INCOMPANY").toString();
      String newCostId = row.get("NEWCOSTID") == null?"":row.get("NEWCOSTID").toString();
      JSONObject rowMsg = new JSONObject();
      rowMsg.put("inCompany", inCompany);
      rowMsg.put("newCostId", newCostId);
      rowMsg.put("status", "Y");
      result.put(mchCode, rowMsg);
      mchCodeList.remove(mchCode);
   }

   for(int i=0;i<mchCodeList.size();i++){
      String mchCode = mchCodeList.get(i);
      JSONObject rowMsg = new JSONObject();
      rowMsg.put("status", "N");
      result.put(mchCode, rowMsg);
   }
   return result.toString();
}

2、创建Service，编写方法

public List<Map<String, Object>> queryAssetInComAndCostId(String mchCodeList) {
   List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
   StringBuilder sql = new StringBuilder();
   sql.append("SELECT T.INCOMPANY, T.UPDATE_TIME,T.NEWCOSTID,T.MCHCODE ");
   sql.append("FROM (SELECT R.INCOMPANY,R.UPDATE_TIME,R.NEWCOSTID,R.MCHCODE,ROW_NUMBER() OVER(PARTITION BY R.MCHCODE ORDER BY R.UPDATE_TIME DESC) RN ");
   sql.append("FROM ( ");
   sql.append("SELECT EXTRACTVALUE(D.DOCUMENT_DATA, '/ROOT/INCOMPANY') AS INCOMPANY,R.UPDATE_TIME,EXTRACTVALUE(R.ROW_DATA, '/ROOT/ROW/NEWCOSTID') AS NEWCOSTID,");
   sql.append("EXTRACTVALUE(R.ROW_DATA, '/ROOT/ROW/MCHCODE') AS MCHCODE FROM DAT_DOCUMENT D,DAT_TABLE_ROW R WHERE FORM_NAME='FM_PD00016' AND R.TABLE_ID='PD00016' ");
   sql.append("AND D.DOCUMENT_ID=R.DOCUMENT_ID AND EXTRACTVALUE(R.ROW_DATA, '/ROOT/ROW/MCHCODE') IN(" + mchCodeList + ")");
   sql.append(") R ) T WHERE T.RN = 1");
   result = gdao.executeJDBCSqlQuery(sql.toString());
   return result;
}

3、创建单元测试

@Test
public void testQueryAssetInComAndCostId() {
   String data = pd00016apiController.queryAssetInComAndCostId("6A0100070010");
   assertNotNull(data);
}

运行成功

 

4、浏览器调用