Oracle8i JDBC Developer's Guide and Reference Release 8.1.5 A64685-01 |
|
This section describes how to get up and running with the Oracle JDBC drivers. When using the Oracle JDBC drivers, you must include certain driver-specific information in your programs. This section describes, in the form of a tutorial, where and how to add the information. The tutorial guides you through creating code to connect to and query a database from the client.
To connect to and query a database from the client, you must provide code for these tasks:
You must supply Oracle driver-specific information for the first three tasks, which allow your program to use the JDBC API to access a database. For the other tasks, you can use standard JDBC Java code as you would for any Java application.
Regardless of which Oracle JDBC driver you use, you must include the following import
statements at the beginning of your program.
|
JDBC packages. |
|
Java math packages; for example, these are required for the |
You will need to add the following Oracle packages to your program when you want to access the extended functionality provided by the Oracle drivers. However, they are not required for the example presented in this section:
oracle.jdbc.driver.* and oracle.sql.* |
Add these packages if you use any Oracle-specific extensions to JDBC in your program. For more information on Oracle extensions, see Chapter 4, "Oracle Extensions". |
You must provide the code to register your installed driver with your program. You do this with the static registerDriver()
method of the JDBC DriverManager
class. This class provides a basic service for managing a set of JDBC drivers.
Because you are using one of Oracle's JDBC drivers, you declare a specific driver name string to registerDriver()
. You register the driver only once in your Java application.
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
Note: If you are registering a Thin driver in an applet, you must enter a driver string that is different from the one used in these examples. For more information on registering a Thin driver for an applet, see "Coding Applets". |
You open a connection to the database with the static getConnection()
method of the JDBC DriverManager
class. This method returns an object of the JDBC Connection
class which needs as input a userid, password, connect string that identifies the JDBC driver to use, and the name of the database to which you want to connect.
Connecting to a database is a step where you must enter Oracle JDBC driver-specific information in the getConnection()
method. If you are not familiar with this method, continue reading the "Understanding the Forms of getConnection()" section below.
If you are already familiar with the getConnection()
method, you can skip ahead to either of these sections, depending on the driver you installed:
The instructions in this section are specific to the client-side drivers only. To find out how to open a database connection using the server-side driver, see "Server-Side Basics".
Note:
The getConnection()
method is an overloaded method that you declare by the techniques described in these sections:
You do not have to specify the database name if there is a default connection. For more information about default connections, see "Connecting to the Database with the Server Driver".
Note:
If you want to specify a database name in the connection, it must be in one of the following formats:
TNSNAMES
entry (OCI driver only)
For information on how to specify a keyword-value pair or a TNSNAMES
entry, see your Net8 Administrator's Guide.
getConnection(String URL, String user, String password);
where the URL is of the form:
jdbc:oracle:<drivertype
>:@<database
>
The following example connects user scott
with password tiger
to a database with SID orcl
through port 1521 of host myhost
, using the Thin driver.
Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@myhost:1521:orcl", "scott", "tiger");
If you want to use the default connection for an OCI driver, specify either:
Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:scott/tiger@");
OR
Connection conn =DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");
For all JDBC drivers you can also specify the database with a Net8 keyword-value pair. The Net8 keyword-value pair substitutes for the TNSNAMES
entry. The following example uses the same parameters as the preceding example, but in the keyword-value format:
Connection conn =DriverManager.getConnection (jdbc:oracle:oci8:@MyHostString","scott","tiger");
OR
Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:@(description=(address=(host= myhost)(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))", "scott", "tiger");
getConnection(String URL);
where the URL is of the form:
jdbc:oracle:<drivertype
>:<user
>/<password
>@<database
>
The following example connects user scott
with password tiger
to a database using the OCI driver. In this case, however, the URL includes the userid and password, and is the only input parameter.
Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:scott/tiger@myhost);
getConnection(String URL, Properties info);
where the URL
is of the form:
jdbc:oracle:<drivertype
>:@<database
>
In addition to the URL, use an object of the standard Java Properties
class as input. For example:
java.util.Properties info = new java.util.Properties(); info.put ("user", "scott"); info.put ("password"
,"tiger"
); info.put ("defaultRowPrefetch","15"); getConnection ("jdbc:oracle:oci8:@",info);
Oracle has defined several extensions to the connection properties that Oracle JDBC drivers support. For more information on this form of the getConnection()
method and the Oracle extensions to the Properties
object, see "Oracle Extensions for Connection Properties".
For the JDBC OCI driver, you can specify the database by a TNSNAMES
entry. You can find the available TNSNAMES
entries listed in the file tnsnames.ora
on the client computer from which you are connecting. On Windows NT this file is located in [ORACLE_HOME]\NETWORK\ADMIN
. On UNIX systems, you can find it in /var/opt/oracle
.
For example, if you want to connect to the database on host myhost
as user scott
with password tiger
that has a TNSNAMES
entry of MyHostString
, enter:
Connection conn =DriverManager.getConnection ("jdbc:oracle:oci8:@MyHostString","scott", "tiger");
Note that both the ":
" and "@
" characters are necessary.
For the JDBC OCI driver (as with the Thin driver), you can also specify the database with a Net8 keyword-value pair. This is less readable than a TNSNAMES
entry but does not depend on the accuracy of the TNSNAMES.ORA
file. The Net8 keyword-value pair also works with other JDBC drivers.
For example, if you want to connect to the database on host myhost
that has a TCP/IP listener up on port 1521, and the SID
(system identifier) is orcl
, use a statement such as:
Connection conn =DriverManager.getConnection("jdbc:oracle:oci8:@(description=(address=(host= myhost)(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))","scott", "tiger");
Because you can use the JDBC Thin driver in applets that do not depend on an Oracle client installation, you cannot use a TNSNAMES
entry to identify the database to which you want to connect. You have to either:
OR
For example, use this string if you want to connect to the database on host myhost
that has a TCP/IP listener on port 1521 for the database SID
(system identifier) orcl
. You can logon as user scott
, with password tiger
:
Connection conn =DriverManager.getConnection ("jdbc:oracle:thin:@myhost:1521:orcl", "scott", "tiger");
You can also specify the database with a Net8 keyword-value pair. This is less readable than the first version, but also works with the other JDBC drivers.
Connection conn =DriverManager.getConnection("jdbc:oracle:thin:@(description=(address=(host=myhost)(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))", "scott", "tiger");
Note:If you are writing a connection statement for an applet, you must enter a connect string that is different from the one used in these examples. For more information on connecting to a database with an applet, see "Coding Applets".
Once you connect to the database and, in the process, create your Connection
object, the next step is to create a Statement
object. The createStatement()
method of your JDBC Connection
object returns an object of the JDBC Statement
class. To continue the example from the previous section where the Connection
object conn
was created, here is an example of how to create the Statement
object:
Statement stmt = conn.createStatement();
Note that there is nothing Oracle-specific about this statement; it follows standard JDBC syntax.
To query the database, use the executeQuery()
method of your Statement
object. This method takes a SQL statement as input and returns an object of the JDBC ResultSet
class.
To continue the example, once you create the Statement
object stmt
, the next step is to execute a query that populates a ResultSet
object with the contents of the ENAME
(employee name) column of a table of employees that is named EMP
:
ResultSet rset = stmt.executeQuery ("SELECT ename FROM emp");
Again, there is nothing Oracle-specific about this statement; it follows standard JDBC syntax.
Note:
The JDBC drivers actually return an |
Once you execute your query, use the next()
method of your ResultSet
object to iterate through the results. This method loops through the result set row by row, detecting the end of the result set when it is reached.
To pull data out of the result set as you iterate through it, use the various getXXX()
methods of the ResultSet
object, where XXX
corresponds to a Java datatype.
For example, the following code will iterate through the ResultSet
object rset
from the previous section, and will retrieve and print each employee name:
while (rset.next())System.out.println (rset.getString(1));
Once again, this is standard JDBC syntax. The next()
method returns false when it reaches the end of the result set. The employee names are materialized as Java Strings.
You must explicitly close the ResultSet
and Statement
objects after you finish using them. This applies to all ResultSet
and Statement
objects you create when using the Oracle JDBC drivers. The drivers do not have finalizer methods; cleanup routines are performed by the close()
method of the ResultSet
and Statement
classes. If you do not explicitly close your ResultSet
and Statement
objects, serious memory leaks could occur. You could also run out of cursors in the database. Closing a result set or statement releases the corresponding cursor in the database.
For example, if your ResultSet
object is rset
and your Statement
object is stmt
, close the result set and statement with these lines:
rset.close()
stmt.close();
When you close a Statement
object that a given Connection
object creates, the connection itself remains open.
You must close your connection to the database once you finish your work. Use the close()
method of the Connection
class to do this. For example, if your Connection
object is conn
, close the connection with this statement:
conn.close();