/**
* 从数据库获取一个页面的结果
*
* @param eleConfig Element
* @param sqlConfig SqlConfig
* @param request HttpServletRequest
* @param queryValue QueryValue
*/
public void fetchPageResultFromDb(
Element eleConfig, SqlConfig sqlConfig, HttpServletRequest request, QueryValue queryValue) {
DataSource ds = BaseFrameworkApplication.getBaseJdbcDAO().getDataSource();
Connection conn = null;
Statement stmt = null;
ResultSet resultSet = null;
String sql = null;
try {
conn = ds.getConnection();
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
sql = sqlConfig.sqlData + sqlConfig.sqlOrder;
resultSet = stmt.executeQuery(sql);
if (logger.isDebugEnabled()) {
logger.debug(
"通用查询：查询" + this.query.getQueryName() + "获取数据，Sql为：" + StringUtils.LINE_SEPARATOR);
logger.debug(sql.replaceAll("[\n][ \t]*", "\n").replaceAll("[\n]+", "\n"));
}
// 获取标题
ResultSetMetaData rsmd = resultSet.getMetaData();
int columnCount = rsmd.getColumnCount();
queryValue.title = new String[columnCount];
queryValue.dataType = new int[columnCount];
for (int i = 0; i < rsmd.getColumnCount(); i++) {
queryValue.dataType[i] = rsmd.getColumnType(i + 1);
queryValue.title[i] = rsmd.getColumnLabel(i + 1);
}
// TODO 首次是否查询
if (true) {
// 计算位置并跳转
int position = 0;
if (queryValue.pageSize > 0) {
position = queryValue.pageSize * (queryValue.pageNo - 1);
if (position > 0) resultSet.absolute(position);
}
// 查询数据
int record = 0;
while ((record < queryValue.pageSize || queryValue.pageSize == -1) && resultSet.next()) {
String[] data = new String[columnCount];
for (int i = 0; i < rsmd.getColumnCount(); i++) {
data[i] = resultSet.getString(i + 1);
}
queryValue.dataList.add(data);
record++;
}
if (resultSet.next()) queryValue.hasNextPage = true;
resultSet.close();
// 查询统计数据
if (sqlConfig.sqlDataSum.length() > 0) {
sql = sqlConfig.sqlDataSum;
resultSet = stmt.executeQuery(sql);
rsmd = resultSet.getMetaData();
if (resultSet.next()) {
queryValue.dataSum = new String[queryValue.title.length];
for (int i = 0; i < rsmd.getColumnCount() && i < queryValue.dataSum.length; i++) {
queryValue.dataSum[i] = resultSet.getString(i + 1);
}
}
}
if (needCount && sqlConfig.sqlCount.length() > 0) {
sql = sqlConfig.sqlCount;
resultSet = stmt.executeQuery(sql);
if (resultSet.next()) {
queryValue.recordCount = resultSet.getInt(1);
}
}
if (queryValue.groupList != null) {
// Group从数据中获取MetaData
for (int i = 0; i < queryValue.groupList.size(); i++) {
Group group = (Group) queryValue.groupList.get(i);
// 仅对没有metadata的进行处理（没有配置metadata获取方式）
if (group.metadata.size() == 0) {
ArrayList metadataList = new ArrayList();
// 在数据集中循环
for (int j = 0; j < queryValue.dataList.size(); j++) {
String[] data = (String[]) queryValue.dataList.get(j);
// 数组越界检查
if (data.length < i) continue;
// 在metadataList中查询是否存在
boolean exist = false;
for (int k = 0; k < metadataList.size(); k++) {
if (((String[]) metadataList.get(k))[0].equals(data[i])) {
exist = true;
break;
}
}
// 如果metadataList中没有就插入
if (!exist && data[i] != null) metadataList.add(new String[] {data[i]});
}
group.metadata = metadataList;
} // if(group.metadata.size() == 0)
} // for(int i = 0; i < queryValue.groupList.size(); i++)
} // Group从数据中获取MetaData
}
} catch (SQLException ex) {
throw new JdbcException("通用查询：Sql执行错误", sql, ex);
} finally {
JdbcUtils.close(resultSet, stmt, conn);
}
}
/**
* 获取需要缓存的数据结果集
*
* @param eleConfig Element
* @param sqlConfig SqlConfig
* @param request HttpServletRequest
* @param queryValue QueryValue
* @return boolean
*/
public boolean fetchCacheResultFromDb(
Element eleConfig, SqlConfig sqlConfig, HttpServletRequest request, QueryValue queryValue) {
CalTime calTime = new CalTime();
CalTime calTimeTotal = new CalTime();
calTimeTotal.begin();
calTime.begin();
DataSource ds = BaseFrameworkApplication.getBaseJdbcDAO().getDataSource();
Connection conn = null;
Statement stmt = null;
ResultSet resultSet = null;
String sql = null;
queryValue
.calTimeSb
.append("初始化时间：")
.append(calTime.endString())
.append(StringUtils.LINE_SEPARATOR);
try {
// 根据SqlData从缓存获取缓存结果
calTime.begin();
HashMap sqlCacheMap = null;
if (cacheScope == this.CACHE_APPLICATION) sqlCacheMap = cacheHashMap;
if (cacheScope == this.CACHE_SESSION) {
Object objSqlCacheMap =
SessionUtils.getObjectAttribute(request, SessionNameConstants.SQL_CACHE_MAP);
if (objSqlCacheMap != null) sqlCacheMap = (HashMap) objSqlCacheMap;
else {
sqlCacheMap = new HashMap();
SessionUtils.setObjectAttribute(request, SessionNameConstants.SQL_CACHE_MAP, sqlCacheMap);
}
}
Object objCacheQueryValue = sqlCacheMap.get(sqlConfig.sqlData);
queryValue
.calTimeSb
.append("根据SqlData从缓存获取缓存结果时间：")
.append(calTime.endString())
.append(StringUtils.LINE_SEPARATOR);
if (objCacheQueryValue != null
&& objCacheQueryValue instanceof QueryValue
&& sqlConfig.isOverrideCache == false) {
calTime.begin();
QueryValue cacheQueryValue = (QueryValue) objCacheQueryValue;
queryValue.dataSum = cacheQueryValue.dataSum;
queryValue.allDataList = cacheQueryValue.allDataList;
queryValue.recordCount = cacheQueryValue.recordCount;
queryValue.title = cacheQueryValue.title;
queryValue
.calTimeSb
.append("根据SqlData缓存结果赋值时间：")
.append(calTime.endString())
.append(StringUtils.LINE_SEPARATOR);
} else {
calTime.begin();
conn = ds.getConnection();
queryValue
.calTimeSb
.append("获取连接时间：")
.append(calTime.endString())
.append(StringUtils.LINE_SEPARATOR);
calTime.begin();
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
queryValue
.calTimeSb
.append("创建Statement时间：")
.append(calTime.endString())
.append(StringUtils.LINE_SEPARATOR);
// 获取Count
/*
sql = sqlConfig.sqlCount;
resultSet = stmt.executeQuery(sql);
if(resultSet.next())
{
queryValue.recordCount = resultSet.getInt(1);
}
if(queryValue.recordCount > cacheLimit)
return false;
LogUtils.logTimer(logger,"通用查询：获取数据：Count时间" + calTime.endString());
*/
calTime.begin();
sql = sqlConfig.sqlData + sqlConfig.sqlOrder;
resultSet = stmt.executeQuery(sql);
if (logger.isDebugEnabled()) {
logger.debug(
"通用查询：查询" + this.query.getQueryName() + "获取数据，Sql为：" + StringUtils.LINE_SEPARATOR);
logger.debug(sql.replaceAll("[\n][ \t]*", "\n").replaceAll("[\n]+", "\n"));
}
queryValue
.calTimeSb
.append("Sql执行时间：")
.append(calTime.endString())
.append(StringUtils.LINE_SEPARATOR);
// 获取标题
calTime.begin();
ResultSetMetaData rsmd = resultSet.getMetaData();
int columnCount = rsmd.getColumnCount();
queryValue.title = new String[columnCount];
queryValue.dataType = new int[columnCount];
for (int i = 0; i < rsmd.getColumnCount(); i++) {
queryValue.dataType[i] = rsmd.getColumnType(i + 1);
queryValue.title[i] = rsmd.getColumnLabel(i + 1);
}
queryValue
.calTimeSb
.append("获取标题时间：")
.append(calTime.endString())
.append(StringUtils.LINE_SEPARATOR);
// 获取数据
calTime.begin();
while (resultSet.next()) {
String[] data = new String[columnCount];
for (int i = 0; i < rsmd.getColumnCount(); i++) {
data[i] = resultSet.getString(i + 1);
}
queryValue.allDataList.add(data);
}
resultSet.close();
queryValue.recordCount = queryValue.allDataList.size();
queryValue
.calTimeSb
.append("获取数据时间：")
.append(calTime.endString())
.append(StringUtils.LINE_SEPARATOR);
// 随机处理
calTime.begin();
if (needRadom) {
queryValue.allDataList = ArrayListUtils.random(queryValue.allDataList);
}
queryValue
.calTimeSb
.append("随机处理时间：")
.append(calTime.endString())
.append(StringUtils.LINE_SEPARATOR);
// 统计数据获取
if (sqlConfig.sqlDataSum.length() > 0) {
calTime.begin();
sql = sqlConfig.sqlDataSum;
resultSet = stmt.executeQuery(sql);
rsmd = resultSet.getMetaData();
if (resultSet.next()) {
queryValue.dataSum = new String[queryValue.title.length];
for (int i = 0; i < rsmd.getColumnCount() && i < queryValue.dataSum.length; i++) {
queryValue.dataSum[i] = resultSet.getString(i + 1);
}
}
queryValue
.calTimeSb
.append("统计数据时间：")
.append(calTime.endString())
.append(StringUtils.LINE_SEPARATOR);
}
}
sqlCacheMap.put(sqlConfig.sqlData, queryValue);
queryValue
.calTimeSb
.append("从缓存获取数据时间：")
.append(calTimeTotal.endString())
.append(StringUtils.LINE_SEPARATOR);
return true;
} catch (SQLException ex) {
throw new JdbcException("通用查询：Sql执行错误", sql, ex);
} finally {
JdbcUtils.close(resultSet, stmt, conn);
}
}
/**
* 运行查询
*
* @param request HttpServletRequest
* @return QueryValue
*/
public QueryValue run(HttpServletRequest request) {
CalTime calTime = new CalTime();
CalTime calTimeSub = new CalTime();
// 获取结果
QueryValue queryValue = new QueryValue();
request.setAttribute("queryValue", queryValue);
// 获取运行次数
queryValue.queryName = this.getQuery().getQueryName();
queryValue.lastQueryName = RequestUtils.getStringParameter(request, "QueryName", "");
if (queryValue.queryName.equals(queryValue.lastQueryName)) queryValue.runTime = 1;
else {
if (queryValue.lastQueryName.equals("")) queryValue.runTime = 0;
else queryValue.runTime = -1;
}
// 解析参数中的页面大小和页数
queryValue.pageSize = RequestUtils.getIntParameter(request, "query_pagesize", 0);
if (queryValue.pageSize == 0) queryValue.pageSize = this.pageSize;
queryValue.pageNo =
RequestUtils.getIntParameter(request, queryValue.queryName + "_query_pageno", 1);
// 将配置的Sql元素传送给SqlConfig
SqlConfig sqlConfig = new SqlConfig();
SqlString sqlStringSelect = new SqlString(this.sqlSelect);
sqlConfig.sqlSelect = sqlStringSelect.getParseSql(request, null);
// sqlConfig.sqlStringSelect = this.sqlStringSelect;
sqlConfig.sqlStringFromWhere = this.sqlStringFromWhere;
// 统计解析
String sqlGroup = "";
String sqlGroupSelectGroup = "";
queryValue.drillURL = drillURL;
queryValue.needTotalX = this.needTotalX;
queryValue.needTotalY = this.needTotalY;
if (groupList != null) {
// 统计没有分页
queryValue.pageSize = -1;
StringBuffer sqlGroupSb = new StringBuffer();
StringBuffer sqlGroupSelectGroupSb = new StringBuffer();
queryValue.groupRalationList = this.groupRelationList;
queryValue.groupList = new ArrayList();
// 初始化XYZ轴
if (queryValue.runTime != 1) // 第一次运行和从别的查询过来
{
queryValue.groupListY = (ArrayList) this.groupListY.clone();
queryValue.groupListX = (ArrayList) this.groupListX.clone();
queryValue.groupListSelect = (ArrayList) this.groupListSelect.clone();
queryValue.groupList = (ArrayList) this.groupList.clone();
} // end 初始化XYZ轴 if
else {
// 从Request中获取用户选择的X、Y轴
queryValue.groupListY = new ArrayList();
queryValue.groupListX = new ArrayList();
queryValue.groupListSelect = new ArrayList();
// Y轴
String[] groupYName = RequestUtils.getStringParameters(request, "groupYName");
String[] checkGroupYSubTotal =
RequestUtils.getStringParameters(request, "checkGroupYSubTotal");
for (int i = 0; i < groupYName.length; i++) {
Object obj = groupMap.get(groupYName[i]);
if (obj != null) {
Group group = (Group) ((Group) obj).clone();
group.position = 'y' + String.valueOf(i + 1);
if (ArrayUtils.contains(checkGroupYSubTotal, groupYName[i])) group.needSubTotal = true;
else group.needSubTotal = false;
queryValue.groupListY.add(group);
}
}
// X轴
String[] groupXName = RequestUtils.getStringParameters(request, "groupXName");
for (int i = 0; i < groupXName.length; i++) {
Object obj = groupMap.get(groupXName[i]);
if (obj != null) {
Group group = (Group) ((Group) obj).clone();
group.position = 'x' + String.valueOf(i + 1);
queryValue.groupListX.add(group);
}
}
// Z轴
String[] groupList = RequestUtils.getStringParameters(request, "groupZName");
for (int i = 0; i < groupList.length; i++) {
Object obj = groupMap.get(groupList[i]);
if (obj != null) {
Group group = (Group) ((Group) obj).clone();
group.position = String.valueOf(i + 1);
queryValue.groupListSelect.add(group);
}
}
queryValue.groupList.addAll(queryValue.groupListX);
queryValue.groupList.addAll(queryValue.groupListY);
} // end 初始化XYZ轴 else
if (!(queryValue.runTime == 0 && !firstShow)) {
for (int i = 0; i < queryValue.groupList.size(); i++) {
Group group = (Group) queryValue.groupList.get(i);
if (group.metadata.size() == 0 && !group.metadatasql.equals("")) {
group.metadata =
JdbcUtils.queryForList(
group.metadataSqlString.getParseSql(request, this.replaceMap));
}
sqlGroupSb.append(group.macrovalue).append(',');
sqlGroupSelectGroupSb
.append(group.macrovalue)
.append(" as ")
.append(group.groupLabel)
.append(',');
}
sqlGroup = sqlGroupSb.deleteCharAt(sqlGroupSb.length() - 1).toString();
sqlGroupSelectGroup = sqlGroupSelectGroupSb.toString();
}
}
// 如果不是第1次并配置为第1次不运行
if (queryValue.runTime == 0 && !firstShow) {
queryValue.title = new String[0];
} else {
queryValue.dataList = new ArrayList();
queryValue.allDataList = new ArrayList();
// 进行sqlFromWhere的解析
sqlConfig.sqlFromWhere = sqlConfig.sqlStringFromWhere.getParseSql(request, this.replaceMap);
// sqlConfig.sqlSelect = sqlConfig.sqlStringSelect.getParseSql(request, this.replaceMap);
if (sqlGroup.length() > 0)
sqlConfig.sqlData =
"select "
+ sqlGroupSelectGroup
+ sqlGroupSelect.getSql(request)
+ " "
+ sqlConfig.sqlFromWhere
+ " group by "
+ sqlGroup;
else {
sqlConfig.sqlData = "select " + sqlConfig.sqlSelect + " " + sqlConfig.sqlFromWhere;
sqlConfig.sqlSum = this.sqlSum;
if (sqlConfig.sqlSum.length() > 0)
sqlConfig.sqlDataSum = "select " + sqlConfig.sqlSum + " " + sqlConfig.sqlFromWhere;
sqlConfig.sqlCount =
"select count(*) from (" + sqlConfig.sqlData + ")"; // + sqlConfig.sqlFromWhere;
String query_sortcol = RequestUtils.getStringParameter(request, "query_sortcol", "");
sqlConfig.sqlOrder = this.sqlOrder;
if (!StringUtils.isBlank(query_sortcol)) {
sqlConfig.sqlOrder =
" order by "
+ query_sortcol
+ " "
+ RequestUtils.getStringParameter(request, "query_sortdir", "");
}
sqlConfig.sqlOrder = StringUtils.trimToEmpty(sqlConfig.sqlOrder);
}
// 从数据库获取数据
calTime.begin();
if (needCache
&& agentClass.fetchCacheResultFromDb(eleConfig, sqlConfig, request, queryValue)) {
int position = queryValue.pageSize * (queryValue.pageNo - 1);
int maxsize = position + queryValue.pageSize;
if (queryValue.allDataList.size() <= maxsize) maxsize = queryValue.allDataList.size();
else {
queryValue.hasNextPage = true;
}
if (queryValue.pageSize == -1) {
position = 0;
maxsize = queryValue.allDataList.size();
}
for (int i = position; i < maxsize; i++) {
queryValue.dataList.add(queryValue.allDataList.get(i));
}
} else {
calTimeSub.begin();
agentClass.fetchPageResultFromDb(eleConfig, sqlConfig, request, queryValue);
queryValue
.calTimeSb
.append("从数据库获取数据时间：")
.append(calTimeSub.endString())
.append(StringUtils.LINE_SEPARATOR);
}
queryValue
.calTimeSb
.append("总共获取数据时间：")
.append(calTime.endString())
.append(StringUtils.LINE_SEPARATOR);
}
return queryValue;
}
