Oracle8i SQLJ Developer's Guide and Reference Release 8.1.5 A64684-01 |
|
SQLJ supports connection contexts for connecting to different types of database schemas from the same application.
When connecting to different schema types you will typically want to declare one or more connection context classes, as discussed in "Overview of SQLJ Declarations". Each connection context class can be used for a particular type of schema, meaning that all the connections you define using a particular connection context class will use the same set of SQL objects (such as tables, views, and stored procedures). Note, however, that a connection context declaration does not define a type of schema that the connection context class is used for, and it is permissible to use the same connection context class for different schema types.
An example of a schema type is the set of tables and stored procedures used by the Human Resources department. Perhaps they use tables EMPLOYEES
and DEPARTMENTS
and stored procedures CHANGE_DEPT
and UPDATE_HEALTH_PLAN
. Another schema type might be the set of tables and procedures used by the Payroll department, perhaps consisting of the table EMPS
(another table of employees, but different than the one used by HR) and stored procedures GIVE_RAISE
and CHANGE_WITHHOLDING
.
The advantage in tailoring connection context classes to database schemas is in the degree of online semantics-checking that this allows. To avoid semantics errors when doing online checking, all of the SQL objects used in SQLJ statements that use a given connection context class must match SQL objects found in the exemplar schema you provide for online checking of that connection context class. (The exemplar schema is the database connection you provide using the SQLJ translator -user
, -password
, and -url
options. See "Connection Options" for information about these options.)
If you have SQLJ statements that relate to a variety of schemas but use a single connection context class, then the exemplar schema you provide for this connection context class must be very general, containing all of the tables, views, and stored procedures that are used in any of the schemas. Alternatively, if all of the SQLJ statements using a given connection context class use a set of SQL objects belonging to a single schema type, then you can provide a more meaningful exemplar schema which allows more accurate semantics-checking.
Declaring a connection context class results in the SQLJ translator defining a class for you in the translator-generated code. In addition to any connection context classes that you declare, there is always the default connection context class:
sqlj.runtime.ref.DefaultContext
When you construct a connection context instance, you specify a particular schema (username, password, and URL) and a particular session and transaction in which SQL operations will execute. You typically accomplish this by specifying a username, password, and database URL as input to the constructor of the connection context class. The connection context instance manages the set of SQL operations performed during the session.
In each SQLJ statement, you can specify a connection context instance to use, as discussed in "Specifying Connection Context Instances and Execution Context Instances".
The following example shows basic declaration and use of a connection context class, MyContext
, to connect to two different schemas (for typical usage, we will assume these schemas are of the same schema type):
Declaration:
#sql context MyContext;
Executable code:
MyContext mctx1 = new MyContext ("jdbc:oracle:thin@localhost:1521:ORCL", "scott", "tiger", false); MyContext mctx2 = new MyContext ("jdbc:oracle:thin@localhost:1521:ORCL", "brian", "mypasswd", false);
Note that connection context class constructors specify a boolean auto-commit parameter (this is further discussed in "Declaring and Using a Connection Context Class").
Also note that you can connect to the identical schema with different connection context instances. During runtime, however, one connection does not see changes to the database made from the other connection until the changes are committed. In the example above, both mctx1
and mctx2
could connect to scott/tiger
if desired.
The following are examples of situations where you will typically declare one or more connection context classes:
The program in "Example of Multiple Connection Contexts" demonstrates the use of multiple contexts. It uses the default context to access a table of employees and a user-defined context to access employee department information. By using distinct contexts, it is possible for you to store the employee and department information in different schemas or even physically different databases.
For an overview of single connections vs. multiple connections, see "Connection Considerations".
This section gives a detailed example of how to declare a connection context class, then define a database connection using an instance of the class.
A connection context class has constructors for opening a connection to a database schema given any of the following (as with the DefaultContext
class):
String
), username (String
), password (String
), auto-commit (boolean
)
String
), java.util.Properties
object, auto-commit (boolean
)
String
fully specifying connection and including username and password), auto-commit setting (boolean
)
Connection
object only
Notes:
|
The following declaration creates a connection context class:
#sql context OrderEntryCtx <implements clause> <with clause>;
This results in the SQLJ translator generating a class that implements the sqlj.runtime.ConnectionContext
interface and extends some base class (probably an abstract class) that also implements ConnectionContext
. This base class would be a feature of the particular SQLJ implementation you are using.
The implements
clause and with
clause are optional, specifying additional interfaces to implement and variables to define and initialize, respectively. "Declaration IMPLEMENTS Clause" and "Declaration WITH Clause" discuss these.
The following is an example of what the SQLJ translator generates (with method implementations omitted):
class OrderEntryCtx implements sqlj.runtime.ConnectionContext extends ... { public OrderEntryCtx(String url, Properties info, boolean autocommit) throws SQLException; public OrderEntryCtx(String url, boolean autocommit) throws SQLException; public OrderEntryCtx(String url, String user, String password, boolean autocommit) throws SQLException; public OrderEntryCtx(Connection conn) throws SQLException; public OrderEntryCtx(ConnectionContext other) throws SQLException; public static OrderEntryCtx getDefaultContext(); public static void setDefaultContext(OrderEntryCtx ctx); }
Continuing the preceding example, instantiate the OrderEntryCtx
class with the following syntax:
OrderEntryCtx myOrderConn = new OrderEntryCtx (url, username, password, autocommit);
For example:
OrderEntryCtx myOrderConn = new OrderEntryCtx ("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger", true);
This is accomplished in the same way as instantiating the DefaultContext
class using one of its constructors, as discussed in "More About the DefaultContext Class".
Note: You will typically have to register your JDBC driver prior to constructing a connection context instance. See "Driver Selection and Registration for Runtime". |
Recall that the basic SQLJ statement syntax is as follows:
#sql <[<conn><, ><exec>]> { SQL operation };
Specify the connection context instance inside square brackets following the #sql
token. For example, in the following SQLJ statement, the connection object is myOrderConn
from the previous example:
#sql [myOrderConn] { UPDATE TAB2 SET COL1 = :w WHERE :v < COL2 };
This is the same way you might specify instances of DefaultContext
if you require multiple connections but to the same type of database schema (as discussed in "Connection Considerations").
The following is an example of a SQLJ application using multiple connection contexts. It implicitly uses an object of the DefaultContext
class for one type of schema and uses an instance of the declared connection context class DeptContext
for another type of schema.
This example uses the static Oracle.connect()
method to establish a default connection, then constructs an additional connection by using the static Oracle.getConnection()
method to pass another DefaultContext
instance to the DeptContext
constructor. As previously mentioned, this is just one of several ways you can construct a SQLJ connection context instance.
import java.sql.SQLException; import oracle.sqlj.runtime.Oracle; // declare a new context class for obtaining departments #sql context DeptContext; #sql iterator Employees (String ename, int deptno); class MultiSchemaDemo { public static void main(String[] args) throws SQLException { /* if you're using a non-Oracle JDBC Driver, add a call here to DriverManager.registerDriver() to register your Driver */ // set the default connection to the URL, user, and password // specified in your connect.properties file Oracle.connect(MultiSchemaDemo.class, "connect.properties"); // create a context for querying department info using // a second connection DeptContext deptCtx = new DeptContext(Oracle.getConnection(MultiSchemaDemo.class, "connect.properties")); new MultiSchemaDemo().printEmployees(deptCtx); deptCtx.close(); } // performs a join on deptno field of two tables accessed from // different connections. void printEmployees(DeptContext deptCtx) throws SQLException { // obtain the employees from the default context Employees emps; #sql emps = { SELECT ename, deptno FROM emp }; // for each employee, obtain the department name // using the dept table connection context while (emps.next()) { String dname; int deptno = emps.deptno(); #sql [deptCtx] { SELECT dname INTO :dname FROM dept WHERE deptno = :deptno }; System.out.println("employee: " +emps.ename() + ", department: " + dname); } emps.close(); } }
This section discusses how SQLJ implements connection context classes, including the DefaultContext
class, and what noteworthy methods they contain.
As mentioned earlier, the DefaultContext
class and all generated connection context classes implement the ConnectionContext
interface.
Note: Subclassing connection context classes is not permitted in the current SQLJ specification and is not supported by Oracle SQLJ. |
Each connection context class implements the sqlj.runtime.ConnectionContext
interface.
Basic methods specified by the this interface include the following:
close(boolean CLOSE_CONNECTION/KEEP_CONNECTION)
--Releases all resources used in maintaining this connection and closes any open connected profiles. Additionally, it closes the underlying JDBC connection if you pass in CLOSE_CONNECTION
, a static boolean constant of the ConnectionContext
interface. It does not close the underlying JDBC connection if you pass in the boolean KEEP_CONNECTION
, which is also a static constant of ConnectionContext
.
If you do not pass in a parameter, the default is CLOSE_CONNECTION
. If you do not explicitly close a connection context instance, the finalizer will close it with a KEEP_CONNECTION
setting.
Specify one of these constants as follows (assume a connection context instance ctx
):
ctx.close(ConnectionContext.CLOSE_CONNECTION);
For further discussion, see "Closing Shared Connections".
getConnection()
--Returns the underlying JDBC Connection
object for this connection context instance.
getExecutionContext()
--Returns the default ExecutionContext
instance for this connection context instance. For more information, see "Execution Contexts".
In addition to the methods specified and defined in the ConnectionContext
interface, each connection context class defines the following methods:
getDefaultContext()
--This is a static method that returns the default connection context instance for a given connection context class.
setDefaultContext(
Your_Ctx_Class
conn_ctx_instance
)
--This is a static method that defines the default context instance for a given connection context class.
Although it is true that you can use an instance of only the DefaultContext
class as your default connection, it might still be useful to designate an instance of a declared connection context class as the default context for that class, using the setDefaultContext()
method. Then you could conveniently retrieve it using the getDefaultContext()
method of the particular class. This would allow you, for example, to specify a connection context instance for a SQLJ executable statement as follows.
Declaration:
#sql context MyContext;
Executable code:
... MyContext myctx1 = new MyContext(url, user, password, auto-commit); ... MyContext.setDefaultContext(myctx1); ... #sql [MyContext.getDefaultContext()] { SQL operations }; ...
There might be situations where it is useful to implement an interface in your connection context declarations. For general information and syntax, see "Declaration IMPLEMENTS Clause".
You might, for example, want to define an interface that exposes just a subset of the functionality of a connection context class. More specifically, you might want the capability of a class that has getConnection()
functionality, but does not have other functionality of a connection context class.
You can create an interface called HasConnection
, for example, that specifies a getConnection()
method but does not specify other methods found in a connection context class. You can then declare a connection context class but expose only the getConnection()
functionality by assigning a connection context instance to a variable of the type HasConnection
instead of to a variable that has the type of your declared connection context class.
The declaration will be as follows (presume HasConnection
is in package mypackage
):
#sql public context MyContext implements mypackage.HasConnection;
Then you can instantiate a connection instance as follows:
HasConnection myConn = new MyContext (url, username, password, autocommit);
For example:
HasConnection myConn = new MyContext ("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger", true);
A significant feature of SQLJ is the strong typing of connection context classes, with each class typically used with a particular type of schema. This allows SQLJ semantics-checking to verify during translation that you are using your connection context instances correctly in your code. The type of schema specifies such properties as names and privileges associated with tables and views, the datatypes of their rows, and names and definitions of stored procedures.
To use online semantics-checking during translation, provide an example of the type of schema for any particular connection context class. You accomplish this by setting the SQLJ -user
, -password
, and -url
options. These schema examples are sometimes referred to as exemplar schemas. For information about these SQLJ options, see "Connection Options".
During semantics-checking, the translator connects to the specified exemplar schema for a particular connection context class and performs the following:
It is the responsibility of the application developer to pick an exemplar schema that represents the runtime schema in appropriate ways. For example, it must have tables, views, stored functions, and stored procedures with identical names and types, and with privileges set appropriately.
If no appropriate exemplar schema is available during translation for one of your connection context classes, or if it is inconvenient to connect to a schema of that type during translation, then you do not have to specify SQLJ options (-user
, -password
, -url
) for that particular connection context class. In that case, SQLJ statements specifying connection objects of that connection context class are only semantically checked to the extent possible.