Getting Started With JDBC

By John Papageorge

(August 1997)

원본출처 : http://developer.java.sun.com/servlet/PrintPageServlet?url=http%3A//developer.java.sun.com/developer/technicalArticles/Interviews/StartJDBC/

In the last several months there has been a lot of excitement over JDK 1.1 features, such as RMI, Beans, the new event model, and internationalization. But according to Aaron Alpar, there has not been a whole lot of talk about the JDBC API that is now standard with the 1.1 release. And he thinks it deserves some loving attention.

"JDBC is the most useful tool in the JDK, and the least understood," Alpar insists.

He believes the JDBC, a simple set of classes for accessing relational databases from Java with a class library that mirrors the ODBC from Microsoft, has been ignored because database access is not new when compared to RMI and the new event model. He also believes it hasn't gotten its due because many vendors, rather than waiting for JDBC to be an official part of the 1.1 release, have come up with JDBC-compliant APIs.

"If you visit the JavaSoft web site looking for something that will allow you to write Java applications to your Sybase database, you're likely to run into a dizzying array of options; from direct desktop drivers, to middleware, to application-server solutions all providing JDBC support," Alpar says.

Keep in mind that JDBC database access is not limited to some of the larger vendors of database servers, such as Informix, Oracle, Sybase, and Microsoft. Database access from JDBC equally applies to smaller desktop database systems, such as xBase files, FoxPro, MS Access, and mSQL. JDBC, through ODBC, even works with text files, and Excel spreadsheets.

Alpar, who is currently on a 14-city tour promoting Java, plans to celebrate the JDBC. "I'm going to discuss the JDBC API," he says. "It's a good foundation for understanding the current state-of-the art in Java database access. It will also help users understand the features in some of the higher-level APIs, such as JSQL."

Within JDBC there are four particularly important classes: DriverManager, Connection, PreparedStatement, and ResultSet. Each class corresponds to a indispensable phase of database access:

The DriverManager loads and configures your database driver on your client.

The Connection class performs connection and authentication to your database server using URLs.

PreparedStatement moves SQL to the database engine for preprocessing and eventually execution.

The ResultSet class allows for the inspection of results from "select" statements.

Alpar explains that each class relies on it's previous class for instance creation. For example, an instance of Connection cannot exist without being created from the DriverManager.

Below, Alpar shows you how to perform the basics. You will find that simple operations, like inserting a row, are simple to perform. When you get into more complex operations, such as passing out parameters from stored procedures, your JDBC code will get more complicated.

Because JDBC can work with a great number of drivers, there's a class called the DriverManager to load drivers into your environment. Generally, you will only need the driver manager once, in the beginning of the main or init section of your application or applet, respectively.

Example In my examples, I'll be using dbANYWHERE from Symantec with tables from a fictitious order entry system.

  String driverName = "symantec.itools.db.jdbc.Driver";
  Properties systemProperties = System.getProperties();
  systemProperties.put("jdbc.drivers", driverName);

There's only one method that you really need to know for the DriverManager, and that's getConnection. Connection objects are created from the class DriverManager. Connection objects are driven by URLs. For a more complete discussion on how to form JDBC URLs, I recommend that you read the URL specifications from JDBC Guide: Getting Started from the JavaSoft web site.

The DriverManager is very politically correct about how it finds the right driver for the URL specified in getConnection. It's not dictatorial but rather operates by voluntary delegation, where JDBC visits each driver registered with the DriverManager and asks, "will you connect to this URL?" If the answer is no, the next driver is asked and the process continues, otherwise the driver attempts to contact the URL:

  aConnection = aDbAnywhereDriver.getConnection
  ("jdbc:dbaw://localhost:8889/tutorial", "guest", "guestpass");

In the preceding example, "jdbc" is the main protocol, "dbaw" is the sub protocol, and is followed by the hostname "localhost" and port number "8889" of the dbANYWHERE server.

As mentioned before, each class is responsible for creating the next class, as in the case of the PreparedStatement objects that are created from a Connection instance:

  try {
    aPreparedStatement = 
    aConnection.prepareStatement ( 
      "select order_id, 
      description from order_items" );
  } catch ( SQLException e ) {
    System.out.println (
     e.messageString() );
  }

Retrieving the information is a fairly straightforward process of fetching the row and retrieving the row values column by column:

  try {
    // Fetch the next row
    aPackageResultSet.next();
    // then get the first value by column position 
    aPackageResultSet.getDouble (1);
    // and then the next value by column name.
    aPackageResultSet.getString ("description");
  } catch ( SQLException e ) {
    System.out.println ( e.messageString() );
  }

Note that first, I'm retrieving values out of the database using what JavaSoft likes to call getXXX methods. GetXXX is simply shorthand for a typed accessor method such as getString, getDouble, or getInteger. Second, I've used two forms of those accessor methods. One method retrieves values by column index starting at 1. The other method retrieves values by column name. The first method is much more efficient, however in my opinion, more open to producing erroneous code.

PreparedStatement objects can also take in-parameters, which are parameters that can be passed into a SQL statement after the statement has been prepared on the database server and before the statement is executed. By preparing statements in advance, I can then execute them over an over again without having to resend and reparse the statement on the database server. This technique is a big resource saver for repetitive SQL statements. I've used an example of an insert statement below:

  PreparedStatement anInsertStatement = 
    aConnection.prepareStatement (
    "insert into order_items ( order_id, line_id, description ) 
        values (?, ?, ? )" );

    anInsertStatement.setDouble ( 1, 100.00 );
    anInsertStatement.setDouble ( 2, 1.00 );
    anInsertStatement.setString ( 3, "tennis racket" );
    anInsertStatement.execute();

The preceding examples demonstrate the basics of how to get things done in JDBC. I've shown you the basics of the four classes, DriverManager, Connection, PreparedStatement, and ResultSet. There are also features, including transaction management, support for out parameters, and many more. JavaSoft, in their usual style, has made an excellent set of JDBC documentation available at no cost on their Web site. (Editors note: We didn't pay this guy to say these nice things. Really!) There you'll find a simple set of classes for accessing relational databases from Java and the class library mirrors the ODBC API from Microsoft, a brief description, and how to use them. And that's really most of what JDBC is all about.

------------------------------------------------------------------

Of Java, Databases, and Really Cool Dead Guys

Aaron Alpar explains JDBC, as well as where he goes for programming explanation.

Ever Feel Like Life Is Just Scrolling By?

In a Java-code sidebar, Alpar shows you how to use JDBC to implement an aspect of cursor control.