Java Examples In A Nutshell (3rd Edition) [Electronic resources] نسخه متنی

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) {  }
}
}
}