17.3 Simplifying Database Access with JDBC Utilities | Core Servlets and Javaserver Pages: Core Technologies, Vol. 1 (2nd Edition)

In this section, we present a couple of helper classes that are used throughout this chapter to simplify coding. These classes provide basic functionality for loading drivers and making database connections.

For example, the DriverUtilities class (Listing 17.5) simplifies the building of a URL to connect to a database. To build a URL for MySQL, which is in the form

 String url = "jdbc:mysql://  host  :3306/  dbname  ";

you first need to load the vendor data by calling loadDrivers . Then, call makeURL to build the URL, as in

  DriverUtilities.loadDrivers();  String url =  DriverUtilities.makeURL  (host, dbname, DriverUtilities.MYSQL);

where the host, database name , and vendor are dynamically specified as arguments. In this manner, the database URL does not need to be hard-coded in the examples throughout this chapter. More importantly, you can simply add information about your database to the loadDrivers method in DriverUtilities (and a constant to refer to your driver, if desired). Afterwards, the examples throughout this chapter should work for your environment.

As another example, the ConnectionInfoBean class (Listing 17.9) provides a utility method, getConnection , for obtaining a Connection to a database. Thus, to obtain a connection to the database, replace

  Connection connection = null;  try {   Class.forName(driver);  connection = DriverManager.getConnection(url, username,   password);  } catch(ClassNotFoundException cnfe) {   System.err.println("Error loading driver: " + cnfe); } catch(SQLException sqle) {   System.err.println("Error connecting: " + sqle); }

with

 Connection connection =  ConnectionInfoBean.getConnection  (driver, url,                                   username, password);

If an SQLException occurs while the connection is being acquired , null is returned.

We define four utility classes in this section.

DriverUtilities

This class, shown in Listing 17.5, loads explicitly coded driver information about various database vendors . It then provides methods for obtaining the driver class for a vendor ( getDriver ) and creating a URL ( makeURL ), given the host, database name, and vendor. We provide driver information for Microsoft Access, MySQL, and Oracle databases, but you can easily update the class for your environment.
DriverUtilities2

This class, shown in Listing 17.6, extends DriverUtilities (Listing 17.5) and overrides loadDrivers to obtain the driver information from an XML file. A representative XML file is shown in drivers.xml , Listing 17.7.
DriverInfoBean

The DriverInfoBean class, shown in Listing 17.8, encapsulates driver information for a specific vendor (used by DriverUtilities , Listing 17.5). The bean contains a keyword (vendor name), a brief description of the driver, the driver classname, and a URL for connecting to a database.
ConnectionInfoBean

This class, shown in Listing 17.9, encapsulates information for connection to a particular database. The bean encapsulates a name for the connection, a brief description of the connection, the driver class, the URL to connect to the database, the username, and the password. In addition, the bean provides a getConnection method to directly obtain a Connection to a database.

