Our query retrieves all columns from the Inventory table (SELECT * FROM Inventory), so we need to determine the number of columns by looking at the metadata of the result set. Bold lines indicate the typical code used for creating a connection, querying the database, and querying the result set metadata to determine how many rows were returned from the query. Figure 3.3 shows the results.

import java.sql.*;

public class Example
{
    public static void main (String args[])
    {
        try
        {
        /*
          Load the JDBC–ODBC bridge,
          it will register itself with DriverManager.
        */
          Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
        } catch (Exception e)
        {
            System.out.println(“JDBC–ODBC driver failed to load.”);
            return;
        }
  
        try
        {  
         /*
           Pass the url to the DriverManager.
           It should find our driver and return a database
           connection to an ODBC data source called “inventory.”
         */

            Connection con =
              DriverManager.getConnection(“jdbc:odbc:inventory”,“”,“”);
  
          // Create a statement and use it to execute a query.
          Statement stmt = con.createStatement();
          /*
            This returns a result set.
            We will assume it is not null for simplicity.
          */
            ResultSet rs =
              stmt.executeQuery(“SELECT * FROM inventory” +
                                “ORDER BY price”);
  
          /*
            In order to determine the type and amount of data returned
            by our query, we access the metadata of the result set.
          */
            ResultSetMetaData rsmd = rs.getMetaData();

          /*
            From the metadata, we determine how many columns were
            actually returned.
          */
            int numberOfColumns = rsmd.getColumnCount();
            int rowCount = 1;
          /*
            We will loop through the result set, printing out
            values for each row.
          */
            while (rs.next())
            {
                for (int i = 1; i <= numberOfColumns; i++)
                {
                    System.out.print(rs.getString(i)+“ ”);
                }              
                System.out.println(“”);
                rowCount++;
            }
            // We are done with the statement, so we close it.
            stmt.close();
            // We are done with our connection, so we close it.
            con.close();
        } catch (Exception e)
        {
            System.out.println(e);
        }
    }
}

You can experiment with the SELECT statement by specifying which columns of Inventory you want to retrieve. The next section looks at the specifics of connecting to any data source.

Connecting to a Data Source

The last section provided a simple example of a complete JDBC application. This section provides a detailed look at the process for choosing a driver and connecting to a data source.

Any item with a JDBC driver, such as a database management system (DBMS), a file system, or a text file, can be a data source. To connect to a data source, first identify the data source you intend to connect to. For example, is it Oracle? Sybase? Is it another relational database or some other type of data source such as a file system? Next, obtain a JDBC driver for your data source. Typically, the data source vendor will provide the driver; however, you can get a complete list of the registered vendors from the Sun Web site.

Figure 3.3  Example results.

Determine if the data source will be local to the application or remotely accessed. If the driver will be remotely accessed, make sure you consider the standard Java security issues involved in loading Java classes over the network, such as loading classes in an applet. There are no unusual security issues for local access. Just make sure the driver is in your CLASSPATH.

---

NOTE:  JDBC tracks the class loader that provides each driver. When the DriverManager class opens a connection, it uses only drivers from the local file system or from the same class loader as the code requesting the connection.

---

Load the appropriate driver in your code. One way to load drivers is to put the name of the driver in the jdbc.drivers System property. This will be checked when the DriverManager class is initialized. If you want to load several drivers, define a list of drivers, separated by colons. Your property entry might look something like this:

jdbc.drivers= sun.jdbc.odbc.JdbcOdbcDriver:another.typeof.driver

Another option is to use the Class.forName() method to load the class explicitly. When a driver is loaded, it is expected to call the DriverManager method registerDriver(). This registers the driver so it can be used later. If you want to know what drivers are available, call the DriverManager method getDrivers().

Once you have loaded the driver, call DriverManager.getConnection() to get a connection to your data source. There are several versions of the getConnection() method; they are listed in Table 3.6. All of these versions take a URL parameter. The URL is a JDBC protocol that specifies the data source to load. The URL takes the form

jdbc:sub–protocol:datasource–name;optional–parameter=value

Table 3.7 lists the meaning of each URL component.

Each of the getConnection() methods returns a Connection object representing a physical connection to the specified URL.

---

NOTE:  A driver developer can reserve a name to be used as the subprotocol in a JDBC URL. JavaSoft acts as an informal registry for JDBC subprotocol names. To register a subprotocol name, JavaSoft recommends you send e–mail to jdbc@wombat.eng.sun.com.

---

Use the Connection returned by DataManager to access the data source. A Connection object represents a session with a data source. Any number of SQL statements can be executed over this Connection, as shown in Figure 3.4. An application can have one or more connections to a single data source, or it can have connections to several databases.