下面列出了org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#queryForList ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
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());
}
@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;
}
/**
* @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());
}
/**
* @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());
}
/**
* @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());
}
/**
* @方法名 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
* @方法名 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());
}
/**
* @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());
}
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();
}
}
}
}