Java Code Examples for org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#queryForList()

private List <Map <String, Object>> doQuery (NamedParameterJdbcTemplate jdbc, String q, Map <String, Object> packed) throws SQLException {
	show ("QLM", q, packed);

	Retry <List <Map <String, Object>>>	r = new Retry <List <Map <String, Object>>> ("queryList", this, jdbc) {
		@Override
		public void execute () throws SQLException {
			priv = jdbc.queryForList (q, packed);
		}
	};
	if (retry (r)) {
		return r.priv;
	}
	throw failure (q, r.error);
}
 

/**
 * 支持miniDao语法操作的查询
 *
 * @param dbKey 数据源标识
 * @param sql   执行sql语句，sql支持minidao语法逻辑
 * @param data  sql语法中需要判断的数据及sql拼接注入中需要的数据
 * @return
 */
public static List<Map<String, Object>> findListByHash(final String dbKey, String sql, HashMap<String, Object> data) {
    List<Map<String, Object>> list;
    JdbcTemplate jdbcTemplate = getJdbcTemplate(dbKey);
    //根据模板获取sql
    sql = FreemarkerParseFactory.parseTemplateContent(sql, data);
    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate.getDataSource());
    list = namedParameterJdbcTemplate.queryForList(sql, data);
    return list;
}
 

/**
 * 支持miniDao语法操作的查询 返回单列数据list
 *
 * @param dbKey 数据源标识
 * @param sql   执行sql语句，sql支持minidao语法逻辑
 * @param clazz 类型Long、String等
 * @param data  sql语法中需要判断的数据及sql拼接注入中需要的数据
 * @return
 */
public static <T> List<T> findListByHash(final String dbKey, String sql, Class<T> clazz, HashMap<String, Object> data) {
    List<T> list;
    JdbcTemplate jdbcTemplate = getJdbcTemplate(dbKey);
    //根据模板获取sql
    sql = FreemarkerParseFactory.parseTemplateContent(sql, data);
    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate.getDataSource());
    list = namedParameterJdbcTemplate.queryForList(sql, data, clazz);
    return list;
}
 

/**
 * 支持miniDao语法操作的查询
 *
 * @param dbKey 数据源标识
 * @param sql   执行sql语句，sql支持minidao语法逻辑
 * @param data  sql语法中需要判断的数据及sql拼接注入中需要的数据
 * @return
 */
public static List<Map<String, Object>> findListByHash(final String dbKey, String sql, HashMap<String, Object> data) {
    List<Map<String, Object>> list;
    JdbcTemplate jdbcTemplate = getJdbcTemplate(dbKey);
    //根据模板获取sql
    sql = FreemarkerParseFactory.parseTemplateContent(sql, data);
    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate.getDataSource());
    list = namedParameterJdbcTemplate.queryForList(sql, data);
    return list;
}
 

/**
 * 支持miniDao语法操作的查询 返回单列数据list
 *
 * @param dbKey 数据源标识
 * @param sql   执行sql语句，sql支持minidao语法逻辑
 * @param clazz 类型Long、String等
 * @param data  sql语法中需要判断的数据及sql拼接注入中需要的数据
 * @return
 */
public static <T> List<T> findListByHash(final String dbKey, String sql, Class<T> clazz, HashMap<String, Object> data) {
    List<T> list;
    JdbcTemplate jdbcTemplate = getJdbcTemplate(dbKey);
    //根据模板获取sql
    sql = FreemarkerParseFactory.parseTemplateContent(sql, data);
    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate.getDataSource());
    list = namedParameterJdbcTemplate.queryForList(sql, data, clazz);
    return list;
}
 

private Object executeWithJdbcTemplate(NativeQueryInfo info) {
    NamedParameterJdbcTemplate jdbcTemplate = ApplicationContextProvider.getApplicationContext().getBean(NamedParameterJdbcTemplate.class);

    Map<String, Object> parametroList = new HashMap<>();
    for (NativeQueryParameter parametro : info.getParameterList()) {
        if (parametro.getValue() != null && info.getSql().contains(":" + parametro.getName())) {
            parametroList.put(parametro.getName(), parametro.getValue());
        }
    }

    BeanPropertyRowMapper<?> beanPropertyRowMapper = new BeanPropertyRowMapper<>(info.getAliasToBean());
    if (info.getReturnType().getSimpleName().equals(Void.TYPE.getName())) {
        jdbcTemplate.update(info.getSql(), parametroList);
        return null;
    }

    if (info.isSingleResult()) {
        if (info.isJavaObject()) {
            return jdbcTemplate.queryForObject(info.getSql(), parametroList, info.getAliasToBean());
        }

        if (info.returnTypeIsOptional()) {
            return getOptionalReturn(() -> jdbcTemplate.queryForObject(info.getSql(), parametroList, beanPropertyRowMapper));
        }

        return jdbcTemplate.queryForObject(info.getSql(), parametroList, beanPropertyRowMapper);
    }

    if (info.isJavaObject()) {
        return jdbcTemplate.queryForList(info.getSql(), parametroList, info.getAliasToBean());
    }
    return jdbcTemplate.query(info.getSql(), parametroList, beanPropertyRowMapper);
}
 

