/**
* Construct a ResultList from SQL query ResultSet with at most maxCount rows.
*
* @param rs
* @param maxCount If positive number, at most that number of records will be returned
* @return
* @throws SQLException
*/
public static ResultList fromSqlResultSet(ResultSet rs, int maxCount) throws SQLException {
logger.fine(new java.util.Date() + ": Process results ...");
ResultList resList = new ResultList();
if (rs == null) return resList;
java.sql.ResultSetMetaData meta = rs.getMetaData();
int colCnt = meta.getColumnCount();
ColumnDescriptor desc = new ColumnDescriptor();
desc.setColumns(new java.util.ArrayList<ColumnInfo>(colCnt));
for (int i = 1; i <= colCnt; i++) {
// for now, we only record name
ColumnInfo col = new ColumnInfo(meta.getColumnName(i));
int sqlt = meta.getColumnType(i);
if (sqlt == java.sql.Types.BIGINT
|| sqlt == java.sql.Types.DECIMAL
|| sqlt == java.sql.Types.DOUBLE
|| sqlt == java.sql.Types.FLOAT
|| sqlt == java.sql.Types.INTEGER
|| sqlt == java.sql.Types.NUMERIC
|| sqlt == java.sql.Types.TINYINT
|| sqlt == java.sql.Types.SMALLINT) col.setNumberType(true);
col.setPosition(i);
desc.getColumns().add(col);
}
resList.setColumnDescriptor(desc);
int rowCnt = 0;
List<ColumnInfo> cols = desc.getColumns();
while (rs.next()) {
// logger.info(new java.util.Date()+": process "+rowCnt+" rows");
ResultRow row = new ResultRow();
row.setColumnDescriptor(desc);
java.util.ArrayList<String> cols2 = new java.util.ArrayList<String>(colCnt);
row.setColumns(cols2);
for (int i = 1; i <= colCnt; i++) {
String val = rs.getString(i);
if (cols.get(i - 1).isNumberType() && val != null && val.startsWith("."))
val = "0" + val; // prefix Oracle float number with 0 if starting with "."
else if (cols.get(i - 1).isNumberType() && val != null && val.startsWith("-."))
val = val.replace("-.", "-0."); // prefix Oracle float number with 0 if starting with "."
cols2.add(val);
}
resList.addRow(row);
rowCnt++;
if (maxCount > 0 && rowCnt >= maxCount) break;
}
logger.fine(new java.util.Date() + ": Process results done: " + resList.getRows().size());
return resList;
}
/**
* This method will flat the rows to columns if Sql is configured that way
*
* @param sql
* @param rs
* @param maxCount
* @return
* @throws SQLException
*/
public static ResultList flatSqlResultSet(Sql sql, ResultSet rs, int maxCount)
throws SQLException {
if (sql.isExpandRow()) return expandSqlResultSet(sql, rs);
else if (sql == null
|| sql.getFlatKey() == null
|| sql.getFlatKey().trim().length() == 0
|| sql.getFlatValueList().size() == 0) {
return fromSqlResultSet(rs, maxCount);
}
ResultList resList = new ResultList();
if (rs == null) return resList;
java.sql.ResultSetMetaData meta = rs.getMetaData();
int colCnt = meta.getColumnCount();
ColumnDescriptor desc = new ColumnDescriptor();
desc.setColumns(new java.util.ArrayList<ColumnInfo>(colCnt));
Map<String, Integer> typeMap = new HashMap<String, Integer>();
for (int i = 1; i <= colCnt; i++) {
typeMap.put(meta.getColumnName(i), meta.getColumnType(i));
}
Map<String, Integer> flatkeyIdx = new HashMap<String, Integer>();
for (int i = 0; i < sql.getFlatValueList().size(); i++) {
// logger.info("Add "+sql.getFlatValueList().get(i));
flatkeyIdx.put(sql.getFlatValueList().get(i), i);
}
int colIndex = 1;
// first all keys
for (String k : sql.getKeyList()) {
ColumnInfo col = new ColumnInfo(k);
col.setPosition(colIndex);
int sqlt = typeMap.get(k);
if (sqlt == java.sql.Types.BIGINT
|| sqlt == java.sql.Types.DECIMAL
|| sqlt == java.sql.Types.DOUBLE
|| sqlt == java.sql.Types.FLOAT
|| sqlt == java.sql.Types.INTEGER
|| sqlt == java.sql.Types.NUMERIC
|| sqlt == java.sql.Types.TINYINT
|| sqlt == java.sql.Types.SMALLINT) col.setNumberType(true);
desc.getColumns().add(col);
colIndex++;
}
// next all metrics
for (String s : sql.getFlatValueList()) {
for (Map.Entry<String, String> e : sql.getMetrics().entrySet()) {
ColumnInfo col = new ColumnInfo(sql.getFlatValueAbbrMap().get(s) + e.getValue());
col.setPosition(colIndex);
int sqlt = typeMap.get(e.getKey());
if (sqlt == java.sql.Types.BIGINT
|| sqlt == java.sql.Types.DECIMAL
|| sqlt == java.sql.Types.DOUBLE
|| sqlt == java.sql.Types.FLOAT
|| sqlt == java.sql.Types.INTEGER
|| sqlt == java.sql.Types.NUMERIC
|| sqlt == java.sql.Types.TINYINT
|| sqlt == java.sql.Types.SMALLINT) col.setNumberType(true);
desc.getColumns().add(col);
colIndex++;
}
}
resList.setColumnDescriptor(desc);
int rowCnt = 0;
List<ColumnInfo> cols = desc.getColumns();
String[] prevkeys = new String[sql.getKeyList().size()];
ResultRow row = null;
while (rs.next()) {
String[] newkeys = new String[sql.getKeyList().size()];
for (int i = 0; i < sql.getKeyList().size(); ++i) {
newkeys[i] = rs.getString(sql.getKeyList().get(i));
}
if (!isSame(prevkeys, newkeys)) // start a new row
{
row = new ResultRow();
row.setColumnDescriptor(desc);
row.setColumns(new java.util.ArrayList<String>(cols.size()));
// initialize it
for (int i = 0; i < cols.size(); i++) row.getColumns().add("");
resList.addRow(row);
rowCnt++;
for (int i = 0; i < sql.getKeyList().size(); ++i) {
String val = newkeys[i];
if (cols.get(i).isNumberType() && val != null && val.startsWith("."))
val = "0" + val; // prefix Oracle float number with 0 if starting with "."
else if (cols.get(i).isNumberType() && val != null && val.startsWith("-."))
val =
val.replace(
"-.", "-0."); // prefix Oracle float number with 0 if starting with "."
row.getColumns().set(i, val);
}
}
// now we need get the flat record
String flatVal = rs.getString(sql.getFlatKey());
// check its index
int idx = flatkeyIdx.get(flatVal.toUpperCase());
int mi = 0;
for (String s : sql.getMetrics().keySet()) {
String val = rs.getString(s);
int colIdx = sql.getKeyList().size() + idx * sql.getMetrics().size() + mi;
if (cols.get(colIdx).isNumberType() && val != null && val.startsWith("."))
val = "0" + val; // prefix Oracle float number with 0 if starting with "."
else if (cols.get(colIdx).isNumberType() && val != null && val.startsWith("-."))
val = val.replace("-.", "-0."); // prefix Oracle float number with 0 if starting with "."
row.getColumns().set(colIdx, val);
mi++;
}
prevkeys = newkeys;
if (maxCount > 0 && rowCnt >= maxCount) break;
}
return resList;
}