// This example is from _Java Examples in a Nutshell_. (http://www.oreilly.com)
// Copyright (c) 1997 by David Flanagan
// This example is provided WITHOUT ANY WARRANTY either expressed or implied.
// You may study, use, modify, and distribute it for non-commercial purposes.
// For any commercial use, see http://www.davidflanagan.com/javaexamples

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 properties 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, using defaults if not found.
      // These defaults will probably not work for you!
      String driver = p.getProperty("driver", "postgres95.PGDriver");
      String database = p.getProperty("database","jdbc:postgres95:///APIDB");
      String user = p.getProperty("user", "");
      String password = p.getProperty("password", "");

      // 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 extra 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
      int isField = r.getInt(4);              // is the member a field?
      // Display this match
      System.out.println(pkg + "." + cls + "." + member + 
                         ((isField==1)?"":"()"));
    }
    
    // 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 of the name.
    // Note the use of the SQL LIKE keyword and % wildcard characters
    s.executeQuery("SELECT name FROM package " + 
                   "WHERE 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 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();
  }
}