JDBC versus SQLJ

This section has the following subsections:

• Advantages of SQLJ over JDBC for Static SQL

• General Guidelines for using JDBC and SQLJ

Developers who are familiar with the Oracle Call Interface (OCI) layer of client-side C code will recognize that JDBC provides the power and flexibility for the Java programmer that OCI does for the C or C++ programmer. Just as with OCI, you can use JDBC to query and update tables where, for example, the number and types of the columns are not known until runtime. This capability is called dynamic SQL. Therefore, JDBC is a way to use dynamic SQL statements in Java programs. Using JDBC, a calling program can construct SQL statements at runtime. Your JDBC program is compiled and run like any other Java program. No analysis or checking of the SQL statements is performed. Any errors that are made in your SQL code raise runtime errors. JDBC is designed as an API for dynamic SQL.

However, many applications do not need to construct SQL statements dynamically because the SQL statements they use are fixed or static. In this case, you can use SQLJ to embed static SQL in Java programs. In static SQL, all of the SQL statements are complete or "textually evident" in the Java program. That is, details of the database object, such as the column names, number of columns in the table, and table name, are known before runtime. SQLJ provides advantages for these applications because it permits error checking at precompile time.

The precompile step of a SQLJ program performs syntax-checking of the embedded SQL, type checking against the database to assure that the data exchanged between Java and SQL have compatible types and proper type conversions, and schema checking to assure congruence between SQL constructs and the database schema. The result of the precompilation is Java source code with SQL runtime code which, in turn, can use JDBC calls. The generated Java code compiles and runs like any other Java program.

Although SQLJ provides direct support for static SQL operations that are known at the time the program is written, it can also inter-operate with dynamic SQL through JDBC. SQLJ allows you to create JDBC objects when they are needed for dynamic SQL operations. In this way, SQLJ and JDBC can co-exist in the same program. Convenient conversions are supported between JDBC connections and SQLJ connection contexts, as well as between JDBC result sets and SQLJ iterators. For more information on this, see the Oracle8i SQLJ Developer's Guide and Reference.

The syntax and semantics of SQLJ and JDBC do not depend on the configuration under which they are running, thus enabling implementation on the client or database side or in the middle tier.

Advantages of SQLJ over JDBC for Static SQL

While JDBC provides a complete dynamic SQL interface from Java to relational databases, SQLJ fills a complementary role for static SQL.

Although you can use static SQL statements in your JDBC programs, they can be represented more conveniently in SQLJ. Some advantages you gain in using SQLJ over JDBC for static SQL statements are:

• SQLJ source programs are smaller than equivalent JDBC programs because SQLJ provides a shorter syntax.

• SQLJ uses database connections to type-check static SQL code. JDBC, being a completely dynamic API, does not do any type checking until run-time.

• SQLJ programs allow direct embedding of Java bind expressions within SQL statements. JDBC requires separate get and/or set call statements for each bind variable and specifies the binding by position number.

• SQLJ provides strong typing of query outputs and return parameters and allows type-checking on calls. JDBC passes values to and from SQL without compile-time type checking.

• SQLJ provides simplified rules for calling SQL stored procedures and functions.

General Guidelines for using JDBC and SQLJ

Use SQLJ to write your program when:

• you want to be able to check your program for errors at translation-time rather than at run-time.

• you want to write an application that you can deploy to another database. Using SQLJ, you can customize the static SQL for that database at deployment-time.

• you are working with a database that contains compiled SQL. You will want to use SQLJ because you cannot compile SQL statements in a JDBC program.

Use JDBC to write your program when:

• your program uses dynamic SQL. For example, you have a program that builds queries on-the-fly or has an interactive component.

• you do not want to have a SQLJ layer during deployment or development. For example, you might want to download only the JDBC Thin driver and not the SQLJ runtime libraries to minimize download time over a slow link.

---