SQLJ: The ‘open sesame’ of Java database applications

ANSI and ISO standards for embedded SQL in general-purpose programming languages such as C, FORTRAN, COBOL, and ADA have been available for several years. SQL (Structured Query Language) itself dates back to the 1970s and today is accepted as the standard RDBMS language. SQL is powerful because it lets you work with data at the logical level. Implementation details can wait until you are ready to manipulate the data. Thousands of businesses and organizations worldwide use SQL to manipulate and manage large amounts of data effectively. Because of this widespread use of SQL, many businesses saw Java’s lack of SQL support as a serious challenge for enterprise applications. Several leading database and application vendors met this challenge head-on and resolved to do something about it. The result was the formation of a consortium to define the SQL Java (SQLJ) standard.

This article will take a look at that standard, which is a set of programming extensions that define the interaction between the SQL database language and the Java programming language. SQLJ consists of a set of clauses that extend Java to include static SQL constructs. The SQLJ clauses are transformed by a translator into standard Java statements that access the database via a call interface.

The article will also explore the differences between SQLJ and JDBC. It’s important to note that SQLJ supports static SQL only, and is complementary to JDBC. JDBC provides a fine-grained, dynamic interface from Java to the database.

The birth of a standard

About two years ago (April 1997 to be exact) Oracle, IBM, and Compaq’s Tandem Division formed a consortium to define and advance the standardization of Java development for enterprise-level and server-side database applications. Other key database and application server vendors — Sybase, Informix, and what was then called JavaSoft (now the Java Software Division at Sun) soon joined them. As a cofounder of the original SQLJ consortium, Oracle distributed a reference implementation of SQLJ to the other members in December 1997.

For the next year, the consortium members cooperated to formulate a comprehensive specification for submission to the ANSI/ISO standards body. They also went to great lengths to ensure the compatibility and interoperability of all SQLJ implementations from the various database vendors. In December 1998, this specification, now called SQLJ, was accepted as ANSI Standard X3.135.10-1998. This was a significant milestone for the Java language for two reasons:

• It provides integration of SQL and Java, thus reinforcing the adoption and use of Java for enterprise data-intensive applications

• It represents a landmark in multivendor cooperation and support for standards-based application development

What does the SQLJ Standard provide? The standard consists of three parts: The SQLJ Language Specification provides standard language syntax and semantics for embedding static SQL in Java programs. The Stored Procedure Specification defines standards for implementing database stored procedures and functions in Java. This will allow users who have written stored procedures in Java to easily migrate them between databases. The Stored Java Class Specification addresses standard ways to store Java datatypes and classes as objects in a database.

SQLJ productivity

So who can use SQLJ? The answer is, anyone involved in application development — independent software vendors, corporate programmers, IT management, and all application developers. When should SQLJ be used? SQLJ is an excellent choice for static SQL programming tasks, and many SQL applications are static in nature. SQLJ does not handle dynamic SQL actions determined at runtime of the application; JDBC must be used to handle dynamic SQL.

SQLJ primarily is a productivity environment that gives Java developers a quick and easy way to use SQL directly in their Java applications without the tedium of having to do database programming. This means that applications involving a very large quantity of data manipulation, such as financial, personnel, or inventory control, can now be written in Java rather than in C.

Veteran SQL programmers can benefit from using SQLJ, as they can concentrate on Java application logic while continuing to use familiar SQL to access the database. And any developer familiar with JDBC will appreciate SQLJ because it eliminates the overhead of writing the actual JDBC calls.

How does SQLJ deliver all this?

SQLJ provides application developers with a higher-level programming interface than JDBC for static SQL

You can see the difference in the number of lines of code by comparing the following examples showing an update from a query result.

The first is the SQLJ example:

#sql iterator SeatCursor(Integer row, Integer col, String type, int status);
Integer status = ?;
SeatCursor sc; 
#sql sc = { 
select rownum, colnum from seats where status <= :status 
   };  
while(sc.next()) 
     {    
#sql { insert into categ values(:(sc.row()), :(sc.col())) };  
     }   
sc.close();

And here’s the JDBC example:

