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

## Summary: First Steps in SQLJ Code

The best way to summarize the SQLJ executable statement features and functionality discussed to this point is by examining short but complete programs. This section presents two such examples.

The first example, presented one step at a time and then again in its entirety, uses a `SELECT INTO` statement to perform a single-row query of two columns from a table of employees. If you want to run the example, make sure to change the parameters in the `connect.properties` file to settings that will let you connect to an appropriate database.

The second example, slightly more complicated, will make use of a SQLJ iterator for a multi-row query.

### Import Required Classes

Import any JDBC or SQLJ packages you will need.

You will need at least some of the classes in the `java.sql` package:

    import java.sql.*;

You may not need all the `java.sql` package, however. Key classes there are `java.sql.SQLException` and any classes that you refer to explicitly (for example, `java.sql.Date`, `java.sql.ResultSet`).

You will need the following package for the `Oracle` class, which you typically use to instantiate `DefaultContext` objects and establish your default connection:

    import oracle.sqlj.runtime.*;

If you will be using any SQLJ runtime classes directly in your code, import the following packages:

    import sqlj.runtime.*;
    import sqlj.runtime.ref.*;

If your code does not use any SQLJ runtime classes directly, however, it will be sufficient to have them in your `CLASSPATH` as described in "Set the PATH and CLASSPATH".

(Key runtime classes include `AsciiStream`, `BinaryStream`, and `ResultSetIterator` in the `sqlj.runtime` package, and `DefaultContext` in the `sqlj.runtime.ref` package.)

### Register JDBC Drivers and Set Default Connection

Declare the `SimpleExample` class with a constructor that uses the static `Oracle.connect()` method to set the default connection. This also registers the Oracle JDBC drivers. If you are using a non-Oracle JDBC driver, you must add code to register it (as mentioned in the code comments below).

This uses a signature of `connect()` that takes the URL, user name, and password from the `connect.properties` file. An example of this file is in the directory `[Oracle Home]/sqlj/demo` and also in "Set Up the Runtime Connection".

    public class SimpleExample {

      public SimpleExample() throws SQLException {
        /* If you are using a non-Oracle JDBC driver, add a call here to
           DriverManager.registerDriver() to register your driver.  */
        // Set default connection (as defined in connect.properties).
        Oracle.connect(getClass(), "connect.properties");
      }

(The `main()` method is defined below.)

### Set Up Exception Handling

Create a `main()` that calls the `SimpleExample` constructor and then sets up a `try/catch` block to handle any SQL exceptions thrown by the `runExample()` method (which performs the real work of this application):

    ...
    public static void main (String [] args) {

       try {
          SimpleExample o1 = new SimpleExample();
          o1.runExample();
       }
       catch (SQLException ex) {
          System.err.println("Error running the example: " + ex);
       }
    }
    ...

(The `runExample()` method is defined below.)

You can also use a `try/catch` block inside a `finally` clause when you close the connection (presuming the `finally` clause is not already inside a `try/catch` block):

    finally
    {
       try { Oracle.close(); } catch(SQLException ex) {...}
    }

### Set Up Host Variables, Execute SQLJ Clause, Process Results

Create a `runExample()` method that performs the following:

1. Throws any SQL exceptions to the `main()` method for processing.

2. Declares Java host variables.

3. Executes a SQLJ clause that binds the Java host variables into an embedded `SELECT` statement and selects the data into the host variables.

4. Prints the results.

       void runExample() throws SQLException {

            System.out.println( "Running the example--" );

            // Declare two Java host variables--
            Float salary;
            String empname;

            // Use SELECT INTO statement to execute query and retrieve values.
             #sql { SELECT ename, sal INTO :empname, :salary FROM emp
                    WHERE empno = 7499 };

            // Print the results--
            System.out.println("Name is " + empname + ", and Salary is " + salary);
         }
       }    // Closing brace of SimpleExample class

This example declares `salary` and `ename` as Java host variables. The SQLJ clause then selects data from the `ENAME` and `SAL` columns of the `EMP` table and places the data into the host variables. Finally, the values of `salary` and `empname` are printed out.

Note that this `SELECT` statement could select only one row of the `EMP` table, because the `EMPNO` column in the `WHERE` clause is the primary key of the table.

### Example of Single-Row Query using SELECT INTO

