json传参多数组并使用jdbc生拼sql（由于持久层框架消耗性能）返回json数组

桃扇骨 2021-10-09 10:38 121阅读 0赞

一：入参格式

\{"city":"shanghai",  
"dataList":\["2019-04-08","2019-04-09","2019-04-10"\],  
"eventsList":\["01","02"\],  
"isContinuous":"fale",  
"roadSecList":\[\],  
    "sort":"DESC",  
    "timeFrame":\["01:30","02:30"\]  
\}

二：

1.创建接收对象ParamsBean

package com.cennavi.audi_data_collect.bean;
    
    import org.springframework.stereotype.Component;
    
    /**
     * Created by cennavi on 2019/5/7.
     */
    @Component
    public class ParamsBean {
        private String city;
        private String dataList[];
        private String dataListFormat[];
        private String eventsList[];
        private String isContinuous;
        private Integer roadSecList[];
        private String sort;
        private String timeFrame[];
        private Integer segmentId[];
    
       ....(get/set)
    }

2.编程Controller(入参前一定要加@RequestBody )

@RestController
    @RequestMapping("/eventUser")
    public class EventUserController {
    	
    	 @Autowired
    	    private EventUserService eventUserService;
    //数据统计  test
    			@RequestMapping(value = "/dataStatistics")
    			public Object dataStatistic(@RequestBody ParamsBean paramsBean) throws Exception{
    				return eventUserService.dataStatistic(paramsBean);
    			}
    
    }

3.编写services

public Object dataStatistic(ParamsBean paramsBean) throws Exception {
    		// TODO Auto-generated method stub
    		return eventUserDao.dataStatistic(paramsBean);
    	}

4.编写dao

（用于获取数组中元素后拼接成sql可用的格式）

if(paramsBean.getEventsList() != null) {
    			String es = "(";
    			for(int i=0;i<paramsBean.getEventsList().length;i++) {
    					es += "'" + paramsBean.getEventsList()[i] + "',";
    			}
    			es = es.substring(0, es.length()-1) + ")";
    			if(paramsBean.getEventsList().length>0)
    			{
    				sql += " and a.event_type in " + es;
    				sql2 += " and a.event_type in " + es;
    			}
    				
    		}

dao完整代码：