Integer status = ?;
PreparedStatement stmt = conn.prepareStatement("select row, col from seats
where status <= ?");  
if (status == null) stmt.setNull(1,Types.INTEGER);  
else   stmt.setInt(1,status.intValue()); 
ResultSet sc = stmt.executeQuery();   
while(sc.next()) 
{    
int row = sc.getInt(1);   
boolean rowNull = sc.wasNull();    
int col = sc.getInt(2);  
boolean colNull = sc.wasNull();    
PreparedStatement stmt2 = conn.prepareStatement("insert into categ
values(?, ?)");    
if (rowNull) stmt2.setNull(3,Types.INTEGER);   
else stmt2.setInt(3,rownum);      
if (colNull) stmt2.setNull(4,Types.INTEGER);  
else stmt2.setInt(4,colnum);          
stmt2.executeUpdate();  
stmt2.close();
} 
sc.close();
stmt.close();

The SQLJ translator performs type-checking and schema-checking of SQL statements at program development time, rather than at runtime

Because of this, programs written in SQLJ are more robust than JDBC programs and are much easier to maintain. Also, unlike JDBC, SQLJ permits compile-time checking of the SQL syntax, of the type compatibility of the host-variables with the SQL statements in which they are used, and of the correctness of the query itself with respect to the definition of tables, views, stored procedures, and so on, in the database schema. It should be pointed out again that SQLJ and JDBC are complementary because SQLJ supports static SQL only. To perform dynamic SQL operations from a SQLJ program, you still must use JDBC.

SQLJ is comprehensive

It provides embedded SQL syntax to simplify database access for a variety of different facilities. These include transaction management, queries, DDL statements, DML statements, and stored procedure and function calls. These procedures and functions could be written in Java, C, C++, or any other language supported by the database. Languages specific to a database, such as Oracle’s PL/SQL, can also be used. Let’s look at a stored procedure example using PL/SQL:

#sql { CALL PROC(<PARAM_LIST>) };

In the example above, PROC is the name of the existing stored procedure defined in Oracle PL/SQL:

CREATE OR REPLACE PROCEDURE MAX_DEADLINE (deadline OUT DATE) IS
   BEGIN
      SELECT MAX(start_date + duration) INTO deadline FROM projects;
   END;

This procedure reads the table called “projects” in the database, looks at the start_date and duration columns, calculates start_date plus duration in each row, then takes the maximum start_date + duration total and selects it into deadline, which is an output parameter of type DATE. In SQLJ, you can call this MAX_DEADLINE procedure as follows:

java.sql.Date maxDeadline;
...
#sql { CALL MAX_DEADLINE(:out maxDeadline) };

In short, SQLJ offers developers substantial benefits for static SQL applications. Use of SQLJ reduces the number of written lines of code and performs type and schema checking earlier in the development cycle for improved quality and productivity. At the same time, it opens up the richness of SQL for data manipulation to the Java language. Developers now truly have a choice of using Java, C, or any other programming language to develop the logic for their database applications.

SQLJ’s database independence

Besides productivity, SQLJ offers a second major benefit — increased portability. SQLJ applications were designed to be vendor-independent in three important ways: First, the SQLJ syntax is designed to be database-neutral, and the SQLJ translator makes minimal assumptions about the SQL dialect. Second, the consortium members share a common SQLJ translator reference implementation. Third, the SQLJ-generated code and runtime are standard. A SQLJ program can access any data server for which a SQLJ runtime implementation exists. Since the default implementation of the SQLJ runtime performs database access using JDBC, a SQLJ program can access any data server for which JDBC drivers are implemented.

By ensuring interoperability between SQLJ implementations, the standard offers users the ability to develop applications in Java that can be transparently moved from one database platform to another. The code generated by the SQLJ translator is 100 percent standard Java code that can be executed in any standards-compliant Java virtual machine (JVM). This means that compiled Java classes (Java bytecodes) from translated SQLJ programs can be moved to any compatible SQLJ platform and executed regardless of which platform initiated the original translation. This allows SQLJ programs to be partitioned easily across different tiers in a distributed architecture and deployed in many different environments without any code changes.

The SQLJ runtime environment is vendor-neutral also. SQLJ’s runtime environment consists of a thin layer of pure Java code that communicates with the database server across a call-level API.

A SQLJ implementation can use JDBC or any other interface as its runtime environment. Furthermore, a SQLJ implementation using JDBC is not restricted to any particular database vendor’s JDBC driver. For example, the Oracle JDBC driver can be used with a JDBC-ODBC bridge to communicate with another vendor’s database. This portability is consistent with Java’s write-once, run-anywhere philosophy.

How does SQLJ work?

SQLJ is similar to the ANSI/ISO Embedded SQL standards, which prescribe how static SQL is embedded in C, COBOL, FORTRAN, and other languages. When writing an SQLJ application, a developer writes a Java program and embeds SQL statements in it, following certain standard syntactic rules that govern how SQL statements can be embedded in Java programs. The developer 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 as illustrated below.

What exactly does SQLJ do during a translation? In the first phase of SQLJ translation, the SQLJ translator parses the sqlj file, invokes a Java parser to check the syntax of Java statements and invokes a SQLJ parser to check the syntax of SQLJ constructs (anything preceded by: #sql). The SQLJ parser also invokes the Java parser to check the syntax of Java host variables and expressions within SQLJ executable statements.

The SQLJ parser checks the grammar of SQLJ constructs according to the SQLJ language specification. This syntax-check discovers errors such as missing semicolons, mismatched curly braces, and obvious type mismatches (such as multiplying a number by a string). If the parsers discover any syntactical errors or type mismatches during this phase, the translation is aborted and the errors are reported to the user.

Once the SQLJ application source code is verified as syntactically correct, the translator enters into the semantics-checking phase and invokes a semantics-checker. The semantics-checker verifies the validity of Java types used in SQL operations (result expressions or host expressions) and optionally connects to a database to check compatibility between Java types and SQL types.

The three basic tasks that are performed during semantics-checking are:

1. The Java types of Java expressions in your SQLJ executable statements are analyzed. This includes examining the SQLJ source files being translated, any java files that were also entered on the command line, and any imported Java classes that can be found through the CLASSPATH.

2. If online checking is used, the syntax of embedded SQL DML operations (SELECT, INSERT, UPDATE, DELETE, and so on) in SQLJ executable statements is checked.

3. If online checking is used, Java types of Java expressions in SQLJ executable statements are checked against SQL types of corresponding columns in the database and SQL types of corresponding arguments and return variables of stored procedures and functions. SQLJ also verifies in the process that the schema objects in your SQLJ executable statements (such as tables, views, and stored procedures) actually exist in the database.

Once your application source code has passed the preceding syntax and semantics checks, it is translated and sent to a java file. SQLJ executable statements are replaced by calls to the SQLJ runtime. If you are using Oracle’s SQLJ implementation, the SQLJ runtime contains the calls to the JDBC driver by default for database access. The generated java file has the same base name as the input sqlj file. For example, Foo.sqlj defines class Foo, and the generated file is Foo.java. By default, SQLJ places the java file in the directory of the sqlj input file.

After code generation, SQLJ invokes the Java compiler to compile the generated java file. This produces a class file for each class you defined in your application (including iterator and connection context declarations). Any java files you specified directly on the SQLJ command line (for type-resolution, for example) are compiled at this time as well. By default, SQLJ invokes the standard javac compiler of the Sun Microsystems JDK, but other compilers can be used instead.

SQLJ at runtime

The SQLJ runtime environment consists of a thin SQLJ runtime library, which is implemented in pure Java. A SQLJ runtime uses SQLJ statement objects that are similar to JDBC statement objects, though a particular implementation of SQLJ may or may not wrap JDBC statement classes directly.

It’s worth noting here again that SQLJ and JDBC are complementary. You can combine SQLJ and JDBC in a single application to address both static and dynamic SQL. This means you can type-check your static SQL, without restricting use of dynamic SQL within the same application.

The following example uses JDBC to perform a dynamic query, casts the JDBC result set to a SQLJ iterator, and uses the iterator to view the results. It demonstrates how SQLJ and JDBC can interoperate in the same program.

import java.sql.*;
import sqlj.runtime.ref.DefaultContext;
import oracle.sqlj.runtime.Oracle;
public class JDBCInterop 
{
  // in this example, we use an iterator that is inner class
  #sql public static iterator Employees ( String ename, double sal ) ;
  public static void main(String[] args) throws SQLException 
  {
    if (args.length != 1) {
      System.out.println("usage: JDBCInterop <whereClause>");
      System.exit(1);
    }
    /* if you're using a non-Oracle JDBC Driver, add a call here to
       DriverManager.registerDriver() to register your Driver
    */
    // set the default connection to the URL, user, and password
    // specified in your connect.properties file
    Oracle.connect(JDBCInterop.class, "connect.properties");
    Connection conn = DefaultContext.getDefaultContext().getConnection();
    // create a JDBCStatement object to execute a dynamic query
    Statement stmt = conn.createStatement();
    String query = "SELECT ename, sal FROM emp WHERE "; 
    query += args[0];
    // use the result set returned by executing the query to create
    // a new strongly-typed SQLJ iterator
    ResultSet rs = stmt.executeQuery(query);
    Employees emps;
    #sql emps = { CAST :rs };
    while (emps.next()) {
      System.out.println(emps.ename() + " earns " + emps.sal());
    }
    emps.close();
    stmt.close();  
  }
}

Vendor-specific customization

SQLJ was designed to allow easy customization of applications at development time and at runtime, without requiring that code be rewritten or retranslated. At program development time, the Java developer can replace the semantic analyzer in SQLJ with a vendor-specific semantic analyzer to do compile-time syntax and semantic analysis for that vendor’s implementation of SQL.

According to the SQLJ standard, the SQL operations appearing in the original program source are placed into a set of SQLJ profiles. This facility provides companies with the ability to customize SQLJ applications for their database. By creating and installing a specific customization into a profile, vendors may customize the SQLJ application for their particular platform. Customizations are used for the following reasons:

• To improve SQL execution performance
• To provide full database portability
• To grant access to vendor-specific features not otherwise available to SQLJ programs

Multiple customizations can be installed into the same SQLJ binary, so that the same binary can be used to execute SQL on databases from many vendors, and the execution of that operation will take advantage of the customization available for each vendor.

Oracle’s SQLJ Translator uses this customization capability. IBM and Oracle were the first vendors to deliver SQLJ implementations that are 100 percent compliant with the SQLJ standard. Oracle has also implemented SQLJ directly into Oracle8i so that Java developers can take advantage of its advanced features. These extensions include support for ResultSet and Iterator, ROWID, Extended Output Stream, Extended BigDecimal, BLOB, CLOB, and BFILE and Oracle8i Object and REF. Additional types supported are:

• Collections — arrays and nested tables
• Oracle native SQL wrapper classes — examples are oracle.sql.CHAR, oracle.sql.DATE, and so on
• Client-defined CustomDatum classes

BLOBs, CLOBs, and BFILE extensions

Let’s examine a few of these extensions in more detail. Oracle 8.0 SQL supports the definition, creation, deletion, and complete updates of LOB (Oracle Large Object) datatypes: BLOB (binary LOB), CLOB (single-character LOB), and BFILE (read-only binary files stored outside the database). BLOB, CLOB, and BFILE classes can be used in SQLJ applications connecting to an Oracle8i database. They may be used in SQL DML statements (as host variables and iterator column types) and as arguments to functions and procedures such as those defined in the DBMS_LOB database package.

The DBMS_LOB package is a PL/SQL package that defines a set of routines that operate on BLOB, CLOB, and BFILE datatypes. You can manipulate LOB data in chunks by using the instr( ), read(), substr( ), and write( ) functions of the DBMS_LOB package. Note that the type of the chunk depends on the kind of LOB being manipulated. CLOBs contain character data, therefore Java strings are used to hold chunks of data. BLOBs and BFILEs contain binary data, therefore Java byte arrays are used hold chunks of data. Oracle’s JDBC driver defines Java wrapper classes to manipulate LOB datatypes in JDBC programs. This example uses routines of the DBMS_LOB package to write to a BLOB.

void writeToBlob(OracleBlob blob, long blobLen) throws SQLException
{
   byte[] chunk = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 };
   long chunkLen = (long)chunk.length;
   for (long i = 1; i <= blobLen; i += chunkLen) {
      if ((blobLen - i + 1) < chunkLen) chunkLen = blobLen - i + 1;
      chunk[0] = (byte)i;       
      #sql { call dbms_lob.write(:INOUT blob, :chunkLen, :i, :chunk) };
   }
} 

An alternative to using DBMS_LOB is simply using methods of oracle.sql.BLOB. The advantage to this approach is programming convenience, as specific calls to DBMS_LOB do not have to be made. This is the same example from above that goes through a loop that writes to the BLOB in 10-byte chunks until the specified BLOB length has been reached. This time, the example uses methods of the oracle.sql.BLOB class:

void writeToBlob(BLOB blob, long blobLen) throws SQLException 
{ 
  byte[] chunk = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 }; 
  long chunkLen = (long)chunk.length; 
  for (long i = 0; i < blobLen; i+= chunkLen) { 
    if (blobLen < chunkLen) chunkLen = blobLen; 
    chunk[0] = (byte)(i+1); 
    chunkLen = blob.putBytes(i, chunk); 
  } 
} 

Object-relational extensions

Oracle’s SQLJ Translator also supports Oracle8i object-relational extensions. These extensions are useful because they provide a simpler mechanism for persistent storage of Java classes. Using Oracle8i object types with SQLJ is very similar to using them with the Oracle’s Pro*C precompiler. Oracle’s SQLJ Translator provides a version of the object type translator, JPublisher, that provides Java class mapping corresponding to Oracle8i object types and REFs. SQLJ Translator provides compile-time type checking of Java classes against schemas that include Oracle8i object types and REFs.

Another example of vendor-specific customization is access to a proprietary language such as Oracle’s PL/SQL for stored procedures and anonymous blocks. Support for a proprietary language is important to current users because it protects the investment already made and offers an evolutionary path to Java. Since PL/SQL is Oracle-specific, this does limit portability but offers a significant advantage in reusability of millions of lines of existing PL/SQL. It’s worth noting here that developers considering any vendor-specific customizations including the reuse of proprietary language code should weigh the pros and cons before doing so. These customizations do have advantages for introducing Java into an existing environment where integration of new and existing applications is important. However, it may be a better to choice to stay away from customization and use standard SQLJ and JDBC only, if the application is required to run unchanged across several databases.

The following example uses some existing PL/SQL (inside the curly brackets) to perform a loop that inserts new employees in the “emp” table, creating employee numbers 2001 to 2100.

#sql {
   DECLARE
      n NUMBER;
   BEGIN
      n := 1;
      WHILE n <= 100 LOOP
         INSERT INTO emp (empno) VALUES(2000 + n);
         n := n + 1;
      END LOOP;
   END;
};

