18.4 Using the API Database

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.

Example 18-4. LookupAPI.java

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( );     } }