| Contents | Prev | Next | JDBCTM Guide: Getting Started |
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.
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");
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
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:
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
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 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.
Connection method
is appropriate for creating different types of SQL statements:
createStatementmethod is used for
- simple SQL statements (no parameters)
prepareStatementmethod is used for
prepareCallmethod is used for
- call to stored procedures
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.
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.