The use of a flat file is fine for a small amount of data, but it can quickly get out of control. As the amount of data grows, access times slow to a crawl. And just finding data can become quite a challenge: imagine storing the names, cities, and email addresses of all your customers in a text file. It works great for a company that is just starting out, but what happens when you have hundreds of thousands of customers and want to display a list of all your customers in Boston with email addresses ending in "aol.com"?

One of the best solutions to this problem is a Relational Database Management System (RDBMS). At the most basic level, an RDBMS organizes data into tables. These tables are organized into rows and columns, much like a spreadsheet. Particular rows and columns in a table can be related (hence the term "relational") to one or more rows and columns in another table.

One table in a relational database might contain information about customers, another might contain orders, and a third might contain information about individual items within an order.

JDBC is a SQL-level API--one that allows you to execute SQL statements and retrieve the results, if any. The API itself is a set of interfaces and classes designed to perform actions against any database.

9.2.1. JDBC Drivers

The JDBC API, found in the java.sql package, contains only a few concrete classes. Much of the API is distributed as database-neutral interface classes that specify behavior without providing any implementation. The actual implementations are provided by third-party vendors.

An individual database system is accessed via a specific JDBC driver that implements the java.sql.Driver interface. Drivers exist for nearly all popular RDBMS systems, though few are available for free. Sun bundles a free JDBC-ODBC bridge driver with the JDK to allow access to standard ODBC data sources, such as a Microsoft Access database. However, Sun advises against using the bridge driver for anything other than development and very limited deployment. Servlet developers in particular should heed this warning because any problem in the JDBC-ODBC bridge driver's native code section can crash the entire server, not just your servlets.

JDBC drivers are available for most database platforms, from a number of vendors and in a number of different flavors. There are four driver categories:

9.2.2. Getting a Connection

The first step in using a JDBC driver to get a database connection involves loading the specific driver class into the application's JVM. This makes the driver available later, when we need it for opening the connection. An easy way to load the driver class is to use the Class.forName() method:

When the driver is loaded into memory, it registers itself with the java.sql.DriverManager class as an available database driver.

The next step is to ask the DriverManager class to open a connection to a given database, where the database is specified by a specially formatted URL. The method used to open the connection is DriverManager.getConnection() . It returns a class that implements the java.sql.Connection interface:

A JDBC URL identifies an individual database in a driver-specific manner. Different drivers may need different information in the URL to specify the host database. JDBC URLs usually begin with jdbc:subprotocol:subname. For example, the Oracle JDBC-Thin driver uses a URL of the form of jdbc:oracle:thin:@dbhost:port:sid;the JDBC-ODBC bridge uses jdbc:odbc:data- sourcename ;odbcoptions.

During the call to getConnection() , the DriverManager object asks each registered driver if it recognizes the URL. If a driver says yes, the driver manager uses that driver to create the Connection object. Here is a snippet of code a servlet might use to load its database driver with the JDBC-ODBC bridge and create an initial connection:

9.2.3. Executing SQL Queries

To really use a database, we need to have some way to execute queries. The simplest way to execute a query is to use thejava.sql.Statementclass. Statement objects are never instantiated directly; instead, a program calls the createStatement() method of Connection to obtain a new Statement object:

A query that returns data can be executed using the executeQuery() method of Statement. This method executes the statement and returns a java.sql.ResultSet that encapsulates the retrieved data:

You can think of a ResultSet object as a representation of the query result returned one row at a time. You use the next()method of ResultSet to move from row to row. The ResultSet interface also boasts a multitude of methods designed for retrieving data from the current row. The getString()and getObject()methods are among the most frequently used for retrieving column values:

You should know that the ResultSet is linked to its parent Statement. Therefore, if a Statement is closed or used to execute another query, any related ResultSet objects are closed automatically.

Example shows a very simple servlet that uses the Oracle JDBC driver to perform a simple query, printing names and phone numbers for all employees listed in a database table. We assume that the database contains a table named EMPLOYEES, with at least two fields, NAME and PHONE.

Example A JDBC-enabled servlet

