Introduction to SQLJ

This section introduces the basic concepts of SQLJ and discusses the complementary relationship between Java and PL/SQL in Oracle database applications.

Basic Concepts

SQLJ enables applications programmers to embed static SQL operations in Java code in a way that is compatible with the Java design philosophy. A SQLJ program is a Java program containing embedded static SQL statements that comply with the ANSI-standard SQLJ Language Reference syntax. (The ISO standard for SQLJ has been finalized, but has not yet been published. Oracle SQLJ release 8.1.7 also supports the SQLJ ISO standard specification.) Static SQL operations are predefined--the operations themselves do not change in real-time as a user runs the application, although the data values that are transmitted can change dynamically. Typical applications contain much more static SQL than dynamic SQL. Dynamic SQL operations are not predefined--the operations themselves can change in real-time and require direct use of JDBC statements. Note, however, that you can use SQLJ statements and JDBC statements in the same program.

SQLJ consists of both a translator and a runtime component and is smoothly integrated into your development environment. The developer runs the translator, with translation, compilation, and customization taking place in a single step when the sqlj front-end utility is run. The translation process replaces embedded SQL with calls to the SQLJ runtime, which implements the SQL operations. In standard SQLJ this is typically, but not necessarily, performed through calls to a JDBC driver. In the case of an Oracle database, you would typically use an Oracle JDBC driver. When the end user runs the SQLJ application, the runtime is invoked to handle the SQL operations.

The Oracle SQLJ translator is conceptually similar to other Oracle precompilers and allows the developer to check SQL syntax, verify SQL operations against what is available in the schema, and check the compatibility of Java types with corresponding database types. In this way, errors can be caught by the developer instead of by a user at runtime. The translator checks the following:

• syntax of the embedded SQL

• SQL constructs, against a specified database schema to ensure consistency within a particular set of SQL entities (optional)

  It verifies table names and column names, for example.

• datatypes, to ensure that the data exchanged between Java and SQL have compatible types and proper type conversions

The SQLJ methodology of embedding SQL operations directly in Java code is much more convenient and concise than the JDBC methodology. In this way, SQLJ reduces development and maintenance costs in Java programs that require database connectivity. When dynamic SQL is required, however, SQLJ supports interoperability with JDBC such that you can intermix SQLJ code and JDBC code in the same source file. Alternatively, you can use PL/SQL blocks within SQLJ statements for dynamic SQL.

Java and SQLJ versus PL/SQL

Java (including SQLJ) in Oracle database applications does not replace PL/SQL. Java and PL/SQL are complementary to each other in the needs they serve.

While PL/SQL and Java can both be used to build database applications, the two languages were designed with different intents and, as a result, are suited for different kinds of applications:

• PL/SQL is a better solution for SQL-intensive applications. PL/SQL is optimized for SQL, and so SQL operations are faster in PL/SQL than in Java. Also, PL/SQL uses SQL datatypes directly, while Java applications must convert between SQL datatypes and Java types.

• Java, with its superior programming model, is a better solution for logic-intensive applications. Furthermore, Java's more general type system is better suited than PL/SQL for component-oriented applications.

Oracle provides easy interoperability between PL/SQL and Java, ensuring that you can take advantage of the strengths of both languages. PL/SQL programs can transparently call Java/SQLJ stored procedures, enabling you to build component-based Enterprise JavaBeans and CORBA applications. PL/SQL programs can have transparent access to a wide variety of existing Java class libraries through trivial PL/SQL call specifications.

Java programs can call PL/SQL stored procedures and anonymous blocks through JDBC or SQLJ. In particular, SQLJ provides syntax for calling stored procedures and functions from within a SQLJ statement, and also supports embedded PL/SQL anonymous blocks within a SQLJ statement.

Note: As an alternative to using JDBC for dynamic SQL, you can use PL/SQL anonymous blocks within SQLJ statements. See "PL/SQL in SQLJ for Dynamic SQL--DynamicDemo.sqlj" for a sample.

---