/**
 * 支持miniDao语法操作的查询
 *
 * @param dbKey 数据源标识
 * @param sql   执行sql语句，sql支持minidao语法逻辑
 * @param data  sql语法中需要判断的数据及sql拼接注入中需要的数据
 * @return
 */
public static List<Map<String, Object>> findListByHash(final String dbKey, String sql, HashMap<String, Object> data) {
    List<Map<String, Object>> list;
    JdbcTemplate jdbcTemplate = getJdbcTemplate(dbKey);
    //根据模板获取sql
    sql = FreemarkerParseFactory.parseTemplateContent(sql, data);
    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate.getDataSource());
    list = namedParameterJdbcTemplate.queryForList(sql, data);
    return list;
}
 

private static String processCsvText(ExportDataConfig config, NamedParameterJdbcTemplate jdbcTemplate, 
		Map<String, Object> sqlParamMap) throws Exception {
	logger.info("export-Id: " + config.getId() + " name: " + config.getName());
	StringBuilder out = new StringBuilder();
	out.append(config.getTitle()).append("\r\n");
	List<Map<String, Object>> results = jdbcTemplate.queryForList(config.getSql(), sqlParamMap);
	for (int i=0; results!=null && i<results.size(); i++) {
		Map<String, Object> dataMap = results.get(i);
		for (Map.Entry<String, Object> entry : dataMap.entrySet()) {
			if ( entry.getValue() != null ) {
				String str = "";
				if (entry.getValue() instanceof byte[]) { // blob text
					str = new String( (byte[])entry.getValue() , Constants.BASE_ENCODING );
				} else {
					str = String.valueOf( entry.getValue() );
				}					
				if (config.isEscapeCsv()) {
					//str = StringEscapeUtils.escapeCsv(str);
					str = SimpleUtils.escapeCsv(str);
				}
				if (StringUtils.isBlank(str)) {
					str = " ";
				}
				out.append("\"").append(str).append("\"");					
			} else {
				out.append(" ");
			}
			out.append( config.getSeparateSymbol() );
		}
		out.append("\r\n");
	}
	return out.toString();
}
 

/**
 * 支持miniDao语法操作的查询 返回单列数据list
 * @param dbKey 数据源标识
 * @param sql 执行sql语句，sql支持minidao语法逻辑
 * @param clazz 类型Long、String等
 * @param data sql语法中需要判断的数据及sql拼接注入中需要的数据
 * @return
 */
public static <T> List<T> findListByHash(final String dbKey, String sql, Class<T> clazz, HashMap<String, Object> data){
	List<T> list;
	JdbcTemplate jdbcTemplate = getJdbcTemplate(dbKey);
	//根据模板获取sql
	sql = FreemarkerParseFactory.parseTemplateContent(sql, data);
	NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate.getDataSource());
	list = namedParameterJdbcTemplate.queryForList(sql, data, clazz);
	return list;
}
 

/**
 * 方法名： buildPie
 * 功 能： TODO(这里用一句话描述这个方法的作用)
 * 参 数： @param map
 * 参 数： @param info
 * 参 数： @param list
 * 返 回： void
 * 作 者 ： Administrator
 * @throws
 */
public void buildPie(Map<String, Object> map, SysDbmsChartDimension info, StringBuilder sbWhere, String type1, String tableName) {
	
	List<String> legend_data = new ArrayList<>();
	List<Map<String, Object>> series_data = new ArrayList<>();
	StringBuilder sql = new StringBuilder();
	Map<String, Object> param = new HashMap<>();
	// 默认表结构
	sql.append(" select  " + type1 + " as aks,count(1) as num");
	sql.append(" from " + tableName + "  ");
	sql.append(" where 1=1 ");
	sql.append(" and  " + type1 + " is not null ");
	sql.append(" and  " + type1 + " <> '' ");
	sql.append(sbWhere.toString());
	sql.append(" group by  " + type1);

	NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(jdbcTemplate);
	List<Map<String, Object>> listMap = template.queryForList(sql.toString(), param);
	for (Map<String, Object> map2 : listMap) {
		legend_data.add(map2.get("aks").toString());
		// {value:92503371, name:'男'}
		Map<String, Object> data = new HashMap<>();
		data.put("value", Integer.valueOf(map2.get("num").toString()));
		data.put("name", map2.get("aks").toString());
		series_data.add(data);
	}
	map.put("legend_data", legend_data);
	map.put("series_data", series_data);
	map.put("chartType", info.getChartType());
}
 

/**
 * @方法名 buildPieSum
 * @功能 TODO(这里用一句话描述这个方法的作用)
 * @参数 @param map
 * @参数 @param info
 * @参数 @param sbWhere
 * @参数 @param type1
 * @返回 void
 * @author Administrator
 * @throws
 */
