18.4 Testing Your Database Through a JDBC Connection

After installing and configuring your database, you will want to test your database for JDBC connectivity. In Listing 18.3, we provide a program to perform the following database tests.

  • Establish a JDBC connection to the database and report the product name and version.

  • Create a simple "authors" table containing the ID, first name, and last name of the two authors of Core Servlets and JavaServer Pages , Second Edition .

  • Query the "authors" table, summarizing the ID, first name, and last name for each author.

  • Perform a nonrigorous test to determine the JDBC version. Use the reported JDBC version with caution: the reported JDBC version does not mean that the driver is certified to support all classes and methods defined by that JDBC version.

Since TestDatabase is in the coreservlets package, it must reside in a subdirectory called coreservlets . Before compiling the file, set the CLASSPATH to include the directory containing the coreservlets directory. See Section 2.7 (Set Up Your Development Environment) for details. With this setup, simply compile the program by running javac TestDatabase.java from within the coreservlets subdirectory (or by selecting "build" or "compile" in your IDE). However, to run TestDatabase , you need to refer to the full package name as shown in the following command,

 
 Prompt>  java coreservlets.TestDatabase   host dbName   username password vendor  

where host is the hostname of the database server, dbName is the name of the database you want to test, username and password are those of the user configured to access the database, and vendor is a keyword identifying the vendor driver.

This program uses the class DriverUtilities from Chapter 17 (Listing 17.5) to load the vendor's driver information and to create a URL to the database. Currently, DriverUtilities supports Microsoft Access, MySQL, and Oracle databases. If you use a different database vendor, you will need to modify DriverUtilities and add the vendor information. See Section 17.3 (Simplifying Database Access with JDBC Utilities) for details.

The following shows the output when TestDatabase is run against a MySQL database named csajsp , using the MySQL Connector/J 3.0 driver.

 
 Prompt>  java coreservlets.TestDatabase localhost   csajsp brown larry MYSQL  Testing database connection ... Driver: com.mysql.jdbc.Driver URL: jdbc:mysql://localhost:3306/csajsp Username: brown Password: larry Product name: MySQL Product version: 4.0.12-max-nt Driver Name: MySQL-AB JDBC Driver Driver Version: 3.0.6-stable ( $Date: 2003/02/17 17:01:34 $, $Revision: 1.27.2.1 3 $ ) Creating authors table ... successful Querying authors table ... +-------------+--------------+--------------+  id           first_name    last_name     +-------------+--------------+--------------+  1            Marty         Hall           2            Larry         Brown         +-------------+--------------+--------------+ Checking JDBC version ... JDBC Version: 3.0 

Interestingly, the MySQL Connector/J 3.0 driver used with MySQL 4.0.12 reports a JDBC version of 3.0. However, MySQL is not fully ANSI SQL-92 compliant and the driver cannot be JDBC 3.0 certified. Therefore, you should always check the vendor's documentation closely for the JDBC version and always thoroughly test your product before releasing to production.

Core Warning

graphics/bwopenglobe_icon.gif

The JDBC version reported by DatabaseMetaData is unofficial . The driver is not necessarily certified at the level reported. Check the vendor documentation.


