HomeDigital EditionSys-Con RadioSearch Java Cd
Advanced Java AWT Book Reviews/Excerpts Client Server Corba Editorials Embedded Java Enterprise Java IDE's Industry Watch Integration Interviews Java Applet Java & Databases Java & Web Services Java Fundamentals Java Native Interface Java Servlets Java Beans J2ME Libraries .NET Object Orientation Observations/IMHO Product Reviews Scalability & Performance Security Server Side Source Code Straight Talking Swing Threads Using Java with others Wireless XML
 

Oracle's development team is excited about the news that SQLJ has achieved standard status (ANSI X3.135.10-1998).

"It will undoubtedly be a catalyst in the adoption of Java by enterprise application developers. SQLJ not only allows SQL to be incorporated into Java programs in a standard way, it paves the way for database and Java tools vendors to bring Java closer to the enterprise environment. Oracle has been a firm supporter of SQLJ right from the early days when it cofounded the SQLJ consortium which submitted the SQLJ proposal to ANSI."

This Java Developer's Journal feature article will introduce you to the nuts and bolts of SQLJ with a look at Oracle's implementation and full support for this technology.

What Is SQLJ?
SQLJ provides a standard to embed SQL statements in Java programs. When writing an SQLJ application, a user writes a Java program and embeds SQL statements in it, following certain standard syntactic rules that govern how they can be embedded in Java programs. The user then runs an SQLJ translator, which converts this SQLJ program to a standard Java program and replaces the embedded SQL statements with calls to the SQLJ runtime. The generated Java program is compiled, using any Java compiler, and run against a database. The SQLJ runtime environment consists of a thin SQLJ runtime library that's implemented in pure Java, and in turn calls a JDBC driver targeting the appropriate database.

How Does SQLJ Work?
An SQLJ program is typically compiled in two steps. In the first step, an SQLJ Translator translates the SQLJ application; in the second step, any Java compiler can be used to compile those Java files. Figure 1 shows the steps involved in writing SQLJ applications.

Figure 1
Figure 1:

SQLJ Translator
An SQLJ Translator performs two important functions:

  • Translating the SQLJ Source Code - The SQLJ Translator translates the SQLJ application into a Java application with calls to the SQLJ runtime; it replaces the SQLJ clauses and generates a set of standard Java source files.
  • Type Checking - SQLJ Translator performs SQL syntax-checking, schema-checking and type-checking of host variables at translation time if the logon information to the database is provided. This is performed statically, i.e., all SQL statements in the program are checked, irrespective of the actual code paths executed at application runtime.
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 Runtime - At runtime, an SQLJ application communicates with a database through the SQLJ runtime library, which is a thin layer of pure Java code above a JDBC driver.


Figure 2:  SQLJ runtime configuration

  • Type Safety - SQLJ associates the properties of result sets and database connections with the types of Java objects that represent them so those types can appear in the interfaces between separately developed Java components. For example, the shape of rows of an iterator object (the number and types of the columns) is encoded by its type (its class). That iterator class can appear as the types of parameters and results in the interfaces between software components. Therefore, components can exchange SQL result-set data as type-safe, first-class objects with known row shapes, which allows the SQLJ Translator and Java compiler to check accesses of column data.
  • Binary Portability - Applications translated by SQLJ can access any database with an implementation of JDBC or a compliant implementation of the SQLJ runtime API. This property of binary portability allows compiled applications to be portable not only across platforms, but also across different vendors' databases.
SQLJ Deployment
From a platform point of view, the only requirements for running an SQLJ program are the availability of:
  • The SQLJ runtime library
  • A JDBC driver; Oracle's SQLJ Translator can be used with any JDBC driver
  • A Java Virtual Machine, where SQLJ programs will execute
Deployment Scenarios
SQLJ programs can be deployed in a number of different configurations, including:
  • Fat or thin clients
  • Middle-tier Java Web servers or application servers
  • A stored program on the Java Virtual Machine integrated with the Oracle8i database

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.

Figure 3:  Using SQLJ in a two-tier server
configuration uses the Oracle OCI driver

Figure 4:  Using SQLJ in a three-tier
architecture can be used to build "thin"
browser-based applications with SQLJ
deployed on the mid-tier


Figure 5:  Using SQLJ with thin clients

Figure 6:  Using SQLJ for
database stored procedures

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:

  • Regarding the type compatibility of the host variables with the SQL statements in which they are used
  • Pertaining to the correctness of the query itself, with respect to the definition of tables, views, stored procedures, etc., in the database schema.
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/

About the Author
This first look at SQLJ was written by Oracle's SQLJ Development Team. For more information contact Moe Fardoost at [email protected]

	

Listing 1.
 
CREATE TABLE PARTS_MASTER 
 (PART_ID NUMBER(8) PRIMARY KEY, 
  PART_NAME VARCHAR(40), 
  SUPPLIER VARCHAR(200)); 

CREATE TABLE MRP 
 (PART_ID REFERENCES PARTS_MASTER, 
  QUANTITY_ON_HAND NUMBER(6), 
  REORDER_THRESHOLD NUMBER(6)); 
  

// Part of a SQLJ program, showing definition of one method: 
 public class inventory { 
 ... 
 public void pullStock (int part, int quantity) throws OutOfStock { 

  int on_hand, threshold; 

  #sql { SELECT QUANTITY_ON_HAND, REORDER_THRESHOLD 
    INTO :on_hand, :threshold FROM MRP 
    WHERE PART_ID = :part FOR UPDATE }; 
  on_hand -= quantity; 

  if (on_hand < threshold) { 
  String supplier; 
  
#sql { SELECT SUPPLIER INTO :supplier FROM PARTS_MASTER 
    WHERE PART_ID = :part }; 
  inventory.orderMore(part, quantity, supplier); 
  } 

  if (on_hand < 0) { 
  #sql { ROLLBACK }; 
  throw new OutOfStock(); 
  } else { 
  #sql { UPDATE MRP SET QUANTITY_ON_HAND = :on_hand 
    WHERE PART_ID = :part }; 
  #sql { COMMIT }; 
  } 
 } 
 ... 
 } 
  

Listing 2.
 
public void printStock () { 
 AllStock iter; 
 // Instantiate the iterator with a SQL query 
 #sql iter = { SELECT PART_NAME AS "part", 
       QUANTITY_ON_HAND AS "quantity" 
     FROM PARTS_MASTER, MRP 
    WHERE PARTS_MASTER.PART_ID = MRP.PART_ID }; 
 // Now loop through the result rows 
 while (iter.next()) { 
  System.out.println("Part: " + iter.part() + ", Quantity: " 
       + iter.quantity()); 
 } 
 iter.close(); 
 } 

Listing 3.
 
// SQLJ 
 float w; java.sql.Date x; int y; String z; 
 ... 
 #sql { SELECT C1, C2 INTO :w, :x FROM TAB WHERE C3 = :y AND C4 = :z }; 
  

// JDBC 
 float w; java.sql.Date x; int y; String z; 
 ... 
 PreparedStatement s = connection.prepareStatement( 
 "SELECT C1, C2 FROM TAB WHERE C3 = ? AND C4 = ?"); 
 s.setInt(1, y); 
 s.setString(2, z); 
 ResultSet r = s.executeQuery(); 
 r.next(); 
 w = r.getFloat(1); 
 x = r.getDate(2); 
 r.close(); 
 s.close(); 

      
 

All Rights Reserved
Copyright ©  2004 SYS-CON Media, Inc.
  E-mail: [email protected]

Java and Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries. SYS-CON Publications, Inc. is independent of Sun Microsystems, Inc.