public void buildPieSum(Map<String, Object> map, SysDbmsChartDimension info, StringBuilder sbWhere, String type1, String tableName) {
	List<String> legend_data = new ArrayList<>();
	List<Map<String, Object>> series_data = new ArrayList<>();
	StringBuilder sql = new StringBuilder();
	Map<String, Object> param = new HashMap<>();
	// 默认表结构
	sql.append(" SELECT  " + type1 + " AS AKS,SUM(`总中标金额`) AS NUM");
	sql.append(" FROM " + tableName + "  ");
	sql.append(" WHERE  DELETE_FLAG = 0  ");
	sql.append(" AND  " + type1 + " IS NOT NULL ");
	sql.append(" AND  " + type1 + " <> '' ");
	sql.append(" AND  公告类型 IN('中标公告','成交公告') ");
	sql.append(sbWhere.toString());
	sql.append(" group by  " + type1);
	
	NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(jdbcTemplate);
	List<Map<String, Object>> listMap = template.queryForList(sql.toString(), param);
	for (Map<String, Object> map2 : listMap) {
		legend_data.add(map2.get("AKS").toString());
		// {value:92503371, name:'男'}
		Map<String, Object> data = new HashMap<>();
		data.put("value", Double.valueOf(map2.get("NUM") == null ? "0" : map2.get("NUM").toString()).longValue());
		data.put("name", map2.get("AKS").toString());
		series_data.add(data);
	}
	map.put("legend_data", legend_data);
	map.put("series_data", series_data);
	map.put("chartType", info.getChartType());

}
 

/**
 * @param tableName
 * 方法名： buildBarOrLineType1
 * 功 能： TODO(这里用一句话描述这个方法的作用)
 * 参 数： @param sbWhere
 * 参 数： @param type1
 * 参 数： @param info
 * 参 数： @param map
 * 返 回： void
 * 作 者 ： Administrator
 * @throws
 */
private void buildBarOrLineType1(StringBuilder sbWhere, String type1, SysDbmsChartDimension info, Map<String, Object> map, String tableName) {
	StringBuilder sql = new StringBuilder();
	Map<String, Object> param = new HashMap<>();
	sql.append("SELECT  " + type1 + " AS ask,COUNT(1) AS num FROM " + tableName + " t " + " WHERE " + type1 + " IS NOT NULL " + " AND " + type1 + "  <>'' " + sbWhere.toString() + " " + " GROUP BY " + type1 + " " + "ORDER BY " + type1 + " ");
	NamedParameterJdbcTemplate template2 = new NamedParameterJdbcTemplate(jdbcTemplate);
	List<Map<String, Object>> listMap2 = template2.queryForList(sql.toString(), param);

	List<Map<String, Object>> series_data = new ArrayList<>();
	List<String> legend_data = new ArrayList<>();
	legend_data.add("数量");
	Map<String, Object> sdata = new HashMap<>();
	sdata.put("type", "tbar".equals(info.getChartType()) ? "bar" : info.getChartType());
	sdata.put("name", "数量");
	List<Integer> series_data_data = new ArrayList<>();
	List<String> xAxis_data = new ArrayList<>();
	for (Map<String, Object> map2 : listMap2) {
		series_data_data.add(Integer.valueOf(map2.get("num").toString()));
		xAxis_data.add(map2.get("ask").toString());
	}
	sdata.put("data", series_data_data);
	series_data.add(sdata);
	map.put("series_data", series_data);
	map.put("xAxis_data", xAxis_data);
	map.put("legend_data", legend_data);
	map.put("chartType", info.getChartType());
}
 

/**
 * @param tableName
 * 方法名： buildMapNoType
 * 功 能： TODO(这里用一句话描述这个方法的作用)
 * 参 数： @param map
 * 参 数： @param info
 * 参 数： @param sbWhere
 * 返 回： void
 * 作 者 ： Administrator
 * @throws
 */
private void buildMapNoType(Map<String, Object> map, SysDbmsChartDimension info, StringBuilder sbWhere, String tableName) {
	StringBuilder sql = new StringBuilder();
	Map<String, Object> param = new HashMap<>();
	
	sql.append(" select  行政区域_省 as province ,count(1) as num");
	sql.append(" from " + tableName + " t ");
	sql.append(" where 1=1 ");
	sql.append(" and  行政区域_省  is not null ");
	sql.append(" and  行政区域_省 <> ''  ");
	sql.append(sbWhere.toString());
	sql.append(" group by  行政区域_省 ");
	
	NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(jdbcTemplate);
	List<Map<String, Object>> listMap = template.queryForList(sql.toString(), param);
	// series_data=[{name:'安徽',value:5483043}, ];
	List<Map<String, Object>> series_data = new ArrayList<>();
	Map<String, Object> mapt = new HashMap<>();
	mapt.put("name", "数量");
	mapt.put("type", "map");
	mapt.put("mapType", "china");
	mapt.put("roam", false);
	
	Map<String, Boolean> emap = new HashMap<>();
	Map<String, Object> label = new HashMap<>();
	emap.put("show", true);
	label.put("emphasis", emap);
	label.put("normal", emap);
	mapt.put("label", label);

	List<Map<String, Object>> series_data_data = new ArrayList<>();
	for (Map<String, Object> map2 : listMap) {
		Map<String, Object> data = new HashMap<>();
		data.put("name", map2.get("province").toString());
		data.put("value", Integer.valueOf(map2.get("num").toString()));
		series_data_data.add(data);
	}
	mapt.put("data", series_data_data);
	series_data.add(mapt);
	
	map.put("series_data", series_data);
	String[] legend_data = { "数量" };
	map.put("legend_data", legend_data);
	map.put("chartType", info.getChartType());
}
 