import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class DBPhoneLookup extends HttpServlet {

  public void doGet(HttpServletRequest req, HttpServletResponse res)
                               throws ServletException, IOException {
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;

    res.setContentType("text/html");
    PrintWriter out = res.getWriter(); 

    try {
      // Load (and therefore register) the Oracle Driver
      Class.forName("oracle.jdbc.driver.OracleDriver"); 
     
      // Get a Connection to the database
      con = DriverManager.getConnection(
        "jdbc:oracle:thin:@dbhost:1528:ORCL", "user", "passwd");
     
      // Create a Statement object
      stmt = con.createStatement();
     
      // Execute an SQL query, get a ResultSet
      rs = stmt.executeQuery("SELECT NAME, PHONE FROM EMPLOYEES");
     
      // Display the result set as a list
      out.println("<HTML><HEAD><TITLE>Phonebook</TITLE></HEAD>");
      out.println("<BODY>");
      out.println("<UL>");
      while(rs.next()) {
        out.println("<LI>" + rs.getString("name") + " " + rs.getString("phone"));  
      } 
      out.println("</UL>");
      out.println("</BODY></HTML>");
    }
    catch(ClassNotFoundException e) { 
      out.println("Couldn't load database driver: " + e.getMessage());
    }
    catch(SQLException e) { 
      out.println("SQLException caught: " + e.getMessage());
    }
    finally {
      // Always close the database connection.
      try {
        if (con != null) con.close();
      }
      catch (SQLException ignored) { }
    }
  }
}

This is about as simple a database servlet as you are likely to see. All DBPhoneLookup does is connect to the database, run a query that retrieves the names and phone numbers of everyone in the employees table, and display the list to the user.

9.2.4. Handling SQL Exceptions

DBPhoneLookup encloses most of its code in a try/catch block. This block catches two exceptions: ClassNotFoundException and SQLException . The former is thrown by the Class.forName() method when the JDBC driver class can not be loaded. The latter is thrown by any JDBC method that has a problem. SQLException objects are just like any other exception type, with the additional feature that they can chain. The SQLException class defines an extra method, getNextException(), that allows the exception to encapsulate additional Exception objects. We didn't bother with this feature in the previous example, but here's how to use it:

This code displays the message from the first exception and then loops through all the remaining exceptions, outputting the error message associated with each one. In practice, the first exception will generally include the most relevant information

Table Methods to Retrieve Data from a ResultSet

Handling Null Fields

SQL Data Type	Java Type Returned by getObject()	Recommended Alternative to getObject()
`CHAR`	`String`	`String getString()`
`VARCHAR`	`String`	`String getString()`
`LONGVARCHAR`	`String`	`InputStream getAsciiStream() InputStream getUnicodeStream()`
`NUMERIC`	`java.math.BigDecimal`	`java.math.BigDecimal getBigDecimal()`
`DECIMAL`	`java.math.BigDecimal`	`java.math.BigDecimal getBigDecimal()`
`BIT`	`Boolean`	`boolean getBoolean()`
`TINYINT`	`Integer`	`byte getByte()`
`SMALLINT`	`Integer`	`short getShort()`
`INTEGER`	`Integer`	`int getInt()`
`BIGINT`	`Long`	`long getLong()`
`REAL`	`Float`	`float getFloat()`
`FLOAT`	`Double`	`double getDouble()`
`DOUBLE`	`Double`	`double getDouble()`
`BINARY`	`byte[]`	`byte[] getBytes()`
`VARBINARY`	`byte[]`	`byte[] getBytes()`
`LONGVARBINARY`	`byte[]`	`getBinaryStream()`
`DATE`	`java.sql.Date`	`java.sql.Date getDate()`
`TIME`	`Java.sql.Time`	`java.sql.Time getTime()`
`TIMESTAMP`	`Java.sql.Timestamp`	`java.sql.Timestamp getTimestamp()`

Handling null database values with JDBC can be a little tricky. (A database field can be set to null to indicate that no value is present, in much the same way that a Java object can be set to null.) A method that doesn't return an object, like getInt(), has no way of indicating whether a column is null or whether it contains actual information. (Some drivers return a string that contains the text "null" when getString() is called on a null column!) Any special value like -1, might be a legitimate value. Therefore, JDBC includes the wasNull() method in ResultSet, which returns true or false depending on whether the last column read was a true database null. This means that you must read data from the ResultSet into a variable, call wasNull(), and proceed accordingly. It's not pretty, but it works. Here's an example:

