JDBC^TMGuide: Getting Started

3 Connection

This overview is excerpted from JDBC^TM Database Access with 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.

3.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.

3.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");

3.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. For general use, a URL is made up of three parts, with only the first part being required for all URLs. (Boldface in the examples below is used to indicate the part being described; it is not part of the URL.)

A URL has three parts:

Protocol used to access the information. The protocol 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.
```
        ftp://javasoft.com/docs/JDK-1_apidocs.zip
        http://java.sun.com/products/JDK/1.1
        file:/home/haroldw/docs/tutorial.html
```
Host information. This part gives the information needed to find and access the host where the resource resides. The host information begins with a double slash ("//") if this is an Internet application, such as ftp or http, and a single slash ("/") if it is not. The host information ends with a single slash ("/"). Host information is itself divided into three parts:
- Domain name of the host if the resource resides on the Internet; if the resource is a local file, there is no host name. Instead there is just the path of the file.
- User login name and password, which are included if needed.
- Port number, which is included if needed. A port number follows the host name and a colon (":").
The most common case is to have double slashes and only the hostname:
```
        http://java.sun.com
```
The following URL contains the port number 80:
```
        http://java.sun.com:80/doc/tutorial.html
```
The following is an example of a URL with a login name "happy" and password "1234" included as part of the hostname:
```
        http://netsmile.grin.com."happy"."1234"/news/latest
```
In the domain name java.sun.com, com indicates that java.sun is a commercial venture. Some other designations are edu for an educational institution, org for a non-profit organization, and gov for governmental organization.
Path of what is to be accessed. In the following example, products and JDK are directories, and 1.0.2 is a file. This URL gives the location of the Java Developer's Kit, version 1.0.2:

        http://java.sun.com/products/JDK/1.0.2

