SQLJ: The 'open sesame' of Java database applications

The SQLJ standard now provides SQL-based database access from Java apps

By Shirley Ann Stern, JavaWorld.com, 05/01/99

Page 3 of 6

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.