Database Connection Context 

Use

In SQLJ, database connections are identified by the connection context. A connection context specifies the database, the session and the transaction to be used. In Open SQL/SQLJ, all SQLJ queries or DML statements must use an explicit connection context. This means that each statement must contain an expression designating the connection context object, on which it will be executed.

Activities

A connection context class is a Java class declared using a SQLJ declaration with the following syntax:

#sql <modifiers> context <java class name> ( with '(' dataSource '=' <java string literal> ')' )? ';

 

The SQLJ translator substitutes this connection context declaration with the declaration of a specific Java connection context class. This Java class implements the interface sqlj.runtime.ConnectionContext. The generated connection context class contains static variables. Therefore, a connection context may be declared only as a global class or as a static inner class.

There are two types of a database connection context:

·        The URL connection context has constructors that allow instantiating a new connection context from a database URL.

·        The DataSource connection context allows creating a connection context object from a DataSource.

Typically, if the connection context object is omitted from an SQLJ clause, the clause uses a default connection context. However, in Open SQL/SQLJ a default connection context is not supported. You must specify an explicit connection context for every executable SQLJ statement.

 

Creating a Connection Context with Data Source

If the declaration of a connection context contains a with DataSource clause that specifies the value for a dataSource, the connection context is referred to as a connection context with DataSource. Such a connection context class is associated with the DataSource, which can be found in the JNDI registry under the given name. The default constructor creates an instance of this connection context class that contains a JDBC connection to the associated DataSource.

If your SQLJ sources are part of a Web or an EJB project, you should include a resource reference for the relevant DataSource in the deployment descriptor of the component. The procedure is the same as described in Getting a Connection to the Database. In this case, you use the prefix java:comp/env/ in the lookup of the DataSource.

#sql context SysCtx with (dataSource = "java:comp/env/TST");

 

The following example illustrates how you specify a DataSource in case you do not enter a resource reference:

#sql context SysCtx with (dataSource = "jdbc/TST");   //    ...      SysCtx sysCtx = new SysCtx();      #sql [sysCtx] { DELETE FROM dbtab WHERE key = 17 };

Connection Context with DataSource.A connection context class SysCtx is declared and is associated to the DataSource that can be found under the JDNI name "jdbc/TST". An instance variable sysCtx of class SysCtx is declared. The default constructor of the class SysCtx looks up the DataSource registered under the JNDI name "jdbc/TST". It obtains a new connection from this DataSource. The DELETE statement is executed on this connection.

Creating an URL Connection Context

If the declaration of a connection context does not contain a with DataSource clause, the connection context is referred to as a URL connection context. An URL connection context class allows constructing a new instance from a JDBC connection, from another SQLJ connection context, or from an URL.

In J2EE Engine, connections must be obtained using a DataSource that JDBC Connector Service provides. Only standalone Java applications may obtain connections directly from a JDBC driver.

#sql context MyCtx;

Declaration of a connection context class. A connection context class MyCtx is declared. The SQLJ translator replaces this declaration with the definition of a Java class MyCtx.

To use a connection context class, first you must declare and instantiate an instance variable of this class.

String url = "jdbc:vendor:myDB"; String user = "john"; String password = "secret"; MyCtx myCtx = new MyCtx(url, user, password, false);

Instantiation of a connection context. An instance myCtx of the connection context class MyCtx is constructed from an URL. The auto commit mode is switched off.

The connection context for a particular executable SQLJ statement is specified in square brackets following the #sql token.

#sql [myCtx] { DELETE FROM tab WHERE field = :key };

Using an explicit connection context. The DELETE statement is executed on the connection context myCtx.

Constructors of Connection Context Class

All generated connection context classes <java class name> have constructors with the following signatures:

·        Constructor that allows instantiating a new connection context object, which shares the given JDBC connection conn:

public <java class name> '(' java.sql.Connection conn ')'  throws java.sql.SQLException ';'

 

