Oracle8i SQLJ Developer's Guide and Reference Release 8.1.5 A64684-01 |
|
As described in "Introduction to SQLJ", you can use SQLJ statements for static SQL operations but must use JDBC statements for dynamic SQL operations. There may be situations where your application will require both static and dynamic SQL operations. SQLJ allows you to use SQLJ statements and JDBC statements concurrently and provides interoperability between SQLJ constructs and JDBC constructs.
Two kinds of interactions between SQLJ and JDBC are particularly useful:
For general information about JDBC functionality, see the Oracle8i JDBC Developer's Guide and Reference.
SQLJ allows you to convert in either direction between SQLJ connection context instances and JDBC connection instances.
Note: When converting between a SQLJ connection context and a JDBC connection, bear in mind that the two objects are sharing the same physical database connection. See "About Shared Connections". |
If you want to perform a dynamic SQL operation through a database connection you have established in SQLJ (for example, an operation where the name of the table to select from is not determined until runtime), then you must convert the SQLJ connection context instance to a JDBC connection instance.
Any connection context instance in a SQLJ application, whether an instance of the sqlj.runtime.ref.DefaultContext
class or of a declared connection context class, contains an underlying JDBC java.sql.Connection
instance and a getConnection()
method that returns that Connection
instance. Use the Connection
instance to create JDBC statement objects if you want to use any dynamic SQL operations.
Following is an example of how to use the getConnection()
method.
Imports:
import java.sql.*;
Executable code:
DefaultContext ctx = new DefaultContext ("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger", true); ... (static operations through SQLJ ctx connection context instance) ... Connection conn = ctx.getConnection(); ... (dynamic operations through JDBC conn connection instance) ...
(The connection context instance can be an instance of the DefaultContext
class or of any connection context class that you have declared.)
To retrieve the underlying JDBC connection of your default SQLJ connection, you can use getConnection()
directly from a DefaultContext.getDefaultContext()
call, where getDefaultContext()
returns a DefaultContext
instance that you had previously initialized as your default connection, and getConnection()
returns its underlying JDBC Connection
instance. In this case, because you do not have to use the DefaultContext
instance explicitly, you can also use the Oracle.connect()
method. This method implicitly creates the instance and makes it the default connection.
(See "Connection Considerations" for an introduction to connection context instances and default connections. See "More About the Oracle Class" for information about the Oracle.connect()
method.)
Following is an example.
Imports:
import java.sql.*;
Executable code:
... Connection conn = Oracle.connect( "jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger").getConnection(); ... (dynamic operations through JDBC conn connection instance) ...
Following is a sample method that uses the underlying JDBC connection instance of the default SQLJ connection context instance to perform dynamic SQL operations. The dynamic operations are performed using JDBC java.sql.Connection
, java.sql.PreparedStatement
, and java.sql.ResultSet
objects. (For information about such basic features of JDBC programming, see the Oracle8i JDBC Developer's Guide and Reference.)
import java.sql.*; public static void projectsDue(boolean dueThisMonth) throws SQLException { // Get JDBC connection from previously initialized SQLJ DefaultContext. Connection conn = DefaultContext.getDefaultContext().getConnection(); String query = "SELECT name, start_date + duration " + "FROM projects WHERE start_date + duration >= sysdate"; if (dueThisMonth) query += " AND to_char(start_date + duration, 'fmMonth') " + " = to_char(sysdate, 'fmMonth') "; PreparedStatement pstmt = conn.prepareStatement(query); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { System.out.println("Project: " + rs.getString(1) + " Deadline: " + rs.getDate(2)); } rs.close(); pstmt.close(); }
If you initiate a connection as a JDBC java.sql.Connection
instance but later want to use it as a SQLJ connection context instance (for example, if you want to use it in a context expression to specify the connection to use for a SQLJ executable statement), you can convert the JDBC Connection
instance to a SQLJ connection context instance.
The DefaultContext
class and all declared connection context classes have a constructor that takes a JDBC Connection
instance as input and constructs a SQLJ connection context instance.
For example, presume you instantiated and defined the JDBC Connection
instance conn
and want to use the same connection for an instance of a declared SQLJ connection context class, MyContext
. You can do this as follows:
... #sql context MyContext; ... MyContext myctx = new MyContext(conn); ...
A SQLJ connection context instance and the associated JDBC Connection
instance share the same underlying database connection. As a result, the following is true:
java.sql.Connection
instance from a SQLJ connection context instance (using the connection context getConnection()
method), the Connection
instance inherits the state of the connection context instance. Among other things, the Connection
instance will retain the auto-commit setting of the connection context instance.
Connection
instance (using the connection context constructor that takes a Connection
instance as input), the connection context instance inherits the state of the Connection
instance. Among other things, the connection context instance will retain the auto-commit setting of the Connection
instance. (By default, a JDBC Connection
instance has an auto-commit setting of true
but this can be modified using the setAutoCommit()
method of the Connection
instance.)
Connection
instance, calls to methods that alter session state in one instance will also affect the other instance because it is actually the underlying shared database session that is being altered.
Whether you get a JDBC java.sql.Connection
instance from a SQLJ connection context instance (using the getConnection()
method) or you create a SQLJ connection context instance from a JDBC Connection
instance (using the connection context constructor), you only need to close the connection context instance. By default, calling the close()
method of a connection context instance closes the associated JDBC Connection
instance and the underlying database connection, thereby freeing all resources associated with the connection.
Note, however, that closing the JDBC Connection
instance will not close the associated SQLJ connection context instance. The underlying database connection would be closed, but the resources of the connection context instance would not be freed until garbage collection.
If you want to close a SQLJ connection context instance without closing the associated JDBC Connection
instance (if, for example, the Connection
instance is being used elsewhere, either directly or by another connection context instance), then you can specify the boolean KEEP_CONNECTION
to the close()
method, as follows (presume you have been using a connection context instance ctx
):
ctx.close(ConnectionContext.KEEP_CONNECTION);
If you do not specify KEEP_CONNECTION
, then the associated JDBC Connection
instance is closed by default. You can also specify this explicitly:
ctx.close(ConnectionContext.CLOSE_CONNECTION);
KEEP_CONNECTION
and CLOSE_CONNECTION
are static constants of the ConnectionContext
interface.
If you do not explicitly close a connection context instance, then it will be closed by the finalizer during garbage collection with KEEP_CONNECTION
, meaning the resources of the JDBC Connection
instance would not be freed until released explicitly or by garbage collection.
SQLJ allows you to convert in either direction between SQLJ iterators and JDBC result sets. For situations where you are selecting data in a SQLJ statement but do not care about strongly typed iterator functionality, SQLJ also supports a weakly typed iterator, which you can convert to a JDBC result set.
There are a number of situations where you may find yourself manipulating JDBC result sets. For example, another package may be implemented in JDBC and may provide access to data only through result sets, or may require ResultSetMetaData
information because it is a routine written generically for any kind of result set. Or your SQLJ application may invoke a stored procedure that returns a JDBC result set.
If the dynamic result set has a known structure, it is typically desirable to manipulate it as an iterator to use the strongly typed paradigm that iterators offer.
In SQLJ, you can populate a named or positional iterator object by converting an existing JDBC result set object. This can be thought of as casting a result set to an iterator and the syntax reflects this, as follows:
#sql iter = { CAST :rs };
This binds the result set object rs
into the SQLJ executable statement, converts the result set, and populates the iterator iter
with the result set data.
Following is an example. Presume myEmpQuery()
is a static Java function in a class called RSClass
, with a predefined query that returns a JDBC result set object.
Imports and declarations:
import java.sql.*; ... #sql public iterator MyIterator (String empname, float empsal); ...
Executable code:
ResultSet rs; MyIterator iter; ... rs = RSClass.myEmpQuery(); #sql iter = { CAST :rs }; ... (process iterator) ... iter.close(); ...
This example could have used a positional iterator instead of a named iterator; the functionality is identical.
The following rules apply when converting a JDBC result set to a SQLJ iterator and processing the data:
-warn=nostrict
option setting.)
java.sql.ResultSet
. (The class oracle.jdbc.driver.OracleResultSet
implements this interface, as does any standard result set class.)
public
.
For a complete example of how SQLJ and JDBC can interoperate in the same program, see "Interoperability with JDBC--JDBCInteropDemo.sqlj".
You may also encounter situations where you want to define a query using SQLJ but ultimately need a result set. (SQLJ offers more natural and concise syntax, but perhaps you want to do dynamic processing of the results, or perhaps you want to use an existing Java method that takes a result set as input.)
So that you can convert iterators to result sets, every SQLJ iterator class, whether named or positional, is generated with a getResultSet()
method. This method can be used to return the underlying JDBC result set object of an iterator object.
Following is an example showing use of the getResultSet()
method.
Imports and declarations:
import java.sql.*; #sql public iterator MyIterator (String empname, float empsal); ...
Executable code:
MyIterator iter; ... #sql iter = { SELECT * FROM emp }; ResultSet rs = iter.getResultSet(); ... (process result set) ... iter.close(); ...
The following rules apply when converting a SQLJ iterator to a JDBC result set and processing the data:
You may have a situation similar to what is discussed in "Converting from Named or Positional Iterators to JDBC Result Sets", but where you do not at any time require the strongly typed functionality of the iterator. All you may care about is being able to use SQLJ syntax for the query and then processing the data dynamically from a result set.
For such circumstances, you can directly use the interface sqlj.runtime.ResultSetIterator
to receive query data, and avoid having to declare a named or positional iterator class.
In using ResultSetIterator
instead of a strongly typed iterator, you are trading the strong type-checking of the SQLJ SELECT
operation for the convenience of not having to declare an iterator class.
In using SQLJ statements and ResultSetIterator
functionality instead of using JDBC statements and standard result set functionality, you enable yourself to use the more concise SELECT
syntax of SQLJ.
As discussed in "Iterator Class Implementation and Advanced Functionality", the ResultSetIterator
interface underlies all named and positional iterator classes and specifies the getResultSet()
and close()
methods.
Following is an example of how to use and convert a weakly typed iterator.
Imports:
import sqlj.runtime.*; import java.sql.*; ...
Executable code:
ResultSetIterator rsiter; ... #sql rsiter = { SELECT * FROM table }; ResultSet rs = rsiter.getResultSet(); ... (process result set) ... rsiter.close(); ...
The following rules apply when converting a ResultSetIterator
object to a JDBC result set and processing the data:
ResultSetIterator
object. You must convert it to a result set to access the query data.
ResultSetIterator
object, not the result set. Closing the ResultSetIterator
will also close the result set, but closing the result set will not close the ResultSetIterator
. When interoperating with JDBC, always close the SQLJ entity.
Note:
As |