Oracle8i SQLJ Developer's Guide and Reference Release 3 (8.1.7) Part Number A83723-01 |
|
SQLJ supports the concept of connection contexts, allowing strongly typed connections for use with different sets of SQL entities. You can think of a connection context as being associated with a particular set of SQL entities such as tables, views, and stored procedures. SQLJ lets you declare additional connection context classes so that you can use each class for connections that use a particular set of SQL entities. Different instances of a single connection context class will typically not use the same physical entities or connect to the same schema, but will at least use sets of entities with the same names and datatypes.
Note: For an overview of connection basics, focusing on situations where you are using just a single set of SQL entities and a single connection context class, see "Connection Considerations". |
If your application uses different sets of SQL entities, then you will typically want to declare and use one or more additional connection context classes, as discussed in "Overview of SQLJ Declarations". Each connection context class can be used for a particular set of interrelated SQL entities, meaning that all the connections you define using a particular connection context class will use tables, views, stored procedures, and so on, that have the same names and use the same datatypes.
An example of a set of SQL entities 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 set of SQL entities 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 the stored procedures GIVE_RAISE
and CHANGE_WITHHOLDING
.
The advantage in tailoring connection context classes to sets of SQL entities is in the degree of online semantics-checking that this allows. Online checking verifies that all the SQL entities appearing in SQLJ statements that use a given connection context class match SQL entities found in the exemplar schema used during translation. An exemplar schema is a database account that SQLJ connects to for online checking of all the SQLJ statements that use a particular connection context class. You provide exemplar schemas to the translator through the SQLJ command-line -user
, -password
, and -url
options. (See "Connection Options" for information about these options.) An exemplar schema might or might not be the same account your application will use at runtime.
If you have SQLJ statements that use a broad and perhaps unrelated group of SQL entities, but you use only a single connection context class for these statements, then the exemplar schema you provide must be very general. It must contain all the tables, views, and stored procedures used throughout all the statements. Alternatively, if all the SQLJ statements using a given connection context class use a tight, presumably interrelated, set of SQL entities, then you can provide a more specific exemplar schema that allows more thorough and meaningful 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 (user name, password, and URL) and a particular session and transaction in which SQL operations will execute. You typically accomplish this by specifying a user name, 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 a Connection Context Instance for a SQLJ Clause".
The following example shows basic declaration and use of a connection context class, MyContext
, to connect to two different schemas. For typical usage, assume these schemas include a set of SQL entities with common names and datatypes.
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 "More About Declaring and Using a Connection Context Class").
In addition, note that you can connect to the same schema with different connection context instances. In the example above, both mctx1
and mctx2
could specify scott/tiger
if desired. During runtime, however, one connection context instance would not see changes to the database made from the other until the changes are committed. The only exception to this would be if both connection context instances were created from the same underlying JDBC connection instance. (One of the constructors of any connection context class takes a JDBC connection instance as input.)
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
), user name (String
), password (String
), auto-commit (boolean
)
String
), java.util.Properties
object, auto-commit (boolean
)
String
fully specifying connection and including user name and password), auto-commit setting (boolean
)
Connection
or OracleConnection
)
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 the ConnectionContext
interface. 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. All connection context classes, including DefaultContext
, have the same constructor signatures.
Note: You typically must 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 context instance is myOrderConn
from the previous example:
#sql [myOrderConn] { UPDATE TAB2 SET COL1 = :w WHERE :v < COL2 };
In this way, you can specify an instance of either the DefaultContext
class or any declared connection context class.
It is advisable to close all connection context instances when you are done. Each connection context class includes a close()
method, as discussed for the DefaultContext
class in "Closing Connections".
In closing a connection context instance that shares the underlying connection with another connection instance, you might want to keep the underlying connection open. See "Closing Shared Connections".
The following is an example of a SQLJ application using multiple connection contexts. It implicitly uses an instance of the DefaultContext
class for one set of SQL entities, and uses an instance of the declared connection context class DeptContext
for another set of SQL entities.
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.
Each connection context class implements the sqlj.runtime.ConnectionContext
interface.
Basic methods specified by 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. It might or might not close the underlying JDBC connection, depending on whether CLOSE_CONNECTION
or KEEP_CONNECTION
is specified. These are static boolean constants of the ConnectionContext
interface.
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 strong typing of connections, with each connection context class typically used for operations on a particular set of interrelated SQL entities. This doesn't mean that all the connection entities instances of a single class use the same physical entities, but that they use entities that have the same properties, such as names and privileges associated with tables and views, datatypes of their rows, and names and definitions of stored procedures. This strong typing allows SQLJ semantics-checking to verify during translation that you are using your SQL operations correctly, with respect to your database connections.
To use online semantics-checking during translation, provide a sample schema (that includes an appropriate set of SQL entities) for each connection context class. These sample schemas are referred to as exemplar schemas. Provide exemplar schemas through the SQLJ -user
, -password
, and -url
options. (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 accomplishes 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 names and datatypes that match what are used in your SQL operations, and with privileges set appropriately.
If no appropriate exemplar schema is available during translation for one of your connection context classes, then you need not specify SQLJ translator options (-user
, -password
, -url
) for that particular connection context class. In that case, SQLJ statements specifying connection objects of that connection context class are semantically checked only to the extent possible.
The JDBC 2.0 extended API specifies the use of DataSource
s and JNDI as a portable alternative to the current DriverManager
mechanism for obtaining JDBC connections. It permits database connections to be established through a JNDI name lookup. This name is bound to a particular database and schema prior to program runtime through a javax.sql.DataSource
object, typically installed through a GUI JavaBeans deployment tool. The name can be bound to different physical connections without any source code changes simply by rebinding the name in the directory service.
SQLJ uses the same mechanism to create connection context instances in a flexible and portable way. Data sources can also be implemented using a connection pool or distributed transaction service, as defined by the JDBC 2.0 extended API.
In SQLJ it is natural to associate a connection context class with a logical schema, in much the same way that a DataSource
name serves as a symbolic name for a JDBC connection. Combine both concepts by adding the DataSource
name to the connection context declaration.
#sql context EmpCtx with (dataSource="jdbc/EmpDB");
Any connection context that you declare with a dataSource
property, such as EmpCtx
above, provides the following new constructors:
public EmpCtx()
--Looks up the DataSource for "jdbc/EmpDB
" and then calls the getConnection()
method on it to obtain a connection.
public EmpCtx(String user, String password)
--Looks up the DataSource
for "jdbc/EmpDB
" and then calls the getConnection(user,password)
on it to obtain a connection.
public EmpCtx(ConnectionContext ctx)
--Delegates to ctx
to obtain a connection.
Any connection context declared with a dataSource
property, such as EmpCtx
above, will omit the following DriverManager
-based constructors:
public EmpCtx(Connection conn)
public EmpCtx(String url, String user, String password, boolean autoCommit)
public EmpCtx(String url, boolean autoCommit)
public EmpCtx(String url, java.util.Properties info, boolean autoCommit)
public EmpCtx(String url, boolean autoCommit)
Unlike the DriverManager
-based constructors they replace, the new DataSource
-based constructors do not include an explicit auto commit parameter. They always use the auto commit mode defined by the data source.
Data sources are configured to have a default auto commit mode depending on the deployment scenario. For example, data sources in the server and middle tier typically have auto commit off; those on the client may have it on. However, it is also possible to configure data sources with a specific auto commit setting. This permits data sources to be configured for a particular application and deployment scenario. Contrast this with JDBC URLs that may specify only a single database/driver configuration.
Programs can verify and possibly override the current auto commit setting with the JDBC connection that underlies their connection context.
.
If the SQLJ program accesses the default connection context, and the default context has not been set, then the SQLJ runtime will use the SQLJ default data source to establish its connection. The SQLJ default data source is bound to the name:
jdbc/defaultDataSource
This mechanism provides a portable means to define and install a default JDBC connection for the default SQLJ connection context.
For your program to use data sources, you must supply the packages javax.sql.*
and javax.naming.*,
and an InitialContext
provider in your Java environment. The latter is required to obtain the JNDI context in which the SQLJ runtime can look up the DataSource
object.
Typically, you would use DataSource
s in a JDK 1.2 environment with the Java Extension classes, or in a J2EE environment. However, you can also use DataSource
s under JDK 1.1.x with the Java extension classes.
All the SQLJ runtime libraries support DataSource
s. However, if you use the runtime12ee.zip
you always need to have javax.sql.*
and javax.naming.*
in your CLASSPATH
or the runtime will not load. By contrast, the other libraries--runtime.zip
, runtime11.zip
, and runtime12.zip
--use reflection to retrieve DataSource
objects.
|
![]() Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|