@Component
    
    public class EventUserDao {
    	 @Autowired
    	    private JdbcTemplate jdbcTemplate;
    
    public Object dataStatistic(ParamsBean paramsBean) throws Exception {
    		String sql="SELECT  t1.event_name_en as typeName ,t1.type_code as typeCode,COALESCE(t2.nums,0) AS  num    from event_type t1  LEFT JOIN  ( SELECT COALESCE(count(a.event_id), 0) as nums  , b.type_name as eventName  FROM  collection_info_new   a   LEFT JOIN   event_type b    ON a.event_type=b.type_code LEFT JOIN  gaosu_segment  c  ON c.id=a.segment_id LEFT JOIN gaosu  d  ON c.road_id=d.road_id WHERE 1=1";
    		String sql2="SELECT COALESCE(count(a.event_id), 0) as nums   FROM event_type b    LEFT JOIN   collection_info_new   a   ON a.event_type=b.type_code LEFT JOIN  gaosu_segment  c   ON c.id=a.segment_id  LEFT JOIN gaosu  d  ON c.road_id=d.road_id  WHERE 1=1  ";
    
    		if(paramsBean.getCity()!= null && !paramsBean.getCity().equals("")) {
    			sql += " and a.city_name='"+paramsBean.getCity()+"'";
    			sql2 += " and a.city_name='"+paramsBean.getCity()+"'";
    		}
    		
    		if(paramsBean.getDataList() != null) {
    			if(paramsBean.getIsContinuous().equals("true")) {
    				
    				if(paramsBean.getDataList().length>0) {
    					sql += " and substring(a.upload_time,0,11) between '"+paramsBean.getDataList()[0]+"' and '"+paramsBean.getDataList()[1]+"'";
    					sql2 += " and substring(a.upload_time,0,11) between '"+paramsBean.getDataList()[0]+"' and '"+paramsBean.getDataList()[1]+"'";
    
    				}
    	    	}else {
    	    		String es2 = "(";
    				for(int i=0;i<paramsBean.getDataList().length;i++) {
    					es2 += "'" + paramsBean.getDataList()[i] + "',";
    				}
    				es2 = es2.substring(0, es2.length()-1) + ")";
    				if(paramsBean.getDataList().length>0)
    				{
    					sql += " and substring(a.upload_time,0,11) in "+es2;
    					sql2 += " and substring(a.upload_time,0,11) in "+es2;
    				}
    					
    	    	}
    		}
    		
    		
    		if(paramsBean.getEventsList() != null) {
    			String es = "(";
    			for(int i=0;i<paramsBean.getEventsList().length;i++) {
    					es += "'" + paramsBean.getEventsList()[i] + "',";
    			}
    			es = es.substring(0, es.length()-1) + ")";
    			if(paramsBean.getEventsList().length>0)
    			{
    				sql += " and a.event_type in " + es;
    				sql2 += " and a.event_type in " + es;
    			}
    				
    		}
    		
    		if(paramsBean.getTimeFrame()!= null ) {
    			if(paramsBean.getTimeFrame().length>0)
    			{
    				sql += " and substring(a.upload_time,12,5) between '"+paramsBean.getTimeFrame()[0]+"' and '"+paramsBean.getTimeFrame()[1]+"'";
    			    sql2 += " and substring(a.upload_time,12,5) between '"+paramsBean.getTimeFrame()[0]+"' and '"+paramsBean.getTimeFrame()[1]+"'";
    			}
    		}
    		if(paramsBean.getRoadSecList() != null) {
    			String es1 = "(";
    			for(int i=0;i<paramsBean.getRoadSecList().length;i++) {
    				es1 += paramsBean.getRoadSecList()[i] + ",";
    			}
    			es1 = es1.substring(0, es1.length()-1) + ")";
    			if(paramsBean.getRoadSecList().length>0)
    				{
    				sql += " and d.r_id in "+es1;
    				sql2 += " and d.r_id in "+es1;
    				}
    		}
    		
    		  sql+=" group  by b.type_name   )  t2  ON t2.eventName=t1.type_name ";
    		  
    		  if ( paramsBean.getSort()!=null  && !paramsBean.getSort().equals("")) {
    			  sql += "  order by  num  "+paramsBean.getSort()+"";
    		  		}
    		  System.err.println("sql:"+sql);
    		  System.err.println("sql2:"+sql2);
    		  double k=jdbcTemplate.queryForObject(sql2,double.class);
    			 System.out.println("k:"+k);
    			
    		  List<Map<String, Object>> queryForList = jdbcTemplate.queryForList(sql);
    		  List< Map<String,Object>> queryForList2 = new ArrayList<Map<String,Object>>();
    		  for (Map<String, Object> map : queryForList) {
    			  Map<String, Object> map2=  new HashMap<String, Object>();
    				 for (String s : map.keySet()) {
    					 map2.put(s, map.get(s));
    					 if(s.equals("num")) {
    						 double a=Integer.parseInt(map.get(s).toString());
    						 double n=(double)Math.round(a/k*10000)/100;
    						 if(n==100.00) {
    							 n=99.99;
    						 }
    						 map2.put("percentage",  n);
    					 }
    		            }
    				 
    				 queryForList2.add(map2);
    		}
    			JSONObject json;
    			 JSONArray jsonArray = new JSONArray();
    		  for (Map<String, Object> map : queryForList2) {  
    			  json =new JSONObject(map);
    			  jsonArray.add(json);
    		}
    		  
    		return jsonArray;
    	}
    
    }

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzU1ODU4_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzU1ODU4_size_16_color_FFFFFF_t_70]: /images/20211009/f467e0d0f02a400a915660171d00253b.png