ORACLE: Choosing SQLJ or JDBC

by in Uncategorized and tagged

JDeveloper supports two mechanisms for embedding SQL in Java programs:

  • SQLJ: If you know the PL/SQL tables and columns involved at compile time (static application), you can use SQLJ. SQLJ is an industry standard for defining precompiled SQL code in Java programs.

    SQLJ allows you to code at a higher level than JDBC, by embedding SQL statements directly in your Java code. The SQLJ precompiler that is integrated into JDeveloper translates the SQL into Java plus JDBC code for you. SQLJ with JDeveloper lets you write and debug applications much faster than you can using just JDBC.

  • JDBC: If you require fine-grained control over database access, or if you are developing an application that requires precise information about database (or instance) metadata, you can code your application entirely in Java using the JDBC API.

You can mix JDBC calls with SQLJ statements in your program. One way to do this is through connection context sharing.

28.2.1 Using SQLJ

SQLJ is a standard way to embed static SQL statements in Java programs. SQLJ applications are portable and can communicate with databases from multiple vendors using standard JDBC drivers.

SQLJ provides a way to develop applications both on the client side and on the middle-tier that access databases using Java. Developing in SQLJ is fast and efficient, and JDeveloper completely supports SQLJ development. You can create or include SQLJ files in your JDeveloper projects. When you compile a project that contains SQLJ source files, JDeveloper automatically calls the SQLJ translator, or precompiler. The translator produces completely standard Java source code, with calls to JDBC methods to provide the database support. JDeveloper then compiles the Java that the SQLJ translator generates.

For more information, see the Oracle Database SQLJ Developer’s Guide.

28.2.2 Using Oracle JDBC Drivers

JDBC provides Java programs with low-level access to databases.

Oracle JDBC drivers can be grouped into two main categories with the following attributes:

  • Java-based drivers (thin client / Type 4 driver):
    • are implemented entirely in Java
    • are highly portable
    • can be downloaded from the server system to a web browser
    • can connect using the TCP/IP protocol
    • are the only option for applets (due to security restrictions)
  • OCI-based drivers (Type 2 driver):
    • are implemented using native method libraries (OCI DLLs)
    • have OCI libraries that must be available on the client system
    • cannot be downloaded to a browser
    • can connect using any Net8 protocol
    • deliver high performance

    The following figure illustrates how JDBC components and the driver run in the same memory space as an applet.

    Figure 28-1 JDBC Components

    JDBC components running in same memory space as an applet

The following figure illustrates how the Oracle JDBC OCI drivers run in a separate memory space from your Java application. These JDBC drivers make OCI calls to a separately loaded file.

Figure 28-2 Oracle JDBC OCI Drivers

JDBC OCI drivers run in a separate memory space

Note:

Take care not to confuse the terms JDBC and JDBC drivers. All Java applications, no matter how they are developed or where they execute, ultimately use the JDBC-level drivers to connect to Oracle. However, coding using the pure JDBC API is low-level development, akin to using the Oracle Call Interface (OCI) to develop a database application. Like the OCI, the JDBC API provides a very powerful, but also very code-intensive, way of developing an application.

28.2.3 SQLJ versus JDBC

How does SQLJ compare to JDBC? Here are some of the advantages that SQLJ offers over coding directly in JDBC:

  • SQLJ programs require fewer lines of code than JDBC programs. They are shorter, and hence easier to debug.
  • SQLJ can perform syntactic and semantic checking on the code, using database connections at compile time.
  • SQLJ provides strong type-checking of query results and other return parameters, while JDBC values are passed to and from SQL without having been checked at compile time.
  • SQLJ provides a simplified way of processing SQL statements. Instead of having to write separate method calls to bind each input parameter and retrieve each select list item, you can write one SQL statement that uses Java host variables. SQLJ takes care of the binding for you.

However, JDBC provides finer-grained control over the execution of SQL statements and offers true dynamic SQL capability. If your application requires dynamic capability (discovery of database or instance metadata at runtime), then you should use JDBC.

28.2.4 Embedding SQL in Java Programs with SQLJ

You have to perform a number of tasks to embed SQL in Java programs with SQLJ.

28.2.4.1 How to Create SQL Files

You can create a new SQL (.sql) file and add it to the current project.

To create a SQL file:

  1. In the Application Navigator, select the project.
  2. From the main menu, choose File > New to open the New Gallery.
  3. In the New Gallery, in the Categories tree, select Database Tier then Database Files. In the Items list, double-click SQL File.
  4. In the Create SQL File dialog, provide the details to describe the new file.

    For more information at any time, press F1 or click Help from within the dialog.

  5. Click OK.

An empty SQL file is added to the current project and opened for editing.

28.2.4.2 How to Create SQLJ Classes

Create a new SQLJ (.sqlj) file and add it to the current project.

To create a new SQLJ file:

  1. In the Navigator, select the project.
  2. From the main menu, choose File > New to open the New Gallery.
  3. In the Categories tree, expand Database Tier and select Database Files.

    For more information at any time, press F1 or click Help from within the dialog.

  4. In the Items list, double-click SQLJ Class to open the Create SQLJ Class dialog.
  5. In the Create SQLJ File dialog, provide the details to describe the new file.

    For more information at any time, press F1 or click Help from within the dialog.

  6. Click OK.