3.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. A driver needs to understand only one URL naming syntax and can happily reject any other URLs that are presented to it. It is the driver writers themselves who determine the format of a JDBC URL. The first part will always be jdbc. The second part will be the subprotocol, which the driver writer provides. The rest of a JDBC URL is the datasource. Information needed to access the data source, such as the user's login name and password, may be part of the JDBC URL, or it may be supplied separately. Users trying to connect to a database just follow the format provided with a driver and supply the information needed to access a database. JDBC's role is simply to recommend some conventions for driver writers to use in structuring 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 after the subname (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.

Since the standard URL naming mechanism already provides many of the features needed in JDBC URLs, the JDBC URL conventions just add a new syntax. The standard syntax for JDBC URLs is:

      jdbc:<subprotocol>:<subname>

A JDBC URL has three parts, which are separated by colons:

jdbc is the protocol. The protocol in a JDBC URL is always jdbc.
<subprotocol> is usually the driver or the 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.
<subname> is 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

3.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

3.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 there were, for another example, a Miracle Corporation, it might want to register "miracle" as the subprotocol for the JDBC driver that connects to its Miracle DBMS so that no one else would use that name.

JavaSoft is acting as an informal registry for JDBC subprotocol names. To register a subprotocol name, send email to:

      jdbc@wombat.eng.sun.com

3.1.6 Sending SQL Statements

Once a connection is established, it is used to pass SQL statements to its underlying database. JDBC does not put any restrictions on the kinds of SQL statements that can be sent; this provides a great deal of flexibility, allowing the use of database-specific statements or even non-SQL statements. It requires, however, that the user be responsible for making sure that the underlying database can process the SQL statements being sent and suffer the consequences if it cannot. For example, an application that tries to send a stored procedure call to a DBMS that does not support stored procedures will be unsuccessful and generate an exception. JDBC requires that a driver provide at least ANSI SQL-2 Entry Level capabilities in order to be designated JDBC COMPLIANT^TM. This means that users can count on at least this standard level of functionality.

JDBC provides three classes for sending SQL statements to the database, and three methods in the Connection interface create instances of these classes. These classes and the methods which create them are listed below:

Statement- -created by the method createStatement. A Statement object is used for sending simple SQL statements.
PreparedStatement- -created by the method prepareStatement. A PreparedStatement object is used for SQL statements that take one or more parameters as input arguments (IN parameters). PreparedStatement has a group of methods which set the value of IN parameters, which are sent to the database when the statement is executed. Instances of PreparedStatement extend Statement and therefore include Statement methods. A PreparedStatement object has the potential to be more efficient than a Statement object because it has been pre-compiled and stored for future use.
CallableStatement- -created by the method prepareCall. CallableStatement objects are used to execute SQL stored procedures- -a group of SQL statements that is called by name, much like invoking a function. A CallableStatement object inherits methods for handling IN parameters from PreparedStatement; it adds methods for handling OUT and INOUT parameters.

The following list gives a quick way to determine which Connection method is appropriate for creating different types of SQL statements:

createStatement method is used for

simple SQL statements (no parameters)

prepareStatement method is used for

SQL statements with one or more IN parameters

simple SQL statements that are executed frequently

prepareCall method is used for

call to stored procedures

3.1.7 Transactions

A transaction consists of one or more statements that have been executed, completed, and then either committed or rolled back. When the method commit or rollback is called, the current transaction ends and another one begins.

A new connection is in auto-commit mode by default, meaning that when a statement is completed, the method commit will be called on that statement automatically. In this case, since each statement is committed individually, a transaction consists of only one statement. If auto-commit mode has been disabled, a transaction will not terminate until the method commit or rollback is called explicitly, so it will include all the statements that have been executed since the last invocation of the commit or rollback method. In this second case, all the statements in the transaction are committed or rolled back as a group.

The method commit makes permanent any changes an SQL statement makes to a database, and it also releases any locks held by the transaction. The method rollback will discard those changes.

Sometimes a user doesn't want one change to take effect unless another one does also. This can be accomplished by disabling auto-commit and grouping both updates into one transaction. If both updates are successful, then the commit method is called, making the effects of both updates permanent; if one fails or both fail, then the rollback method is called, restoring the values that existed before the updates were executed.

Most JDBC drivers will support transactions. In fact, a JDBC-compliant driver must support transactions. DatabaseMetaData supplies information describing the level of transaction support a DBMS provides.

3.1.8 Transaction Isolation Levels

If a DBMS supports transaction processing, it will have some way of managing potential conflicts that can arise when two transactions are operating on a database at the same time. A user can specify a transaction isolation level to indicate what level of care the DBMS should exercise in resolving potential conflicts. For example, what happens when one transaction changes a value and a second transaction reads that value before the change has been committed or rolled back? Should that be allowed, given that the changed value read by the second transaction will be invalid if the first transaction is rolled back? A JDBC user can instruct the DBMS to allow a value to be read before it has been committed ("dirty reads") with the following code, where con is the current connection:

    con.setTransactionIsolation(TRANSACTION_READ_UNCOMMITTED);

The higher the transaction isolation level, the more care is taken to avoid conflicts. The Connection interface defines five levels, with the lowest specifying that transactions are not supported at all and the highest specifying that while one transaction is operating on a database, no other transactions may make any changes to the data read by that transaction. Typically, the higher the level of isolation, the slower the application executes (due to increased locking overhead and decreased concurrency between users). The developer must balance the need for performance with the need for data consistency when making a decision about what isolation level to use. Of course, the level that can actually be supported depends on the capabilities of the underlying DBMS.

When a new Connection object is created, its transaction isolation level depends on the driver, but normally it is the default for the underlying database. A user may call the method setIsolationLevel to change the transaction isolation level, and the new level will be in effect for the rest of the connection session. To change the transaction isolation level for just one transaction, one needs to set it before the transaction begins and reset it after the transaction terminates. Changing the transaction isolation level during a transaction is not recommended, for it will trigger an immediate call to the method commit, causing any changes up to that point to be made permanent.

Contents | Prev | Next

jdbc@wombat.sun.com or jdbc-odbc@wombat.sun.com