JDBC^TM Guide: Getting Started

2 - Connection

This overview is excerpted from JDBC^TM Database Access from Java^TM: A Tutorial and Annotated Reference, currently in progress at JavaSoft. This book, both a tutorial and the definitive reference manual for JDBC, will be published in the spring of 1997 by Addison-Wesley Publishing Company as part of the Java series.

2.1 Overview

A Connection object represents a connection with a database. A connection session includes the SQL statements that are executed and the results that are returned over that connection. A single application can have one or more connections with a single database, or it can have connections with many different databases.

2.1.1 Opening a Connection

The standard way to establish a connection with a database is to call the method DriverManager.getConnection. This method takes a string containing a URL. The DriverManager class, referred to as the JDBC management layer, attempts to locate a driver than can connect to the database represented by that URL. The DriverManager class maintains a list of registered Driver classes, and when the method getConnection is called, it checks with each driver in the list until it finds one that can connect to the database specified in the URL. The Driver method connect uses this URL to actually establish the connection.

A user can bypass the JDBC management layer and call Driver methods directly. This could be useful in the rare case that two drivers can connect to a database and the user wants to explicitly select a particular driver. Normally, however, it is much easier to just let the DriverManager class handle opening a connection.

The following code exemplifies opening a connection to a database located at the URL "jdbc:odbc:wombat" with a user ID of "oboy" and "12Java" as the password :

    String url = "jdbc:odbc:wombat";
    Connection con = DriverManager.getConnection(url, "oboy", "12Java");

2.1.2 URLs in General Use

Since URLs often cause some confusion, we will first give a brief explanation of URLs in general and then go on to a discussion of JDBC URLs.

A URL (Uniform Resource Locator) gives information for locating a resource on the Internet. It can be thought of as an address.

The first part of a URL specifies the protocol used to access information, and it is always followed by a colon. Some common protocols are "ftp", which specifies "file transfer protocol," and "http," which specifies "hypertext transfer protocol." If the protocol is "file," it indicates that the resource is in a local file system rather than on the Internet. (Underlining in the examples below is used to indicate the part being described; it is not part of the URL.)

            ftp://javasoft.com/docs/JDK-1_apidocs.zip
          http://java.sun.com/products/jdk/CurrentRelease
            file:/home/haroldw/docs/books/tutorial/summary.html

The rest of a URL, everything after the first colon, gives information about where the data source is located. If the protocol is file, the rest of the URL is the path to a file. For the protocols ftp and http, the rest of the URL identifies the host and may optionally give a path to a more specific site. For example, below is the URL for the JavaSoft home page. This URL identifies only the host:

    http://java.sun.com

By navigating from this home page, one can go to many other pages, one of which is the JDBC home page. The URL for the JDBC home page is more specific and looks like this:

    http://java.sun.com/products/jdbc

2.1.3 JDBC URLs

A JDBC URL provides a way of identifying a database so that the appropriate driver will recognize it and establish a connection with it. Driver writers are the ones who actually determine what the JDBC URL that identifies their particular driver will be. Users do not need to worry about how to form a JDBC URL; they simply use the URL supplied with the drivers they are using. JDBC's role is to recommend some conventions for driver writers to follow in structuring their JDBC URLs.

Since JDBC URLs are used with various kinds of drivers, the conventions are of necessity very flexible. First, they allow different drivers to use different schemes for naming databases. The odbc subprotocol, for example, lets the URL contain attribute values (but does not require them).

Second, JDBC URLs allow driver writers to encode all necessary connection information within them. This makes it possible, for example, for an applet that wants to talk to a given database to open the database connection without requiring the user to do any system administration chores.

Third, JDBC URLs allow a level of indirection. This means that the JDBC URL may refer to a logical host or database name that is dynamically translated to the actual name by a network naming system. This allows system administrators to avoid specifying particular hosts as part of the JDBC name. There are a number of different network name services (such as DNS, NIS, and DCE), and there is no restriction about which ones can be used.

The standard syntax for JDBC URLs is shown below. It has three parts, which are separated by colons:

      jdbc:<subprotocol>:<subname>

The three parts of a JDBC URL are broken down as follows:

jdbc-the protocol. The protocol in a JDBC URL is always jdbc.
```
    
```
<subprotocol>-the name of the driver or the name of a database connectivity mechanism, which may be supported by one or more drivers. A prominent example of a subprotocol name is "odbc", which has been reserved for URLs that specify ODBC-style data source names. For example, to access a database through a JDBC-ODBC bridge, one might use a URL such as the following:
```
      jdbc:odbc:fred

 In this example, the subprotocol is "odbc", and the subname "fred" is a local 
ODBC data source.
```
If one wants to use a network name service (so that the database name in the JDBC URL does not have to be its actual name), the naming service can be the subprotocol. So, for example, one might have a URL like:
```
      jdbc:dcenaming:accounts-payable

 In this example, the URL specifies that the local DCE naming service should 
resolve the database name "accounts-payable" into a more specific name that 
can be used to connect to the real database.
```
<subname>-a way to identify the database. The subname can vary, depending on the subprotocol, and it can have a subsubname with any internal syntax the driver writer chooses. The point of a subname is to give enough information to locate the database. In the previous example, "fred" is enough because ODBC provides the remainder of the information. A database on a remote server requires more information, however. If the database is to be accessed over the Internet, for example, the network address should be included in the JDBC URL as part of the subname and should follow the standard URL naming convention of

//hostname:port/subsubname Supposing that "dbnet" is a protocol for connecting to a host on the Internet, a JDBC URL might look like this:

jdbc:dbnet://wombat:356/fred

2.1.4 The "odbc" Subprotocol

The subprotocol odbc is a special case. It has been reserved for URLs that specify ODBC-style data source names and has the special feature of allowing any number of attribute values to be specified after the subname (the data source name). The full syntax for the odbc subprotocol is:

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

Thus all of the following are valid jdbc:odbc names:

      jdbc:odbc:qeor7
      jdbc:odbc:wombat
      jdbc:odbc:wombat;CacheSize=20;ExtensionCase=LOWER
      jdbc:odbc:qeora;UID=kgh;PWD=fooey

2.1.5 Registering Subprotocols

A driver developer can reserve a name to be used as the subprotocol in a JDBC URL. When the DriverManager class presents this name to its list of registered drivers, the driver for which this name is reserved should recognize it and establish a connection to the database it identifies. For example, odbc is reserved for the JDBC- ODBC Bridge. If ther