public void previewData(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
	String sql=req.getParameter("sql");
	String parameters=req.getParameter("parameters");
	Map<String, Object> map = buildParameters(parameters);
	sql=parseSql(sql, map);
	Connection conn=null;
	try{
		conn=buildConnection(req);
		List<Map<String,Object>> list=null;
		if(ProcedureUtils.isProcedure(sql)){
			list=ProcedureUtils.procedureQuery(sql, map, conn);
		}else{
			DataSource dataSource=new SingleConnectionDataSource(conn,false);
			NamedParameterJdbcTemplate jdbc=new NamedParameterJdbcTemplate(dataSource);
			list=jdbc.queryForList(sql, map);				
		}
		int size=list.size();
		int currentTotal=size;
		if(currentTotal>500){
			currentTotal=500;
		}
		List<Map<String,Object>> ls=new ArrayList<Map<String,Object>>();
		for(int i=0;i<currentTotal;i++){
			ls.add(list.get(i));
		}
		DataResult result=new DataResult();
		List<String> fields=new ArrayList<String>();
		if(size>0){
			Map<String,Object> item=list.get(0);
			for(String name:item.keySet()){
				fields.add(name);
			}
		}
		result.setFields(fields);
		result.setCurrentTotal(currentTotal);
		result.setData(ls);
		result.setTotal(size);
		writeObjectToJson(resp, result);
	}catch(Exception ex){
		throw new ServletException(ex);
	}finally{
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
 

/**
 * @param tableName
 * @方法名 buildBarOrLineType2Sum
 * @功能 TODO(这里用一句话描述这个方法的作用)
 * @参数 @param sbWhere
 * @参数 @param type1
 * @参数 @param type2
 * @参数 @param info
 * @参数 @param map
 * @返回 void
 * @author Administrator
 * @throws
 */
private void buildBarOrLineType2Sum(StringBuilder sbWhere, String type1, String type2, SysDbmsChartDimension info, Map<String, Object> map, String tableName) {
	StringBuilder sql = new StringBuilder();
	Map<String, Object> param = new HashMap<>();
	List<Map<String, Object>> series_data = new ArrayList<>();
	sql.append(" SELECT  " + type1 + " AS ASK1," + type2 + " AS ASK2,SUM(`总中标金额`) AS NUM ");
	sql.append(" FROM " + tableName + " t ");
	sql.append(" WHERE  DELETE_FLAG = 0  ");
	sql.append(" AND  公告类型  IN ('中标公告','成交公告') ");
	sql.append(" AND " + type1 + " IS NOT NULL ");
	sql.append(" AND " + type1 + "  <>'' ");
	sql.append(" AND " + type2 + " IS NOT NULL ");
	sql.append(" AND " + type2 + "  <>'' ");
	sql.append(sbWhere.toString() + " ");
	sql.append(" GROUP BY " + type1 + ", " + type2 + " ");
	sql.append("ORDER BY " + type1 + " ," + type2 + " ");
	List<String> legend_data = new ArrayList<>();
	
	NamedParameterJdbcTemplate template2 = new NamedParameterJdbcTemplate(jdbcTemplate);
	List<Map<String, Object>> listMap2 = template2.queryForList(sql.toString(), param);
	List<String> xAxis_data = new ArrayList<>();
	// 分组
	List<List<Map<String, Object>>> gLists = new ArrayList<>();
	buildGroupList(gLists, listMap2, xAxis_data);
	for (List<Map<String, Object>> list : gLists) {
		Map<String, Object> sdata = new HashMap<>();
		sdata.put("type", "tbar".equals(info.getChartType()) ? "bar" : info.getChartType());
		sdata.put("name", list.get(0).get("ASK2").toString());
		if (!legend_data.contains(list.get(0).get("ASK2").toString())) {
			legend_data.add(list.get(0).get("ASK2").toString());
		}
		List<Long> series_data_data = new ArrayList<>();
		for (String string : xAxis_data) {
			boolean check = true;
			for (Map<String, Object> map2 : list) {
				if (map2.get("ASK1").toString().equals(string)) {
					series_data_data.add(Double.valueOf(map2.get("NUM") == null ? "0" : map2.get("NUM").toString()).longValue());
					check = false;
					break;
				}
			}
			if (check) {
				series_data_data.add(Long.valueOf(0));
			}
		}
		sdata.put("data", series_data_data);
		series_data.add(sdata);
	}

	map.put("series_data", series_data);
	map.put("xAxis_data", xAxis_data);
	map.put("legend_data", legend_data);
	map.put("chartType", info.getChartType());
	
}
 

/**
 * @param tableName
 * @方法名 buildBarOrLineType3Sum
 * @功能 TODO(这里用一句话描述这个方法的作用)
 * @参数 @param sbWhere
 * @参数 @param type1
 * @参数 @param type2
 * @参数 @param type3
 * @参数 @param info
 * @参数 @param map
 * @返回 void
 * @author Administrator
 * @throws
 */
private void buildBarOrLineType3Sum(StringBuilder sbWhere, String type1, String type2, String type3, SysDbmsChartDimension info, Map<String, Object> map, String tableName) {
	StringBuilder sql = new StringBuilder();
	Map<String, Object> param = new HashMap<>();
	List<Map<String, Object>> series_data = new ArrayList<>();
	sql.append(" SELECT  " + type1 + " AS ASK1," + type2 + " AS ASK2," + type3 + " AS ASK3,SUM(`总中标金额`) AS NUM ");
	sql.append(" FROM " + tableName + " t ");
	sql.append(" WHERE  DELETE_FLAG = 0  ");
	sql.append(" AND  公告类型  IN ('中标公告','成交公告') ");
	sql.append(" AND " + type1 + " IS NOT NULL ");
	sql.append(" AND " + type1 + "  <>'' ");
	sql.append(" AND " + type2 + " IS NOT NULL ");
	sql.append(" AND " + type2 + "  <>'' ");
	sql.append(" AND " + type3 + " IS NOT NULL ");
	sql.append(" AND " + type3 + "  <>'' ");
	sql.append(sbWhere.toString() + " ");
	sql.append(" GROUP BY " + type1 + ", " + type2 + ", " + type3 + " ");
	sql.append("ORDER BY " + type1 + " ," + type2 + ", " + type3 + " ");
	List<String> legend_data = new ArrayList<>();
	
	NamedParameterJdbcTemplate template2 = new NamedParameterJdbcTemplate(jdbcTemplate);
	List<Map<String, Object>> listMap2 = template2.queryForList(sql.toString(), param);
	List<String> xAxis_data = new ArrayList<>();
	// 分组
	List<List<Map<String, Object>>> gLists = new ArrayList<>();
	buildGroupList(gLists, listMap2, xAxis_data);
	
	for (List<Map<String, Object>> list : gLists) {
		List<List<Map<String, Object>>> gLists2 = new ArrayList<>();
		buildGroupList2(gLists2, list);
		for (List<Map<String, Object>> list2 : gLists2) {
			
			Map<String, Object> sdata = new HashMap<>();
			sdata.put("type", "tbar".equals(info.getChartType()) ? "bar" : info.getChartType());
			sdata.put("name", list2.get(0).get("ASK2").toString() + "." + list2.get(0).get("ASK3").toString());
			sdata.put("stack", list2.get(0).get("ASK2").toString());
			if (!legend_data.contains(list2.get(0).get("ASK2").toString() + "." + list2.get(0).get("ASK3").toString())) {
				legend_data.add(list2.get(0).get("ASK2").toString() + "." + list2.get(0).get("ASK3").toString());
			}
			List<Long> series_data_data = new ArrayList<>();
			for (String string : xAxis_data) {
				boolean check = true;
				for (Map<String, Object> map2 : list2) {
					if (map2.get("ASK1").toString().equals(string)) {
						series_data_data.add(Double.valueOf(map2.get("NUM") == null ? "0" : map2.get("NUM").toString()).longValue());
						check = false;
						break;
					}
				}
				if (check) {
					series_data_data.add(Long.valueOf(0));
				}
			}
			sdata.put("data", series_data_data);
			series_data.add(sdata);
		}
		
	}
	map.put("series_data", series_data);
	map.put("xAxis_data", xAxis_data);
	map.put("legend_data", legend_data);
	map.put("chartType", info.getChartType());
	
}
 

/**
	 * @方法名 startConfixMysqlTableColumnsConfig
	 * @功能 TODO(这里用一句话描述这个方法的作用)
	 * @参数 @param sysZhcxTab
	 * @参数 @param multiDatasource
	 * @参数 @param sysDbmsAdviMessInfoDao
	 * @参数 @param jdbcTemplate
	 * @参数 @param colList
	 * @返回 void
	 * @author Administrator
	 * @throws
	 */
	public static void startConfixMysqlTableColumnsConfig(SysDbmsTabsInfo sysZhcxTab, Map<String, DataSource> multiDatasource, SysDbmsAdviMessInfoDao sysDbmsAdviMessInfoDao, JdbcTemplate jdbcTemplate2, List<SysDbmsTabsColsInfo> colList) {
		// 列配置比较建议修正,平台隐藏，实际长度修改(列修改，列配置修改,列统计信息)
		// 列信息处理会有多个同时执行，遮里配置和列的注释都有可能为空，需要对比
		// 统计信息包含陪的实际最大长度 给出建议缩小列的长度类型， 列空值比例，当空超过60% 给出建议平台默认不展示
		// 实际长度暂不处理
		NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(multiDatasource.get(sysZhcxTab.getJdbcUuid()));
		String tableName = sysZhcxTab.getTabsName();
		SysDbmsAdviMessInfo advice = null;
		for (SysDbmsTabsColsInfo sysZhcxCol : colList) {
			StringBuffer sBuffer = new StringBuffer();
			sBuffer.append(" SELECT tc.TABLE_SCHEMA AS OWNER,tc.TABLE_NAME AS TABLE_NAME,tc.COLUMN_NAME AS COLUMN_NAME,tc.COLUMN_COMMENT AS  comments, tc.COLUMN_TYPE AS DATA_TYPE,tc.COLUMN_KEY FROM information_schema.COLUMNS tc ");
			sBuffer.append(" where concat(tc.TABLE_SCHEMA,'.',tc.TABLE_NAME) = :tablename");
			sBuffer.append(" and tc.COLUMN_NAME = :colName");
			Map<String, String> map = new HashMap<>();
			map.put("tablename", tableName);
			map.put("colName", sysZhcxCol.getColsName());
			List<Map<String, Object>> resultlist = template.queryForList(sBuffer.toString(), map);
			if (resultlist != null && resultlist.size() > 0) {
				Map<String, Object> resultmap = resultlist.get(0);
				// 配置列类型
				if (!resultmap.get("DATA_TYPE").toString().contains((sysZhcxCol.getColsType().toUpperCase()))) {
					advice = new SysDbmsAdviMessInfo(UUID.randomUUID().toString(), "列配置修改", sysZhcxTab.getTabsDesc(), sysZhcxTab.getTabsName(), sysZhcxTab.getJdbcUuid());
					String executeSql = "update sys_dbms_tabs_cols_info t set  t.cols_type = '" + resultmap.get("DATA_TYPE") + "',t.update_time = current_timestamp() where t.uuid='" + sysZhcxCol.getUuid() + "'";
					advice.setExecuteSql(executeSql + ";");
					jdbcTemplate2.execute(executeSql);
					advice.setDeleteFlag(1);
					sysDbmsAdviMessInfoDao.save(advice);
				}
				// 配置列展示
//				if (sysZhcxTab.getTabsRows() != null && sysZhcxTab.getTabsRows() > 10000) {
//					BigDecimal numNulls = (BigDecimal) resultmap.get("NUM_NULLS");
//					if (numNulls != null && numNulls.intValue() != 0) {
//						if (numNulls.subtract(new BigDecimal(sysZhcxTab.getTabsRows()).multiply(new BigDecimal(0.6f))).intValue() > 0 && 1 == sysZhcxCol.getPageList().intValue()) {
//							advice = new SysDbmsAdviMessInfo(UUID.randomUUID().toString(), "列配置修改", sysZhcxTab.getTabsDesc(), tableName, sysZhcxTab.getJdbcUuid());
//							StringBuilder sBuilder = new StringBuilder();
//							sBuilder.append("-- 表中的空值超过 60% 建议默认列表不展示");
//							advice = new SysDbmsAdviMessInfo(UUID.randomUUID().toString(), "列配置修改", sysZhcxTab.getTabsDesc(), sysZhcx4Tab.getTabsName(), sysZhcxTab.getJdbcUuid());
//							sBuilder.append("update sys_dbms_tabs_cols_info t set  t.PAGE_LIST = '0',t.update_time = sysdate where t.uuid='" + sysZhcxCol.getUuid() + "'");
//							advice.setMessage(sBuilder.toString() + ";");
//							advice.setDeleteFlag(0);
//							sysDbmsAdviMessInfoDao.save(advice);
//						}
//					}
//				}
				// 注释和翻译
				if (sysZhcxCol.getColsDesc() != null && resultmap.get("comments") != null) {
					StringBuilder sBuilder = new StringBuilder();
					advice = new SysDbmsAdviMessInfo(UUID.randomUUID().toString(), "列配置修改", sysZhcxTab.getTabsDesc(), sysZhcxTab.getTabsName(), sysZhcxTab.getJdbcUuid());
					if (sysZhcxTab.getTabsDesc() == null || "".equals(sysZhcxTab.getTabsDesc())) {
						sBuilder.append("-- 由于配置中的信息没有，建议执行以下语句进行统一：\n");
						sBuilder.append("update sys_dbms_tabs_cols_info t set t.COLS_DESC ='" + resultmap.get("comments") + "' ,t.update_time = sysdate where t.uuid='" + sysZhcxCol.getUuid() + "'; \n");
					} else if (resultmap.get("comments") == null || "".equals(resultmap.get("comments"))) {
						sBuilder.append("-- 由于表中字段注释信息没有，建议执行以下语句进行统一：\n");
						sBuilder.append("alter table " + tableName + "  modify column " + sysZhcxCol.getColsName() + " VARCHAR(500) comment '" + sysZhcxCol.getColsDesc() + "';\n");
					} else if (!sysZhcxCol.getColsDesc().equals(resultmap.get("comments"))) {
						sBuilder.append("-- 由于表中字段注释信息和配置中的信息不一致，建议执行以下语句进行统一：\n");
						sBuilder.append("-- 建议 一 根据表信息 更新配置表中的信息.\n");
						sBuilder.append("--  update sys_dbms_tabs_cols_info t set t.COLS_DESC ='" + resultmap.get("comments") + "' ,t.update_time = sysdate where t.uuid='" + sysZhcxCol.getUuid() + "'; \n");
						sBuilder.append("-- 建议 二 根据配置表中的信息更新表信息 .\n");
						sBuilder.append("alter table  " + tableName + " modify column " + sysZhcxCol.getColsName() + " VARCHAR(500) comment '" + sysZhcxCol.getColsDesc() + "';\n");
					} else {
						return;
					}
					
					advice.setMessage(sBuilder.toString());
					advice.setDeleteFlag(0);
					sysDbmsAdviMessInfoDao.save(advice);
				}
				// 列数据统计建议添加索引，平台隐藏，实际长度修改(索引修改或重建，索引添加，)
				startConfixMysqlTableCloumnIndexConfig(sysZhcxTab, sysZhcxCol, resultlist, sysDbmsAdviMessInfoDao);
			}

		}
		
	}
 

/**
 * @param tableName
 * 方法名： buildBarOrLineType3
 * 功 能： TODO(这里用一句话描述这个方法的作用)
 * 参 数： @param sbWhere
 * 参 数： @param type1
 * 参 数： @param type2
 * 参 数： @param type3
 * 参 数： @param info
 * 参 数： @param map
 * 返 回： void
 * 作 者 ： Administrator
 * @throws
 */
private void buildBarOrLineType3(StringBuilder sbWhere, String type1, String type2, String type3, SysDbmsChartDimension info, Map<String, Object> map, String tableName) {
	StringBuilder sql = new StringBuilder();
	Map<String, Object> param = new HashMap<>();
	List<Map<String, Object>> series_data = new ArrayList<>();
	sql.append("SELECT  " + type1 + " AS ask1," + type2 + " AS ask2," + type3 + " AS ask3,COUNT(1) AS num FROM " + tableName + " t " + " WHERE " + type1 + " IS NOT NULL " + " AND " + type1 + "  <>'' " + " AND " + type2 + " IS NOT NULL " + " AND " + type2 + "  <>'' " + " AND " + type3 + " IS NOT NULL " + " AND " + type3 + "  <>'' " + sbWhere.toString() + " " + " GROUP BY " + type1 + ", " + type2 + ", " + type3 + " " + "ORDER BY " + type1 + " ," + type2 + ", " + type3 + " ");
	List<String> legend_data = new ArrayList<>();
	
	NamedParameterJdbcTemplate template2 = new NamedParameterJdbcTemplate(jdbcTemplate);
	List<Map<String, Object>> listMap2 = template2.queryForList(sql.toString(), param);
	List<String> xAxis_data = new ArrayList<>();
	// 分组
	List<List<Map<String, Object>>> gLists = new ArrayList<>();
	buildGroupList(gLists, listMap2, xAxis_data);
	
	for (List<Map<String, Object>> list : gLists) {
		List<List<Map<String, Object>>> gLists2 = new ArrayList<>();
		buildGroupList2(gLists2, list);
		for (List<Map<String, Object>> list2 : gLists2) {
			
			Map<String, Object> sdata = new HashMap<>();
			sdata.put("type", "tbar".equals(info.getChartType()) ? "bar" : info.getChartType());
			sdata.put("name", list2.get(0).get("ask2").toString() + "." + list2.get(0).get("ask3").toString());
			sdata.put("stack", list2.get(0).get("ask2").toString());
			if (!legend_data.contains(list2.get(0).get("ask2").toString() + "." + list2.get(0).get("ask3").toString())) {
				legend_data.add(list2.get(0).get("ask2").toString() + "." + list2.get(0).get("ask3").toString());
			}
			List<Integer> series_data_data = new ArrayList<>();
			for (String string : xAxis_data) {
				boolean check = true;
				for (Map<String, Object> map2 : list2) {
					if (map2.get("ask1").toString().equals(string)) {
						series_data_data.add(Integer.valueOf(map2.get("num").toString()));
						check = false;
						break;
					}
				}
				if (check) {
					series_data_data.add(Integer.valueOf(0));
				}
			}
			sdata.put("data", series_data_data);
			series_data.add(sdata);
		}
		
	}
	map.put("series_data", series_data);
	map.put("xAxis_data", xAxis_data);
	map.put("legend_data", legend_data);
	map.put("chartType", info.getChartType());

}
 

/**
 * @param tableName
 * @方法名 buildMapNoTypeSum
 * @功能 TODO(这里用一句话描述这个方法的作用)
 * @参数 @param map
 * @参数 @param info
 * @参数 @param sbWhere
 * @返回 void
 * @author Administrator
 * @throws
 */
private void buildMapNoTypeSum(Map<String, Object> map, SysDbmsChartDimension info, StringBuilder sbWhere, String tableName) {
	StringBuilder sql = new StringBuilder();
	Map<String, Object> param = new HashMap<>();

	sql.append(" SELECT  行政区域_省 AS PROVINCE ,SUM(`总中标金额`) AS NUM");
	sql.append(" FROM " + tableName + " t ");
	sql.append(" WHERE  DELETE_FLAG = 0  ");
	sql.append(" AND  公告类型  IN ('中标公告','成交公告') ");
	sql.append(" AND  行政区域_省  IS NOT NULL ");
	sql.append(" AND  行政区域_省 <> ''  ");
	sql.append(sbWhere.toString());
	sql.append(" GROUP BY  行政区域_省 ");

	NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(jdbcTemplate);
	List<Map<String, Object>> listMap = template.queryForList(sql.toString(), param);
	// series_data=[{name:'安徽',value:5483043}, ];
	List<Map<String, Object>> series_data = new ArrayList<>();
	Map<String, Object> mapt = new HashMap<>();
	mapt.put("name", "金额");
	mapt.put("type", "map");
	mapt.put("mapType", "china");
	mapt.put("roam", false);

	Map<String, Boolean> emap = new HashMap<>();
	Map<String, Object> label = new HashMap<>();
	emap.put("show", true);
	label.put("emphasis", emap);
	Map<String, Boolean> normal = new HashMap<>();
	normal.put("show", false);
	label.put("normal", normal);
	mapt.put("label", label);
	
	List<Map<String, Object>> series_data_data = new ArrayList<>();
	for (Map<String, Object> map2 : listMap) {
		Map<String, Object> data = new HashMap<>();
		data.put("name", map2.get("PROVINCE").toString());
		data.put("value", Double.valueOf(map2.get("NUM") == null ? "0" : map2.get("NUM").toString()).longValue());
		series_data_data.add(data);
	}
	mapt.put("data", series_data_data);
	series_data.add(mapt);

	map.put("series_data", series_data);
	String[] legend_data = { "金额" };
	map.put("legend_data", legend_data);
	map.put("chartType", info.getChartType());
	
}
 

@Override
	public List<CaseInsensitiveMap<String, Object>> getCustomers(List<Integer> customerIDs, int companyID) {
		CaseInsensitiveMap<String, ProfileField> profileMap;
		try {
			profileMap = loadCustDBProfileStructure(companyID);
		} catch (Exception e) {
			logger.error("getCustomers: Exception in getQueryProperties", e);
			return Collections.emptyList();
		}

		String query = "SELECT * FROM " + getCustomerTableName(companyID) + " WHERE customer_id IN(:ids) AND " + ComCompanyDaoImpl.STANDARD_FIELD_BOUNCELOAD + " = 0";
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		parameters.addValue("ids", customerIDs);

		NamedParameterJdbcTemplate jTmpl = new NamedParameterJdbcTemplate(getDataSource());
		List<Map<String, Object>> queryResult = jTmpl.queryForList(query, parameters);
	
		List<CaseInsensitiveMap<String, Object>> results = new ArrayList<>();
	
		GregorianCalendar calendar = new GregorianCalendar();
	
//		long estimatedTime = System.nanoTime() - startTime;
//		logger.warn("getCustomers: after query before processing " + estimatedTime + "ns");

		for (Map<String, Object> row : queryResult) {
			CaseInsensitiveMap<String, Object> params = new CaseInsensitiveMap<>();
		
			for (Entry<String, ProfileField> entry : profileMap.entrySet()) {
				String columnName = entry.getKey();
				String columnType = entry.getValue().getDataType();
				Object value = row.get(columnName);
				if ("DATE".equalsIgnoreCase(columnType)) {
					if (value == null) {
						Map<String, String> dateColumnEmptyValues = SUPPLEMENTAL_DATE_COLUMN_SUFFIXES.stream()
								.map(suffix -> columnName + suffix)
								.collect(Collectors.toMap(Function.identity(), pair -> ""));
						dateColumnEmptyValues.put(columnName, "");

						params.putAll(dateColumnEmptyValues);
					} else {
						calendar.setTime((Date) value);
						params.put(columnName + ComRecipientDao.SUPPLEMENTAL_DATECOLUMN_SUFFIX_DAY, Integer.toString(calendar.get(GregorianCalendar.DAY_OF_MONTH)));
						params.put(columnName + ComRecipientDao.SUPPLEMENTAL_DATECOLUMN_SUFFIX_MONTH, Integer.toString(calendar.get(GregorianCalendar.MONTH) + 1));
						params.put(columnName + ComRecipientDao.SUPPLEMENTAL_DATECOLUMN_SUFFIX_YEAR, Integer.toString(calendar.get(GregorianCalendar.YEAR)));
						params.put(columnName + ComRecipientDao.SUPPLEMENTAL_DATECOLUMN_SUFFIX_HOUR, Integer.toString(calendar.get(GregorianCalendar.HOUR_OF_DAY)));
						params.put(columnName + ComRecipientDao.SUPPLEMENTAL_DATECOLUMN_SUFFIX_MINUTE, Integer.toString(calendar.get(GregorianCalendar.MINUTE)));
						params.put(columnName + ComRecipientDao.SUPPLEMENTAL_DATECOLUMN_SUFFIX_SECOND, Integer.toString(calendar.get(GregorianCalendar.SECOND)));
						params.put(columnName, new SimpleDateFormat(DateUtilities.YYYY_MM_DD_HH_MM_SS).format(calendar.getTime()));
					}
				} else {
					if (value == null) {
						value = "";
					}
					params.put(columnName, value.toString());
				}
			}
		
			results.add(params);
		}
	
//		estimatedTime = System.nanoTime() - startTime;
//		logger.warn("getCustomers: after processing " + estimatedTime + "ns");
		
		return results;
	}