Formulating a Database URL

After you've loaded the driver, you can establish a connection using the DriverManager.getConnection( ) method. This method is overloaded and therefore has various forms. However, each form requires a database URL. A database URL is an address that points to your database. Formulating a database URL is where most of the problems associated
with establishing a connection occur. For Oracle, the database URL has the following general form:
jdbc:oracle:driver:@database
database ::= {host:port:sid | net_service_name | connect_descriptor}
which breaks down as:
driver
Specifies the type of JDBC driver to use for the connection. The following choices are available:
oci7
For the Oracle 7.3.4 OCI driver
oci8
For an Oracle 8.x.x OCI driver
oci
For an Oracle 9.x.x OCI driver
thin
For the Oracle Thin driver
kprb
For the Oracle internal driver
database
Specifies the database to which you want to connect. You can specify a host, port, and SID; a net service name; or a connect descriptor.
host:port:sid
Used only with the Thin driver and identifies the target database using the following information:
host
The TCP/IP address or DNS alias (hostname) for your database server
port
The TCP/IP port number of the Oracle listener
sid
The System Identifier of your database
net_service_name
Used only with the OCI driver. A net service name, or tnsnames.ora file entry as it is commonly known, is a short name that resolves to a connect descriptor, which is a specially formatted Net8 database address. Net service names are often resolved via a local file named tnsnames.ora but may also be resolved using centralized methods such as Oracle Names. The OCI driver depends on the Oracle Client software to be able to resolve a net service name. That's why net service names are used only with the OCI driver.
connect_descriptor
Can be used by either driver and is a Net8 address specification such as that normally found in a tnsnames.ora file.
Now that you know the rules of how to formulate a database URL, let's look at several examples as we explore the overloaded forms of the getConnection( ) method.
Using a database URL with a username and password
The most commonly used form of getConnection( ) requires you to pass a database URL, a username, and a password:
DriverManager.getConnection(String url, String user, String password)
When using the Thin driver, you'll specify a host:port:sid value for the database portion of the URL. For example, if you have a host at TCP/IP address 192.0.0.1 with a host name of esales, and your Oracle listener is configured to listen on port 1521, and your database system identifier is orcl, then the database portion of the URL would look like:
esales:1521:orcl
The corresponding complete database URL would then be:
jdbc:oracle:thin:@esales:1521:orcl
When you call the getConnection( ) method, it returns a Connection object. For example:
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@esales:1521:orcl", "scott", "tiger" );
You'll use this Connection object later to create other objects that will allow you to insert, update, delete, and select data.
When using the OCI driver, you'll specify a net service name for the database portion of the URL. For example, if your net service name was esales, your call to create a connection would look like:
Connection conn = DriverManager.getConnection(
"jdbc:oracle:oci8:@esales", "scott", "tiger" );
You can also use the rather obscure (that is, to a programmer) Net8 connect descriptor for the database portion of the URL. You may be familiar with connect descriptors because they are used in the tnsnames.ora file for an OCI client to define the specific address details for a net service name. Using a connect descriptor, our getConnection( ) example would look like:
Connection conn = getConnection(
"jdbc:oracle:thin:@(description=(address=(host=esales)
(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))",
"scott", "tiger" );