We begin with a sample application which queries a relational database via JDBC and
then reports the query results to the user via the standard output stream. The database
contains customer sales and the results are sorted by sales per geographic region (e.g.,
Europe). The program determines the total sales for a particular region and prints its
results to standard output. The initial Java program is shown in Figure 1.
1: /* DBAccessProgram.java
2: * Copyright © 1997 Horizon Systems Laboratory,
3: * Mitsubishi Electric Information Technology Center America.
4: * All rights reserved.
5: *
6: * CONFIDENTIAL AND PROPRIETARY PROPERTY OF MITSUBISHI ELECTRIC ITA.
7: *
8: */
9:
10: import java.net.URL;
11: import java.sql.*;
12: import java.util.*;
13: import java.io.*;
14:
15: import COM.meitca.concordia.Agent;
16: import COM.meitca.concordia.security.*;
17:
18:
19: /*
20: * A program which accesses an ODBC database (via JDBC).
21: * This example accesses the "demo" database. This example requires
22: * JDBC, ODBC and the JDBC-ODBC bridge.
23: *
24: */
25: public class DBAccessProgram {
26: /*
27: * This object contains the results of the SQL query made by the
28: * agent.
29: */
30: QueryResult itsResult;
31:
32: /*
33: * This string specifies the sales region the agent is interested
34: * in.
35: */
36: String itsRegion;
37:
38: /*
39: * Constructs a DBAccessProgram
40: */
41: public DBAccessProgram(String region) {
42: itsRegion = region;
43: itsResult = new QueryResult();
44: }
45:
46: /*
47: * Executes a SQL query on a database. This method performs a
48: * simple SELECT query on an odbc database. The results of the
49: * query are stuffed into a QueryResult object.
50: */
51: public void queryDatabase() {
52:
53: try {
54:
55: // Load the JDBC-ODBC Bridge Driver
56: Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
57:
58: // Create a URL specifying an ODBC data source name.
59: // This URL indicates an ODBC data source named demo.
60: String url = "jdbc:odbc:demo";
61:
62: // Connect to the database at that URL.
63: Connection con = DriverManager.getConnection(url);
64:
65: // Execute a SELECT statement
66: Statement stmt = con.createStatement();
67: ResultSet rs = stmt.executeQuery("SELECT DISTINCTROW Regions.Region, Sum([UnitPrice]*[Quantity]) AS Total " +
68: "FROM (Customers INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Customers.CustomerID = Orders.CustomerID) INNER JOIN Regions ON Customers.Country = Regions.Country " +
69: "GROUP BY Regions.Region " +
70: "HAVING (((Regions.Region)='" + itsRegion + "')) " +
71: "ORDER BY Regions.Region;");
72:
73: // Retrieve the result.
74: rs.next();
75:
76: //pull the results out of the Statement
77: itsResult.region = rs.getString(1);
78: itsResult.sales = rs.getFloat(2);
79:
80: stmt.close();
81: con.close();
82: } catch (java.lang.Exception ex) {
83: ex.printStackTrace();
84: }
85: }
86:
87: /*
88: * Report query results to console using System.out.println
89: * statements. This method could also report the results
90: * using an AWT GUI, or had the results off to another
91: * Java class for manipulation or reporting.
92: */
93: public void reportResults() {
94: System.out.println("Sales for region " + itsResult.region + ":");
95: System.out.println("Sales = " + itsResult.sales);
96: System.out.print("\n");
97:
98: }
99:
100:
101: public static void main(String args[])
102: throws Exception {
103:
104: System.setSecurityManager(new InsecurityManager());
105: DBAccessProgram app = new DBAccessProgram("Europe");
106: app.queryDatabase();
107: app.reportResults();
108: }
109:
110: }
111:
112:
113: /* QueryResult.java
114: * Copyright © 1997 Horizon Systems Laboratory,
115: * Mitsubishi Electric Information Technology Center America.
116: * All rights reserved.
117: *
118: * CONFIDENTIAL AND PROPRIETARY PROPERTY OF MITSUBISHI ELECTRIC ITA.
119: *
120: * DESCRIPTION
121: * A minimal data structure for holding query results.
122: *
123: */
124:
125: import java.io.*;
126: /*
127: * A minimal data structure for holding query results.
128: */
129: class QueryResult implements Serializable {
130: String region;
131: float sales;
132: }
Figure 1 - The Database Access Program
When this program executes, it queries the ODBC database identified by the name demo via the JDBC-ODBC bridge driver (lines 56-71). It queries the Customers table and computes the total sales for the specified region (in this example, Europe -- line 105). The query results are stored in a QueryResult object (lines 113-132) and displayed to the user by the DBAccessProgram's reportResults method.
Now, we will turn this program into a simple agent.