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.
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
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."); } }  |
EAN: 2147483647
Pages: 194