Introduction

This section presents a brief introduction to Oracle JDBC, including a comparison to SQLJ.

What is JDBC?

JDBC (Java Database Connectivity) is a standard Java interface for connecting from Java to relational databases. The JDBC standard was defined by Sun Microsystems, allowing individual providers to implement and extend the standard with their own JDBC drivers.

JDBC is based on the X/Open SQL Call Level Interface and complies with the SQL92 Entry Level standard.

In addition to supporting the standard JDBC API, Oracle drivers have extensions to support Oracle-specific datatypes and to enhance performance.

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 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 offers 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 known at the time the program is written, it can also interoperate 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. Here are some advantages you gain in using SQLJ over JDBC for static SQL statements:

• SQLJ source programs are more concise than equivalent JDBC programs, because SQLJ provides a shorter syntax.

• SQLJ provides strong typing of connections (and the sets of SQL entities that they access), query outputs, and return parameters.

• SQLJ can use database connections to type-check static SQL code at translation time. JDBC, being a completely dynamic API, does not perform any type-checking until run-time.

• SQLJ programs allow direct embedding of Java bind expressions within SQL statements. JDBC requires separate getter and/or setter call statements for each bind variable.

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

General Guidelines for Using JDBC and SQLJ

SQLJ is effective in the following circumstances:

• 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.

JDBC is effective in the following circumstances:

• Your program uses dynamic SQL. For example, you have a program that builds queries in real-time or has an interactive query 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.

  Note: You can intermix SQLJ code and JDBC code in the same source. This is discussed in the Oracle8i SQLJ Developer's Guide and Reference.

---