Listing 17.5 DriverUtilities.java

 package coreservlets; import java.io.*; import java.sql.*; import java.util.*; import coreservlets.beans.*; /** Some simple utilities for building JDBC connections to  *  databases from different vendors. The drivers loaded are  *  hard-coded and specific to our local setup. You can  *  either modify the loadDrivers method and recompile or  *  use <CODE>DriverUtilities2</CODE> to load the driver  *  information for each vendor from an XML file.  */ public class DriverUtilities {  public static final String MSACCESS = "MSACCESS";   public static final String MYSQL = "MYSQL";   public static final String ORACLE = "ORACLE";   // Add constant to refer to your database here ...  protected static Map driverMap = new HashMap();   /** Load vendor driver information. Here we have hard-coded    *  driver information specific to our local setup.    *  Modify the values according to your setup.    *  Alternatively, you can use <CODE>DriverUtilities2</CODE>    *  to load driver information from an XML file.    *  <P>    *  Each vendor is represented by a    *  <CODE>DriverInfoBean</CODE> that defines a vendor    *  name (keyword), description, driver class, and URL. The    *  bean is stored in a driver map; the vendor name is    *  used as the keyword to retrieve the information.    *  <P>    *  The url variable should contain the placeholders    *  [$host] and [$dbName] to substitute for the <I>host</I>    *  and <I>database name</I> in <CODE>makeURL</CODE>.    */   public static void loadDrivers() {     String vendor, description, driverClass, url;     DriverInfoBean info = null;  // MSAccess   vendor = MSACCESS;   description = "MS Access 4.0";   driverClass = "sun.jdbc.odbc.JdbcOdbcDriver";   url = "jdbc:odbc:[$dbName]";   info = new DriverInfoBean(vendor, description,   driverClass, url);   addDriverInfoBean(info);   // MySQL   vendor = MYSQL;   description = "MySQL Connector/J 3.0";   driverClass = "com.mysql.jdbc.Driver";   url = "jdbc:mysql://[$host]:3306/[$dbName]";   info = new DriverInfoBean(vendor, description,   driverClass, url);   addDriverInfoBean(info);   // Oracle   vendor = ORACLE;   description = "Oracle9i Database";   driverClass = "oracle.jdbc.driver.OracleDriver";   url = "jdbc:oracle:thin:@[$host]:1521:[$dbName]";   info = new DriverInfoBean(vendor, description,   driverClass, url);   addDriverInfoBean(info);   // Add info on your database here...  }   /** Add information (<CODE>DriverInfoBean</CODE>) about new    *  vendor to the map of available drivers.    */   public static void addDriverInfoBean(DriverInfoBean info) {     driverMap.put(info.getVendor().toUpperCase(), info);   }   /** Determine if vendor is represented in the loaded    *  driver information.    */   public static boolean isValidVendor(String vendor) {     DriverInfoBean info =       (DriverInfoBean)driverMap.get(vendor.toUpperCase());     return(info != null);   }   /** Build a URL in the format needed by the    *  database drivers. In building of the final URL, the    *  keywords [$host] and [$dbName] in the URL    *  (looked up from the vendor's <CODE>DriverInfoBean</CODE>)    *  are appropriately substituted by the host and dbName    *  method arguments.    */   public static String makeURL(String host, String dbName,                                String vendor) {     DriverInfoBean info =       (DriverInfoBean)driverMap.get(vendor.toUpperCase());     if (info == null) {       return(null);     }     StringBuffer url = new StringBuffer(info.getURL());     DriverUtilities.replace(url, "[$host]", host);     DriverUtilities.replace(url, "[$dbName]", dbName);     return(url.toString());   }   /** Get the fully qualified name of a driver. */   public static String getDriver(String vendor) {     DriverInfoBean info =       (DriverInfoBean)driverMap.get(vendor.toUpperCase());     if (info == null) {       return(null);     } else {       return(info.getDriverClass());     }   }   /** Perform a string substitution, where the first "match"    *  is replaced by the new "value".    */   private static void replace(StringBuffer buffer,                               String match, String value) {     int index = buffer.toString().indexOf(match);     if (index > 0) {       buffer.replace(index, index + match.length(), value);     }   } }

In DriverUtilities , driver information for each vendor (Microsoft Access, MySQL, and Oracle9i) is explicitly coded into the program. If you are using a different database, you will need to modify DriverUtilities to include your driver information and then recompile the code. Since this approach may not be convenient , we include a second program, DriverUtilities2 in Listing 17.6, that reads the driver information from an XML file. Then, to add a new database vendor to your program, you simply edit the XML file. An example XML file, drivers.xml , is given in Listing 17.7

When using DriverUtilites2 in a command-line application, place the driver file, drivers.xml , in the working directory from which you started the application. Afterwards, call loadDrivers with the complete filename (including path ).

For a Web application, we recommend placing drivers.xml in the WEB-INF directory. You may want to specify the filename as a context initialization parameter in web.xml (for details, see the chapter on web.xml in Volume 2 of this book). Also, remember that from the servlet context you can use getRealPath to determine the physical path to a file relative to the Web application directory, as shown in the following code fragment.

 ServletContext context = getServletContext(); String path = context.getRealPath("/WEB-INF/drivers.xml");

JDK 1.4 includes all the necessary classes to parse the XML document, drivers.xml . If you are using JDK 1.3 or earlier, you will need to download and install a SAX and DOM parser. Xerces-J by Apache is an excellent parser and is available at http://xml.apache.org/xerces2-j/. Most Web application servers are already bundled with an XML parser, so you may not need to download Xerces-J. Check the vendor's documentation to determine where the parser files are located and include them in your CLASSPATH for compiling your application. For example, Tomcat 4.x includes the parser JAR files ( xercesImpl.jar and xmlParserAPI.jar ) in the install_dir /common/endorsed directory.

Note that if you are using servlets 2.4 (JSP 2.0) in a fully J2EE-1.4-compatible server, you are guaranteed to have JDK 1.4 or later.

Listing 17.6 DriverUtilities2.java

 package coreservlets; import java.io.*; import java.util.*; import javax.xml.parsers.*; import org.w3c.dom.*; import org.xml.sax.*; import coreservlets.beans.*; /** Extends <CODE>DriverUtilities</CODE> to support the  *  loading of driver information for different database vendors  *  from an XML file (default is drivers.xml). Both DOM and  *  JAXP are used to read the XML file.  The format for the  *  XML file is:  *  <P>  *  <PRE>  *    &lt;drivers&gt;  *      &lt;driver&gt;  *        &lt;vendor&gt;ORACLE&lt;/vendor&gt;  *        &lt;description&gt;Oracle&lt;/description&gt;  *        &lt;driver-class&gt;  *          oracle.jdbc.driver.OracleDriver  *        &lt;/driver-class&gt;  *        &lt;url&gt;  *          jdbc:oracle:thin:@[$host]:1521:[$dbName]  *        &lt;/url&gt;  *      &lt;/driver&gt;  *      ...  *    &lt;drivers&gt;  *  </PRE>  *  <P>  *  The url element should contain the placeholders  *  [$host] and [$dbName] to substitute for the host and  *  database name in makeURL.  */ public class DriverUtilities2 extends DriverUtilities {   public static final String DEFAULT_FILE = "drivers.xml";   /** Load driver information from default XML file,    *  drivers.xml.    */   public static void loadDrivers() {     DriverUtilities2.loadDrivers(DEFAULT_FILE);   }   /** Load driver information from specified XML file. Each    *  vendor is represented by a <CODE>DriverInfoBean</CODE>    *  object and stored in the map, with the vendor name as    *  the key. Use this method if you need to load a    *  driver file other than the default, drivers.xml.    */   public static void loadDrivers(String filename) {     File file = new File(filename);     try {       InputStream in = new FileInputStream(file);       DocumentBuilderFactory builderFactory =         DocumentBuilderFactory.newInstance();       DocumentBuilder builder =         builderFactory.newDocumentBuilder();       Document document = builder.parse(in);       document.getDocumentElement().normalize();       Element rootElement = document.getDocumentElement();       NodeList driverElements =         rootElement.getElementsByTagName("driver");       // Build DriverInfoBean for each vendor       for(int i=0; i<driverElements.getLength(); i++) {         Node node = driverElements.item(i);         DriverInfoBean info =           DriverUtilities2.createDriverInfoBean(node);         if (info != null) {           addDriverInfoBean(info);         }       }     } catch(FileNotFoundException fnfe) {       System.err.println("Can't find " + filename);     } catch(IOException ioe) {       System.err.println("Problem reading file: " + ioe);     } catch(ParserConfigurationException pce) {       System.err.println("Can't create DocumentBuilder");     } catch(SAXException se) {       System.err.println("Problem parsing document: " + se);     }   }   /** Build a DriverInfoBean object from an XML DOM node    *  representing a vendor driver in the format:    *  <P>    *  <PRE>    *    &lt;driver&gt;    *      &lt;vendor&gt;ORACLE&lt;/vendor&gt;    *      &lt;description&gt;Oracle&lt;/description&gt;    *      &lt;driver-class&gt;    *         oracle.jdbc.driver.OracleDriver    *      &lt;/driver-class&gt;    *      &lt;url&gt;    *        jdbc:oracle:thin:@[$host]:1521:[$dbName]    *      &lt;/url&gt;    *    &lt;/driver&gt;    *  </PRE>    */   public static DriverInfoBean createDriverInfoBean(Node node) {     Map map = new HashMap();     NodeList children = node.getChildNodes();     for(int i=0; i<children.getLength(); i++) {       Node child = children.item(i);       String nodeName = child.getNodeName();       if (child instanceof Element) {         Node textNode = child.getChildNodes().item(0);         if (textNode != null) {           map.put(nodeName, textNode.getNodeValue());         }       }     }     return(new DriverInfoBean((String)map.get("vendor"),                               (String)map.get("description"),                               (String)map.get("driver-class"),                               (String)map.get("url")));   } }

Listing 17.7 drivers.xml

 <?xml version="1.0"?> <!-- Used by DriverUtilities2. Here you configure information about your database server in XML. To add a driver, include a vendor keyword, description, driver-class, and URL. For general use, the host and database name should not be included in the URL; a special notation is required for the host and database name. Use [$host] as a placeholder for the host server and [$dbName] as a placeholder for the database name. Specify the actual host and database name when making a call to makeUrl (DriverUtilities). Then, the appropriate strings will be substituted for [$host] and [$dbName] before the URL is returned. --> <drivers>   <driver>     <vendor>  MSACCESS  </vendor>     <description>MS Access</description>     <driver-class>sun.jdbc.odbc.JdbcOdbcDriver</driver-class>     <url>jdbc:odbc:[$dbName]</url>   </driver>   <driver>     <vendor>  MYSQL  </vendor>     <description>MySQL Connector/J 3.0</description>     <driver-class>com.mysql.jdbc.Driver</driver-class>     <url>jdbc:mysql://[$host]:3306/[$dbName]</url>   </driver>   <driver>     <vendor>  ORACLE  </vendor>     <description>Oracle</description>     <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>     <url>jdbc:oracle:thin:@[$host]:1521:[$dbName]</url>   </driver> </drivers>

Listing 17.8 DriverInfoBean.java

 package coreservlets.beans; /** Driver information for a vendor.  Defines the vendor  *  keyword, description, driver class, and URL construct for  *  connecting to a database.  */ public class DriverInfoBean {   private String vendor;   private String description;   private String driverClass;   private String url; public class DriverInfoBean {   private String vendor;   private String description;   private String driverClass;   private String url;   public DriverInfoBean(String vendor,                         String description,                         String driverClass,                         String url) {     this.vendor = vendor;     this.description = description;     this.driverClass = driverClass;     this.url = url;   }   public String getVendor() {     return(vendor);   }   public String getDescription() {     return(description);   }   public String getDriverClass() {     return(driverClass);   }   public String getURL() {     return(url);   } }

Listing 17.9 ConnectionInfoBean.java

 package coreservlets.beans; import java.sql.*; /** Stores information to create a JDBC connection to  *  a database. Information includes:  *  <UL>  *    <LI>connection name  *    <LI>description of the connection  *    <LI>driver classname  *    <LI>URL to connect to the host  *    <LI>username  *    <LI>password  *  </UL>  */ public class ConnectionInfoBean {   private String connectionName;   private String description;   private String driver;   private String url;   private String username;   private String password;   public ConnectionInfoBean() { }   public ConnectionInfoBean(String connectionName,                             String description,                             String driver,                             String url,                             String username,                             String password) {     setConnectionName(connectionName);     setDescription(description);     setDriver(driver);     setURL(url);     setUsername(username);     setPassword(password);   }   public void setConnectionName(String connectionName) {     this.connectionName = connectionName;   }   public String getConnectionName() {     return(connectionName);   }   public void setDescription(String description) {     this.description = description;   }   public String getDescription() {     return(description);   }   public void setDriver(String driver) {     this.driver = driver;   }   public String getDriver() {     return(driver);   }   public void setURL(String url) {     this.url = url;   }   public String getURL() {     return(url);   }   public void setUsername(String username) {     this.username = username;   }   public String getUsername() {     return(username);   }   public void setPassword(String password) {     this.password = password;   }   public String getPassword() {     return(password);   }   public Connection getConnection() {     return(getConnection(driver, url, username, password));   }   /** Create a JDBC connection or return null if a    *  problem occurs.    */   public static Connection getConnection(String driver,                                          String url,                                          String username,                                          String password) {     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);     }   } }