Day 21

Implementing J/SQL for Web Applications

In the world of the Internet, there will be times when you need to design and build an application that uses a Web browser to access information inside an Oracle database. With the advent of Java and multimedia technologies, you can now use your browser with these new tools in your Internet/database applications. It seems as though every day new products hit the marketplace that claim they can integrate your browser and the Internet with an Oracle database. Some claims are true while others offer only a partial solution.

Today provides an overview of the Internet browser/Oracle database junction. This chapter is intended to make you aware of the Internet's ability to access data within the Oracle database. Specifically, the primary focus of this chapter will be an overview of J/SQL.

J/SQL

J/SQL (Java/Structured Query Language) provides the integration of SQL statements inside a Java program. This means that a J/SQL program is simply a Java source program with embedded SQL statements. J/SQL was developed by Oracle as the open specification for a precompiled, embedded SQL interface to Java. It is much like embedding SQL code into other languages like PRO*COBOL or PRO*C. In fact, the SQL in J/SQL looks and behaves exactly the same way as it does inside PL/SQL. J/SQL will open the door for Java to become the enterprise language choice for all MIS organizations. Java access to the Oracle relational database is vital to the future of building robust applications that combine Java's power and ease of use with the functionality of the database.

The J/SQL processor consists of a precompiler written in Java and several Java classes that implement J/SQL's runtime support. A developer will write J/SQL source code and then pass it through the J/SQL precompiler to produce Java source code with standard JDBC calls. This processing flow is illustrated in Figure 21.1. JDBC (Java Database Connectivity) is discussed briefly later in this section and in more detail later in the chapter in the section "JDBC."

Figure 21.1.
Overview of J/SQL
processing.

As shown in Figure 21.1, the Java compiler will do the necessary checks for the argument number and types of arguments. This will make sure that the arguments pass cleanly from the SQL statements and the Java programs. J/SQL automatically maps SQL types to Java types, making it easier to pass information between the Java application and the Oracle database.

J/SQL takes full advantage of its own precompiler's many features. For example, the J/SQL precompiler will check the SQL source code's syntax and optimize the code for performance. This precompiler, or preprocessor, receives as input either a file of J/SQL classes that are SQL statements using J/SQL parameter syntax, or a file of Java source code with SQL statements embedded within it. In either situation, the precompiler generates Java classes source code as its output. J/SQL programs can include queries, Data Manipulation Language (DML) statements, Data Definition Language (DDL) statements, transaction controls, and calls to stored programs.

Oracle has promoted J/SQL as an open standard for embedding SQL within Java. This makes J/SQL code very portable and open to any SQL-compliant database. Additionally, the J/SQL syntax is the same across all platforms and architectures, so programs written in J/SQL can be partitioned across the application architecture. J/SQL programs can be run on the client, the server, or even the middle tier. J/SQL is built on JDBC so that it can be used to write programs to access any data server that is JDBC compliant.

J/SQL can also be stored in the database just like PL/SQL. This enables programmers to extend the functionality of the server with their user-defined stored programs. Furthermore, PL/SQL and Java can call each other seamlessly, which adds to J/SQL's capabilities because the J/SQL preprocessor will treat PL/SQL code as a J/SQL clause and will present it to the Java compiler and programs as standard Java classes and objects.

J/SQL Coding Standards

J/SQL is very class structured and a strongly typed object-oriented programming language. This section summarizes its current methods and classes. However, Oracle makes no commitment to maintain future compatibility with J/SQL's current features and those of later versions, and this version of J/SQL is not final.

Interface Methods

The first method to discuss is using a static cursor. A cursor defines methods available to all J/SQL cursor objects that correspond to the result set obtained by the execution of a statement.

The Syntax for the Cursor Interface

SQL.cursor ( public identifier ( jsql-column-list ) )

In this syntax, the parameters are as follows:

• SQL.cursor—The reserved J/SQL cursor declaration syntax.
• public—Defines the Java class as public.
• identifier—The name for this class.
• jsql-column-list—Specifies the names of the columns of any JDBC result set the cursor can receive. The cursor has a public accessor method for each of these names that returns the value of the named column of the cursor's current row.

The following is an example of a J/SQL cursor:

SQL.cursor
(
  SalesCursor
  (
    int item_number,
    String item_name,
    java.sql.Date sales_date,
    double cost,
    Integer sales_rep_number,
    String sales_rep_name
  )
)

The comma-separated terms appearing in parentheses after the class name serve two purposes: They correspond to column names in the result sets that can occupy SalesCursor objects, and they become the names of accessor methods for the corresponding data. The correspondence between the terms and column names is usually case insensitive, but the correspondence between the terms and method names is case sensitive.

In some cases, it is desirable to immediately release a cursor's database and JDBC resources instead of waiting for this to happen when the database is automatically closed; the CLOSE() method provides this immediate release.

The NEXT() method will move the cursor to the next row. The getrownum() method obtains the row number for the current row.

The second interface method is outholder. This public holder interface for objects should be treated as an out parameter when used in J/SQL operations.

J/SQL Classes

The common J/SQL classes are as follows:

• oracle.jsql.AsciiStream—An InputStream-derived class whose bytes should be interpreted as ASCII characters.