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 purposes.

Development Steps with SQLJ
Here's what you need to do to develop and run a SQLJ application:

1. Translate the SQLJ source files with the SQLJ translator. This process generates Java files with calls to the SQLJ runtime, as well as binary SQLJ profile files that contain information about the static SQL statements present in the SQLJ source.
   
   
2. Compile the Java code with the Java compiler. After compilation, the generated profiles are customized for using database-specific data types, extensions, and features.
   
   
3. Run the application, using the SQLJ runtime library and database-specific JDBC driver.

For example, if your main .sqlj file defines class MyClass, then the source filename must be MyClass.sqlj. The translator produces the MyClass.java source file, and the compiler produces the MyClass.class class file. Also, both the translator and the compiler produce the profile-keys class, MyClass_SJProfileKeys.class. The translator names iterator classes and connection context classes according to how you declare them. For example, if you declare an iterator, MyIter, there will be a MyIter.class class file.

Here are the requirements for deploying SQLJ:

• SQLJ runtime library
  
  
• JDBC driver, e.g. JDBC/ODBC bridge, Oracle JDBC/OCI driver, Oracle Thin JDBC driver, DB2 JDBC driver, etc.
  
  
• JVM where the SQLJ program will execute

Now let's compare SQLJ with JDBC, and SQLJ/JDBC with PL/SQL.

SQLJ vs. JDBC
SQLJ was developed to complement the dynamic JDBC SQL model with a static SQL model. Unlike the ODBC and JDBC dynamic models, the static model provides strong type checking at application translation time. This entails not only compile-time checking of the SQL syntax and of the type compatibility of the host variables with the SQL statements in which they are used, but also of the correctness of the query itself with respect to the definition of tables, views, stored procedures, etc. in the database schema. Because all SQL is compiled, SQLJ could be a vehicle for better performance.

  SQLJ Code vs. JDBC Code: For SQL statements with input arguments, SQLJ classes are often shorter than the equivalent dynamic SQL (JDBC) calls, 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.

Here is the SQLJ code fragment for a single-row SELECT statement...

String vName; int vSalary; String vJob; Java.sql.Timestamp vDate;
...
#sql { SELECT Ename, Sal
       INTO :vName, :vSalary
       FROM Emp
       WHERE Job = :vJob and HireDate = :vDate };

and here is the JDBC code fragment for the same single-row SELECT statement:

String vName; int vSalary; String vJob; Java.sql.Timestamp vDate;
...
PreparedStatement stmt = connection.prepareStatement(
   "SELECT Ename, Sal "      +
   "INTO :vName, :vSalary "  +
   "FROM Emp "               +
   "WHERE Job = :vJob and HireDate = :vDate");
   
stmt.setString(1, vJob);
stmt.setTimestamp(2, vDate);
 
ResultSet rs = stmt.executeQuery();
rs.next();
 
vName   = rs.getString(1);
vSalary = rs.getInt(2);
 
rs.close();

As you can see, embedding SQL statements directly in a Java program produces much more concise and easy-to-read code than JDBC. Therefore, SQLJ reduces development time and maintenance costs in Java applications with database access. SQLJ programs could easily interact with JDBC code in the same source file to allow dynamic SQL calls, or you could use PL/SQL blocks within SQLJ statements for that. In addition, Oracle 9i added direct support for dynamic SQL in SQLJ code.

  Java (SQLJ and JDBC) vs. PL/SQL in Oracle Databases: Java in Oracle database applications does not replace PL/SQL. Java and PL/SQL complement each other. Java (SQLJ/JDBC) enjoys the following advantages over PL/SQL:

• Java offers significant performance advantages for computationally expensive routines. According to Oracle performance tuning expert Guy Harrison, Software Architect with Quest Software, Inc., Java stored procedures are 5 to 100 times (!) faster, depending on the math operations and data types used in the procedure.
  
  
• Theoretically, Java stored procedures could be more easily converted to run against other databases.
  
  
• Java procedures could be deployed at any tier in a complex application: on the client, on an application server in the middle tier, or in the database server itself.

Java (SQLJ/JDBC) also has its share of disadvantages compared to PL/SQL:

• PL/SQL is tightly integrated with the Oracle database server. Oracle has been extending and polishing PL/SQL for more than 20 years while Java was just introduced in Oracle 8i in 1998.
  
  
• PL/SQL datatypes are equivalent to Oracle-native datatypes, so there is no datatype conversion required. On the other hand, JDBC interposes a generic layer between Java code and SQL, and SQLJ is one more layer.
  
  
• PL/SQL outperforms Java for database-centric routines: PL/SQL stored procedures run 1.5 times (for OLTP) to 2.5 times (for batch process) faster than Java procedures. In addition, Guy Harrison's tests show that Java procedures use more CPU power than PL/SQL. CPU overhead could be interpreted as a result of having to work with a longer code path and additional data type conversion from Oracle to Java.

A "Best of Both Worlds" Solution
Oracle provides an ideal environment for taking advantage of the strengths of both PL/SQL and Java languages. On the one hand, PL/SQL programs can transparently call SQLJ and JDBC stored procedures, allowing you to build component-based EJB and CORBA applications. Existing Java class libraries could be easily utilized and incorporated into database code development by using trivial PL/SQL call specifications. On the other hand, Java programs can call PL/SQL stored procedures, functions, and anonymous blocks via JDBC or SQLJ.

In particular, SQLJ provides syntax for calling...

• Stored procedures—e.g., to call the UpdateSalary procedure with the CALL operator:

  #sql { CALL UpdateSalary };

• Functions—e.g., to call the GetName() function using the VALUES operator:

  String name;
  #sql { name = { VALUES GetName() };

  or by using the SET operator:

  String name;
  #sql  { SET :name = GetName() };

• Anonymous PL/SQL blocks:

  #sql { [DECLARE ...] BEGIN ... END; };

It is important to note that the decision to use a particular language should be influenced not only by performance—although that's one of the major factors considered nowadays in application development—but also by programmer productivity, available expertise, and portability. Luckily for database developers, it's not necessary to choose one language over the other. You can easily mix and match both Java (SQLJ and JDBC) and PL/SQL stored routines in one database to get a "best of both worlds" solution.

In my next article, I explore the main components of SQLJ programming and show you how to use them with some sample code.

Boris Milrud has 10 years of experience as a software developer. He is a Senior Database Engineer with Callidus Software, Inc. located in San Jose CA. He is specializing in all aspects of Oracle database software development including database design, programming, optimization and tuning. Reach him at milrud@hotmail.com.