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.
#	User	Rev	Content
1	pjm2	1.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			}