package zeta.util;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Map;
import zeta.ZetaServlet;
public class Query {
protected Table table;
protected ZetaServlet servlet;
public Query(Map names, String query, Connection con, ZetaServlet servlet) {
this.servlet = servlet;
table = executeQuery(names, query, false, null, con);
}
public Query(Map names, String query, boolean viewPlace, String tendencyQuery, Connection con, ZetaServlet servlet) {
this.servlet = servlet;
table = executeQuery(names, query, viewPlace, tendencyQuery, con);
}
public Table getResult() {
return table;
}
protected Table executeQuery(Map names, String query, boolean viewPlace, String tendencyQuery, Connection con) {
Statement stmt = null;
try {
stmt = con.createStatement();
if (tendencyQuery != null && (rankingQuery == null || !rankingQuery.equals(tendencyQuery))) {
rankingQuery = tendencyQuery;
ranking.clear();
ResultSet rs = stmt.executeQuery(tendencyQuery);
while (rs.next()) {
ranking.add(rs.getString(1));
}
rs.close();
}
ResultSet rs = stmt.executeQuery(query);
ResultSetMetaData rsmd = rs.getMetaData();
int columns = rsmd.getColumnCount();
while (columns > 0) {
String s = rsmd.getColumnName(columns);
if (s != null && s.length() > 0 && !Character.isDigit(s.charAt(0))) break;
--columns;
}
if (viewPlace) ++columns;
if (tendencyQuery != null) ++columns;
Table table = new Table(columns);
int idx = 1;
for (int i = 0; i < columns; ++i) {
int type = 0;
if (i == 0 && viewPlace) {
type = Types.CHAR;
table.setColumnName(0, "place");
} else if (i == 1 && tendencyQuery != null) {
type = Types.CHAR;
table.setColumnName(1, "trend");
} else {
table.setColumnName(i, rsmd.getColumnName(idx));
table.setPrecision(i, rsmd.getPrecision(idx));
table.setScale(i, rsmd.getScale(idx));
type = rsmd.getColumnType(idx);
++idx;
}
table.setType(i, type);
switch (type) {
case Types.DATE :
case Types.DECIMAL :
case Types.DOUBLE :
case Types.FLOAT :
case Types.INTEGER :
case Types.NUMERIC :
table.setAlignment(i, Table.RIGHT);
break;
case Types.TIME :
case Types.TIMESTAMP :
table.setAlignment(i, Table.CENTER);
break;
case Types.CHAR :
case Types.LONGVARCHAR:
case Types.VARCHAR :
table.setAlignment(i, (i == 1 && tendencyQuery != null)? Table.CENTER : Table.LEFT);
break;
}
}
int row = 0;
while (rs.next()) {
table.addRow();
idx = 1;
Object name = null;
for (int col = 0; col < table.getColumnCount(); ++col) {
Object value = null;
if (col == 0) name = null;
if (col == 0 && viewPlace) {
value = Integer.toString(row+1) + '.';
} else if (col == 0 && tendencyQuery == null || col == 1 && (viewPlace && tendencyQuery == null || !viewPlace && tendencyQuery != null) || col == 2 && viewPlace && tendencyQuery != null) {
if (name == null) name = rs.getString(1);
value = (names == null)? name : names.get(name);
} else if (col == 1 && tendencyQuery != null) {
value = "+";
if (name == null) name = rs.getString(1);
int pos = ranking.indexOf(name);
if (pos == table.getRowCount()-1) value = " ";
else if (pos >= 0 && pos < table.getRowCount()-1) value = "-";
} else {
++idx;
switch (table.getType(col)) {
case Types.DATE:
value = toString(rs.getDate(idx));
break;
case Types.DECIMAL:
case Types.NUMERIC:
if (table.getScale(col) > 0) {
value = new Double(rs.getDouble(idx));
} else {
value = new Long(rs.getLong(idx));
}
break;
case Types.DOUBLE:
case Types.FLOAT:
value = new Double(rs.getDouble(idx));
break;
case Types.BIGINT:
value = new Long(rs.getLong(idx));
break;
case Types.INTEGER:
value = new Integer(rs.getInt(idx));
break;
case Types.SMALLINT:
case Types.TINYINT:
value = new Short(rs.getShort(idx));
break;
case Types.TIME:
value = rs.getTime(idx);
break;
case Types.TIMESTAMP:
value = rs.getTimestamp(idx);
break;
case Types.CHAR:
case Types.LONGVARCHAR:
case Types.VARCHAR:
value = rs.getString(idx);
break;
default:
value = rs.getString(idx);
}
}
table.setValue(row, col, value);
}
row++;
}
rs.close();
return table;
} catch (SQLException e) {
try {
servlet.log(query);
servlet.log(e.getMessage(), e);
} catch (NullPointerException npe) {
ThrowableHandler.handle(e);
}
} finally {
DatabaseUtils.close(stmt);
}
return null;
}
protected String toString(java.sql.Date date) {
return (date != null)? dateFormater.format(date) : null;
}
static DateFormat dateFormater = new SimpleDateFormat("MM/dd/yyyy");
private static ArrayList ranking = new ArrayList(5000);
private static String rankingQuery = null;
}