Previous Table of Contents Next


Table 3.4 ProductSupplier Schema
FIELD TYPE NOTES
SupplierID Number (integer) Primary key
ProductID Number (integer) Foreign key
Price Currency

JDBC Basics

The process for accessing a database using JDBC is fairly straightforward. This section describes the steps necessary to create a connection to a data source and then access it.

The first step in any enterprise project is to determine the environment in which the project will be deployed. For example, are you writing an applet, an application, servlets, or enterprise JavaBeans? If JDBC is part of the project, you will have a number of JDBC–specific issues to decide. The main configuration issues you need to determine will be:

Which database should you choose? You might need to consider database–specific issues, such as how to map custom data types to Java.


Figure 3.1  INV database schema.

Which driver should you choose? There may be more than one driver available for a given data source. Knowing the benefits of each driver will help you determine which is right for your application. For example, if you choose the ODBC driver, you have more flexibility as to which data source you connect to; however, if you choose a driver specifically designed for Oracle, it may be more efficient for accessing an Oracle database.
Where will the driver be located? Depending on where you locate the driver, you may have performance considerations. For example, if you load the driver from a remote server, you’ll need to handle standard Java security issues.

Regardless of the configuration you choose, the steps for accessing and interacting with the data source are almost identical. First, load a JDBC driver. A JDBC driver should implement the java.sql.Driver interface. When a driver class is loaded, it should create an instance of itself and register it with the DriverManager. This means that a user can load and register a driver by calling Class.forName(“package.DriverName”). Table A.3 in Appendix A contains a partial list of the drivers currently available. The list is maintained on the Sun Web site at www.javasoft.com/products/jdbc/jdbc.drivers.html.

NOTE:  When loading a driver, you must consider certain security issues. For example, if you have an applet in a Web browser that loads a driver over the network, you need to make sure the applet is from a trusted source. For more information on this subject, check out
java.sun.com/products/jdk/1.2/docs/ guide/jdbc/spec/jdbc–spec.frame5.html.

Next, create a connection to the data source. Create a URL that describes your data source and call the getConnection() method of the DriverManager. This method searches all the available drivers, looking for one that understands the URL passed in. Once the method finds one that can handle your data source, it returns a connection to that data source, as shown in Figure 3.2.


Figure 3.2  Selecting a driver.

If necessary, query the Connection for meta–data about the database structure. The meta–data is returned from the Connections’ method getMetaData in an object implementing the interface DatabaseMetaData. This interface contains a large number of methods. The DatabaseMetaData methods are described in detail in Appendix A, “JDBC Information.” The metadata is useful for figuring out unique features of a data source, such as a data source’s ability to use the GROUP BY clause or whether the database supports transactions.

Once you have your connection, create a SQL statement from that connection. To create a statement, call the Connections method createStatement(). This returns an object implementing the Statement interface. Use this Statement object to execute the query that uses the statement. The Statement object can be used to execute a SQL query by calling one of the methods listed in Table 3.5.

NOTE:  JDBC 2.0 adds the method int[] executeBatch(), which can be used to submit a batch of commands to the database for execution. This method returns an array of update counts for each statement executed.

Once a statement is executed, check for any problems with the query by calling getWarnings(). This returns an instance of the SQLWarning class.

If no warnings occurred or if you can ignore the warnings, process the results from the query. Call getResults() to access ResultSet. Use this object to access all the return values.

Finally, close the database connection. Database connections are usually a limited resource for a database, so close the connection when you are finished.

Table 3.5 Execute Methods of the Statement Interface
METHOD FUNCTION
boolean execute(String sql) Execute a SQL statement that may return multiple result sets. This usually occurs when you call a stored procedure that returns non–rectangular data. The method returns true if the next result from this statement will be a result table.
ResultSet executeQuery(String sql) Execute a SQL statement that returns a single result set. A result set is an interface that represents a set of data returned from a query.
int executeUpdate(String sql) Execute a SQL INSERT, UPDATE, or DELETE statement. It returns a the number of affected rows.

This is the complete process for accessing a data source with JDBC. The next section looks at the source code for a simple example that demonstrates all of these steps. You can compile and run the version on your CD–ROM; the example is located in the Chapter 3 directory.

A Simple JDBC Example

This small example shows the steps to connect, query, and print the results of a database call. To keep the example simple, little error checking is done. A Microsoft Access database called INV.mdb is used as the data source; it is available on the CD–ROM. The URL for the data source in this example is jdbc:odbc:inventory.

NOTE:  You must have the JDBC driver that you are using in your class path. For example, this example uses the sun.jdbc.odbc.JdbcOdbcDriver.



Previous Table of Contents Next