Overview of SQLJ Declarations

A SQLJ declaration consists of the #sql token followed by the declaration of a class. SQLJ declarations introduce specialized Java types into your application. There are currently two kinds of SQLJ declarations, iterator declarations and connection context declarations, defining Java classes as follows:

• Iterator declarations define iterator classes. Iterators are conceptually similar to JDBC result sets and are used to receive multi-row query data. An iterator is implemented as an instance of an iterator class.

• Connection context declarations define connection context classes. Each connection context class is typically used for connections whose operations use a particular set of SQL entities (tables, views, stored procedures, and so on). That is to say, instances of a particular connection context class are used to connect to schemas that include SQL entities with the same names and characteristics. SQLJ implements each database connection as an instance of a connection context class.

In any iterator or connection context declaration, you may optionally include the following clauses:

• implements clause--Specifies one or more interfaces that the generated class will implement.

• with clause--Specifies one or more initialized constants to be included in the generated class.

These are described in "Declaration IMPLEMENTS Clause" and in "Declaration WITH Clause".

Rules for SQLJ Declarations

SQLJ declarations are allowed in your SQLJ source code anywhere that a class definition would be allowed in standard Java. The only limitation is that you cannot have a declaration inside a method block under JDK 1.1.x. For example:

SQLJ declaration;   // OK (top level scope)

class Outer
{
   SQLJ declaration; // OK (class level scope)

   class Inner
   {
      SQLJ declaration; // OK (nested class scope)
   }

   void func()
   {
      SQLJ declaration; // OK in JDK 1.2.x; ILLEGAL in JDK 1.1.x (method block)
   }
}

Iterator Declarations

An iterator declaration creates a class that defines a kind of iterator for receiving query data. The declaration will specify the column types of the iterator instances, which must match the column types being selected from the database table.

Basic iterator declarations use the following syntax:

#sql <modifiers> iterator iterator_classname (type declarations);

Modifiers are optional and can be any standard Java class modifiers such as public, static, etc. Type declarations are separated by commas.

There are two categories of iterators--named iterators and positional iterators. For named iterators, you specify column names and types; for positional iterators, you specify only types.

The following is an example of a named iterator declaration:

#sql public iterator EmpIter (String ename, double sal);

This statement results in the SQLJ translator creating a public EmpIter class with a String attribute ename and a double attribute sal. You can use this iterator to select data from a database table with corresponding employee name and salary columns of matching names (ENAME and SAL) and datatypes (CHAR and NUMBER).

Declaring EmpIter as a positional iterator, instead of a named iterator, would be done as follows:

#sql public iterator EmpIter (String, double);

For more information about iterators, see "Multi-Row Query Results--SQLJ Iterators".

Connection Context Declarations

A connection context declaration creates a connection context class, whose instances are typically used for database connections that use a particular set of SQL entities.

Basic connection context declarations use the following syntax:

#sql <modifiers> context context_classname;

As for iterator declarations, modifiers are optional and can be any standard Java class modifiers. The following is an example:

#sql public context MyContext;

As a result of this statement, the SQLJ translator creates a public MyContext class. In your SQLJ code you can use instances of this class to create database connections to schemas that include a desired set of entities, such as tables, views, and stored procedures. Different instances of MyContext might be used to connect to different schemas, but each schema might be expected, for example, to include an EMP table, a DEPT table, and a TRANSFER_EMPLOYEE stored procedure.

Declared connection context classes are an advanced topic and are not necessary for basic SQLJ applications that use only one interrelated set of SQL entities. In more basic scenarios, you can use multiple connections by creating multiple instances of the sqlj.runtime.ref.DefaultContext class, which does not require any connection context declarations.

See "Connection Considerations" for an overview of connections and connection contexts.

For information about creating additional connection contexts, see "Connection Contexts".

Declaration IMPLEMENTS Clause

When you declare any iterator class or connection context class, you can specify one or more interfaces to be implemented by the generated class. This is an advanced topic, however, and is probably not of interest to most developers.

Use the following syntax for an iterator class:

