SQLJ: Embedded SQL in Java
An Oracle Technical White Paper
May 15, 1998
Introduction
During the past year and a half, Javaä has become the language of choice for developing and deploying Internet/Intranet applications. Its 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 provide support for Java. Client-side Java interfaces and tools are being complemented by highly productive, scaleable, reliable, and performant Java servers as leading software vendors such as Oracle integrate Java Virtual Machines with their databases and other application platforms. Leading browser platforms are building Java Virtual Machines into their systems. Java applets and servlets are appearing all over the Web, bringing rich functionality to what was before only a static medium. Java’s value proposition is so compelling that many large companies are already beginning to re-architect their business critical applications in Java.
This paper provides a technical overview of SQLJ, a new language that has emerged as a result of a multi-vendor effort to provide support for static SQL in Java programs. It does so in 5 sections:
Java has raised considerable interest both within the application developer community and within the corporate CIO community, because it promises to provide both a very productive application development language and the potential to significantly simplify and reduce the cost structure of the information technology infrastructure within companies today.
Software developers are attracted by Java’s power and productivity as an application programming language.
While Java has attracted the attention of the software development community, it has also raised considerable excitement among Fortune 500 Chief Information Officers [CIOs]. CIOs are attracted by Java’s promise of:
Oracle’s Network Computing Strategy brings the Internet model of computing to enterprise applications. Oracle has had a significant Java project underway aimed at providing a complete Java solution to its enterprise customers including an enterprise class Java server platform and a highly productive set of Java development tools. Oracle Data Server Technologies, the division that builds Oracle’s industry leading database server, has a number of projects underway to deliver client-side programmatic interfaces and Java integration with the Oracle database.
Having provided an overview of Oracle’s Java strategy in general, this section provides considerably more detail on SQLJ - an important component in this strategy. The section is divided into three parts:
SQLJ provides a
standard way to embed SQL statements in Java programs. In writing a SQLJ application, a user writes a Java program and embeds SQL statements in it following certain standard syntactic rules governing how SQL statements can be embedded in Java programs. The user then runs a SQLJ translator which converts this SQLJ program to a standard Java program, replacing 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. The SQLJ runtime environment consists of a thin SQLJ runtime library which is implemented in pure Java, and which in turn calls a JDBC driver targeting the appropriate database.
SQLJ is, therefore, similar to the ANSI/ISO "Embedded SQL" standards, which prescribe how static SQL is embedded in C, COBOL, FORTRAN, and other languages. For example, Oracle's pre-compiler product PRO*C is an implementation of the Embedded SQL standard in the host language C - those familiar with the Oracle pre-compilers may think of SQLJ as if it were PRO*Java. For Java developers who are unfamiliar with embedded SQL interfaces to databases, there are two important facets to SQLJ that require further exploration:
A SQLJ Application is a standard compiled Java program that was prepared from SQLJ source files. A SQLJ program is typically compiled in two steps. In the first step, a SQLJ Translator translates the SQLJ application into a Java application with calls to the SQLJ runtime replacing the SQLJ clauses - its output is a set of standard Java source files. In the second step, any Java compiler can be used to compile those Java files.
A SQLJ Translator performs two important functions:
Reference Implementation: The SQLJ partners (Oracle, IBM, Sybase, Informix, Compaq/Tandem, JavaSoft, and others) have collaborated to produce a Reference Implementation of a SQLJ Translator. The SQLJ Reference Implementation is written entirely in Java, and is designed so that it can be incorporated into Java development tools which will allow SQLJ translation and Java compilation to be performed in one step.
A compiled SQLJ application is a standard Java program and can run wherever a Java VM, the SQLJ runtime library and a JDBC driver are available. There are three important aspects to consider regarding executing SQLJ applications:
Figure: SQLJ Runtime Configurations
Having described what SQLJ is and how it works, let us now examine its most important features using an example. There are 5 important features each of which are described in more detail below.
Static SQL statements appear in a SQLJ program text as SQLJ clauses. A SQLJ clause is introduced by the token #sql, and contains a SQL statement inside curly braces. An executable SQLJ clause may appear where a Java statement may appear. Here is a SQLJ clause that contains a SQL UPDATE statement:
#sql { UPDATE TAB SET COL1 = :x WHERE COL2 > :y AND COL3 < :z };
The inputs and outputs of SQL statements are passed through host-variables. A host-variable is a Java variable, parameter, or field that is embedded in a SQL statement, prefixed by a colon character. The standard JDBC types, such as boolean, byte, short, int, String, byte[], java.sql.Date, Integer, Double, etc. are valid host variable types in SQLJ. In addition, Oracle’s SQLJ translator supports Oracle7 and Oracle8 specific types, such as ROWID, CLOB, BLOB, as well as Object and REF types.
The following example consists of two SQL table definitions, and a Java method containing SQLJ clauses that access those tables.
CREATE TABLE PARTS_MASTER
(PART_ID NUMBER(8) PRIMARY KEY,
PART_NAME VARCHAR(40),
SUPPLIER VARCHAR(200));
CREATE TABLE MRP
(PART_ID REFERENCES PARTS_MASTER,
QUANTITY_ON_HAND NUMBER(6),
REORDER_THRESHOLD NUMBER(6));
// Part of a SQLJ program, showing definition of one method:
public class inventory {
...
public void pullStock (int part, int quantity) throws OutOfStock {
int on_hand, threshold;
#sql { SELECT QUANTITY_ON_HAND, REORDER_THRESHOLD
INTO :on_hand, :threshold FROM MRP
WHERE PART_ID = :part FOR UPDATE };
on_hand -= quantity;
if (on_hand < threshold) {
String supplier;
#sql { SELECT SUPPLIER INTO :supplier FROM PARTS_MASTER
WHERE PART_ID = :part };
inventory.orderMore(part, quantity, supplier);
}
if (on_hand < 0) {
#sql { ROLLBACK };
throw new OutOfStock();
} else {
#sql { UPDATE MRP SET QUANTITY_ON_HAND = :on_hand
WHERE PART_ID = :part };
#sql { COMMIT };
}
}
...
}
The above example shows that SQLJ is quite similar to ANSI/ISO Embedded SQL. It allows SQL statements to appear directly in program logic. At program development time, static analysis can detect errors in their SQL syntax, in their uses of tables and other schema definitions, and in their numbers and types of arguments and results.
In a SQLJ program, a result-set returned by a multi-row query is manipulated by means of an iterator object, that iterates through the rows in the result-set. An iterator is an object of an iterator-class, which is a Java class that is defined by a declarative SQLJ clause that may appear where a class definition may appear. The clause defining a named-iterator class lists the Java names and types for columns in a row of a result-set. The following clause defines an iterator-class called AllStock:
#sql iterator AllStock (String part, int quantity);
The above clause implicitly defines Java class AllStock with methods named part and quantity, of types String and int, respectively. Those column-accessor methods return the values of columns from rows of a result-set contained in an iterator of type AllStock. The following SQLJ program fragment defines a local variable of class AllStock, executes a query to populate that variable with an iterator object, and calls the column-accessor methods of the iterator and prints the column values:
public void printStock () {
AllStock iter;
// Instantiate the iterator with a SQL query
#sql iter = { SELECT PART_NAME AS "part",
QUANTITY_ON_HAND AS "quantity"
FROM PARTS_MASTER, MRP
WHERE PARTS_MASTER.PART_ID = MRP.PART_ID };
// Now loop through the result rows
while (iter.next()) {
System.out.println("Part: " + iter.part() + ", Quantity: "
+ iter.quantity());
}
iter.close();
}
In addition, SQLJ provides support for defining positioned iterators that use traditional FETCH…INTO syntax to access query columns by position.
The example above contains no explicit management of database connections. Its SQL statements execute on the default database connection, which is global to the program. SQLJ programs may also manipulate multiple database connections. Users may explicitly declare a connection-context class, wherever a Java class declaration is permitted. For example:
#sql context PartsDB; // declares a class PartsDB
PartsDB pdb = new PartsDB( url, user, password );
Each SQLJ clause may designate a particular instance of a connection context, in square brackets, immediately following the token #sql, for example.
#sql [pdb] { INSERT INTO MRP VALUES (:id, :quantity, :threshold) };
Multiple connection context classes can be used in the same program to partition SQL statements that are executed on different schemas. At translation time, different SQL checking can be performed for each connection context class used.
A SQLJ program may contain both SQLJ clauses and JDBC calls, for static and dynamic SQL, respectively. The two paradigms interoperate at the level of database connections and result sets/iterators. For example, a SQLJ connection context can be initialized with an existing JDBC connection:
java.sql.connection conn = …;
PartsDb pdb = new PartsDB(conn);
It is also possible to extract a JDBC connection object from a SQLJ connection context. Similar conversions are possible between JDBC result sets and structured SQLJ iterators. For example:
AllStock iter;
#sql iter = { SELECT … };
java.sqlj.ResultSet rs = iter.getResultSet();
Thus, the dynamic SQL API for SQLJ is simply JDBC.
For SQL statements with input arguments, SQLJ clauses 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. Contrast SQLJ and JDBC program fragments for the same single-row SELECT statement:
// SQLJ
float w; java.sql.Date x; int y; String z;
...
#sql { SELECT C1, C2 INTO :w, :x FROM TAB WHERE C3 = :y AND C4 = :z };
// JDBC
float w; java.sql.Date x; int y; String z;
...
PreparedStatement s = connection.prepareStatement(
"SELECT C1, C2 FROM TAB WHERE C3 = ? AND C4 = ?");
s.setInt(1, y);
s.setString(2, z);
ResultSet r = s.executeQuery();
r.next();
w = r.getFloat(1);
x = r.getDate(2);
r.close();
s.close();
Unlike dynamic SQL, 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, etc. in the database schema. Type-checking and schema-checking is also done where column-data is fetched from an iterator object (by a FETCH statement, or by column-accessor methods), because the class of the iterator object defines the number and types of columns in rows contained by that iterator.
Having understood how SQLJ works and what its most important features are, it is now appropriate to consider how SQLJ programs can be deployed in a variety of different configurations. This section is divided into two parts: first, it discusses platform requirements to run a SQLJ program; next, it discusses five different scenarios in which SQLJ can be deployed.
The only requirements from a platform point of view to run a SQLJ program are the availability of:
SQLJ Programs can be deployed in a number of different configurations including either fat or thin clients, in middle tier Java web servers or application servers, or as stored programs on the Java Virtual Machine integrated with the Oracle8.1 database server. Since the SQLJ runtime library is a thin layer of pure Java code that sits above the chosen JDBC driver, the user must choose the JDBC driver best suited for the particular deployment configuration he needs. The remainder of this section illustrates how Oracle’s SQLJ Translator can be used in combination with Oracle’s own JDBC drivers in five different deployment configurations:
Oracle’s JDBC/OCI driver is targeted to two kinds of application developers - those who are beginning to use Java for traditional client-server application development in an Intranet environment and those who are choosing to develop Java-based middle tiers such as Java application servers. SQLJ can be used with Oracle’s JDBC/OCI driver in a traditional two-tier client-server using SQLJ in combination with Oracle’s JDBC/OCI Driver. The Java application makes calls to the SQLJ library which in turn translates calls via the JDBC/OCI driver across SQL*Net to communicate with Oracle Database Server. In this configuration, users will need to deploy the following libraries on each client:
Figure: Using SQLJ in a Two Tier Client Server Configuration
In a 3-tier configuration, a browser-based client communicates with a Java-based middle tier either using a stateless protocol such as HTTP today or a stateful protocol such as IIOP in the future. The middle tier in turn executes the Java application logic written in SQLJ and communicates with the back end database server. The following diagram indicates how SQLJ can be used in combination with Oracle’s JDBC/OCI Driver in a three-tier configuration either in an Intranet setting or an Extranet setting [where the web server is located behind the firewall].
The client is a web browser that communicates with the web server using the HTTP protocol. The Java application [the executable], the SQLJ runtime library, the JDBC/OCI driver, and the OCI, CORE and SQL*Net libraries are installed on the web server. The user can start/invoke the Java application in a number of different ways: The user can use a CGI script, Oracle’s Web Request Broker API [if the application is deployed on Oracle’s Web Application Server], or even an IIOP style invocation. Once the Java application has been invoked, it communicates with the backend database server using SQL*Net.
Figure: Using SQLJ in a Three Tier Architecture
There are three important deployment issues that arise from this form of application deployment - how to manage application state, how to improve the scalability of the application, and how to optimize application responsiveness. Users are directed to Oracle’s JDBC technical white paper which discusses these issues in considerably greater detail.
SQLJ programs may be run as Java applets - Java applets execute in a browser and therefore cannot require any client-side installation. From the browser, the user simply clicks on a Uniform Resource Locator (URL) in a HTML page that contains a Java applet tag. When the user loads the page, the Java applet (which can be a translated SQLJ program), the SQLJ runtime library (which is 100% Java), and the necessary JDBC driver are downloaded into the browser from the web server. Once they are in the browser and begin executing in the browser’s Java VM, the compiled SQLJ code establishes a connection with the database server via the JDBC driver. Since it needs to be downloadable along with the Java applet and the SQLJ runtime library, the JDBC Driver also needs to be a pure Java implementation. It also needs to be relatively small in size so that it can be downloaded quickly and execute in a performant manner on browser-based Java VMs.
Oracle’s SQLJ Runtime and the Thin JDBC driver have been designed explicitly for such implementations. By eliminating the need for a client-side installation, Oracle’s downloadable SQLJ Runtime and its Thin JDBC driver reduce the costs associated with maintenance and administration in an Intranet environment. They are even better suited for Extranet applications where client-side installations preclude the universal access that is fundamental to the success of the worldwide web. The following diagram shows the typical configuration in which the SQLJ Runtime and the Thin JDBC driver can be used with Java applets. The Java applet, SQLJ runtime library and Thin JDBC are downloaded into the browser after the user opens a URL. The SQLJ application communicates directly with the database server using SQL*Net. The web server and the database server can be on physically separate machines or on the same machine. In an Intranet deployment the entire configuration is behind a firewall; in an Extranet deployment, the web server and the database are both behind the firewall.
Figure: Using SQLJ with Thin Clients
The deployment configuration described above raises three important issues - how to handle Java security, how to manage application state, and how to support large numbers of browsers connecting in this two tier configuration. These issues are discussed in considerably greater detail in Oracle’s JDBC Technical Whitepaper to which the user is referred.
The SQLJ Translator and the Thin JDBC Driver can be used together with the Net8 Connection Manager to achieve significantly greater scalability in a two-tier or three-tier configuration on platforms with physical number of end point limitations. Oracle Connection Manager is a multipurpose networking service for Oracle environments providing client connection concentration, client connection access control, and multiprotocol connectivity. Oracle Connection Manager enables large numbers of users to connect to a single server by multiplexing multiple client database sessions across a single network connection thereby reducing internal memory usage within the database server [the server allocates memory internally to handle each connection or typically establishes a pool of connections that it swaps between a number of active connections]. By reducing the resource requirements within the database server, Oracle Connection Manager allows the Oracle database server to support a large numbers of concurrent users.
Figure: Using SQLJ with Net8 Connection Manager
Oracle8.1 tightly integrates a highly scaleable and high performance Java Virtual Machine with the database kernel. It provides an ideal platform on which to run data intensive Java application logic. A key element of the Java VM’s architecture is an efficient interface through which Java programs can access SQL and PL/SQL within the database. The Java VM provides two ways with which users can write such queries:
Figure: Using SQLJ for Database Stored Procedures
Having understood some of the technical underpinnings of Oracle’s SQLJ Translator and the numerous configurations in which it can be used, it is important to discuss the various benefits that Oracle’s SQLJ Translator provides developers building database applications in Java. These benefits can be divided into two different categories:
The SQLJ Partners - Oracle, IBM, Javasoft, Compaq Computer/Tandem, Sybase, Informix, and others - are all cooperatively developing a common standard - the SQLJ Standard - to integrate Java and SQL providing customers a highly productive, open, multivendor solution to build enterprise applications in Java. The SQLJ Standard shared by these vendors provides a number of important benefits.
SQLJ was developed cooperatively by the major database vendors primarily to provide Java application developers a simple and highly productive way to build database applications.
By cooperatively developing the SQLJ standard, Oracle, IBM, Sybase, Compaq, Informix and Javasoft and several other leading industry partners are developing a common, open standard that bridges Java and SQL. There are a number of elements to SQLJ’s vendor independence.
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 vendors with the ability to customize a SQLJ application for a vendor’s database. By creating and installing a specific customization into a profile, vendors may customize the SQLJ application for their platform. Customizations may used for the following reasons:
Multiple customizations can be installed into the same SQLJ binary, so that the same binary can be used to execute SQL on databases from vendors, and the execution of that operation will take advantage of the customization available for each vendor.
Oracle’s own SQL Translator complies with the SQLJ Standard specification, but provides customizations for both performance and to access Oracle-specific features.
In addition to providing the benefits described above that are common to all compliant SQLJ implementations, Oracle’s own SQLJ translator and runtime library has a number of other important benefits that are described in the remainder of this section.
While complying with the SQLJ Standard, Oracle’s SQLJ Translator has been customized with Oracle-specific extensions designed to simply and easily expose the database server’s capabilities to Java application developers. SQLJ developers wishing to use these Oracle-specific features need to install the Oracle SQLChecker classes, the Oracle runtime customizer classes, and an Oracle JDBC driver. Oracle’s SQLJ Translator provides support for a number of Oracle-specific features including:
Oracle’s SQLJ Translator, runtime library and generated SQLJ programs comply with a number of different Java and SQL standards.
Oracle’s AppBuilder for Java 1.0 release provides a number of features integrating SQLJ programs with the AppBuilder for Java development environment. Specifically, the AppBuilder for Java development environment provides a very rich environment for SQLJ developers to develop and deploy applications. It includes three specific capabilities:
Oracle’s SQLJ translator will be certified in a number of different configurations with most industry standard browsers and Java VMs.
SQLJ applications can be deployed in a variety of different configurations as described earlier in this paper. Specifically, they can be used for three kinds of applications:
SQLJ applications can work across firewalls. Since the runtime environment for SQLJ applications is essentially a thin layer of pure Java code together with the JDBC driver selected by the user, SQLJ applications work with all the firewalls with which the specific JDBC driver chosen works. Oracle’s own JDBC drivers can work in both an Intranet and in an Extranet setting. In an Extranet deployment, the drivers can be used with most industry leading firewalls which have been SQL*Net certified. Today, the following firewall vendors have certified their Firewalls with SQL*Net:
SQLJ applications can also be used to access a wide variety of heterogeneous data sources. By using Oracle's Transparent Gateway products, SQLJ can provide high performance transparent connectivity to over 25 different enterprise and legacy databases from Java applications or applets as shown in the figure below.
Figure: Using JDBC to access Heterogeneous Data Sources
Recognizing that Java application performance is one of the critical gating factors that can drive the widespread adoption of Java, Oracle has designed its SQLJ runtime environment with performance in mind. There are three important ways in which the SQLJ has been optimized to provide excellent performance:
The paper has provided an overview of SQLJ, a highly productive, open standard for Embedded SQL in Java supported by all the leading database vendors including Oracle, IBM, Sybase, Informix, Javasoft and Compaq/Tandem. SQLJ program can be deployed in a number of different configurations including two-tier client server applications, three-tier Intranet and Extranet applications, and to write database stored procedures, triggers and methods with Oracle8.1.
Oracle’s SQLJ Translator conforms to the SQLJ standard but provides support for a number of database features. Oracle will aggressively evolve its SQLJ implementation as a critical aspect of its comprehensive server-centric Java strategy. Oracle will be making its SQLJ translator commercially available with Oracle8.1. Some of the additional features that Oracle will include in its Oracle8.1 SQLJ product are:
SQLJ: Embedded SQL for Java
May 15, 1998
Copyright © Oracle Corporation 1997, 1998
All Rights Reserved Printed in the U.S.A.
This document is provided for informational purposes
only and the information herein is subject to change
without notice. Please report any errors herein to
Oracle Corporation. Oracle Corporation does not
provide any warranties covering and specifically
disclaims any liability in connection with this document.
Oracle and SQL*Net are registered trademarks of Oracle Corporation
JDBC and Java are registered Trademarks of JavaSoft Corporation
|
Oracle Corporation
World Headquarters
500 Oracle Parkway
Redwood Shores, CA 94065
U.S.A.
Worldwide Inquiries:
415.506.7000
Fax 415.506.7200
Copyright © Oracle Corporation 1997, 1998
All Rights Reserved