A skeleton SQLJ class will be added to the current project and be opened for editing.

28.2.4.3 How to Compile SQLJ Classes

You can compile SQLJ classes into Java .class files.

To compile a SQLJ class:

  1. Set the project’s SQLJ translator options to control how the file is compiled.
  2. In the Application Navigator, locate and select the SQLJ class.
  3. Right-click the class, and choose Make.

The status bar at the bottom of the JDeveloper window shows the result of the compilation. Errors, if any, are listed in the log window.

28.2.4.4 How to Use Named SQLJ Connection Contexts

A SQLJ executable statement can designate a connection context object that specifies the database connection where the SQL operation in that clause will execute. If the SQLJ statement omits the connection context clause, then the default connection context is used.

28.2.4.5 How to Declare a SQLJ Connection Context Class

A connection context is an object of a connection context class, which you define using a SQLJ connection declaration.

To declare a context class:

  1. Declare a context class.

    Named connection contexts are not required: SQLJ statements that omit the connection context name use the default connection context.

    For example, this statement declares the context class MyConnectionContext:

    #sql context MyConnectionContext;

    Context classes extend sqlj.runtime.ref.ConnectionContextImpl and implement sqlj.runtime.ConnectionContext.

After you have declared a context class, create a context object.

28.2.4.6 How to Create a Connection Context Object

Before it can be used in an SQLJ statement, a declared connection context must be created.

To create a context object:

  1. Named connection contexts are not required: SQLJ statements that omit the connection context name use the default connection context.

    For example, use this statement to create an instance thisCtx for the connection context class MyConnectionContext:

    MyConnectionContext thisCtx = new MyConnectionContext (myPath, myUID, myPasswd, autocommit

28.2.4.7 How to Debug SQLJ Classes

You debug SQLJ code by debugging the SQLJ source directly, not the generated Java code.

SQLJ is debugged in JDeveloper in the same manner as other source code.

For more information, see the Oracle Database SQLJ Developer’s Guide.

28.2.4.8 How to Debug SQLJ Classes

You debug SQLJ code by debugging the SQLJ source directly, not the generated Java code.

SQLJ is debugged in JDeveloper in the same manner as other source code.

For more information, see the Oracle Database SQLJ Developer’s Guide.

28.2.4.9 How to Set SQLJ Translator Options

You can control the translation of SQLJ classes through the controls in the Project Properties dialog:

  • Provide syntactic as well as semantic checking of SQL code.
  • Provide syntax and type checking on the SQL statements.
  • Test the compatibility of Java and SQL expressions at compile time.
  • Specify a connection to a database server.
  • Check the semantics of your SQL statements against the database schemas specified by connection contexts.

To set the SQLJ translator options:

  1. In the Application Navigator, select the project that contains the SQLJ file.
  2. Choose Application > Project Properties > Compiler and select SQLJ.
  3. In the SQLJ panel, set the compilation options.
  4. Click OK.

You can set SQLJ translator properties for all projects by choosing Default Project Properties from the Application menu

28.2.4.10 How to Use SQLJ Connection Options

SQLJ connection options specify the database connection for online checking. The general form for connection options is

-option@context=value

where option is one of the four options listed below.

The context tag is a connection context type, which permits the use of separate exemplar schemas for each of the connection contexts. If you omit the connection context type, the value will be used for any SQL statements that use the default connection context. The driver option does not allow a context tag.

The options are:

  • user This option specifies the user name for connecting to a database in order to perform semantic analysis of the SQL expressions embedded in a SQLJ program. It contains the user name, for example: 
    -user=hr

    The user command line option may include a connection context type. For example:

    -user@Ctx1=hr

    Whenever a user name is required for the connection to a database context Ctx1, SQLJ uses the user option that was tagged with Ctx1. If it can not find one, SQLJ issues a message and looks for an untagged user option to use instead.

    Specifying a user value indicates to SQLJ that online checking is to be performed. If you do not specify the user option, SQLJ does not connect to the database for semantic analysis. There is no default value for the user option.

    If you have turned on online checking by default (by specifying, for example, -user=hr), then in order to disable online checking for a particular connection context type Ctx2, you must explicitly specify an empty user name, for example:

    -user@Ctx2Z
  • password This option specifies a password for the user. The password will be requested interactively if it is not supplied. This option can be tagged with a connection context type. Examples of the two forms are: 
    -password=hr
-password@Ctx1=hr
  • url This option specifies a JDBC URL for establishing a database connection. The default is jdbc:oracle:oci9:@. This option can be tagged with a connection context type. For example: 
    -url=jdbc:oracle:oci8:@ -url@Ctx1=jdbc:oracle:thin:@<local_host>:1521:orcl
  • driver This option specifies a list of JDBC drivers that should be registered in order to interpret JDBC connection URLs for online analysis. The default isoracle.jdbc.driver.OracleDriver. For example: 
    -driver=sun.jdbc.odbc.JdbcOdbcDriver,oracle.jdbc.driver.OracleDriver

    This option cannot be tagged with a connection context type.

    (Src: Oracle® Fusion Middleware User’s Guide for Oracle JDeveloper)

Leave a comment