Example 18-4 displays a program, LookupAPI, that uses the database built by the MakeAPIDB program of Example 18-3 and makes interesting SQL queries against it. LookupAPI behaves as follows:
When invoked with the name of a class member, it lists the full name (including package and class) of each field and/or method that has that name.
When run with the name of a class, it lists the full name of every class in any package that has that name.
When called with a portion of a package name, it lists the names of all the packages that contain that string.
When invoked with the -l option and a class name, it lists every member of every class that has that name.
When run with the -l option and a portion of a package name, it lists all the classes and interfaces in any package that matches that string.
LookupAPI reads the same APIDB.props property file MakeAPIDB does. Or, alternatively, it reads a property file specified on the command line following a -p flag. Using the database connection parameters in the property file, the program connects to a database and executes the necessary SQL queries to return the desired information. Note that it calls the setReadOnly( ) method of the Connection object. Doing this provides a hint that the program performs only queries and doesn't modify the database in any way. For some database systems, this may improve efficiency. Other than the setReadOnly( ) method, this example doesn't introduce any new JDBC features. It simply serves as a real-world application of a database and demonstrates some of the powerful database queries that can be expressed using SQL.
package je3.sql;
import java.sql.*;
import java.io.FileInputStream;
import java.util.Properties;
/**
* This program uses the database created by MakeAPIDB.
It opens a connection
* to a database using the same property file used by MakeAPIDB.Then it
* queries that database in several interesting ways to obtain useful
* information about Java APIs.
It can be used to look up the fully-qualified
* name of a member, class,
or package, or it can be used to list the members
* of a class or package.
**/
public class LookupAPI {
public static void main(String[ ] args) {
Connection c = null; // JDBC connection to the database
try {
// Some default values
String target = null; // The name to look up
boolean list = false; // List members or lookup name?
String propfile = "APIDB.props"; // The file of db parameters
// Parse the command-line arguments
for(int i = 0; i < args.length; i++) {
if (args[i].equals("-l")) list = true;
else if (args[i].equals("-p")) propfile = args[++i];
else if (target != null)
throw new IllegalArgumentException("Unexpected argument: "
+ args[i]);
else target = args[i];
}
if (target == null)
throw new IllegalArgumentException("No target specified");
// Now determine the values needed to set up the database
// connection. The program attempts to read a property file
// named "APIDB.props", or optionally specified with the
// -p argument. This property file may contain "driver",
// "database", "user", and "password" properties that
// specify the necessary values for connecting to the db.
// If the properties file does not exist, or does not
// contain the named properties, defaults will be used.
Properties p = new Properties( ); // Empty properties
try { p.load(new FileInputStream(propfile)); } // Try to load props
catch (Exception e) { }
// Read values from Properties file
String driver = p.getProperty("driver");
String database = p.getProperty("database");
String user = p.getProperty("user", ");
String password = p.getProperty("password", ");
// The driver and database properties are mandatory
if (driver == null)
throw new IllegalArgumentException("No driver specified!");
if (database == null)
throw new IllegalArgumentException("No database specified!");
// Load the database driver
Class.forName(driver);
// And set up a connection to the specified database
c = DriverManager.getConnection(database, user, password);
// Tell it we will not do any updates.
// This hint may improve efficiency.
c.setReadOnly(true);
// If the "-l" option was given, then list the members of
// the named package or class. Otherwise, lookup all
// matches for the specified member, class, or package.
if (list) list(c, target);
else lookup(c, target);
}
// If anything goes wrong, print the exception and a usage message. If
// a SQLException is thrown, display the state message it includes.
catch (Exception e) {
System.out.println(e);
if (e instanceof SQLException)
System.out.println(((SQLException) e).getSQLState( ));
System.out.println("Usage: java LookupAPI [-l] [-p <propfile>] " +
"target");
}
// Always close the DB connection when we're done with it.
finally {
try { c.close( ); } catch (Exception e) { }
}
}
/**
* This method looks up all matches for the specified target string in the
* database. First, it prints the full name of any members by that name.
* Then it prints the full name of any classes by that name. Then it
* prints the name of any packages that contain the specified name
**/
public static void lookup(Connection c, String target)
throws SQLException
{
// Create the Statement object we'll use to query the database
Statement s = c.createStatement( );
// Go find all class members with the specified name
s.executeQuery("SELECT DISTINCT " +
"package.name, class.name, member.name, member.isField"+
" FROM package, class, member" +
" WHERE member.name='" + target + "'" +
" AND member.classId=class.id " +
" AND class.packageId=package.id");
// Loop through the results, and print them out (if there are any).
ResultSet r = s.getResultSet( );
while(r.next( )) {
String pkg = r.getString(1); // package name
String cls = r.getString(2); // class name
String member = r.getString(3); // member name
boolean isField = r.getBoolean(4); // is the member a field?
// Display this match
System.out.println(pkg + "." + cls + "." + member +
(isField?":"( )"));
}
// Now look for a class with the specified name
s.executeQuery("SELECT package.name, class.name " +
"FROM package, class " +
"WHERE class.name='" + target + "' " +
" AND class.packageId=package.id");
// Loop through the results and print them out
r = s.getResultSet( );
while(r.next( )) System.out.println(r.getString(1) + "." +
r.getString(2));
// Finally, look for a package that matches a part of the name.
// Note the use of the SQL LIKE keyword and % wildcard characters
s.executeQuery("SELECT name FROM package " +
"WHERE name='" + target + "'" +
" OR name LIKE '%." + target + ".%' " +
" OR name LIKE '" + target + ".%' " +
" OR name LIKE '%." + target + "'");
// Loop through the results and print them out
r = s.getResultSet( );
while(r.next( )) System.out.println(r.getString(1));
// Finally, close the Statement object
s.close( );
}
/**
* This method looks for classes with the specified name, or packages
* that contain the specified name.
For each class it finds, it displays
* all methods and fields of the class. For each package it finds, it
* displays all classes in the package.
**/
public static void list(Connection conn, String target)
throws SQLException
{
// Create two Statement objects to query the database with
Statement s = conn.createStatement( );
Statement t = conn.createStatement( );
// Look for a class with the given name
s.executeQuery("SELECT package.name, class.name " +
"FROM package, class " +
"WHERE class.name='" + target + "' " +
" AND class.packageId=package.id");
// Loop through all matches
ResultSet r = s.getResultSet( );
while(r.next( )) {
String p = r.getString(1); // package name
String c = r.getString(2); // class name
// Print out the matching class name
System.out.println("class " + p + "." + c + " {");
// Now query all members of the class
t.executeQuery("SELECT DISTINCT member.name, member.isField " +
"FROM package, class, member " +
"WHERE package.name = '" + p + "' " +
" AND class.name = '" + c + "' " +
" AND member.classId=class.id " +
" AND class.packageId=package.id " +
"ORDER BY member.isField, member.name");
// Loop through the ordered list of all members, and print them out
ResultSet r2 = t.getResultSet( );
while(r2.next( )) {
String m = r2.getString(1);
int isField = r2.getInt(2);
System.out.println(" " + m + ((isField == 1)?":"( )"));
}
// End the class listing
System.out.println("}");
}
// Now go look for a package that matches the specified name
s.executeQuery("SELECT name FROM package " +
"WHERE name='" + target + "'" +
" OR name LIKE '%." + target + ".%' " +
" OR name LIKE '" + target + ".%' " +
" OR name LIKE '%." + target + "'");
// Loop through any matching packages
r = s.getResultSet( );
while(r.next( )) {
// Display the name of the package
String p = r.getString(1);
System.out.println("Package " + p + ": ");
// Get a list of all classes and interfaces in the package
t.executeQuery("SELECT class.name FROM package, class " +
"WHERE package.name='" + p + "' " +
" AND class.packageId=package.id " +
"ORDER BY class.name");
// Loop through the list and print them out.
ResultSet r2 = t.getResultSet( );
while(r2.next( )) System.out.println(" " + r2.getString(1));
}
// Finally, close both Statement objects
s.close( ); t.close( );
}
}