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 of 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, username, and password from the file connect.properties. 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.)

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 ename;
   
        // Use SELECT INTO statement to execute query and retrieve values.
         #sql { SELECT Ename, Sal INTO :ename, :salary FROM Emp
                WHERE Empno = 7499 };
        
        // Print the results--
        System.out.println("Name is " + ename + ", and Salary is " + salary);
     }
   }    // Closing brace of SimpleExample class
   
   

This 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 ename 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);
    }
  }

  void runExample() throws SQLException {
     
     System.out.println( "Running the example--" );
     
     // Declare two Java host variables--
     Float salary;
     String ename;

     // Use SELECT INTO statement to execute query and retrieve values.
        #sql { SELECT Ename, Sal INTO :ename, :salary FROM Emp
              WHERE Empno = 7499 };
     
     // Print the results--
     System.out.println("Name is " + ename + ", and Salary is " + salary);
  }
}

Set Up 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, Integer is required.)
      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 );
    }
  }

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

---