3.2 Database Connections
The easiest way to connect to a database is to use the connect( ) method defined in the class oracle.sqlj.runtime.Oracle. Recall from Chapter 1 that this is one of the classes that you must import into your SQLJ program.
3.2.1 The connect( ) Method
The connect( ) method accepts three parameters: a database username, a password, and a database URL. The syntax of a call to the connect( ) method is:
Oracle.connect(URL, username, password);
The syntax elements are as follows:
- URL
-
Identifies the database that you want to connect to, along with the driver you wish to use to make the connection. See Section 3.2.2,for details on this parameter.
- username
-
Specifies your database username.
- password
-
Specifies the password for your username.
This example shows the connect( ) method being used to connect to a database:
Oracle.connect( "jdbc:oracle:thin:@localhost:1521:orcl", "fundamental_user", "fundamental_password" );
In all cases, the connection to a database is made through Oracle Net8 (or higher).
3.2.2 Database URL Structure
The database URL tells SQLJ where your database is located. The structure of the database URL is dependent on the brand of JDBC driver being used. In the case of Oracle's JDBC drivers, the URL structure is:
driver_name:@driver_specific_information
The syntax elements are as follows:
- driver_name
-
Specifies the name of the Oracle JDBC driver that you want to use. This may be any one of the following:
- jdbc:oracle:thin
-
Oracle JDBC Thin driver (for Oracle7 and higher)
- jdbc:oracle:oci
-
Oracle JDBC OCI driver (for Oracle9i and higher)
- jdbc:oracle:oci8
-
Oracle JDBC OCI8 driver (for Oracle8i and Oracle8)
- jdbc:oracle:oci7
-
Oracle JDBC OCI7 driver (for Oracle7)
- driver_specific_information
-
Specifies any 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 all the Oracle JDBC drivers including the various OCI drivers the driver-specific information may be specified using an Oracle Net8 (or higher) keyword-value pair, which you may specify using the following format:
(description=(address=(host=host_name)(protocol=tcp)(port=port)) (connect_data=(sid=database_SID)))
The keyword-value elements are as follows:
- host_name
-
Specifies the name of the machine on which the database is running.
- port
-
Specifies the port number on which the Net8 database listener waits for requests. 1521 is the default port number. Check with your DBA to ensure that this is the correct value in your environment.
- database_SID
-
Specifies the system identifier (SID) of the database instance to which you want to connect. Your DBA will be able to provide you with the correct database SID to use.
You may also use an Oracle Net8 (or higher) TNSNAMES string. For more information on this, speak with your DBA or consult the Oracle documentation.
The following example shows the connect( ) method being used to connect to a database using the Oracle OCI8 driver:
Oracle.connect( "jdbc:oracle:oci8:@(description=(address=(host=localhost)" + "(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))", "scott", "tiger" );
The Oracle JDBC Thin driver has the least amount of system resource requirements and is generally used in lightweight, client-based programs such as Java applets. The Oracle JDBC Thin driver may be used to access Oracle7 and higher databases.
The various Oracle JDBC OCI drivers require more system resources than the Thin driver, but they are faster and more suitable for middle-tier programs. The OCI driver is used to access Oracle9i and higher databases. The OCI8 driver is used for accessing Oracle8i and Oracle8 databases. The OCI7 driver is used for accessing Oracle7 databases.
| When deciding which driver to use, think about where the SQLJ program will reside and pick the appropriate driver for that tier. |
|
Once you've made a connection to the database using the connect( ) method, you may execute SQLJ statements that contain embedded SQL operations.