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、浏览器调用