18.2 Using Database Metadata

Sometimes, in addition to querying and updating the data in a database, you also want to retrieve information about the database itself and its contents. This information is called metadata. The DatabaseMetaData interface allows you to retrieve this kind of information. You can obtain an object that implements this interface by calling the getMetaData( ) method of the Connection object, as shown in Example 18-2.

After GetDBInfo opens a database Connection and obtains a DatabaseMetaData object, it displays some general information about the database server and JDBC driver. Then, if the user just specified a database name on the command line, the program lists all the tables in that database. If the user specified a database name and a table name, however, the program lists the name and data type of each column in that table.

An interesting feature of this GetDBInfo program is how it obtains the parameters needed to connect to the database. The example operates on the premise that at any given site, it is typically used to connect to the same database server, using the same database driver, and may also be used with the same database username and password. So, instead of requiring the user to type all this cumbersome information on the command line each time the program is run, the program reads default values from a file named DB.props that is stored in the same directory as the GetDBInfo.class file. In order to run Example 18-2, you have to create an appropriate DB.props file for your system. On my system, this file contains:

# The name of the JDBC driver class driver=com.mysql.jdbc.Driver # The URL that specifies the database server. # It should not include the name of the database to connect to server=jdbc:mysql:/// # The database account name user=david # The password for the specified account, if any. # Uncomment the line below if you need to specify a password #password=

Lines that begin with # are comments, obviously. The name=value format is the standard file format for the java.util.Properties object that is used to read the contents of this file.

After the program reads the default values from the DB.props file, it parses its command-line arguments, which can override the driver, server, user, and password properties specified in the file. The name of the database to connect to must be specified on the command line; the database name is simply appended to the server URL. The name of a table in the database can optionally be specified on the command line. For example, you might run the program as follows:

% java je3.sql.GetDBInfo api class DBMS: MySQL 4.0.14-standard JDBC Driver: MySQL-AB JDBC Driver 3.0.8-stable ($Date: 2004/01/29 23:10:56 $) Database: jdbc:mysql:///apidb User: david@localhost Columns of class:         id : int         packageId : int         name : varchar

Example 18-2. GetDBInfo.java

package je3.sql; import java.sql.*; import java.util.Properties; /**  * This class uses the DatabaseMetaData class to obtain information about  * the database, the JDBC driver, and the tables in the database, or about  * the columns of a named table.  **/ public class GetDBInfo {     public static void main(String[  ] args) {         Connection c = null;  // The JDBC connection to the database server         try {             // Look for the properties file DB.props in the same directory as             // this program.  It will contain default values for the various             // parameters needed to connect to a database             Properties p = new Properties( );             try { p.load(GetDBInfo.class.getResourceAsStream("DB.props")); }             catch (Exception e) {  }                          // Get default values from the properties file             String driver = p.getProperty("driver");     // Driver class name             String server = p.getProperty("server", ""); // JDBC URL for server             String user = p.getProperty("user", "");     // db user name             String password = p.getProperty("password", ""); // db password                          // These variables don't have defaults             String database = null; // The db name (appended to server URL)             String table = null;    // The optional name of a table in the db                          // Parse the command-line args to override the default values above             for(int i = 0; i < args.length; i++) {                 if (args[i].equals("-d")) driver = args[++i];     //-d <driver>                 else if (args[i].equals("-s")) server = args[++i];//-s <server>                 else if (args[i].equals("-u")) user = args[++i];  //-u <user>                 else if (args[i].equals("-p")) password = args[++i];                  else if (database == null) database = args[i];    // <dbname>                 else if (table == null) table = args[i];          // <table>                 else throw new IllegalArgumentException("Unknown argument: "                                                         +args[i]);             }             // Make sure that at least a server or a database were specified.              // If not, we have no idea what to connect to, and cannot continue.             if ((server.length( ) == 0) && (database.length( ) == 0))                 throw new IllegalArgumentException("No database specified.");             // Load the db driver, if any was specified.             if (driver != null) Class.forName(driver);                          // Now attempt to open a connection to the specified database on             // the specified server, using the specified name and password             c = DriverManager.getConnection(server+database, user, password);             // Get the DatabaseMetaData object for the connection.  This is the             // object that will return us all the data we're interested in here             DatabaseMetaData md = c.getMetaData( );             // Display information about the server, the driver, etc.             System.out.println("DBMS: " + md.getDatabaseProductName( ) +                                 " " + md.getDatabaseProductVersion( ));             System.out.println("JDBC Driver: " + md.getDriverName( ) +                                 " " + md.getDriverVersion( ));             System.out.println("Database: " + md.getURL( ));             System.out.println("User: " + md.getUserName( ));             // Now, if the user did not specify a table, then display a list of             // all tables defined in the named database.  Note that tables are             // returned in a ResultSet, just like query results are.             if (table == null) {                 System.out.println("Tables:");                 ResultSet r = md.getTables("", "", "%", null);                 while(r.next( )) System.out.println("\t" + r.getString(3));             }             // Otherwise, list all columns of the specified table.             // Again, information about the columns is returned in a ResultSet             else {                 System.out.println("Columns of " + table + ": ");                 ResultSet r = md.getColumns("", "", table, "%");                 while(r.next( ))                      System.out.println("\t" + r.getString(4) + " : " +                                        r.getString(6));             }         }         // Print an error message if anything goes wrong.         catch (Exception e) {             System.err.println(e);             if (e instanceof SQLException)                 System.err.println(((SQLException)e).getSQLState( ));             System.err.println("Usage: java GetDBInfo [-d <driver>] " +                                "[-s <dbserver>]\n" +                                "\t[-u <username>] [-p <password>] <dbname>");         }         // Always remember to close the Connection object when we're done!         finally {              try { c.close( ); } catch (Exception e) {  }         }     } }