Introduction to SQLJ

SQLJ enables you to embed SQL statements into Java programs. SQLJ is an ANSI standard developed by a consortium of leading providers of database and application server software including IBM Corporation, Microsoft Corporation, Sun Microsystems, and Oracle.

The SQLJ translator translates an SQLJ source file into a standard Java source file plus an SQLJ serialized profile that encapsulates information about static SQL in the SQLJ source. The translator converts SQLJ clauses to standard Java statements by replacing the embedded SQL statements with calls to the SQLJ run-time library. An SQLJ customization script binds the SQLJ profile to the database, producing one or more database packages. The Java file is compiled and run (with the packages) on the database. The SQLJ run-time environment consists of an SQLJ run-time library that is implemented in pure Java. The SQLJ run-time library calls the JDBC driver for the target database, such as DB2 Universal Database.

You can use SQLJ in the workbench to create applications to run with any database that supports SQLJ (for example, DB2 Universal Database or Oracle). The built-in customization feature only works with DB2 Universal Database.

The Workbench has the following SQLJ features:

• An SQLJ editor with SQLJ statement syntax highlighting

• An SQLJ translator that translates SQLJ files to pure Java files and produces SQLJ serialized profiles

• An SQLJ debugger that enables you to debug SQLJ files directly

• Built-in customization when developing applications for DB2 Universal Database

 

SQLJ and JDBC

Although both SQLJ and JDBC provide Java applications with the capability to access relational databases, there are several differences between them:

• JDBC uses dynamic SQL. In contrast SQLJ can use static SQL. To use static SQL, you run a customization script to bind the SQL statements to the database before you run the application.

• SQLJ requires precompilation. The SQLJ translator converts code that contains SQLJ clauses embedded in Java code into pure Java code that calls into the SQLJ run-time library.

 

Dynamic SQL versus static SQL

In general, dynamic SQL is more flexible than static SQL, because it does not require SQL statements to be created in advance. Static SQL is more efficient, because the database has done much of the work required to run the SQL statements before run time.

Dynamic SQL provides the ability to query and update tables when, for example, you do not know the number and types of the columns until run time. Using dynamic SQL in JDBC, a Java program can create SQL statements at run time.

With dynamic SQL, database access and authorization are determined at run time. The user of the application must have all required database privileges, and the database must determine the best way to access the required data at run time. However, with static SQL, access and authorization are determined at customization and bind time. The privileges of the user who performs the bind process determine what the application can do. The database determines the best way to access data during customization and bind time, which improves run-time performance.

Many applications do not need to dynamically create SQL statements because the database metadata (such as table and column names) does not change at run time. Such applications can use static SQL, which provides better performance than dynamic SQL.

 

Advantages of SQLJ

In addition to better performance through using static SQL, SQLJ has the following advantages over JDBC:

• SQLJ generally requires fewer lines of code than JDBC to perform the same tasks, which means less time spent developing, debugging, and maintaining an application.

• The SQLJ translator checks the syntax of SQL statements during translation. This check prevents any run-time SQL syntax errors. JDBC performs no such checks, which can allow run-time SQL syntax errors to occur.

• SQLJ uses database connections to type-check static SQL code. JDBC does not perform type-checking until run time.

• With SQLJ, you can embed Java variables in SQL statements. In contrast, JDBC requires separate get and set calls for each variable, and specify the binding by position number.

• SQLJ provides strong typing of query output and return parameters and allows type-checking on calls. JDBC does not perform compile-time type-checking of variables passed to and received from SQL.

• SQLJ provides static package-level security with compile-time encapsulation of database authorization.

JDBC can do one thing that SQLJ cannot do: run dynamic SQL statements. However, if an application needs to use dynamic SQL, you can include some JDBC code in your program with SQLJ clauses.

 

SQLJ syntax

SQLJ clauses are embedded into Java code. Each clause is preceded by the symbol #sql. The following SQLJ clause is an example of a SELECT statement with a Java variable in the WHERE clause:

#sql [ctx] cursor1 = {SELECT EMP_ACT.EMPNO FROM EMP_ACT WHERE
EMP_ACT.PROJNO = :strProjNo};

 

SQLJ in DB2 Universal Database

DB2 SQLJ support is based on the SQLJ ANSI standard. The following kinds of SQL constructs can appear in SQLJ programs:

• SELECT statements and expressions

• SQL data manipulation language (DML) statements; for example, INSERT, UPDATE, DELETE

• Data statements; for example, FETCH, SELECT INTO

• Transaction control statements such as COMMIT, ROLLBACK.

• Data definition language (DDL) statements such as CREATE, DROP, and ALTER

• Calls to stored procedures and user-defined functions

 

Parent topic

Developing SQLJ applications

 

Related concepts

Application performance with SQLJ

Related reference
SQLJ statement syntax
Restrictions on SQLJ (DB2 Universal Database)