Java tutorial
package com.chexiang.idb.controller; import java.util.ArrayList; import java.util.Collections; import java.util.Enumeration; import java.util.HashMap; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; import javax.annotation.Resource; import javax.servlet.http.HttpServletRequest; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.stereotype.Controller; import org.springframework.ui.Model; import org.springframework.util.StringUtils; import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.ResponseBody; import com.chexiang.idb.entity.Dictionary; import com.chexiang.idb.service.DictionaryService; import com.chexiang.idb.util.HttpClientUtils; import com.chexiang.idb.util.XPathUtils; import com.meidusa.fastjson.JSON; import com.meidusa.fastjson.TypeReference; @Controller @RequestMapping(value = "/slowSel") public class DatabaseSlowSelController { @Resource DictionaryService dictionaryService; private static final Logger LOGGER = LoggerFactory.getLogger(DatabaseSlowSelController.class); @RequestMapping(value = "/queryAll") public String queryAll(HttpServletRequest request, Model model) { HashMap<String, String> paraMap = new HashMap<String, String>(); paraMap.put("dicName", "SlowSel_SolrAddredd"); List<Dictionary> dicList = dictionaryService.find(paraMap); String solrAddress = "http://solr01.hadoop:8983/solr/db_slow_query_shard1_replica1/select"; if (dicList.size() > 0) { solrAddress = dicList.get(0).getDicValue(); } String url = solrAddress + "?fl=db&q=db:*&start=1&rows=1&wt=xml&facet=true&facet.field=db"; String content = HttpClientUtils.get(url); int start = content.indexOf("facet_fields"); content = content.substring(content.indexOf("<lst", start), content.indexOf("</lst>", start) + 6); List<String> dblist = XPathUtils.explainXml(content); model.addAttribute("dblist", dblist); return "/WEB-INF/manager/databaseSlowSel_list.jsp"; } //? @RequestMapping(value = "/queryPage/{pageNum}/{pageSize}") public @ResponseBody String queryPage(HttpServletRequest request, Model model, @PathVariable int pageNum, @PathVariable int pageSize) { System.out.println("??" + System.currentTimeMillis()); if (pageNum <= 0) { pageNum = 1; } if (pageSize <= 0) { pageSize = 15; } ; HashMap<String, String> paraMap = new HashMap<String, String>(); String remParSign = request.getParameter("remParSign"); boolean sign = false; if ("remParSign".equals(remParSign)) { sign = true; } String searchStr = "&q="; String sortStr = "&sort="; Enumeration<String> parName = request.getParameterNames(); while (parName.hasMoreElements()) { String key = parName.nextElement(); if ("remParSign".equals(key)) { continue; } String value = request.getParameter(key); if (!StringUtils.isEmpty(value)) { if (key.endsWith("_wa8554d8r7gew")) { key = key.replace("_wa8554d8r7gew", ""); sortStr += (key + " asc,"); } else if (key.endsWith("wa8554d8r7gew_")) { key = key.replace("wa8554d8r7gew_", ""); sortStr += (key + " desc,"); } else { String[] vals = value.split(","); String prefix = ""; String suffix = ""; if (key.equals("user_host")) { prefix = "*"; suffix = "*"; } if (vals.length == 1) { searchStr += (key + ":" + prefix + vals[0] + suffix + " AND "); } else { searchStr += key + ":("; for (int i = 0; i < vals.length; i++) { if (i == vals.length - 1) { searchStr += (prefix + vals[i] + suffix + ") AND "); } else { searchStr += (prefix + vals[i] + suffix + " OR "); } } } } } } if ("&q=".equals(searchStr)) { searchStr += "*:*"; } else { searchStr = searchStr.substring(0, searchStr.length() - 5); } if ("&sort=".equals(sortStr)) { sortStr += "start_time desc"; } else { sortStr = sortStr.substring(0, sortStr.length() - 1); } String httpUrl = ""; String parUrl = ""; String solrAddress = ""; int start = (pageNum - 1) * pageSize; int rows = pageSize; if (sign) { paraMap.put("dicName", "RemPar_SolrAddredd"); List<Dictionary> dicList = dictionaryService.find(paraMap); solrAddress = "http://10.32.117.73:8983/solr/db_slow_query_novariable_shard1_replica1/select"; if (dicList.size() > 0) { solrAddress = dicList.get(0).getDicValue(); } httpUrl = solrAddress + "?fl=dbtype,db,start_time,query_time,lock_tim&rows=-1&group=true&group.field=sql_text"; } else { paraMap.put("dicName", "SlowSel_SolrAddredd"); List<Dictionary> dicList = dictionaryService.find(paraMap); solrAddress = "http://solr01.hadoop:8983/solr/db_slow_query_shard1_replica1/select"; if (dicList.size() > 0) { solrAddress = dicList.get(0).getDicValue(); } httpUrl = solrAddress + "?fl=dbtype,db,start_time,user_host,query_time,lock_time,rows_sent,rows_examined,sql_text&start=" + start + "&rows=" + rows; } parUrl += sortStr; parUrl += "&wt=json"; parUrl += searchStr; parUrl = parUrl.replaceAll(" ", "%20"); String url = httpUrl + parUrl; System.out.println("???" + System.currentTimeMillis()); System.out.println("??" + System.currentTimeMillis()); System.out.println(url); String jsonStr = HttpClientUtils.get(url); System.out.println("???" + System.currentTimeMillis()); System.out.println(jsonStr); System.out.println("?" + System.currentTimeMillis()); if (sign) { //json??json Map<String, Object> retMap = JSON.parseObject(jsonStr, new TypeReference<LinkedHashMap<String, Object>>() { }); Map<String, Map<String, Object>> groupMap = (Map<String, Map<String, Object>>) retMap.get("grouped"); Map<String, Object> sqlTextMap = groupMap.get("sql_text"); Object[] objs = (Object[]) sqlTextMap.get("groups"); //resonse Map<String, Object> respMap = new LinkedHashMap<String, Object>(); respMap.put("numFound", sqlTextMap.get("matches")); respMap.put("start", start); //docs Object[] docArrayList = new Object[objs.length]; respMap.put("docs", docArrayList); for (int i = 0; i < objs.length; i++) { Map<String, Object> groupobjMap = (Map<String, Object>) objs[i]; Map<String, Object> doclist = (Map<String, Object>) groupobjMap.get("doclist"); Object[] docObjects = (Object[]) doclist.get("docs"); //query_time lock_time int queryCount = 0; int lockCount = 0; ArrayList<Integer> queryMap = new ArrayList<Integer>(); ArrayList<Integer> lockMap = new ArrayList<Integer>(); for (int j = 0; j < docObjects.length; j++) { Map<String, Object> resultMap = (Map<String, Object>) docObjects[j]; int queryTime = getTime(resultMap.get("query_time")); int lock_time = getTime(resultMap.get("lock_time")); queryMap.add(queryTime); lockMap.add(lock_time); queryCount += queryTime; lockCount += lock_time; } Collections.sort(queryMap); Collections.sort(lockMap); Map<String, Object> resultMap = (Map<String, Object>) docObjects[0]; //querytaime?? String min_query_time = getTimeStr(queryMap.get(0)); String max_query_time = getTimeStr(queryMap.get(queryMap.size() - 1)); String acg_query_time = getTimeStr(queryCount / docObjects.length); resultMap.put("query_time", min_query_time + "<span class='sign'>(min)</span><br/>" + acg_query_time + "<span class='sign'>(avg)</span><br/>" + max_query_time + "<span class='sign'>(max)</span><br/>"); String min_lock_time = getTimeStr(lockMap.get(0)); String max_lock_time = getTimeStr(lockMap.get(lockMap.size() - 1)); String acg_lock_time = getTimeStr(lockCount / docObjects.length); resultMap.put("lock_time", min_lock_time + "<span class='sign'>(min)</span><br/>" + acg_lock_time + "<span class='sign'>(avg)</span><br/>" + max_lock_time + "<span class='sign'>(max)</span><br/>"); resultMap.put("sql_text", groupobjMap.get("groupValue")); resultMap.put("sql_count", doclist.get("numFound")); docArrayList[i] = resultMap; } retMap.remove("grouped"); retMap.put("response", respMap); jsonStr = JSON.toJSONString(retMap); jsonStr = jsonStr.replaceAll(" ", " "); jsonStr = jsonStr.replaceAll(" ", " "); jsonStr = jsonStr.replaceAll(" ", " "); jsonStr = jsonStr.replaceAll(" ", " "); } System.out.println("??" + System.currentTimeMillis()); return jsonStr; } public int getTime(Object time) { if (StringUtils.isEmpty(time)) { return 0; } String[] times = time.toString().split(":"); int intTime = 0; int hours = Integer.parseInt(times[0]); intTime += (hours * 3600); int minute = Integer.parseInt(times[1]); intTime += (minute * 60); int second = Integer.parseInt(times[2]); intTime += second; return intTime; } public String getTimeStr(int intTime) { if (intTime < 60) { return "00:00:" + addLeftZero(intTime); } int hours = (intTime / 3600); int minute = ((intTime % 3600) / 60); int second = (intTime % 60); return addLeftZero(hours) + ":" + addLeftZero(minute) + ":" + addLeftZero(second); } public String addLeftZero(int time) { if (time < 10) { return "0" + time; } return time + ""; } // public static void main(String[] args) { // String jsonStr="{\"responseHeader\":{\"status\":0,\"QTime\":14,\"params\":{\"fl\":\"dbtype,db,query_time,lock_time,sql_text\",\"start\":\"0\",\"q\":\"*:*\",\"group.field\":\"sql_text\",\"group\":\"true\",\"wt\":\"json\",\"rows\":\"15\"}},\"grouped\":{\"sql_text\":{\"matches\":4285,\"groups\":[{\"groupValue\":\"where\",\"doclist\":{\"numFound\":4006,\"start\":0,\"docs\":[{\"query_time\":\"00:00:01\",\"lock_time\":\"00:00:00\",\"sql_text\":\"select count(?) from( SELECT t.id AS id, t.sheet_no AS sheetNo, t.proc_inst_id AS procInstId, t.customer_phone AS customerPhone, t.customer_name AS customerName, t.status AS status, t.sheet_type AS sheetType, t.sheet_type_name AS sheetTypeName, t.priority AS priority, t.seriousness AS seriousness, t.company_type AS companyType, t.company_name AS companyName, t.source AS source, t.create_time AS createTime, t.update_time AS updateTime, t.reach_time as reachTimeEnd, t.brand_name as brandName, t.owner_name as ownerName, t.car_linsence as carLinsence, t.create_user as createUser, c.cust_name AS contactName, t.content as content FROM t_sr_worksheet t LEFT JOIN t_customer c ON c.cust_id = t.cust_id WHERE ?=? AND t.car_linsence like concat(?,?,?) AND t.tenant_id =? and t.sheet_type not in (?,?) ORDER BY t.create_time Desc ) t\",\"dbtype\":\"mysql\",\"db\":[\"smccac\"]}]}},{\"groupValue\":\"?\",\"doclist\":{\"numFound\":2,\"start\":0,\"docs\":[{\"query_time\":\"00:00:04\",\"lock_time\":\"00:00:00\",\"sql_text\":\"SELECT \\n\t r.begin_time \\\\'?\\\\',\\n\t r.AGENT_ID AS \\\\'?\\\\',\\n r.call_no AS \\\\'??\\\\',\\n\t tt.name AS \\\\'\\\\',\\n CASE WHEN r.manyidu_dispatch_time IS NULL THEN '?' ELSE '' END AS \\\\'?IVR\\\\',\\n CASE \\n WHEN r.manyidu IS NULL THEN ''\\n WHEN r.manyidu = '1' THEN '???' \\n WHEN r.manyidu = '2' THEN '??' \\n WHEN r.manyidu = '3' THEN ''\\n WHEN r.manyidu = '4' THEN '?'\\n WHEN r.manyidu = '5' THEN '??' \\n ELSE '' \\n END AS \\\\'?\\\\' \\nFROM t_call_record r \\nleft join t_service_record tsr\\non r.id = tsr.call_record_id \\nleft JOIN t_service_request_type tt\\non tsr.service_type = tt.code\\nwhere r.id >= 1000000\\nand r.type = 1\\n AND r.begin_time >= '2015-09-15 14:03:24' \\n AND r.begin_time <= '2015-09-16 14:03:27' \\n\\n\\nORDER BY r.begin_time DESC\",\"dbtype\":\"mysql\",\"db\":[\"eadccc\"]}]}},{\"groupValue\":\"\",\"doclist\":{\"numFound\":1,\"start\":0,\"docs\":[{\"query_time\":\"00:00:02\",\"lock_time\":\"00:00:00\",\"sql_text\":\"SELECT t2.serial_num ??, t2.salesman ?, t2.model , t2.cust_name ??,t2.plate_num ,t2.vin VIN, t2.repair_type , t2.settle_date , t2.telephone ??, t3.a1 1,t3.a2 2,t3.a3 3,t3.a4 4,t3.a5 5,t3.d1 1, t3.a6 6,t3.a7 7,t3.d2 2,t3.a8 8,t3.a9 9,t3.d3 3,t3.a10 10, t1.comments ,t1.agent_id ???,t1.lst_call_end_time ,t1.call_num ?,t4.reason FROM t_task t1 INNER JOIN t_fours_return_visit t2 ON t1.biz_id = t2.id LEFT JOIN ( select tt.task_id, MAX(CASE tt.rownum WHEN ? THEN tt.value END) AS a1, MAX(CASE tt.rownum WHEN ? THEN tt.value END) AS a2, MAX(CASE tt.rownum WHEN ? THEN tt.value END) AS a3, MAX(CASE tt.rownum WHEN ? THEN tt.value END) AS a4, MAX(CASE tt.rownum WHEN ? THEN tt.value END) AS a5, MAX(CASE tt.rownum WHEN ? THEN tt.value_ext END) AS d1, MAX(CASE tt.rownum WHEN ? THEN tt.value END) AS a6, MAX(CASE tt.rownum WHEN ? THEN tt.value END) AS a7, MAX(CASE tt.rownum WHEN ? THEN tt.value_ext END) AS d2, MAX(CASE tt.rownum WHEN ? THEN tt.value END) AS a8, MAX(CASE tt.rownum WHEN ? THEN tt.value END) AS a9, MAX(CASE tt.rownum WHEN ? THEN tt.value_ext END) AS d3, MAX(CASE tt.rownum WHEN ? THEN tt.value END) AS a10 FROM (SELECT t1.id task_id,@rownum:=@rownum+? - (@rownum DIV ?) * ? AS rownum,q1.value,q1.value_ext from t_task t1,t_questionnaire_answer q1,(select @rownum:=?) r WHERE t1.config_code = ? and t1.id = q1.task_id) tt GROUP BY tt.task_id ) t3 ON t3.task_id = t1.id LEFT JOIN t_outbound_config t4 ON t1.call_result = t4.code WHERE t1.config_code = ? AND t1.status = ? AND DATE_FORMAT(DATE_ADD(t2.create_time, interval ? day),? ) >= ? AND DATE_FORMAT(DATE_ADD(t2.create_time, interval ? day),? ) <= ?\",\"dbtype\":\"mysql\",\"db\":[\"rsm\"]}]}},{\"groupValue\":\"t_message_detail\",\"doclist\":{\"numFound\":6,\"start\":0,\"docs\":[{\"query_time\":\"00:00:01\",\"lock_time\":\"00:00:00\",\"sql_text\":\"SELECT * from t_message_detail\",\"dbtype\":\"mysql\",\"db\":[\"ccc\"]}]}},{\"groupValue\":\"worksheetid\",\"doclist\":{\"numFound\":10,\"start\":0,\"docs\":[{\"query_time\":\"00:00:01\",\"lock_time\":\"00:00:00\",\"sql_text\":\"select count(?) from( SELECT w.id AS worksheetId, w.code_id AS codeId, w.code_name AS taskName, w.cust_name AS custName, w.process_group_code AS groupCode, w.process_user_id AS processUserId, w.process_status AS processStatus, w.mobilephone AS mobilePhone, w.dispatch_time AS dispatchTime, w.fixedphone AS fixedphone, (SELECT COUNT(*) FROM t_call_record c WHERE c.task_id = w.id) callNum from t_task_worksheet_info w where `status`=? and tenant_id=? AND w.process_status =? ORDER BY w.dispatch_time DESC ) t\",\"dbtype\":\"mysql\",\"db\":[\"smccac\"]}]}},{\"groupValue\":\"wifi_status\",\"doclist\":{\"numFound\":39,\"start\":0,\"docs\":[{\"query_time\":\"00:00:01\",\"lock_time\":\"00:00:00\",\"sql_text\":\"SELECT \tid, \tassign_time, \tbuy_time, \tcampaign, \tcampaign_endtime, \tcampaign_starttime, \tcar_type, \tcity, \tdata_ownership, \tdata_type, \tdealer, \temail, \tfirst_channel, \tgender, \tinfo_source, \tinit_id, \tprovince, \treg_time, \tremark, \tsales, \tsencond_channel, \ttwo_dimensional_status, \tuser_name, \twifi_status, \tbrand, \tcustombuiltinfo, \tmobile, \tremark1, \tremark2, \tsis_id, \tcreate_time, \tdistribute_time, \ttask_id, \tIS_SEND_TO_DLM, \tSEND_TO_DLM_TIME, \tCREATE_USER, \tUPDATE_TIME, \tUPDATE_USER\tFROM \tSIS.T_DISTRIBUTE_RESULT WHERE UPDATE_TIME >=?\",\"dbtype\":\"mysql\",\"db\":[\"sis\"]}]}},{\"groupValue\":\"t_followup\",\"doclist\":{\"numFound\":13,\"start\":0,\"docs\":[{\"query_time\":\"00:00:01\",\"lock_time\":\"00:00:00\",\"sql_text\":\"SELECT count(*) FROM t_followup f INNER JOIN t_cust_base c ON f.cust_id = c.id AND f.plan_follow_time < ? AND f.status = ? AND f.deleted_flag = ? AND c.deleted_flag = ? AND c.salesman in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND c.dealer_id in (?)\",\"dbtype\":\"mysql\",\"db\":[\"dlm\"]}]}},{\"groupValue\":\"sleep\",\"doclist\":{\"numFound\":2,\"start\":0,\"docs\":[{\"query_time\":\"00:00:05\",\"lock_time\":\"00:00:00\",\"sql_text\":\"select sleep(?)\",\"dbtype\":\"mysql\",\"db\":[\"\"]}]}},{\"groupValue\":\"timestamp\",\"doclist\":{\"numFound\":2,\"start\":0,\"docs\":[{\"query_time\":\"00:00:02\",\"lock_time\":\"00:00:00\",\"sql_text\":\"SELECT\\r\\n\tb.id,\\r\\n\tb.TYPE,\\r\\n\tb.user_id,\\r\\n\tb.agent_id,\\r\\n\tb.call_no,\\r\\n\tb.cust_id,\\r\\n\tb.subinst_id,\\r\\n\tb.member_id,\\r\\n\tb.task_id,\\r\\n\tb.cust_card_no,\\r\\n\tb.cust_name,\\r\\n\tb.cust_gender,\\r\\n\tb.cust_mobile_num,\\r\\n\tb.manyidu,\\r\\n\tb.connid,\\r\\n\tb.extension,\\r\\n\tb.begin_time,\\r\\n\tb.outbound_config_code,\\r\\n\tb.is_connected,\\r\\n\tb.talk_time,\\r\\n\tb.end_time,\\r\\n\tb.talk_length,\\r\\n\tb.remark,\\r\\n\tb.content,\\r\\n\tb.create_time,\\r\\n\tb.create_user_name,\\r\\n\tb.create_user_id,\\r\\n\tb.update_time,\\r\\n\tb.update_user_name,\\r\\n\tb.update_user_id\\r\\nFROM\\r\\n\tt_call_record b\\r\\nWHERE\\r\\n\t1 = 1\\r\\nAND b.begin_time >= TIMESTAMP ('2015-03-01')\\r\\nAND (\\r\\n\tb.call_no = '58997681'\\r\\n\tOR b.cust_mobile_num = '58997681'\\r\\n)\",\"dbtype\":\"mysql\",\"db\":[\"eadccc\"]}]}},{\"groupValue\":\"trade_id\",\"doclist\":{\"numFound\":2,\"start\":0,\"docs\":[{\"query_time\":\"00:00:01\",\"lock_time\":\"00:00:00\",\"sql_text\":\"select a.* from t_synbsnvehicle_search a,\\n(select trade_id,max(id) maxid from t_synbsnvehicle_search group by trade_id) b,\\ntt_intent c\\nwhere a.trade_id = b.trade_id\\nand a.id=b.maxid\\nand b.trade_id = c.intent_id\\nand a.bsn_point=1 and a.bsn_link=21 and a.bsn_status=0\\norder by a.id desc limit 10,30\",\"dbtype\":\"mysql\",\"db\":[\"chexiangpai\"]}]}},{\"groupValue\":\"y\",\"doclist\":{\"numFound\":3,\"start\":0,\"docs\":[{\"query_time\":\"00:00:04\",\"lock_time\":\"00:00:00\",\"sql_text\":\"SELECT\\r\\n\ta.serial_num AS 'caseSerialNum',\\r\\n\tres_Addr AS 'resAddr',\\r\\n\tdestination,\\r\\n\tc.type AS 'carType',\\r\\n\tc.serial_num AS 'carSerialNum',\\r\\n\tb.res_driver_name AS 'driveName',\\r\\n\tamount,\\r\\n\tm. NAME AS 'carOwnerName',\\r\\n\tmobilephone,\\r\\n\td.license_Num AS 'licenseNum',\\r\\n\tcolor\\r\\nFROM\\r\\n\tt_case a\\r\\nLEFT JOIN (\\r\\n\tSELECT\\r\\n\t\t*\\r\\n\tFROM\\r\\n\t\tt_dispatch\\r\\n\tWHERE\\r\\n\t\tSTATUS = 10011001\\r\\n) b ON a.id = b.case_id\\r\\nLEFT JOIN (\\r\\n\tSELECT\\r\\n\t\ta.type,\\r\\n\t\ta.serial_num,\\r\\n\t\ta.id\\r\\n\tFROM\\r\\n\t\tt_service_car a\\r\\n) c ON b.res_car_id = c.id\\r\\nLEFT JOIN t_car d ON a.car_id = d.id\\r\\nLEFT JOIN (\\r\\n\tSELECT\\r\\n\t\tb. NAME,\\r\\n\t\tb.mobilephone,\\r\\n\t\ta.subinst_id\\r\\n\tFROM\\r\\n\t\tt_subscribe_inst a\\r\\n\tINNER JOIN t_customer b ON a.cust_id = b.cust_id\\r\\n) m ON a.subinst_id = m.subinst_id\\r\\nWHERE\\r\\n\t a.accept_Time >= DATE_FORMAT(NOW(), '%Y-%m-%d')\\r\\nand a.accept_time<DATE_FORMAT(DATE_ADD(now(),INTERVAL 1 day),'%Y-%m-%d')\\r\\nAND (\\r\\n\t(\\r\\n\t\ta.accept_id IS NOT NULL\\r\\n\t\tAND a.accept_id IN (10816)\\r\\n\t)\\r\\n\tOR (\\r\\n\t\ta.dispatch_id IS NOT NULL\\r\\n\t\tAND a.dispatch_id IN (10816)\\r\\n\t)\\r\\n)\\r\\nLIMIT 0,\\r\\n 5\",\"dbtype\":\"mysql\",\"db\":[\"rsm\"]}]}},{\"groupValue\":\"?\",\"doclist\":{\"numFound\":11,\"start\":0,\"docs\":[{\"query_time\":\"00:00:01\",\"lock_time\":\"00:00:00\",\"sql_text\":\"SELECT * FROM\\n \\n (\\n -- ?\\n SELECT\\n vhcl.vhcl_id carId,\\n task.OWNER_NAME usedCarOwner,\\n task.OWNER_MOBILE usedCarOwnerTel,\\n vhcl.VHCL_PICTURE_URL carPicture,\\n concat(vhcl.BRAND, ' ', vhcl.SERIES) brand,\\n vhcl.city_name cityName,\\n vhcl.LICENCE licence,\\n vhcl.VIN vin,\\n auction.utid utid,\\n IFNULL(trade_info.negatiate_price, trade_bidlist.price) usedCarDealPrice,\\n IFNULL(bidding_del.MODIFY_DATE, '') usedCarDealTime,\\n auction.id auctionId,\\n substitution.create_time createTime,\\n substitution.audit_status auditStatus,\\n substitution.audit_time auditTime,\\n substitution.id id,\\n 1 dealPlatform,\\n 0 sourceType,\\n '' filingChannelName,\\n substitution.is_yellow_car_yn isYellowCarYn,\\n IFNULL(substitution.drivinglicense_img, report.DRIVING_LICENSE_IMG) drivingLicenseImg,\\n IFNULL(substitution.registration_img, report.CAR_REG_CERT_IMG) registrationImg,\\n IFNULL(substitution.registration_img_beta, report.CAR_REG_CERT_IMG2) registrationImgBeta\\n FROM t_trade_info trade_info\\n JOIN t_bid_auction auction ON auction.utid = trade_info.trade_id\\n JOIN tt_bidding_del bidding_del ON bidding_del.utid = auction.utid\\n LEFT JOIN tm_car_substitution substitution ON substitution.utid = auction.utid\\n JOIN tt_pg_task task on task.AT_CODE = auction.utid\\n join tm_vhcl vhcl on trade_info.car_id = vhcl.VHCL_ID\\n left join t_trade_bidlist trade_bidlist on (trade_info.trade_id = trade_bidlist.trade_id and trade_bidlist.status = 1)\\n JOIN tt_pg_task task2 ON task2.at_code = vhcl.utid\\n JOIN tt_pg_report report ON report.AT_ID = task2.UUID AND report.end_report = 1\\n WHERE bidding_del.PAY_STATUS = 60070001 and trade_info.phase in (20,30)\\n \\n \\n \\n \\n \\n \\n \\n AND vhcl.DEALER_ID = '1061'\\n \\n\\n UNION ALL\\n\\n -- ?\\n SELECT\\n car.id carId,\\n car.name usedCarOwner,\\n car.phone usedCarOwnerTel,\\n car.vhcl_picture_url carPicture,\\n concat(\\n car.car_brand,\\n ' ',\\n car.car_family\\n ) brand,\\n region.region_name cityName,\\n car.num licence,\\n car.vin vin,\\n car.utid utid,\\n auction.bid_price usedCarDealPrice,\\n auction.bid_time usedCarDealTime,\\n '' auctionId,\\n substitution.create_time createTime,\\n substitution.audit_status auditStatus,\\n substitution.audit_time auditTime,\\n substitution.id id,\\n 3 dealPlatform,\\n car.SOURCETYPE sourceType,\\n '' filingChannelName,\\n substitution.is_yellow_car_yn isYellowCarYn,\\n IFNULL(substitution.drivinglicense_img, motor.DRIVINGLICENSE_IMG) drivingLicenseImg,\\n IFNULL(substitution.registration_img, motor.REGISTRATION_IMG) registrationImg,\\n IFNULL(substitution.registration_img_beta, motor.REGISTRATION_IMG_BETA) registrationImgBeta\\n FROM\\n t_inbid_car car\\n JOIN t_inbid_auction auction ON car.id = auction.car_id\\n LEFT JOIN tm_car_substitution substitution ON substitution.utid = car.utid\\n LEFT JOIN tm_region region on region.region_id = car.city_id\\n left join tm_vhcl_saicmotor motor on motor.utid = car.utid\\n WHERE\\n auction. STATUS = 6\\n \\n \\n \\n \\n \\n \\n \\n AND car.user_id = '1000000000002776'\\n \\n\\n UNION ALL\\n\\n -- ?\\n SELECT\\n NULL carId,\\n car.owner_name usedCarOwner,\\n car.owner_tel usedCarOwnerTel,\\n car.car_picture carPicture,\\n concat(car.brand, ' ', car.SERIES) brand,\\n car.city_name cityName,\\n car.LICENCE licence,\\n car.vin vin,\\n car.utid utid,\\n substitution.used_car_deal_price usedCarDealPrice,\\n substitution.used_car_deal_time usedCarDealTime,\\n car.report_url auctionId,\\n substitution.create_time createTime,\\n substitution.audit_status auditStatus,\\n substitution.audit_time auditTime,\\n substitution.id id,\\n case\\n car.channel_type\\n WHEN 1 THEN 4\\n WHEN 0 THEN 2\\n end as dealPlatform,\\n 0 sourceType,\\n other.CHANNEL_NAME filingChannelName,\\n substitution.is_yellow_car_yn isYellowCarYn,\\n IFNULL(substitution.drivinglicense_img, motor.DRIVINGLICENSE_IMG) drivingLicenseImg,\\n IFNULL(substitution.registration_img, motor.REGISTRATION_IMG) registrationImg,\\n IFNULL(substitution.registration_img_beta, motor.REGISTRATION_IMG_BETA) registrationImgBeta\\n FROM\\n tm_other_platform_car car\\n LEFT JOIN tm_car_substitution substitution ON substitution.utid = car.utid\\n left join motor_filing_channels other on car.dealer_id = other.dealer_id\\n left join tm_vhcl_saicmotor motor on motor.utid = car.utid\\n WHERE car.DEALER_ID = '1061' \\n )\\n \\n car_substitution_temp\\n order by if(car_substitution_temp.auditStatus is null,0,1),car_substitution_temp.createTime desc limit 20,10\",\"dbtype\":\"mysql\",\"db\":[\"chexiangpai\"]}]}},{\"groupValue\":\"\",\"doclist\":{\"numFound\":2,\"start\":0,\"docs\":[{\"query_time\":\"00:00:07\",\"lock_time\":\"00:00:00\",\"sql_text\":\"-- \\nUPDATE t_deliver_vel d\\nINNER JOIN t_transaction t ON d.id = t.order_vel_id \\nSET d.create_time = t.create_time\\nWHERE DATE_FORMAT(t.create_time,'%Y-%m-%d') < DATE_FORMAT(d.create_time,'%Y-%m-%d')\\nAND t.create_time > '2014-7-21'\",\"dbtype\":\"mysql\",\"db\":[\"dlm\"]}]}},{\"groupValue\":\"u.user_name\",\"doclist\":{\"numFound\":1,\"start\":0,\"docs\":[{\"query_time\":\"00:00:07\",\"lock_time\":\"00:00:00\",\"sql_text\":\"SELECT\\r\\n\tt.id AS id,\\r\\n\tt.buy_time,\\r\\n\tt.campaign AS campaign,\\r\\n\tt.city_name AS city,\\r\\n\tt.custom_built_info customerBuildInfo,\\r\\n\tt.customer_name AS customerName,\\r\\n\tt.customer_status,\\r\\n\tt.email,\\r\\n\tt.gender AS gender,\\r\\n\tt.mobile AS mobile,\\r\\n\tt.model AS model,\\r\\n\tt.province_name AS province,\\r\\n\tt.tel_phone,\\r\\n\tt.reg_time,\\r\\n\tt.custom_second_source,\\r\\n\tt.remark1,\\r\\n\tt.remark2,\\r\\n\tt.sis_id,\\r\\n\tt.sis_init_id,\\r\\n\tt.source,\\r\\n\tt.info_source AS infoSouce,\\r\\n\tt.two_dimensional_status twoDimensionalStatus,\\r\\n\tt.wifi_status,\\r\\n\tt.brand AS brand,\\r\\n\tt.follow_status,\\r\\n\tt.leads_push_time,\\r\\n\tt.create_time,\\r\\n\tt.last_update_time,\\r\\n\tt.order_type AS orderType,\\r\\n\tt.cars,\\r\\n\tt.address,\\r\\n\tt.first_source firstSource,\\r\\n\tt.second_source secondSource,\\r\\n\tt.seat_id AS seatId,\\r\\n\tt.campaign_starttime AS campaignStartTime,\\r\\n\tt.campaign_endtime AS campaignEndtime,\\r\\n\tt.data_ownership,\\r\\n\tt.second_channel AS secondChannel,\\r\\n\tt.car_type,\\r\\n\tt.data_type,\\r\\n\tt.sales,\\r\\n\tt.user_name,\\r\\n\tt.remark,\\r\\n\tt.remark_label,\\r\\n\tt.first_channel AS firstChannel,\\r\\n\tt.call_time AS callTime,\\r\\n\tt.call_status AS callResult,\\r\\n\tt.call_count AS callCount,\\r\\n\tt.dealer_id AS dealerId,\\r\\n\tt.dealer_code,\\r\\n\tt.assgin_time,\\r\\n\tt.customer_detail_remark AS customerDetailRemark,\\r\\n\tt.mobile_region AS mobileRegion,\\r\\n\tt.arrange_date AS arrangeDate,\\r\\n\tt.arrange_time AS arrangeTime,\\r\\n\tt.arrange_address AS arrangeAddress,\\r\\n\tt.data_ownership_es AS dataOwnershipes,\\r\\n\tt.distribute_time AS distributeTime,\\r\\n\td.dealer_address AS dealerAddress,\\r\\n\td.dol AS dealerCode,\\r\\n\td.dealer_name AS dealerName,\\r\\n\td.sis_code AS sisAccount,\\r\\n\td.sis_name AS siserName,\\r\\n\td.sis_phone AS sisPhone,\\r\\n\td.dealer_manager_name AS dealerManager,\\r\\n\td.dealer_manager_phone AS dealerManagerPhone,\\r\\n\tu.user_name AS seatName\\r\\nFROM\\r\\n\tt_push_customer_task t\\r\\nLEFT JOIN t_dealer d ON d.id = t.dealer_id\\r\\nLEFT JOIN t_user u ON t.seat_id = u.id\\r\\nWHERE\\r\\n\t1 = 1\\r\\nAND t.CALL_STATUS = 1\\r\\nAND u.user_id = '60819'\\r\\nORDER BY\\r\\n\tt.assgin_time DESC\\r\\nLIMIT 0,\\r\\n 10\",\"dbtype\":\"mysql\",\"db\":[\"sis\"]}]}},{\"groupValue\":\"zip_cd\",\"doclist\":{\"numFound\":2,\"start\":0,\"docs\":[{\"query_time\":\"00:00:03\",\"lock_time\":\"00:00:00\",\"sql_text\":\"SELECT id, golbal_id, name, mobile, email, state, city, zip_cd, addr, vin, update_type, microblog_acct, wechat_acct, identity_num, last_update_time, last_update_user, create_time, update_time, send_source_target, send_status FROM t_cem_base_plate_customer WHERE send_source_target = ? AND (send_status=? OR send_status=?) ORDER BY create_time DESC LIMIT ?,?\",\"dbtype\":\"mysql\",\"db\":[\"ccc\"]}]}}]}}}"; // //json??json // Map<String, Object> retMap=JSON.parseObject(jsonStr, new TypeReference<LinkedHashMap<String, Object>>(){}); // Map<String, Map<String,Object>> groupMap=(Map<String, Map<String,Object>>)retMap.get("grouped"); // Map<String, Object> sqlTextMap=groupMap.get("sql_text"); // Object[] objs=(Object[])sqlTextMap.get("groups"); // // //resonse // Map<String, Object> respMap=new LinkedHashMap<String, Object>(); // respMap.put("numFound", sqlTextMap.get("matches")); // //docs // Object[] docArrayList=new Object[objs.length]; // respMap.put("docs", docArrayList); // respMap.put("start", 0); // for(int i=0;i<objs.length;i++){ // Map<String,Object> groupobjMap=(Map<String,Object>)objs[i]; // Map<String, Object> doclist=(Map<String, Object>)groupobjMap.get("doclist"); // Object[] docObjects=(Object[])doclist.get("docs"); //// for(int j=0;i<docObjects.length;j++){ //// //// } // //map?map // Map<String, Object> resultMap= (Map<String, Object>)docObjects[0]; // //querytaime?? //// resultMap.put("query_time", ""); //// resultMap.put("min_query_time", ""); //// resultMap.put("max_query_time", ""); // docArrayList[i]=resultMap; // } // retMap.remove("grouped"); // retMap.put("response", respMap); // jsonStr=JSON.toJSONString(retMap); // System.out.println(jsonStr); // } }