| 1 |
import java.sql.*; |
| 2 |
import java.util.*; |
| 3 |
|
| 4 |
// DBReporter class - establishes the database connection and is |
| 5 |
// used to run all of the reports. |
| 6 |
public class DBReporter { |
| 7 |
|
| 8 |
// Constructor. |
| 9 |
public DBReporter(long startTime, long endTime) { |
| 10 |
_startTime = startTime; |
| 11 |
_endTime = endTime; |
| 12 |
} |
| 13 |
|
| 14 |
// Runs all of the reports for each distinct machine name. |
| 15 |
public void runReports(ReportList reports, String webDir) throws SQLException, Exception { |
| 16 |
|
| 17 |
System.out.println("Preparing reports ..."); |
| 18 |
|
| 19 |
// Get the MySQL DB driver. |
| 20 |
try { |
| 21 |
Class.forName("org.gjt.mm.mysql.Driver").newInstance(); |
| 22 |
} |
| 23 |
catch (Exception e){ |
| 24 |
throw new Exception("Could not find the MySQL driver: " + e); |
| 25 |
} |
| 26 |
|
| 27 |
try { |
| 28 |
_conn = DriverManager.getConnection("jdbc:mysql://localhost/co600_10_db?user=co600_10&password=bung-i-scream"); |
| 29 |
_stmt = _conn.createStatement(); |
| 30 |
} |
| 31 |
catch (SQLException e){ |
| 32 |
throw new SQLException("Could not connect to the database: " + e); |
| 33 |
} |
| 34 |
|
| 35 |
// This is essential to prevent temporary tables running out of |
| 36 |
// physical memory for large reports. With this set to 1, all |
| 37 |
// temporary tables shall be written to disk. This is a daily |
| 38 |
// task, so we shall not be too concerned about the reduced |
| 39 |
// performance. |
| 40 |
try { |
| 41 |
_stmt.executeUpdate("SET SQL_BIG_TABLES = 1;"); |
| 42 |
} |
| 43 |
catch (SQLException e){ |
| 44 |
System.out.println("WARNING: Could not set MySQL to write temporary tables to disk."); |
| 45 |
System.out.println("Some of the following i-scream reports may fail if there is insufficient memory."); |
| 46 |
} |
| 47 |
|
| 48 |
// Get a list of distinct machine names. |
| 49 |
LinkedList machines = this.getDistinctMachines(); |
| 50 |
|
| 51 |
System.out.println("Readying reports for " + machines.size() + " machines."); |
| 52 |
|
| 53 |
// Produce all of the reports for each machine. |
| 54 |
ReportMaker rm = new ReportMaker(_stmt, webDir, DateUtils.shortDateString(_startTime), _startTime, _endTime); |
| 55 |
Iterator machineIt = machines.iterator(); |
| 56 |
while (machineIt.hasNext()) { |
| 57 |
String machine = (String)machineIt.next(); |
| 58 |
rm.produce(reports, machine); |
| 59 |
} |
| 60 |
|
| 61 |
} |
| 62 |
|
| 63 |
|
| 64 |
// Returns a list of distinct machine names. |
| 65 |
private LinkedList getDistinctMachines() throws SQLException { |
| 66 |
|
| 67 |
LinkedList machines = new LinkedList(); |
| 68 |
try { |
| 69 |
ResultSet rs = _stmt.executeQuery("SELECT DISTINCT machine_name from ipacket WHERE receipt_date >= " + _startTime + " AND receipt_date <= " + _endTime + ";"); |
| 70 |
while (rs.next()) { |
| 71 |
String machine_name = rs.getString("machine_name"); |
| 72 |
machines.add(machine_name); |
| 73 |
} |
| 74 |
rs.close(); |
| 75 |
} |
| 76 |
catch (SQLException e) { |
| 77 |
throw new SQLException("Unable to find distinct machine names: " + e); |
| 78 |
} |
| 79 |
|
| 80 |
return machines; |
| 81 |
} |
| 82 |
|
| 83 |
|
| 84 |
|
| 85 |
private long _startTime; |
| 86 |
private long _endTime; |
| 87 |
|
| 88 |
private Connection _conn = null; |
| 89 |
private Statement _stmt = null; |
| 90 |
|
| 91 |
} |
