Oracle8i JDBC Developer's Guide and Reference Release 3 (8.1.7) Part Number A83724-01 |
Library | Service | Contents | Index

## First Steps in JDBC

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:

1. Import Packages

2. Register the JDBC Drivers

3. Open a Connection to a Database

4. Create a Statement Object

5. Execute a Query and Return a Result Set Object

6. Process the Result Set

7. Close the Result Set and Statement Objects

8. Make Changes to the Database

9. Commit Changes

10. Close the Connection

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.

### Import Packages

Regardless of which Oracle JDBC driver you use, include the following `import` statements at the beginning of your program (`java.math` only if needed):

`import java.sql.*;` | for standard JDBC packages
`import java.math.*;` | for `BigDecimal` and `BigInteger` classes

Import the following Oracle packages 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:

`import oracle.jdbc.driver.*;` `import oracle.sql.*;` | for Oracle extensions to JDBC

For an overview of the Oracle extensions to the JDBC standard, see Chapter 6, "Overview of Oracle Extensions".

### Register the JDBC Drivers

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.

Note: Alternatively, you can use the `forName()` method of the `java.lang.Class` class to load the JDBC drivers directly. For example: `Class.forName ("oracle.jdbc.driver.OracleDriver");` However, this method is valid only for JDK-compliant Java virtual machines. It is not valid for Microsoft Java virtual machines.

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());

### Open a Connection to a Database

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 that needs as input a user name, 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:

- "Opening a Connection for the JDBC OCI Driver"

- "Opening a Connection for the JDBC Thin Driver"

  Notes: With JDK 1.2, using JNDI (Java Naming and Directory Interface) is becoming the recommended way to make connections. See "A Brief Overview of Oracle Data Source Support for JNDI" and "Creating a Data Source Instance, Registering with JNDI, and Connecting". If you are using the Thin driver, be aware that it does not support OS authentication in making the connection. As a result, special logins are not supported. This discussion in this section does not apply to the server-side internal driver, which uses an implicit connection. See "Connecting to the Database with the Server-Side Internal Driver".

#### Understanding the Forms of getConnection()

The `DriverManager` class `getConnection()` method whose signatures and functionality are described in the following sections:

- "Specifying a Database URL, User Name, and Password"

- "Specifying a Database URL That Includes User Name and Password"

- "Specifying a Database URL and Properties Object"

If you want to specify a database name in the connection, it must be in one of the following formats:

- a Net8 keyword-value pair

- a string of the form <host_name>:<port_number>:<sid> (Thin driver only)

- a `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.

#### Specifying a Database URL, User Name, and Password

The following signature takes the URL, user name, and password as separate parameters:

    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");

#### Specifying a Database URL That Includes User Name and Password

The following signature takes the URL, user name, and password all as part of a URL parameter:

    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 on host `myhost` 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);

If you want to connect with the Thin driver, you must specify the port number and SID. 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`:

    Connection conn = DriverManager.getConnection
                            ("jdbc:oracle:thin:scott/tiger@myhost:1521:orcl);

#### Specifying a Database URL and Properties Object

The following signature takes a URL, together with a properties object that specifies user name and password (perhaps among other things):

    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);

Table 3-1 lists the connection properties that Oracle JDBC drivers support.

##### Table 3-1 Connection Properties Recognized by Oracle JDBC Drivers

Name | Short Name | Type | Description
--- | --- | --- | ---
user | n/a | String | the user name for logging into the database
password | n/a | String | the password for logging into the database
database | server | String | the connect string for the database
internal_logon | n/a | String | a role, such as `sysdba` or `sysoper`, that allows you to log on as `sys`
defaultRowPrefetch | prefetch | String (containing integer value) | the default number of rows to prefetch from the server (default value is "10")
remarksReporting | remarks | String (containing boolean value) | "true" if `getTables()` and `getColumns()` should report `TABLE_REMARKS`; equivalent to using `setRemarksReporting()` (default value is "false")
defaultBatchValue | batchvalue | String (containing integer value) | the default batch value that triggers an execution request (default value is "10")
includeSynonyms | synonyms | String (containing boolean value) | "true" to include column information from predefined "synonym" SQL entities when you execute a `DataBaseMetaData` `getColumns()` call; equivalent to connection `setIncludeSynonyms()` call (default value is "false")

See Table 18-4, "OCI Driver Client Parameters for Encryption and Integrity" and Table 18-5, "Thin Driver Client Parameters for Encryption and Integrity" for descriptions of encryption and integrity drivers.

#### Using Roles for Sys Logon

To specify the role (mode) for `sys` logon, use the `internal_logon` connection property. (See Table 3-1, "Connection Properties Recognized by Oracle JDBC Drivers", for a complete description of this connection property.) To logon as `sys`, set the `internal_logon` connection property to `sysdba` or `sysoper`.

Note: The ability to specify a role is supported only for `sys` user name.

##### Example

The following example illustrates how to use the `internal_logon` and `sysdba` arguments to specify `sys` logon.

    //import packages and register the driver
    import java.sql.*;
    import java.math.*;
    DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());

    //specify the properties object
    java.util.Properties info = new java.util.Properties();
    info.put ("user", "sys");
    info.put ("password", "change_on_install");
    info.put ("internal_logon","sysdba");

    //specify the connection object
    Connection conn = DriverManager.getConnection
                            ("jdbc:oracle:thin:@database",info);
    ...

##### Properties for Oracle Performance Extensions

