An Overview of SQLJ

Today's enterprise-level applications usually work on multiple database platforms, such as Oracle, DB2, Sybase, SQL Server, Informix, etc. Code portability and maintainability are becoming increasingly important in this environment. Porting code from one database platform to another is complex and time-consuming because database vendors use their own proprietary procedural languages (e.g., Oracle uses PL/SQL, while Sybase and SQL Server use Transact-SQL).

For years Oracle developers have been using PL/SQL—a language that provides procedural extensions to the relational database language SQL—to build complex systems that manage vast arrays of data. Unfortunately, stored procedures written in PL/SQL work only in Oracle databases. But SQL developers have a powerful tool for writing code that can be easily ported to other database platforms—namely Java, which became very popular because of its prevalence in cross-platform development and on the Internet.

Java's promise of a unified, portable application development solution that can execute on a simpler, lower-cost, network-centric IT infrastructure has prompted major development tool vendors and infrastructure providers to support Java. Leading software vendors, such as Oracle and IBM, integrate the Java Virtual Machine (JVM) with their databases and other application platforms.

For its part, Oracle introduced extensive support for Java in Oracle 8i. There are two basic ways to use Java in it:

• JDBC: Like ODBC, it provides a driver-based interface, allowing access to the Oracle database from Java applications.
• SQLJ: This new language has emerged as a result of a multivendor effort to provide support for static SQL in Java code. In theory, it offers a greater degree of programmer productivity than JDBC.

The integration of the JVM into Oracle 8i database server is one of the most significant technical innovations introduced by Oracle in recent years. The Oracle-integrated JVM, called JServer (also known as Aurora JVM), supports two different programming models:

• Integration with SQL, allowing users to write traditional database-stored procedures, functions, and triggers in Java.
• Transaction-server platform for distributed Java components, called Enterprise JavaBeans (EJB), that allow programmers to develop reusable server-side application components.

In this series I focus on Java integration with SQL in Oracle 8i, which is bidirectional in nature. That is to say, Java can call SQL and PL/SQL, and PL/SQL and SQL can call Java. Java programs call their SQL and PL/SQL counterparts using the JDBC driver, which is embedded into the JVM architecture residing in the Oracle 8i database.

In the other direction—from SQL and PL/SQL to Java—Oracle 8i offers two features. In Oracle 8i, Java namespaces map to database schemas, which facilitates dependency maintenance to allow Java to be stored in the database. Oracle 8i also provides extended Data Definition Language (DDL), such as the CREATE PROCEDURE AS JAVA command, for embedding Java code easily into Oracle 8i.

What Is SQLJ?
SQLJ is a language that allows embedding static SQL statements textually in Java programs. In writing a SQLJ application, you write a Java program and embed SQL statements in it following certain standard rules that define how SQL statements can be put inside Java programs (see the Oracle 8i SQLJ Developer's Guide and Reference on your Documentation Library CD-ROM).

Next, you run a SQLJ translator that converts this SQLJ program to a standard Java program by replacing the embedded SQL statements with calls to the SQLJ runtime library. That generated Java program is then ready to be compiled with any standard Java compiler (e.g., javac) and run against the database. The SQLJ runtime environment consists of a thin (no-overhead) SQLJ runtime library that is implemented in pure Java and which, in turn, calls a corresponding (Oracle, DB2, etc.) JDBC driver.

SQLJ is similar to other implementations of embedded SQL, like Oracle Pro*C (SQL embedded in C environment). This means it could have been named "Pro*Java" had Oracle followed this naming pattern.

The SQLJ language was designed to help Java-based programmers build database apps. SQLJ is an ISO and ANSI standard that was developed and supported by the leading database and software vendors, including Oracle, IBM, Sybase, Informix, Compaq, and others. All these corporations have cooperated to develop compatible SQLJ translator implementations for different databases.

SQLJ's Benefits
A compiled SQLJ application is a standard Java program that runs in any environment with three components: JVM, the SQLJ runtime library, and a JDBC driver. It provides the following benefits:

• Compact code. SQLJ code is more compact and error-free than JDBC code.
• Translation-time syntax and semantic checking. The SQLJ translator provides type-checking and schema-object-checking to detect syntax errors and missing or misspelled object errors in SQL statements at translation time rather than at runtime. Programs written in SQLJ are, therefore, more robust than JDBC programs.
• Multivendor interoperability. SQLJ syntax is developed and supported by major software vendors. Because SQLJ programs access the database using runtime JDBC calls, SQLJ can access any database server for which JDBC drivers are implemented.
• Flexible deployment. Because the SQLJ runtime library is a Java-based program, SQLJ applications can be deployed in any JDBC-deployable environment, such as on a thin client, a thick client, in the middle tier, or in a database server.
• Vendor-specific customization. SQLJ supports vendor-specific features and extensions through subsequent customization of Java byte code. It could be used for improving SQL execution performance, for granting access to vendor-specific features or extensions not otherwise available to SQLJ programs, or for debugging and logging purpose