·        Constructor that allows to instantiate a new connection context object, which shares the JDBC connection underlying the SQLJ connection context other.

public <java class name> '(' sqlj.runtime.ConnectionContext other ')'  throws java.sql.SQLException ';'

 

Additionally to these constructors that allow sharing a given database connection, other constructors are generated that open a database connection upon invocation. The signature of these additional constructors depends on whether the connection context is an URL connection context or a connection context with a DataSource.

Constructors of DataSource Connection Context Class

A generated connection context class associated with the DataSource <data source> will additionally have two constructors with the following signatures:

·        A constructor that looks up the DataSource associated with the name <data source> in the JNDI registry. A connection context is instantiated that uses the connection returned by the getConnection() method of the DataSource

public <java class name> '(' ')' throws java.sql.SQLException ';'

 

·        A constructor that looks up the DataSource associated with the name <data source> in the JNDI registry. A connection context is instantiated that uses the connection returned by the getConnection(user, password) method of the data source.

public <java class name> '(' String user ',' String password ')' throws java.sql.SQLException';'

 

This constructor cannot be used in J2EE Engine. User and password of a DataSource are specified when registering the DataSource in the JDBC Connector Service.

 

The following example illustrates the use of these constructors:

public class DataSourceCtx implements        sqlj.runtime.ConnectionContext {       public DataSourceCtx(java.sql.Connection conn) throws java.sql.SQLException [...]         public DataSourceCtx(sqlj.runtime.ConnectionContext other) throws java.sql.SQLException [...]         public DataSourceCtx() throws java.sql.SQLException [...]         public DataSourceCtx(java.lang.String url,                       java.lang.String user) throws java.sql.SQLException [...]         public static final java.lang.String dataSource = "jdbc/TST";         [...]    }

Constructors of generated Data Source Connection Context. A connection context class declared by the declaration "#sql public context DataSourceContext with (dataSource = "jdbc/TST")" has constructors with the signatures listed above. The name of the DataSource is generated into the connection context class as a public static final attribute.

Constructors of URL Connection Context Class

A generated connection context class without data source additionally has three constructors with the following signatures.

In the J2EE engine, connections must be obtained through the JDBC Connector Service. The following constructors can be used only by standalone Java applications.

The additional constructors have the following signatures:

·        In the following constructor, urlspecifies the URL of a database, user and password specify the database user and password to be used and autoCommit denotes whether the connection shall be opened in auto commit mode.

public <java class name> '(' String url ',' String user ',' String password ',' boolean autoCommit ')' throws java.sql.SQLException ';'

 

·        The following constructor can be used if no user and password are required to connect to the database.

public <java class name> '(' String url ',' boolean autoCommit ')' throws java.sql.SQLException ';'

 

·        In the following constructor, info is a Propertiesobject that contains a list of name-value-pairs that provide additional information needed to open a connection – typically the user and the password.

public <java class name> '(' String url ',' Properties info ',' boolean autoCommit ')'   throws java.sql.SQLException ';'

 

The following example illustrates the use of these constructors:

 

class UrlCtx implements sqlj.runtime.ConnectionContext {    public UrlCtx(java.sql.Connection conn) throws java.sql.SQLException [...]      public UrlCtx(sqlj.runtime.ConnectionContext other) throws java.sql.SQLException [...]      public UrlCtx(java.lang.String url,               java.lang.String user,               java.lang.String password,               boolean autoCommit) throws java.sql.SQLException [...]      public UrlCtx(java.lang.String url,               java.util.Properties info,               boolean autoCommit) throws java.sql.SQLException [...]      public UrlCtx(java.lang.String url,               boolean autoCommit) throws java.sql.SQLException [...]      [...] }

Constructors of generated URL Connection Context. A connection context class declared by the declaration "#sql context UrlContext" has constructors with the signatures listed above.

 

See also:

 

Execution Context