Compiling and Running an SQLJ Application
A compiled SQLJ application is a standard Java program that can run wherever a Java VM, the SQLJ runtime library and a JDBC driver are available. There are three important aspects to consider regarding executing SQLJ applications:

SQLJ Deployment
From a platform point of view, the only requirements for running an SQLJ program are the availability of:

Deployment Scenarios
SQLJ programs can be deployed in a number of different configurations, including:

Since the SQLJ runtime library is a thin layer of pure Java code that sits above the chosen JDBC driver, users must choose the JDBC driver best suited for their particular deployment configuration. Figures 3-6 illustrate how Oracle's SQLJ Translator can be used in combination with Oracle's own JDBC drives in four different deployment configurations.

SQLJ Features
The following is a look at SQLJ's most important features.

SQLJ Clauses
Static SQL statements appear in an SQLJ program text as SQLJ clauses. An SQLJ clause is introduced by the token "#sql", and contains an SQL statement inside curly braces. An executable SQLJ clause may appear wherever a Java statement may appear. Here is an example of an SQLJ clause that contains a SQL UPDATE statement:

#sql { UPDATE TAB SET COL1 = :x WHERE COL2 > :y AND COL3 <:z };

Host Variables
The inputs and outputs of SQL statements are passed through host variables. A host variable is a Java variable, parameter or field that's embedded in an SQL statement and prefixed by a color. The standard JDBC types, such as Boolean, byte, short, int, String, byte[], java.sql.Date, Integer, Double, etc., are valid host variable types in SQLJ. In addition, Oracle's SQLJ Translator supports Oracle7- and Oracle8i-specific types, such as ROWID, CLOB and BLOB, as well as Object and REF types.

Listing 1 consists of two SQL table definitions and a Java method containing SQLJ clauses that access those tables. It shows that SQLJ is quite similar to the ANSI/ISO-Embedded SQL and allows SQL statements to appear directly in program logic. At program development time, static analysis can detect errors in their SQL syntax, in their uses of tables and other schema definitions, and in their numbers and types of arguments and results.

Result Sets Returned by Queries
In an SQLJ program, a result set returned by a multirow query is manipulated by means of an iterator object, which iterates through the rows in the result set. An iterator is an object of an iterator class, which is a Java class defined by a declarative SQLJ clause that can appear wherever a class definition can appear. The clause defining a named iterator class lists the Java names and types for columns in a row of a result set. The following clause defines an iterator class called AllStock:

#sql iterator AllStock (String part, int quantity);

This clause implicitly defines the Java class AllStock with methods named part and quantity, of types String and int, respectively. Those column-accessor methods return the values of columns from rows of a result set contained in an iterator of type AllStock. The following SQLJ program fragment defines a local variable of class AllStock, executes a query to populate that variable with an iterator object, calls the column-accessor methods of the iterator and prints the column values. The code for this can be found in Listing 2.

In addition, SQLJ provides support for defining positioned iterators that use traditional FETCH...INTO syntax to access query columns by position.

Database Connection Management
Listing 2 contains no explicit management of database connections. Its SQL statements execute on the default database connection, which is global to the program. SQLJ programs may also manipulate multiple database connections. Users may explicitly declare a connection-context class wherever a Java class declaration is permitted.

Combining Static and Dynamic SQL - SQLJ and JDBC
An SQLJ program may contain both SQLJ clauses and JDBC calls, for static and dynamic SQL, respectively. The two paradigms interoperate at the level of database connections and result sets/iterators. For example, an SQLJ connection context can be initialized with an existing JDBC connection:

java.sql.connection conn = ...; PartsDb pdb = new PartsDB(conn);

It's also possible to extract a JDBC connection object from an SQLJ connection context. Similar conversions are possible between JDBC result sets and structured SQLJ iterators. For example:

AllStock iter;
#sql iter = { SELECT ... };
java.sqlj.ResultSet rs = iter.getResultSet();

Thus the dynamic SQL API for SQLJ is JDBC.

