SQLJ: The 'open sesame' of Java database applications

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

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 
#sql { insert into categ values(:(sc.row()), :(sc.col())) };  

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();   
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);          

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:


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

      SELECT MAX(start_date + duration) INTO deadline FROM projects;

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.

Figure 1. SQLJ-Java conversion

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.
1 2 3 Page 1
Page 1 of 3