This chapter provides a general overview of SQLJ features and scenarios. The following topics are discussed:
This section introduces the basic concepts of SQLJ and discusses the complementary relationship between Java and PL/SQL in Oracle Database applications. This section covers the following topics:
SQLJ enables applications programmers to embed SQL statements in Java code in a way that is compatible with the Java design philosophy. A SQLJ program is a Java program containing embedded SQL statements that comply with the International Standardization Organization (ISO) standard SQLJ Language Reference syntax. The Oracle SQLJ implementation supports the ISO SQLJ standard specification. The standard covers only static SQL operations, which are predefined SQL operations that do not change in real time while a user runs the application. The Oracle SQLJ implementation also offers extensions to support dynamic SQL operations, which are not predefined and the operations can change in real time. It is also possible to use dynamic SQL operations through Java Database Connectivity (JDBC) code or PL/SQL code within a SQLJ application. Typical applications contain more static SQL operations than dynamic SQL operations.
SQLJ consists of a translator and a run-time component and is smoothly integrated into your development environment. You can run the translator to translate, compile, and customize the code in a single step using the sqlj
front-end utility. The translation process replaces embedded SQL statements with calls to the SQLJ run time, which processes the SQL statements. In ISO standard SQLJ this is typically, but not necessarily, performed through calls to a JDBC driver. To access an Oracle database, you would typically use an Oracle JDBC driver. When you run the SQLJ application, the run time is started to handle the SQL operations.
The SQLJ translator is conceptually similar to other Oracle precompilers and enables you 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, you can catch errors during development rather than a user catching the errors at run time. The translator checks the following:
Syntax of the embedded SQL statements
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.
Data types, to ensure that the data exchanged between Java and SQL have compatible types and proper type conversions
The SQLJ methodology of embedding SQL statements 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.
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:
Using PL/SQL anonymous blocks within SQLJ statements is one way to support dynamic SQL operations in a SQLJ application. However, the Oracle SQLJ implementation includes extensions to support dynamic SQL directly.The Oracle SQLJ implementation provides the option of Oracle-specific code generation, where Oracle JDBC calls are generated directly in the code. This is the default behavior. In the case of Oracle-specific code generation, you must be aware of the following:
There are no profile files, and therefore, there is no customization step during translation.
At run time, SQL operations do not have to go through the SQLJ run time layer, because JDBC calls, instead of the SQLJ run time calls, are directly generated in the translated code.
This section introduces the main SQLJ components and the concept of SQLJ profiles. It covers the following topics:
Note:
Profiles are for ISO code generation only.The Oracle SQLJ implementation consists of two major components:
SQLJ translator: This component is a precompiler that you run after creating SQLJ source code.
The translator, which is written in pure Java, supports a programming syntax that enables you to embed SQL statements in SQLJ executable statements. SQLJ executable statements and SQLJ declarations are preceded by the #sql
token and can be interspersed with Java statements in a SQLJ source code file. SQLJ source code file names must have the .sqlj
extension. The following is a sample SQLJ statement:
#sql { INSERT INTO emp (ename, sal) VALUES ('Joe', 43000) };
The translator produces a .java
file. It also produces one or more SQLJ profiles for ISO standard SQLJ code generation. These profiles contain information about the embedded SQL operations. SQLJ then automatically invokes a Java compiler to produce .class
files from the .java
file.
You can invoke the translator using the sqlj
command-line utility. On the command line, specify the files that need to be translated and any desired SQLJ option settings.
Note:
By default, there is an Oracle-specific code generation setting that results in translation directly into Oracle JDBC code. In this case, no profiles are produced. Refer to "Oracle-Specific Code Generation (No Profiles)".SQLJ run time: This component is also written in pure Java and is invoked automatically each time you run a SQLJ application.
For ISO standard code generation, the SQLJ run time implements the desired actions of the SQL operations by accessing the database using a JDBC driver. The generic ISO SQLJ standard does not require that a SQLJ run time use a JDBC driver to access the database. However, the Oracle SQLJ implementation does require a JDBC driver. In fact, it requires an Oracle JDBC driver if your application is customized with the default Oracle customizer.
For Oracle-specific code generation, Oracle JDBC calls are generated directly into the translated code and the SQLJ run time plays a much smaller role.
See Also:
"SQLJ Run Time"In addition to the translator and run time, there is a component known as the customizer that plays a role if you use ISO standard code generation. A customizer tailors SQLJ profiles for a particular database implementation and vendor-specific features and data types. By default, for ISO standard code, the SQLJ front end invokes an Oracle customizer to tailor your profiles for an Oracle Database instance and Oracle-specific features and data types.
When you use the Oracle customizer during translation, your application will require the SQLJ run time and an Oracle JDBC driver when it runs.
Note:
From Oracle Database 10g release 1 (10.1), only Oracle JDBC drivers are supported with SQLJ.With ISO standard SQLJ code generation, SQLJ profiles are serialized Java resources or classes generated by the SQLJ translator, which contain details about the embedded SQL statements. The translator creates these profiles. Then, depending on the translator option settings, it either serializes the profiles and puts them into binary resource files or puts them into .class
files.
Note:
By default, Oracle-specific code generation is used. In this case, the translator generates Oracle JDBC calls directly, and details of your embedded SQL statements are embodied in the JDBC calls. There are no profiles. Refer to "Oracle-Specific Code Generation (No Profiles)"This section covers the following topics:
SQLJ profiles are used in ISO standard code for implementing the embedded SQL operations in SQLJ executable statements. Profiles contain information about the SQL operations and the types and modes of data being accessed. A profile consists of a collection of entries, where each entry maps to one SQL operation. Each entry fully specifies the corresponding SQL operation, describing each of the parameters used in processing this instruction.
For ISO code generation, SQLJ generates a profile for each connection context class in your application, where each connection context class corresponds to a particular set of SQL entities you use in your database operations. There is one default connection context class, and you can declare additional classes. The ISO SQLJ standard requires that the profiles be of standard format and content. Therefore, for your application to use vendor-specific extended features, your profiles must be customized. By default, this occurs automatically, with your profiles being customized to use Oracle-specific extended features.
Profile customization allows vendors to add value in the following ways:
Vendors can support their own specific data types and SQL syntax. For example, the Oracle customizer maps standard JDBC PreparedStatement
method calls in translated SQLJ code to OraclePreparedStatement
method calls, which provide support for Oracle type extensions.
Vendors can improve performance through specific optimizations.
Notes:
By default, SQLJ profile file names have the .ser
extension, but this does not mean that all .ser
files are profiles. Other serialized objects can use this extension, and a SQLJ program unit can use serialized objects other than its profiles. Optionally, profiles can be converted to .class
files instead of .ser
files.
A SQLJ profile is not produced if there are no SQLJ executable statements in the source code.
The Oracle SQLJ implementation supports the ISO SQLJ specification. Because the ISO SQLJ standard is a superset of the American National Standards Institute (ANSI) SQLJ standard, using its features requires a Java Development Kit (JDK) 1.2.x or later environment that complies with Java2 Platform, Enterprise Edition (J2EE). The SQLJ translator accepts a broader range of SQL syntax than the ANSI SQLJ standard specifies.
Note:
Oracle SQLJ implementation is supported with JDK 1.2.x, 1.3.x, and 1.4.x.The ANSI standard addresses only the SQL92 dialect of SQL, but allows extension beyond that. The Oracle SQLJ implementation supports the Oracle SQL dialect, which is a superset of SQL92. If you need to create SQLJ programs that work with other databases, then avoid using SQL syntax and SQL types that are not in the standard and, therefore, may not be supported in other environments.
This section covers the following topics:
See Also:
Chapter 5, "Type Support", and Chapter 6, "Objects, Collections, and OPAQUE Types" for information about SQLJ extensions provided by Oracle DatabaseThe Oracle SQLJ implementation supports the following Java types as extensions to the SQLJ standard:
Instances of oracle.sql.*
classes as wrappers for SQL data.
Custom Java classes, typically produced by the JPublisher utility to correspond to SQL objects, object references, and collections. For example, classes that implement the oracle.sql.ORAData
interface or the JDBC standard java.sql.SQLdata
interface.
Note:
TheSQLData
interface is standard. Classes that implement it are supported by JDBC drivers and databases of other vendors.See Also:
"Custom Java Classes"Stream instances: BinaryStream
and CharacterStream
, the latter of which replaces the deprecated AsciiStream
and UnicodeStream
, used as output parameters.
See Also:
"Support for Streams"Iterator and result set instances as input or output parameters. The SQLJ standard specifies them only in result expressions or cast statements.
Unicode character types: NString
, NCHAR
, NCLOB
, and NcharCharacterStream
, the latter of which replaces the deprecated NcharAsciiStream
and NcharUnicodeStream
.
See Also:
"SQLJ Extended Globalization Support"Using any of these extensions requires Oracle-specific code generation or Oracle customization during translation, as well as the Oracle SQLJ run time and an Oracle JDBC driver when your application runs. Do not use these or other types if you want to use your code in other environments. To ensure that your application is portable, use the SQLJ -warn=portable
flag.
See Also:
See "Translator Command Line and Options"The Oracle SQLJ implementation also supports the following extended functionality:
Oracle-specific code generation
This generates JDBC code directly. No profiles are produced and much of the SQLJ run time functionality is bypassed during program execution.
Dynamic SQL in SQLJ statements
See Also:
"Support for Dynamic SQL"Scrollable result set iterators with additional navigation methods, and FETCH
syntax from result set iterators and scrollable result set iterators
See Also:
"Scrollable Iterators"Optimization flags for column and parameter size definitions
Flags for modified translator behavior, such as for binding host expressions by identifier or accounting for blank padding in CHAR
comparisons for WHERE
clauses
SQLJ statement caching on connection contexts
See Also:
"Statement Caching"SQLJ source code contains a mixture of standard Java source together with SQLJ class declarations and SQLJ executable statements containing embedded SQL statements. SQLJ source files have the .sqlj
file name extension. The file name must be a legal Java identifier. If the source file declares a public class, then the file name must match the name of this class. If the source file does not declare a public class, then the file name should match the name of the first defined class.
This section covers the following topics:
After you have written your .sqlj
file, you must run SQLJ to process the files. The following example shows SQLJ being run in its simplest form with no command-line options for the Foo.sqlj
source file with the public class Foo
:
% sqlj Foo.sqlj
This command runs a front-end script or utility depending on the platform. The script or utility reads the command line, invokes a Java virtual machine (JVM), and passes arguments to it. The JVM invokes the SQLJ translator and acts as a front end.
The following sequence of events occurs, presuming each step completes without error:
The JVM invokes the SQLJ translator.
The translator parses the SQLJ and Java code in the .sqlj
file, checking for proper SQLJ syntax and looking for type mismatches between the declared SQL data types and corresponding Java host variables. Host variables are Java local variables that are used as input or output parameters in SQL operations.
Depending on the SQLJ option settings, the translator invokes the online semantics-checker, the offline parser, neither, or both. This is to verify syntax of embedded SQL and PL/SQL statements and to check the use of database elements in the code against an appropriate database schema, for online checking. Even when neither is specified, some basic level of checking is performed.
When online checking is specified, SQLJ will connect to a specified database schema to verify that the database supports all the database tables, stored procedures, and SQL syntax that the application uses. It also verifies that the host variable types in the SQLJ application are compatible with data types of corresponding database columns.
For Oracle-specific SQLJ code generation (-codegen=oracle
, which is default), SQL operations are converted directly into Oracle JDBC calls and no profiles are produced.
For ISO standard code generation (-codegen=iso
), the translator processes the SQLJ source code, converts SQL operations to SQLJ run-time calls, and generates Java output code and one or more SQLJ profiles. A separate profile is generated for each connection context class in the source code, where a different connection context class is typically used for each interrelated set of SQL entities that is used in the operations.
Generated Java code is put into a .java
output file containing the following:
Any class definitions and Java code from the .sqlj
source file
Class definitions created as a result of the SQLJ iterator and connection context declarations
See Also:
"Overview of SQLJ Declarations"A class definition for a specialized class known as the profile-keys class that SQLJ generates and uses in conjunction with the profiles (for ISO standard SQLJ code generation only)
Calls to Oracle JDBC drivers (for Oracle-specific code generation) or to the SQLJ run time (for ISO standard code generation) to implement the actions of the embedded SQL operations
Generated profiles contain information about all the embedded SQL statements in the SQLJ source code, such as actions to take, data types being manipulated, and tables being accessed. When the application is run, the SQLJ run time accesses the profiles to retrieve the SQL operations and passes them to the JDBC driver.
By default, profiles are put into .ser
serialized resource files, but SQLJ can optionally convert the .ser
files to .class
files as part of the translation.
The JVM invokes the Java compiler, which is usually, but not necessarily, the standard javac
provided with the Sun Microsystems JDK.
The compiler compiles the Java source file generated in Step 4 and produces Java .class
files as appropriate. This will include a .class
file for each class that is defined, each of the SQLJ declarations, and the profile-keys class.
For ISO standard SQLJ code generation, the JVM invokes the Oracle customizer or other specified customizer to customize the profiles generated in Step 4.
See Also:
"Internal Translator Operations"Consider the following when translating and running SQLJ applications:
The preceding is a very generic example. It is also possible to specify:
Existing .java
files on the command line to be compiled and to be available for type resolution as well
Existing profiles to be customized
Java Archive (JAR) files containing profiles to be customized
For Oracle-specific code generation, your application will require an Oracle JDBC driver when it runs, even if your code does not use Oracle-specific features.
For ISO code generation, SQLJ generates profiles and the profile-keys class only if your source code includes SQLJ executable statements.
Also for ISO code, if you use the Oracle customizer during translation, then your application will require the Oracle SQLJ run time and an Oracle JDBC driver when it runs, even if your code does not use Oracle-specific features. You can avoid this by specifying -profile=false
when you translate, to bypass Oracle-specific customization.
This section summarizes what the SQLJ translator takes as input, what it produces as output, and where it places its output. This section covers the following topics:
Note:
This discussion mentions iterator class and connection context class declarations. Iterators are similar to JDBC result sets and connection contexts are used for database connections.The SQLJ translator takes one or more .sqlj
source files as input, which can be specified on the command line. The name of the main .sqlj
file is based on the public class it defines, if any, else on the first class it defines.
If the main .sqlj
file defines the MyClass
class, then the source file name must be:
MyClass.sqlj
This must also be the file name if there are no public class definitions, but MyClass
is the first class defined. You must define each public class in separate.sqlj
files. When you run SQLJ, you can also specify numerous SQLJ options on the command line or in the properties files.
The translation step produces a Java source file for each .sqlj
file in the application and at least one application profile for ISO standard code generation, presuming the source code uses SQLJ executable statements.
SQLJ generates Java source files and application profiles as follows:
Java source files will be .java
files with the same base names as the .sqlj
files.
For example, the translator produces MyClass.java
corresponding to MyClass.sqlj
, which defines the MyClass
class. The output .java
file also contains class definitions for any iterators or connection context classes declared in the .sqlj
file.
The application profile files, if applicable, contain information about the SQL operations of the SQLJ application. There will be one profile for each connection class that is used in the application. The profiles will have names with the same base name as the main .sqlj
file and the following extensions:
_SJProfile0.ser _SJProfile1.ser _SJProfile2.ser ...
For example, for MyClass.sqlj
the translator produces:
MyClass_SJProfile0.ser
The .ser
file extension indicates that the profiles are serialized. The .ser
files are binary files.
Note:
The-ser2class
translator option instructs the translator to generate profiles as .class
files instead of .ser
files. Other than the file name extension, the naming is the same.The compilation step compiles the Java source file into multiple class files. One .class
file is generated for each class defined in the .sqlj
source file. Also, a .class
file is generated for a class known as the profile-keys class that the translator generates and uses with the profiles to implement the SQL operations. Additional .class
files are produced if you declared any SQLJ iterators or connection contexts. Also, separate .class
files will be produced for any inner classes or anonymous classes in the code.
See Also:
"Overview of SQLJ Declarations"For Oracle-specific code generation (the default), no profiles or profile-keys class are produced.
The .class
files are named as follows:
The class file for each class defined consists of the name of the class with the .class
extension. For example, the translator output file MyClass.java
is compiled into the MyClass.class
class file.
The profile-keys class that the translator generates is named according to the base name of the main .sqlj
file, plus the following:
_SJProfileKeys
So, the class file has the following extension:
_SJProfileKeys.class
For example, for MyClass.sqlj
, the translator together with the compiler produces:
MyClass_SJProfileKeys.class
The translator names iterator classes and connection context classes according to how you declare them. For example, if you declare an iterator MyIter
, then the compiler will generate a corresponding MyIter.class
class file.
The customization step alters the profiles but produces no additional output.
Note:
It is not necessary to reference SQLJ profiles or the profile-keys class directly. This is all handled automatically.By default, SQLJ places the generated .java
files in the same directory as the .sqlj
file. You can specify a different .java
file location using the SQLJ -dir
option.
By default, SQLJ places the generated .class
and .ser
files in the same directory as the generated .java
files. You can specify a different location for .class
and .ser
files using the SQLJ -d
option. This option setting is passed to the Java compiler so that .class
files and .ser
files will be in the same location.
For both the -d
and -dir
option, you must specify a directory that already exists.
This section discusses run-time processing during program execution. It covers the following topics:
When you translate with the default -codegen=oracle
setting, your program will perform the following at run time:
Execute Oracle-specific application programming interfaces (APIs) that ensure batching support and proper creation and closing of Oracle JDBC statements
Direct calls to Oracle JDBC APIs for registering, passing, and retrieving parameters and result sets
For ISO standard SQLJ applications, the SQLJ run time reads the profiles and creates connected profiles, which incorporate database connections. Then the following occurs each time the application must access the database:
SQLJ-generated application code uses methods in a SQLJ-generated profile-keys class to access the connected profile and read the relevant SQL operations. There is a mapping between SQLJ executable statements in the application and SQL operations in the profile.
The SQLJ-generated application code calls the SQLJ run time, which reads the SQL operations from the profile.
The SQLJ run time calls the JDBC driver and passes the SQL operations to the driver.
The SQLJ run time passes any input parameters to the JDBC driver.
The JDBC driver executes the SQL operations.
If any data is to be returned, then the database sends it to the JDBC driver, which sends it to the SQLJ run time for use by your application.
Note:
Passing input parameters can also be referred to as binding input parameters or binding host expressions. The terms host variables, host expressions, bind variables, and bind expressions are all used to describe Java variables or expressions that are used as input or output for SQL operations.This section presents a side-by-side comparison of two versions of the same sample code, where one version is written in JDBC and the other in SQLJ. The objective of this section is to point out the differences in coding requirements between SQLJ and JDBC. This section covers:
Note:
The particulars of SQLJ statements and features used here are described later in this manual, but this example is still useful here to give you a general idea in comparing and contrasting SQLJ and JDBC. You can look at it again when you are more familiar with SQLJ concepts and features.In the sample, two methods are defined: getEmployeeAddress()
, which selects and returns an employee's address from a table based on the employee's number, and updateAddress()
, which takes the retrieved address, calls a stored procedure, and returns the updated address to the database.
In both versions of the sample code, the following assumptions are made:
A SQL script has been run to create the schema in the database and populate the tables. Both versions of the sample code refer to objects and tables created by this script.
The UPDATE_ADDRESS()
PL/SQL stored function exists, and it updates a given address.
The Connection
object (for JDBC) and default connection context (for SQLJ) have been created previously by the caller.
Exceptions are handled by the caller.
The value of the address argument, addr
, passed to the updateAddress()
method can be null
.
Note:
The JDBC and SQLJ versions of the sample code are only partial samples and cannot run independently. There is nomain()
method in either.Following is the JDBC version of the sample code, which defines methods to retrieve an employee's address from the database, update the address, and return it to the database. Note that the TO DO
items in the comment lines indicate where you might want to add additional code to increase the usefulness of the code sample.
import java.sql.*; import oracle.jdbc.*; /** This is what you have to do in JDBC **/ public class SimpleDemoJDBC // line 7 { //TO DO: make a main that calls this public Address getEmployeeAddress(int empno, Connection conn) throws SQLException // line 13 { Address addr; PreparedStatement pstmt = // line 16 conn.prepareStatement("SELECT office_addr FROM employees" + " WHERE empnumber = ?"); pstmt.setInt(1, empno); OracleResultSet rs = (OracleResultSet)pstmt.executeQuery(); rs.next(); // line 21 //TO DO: what if false (result set contains no data)? addr = (Address)rs.getORAData(1, Address.getORADataFactory()); //TO DO: what if additional rows? rs.close(); // line 25 pstmt.close(); return addr; // line 27 } public Address updateAddress(Address addr, Connection conn) throws SQLException // line 30 { OracleCallableStatement cstmt = (OracleCallableStatement) conn.prepareCall("{ ? = call UPDATE_ADDRESS(?) }"); //line 34 cstmt.registerOutParameter(1, Address._SQL_TYPECODE, Address._SQL_NAME); // line 36 if (addr == null) { cstmt.setNull(2, Address._SQL_TYPECODE, Address._SQL_NAME); } else { cstmt.setORAData(2, addr); } cstmt.executeUpdate(); // line 43 addr = (Address)cstmt.getORAData(1, Address.getORADataFactory()); cstmt.close(); // line 45 return addr; } }
In the getEmployeeAddress()
method, you must pass the Connection
object explicitly.
Prepare a statement that selects an employee's address from the employees
table, based on the employee number. The employee number is represented by a marker variable, which is set with the setInt()
method. Note that because the prepared statement does not recognize the INTO
syntax, you must provide your own code to populate the addr
address variable. Because the prepared statement is returning a custom object, cast the output to OracleResultSet
.
Because the Oracle result set contains a custom object of type Address
, use the getORAData()
method to retrieve it. The Address
class can be created by JPublisher. The getORAData()
method requires a factory object that it can use to create additional custom objects (additional Address
objects in this case) while it retrieves the data to populate them. Use the static factory method Address.getORADataFactory()
to materialize an Address
factory object for the getORAData()
method to use. Because getORAData()
returns a Datum
, cast the output to an Address
object.
Note:
The routine assumes a one-row result set. TheTO DO
items in the comment lines indicate that you must write additional code for the case where the result set contains either no rows or more than one row.Close the result set and prepared statement objects, and then return the addr
variable.
In the updateAddress()
method, you must pass the Connection
object and the Address
object explicitly. The updateAddress()
method passes an Address
object to the database for update and then fetches it back. The actual updating of the address is performed by the UPDATE_ADDRESS()
stored function.
Prepare an Oracle callable statement that takes an Address
object and passes it to the UPDATE_ADDRESS()
stored procedure. To register an object as an output parameter, you must know the SQL type code and SQL type name of the object.
Before passing the addr
address object as an input parameter, the program must determine whether addr
has a value or is null
. Depending on the value of addr
, the program calls different setter methods. If addr
is null
, then the program calls setNull()
. If addr
has a value, then the program calls setORAData()
.
Fetch the return result addr
. Because the Oracle callable statement returns a custom object of type Address
, use the getORAData()
method to retrieve it. The Address
class can be created by JPublisher. The getORAData()
method requires you to use the factory method Address.getORADataFactory
to materialize an instance of an Address
object. Because getORAData()
returns a Datum
object, cast the output to an Address
object.
Close the Oracle callable statement, and then return the addr
variable.
Coding Requirements of the JDBC Version
Note the following coding requirements for the JDBC version of the sample code:
The getEmployeeAddress()
and updateAddress()
definitions must explicitly include the connection object.
Long SQL strings must be concatenated with the SQL concatenation character ("+").
You must explicitly manage resources. For example, close result set and statement objects after using them.
You must cast data types as needed.
You must know the _SQL_TYPECODE
and _SQL_NAME
values of the factory object and any objects that you are registering as output parameters.
NULL
value data must be explicitly processed.
Host variables must be represented by parameter markers in callable and prepared statements.
If you want to reuse statement objects, for example, you want to repeatedly call getEmployeeAddress()
and updateAddress()
, then you must code this appropriately. The Oracle SQLJ and JDBC implementations both support statement caching.
JDBC programs are potentially expensive to maintain. For example, in the preceding code sample, if you add another WHERE
clause, then you must change the SELECT
string. If you append another host variable, then you must increment the index of the other host variables by one. A simple change to one line in a JDBC program might require changes in several other areas of the program.
The SQLJ version of the sample code that defines methods to retrieve an employee's address from the database, update the address, and return it to the database is as follows:
import java.sql.*; /** This is what you have to do in SQLJ **/ public class SimpleDemoSQLJ // line 6 { //TO DO: make a main that calls this public Address getEmployeeAddress(int empno) // line 10 throws SQLException { Address addr; // line 13 #sql { SELECT office_addr INTO :addr FROM employees WHERE empnumber = :empno }; return addr; } // line 18 public Address updateAddress(Address addr) throws SQLException { #sql addr = { VALUES(UPDATE_ADDRESS(:addr)) }; // line 22 return addr; } }
The getEmployeeAddress()
method does not require an explicit Connection
object. SQLJ can use a default connection context instance, which should be initialized somewhere earlier in the application.
The getEmployeeAddress()
method retrieves an employee address according to the employee number. Use standard SQLJ SELECT
INTO
syntax to select an employee's address from the employee
table if the employee number matches the one (empno
) passed in to getEmployeeAddress()
. This requires a declaration of the Address
object (addr
) that will receive the data. The empno
and addr
variables are used as input host variables.
The getEmployeeAddress()
method returns the addr
object.
The updateAddress()
method also uses the default connection context instance.
The address is passed to the updateAddress()
method, which passes it to the database. The database updates the address and passes it back. The actual updating of the address is performed by the UPDATE_ADDRESS()
stored function. Use standard SQLJ function-call syntax to receive the addr
address object returned by UPDATE_ADDRESS()
.
The updateAddress()
method returns the addr
object.
Specific Features of the SQLJ Version of the Code
Note the following features of the SQLJ version of the sample code:
An explicit connection is not required. SQLJ can use a default connection context that has been initialized previously in the application.
No da tatype casting is required.
SQLJ does not require knowledge of _SQL_TYPECODE
, _SQL_NAME
, or factories.
NULL
value data is processed implicitly.
No explicit code for resource management (for example, closing statements or results sets) is required.
SQLJ embeds host variables, in contrast to JDBC, which uses parameter markers.
String concatenation for long SQL statements is not required.
You do not have to register output parameters.
SQLJ syntax is simpler. For example, SELECT INTO
statements are supported and ODBC-style escapes are not used.
You do not have to implement your own statement cache. By default, SQLJ will automatically cache #sql
statements. This results in improved performance, for example, if you repeatedly call getEmployeeAddress()
and updateAddress()
.
Although this manual mainly discusses writing for client-side SQLJ applications, you may find it useful to run SQLJ code in the following scenarios:
From an applet
In the server (optionally running the SQLJ translator in the server as well)
This section covers the following topics:
Because the SQLJ run time is pure Java, you can use SQLJ source code in applets as well as applications. However, there are a few considerations.
See Also:
Oracle Database JDBC Developer's Guide and Reference for applet issues that apply to Oracle JDBC drivers.This section covers the following topics:
The following general considerations apply to the use of SQLJ in applets:
You must package all the SQLJ run time packages with your applet. The packages are:
sqlj.runtime sqlj.runtime.ref sqlj.runtime.profile sqlj.runtime.profile.ref sqlj.runtime.error
Also package the following if you used Oracle customization:
oracle.sqlj.runtime oracle.sqlj.runtime.error
These packages are included with your Oracle installation in one of several run time libraries in the ORACLE_HOME
/lib
directory.
You must specify a pure Java JDBC driver, such as the Oracle JDBC Thin driver, for your database connection.
You must explicitly specify a connection context instance for each SQLJ executable statement in an applet. This is a requirement because you could conceivably run two SQLJ applets in a single browser and, thus, in the same JVM.
See Also:
"Connection Considerations"The default translator setting -codegen=oracle
generates Oracle-specific code. This will eliminate the use of Java reflection at run time and, thus, increase portability across different browser environments.
When end users run your SQLJ applet, classes in their CLASSPATH
may conflict with classes that are downloaded with the applet. Therefore, Oracle recommends that end users clear their CLASSPATH
before running the applet.
The following are some additional considerations regarding the Java environment and use of Oracle-specific features:
SQLJ requires the run-time environment of JDK 1.2 or later versions. Users cannot run SQLJ applets in browsers using JDK 1.0.x or JDK 1.1.x without a plug-in. One option is to use a Java plug-in offered by Sun Microsystems. For information, refer to the following:
Some browsers, such as Netscape Navigator 4.x, do not support resource files with a .ser
extension, which is the extension used by the SQLJ serialized object files that are used for profiles (relevant for ISO standard code only). However, the Sun Microsystems Java plug-in supports .ser
files.
Alternatively, if you do not want to use the plug-in, then the Oracle SQLJ implementation offers the -ser2class
option to convert .ser
files to .class
files during translation.
Note:
This consideration does not apply to the default Oracle-specific code generation, where no profiles are produced.Applets using Oracle-specific features require the Oracle SQLJ run time to work. The Oracle SQLJ run time consists of the classes in the SQLJ run time library file under oracle.sqlj.*
. The Oracle SQLJ runtime.jar
library requires the Java Reflection API, java.lang.reflect.*
. Most browsers do not support the Reflection API or impose security restrictions, but the Sun Microsystems Java plug-in provides support for the Reflection API.
Note:
The term "Oracle-specific features" refers to the use of Oracle type extensions (discussed in Chapter 5, "Type Support") and the use of SQLJ features that require Oracle-specific code generation or, for ISO code generation, require your application to be customized to work against an Oracle Database instance. (For example, this is true of theSET
statement, discussed in Chapter 4, "Basic Language Features".)With ISO standard SQLJ code generation, the following SQLJ language features always require the Java Reflection API, regardless of the version of the SQLJ run time you are using:
The CAST
statement
REF CURSOR
parameters or REF CURSOR
columns being retrieved from the database as instances of a SQLJ iterator
Retrieval of java.sql.Ref
, Struct
, Array
, Blob
, or Clob
objects
Retrieval of SQL objects as instances of Java classes implementing the oracle.sql.ORAData
or java.sql.SQLData
interfaces
Notes:
An exception to the preceding is if you use SQLJ in a mode that is fully compatible with ISO. That is, if you use SQLJ in an environment that complies with J2EE and you translate and run your program with the SQLJ runtime12ee.jar
library, and you employ connection context type maps as specified by ISO. In this case, instances of java.sql.Ref
, Struct
, Array
, Blob
, Clob
, and SQLData
are being retrieved without the use of reflection.
If you use Oracle-specific code generation, then you will eliminate the use of reflection in all of the instances listed.
The preceding issues can be summarized as follows, focusing on users with Internet Explorer and Netscape browsers:
To be able to use Oracle-specific features, distribute your applet with the appropriate SQLJ and JDBC libraries. For a browser that includes JDK 1.2.x or supports a JDK 1.2.x plug-in, use the SQLJ runtime12.jar
and JDBC classes12.jar
libraries available with Oracle Database 11g.
The SQLJ and JDBC versions should match. For example, to use the SQLJ 9.0.0 run time, you must have an Oracle 9.0.0 or earlier JDBC driver.
If you use object types, JDBC 2.0 types, REF CURSORs, or the CAST
statement in your SQLJ statements, then you must adhere to your choice of the following:
Use the default -codegen=oracle
setting when you translate your applet.
Ensure that the browser in which you run supports JDK 1.2 or later and permits reflection.
Run your applet through a browser Java plug-in.
In addition to its use in client applications, SQLJ code can run within a target Oracle database in stored procedures, stored functions, or triggers. Server-side access occurs through an Oracle JDBC driver that runs inside the server itself. Additionally, Oracle Database 11g (and preceding versions) has an embedded SQLJ translator so that SQLJ source files for server-side use can optionally be translated directly in the server.
The two main areas to consider are the following:
Creating SQLJ code for use within the server
Coding a SQLJ application for use within the target Oracle database is similar to coding for client-side use. The issues that exist are due to general JDBC characteristics, as opposed to SQLJ-specific characteristics. The main differences involve connections:
You have only one connection.
The connection is to the database in which the code is running.
The connection is implicit (does not have to be explicitly initialized, unlike on a client).
The connection cannot be closed. Any attempt to close it will be ignored.
Additionally, the JDBC server-side driver used for connections within the server does not support auto-commit mode.
Note:
There is also a server-side Thin driver for connecting to one server from code that runs in another. This case is effectively the same as using a Thin driver from a client and is coded in the same way. Refer "Overview of the Oracle JDBC Drivers" for further information.Translating and loading SQLJ code for server-side use
You can translate and compile your code either on a client or in the server. If you do this on a client, then you can load the class and resource files into the server from your client, either by pushing them from the client using the Oracle loadjava
utility or pulling them in from the server using SQL commands.
Alternatively, you can translate and load in one step using the embedded server-side SQLJ translator. If you load a SQLJ source file instead of class or resource files, then translation and compilation are done automatically. In general, loadjava
or SQL commands can be used for class and resource files or for source files. From a user perspective, .sqlj
files are treated the same as .java
files, with translation taking place implicitly.
See Also:
"Loading SQLJ Source Code into the Server" for information about using the embedded server-side translatorNote:
The server-side translator does not support the SQLJ-codegen
option and generates Oracle-specific code. To use ISO standard code in the server, you must translate on a client and load the individual components into the server. Also note restrictions on interoperability when running code generated with different settings. For more information, refer to "Translating SQLJ Source on a Client and Loading Components" and "Oracle-Specific Code Generation (No Profiles)".The discussion in this book assumes that you are coding manually on a UNIX environment for English-language deployment. However, you can use SQLJ on other platforms and with integrated development environments (IDEs). There is also globalization support for deployment to other languages. This section covers the following topics:
Support for native languages and character encodings by the Oracle SQLJ implementation is based on Java built-in globalization support capabilities.
The standard user.language
and file.encoding
properties of the JVM determine appropriate language and encoding for translator and run-time messages. The SQLJ -encoding
option determines encoding for interpreting and generating source files during translation.
The Oracle SQLJ implementation includes a programmatic API so that it can be embedded in IDEs, such as Oracle JDeveloper 10g. The IDE takes on a role similar to that of the front-end sqlj
script, invoking the translator, semantics-checker, compiler, and customizer (as applicable).
JDeveloper is a Windows-based visual development environment for Java programming. The JDeveloper Suite enables developers to build multitier, scalable Internet applications using Java across the Oracle Internet Platform. The core product of the suite, the JDeveloper IDE, excels in creating, debugging, and deploying component-based applications.
The Oracle JDBC OCI and Thin drivers are included with JDeveloper. The compilation functionality of JDeveloper includes an integrated SQLJ translator so that your SQLJ application is translated automatically as it is compiled.
Information about JDeveloper is available at the following URL:
Note the following if you are using a Microsoft Windows environment instead of a UNIX environment:
This manual uses UNIX syntax. Use platform-specific file names and directory separators, such as "\" on Microsoft Windows, that are appropriate for your platform, because your JVM expects file names and paths in the platform-specific format. This is true even if you are using a shell, such as ksh
, that permits a different file name syntax.
For UNIX, the Oracle SQLJ implementation provides a front-end script, sqlj
, that you use to invoke the SQLJ translator. On Microsoft Windows, Oracle instead provides an executable file, sqlj.exe
. Using a script is not feasible on Microsoft Windows because .bat
files on these platforms do not support embedded equals signs (=) in arguments, string operations on arguments, or wildcard characters in file name arguments.
How to set environment variables is specific to the operating system. There may also be OS-specific restrictions. In Windows 95, use the Environment
tab in the System
control panel. Additionally, because Windows 95 does not support the "=" character in variable settings, SQLJ supports the use of "#" instead of "=" in setting SQLJ_OPTIONS
, an environment variable that SQLJ can use for option settings. Consult your operating system documentation regarding settings and syntax for environment variables, and be aware of any size limitations.
As with any operating system and environment you use, be aware of specific limitations. In particular, the complete, expanded SQLJ command line must not exceed the maximum command-line size, which is 250 characters for Windows 95 and 4000 characters for Windows NT. Consult your operating system documentation.
Refer to the release notes for Windows for additional information.