#sql <modifiers> iterator iterator_classname implements intfc1,..., intfcN 
     (type declarations);

The portion implements intfc1,..., intfcN is known as the implements clause. Note that in an iterator declaration, the implements clause precedes the iterator type declarations.

Here is the syntax for a connection context declaration:

#sql <modifiers> context context_classname implements intfc1,..., intfcN;

The implements clause is potentially useful in either an iterator declaration or a connection context declaration, but is more likely to be useful in iterator declarations--particularly in implementing sqlj.runtime.Scrollable or sqlj.runtime.ForUpdate. Scrollable iterators are supported in Oracle SQLJ; positioned updates or deletes are not currently supported.

For more information about the implements clause, see "Use of the IMPLEMENTS Clause in Iterator Declarations" and "Use of the IMPLEMENTS Clause in Connection Context Declarations".

The following example uses an implements clause in declaring a named iterator class (presume you have created a package, mypackage, that includes an iterator interface, MyIterIntfc).

#sql public iterator MyIter implements mypackage.MyIterIntfc 
     (String ename, int empno);

The declared class MyIter will implement the mypackage.MyIterIntfc interface.

This next example declares a connection context class that implements an interface named MyConnCtxtIntfc (presume it, too, is in the package mypackage).

#sql public context MyContext implements mypackage.MyConnCtxtIntfc; 

Declaration WITH Clause

In declaring any iterator class or connection context class, you can specify and initialize one or more constants to be included in the definition of the generated class. The constants that are produced are always public static final. Use the following syntax for an iterator class:

#sql <modifiers> iterator iterator_classname with (var1=value1,..., varN=valueN)
     (type declarations);

The portion with (var1=value1,..., varN=valueN) is known as the with clause. Note that in an iterator declaration, the with clause precedes the iterator type declarations.

Where there is both a with clause and an implements clause, the implements clause must come first. Note that parentheses are used to enclose with lists, but not implements lists.

Here is the syntax for a connection context declaration:

#sql <modifiers> context context_classname with (var1=value1,..., varN=valueN);

The following example uses a with clause in declaring a named iterator.

#sql public context MyContext with (typeMap="MyPack.MyClass");

The declared class MyContext will define the attribute typeMap that will be public static final of the type String and initialized to the value "MyPack.MyClass". This value is the fully qualified class name of a ListResourceBundle implementation that provides the mapping between SQL and Java types for statements executed on instances of the MyContext class.

Here is another example (see below for the note about sensitivity):

#sql public iterator MyAsensitiveIter with (sensitivity=ASENSITIVE) 
     (String ename, int empno);

This declaration sets the cursor sensitivity to ASENSITIVE for a named iterator class (but sensitivity is not supported in the Oracle8i database).

The following example uses both an implements clause and a with clause (see the following note about holdability).

#sql public context MyContext implements sqlj.runtime.Scrollable
     with (holdability=true) (String ename, int empno);

The implements clause must precede the with clause.

This declaration implements the interface sqlj.runtime.Scrollable and enables the cursor holdability for a named iterator class (but holdability, as with sensitivity,is not currently meaningful to an Oracle8i database).

The following standard constants on iterator declarations are not supported in Oracle SQLJ. They mostly involve cursor states and can take only particular values, as follows:

• sensitivity (SENSITIVE/ASENSITIVE/INSENSITIVE)

• holdability (true/false)

• returnability (true/false)

• updateColumns (a String literal containing a comma-separated list of column names)

An iterator declaration with a with clause that specifies updateColumns must also have an implements clause that specifies the sqlj.runtime.ForUpdate interface.

Oracle SQLJ supports the following standard constants on connection context declarations.

• typeMap(a String literal defining the name of a type map properties resource)

• dataSource(a String literal defining the name under which a DataSource is looked up in the InitialContext)

The following standard constants on connection context declarations are not supported in Oracle SQLJ.

• path (a String literal defining the name of a type map properties resource)

• transformGroup (a String literal defining the name of a SQL transformation group that may be applied to SQL types)

---