Another way to check for null values is to use the getObject() method. If a column is null, getObject() always returns null. Compare this to the getString() method that has been known, in some implementations, to return the empty string if a column is null. Using getObject()eliminates the need to call wasNull() and leads to simpler code.

9.2.7. Updating the Database

Most database-enabled web sites need to do more than just perform queries. When a client submits an order or provides some kind of information, the data needs to be entered into the database. When you know you're executing a SQL UPDATE, INSERT, or DELETE statement and you know you don't expect a ResultSet, you can use the executeUpdate() method of Statement. It returns a count that indicates the number of rows modified by the statement. It's used like this:

If you are executing SQL that may return either a ResultSet or a count (say, if you're handling user-submitted SQL or building generic data-handling classes), use the generic execute() method of Statement. It returns a boolean whose value is true if the SQL statement produced one or more ResultSet objects or false if it resulted in an update count:

The getResultSet()and getUpdateCount()methods of Statement provide access to the results of the execute() method.

Moving the Cursor in Scrollable Result Sets

MySQL Connector/J is an implemntation of the JDBC API for the MySQL relational database server. It strives to conform as much as possible to the API as specified by JavaSoft. It is known to work with many third-party products, including Borland JBuilder, IBM Visual Age for Java, SQL/J, JBoss, Weblogic, IBM WebSphere, Cocobase, ObjectRelationalBridge, etc.

MySQL Connector/J is distributed as a .zip or .tar.gz archive containing the sources and class files that also includes a class-file only "binary" .jar archive named "mysql-connector-java-3.0.9-stable-bin.jar".You will need to use the appropriate gui or command-line utility to un-archive the distribution (for example, WinZip for the .zip archive, and "tar" for the .tar.gz archive).Once you have un-archived the distribution archive,you can install the driver in one of two ways:

Either copy the "com" and "org" subdirectories and all of their contents to anywhere you like, and put the directory holding the "com" and "org" subdirectories in your classpath, or...

Put mysql-connector-java-3.0.9-stable-bin.jar in your classpath, either by adding the FULL path to it to your CLASSPATH enviornment variable, or putting it in $JAVA_HOME/jre/lib/ext.

If you are using a servlet engine or application server, you will have to read your vendor's documentation for more information on how to configure third-party class libraries, as most application servers ignore the CLASSPATH environment variable. If you are developing servlets and/or JSPs, and your application server is J2EE-compliant, you should put the driver's .jar file in the WEB-INF/lib subdirectory of your webapp, as this is the standard location for third party class libraries in J2EE web applications. You can also use the MysqlDataSource, MysqlConnectionPoolDataSource or MysqlXADataSource classes in the com.mysql.jdbc.jdbc2.optional package, if your J2EE application server supports/requires them. MysqlDataSource supports the following parameters (through standard "set" mutators):

If you are going to use the driver with the JDBC DriverManager, you would use "com.mysql.jdbc.Driver" as the class that implements java.sql.Driver. You might use this name in a Class.forName() call to load the driver: Class.forName("com.mysql.jdbc.Driver").newInstance();

To connect to the database, you need to use a JDBC url with the following format ([xxx] denotes optional url components):

jdbc:mysql://[hostname][,failoverhost...][:port]/[dbname][?param1=value1][&param2=value2].....

The driver now has fail-over support. This allows the driver to fail-over to any number of "slave" hosts and still perform read-only queries. Fail-over only happens when the connection is in a autoCommit(true) state, because fail-over can not happen reliably when a transaction is in progress. Most good application servers and connection pools set autoCommit to 'true' at the end of every transaction/connection use. The fail-over functionality has the following behavior: If the URL parameter "autoReconnect" is false. Failover only happens at connection initialization, and failback occurs when the driver determines that the first host has become available again. If the URL parameter "autoReconnect" is true:

Connector/J supports access to MySQL via named pipes on Windows NT/2000/XP using the 'NamedPipeSocketFactory' as a plugin-socket factory via the 'socketFactory' property. If you don't use a 'namedPipePath' property, the default of '\\.\pipe\MySQL' will be used.

Prerequisite :: Core Java

JDBC

Relational Databases