SQLJ and Java IDEs

Many Java tools supporting integrated SQLJ development and debugging should be available soon. JDeveloper from Oracle is on example that currently supports SQLJ. In addition, the SQLJ Consortium — Oracle, IBM, Compaq, Sybase, Informix and Sun’s Java Software Division — are working closely with many of the major Java development tool vendors to integrate SQLJ capabilities with their development environments. Also, the SQLJ standard incorporates a set of interfaces specifically targetting ease of integration for Java development tool vendors.

JDeveloper Suite enables developers to build multitier, scalable Internet applications tightly integrated with Oracle’s platform for Internet application deployment, Oracle8i and Oracle Application Server.

Once developed, SQLJ applications can be deployed in both fat and thin client, two-tier client-server configurations, or they can be deployed on any middle-tier Java Web server or application server. SQLJ applications targeting the Oracle database server can be executed on the Oracle Application Server. For use with Web browsers, be sure to check specific SQLJ runtime and JDBC driver requirements of the specific implementation you’re using.

Finally, SQLJ can work across firewalls. The runtime environment for SQLJ applications essentially is a thin layer of pure Java code that works with a selected JDBC driver. So SQLJ applications work with all the firewalls with which the specific JDBC driver chosen works. Oracle’s JDBC drivers support both intranets and extranets, and can be used with the industry-leading firewalls for maximum deployment flexibility.