SQLJ Code Versus JDBC Code
For SQL statements with input arguments, SQLJ clauses are often shorter than the equivalent dynamic SQL (JDBC) calls. This is because SQLJ uses host variables to pass arguments to SQL statements, while JDBC requires a separate statement to bind each argument and to retrieve each result. Contrast SQLJ and JDBC program fragments for the same single-row SELECT statement can be found in Listing 3.

Unlike dynamic SQL, SQLJ permits compile-time checking of the SQL syntax:

Type- and schema-checking are also done where column data is fetched from an iterator object (by a FETCH statement or by column accessor methods). This is because the class of the iterator object defines the number and types of columns in rows contained by that iterator.

Using SQL Objects in SQLJ
Starting with Oracle8 (version 8.0), Oracle introduced support for abstract data types or objects. These objects are similar in nature to the traditional object-oriented classes or types. They have both attributes and methods associated with them. In Oracle8i, you can perform object manipulation through static SQL statements in SQLJ programs. Any kind of SQL data can be read and written by Java in a fully user-customizable fashion. Users can provide their own customized mapping from RAW columns in SQL to serialized Java objects. The same mechanism is then employed to create mappings from SQL object types to Java classes. This is done using the JPublisher tool, which assists in the generation of customized Java class definitions for these types.

SQLJ supports Oracle8 types through an Oracle-specific customization of the SQLJ runtime profile. This customization is performed automatically when you use the SQLJ translator provided with the Oracle8i database. In this process, runtime calls to standard JDBC entry points - such as getObject() and setObject() - are replaced with calls to Oracle's JDBC API.

We expect the SQLJ specification to evolve in the future and to encompass structured SQL3 types, such as those introduced in JDBC 2.0 and supported preliminarily in the Oracle8i JDBC drivers. Currently, support for Object Types can only be provided as a vendor-specific extension.

Oracle provides the JPublisher tool for automating much of the effort in creating the corresponding Java declarations for the Oracle8 object types and collections such as Object Types, REFs and Collection Types.

Java Stored Procedures
Java stored procedures are a part of the SQLJ standardization. Along with the embedded JDBC driver, the Oracle8i server also has an embedded SQLJ translator that allows application developers to write applications that access persistent data using SQLJ. Once you have written your Java program and tested it, you need to load it into Oracle8i - that is, onto the database's Java VM - and resolve all references. The database supports a variety of different forms in which Java programs can be loaded, including Java source text, standard Java .class files or Java archives (.jar). Java source loaded into the database is automatically compiled by the Java bytecode compiler hosted in the database. The Java programs loaded into the database are stored as "library units" in a database schema similar to how PL/SQL program units are stored in the database. Java library units needed to execute a Java program can be physically located in different database schemas.

Using SQLJ in Enterprise JavaBeans
The Oracle8i RDBMS is tightly integrated with the Oracle8i EJB server. This enables the Enterprise JavaBeans running in the database to use JDBC or SQLJ to access persistent data. Using Oracle8i session Beans, users can explicitly persist the state of their Bean and manage the Bean's persistent state via JDBC or SQLJ.

SQLJ in IDEs
SQLJ was designed so it could be embedded in Java IDEs. Oracle provides an SDK that enables tools and vendors to incorporate Oracle's SQLJ Translator into their Java tools. Oracle's premier Java Development tool, JDeveloper, was the first such tool to support SQLJ. JDeveloper has complete support for authoring and debugging SQLJ programs.

Summary of SQLJ's Benefits
SQLJ is a highly productive, open standard for embedded SQL in Java. It's supported by leading database vendors such as Oracle, IBM, Sybase and JavaSoft. SQLJ programs can be deployed in a number of different configurations, including two-tier client/server applications and three-tier intranet and extranet applications. They can write database stored procedures, triggers and methods with Oracle8i. Oracle's SQLJ Translator conforms to the SQLJ standard and provides support for a number of database features specific to Oracle. Oracle has achieved tight integration between SQLJ support in Oracle8i and JDeveloper for utmost speed and efficiency in generating bug-free code. The company has also given the user flexibility and choice from within the IDE to deploy the application in a variety of ways.

References
For further reading on the topics discussed above, please visit the Oracle Java home page at www.oracle.com/Java/