reports/DBReporter2/DBReporter.java

import java.sql.*;
import java.util.*;

public class DBReporter {
    
    public DBReporter(long startTime, long endTime) {
        _startTime = startTime;
        _endTime = endTime;
    }
    
    public void runReports(ReportList reports) throws SQLException, Exception {
        
        System.out.println("Preparing reports ...");

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