Some of these properties are for use with Oracle performance extensions. Setting these properties is equivalent to using corresponding methods on the `OracleConnection` object, as follows:

- Setting the `defaultRowPrefetch` property is equivalent to calling `setDefaultRowPrefetch()`.

  See "Oracle Row Prefetching".

- Setting the `remarksReporting` property is equivalent to calling `setRemarksReporting()`.

  See "DatabaseMetaData TABLE_REMARKS Reporting".

- Setting the `defaultBatchValue` property is equivalent to calling `setDefaultExecuteBatch()`.

  See "Oracle Update Batching".

##### Example

The following example shows how to use the `put()` method of the `java.util.Properties` class, in this case to set Oracle performance extension parameters.

    //import packages and register the driver
    import java.sql.*;
    import java.math.*;
    DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());

    //specify the properties object
    java.util.Properties info = new java.util.Properties();
    info.put ("user", "scott");
    info.put ("password", "tiger");
    info.put ("defaultRowProfetch","20");
    info.put ("defaultBatchValue", "5");

    //specify the connection object
    Connection conn = DriverManager.getConnection
                            ("jdbc:oracle:thin:@database",info);
    ...

#### Opening a Connection for the JDBC OCI Driver

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 the `[ORACLE_HOME]\NETWORK\ADMIN` directory. On UNIX systems, you can find it in the `/var/opt/oracle` directory.

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 and Thin drivers, 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");

Note: Oracle JDBC does not support login timeouts. Calling the static `DriverManager.setLoginTimeout()` method will have no effect.

#### Opening a Connection for the JDBC Thin Driver

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:

- Explicitly list the host name, TCP/IP port and Oracle SID of the database to which you want to connect.

or:

- Use a keyword-value pair list.

  Note: The JDBC Thin driver supports only the TCP/IP protocol.

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");

Notes: Oracle JDBC does not support login timeouts. Calling the static `DriverManager.setLoginTimeout()` method will have no effect.

### Create a Statement Object

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.

### Execute a Query and Return a Result Set Object

To query the database, use the `executeQuery()` method of your `Statement` object. This method takes a SQL statement as input and returns a JDBC `ResultSet` object.

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 named `EMP`:

    ResultSet rset = stmt.executeQuery ("SELECT ename FROM emp");

Again, there is nothing Oracle-specific about this statement; it follows standard JDBC syntax.

### Process the Result Set

Once you execute your query, use the `next()` method of your `ResultSet` object to iterate through the results. This method steps 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 appropriate `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.

For a complete sample application showing how to execute a query and print the results, see "Listing Names from the EMP Table--Employee.java".

### Close the Result Set and Statement Objects

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.

Note: Typically, you should put `close()` statements in a `finally` clause.

### Make Changes to the Database

To write changes to the database, such as for `INSERT` or `UPDATE` operations, you will typically create a `PreparedStatement` object. This allows you to execute a statement with varying sets of input parameters. The `prepareStatement()` method of your JDBC `Connection` object allows you to define a statement that takes variable bind parameters, and returns a JDBC `PreparedStatement` object with your statement definition.

Use `setXXX()` methods on the `PreparedStatement` object to bind data into the prepared statement to be sent to the database. The various `setXXX()` methods are described in "Standard setObject() and Oracle setOracleObject() Methods" and "Other setXXX() Methods".

Note that there is nothing Oracle-specific about the functionality described here; it follows standard JDBC syntax.

The following example shows how to use a prepared statement to execute `INSERT` operations that add two rows to the `EMP` table. For the complete sample application, see "Inserting Names into the EMP Table--InsertExample.java".

        // Prepare to insert new names in the EMP table
        PreparedStatement pstmt =
          conn.prepareStatement ("insert into EMP (EMPNO, ENAME) values (?, ?)");

        // Add LESLIE as employee number 1500
        pstmt.setInt (1, 1500);          // The first ? is for EMPNO
        pstmt.setString (2, "LESLIE");   // The second ? is for ENAME
        // Do the insertion
        pstmt.execute ();

        // Add MARSHA as employee number 507
        pstmt.setInt (1, 507);           // The first ? is for EMPNO
        pstmt.setString (2, "MARSHA");   // The second ? is for ENAME
        // Do the insertion
        pstmt.execute ();

        // Close the statement
        pstmt.close();

### Commit Changes

By default, DML operations (`INSERT`, `UPDATE`, `DELETE`) are committed automatically as soon as they are executed. This is known as auto-commit mode. You can, however, disable auto-commit mode with the following method call on the `Connection` object:

    conn.setAutoCommit(false);

(For further discussion of auto-commit mode and an example of disabling it, see "Disabling Auto-Commit Mode".)

If you disable auto-commit mode, then you must manually commit or roll back changes with the appropriate method call on the `Connection` object:

    conn.commit();

or:

    conn.rollback();

A `COMMIT` or `ROLLBACK` operation affects all DML statements executed since the last `COMMIT` or `ROLLBACK`.

Important: If auto-commit mode is disabled and you close the connection without explicitly committing or rolling back your last changes, then an implicit `COMMIT` operation is executed. Any DDL operation, such as `CREATE` or `ALTER`, always includes an implicit `COMMIT`. If auto-commit mode is disabled, this implicit `COMMIT` will not only commit the DDL statement, but also any pending DML operations that had not yet been explicitly committed or rolled back.

### Close the Connection

You must close your connection to the database once you finish your work. Use the `close()` method of the `Connection` object to do this:

    conn.close();

Note: Typically, you should put `close()` statements in a `finally` clause.

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index