reports/DBReporter2/DBReporter.java

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

// DBReporter class - establishes the database connection and is
// used to run all of the reports.
public class DBReporter {
    
    // Constructor.
    public DBReporter(long startTime, long endTime) {
        _startTime = startTime;
        _endTime = endTime;
    }

    // Runs all of the reports for each distinct machine name.    
    public void runReports(ReportList reports, String webDir) throws SQLException, Exception {
        
        System.out.println("Preparing reports ...");

        // Get the MySQL DB driver.
        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.");
        }
        
        // Get a list of distinct machine names.
        LinkedList machines = this.getDistinctMachines();
        
        System.out.println("Readying reports for " + machines.size() + " machines.");
        
        // Produce all of the reports for each machine.
        ReportMaker rm = new ReportMaker(_stmt, webDir, DateUtils.shortDateString(_startTime), _startTime, _endTime);
        Iterator machineIt = machines.iterator();
        while (machineIt.hasNext()) {
            String machine = (String)machineIt.next();
            rm.produce(reports, machine);
        }
        
    }
    
    
    // Returns a list of distinct machine names.
    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);
            }
            rs.close();
        }
        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.3
Committed:	Sat Feb 3 20:47:11 2001 UTC (24 years, 10 months ago) by pjm2
Branch:	MAIN
Changes since 1.2:	+2 -2 lines
Log Message:	The web directory is now specified. This is soon to be tied in with a [PHP?] script to allow report browsing.
#	User	Rev	Content
1	pjm2	1.1	import java.sql.*;
2			import java.util.*;
3
4	pjm2	1.2	// DBReporter class - establishes the database connection and is
5			// used to run all of the reports.
6	pjm2	1.1	public class DBReporter {
7
8	pjm2	1.2	// Constructor.
9	pjm2	1.1	public DBReporter(long startTime, long endTime) {
10			_startTime = startTime;
11			_endTime = endTime;
12			}
13	pjm2	1.2
14			// Runs all of the reports for each distinct machine name.
15	pjm2	1.3	public void runReports(ReportList reports, String webDir) throws SQLException, Exception {
16	pjm2	1.1
17			System.out.println("Preparing reports ...");
18
19	pjm2	1.2	// Get the MySQL DB driver.
20	pjm2	1.1	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	pjm2	1.2
27	pjm2	1.1	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	pjm2	1.2	// Get a list of distinct machine names.
49	pjm2	1.1	LinkedList machines = this.getDistinctMachines();
50
51			System.out.println("Readying reports for " + machines.size() + " machines.");
52
53	pjm2	1.2	// Produce all of the reports for each machine.
54	pjm2	1.3	ReportMaker rm = new ReportMaker(_stmt, webDir, DateUtils.shortDateString(_startTime), _startTime, _endTime);
55	pjm2	1.1	Iterator machineIt = machines.iterator();
56			while (machineIt.hasNext()) {
57			String machine = (String)machineIt.next();
58			rm.produce(reports, machine);
59			}
60
61			}
62
63	pjm2	1.2
64			// Returns a list of distinct machine names.
65	pjm2	1.1	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	pjm2	1.2	rs.close();
75	pjm2	1.1	}
76			catch (SQLException e) {
77			throw new SQLException("Unable to find distinct machine names: " + e);
78			}
79
80			return machines;
81			}
82	pjm2	1.2
83
84	pjm2	1.1
85			private long _startTime;
86			private long _endTime;
87
88			private Connection _conn = null;
89			private Statement _stmt = null;
90
91			}