Listing 18.3 TestDatabase.java
 package coreservlets; import java.sql.*; /** Perform the following tests on a database:  *  <OL>  *  <LI>Create a JDBC connection to the database and report  *      the product name and version.  *  <LI>Create a simple "authors" table containing the  *      ID, first name, and last name for the two authors  *      of Core Servlets and JavaServer Pages, 2nd Edition.  *  <LI>Query the "authors" table for all rows.  *  <LI>Determine the JDBC version. Use with caution:  *      the reported JDBC version does not mean that the  *      driver has been certified.  *  </OL>  */ public class TestDatabase {   private String driver;   private String url;   private String username;   private String password;   public TestDatabase(String driver, String url,                       String username, String password) {     this.driver = driver;     this.url = url;     this.username = username;     this.password = password;   }   /** Test the JDBC connection to the database and report the    *  product name and product version.    */   public void testConnection() {     System.out.println();     System.out.println("Testing database connection ...\n");     Connection connection = getConnection();     if (connection == null) {       System.out.println("Test failed.");       return;     }     try {       DatabaseMetaData dbMetaData = connection.getMetaData();       String productName =         dbMetaData.getDatabaseProductName();       String productVersion =         dbMetaData.getDatabaseProductVersion();       String driverName = dbMetaData.getDriverName();       String driverVersion = dbMetaData.getDriverVersion();       System.out.println("Driver: " + driver);       System.out.println("URL: " + url);       System.out.println("Username: " + username);       System.out.println("Password: " + password);       System.out.println("Product name: " + productName);       System.out.println("Product version: " + productVersion);       System.out.println("Driver Name: " + driverName);       System.out.println("Driver Version: " + driverVersion);     } catch(SQLException sqle) {       System.err.println("Error connecting: " + sqle);     } finally {       closeConnection(connection);     }     System.out.println();   }   /** Create a simple table (authors) containing the ID,    *  first_name, and last_name for the two authors of    *  Core Servlets and JavaServer Pages, 2nd Edition.    */   public void createTable() {     System.out.print("Creating authors table ... ");     Connection connection = getConnection();     if (connection == null) {       System.out.println("failure");       return;     }     try {       String format =         "(id INTEGER, first_name VARCHAR(12), " +         " last_name VARCHAR(12))";       String[] rows = { "(1, 'Marty', 'Hall')",                         "(2, 'Larry', 'Brown')" };       Statement statement = connection.createStatement();       // Drop previous table if it exists, but don't get       // error if not. Thus, the separate try/catch here.       try {         statement.execute("DROP TABLE authors");       } catch(SQLException sqle) {}       String createCommand =         "CREATE TABLE authors " + format;       statement.execute(createCommand);       String insertPrefix =         "INSERT INTO authors VALUES";       for(int i=0; i<rows.length; i++) {         statement.execute(insertPrefix + rows[i]);       }       System.out.println("successful");     } catch(SQLException sqle) {       System.out.println("failure");       System.err.println("Error creating table: " + sqle);     } finally {       closeConnection(connection);     }     System.out.println();   }   /** Query all rows in the "authors" table. */   public void executeQuery() {     System.out.println("Querying authors table ... ");     Connection connection = getConnection();     if (connection == null) {       System.out.println("Query failed.");       return;     }     try {       Statement statement = connection.createStatement();       String query = "SELECT * FROM authors";       ResultSet resultSet = statement.executeQuery(query);       ResultSetMetaData resultSetMetaData =         resultSet.getMetaData();       int columnCount = resultSetMetaData.getColumnCount();       // Print out columns       String[] columns = new String[columnCount];       int[] widths = new int[columnCount];       for(int i=1; i <= columnCount; i++) {         columns[i-1] = resultSetMetaData.getColumnName(i);         widths[i-1] = resultSetMetaData.getColumnDisplaySize(i);       }       System.out.println(makeSeparator(widths));       System.out.println(makeRow(columns, widths));       // Print out rows       System.out.println(makeSeparator(widths));       String[] rowData = new String[columnCount];       while(resultSet.next()) {         for(int i=1; i <= columnCount; i++) {           rowData[i-1] = resultSet.getString(i);         }         System.out.println(makeRow(rowData, widths));       }       System.out.println(makeSeparator(widths));     } catch(SQLException sqle) {       System.err.println("Error executing query: " + sqle);     } finally {       closeConnection(connection);     }     System.out.println();   }   /** Perform a nonrigorous test for the JDBC version.    *  Initially, a last() operation is attempted for    *  JDBC 2.0. Then, calls to getJDBCMajorVersion and    *  getJDBCMinorVersion are attempted for JDBC 3.0.    */   public void checkJDBCVersion() {     System.out.println();     System.out.println("Checking JDBC version ...\n");     Connection connection = getConnection();     if (connection == null) {       System.out.println("Check failed.");       return;     }     int majorVersion = 1;     int minorVersion = 0;     try {       Statement statement = connection.createStatement(                               ResultSet.TYPE_SCROLL_INSENSITIVE,                               ResultSet.CONCUR_READ_ONLY);       String query = "SELECT * FROM authors";       ResultSet resultSet = statement.executeQuery(query);  resultSet.last(); // JDBC 2.0  majorVersion = 2;     } catch(SQLException sqle) {       // Ignore - last() not supported     }     try {       DatabaseMetaData dbMetaData = connection.getMetaData();  majorVersion = dbMetaData.getJDBCMajorVersion(); // JDBC 3.0   minorVersion = dbMetaData.getJDBCMinorVersion(); // JDBC 3.0  } catch(Throwable throwable) {       // Ignore - methods not supported     } finally {       closeConnection(connection);     }     System.out.println("JDBC Version: " +                        majorVersion + "." + minorVersion);   }   // A String of the form "  xxx   xxx   xxx "   private String makeRow(String[] entries, int[] widths) {     String row = "";     for(int i=0; i<entries.length; i++) {       row = row + padString(entries[i], widths[i], " ");       row = row + " ";     }     return(row);   }   // A String of the form "+------+------+------+"   private String makeSeparator(int[] widths) {     String separator = "+";     for(int i=0; i<widths.length; i++) {       separator += padString("", widths[i] + 1, "-") + "+";     }     return(separator);   }   private String padString(String orig, int size,                            String padChar) {     if (orig == null) {       orig = "<null>";     }     // Use StringBuffer, not just repeated String concatenation     // to avoid creating too many temporary Strings.     StringBuffer buffer = new StringBuffer(padChar);     int extraChars = size - orig.length();     buffer.append(orig);     for(int i=0; i<extraChars; i++) {       buffer.append(padChar);     }     return(buffer.toString());   }   /** Obtain a new connection to the database or return    *  null on failure.    */   public Connection getConnection() {     try {       Class.forName(driver);       Connection connection =         DriverManager.getConnection(url, username,                                     password);       return(connection);     } catch(ClassNotFoundException cnfe) {       System.err.println("Error loading driver: " + cnfe);       return(null);     } catch(SQLException sqle) {       System.err.println("Error connecting: " + sqle);       return(null);     }   }   /** Close the database connection. */   private void closeConnection(Connection connection) {     try {       connection.close();     } catch(SQLException sqle) {       System.err.println("Error closing connection: " + sqle);       connection = null;     }   }   public static void main(String[] args) {     if (args.length < 5) {       printUsage();       return;     }     String vendor = args[4];     // Change to DriverUtilities2.loadDrivers() to     // load the drivers from an XML file.     DriverUtilities.loadDrivers();     if (!DriverUtilities.isValidVendor(vendor)) {       printUsage();       return;     }     String driver = DriverUtilities.getDriver(vendor);     String host = args[0];     String dbName = args[1];     String url =       DriverUtilities.makeURL(host, dbName, vendor);     String username = args[2];     String password = args[3];     TestDatabase database =       new TestDatabase(driver, url, username, password);     database.testConnection();     database.createTable();     database.executeQuery();     database.checkJDBCVersion();   }   private static void printUsage() {     System.out.println("Usage: TestDatabase host dbName " +                        "username password vendor.");   } } 


Core Servlets and JavaServer Pages (Vol. 1.Core Technologies)
Core Servlets and Javaserver Pages: Core Technologies, Vol. 1 (2nd Edition)
ISBN: 0130092290
EAN: 2147483647
Year: 2002
Pages: 194

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net