/**
* Expand a single row to multiple rows. One row per column.
*
* @param sql
* @param rs
* @return
* @throws SQLException
*/
public static ResultList expandSqlResultSet(Sql sql, ResultSet rs) throws SQLException {
ResultList resList = new ResultList();
if (rs == null) return resList;
ColumnDescriptor desc = new ColumnDescriptor();
desc.addColumn("NAME", false, 1);
desc.addColumn("VALUE", false, 2);
resList.setColumnDescriptor(desc);
java.sql.ResultSetMetaData meta = rs.getMetaData();
int colCnt = meta.getColumnCount();
int rowCnt = 0;
while (rs
.next()) // change to allow to expand multiple rows, with a row number separator and a row
// count suffix
{
if (rowCnt > 0) {
ResultRow row = new ResultRow();
row.addColumn("--- ROW " + (rowCnt + 1) + "---");
row.addColumn("");
resList.addRow(row);
}
for (int i = 1; i <= colCnt; i++) {
ResultRow row = new ResultRow();
if (rowCnt > 0) row.addColumn(meta.getColumnName(i) + "(" + (rowCnt + 1) + ")");
else row.addColumn(meta.getColumnName(i));
row.addColumn(rs.getString(i));
resList.addRow(row);
}
rowCnt++;
}
return resList;
}
public static ResultList fakeResultListData() {
ResultList resList = new ResultList();
ColumnInfo col1 = new ColumnInfo("TEST1");
col1.setPosition(1);
ColumnInfo col2 = new ColumnInfo("TEST2");
col2.setPosition(2);
java.util.ArrayList<ColumnInfo> colInfoList = new java.util.ArrayList<ColumnInfo>(2);
colInfoList.add(col1);
colInfoList.add(col2);
ColumnDescriptor desc = new ColumnDescriptor();
desc.setColumns(colInfoList);
resList.setColumnDescriptor(desc);
ResultRow row1 = new ResultRow();
row1.setColumnDescriptor(desc);
java.util.ArrayList<String> cols = new java.util.ArrayList<String>();
cols.add("GOOD");
cols.add("BAD");
row1.setColumns(cols);
ResultRow row2 = new ResultRow();
cols = new java.util.ArrayList<String>();
cols.add("LONG");
cols.add("SHORT");
row2.setColumns(cols);
resList.getRows().add(row1);
resList.getRows().add(row2);
return resList;
}
/**
* 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;
}
public static ResultList paserBindList(ResultList rList) {
if (rList != null && rList.getRows().size() > 0) {
logger.fine("BindController returns " + rList.getRows().size());
ResultList newList = new ResultList();
ColumnDescriptor desc = new ColumnDescriptor();
for (ColumnInfo col : rList.getColumnDescriptor().getColumns()) {
if ("binds_xml".equalsIgnoreCase(col.getName())) continue;
desc.addColumn(col.getName(), col.isNumberType(), desc.getColumns().size());
}
desc.addColumn("NAME", false, desc.getColumns().size());
desc.addColumn("POS", false, desc.getColumns().size());
desc.addColumn("DTYSTR", false, desc.getColumns().size());
desc.addColumn("MAXLEN", false, desc.getColumns().size());
desc.addColumn("LEN", false, desc.getColumns().size());
desc.addColumn("VALUE", false, desc.getColumns().size());
newList.setColumnDescriptor(desc);
int idx = rList.getColumnDescriptor().getColumnIndex("BINDS_XML");
XMLInputFactory inputFactory = XMLInputFactory.newInstance();
for (ResultRow row : rList.getRows()) {
// logger.info("binds_xml.idx="+idx+", "+ row.getColumns().size());
List<BindVariable> binds = null;
try {
String bindXml = row.getColumns().get(idx);
binds = parseBinds(bindXml, inputFactory);
} catch (Exception iex) {
}
if (binds == null || binds.size() == 0) {
ResultRow newRow = new ResultRow();
newRow.setColumnDescriptor(desc);
List<String> cols = new java.util.ArrayList<String>(row.getColumns().size() + 5);
for (int i = 0; i < row.getColumns().size(); i++) {
if (i == idx) continue;
cols.add(row.getColumns().get(i));
}
cols.add(null);
cols.add(null);
cols.add(null);
cols.add(null);
cols.add(null);
cols.add(null);
newRow.setColumns(cols);
newList.addRow(newRow);
} else {
for (BindVariable var : binds) {
ResultRow newRow = new ResultRow();
newRow.setColumnDescriptor(desc);
List<String> cols = new java.util.ArrayList<String>(row.getColumns().size() + 5);
for (int i = 0; i < row.getColumns().size(); i++) {
if (i == idx) continue;
cols.add(row.getColumns().get(i));
}
cols.add(var.name);
cols.add(var.pos);
cols.add(var.dtystr);
cols.add(var.maxLen);
cols.add(var.len);
cols.add(var.value);
newRow.setColumns(cols);
newList.addRow(newRow);
}
}
}
return newList;
}
return null;
}