This section presents the entire `SimpleExample` class from the previous step-by-step sections. Because this is a single-row query, no iterator is required.

    // Import SQLJ classes:
    import sqlj.runtime.*;
    import sqlj.runtime.ref.*;
    import oracle.sqlj.runtime.*;

    // Import standard java.sql package:
    import java.sql.*;

    public class SimpleExample {

      public SimpleExample() throws SQLException {
        /* If you are using a non-Oracle JDBC driver, add a call here to
           DriverManager.registerDriver() to register your driver.  */
        // Set default connection (as defined in connect.properties).
        Oracle.connect(getClass(), "connect.properties");
      }

      public static void main (String [] args) throws SQLException {

        try {
          SimpleExample o1 = new SimpleExample();
          o1.runExample();
        }
        catch (SQLException ex) {
          System.err.println("Error running the example: " + ex);
        }
      }

      finally
      {
         try { Oracle.close(); } catch(SQLException ex) {...}
      }

      void runExample() throws SQLException {

         System.out.println( "Running the example--" );

         // Declare two Java host variables--
         Float salary;
         String empname;

         // Use SELECT INTO statement to execute query and retrieve values.
            #sql { SELECT ename, sal INTO :empname, :salary FROM emp
                  WHERE empno = 7499 };

         // Print the results--
         System.out.println("Name is " + empname + ", and Salary is " + salary);
      }
    }

### Set Up a Named Iterator

The next example will build on the previous example by adding a named iterator and using it for a multiple-row query.

First, declare the iterator class. Use object types `Integer` and `Float`, instead of primitive types `int` and `float`, wherever there is the possibility of null values.

    #sql iterator EmpRecs(
          int empno,       // This column cannot be null, so int is OK.
                           // (If null is possible, use Integer.)
          String ename,
          String job,
          Integer mgr,
          Date hiredate,
          Float sal,
          Float comm,
          int deptno);

Later, when needed, instantiate the `EmpRecs` class and populate it with query results.

    EmpRecs employees;

    #sql employees = { SELECT empno, ename, job, mgr, hiredate,
                       sal, comm, deptno FROM emp };

Then use the `next()` method of the iterator to print the results.

        while (employees.next())  {
          System.out.println( "Name:       " + employees.ename() );
          System.out.println( "EMPNO:      " + employees.empno() );
          System.out.println( "Job:        " + employees.job() );
          System.out.println( "Manager:    " + employees.mgr() );
          System.out.println( "Date hired: " + employees.hiredate() );
          System.out.println( "Salary:     " + employees.sal() );
          System.out.println( "Commission: " + employees.comm() );
          System.out.println( "Department: " + employees.deptno() );
          System.out.println();
        }

Finally, close the iterator when you are done.

    employees.close();

### Example of Multiple-Row Query Using Named Iterator

This example uses a named iterator for a multiple-row query that selects several columns of data from a table of employees.

Aside from use of the named iterator, this example is conceptually similar to the previous single-row query example.

    // Import SQLJ classes:
    import sqlj.runtime.*;
    import sqlj.runtime.ref.*;
    import oracle.sqlj.runtime.*;

    // Import standard java.sql package:
    import java.sql.*;

    // Declare a SQLJ iterator.
    // Use object types (Integer, Float) for mgr, sal, And comm rather
    // than primitive types to allow for possible null selection.

    #sql iterator EmpRecs(
          int empno,       // This column cannot be null, so int is OK.
                           // (If null is possible, Integer is required.)
          String ename,
          String job,
          Integer mgr,
          Date hiredate,
          Float sal,
          Float comm,
          int deptno);

    // This is the application class.
    public class EmpDemo1App {

       public EmpDemo1App() throws SQLException {
          /* If you are using a non-Oracle JDBC driver, add a call here to
             DriverManager.registerDriver() to register your driver.  */
          // Set default connection (as defined in connect.properties).
          Oracle.connect(getClass(), "connect.properties");
       }

      public static void main(String[] args) {

        try {
          EmpDemo1App app = new EmpDemo1App();
          app.runExample();
        }
        catch( SQLException exception ) {
          System.err.println( "Error running the example: " + exception );
        }
      }

      finally
      {
         try { Oracle.close(); } catch(SQLException ex) {...}
      }

      void runExample() throws SQLException  {
        System.out.println("\nRunning the example.\n" );

        // The query creates a new instance of the iterator and stores it in
        // the variable 'employees' of type 'EmpRecs'.  SQLJ translator has
        // automatically declared the iterator so that it has methods for
        // accessing the rows and columns of the result set.

        EmpRecs employees;

        #sql employees = { SELECT empno, ename, job, mgr, hiredate,
                           sal, comm, deptno FROM emp };

        // Print the result using the iterator.

        // Note how the next row is accessed using method 'next()', and how
        // the columns can be accessed with methods that are named after the
        // actual database column names.

        while (employees.next())  {
          System.out.println( "Name:       " + employees.ename() );
          System.out.println( "EMPNO:      " + employees.empno() );
          System.out.println( "Job:        " + employees.job() );
          System.out.println( "Manager:    " + employees.mgr() );
          System.out.println( "Date hired: " + employees.hiredate() );
          System.out.println( "Salary:     " + employees.sal() );
          System.out.println( "Commission: " + employees.comm() );
          System.out.println( "Department: " + employees.deptno() );
          System.out.println();
        }

        // You must close the iterator when it's no longer needed.
        employees.close() ;
      }
    }

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