Opening a Database Connection

Before you can issue SQL statements in your Java programs, you must open a database connection. There are two main ways to open a database connection. The first way is to use the getConnection() method of the DriverManager class. The second way uses an Oracle data source object, which must first be created and then connected to. This method uses a standardized way of setting database connection details, and an Oracle data source object may be used with the Java Naming and Directory Interface (JNDI).

I ll describe both of these ways to open a database connection in the following sections, starting with the first way ”which uses the getConnection() method of the DriverManager class.

Connecting to the Database Using the getConnection() Method of the DriverManager Class

The getConnection() method accepts three parameters: a database username, a password, and a database URL. The getConnection() method returns a JDBC Connection object, which should be stored in your program so it may be referenced later. The syntax of a call to the getConnection() method is as follows :

 DriverManager.getConnection(  URL, username, password  ); 

where

• URL is the database that your program connects to, along with the JDBC driver you want to use. See the following section, The Database URL, for details on the URL.

• username is the name of the database user that your program connects as.

• password is the password for the username.

The following example shows the getConnection() method being used to connect to a database:

 Connection myConnection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL",  "store",  "store_password"); 

In this example, the connection is made to a database running on the machine identified as localhost with an Oracle System Identifier (SID) of ORCL , using the Oracle JDBC Thin driver. The connection is made with the username store and the password store_password . The Connection object returned by the call to getConnection() is stored in myConnection . I ll use the Oracle JDBC Thin driver for the program examples in this chapter. The connection to a database is made through Oracle Net, which should be up and running.

The Database URL

The database URL specifies where the database your program connects to is located. The structure of the database URL is dependent on the vendor who provides the JDBC drivers. In the case of Oracle s JDBC drivers, the database URL structure is as follows:

 driver_name:@driver_information 

where

• driver_name is the name of the Oracle JDBC driver that your program uses. This may be set to one of the following:

  • jdbc:oracle:thin    The Oracle JDBC Thin driver

  • jdbc:oracle:oci    The Oracle JDBC OCI driver

• driver_information    The driver-specific information required to connect to the database. This is dependent on the driver being used. In the case of the Oracle JDBC Thin driver, the driver-specific information may be specified in the following format:

  • host_name:port:database_SID    For the Oracle JDBC Thin driver

For all the Oracle JDBC drivers, including the Thin driver and the various OCI drivers, the driver-specific information may also be specified using Oracle Net keyword-value pairs, which may be specified in the following format:

 (description=(address=(host=  host_name  )(protocol=tcp)(port=  port  )) (connect_data=(sid=  database_SID  ))) 

where

• host_name is the name of the machine on which the database is running.

• port is the port number on which the Oracle Net database listener waits for requests ; 1521 is the default port number. Your DBA can provide the port number.

• database_SID is the Oracle SID of the database instance to which you want to connect. Your DBA can provide the database SID.

For the Oracle OCI driver, you may also use an Oracle Net TNSNAMES string (for more information on this, speak with your DBA or consult the Oracle documentation that describes Oracle Net).

The following example shows the getConnection() method being used to connect to a database using the Oracle OCI driver, with the driver-specific information specified using Oracle Net keyword-value pairs:

 Connection myConnection = DriverManager.getConnection("jdbc:oracle:oci:@(description=(address=(host=localhost)" +  "(protocol=tcp)(port=1521))(connect_data=(sid=ORCL)))",  "store",  "store_password"); 

As you can see, in this example a connection is made to a database running on the machine identified as localhost , with an Oracle SID of ORCL , using the Oracle OCI driver. The connection to the database is made with the username store , with a password of store_password . The Connection object returned by the call to getConnection() is stored in myConnection .

Connecting to the Database Using an Oracle Data Source

You can also use an Oracle data source to connect to a database. An Oracle data source uses a more standardized way of supplying the various parameters to connect to a database than the previous method that used the DriverManager.getConnection() method. In addition, an Oracle data source may also be registered with JNDI. Using JNDI with JDBC is very useful because it allows you to register, or bind , data sources, and then look up those data sources in your program without having to provide the exact database connection details. Thus, if the database connection details change, only the JNDI object must be changed.

Note  

You can learn about JNDI in my book Oracle9 i JDBC Programming (McGraw-Hill/Osborne, 2002).

There are three steps that must be performed to use an Oracle data source:

1. Create an Oracle data source object of the oracle.jdbc.poo.OracleDataSource class.

2. Set the Oracle data source object attributes using the set methods , which are defined in the class.

3. Connect to the database via the Oracle data source object using the getConnection() method.

The following sections describe these three steps.

