Brought to you by EarthWeb
ITKnowledge Logo Login Graphic Click Here!
Click Here!
ITKnowledge
Search this book:
 
Search the site:
 
EXPERT SEARCH ----- nav

EarthWeb Direct

EarthWeb Direct

EarthWeb sites: other sites

Previous Table of Contents Next


Chapter 5
Database Connectivity, Step by Step

In This Chapter

This chapter discusses step-by-step programming with Java Database Connectivity (JDBC). This chapter includes:

  Initiating a database connection
  Sending simple SQL statements and retrieving results
  Error management

This chapter explains how to make a successful connection to a database, how to send SQL statements to this database, and how to retrieve the results. The procedure is very straightforward; typically, every database application developed using Java and JDBC uses it.

First Steps

JDBC is composed of a set of interfaces and classes that implement the functions needed to deal with a database management system (DBMS). We use these interfaces and classes in this chapter:

  java.sql.DriverManager
  java.sql.Driver
  java.sql.Connection
  java.sql.Statement
  java.sql.ResultSet
  java.sql.Date
  java.sql.Time
  java.sql.Timestamp
  java.sql.Types
  java.sql.DataTruncation

First, make sure that the JDBC Application Programming Interface (API) classes and specific drivers implementations are available on your system and reachable by following the CLASSPATH environment variable value. This environment variable should normally point to one or more directories where your Java classes are located. If necessary, update it or move the JDBC and drivers subdirectories to an appropriate location on your hard disk. When done, the Java class loader will be able to find these classes and will not try to download them from the network.

Typically, you should see a subtree resembling the following when scanning your CLASSPATH environment variable (jdbc/odbc/… was used for Listing 5-1; you may choose not to install it).

Listing 5-1: A typical class subtree.

classes/
+—— java/
|    +—— .../
|    |
|    +—— sql/
|       +——-   CallableStatement.class
|       Connection.class
|       DatabaseMetaData.class
|       DataTruncation.class
|       Date.class
|       Driver.class
|       DriverInfo.class
|       DriverManager.class
|       DriverPropertyInfo.class
|       PreparedStatement.class
|       ResultSet.class
|       ResultSetMetaData.class
|       SQLException.class
|       SQLWarning.class
|       Statement.class
|       Time.class
|       Timestamp.class
|               Types.class
+——   jdbc/
+—— odbc/
+——   JdbcOdbc.class
        JdbcOdbcBoundCol.class
        JdbcOdbcBoundParam.class
...
...

To make the JDBC classes available to the current class using the abbreviated name, use the import statement in source programs. The code will be more readable by not using the fully qualified names. So, the following line should be included at the top of the Java program, applet, or servlet:

import java.sql.*;

This statement is really useful for the programmer. Replace the star symbol with the relevant classes in the JDBC package.

Use the usual command line to compile your JDBC project:

% javac example.java

You may use the classpath parameter to specify additional Java classes that are not reachable through the CLASSPATH environment variable.

Database Connection

A connection must be initiated to access a database. The connection is a Java object containing Java methods to access the database. The connection class also holds information on the state of connections.. Various connection parameters are necessary, for example, to locate the database, to specify drivers and protocols, and to specify user account and password in the DBMS. The format of these parameters and how to set them is discussed later.

As shown in Figure 5-1, the first step is the establishment of a connection. The last step will, of course, be the termination (“closing”) of the connection. Opening and closing the connection usually creates and releases user resources within the database management system and driver.


Figure 5-1:  Overview of the connection.

To create an instance of a connection, it is necessary to understand the JDBC naming scheme.

JDBC’s Database Naming

JDBC uses a particular syntax to name a database. The designers wanted to use a widely understood, appreciated, and supported convention: the Uniform Resource Locator (URL) syntax. In this case, the URL has the following form:

jdbc:<subprotocol>:<subname>

In this form, jdbc means that the protocol is JDBC, the subprotocol field is the name of the JDBC driver to be used, and the subname is a parameter string that is dependent on the subprotocol. Figure 5-2 illustrates the JDBC URL naming mechanism.


Figure 5-2:  JDBC mechanism to name a data source.

These examples show some of the uses of database URLs:

jdbc:odbc:sampledb

A JDBC-Open Database Connectivity (ODBC) bridge will be used, and the ODBC DSN (data source name) is sampledb.

jdbc:odbc:sampledb;UID=javauser;PWD=hotjava

This URL is the same as the previous one, but adds a user-ID and password. Other attributes may also be added.

jdbc:mydbdrv://www.mydomain.net:8192/mydb

In this case, the subprotocol is called mydbdrv. The database engine is running on the www.mydomain.net host (the subname field), the Transmission Control Protocol/Internet Protocol (TCP/IP) port that should be used is 8192, and mydb is the name of the database to be accessed. The significance of these parameters is somewhat arbitrary. For example, if the subprotocol (the driver) always uses the same port number, it is unnecessary to provide it in the URL. In this example, mydb, called a sub-subname, refers to a specific database instance. Other types of JDBC drivers may interpret the sub-subname as something else, other than a specific database instance name.

jdbc:dcenaming:employees

This URL suggests that a local Distributed Computing Environment (DCE) naming service should be used to locate the database named “employees.” This service will resolve “employees” into a particular name more appropriate to locate the database engine. Another type of network naming protocol could be used, for example, NIS (Network Information System).

Note that the ODBC subprotocol URL should always conform to:

jdbc:odbc:<dsn>[;<attribute-name>=<attribute-value>]*

The JDBC URL syntax is flexible enough to allow specific drivers to interpret their own syntax.


Previous Table of Contents Next
HomeAbout UsSearchSubscribeAdvertising InfoContact UsFAQs
Use of this site is subject to certain Terms & Conditions.
Copyright (c) 1996-1999 EarthWeb Inc. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.