A word about SQLJ performance

Performance is one of the critical factors the Java language must address for widespread adoption and acceptance as an enterprise-ready solution. The SQLJ standard simply addresses the common approach for a SQLJ implementation to meet the Java philosophy. It’s then up to the vendor to tune the implementation for further benefits of performance and scalability. With this in mind, a specific SQLJ implementation can be optimized in several ways.

First of all, for direct database access Oracle has employed advanced SQL compilation techniques to improve SQL execution performance. This means that SQLJ performance is competitive with the best JDBC drivers in the marketplace.

Second, extensions have been made to the SQLJ Translator itself. These include support for bulk operations that allows a user to set a number of rows to pre-fetch into the client during queries. This means that Oracle’s SQLJ implementation substantially reduces roundtrips to the server and improves throughput.

Finally, Oracle’s SQLJ implementation is a very thin layer of Java code, which reduces to the minimum overhead introduced by the SQLJ runtime itself. Furthermore, with Oracle8i, SQLJ programs can execute on the database server’s Java VM, which provides a very high performance and scalable platform to execute SQLJ code. Traditional database stored procedures and functions, triggers, or object-relational methods can then be implemented using SQLJ.

Oracle’s approach features a Java virtual machine internal to the database. Other vendors such as IBM, implement the Java VM external to the database and use other techniques to improve Java performance.

Conclusion

This article serves as an introduction to SQLJ only. The SQLJ standard addresses what has been a serious holdup to the us of the Java language: the need for an effective mechanism for handling data-intensive applications. SQLJ works hand-in-hand with JDBC to offer a complete set of options for static and dynamic SQL operations. Corporate developers should be especially interested in SQLJ as it makes Java much more acceptable for developing many common business applications. But independent software vendors, database vendors, and other application vendors also benefit because SQLJ offers interoperability and yet allows for vendor-specific customizations as required.

Established vendors such as Oracle and IBM now offer SQLJ implementations. Other vendors including Sybase and Informix have plans to offer implementations as well. Leading tool vendors are also planning to support SQLJ in their Java IDEs, enabling more flexible development and deployment options.

Using SQLJ can mean improved productivity and better quality as it reduces the amount of code prone to human error. Additionally, SQLJ supports the promise of Java for application portability. I hope this article has provided information sufficient to encourage further investigation of SQLJ for use in your Java applications.