Step 1: Create an Oracle Data Source Object    The first step is to create an Oracle data source object of the oracle.jdbc.pool.OracleDataSource class. The following example creates an OracleDataSource object named myDataSource (you may assume that the oracle.jdb c.pool.OracleDataSource class has been imported):

 OracleDataSource myDataSource = new OracleDataSource(); 

Once you have your OracleDataSource object, the second step is to set that object s attributes using the set methods.

Step 2: Set the Oracle Data Source Object Attributes    Before you can use your OracleDataSource object to connect to a database, you must set a number of attributes in that object to indicate the connection details using various set methods defined in the class. These details include items like the database name, the JDBC driver to use, and so on; each of these details has a corresponding attribute in an OracleDataSource object.

The oracle.jdbc.pool.OracleDataSource class actually implements the javax.sql.DataSource interface provided with JDBC. The javax.sql.DataSource interface defines a number of attributes, which are listed in Table 15-1. This table shows the name, description, and type of each attribute.

The oracle.jdbc.pool.OracleDataSource class provides an additional set of attributes, which are listed in Table 15-2.

You may use a number of methods to read from and write to each of the attributes listed in Tables 15-1 and 15-2. The methods that read from the attributes are known as get methods, and the methods that write to the attributes are known as set methods.

Table 15-1: DataSource Attributes

Attribute Name	Attribute Description	Attribute Type
databaseName	The database name (Oracle SID).	String
dataSourceName	The name of the underlying data source class.	String
description	Description of the data source.	String
networkProtocol	The network protocol to use to communicate with the database. This only applies to the Oracle JDBC OCI drivers, and defaults to tcp . For further details, see the Oracle Net documentation provided by Oracle Corporation.	String
password	The password for the supplied username.	String
portNumber	The port on which the Oracle Net listener waits for database connection requests. The default is 1521. For further details, see the Oracle Net documentation.	int
serverName	The database server machine name (TCP/IP address or DNS alias).	String
user	The database username.	String

Table 15-2: OracleDataSource Attributes

Attribute Name	Attribute Description	Attribute Type
driverType	The JDBC driver to use. If you are using the server-side internal driver, this is set to kprb , and the other settings for the attributes are ignored.	String
url	May be used to specify an Oracle database URL, which can be used as an alternative to setting the database location. See the section earlier on database URLs for details.	String
tnsEntryName	May be used to specify an Oracle Net TNSNAMES string, which can also be used to specify the database location when using the OCI drivers.	String

The set and get method names are easy to remember: take the attribute name, convert the first letter to uppercase, and add the word set or get to the beginning. For example, to set the database name (stored in the databaseName attribute), you use the setDatabaseName() method; to get the name of the database currently set, you use the getDatabaseName() method. There is one exception to this: there is no getPassword() method that you can call. This is for security reasons ”you don t want someone to be able to get your password programmatically!

Most of the attributes are Java String objects, so most of the set methods accept a single String parameter, and most of the get methods return a String . The exception to this is the portNumber attribute, which is an int . Therefore, its set method setPortNumber() accepts an int , and its get method getPortNumber() returns an int .

The following examples illustrate the use of the set methods to write to the attributes of the OracleDataSource object myDataSource that was created earlier in Step 1:

 myDataSource.setServerName("localhost"); myDataSource.setDatabaseName("ORCL"); myDataSource.setDriverType("oci"); myDataSource.setNetworkProtocol("tcp"); myDataSource.setPortNumber(1521); myDataSource.setUser("scott"); myDataSource.setPassword("tiger"); 

The next examples illustrate the use of some of the get methods to read the attributes previously set in myDataSource :

 String serverName = myDataSource.getServerName(); String databaseName = myDataSource.getDatabaseName(); String driverType = myDataSource.getDriverType(); String networkProtocol = myDataSource.getNetworkProtocol(); int portNumber = myDataSource.getPortNumber(); 

Once you ve set your OracleDataSource object attributes, you can use it to connect to the database.

Step 3: Connect to the Database via the Oracle Data Source Object    The third step is to connect to the database via the OracleDataSource object. You do this by calling the getConnection( ) method using your OracleDataSource object. The getConnection() method returns a JDBC Connection object, which must be stored.

The following example shows how to call the getConnection() method using the myDataSource object populated in the previous step:

 Connection myConnection = myDataSource.getConnection(); 

The Connection object returned by getConnection() is stored in myConnection . You can also pass a username and password as parameters to the getConnection() method, as shown in the following example:

 Connection myConnection = myDataSource.getConnection("store", "store_password"); 

In this case, the username and password will override the username and password previously set in myDataSource . Therefore, the connection to the database will be made using the username of store with a password of store_password , rather than scott and tiger , which were set in myDataSource in the previous section.

Once you have your Connection object, you can use it to create a JDBC Statement object.