Extensions to data access APIs
If a single data access API does not provide a complete solution for your applications, use WebSphere® Application Server extensions to achieve interoperability between both the JCA and JDBC APIs.
Applications that draw from diverse and complex resource management configurations might require use of both the Java™ Platform, Enterprise Edition (Java EE) Connector Architecture (JCA) API and the Java Database Connectivity (JDBC) API. However, in some cases the JDBC programming model does not completely integrate with the JCA (even though full integration is a foundation of the JCA specification). These inconsistencies can limit data access options for an application that uses both APIs. WebSphere Application Server provides API extensions to resolve the compatibility issues.
For example:
Without the benefit of an extension, applications using both APIs cannot modify the properties of a shareable connection after making the connection request, if other handles exist for that connection. (If no other handles are associated with the connection, then the connection properties can be altered.) This limitation stems from an incompatibility between the connection-configuration policies of the APIs:
The Connector Architecture (JCA) specification supports relaying to the resource adapter the specific properties settings at the time you request the connection (using the getConnection() method) by passing in a ConnectionSpec object. The ConnectionSpec object contains the necessary connection properties used to get a connection. After you obtain a connection from this environment, your application does not need to alter the properties. The JDBC programming model, however, does not have the same interface to specify the connection properties. Instead, it gets the connection first, then sets the properties on the connection.
WebSphere Application Server provides the following extensions to fill in such gaps between the JDBC and JCA specifications:
- WSDataSource interface - this interface extends the javax.sql.DataSource class, and enables a
component or an application to specify the connection properties through the WebSphere Application Server
JDBCConnectionSpec class to get a connection.
- getConnection(JDBCConnectionSpec) - this method returns a connection with the properties specified in the JDBCConnectionSpec class.
- For more information see the
WSDataSource
API documentation topic (as listed in the API documentation index).
- JDBCConnectionSpec interface - this interface extends the
com.ibm.websphere.rsadapter.WSConnectionSpec class, which extends the
javax.resources.cci.ConnectionSpec class. The standard ConnectionSpec interface provides only the
interface marker without any get() and set() methods. The WSConnectionSpec and the
JDBCConnectionSpec interfaces define a set of get() and set() methods used by the WebSphere Application Server run
time. This interface enables the application to specify all the essential connection properties in
order to get an appropriate connection. You can create this class from the WebSphere WSRRAFactory class. For
more information see the
JDBCConnection
API documentation topic (as listed in the API documentation index). - WSRRAFactory class - this is a factory class for the WebSphere Relational Resource Adapter, which allows the user
to create a JDBCConnectionSpec object or other resource adapter related object. For more information
see the
WSRRAFactory
API documentation topic (as listed in the API documentation index). - WSConnection interface - this is an interface that allows users to call WebSphere proprietary methods on
SQL connections; those methods are:
- setClientInformation(Properties props) - See the topic, Example: Setting the client information with the setClientInformation(Properties) API, for more information and examples of setting client information.
- Properties getClientInformation() - This method returns the properties object that is set using setClientInformation(Properties). Note that the properties object returned is not affected by implicit settings of client information.
- WSSystemMonitor getSystemMonitor() - This method returns the SystemMonitor object from the
backend database connection if the database supports System Monitors. The backend database will
provide some connection statistics in the SystemMonitor object. The SystemMonitor object returned is
wrapped in a WebSphere object (com.ibm.websphere.rsadapter.WSSystemMonitor) to shield
applications from dependency on any database vendor code. See
com.ibm.websphere.rsadapter.WSSystemMonitor
Java
documentation for more information. The following code is an example of using the
WSSystemMonitor
class:import com.ibm.websphere.rsadapter.WSConnection; ... try{ InitialContext ctx=new InitialContext(); // Perform a naming service lookup to get the DataSource object. DataSource ds=(javax.sql.DataSource]ctx.lookup("java:comp/jdbc/myDS"); } catch (Exception e) {;} WSConnection conn=(WSConnection)ds.getConnection(); WSSystemMonitor sysMon=conn.getSystemMonitor(); if (sysMon!=null) // indicates that system monitoring is supported on the current backend database { sysMon.enable(true); sysMon.start(WSSystemMonitor.RESET_TIMES); // interact with the database sysMon.stop(); // collect data from the sysMon object } conn.close();
The WSConnection interface is part of the
plugins_root/com.ibm.ws.runtime.jar
file.
Example: Using IBM extended APIs for database connections.
Using the WSDataSource extended API, you can code your JDBC application to define connection properties through an object prior to obtaining a connection. This behavior increases the chances that the application can share a connection with another component, such as a CMP.
If your application runs with a shareable connection that might be shared with other container-managed persistence (CMP) beans within a transaction, it is recommended that you use the WebSphere Application Server extended APIs to get the connection. When you use these APIs, you cannot port your application to other application servers.
You can code with the extended API directly in your JDBC application; instead of using the DataSource interface to get a connection, use the WSDataSource interface. The following code segment illustrates WSDataSource:
import com.ibm.websphere.rsadapter.*;
...
// Create a JDBCConnectionSpec and set connection properties. If this connection
// is shared with the CMP bean, make sure that the isolation level is the same as
// the isolation level that is mapped by the Access Intent defined on the CMP bean.
JDBCConnectionSpec connSpec = WSRRAFactory.createJDBCConnectionSpec();
connSpec.setTransactionIsolation(CONNECTION.TRANSACTION_REPEATABLE_READ);
connSpec.setCatalog("DEPT407");
// Use WSDataSource to get the connection
Connection conn = ((WSDataSource)datasource).getConnection(connSpec);
Example: Using IBM extended APIs to share connections between CMP beans and BMP beans.
Within an application component that accesses data through JDBC objects (such as a bean-managed persistence (BMP) bean), you can use a WebSphere extended API to define connection properties through an object prior to obtaining a connection. This behavior increases the chances that the BMP bean can share a connection with a container-managed persistence (CMP) bean.
If your BMP bean runs with a shareable connection that might be shared with other container-managed persistence (CMP) beans within a transaction, it is recommended that you use the WebSphere Application Server extended APIs to get the connection. When you use these APIs, you cannot port your application to other application servers.
In this case, use the extended API WSDataSource interface rather than the DataSource interface. To ensure that both the CMP and bean-managed persistence (BMP) beans are sharing the same physical connection, define the same access intent profile on both the CMP and BMP beans. Inside your BMP method, you can get the correct isolation level from the relational resource adapter helper class.
package fvt.example;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.ejb.CreateException;
import javax.ejb.DuplicateKeyException;
import javax.ejb.EJBException;
import javax.ejb.ObjectNotFoundException;
import javax.sql.DataSource;
// following imports are used by the IBM extended API
import com.ibm.websphere.appprofile.accessintent.AccessIntent;
import com.ibm.websphere.appprofile.accessintent.AccessIntentService;
import com.ibm.websphere.rsadapter.JDBCConnectionSpec;
import com.ibm.websphere.rsadapter.WSCallHelper;
import com.ibm.websphere.rsadapter.WSDataSource;
import com.ibm.websphere.rsadapter.WSRRAFactory;
/**
* Bean implementation class for Enterprise Bean: Simple
*/
public class SimpleBean implements javax.ejb.EntityBean {
private javax.ejb.EntityContext myEntityCtx;
// Initial context used for lookup.
private javax.naming.InitialContext ic = null;
// define a JDBCConnectionSpec as instance variable
private JDBCConnectionSpec connSpec;
// define an AccessIntentService which is used to get
// an AccessIntent object.
private AccessIntentService aiService;
// AccessIntent object used to get Isolation level
private AccessIntent intent = null;
// Persitence table name
private String tableName = "cmtest";
// DataSource JNDI name
private String dsName = "java:comp/env/jdbc/SimpleDS";
// DataSource
private DataSource ds = null;
// bean instance variables.
private int id;
private String name;
/**
* In setEntityContext method, you need to get the AccessIntentService
* object in order for the subsequent methods to get the AccessIntent
* object.
* Other ejb methods will call the private getConnection() to get the
* connection which has all specific connection properties
*/
public void setEntityContext(javax.ejb.EntityContext ctx) {
myEntityCtx = ctx;
try {
aiService =
(AccessIntentService) getInitialContext().lookup(
"java:comp/websphere/AppProfile/AccessIntentService");
ds = (DataSource) getInitialContext().lookup(dsName);
}
catch (javax.naming.NamingException ne) {
throw new javax.ejb.EJBException(
"Naming exception:" + ne.getMessage());
}
}
/**
* ejbCreate
*/
public fvt.example.SimpleKey ejbCreate(int newID)
throws javax.ejb.CreateException, javax.ejb.EJBException {
Connection conn = null;
PreparedStatement ps = null;
// Insert SQL String
String sql = "INSERT INTO" + tableName + "(id, name) VALUES (?, ?)";
id = newID;
name = "";
try {
// call the common method to get the specific connection
conn = getConnection();
}
catch (java.sql.SQLException sqle) {
throw new EJBException("SQLException caught:" + sqle.getMessage());
}
catch (javax.resource.ResourceException re) {
throw new EJBException(
"ResourceException caught:" + re.getMessage());
}
try {
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
ps.setString(2, name);
if (ps.executeUpdate() != 1) {
throw new CreateException("Failed to add a row to the DB");
}
}
catch (DuplicateKeyException dke) {
throw new javax.ejb.DuplicateKeyException(
id + "has already existed");
}
catch (SQLException sqle) {
throw new javax.ejb.CreateException(sqle.getMessage());
}
catch (CreateException ce) {
throw ce;
}
finally {
if (ps != null) {
try {
ps.close();
}
catch (Exception e) {
}
}
}
return new SimpleKey(id);
}
/**
* ejbLoad
*/
public void ejbLoad() throws javax.ejb.EJBException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
String loadSQL = null;
try {
// call the common method to get the specific connection
conn = getConnection();
}
catch (java.sql.SQLException sqle) {
throw new EJBException("SQLException caught:" + sqle.getMessage());
}
catch (javax.resource.ResourceException re) {
throw new EJBException(
"ResourceException caught:" + re.getMessage());
}
// You need to determine which select statement to be used based on the
// AccessIntent type:
// If READ, then uses a normal SELECT statement. Otherwise uses a
// SELECT...FORUPDATE statement
// If your backend is SQLServer, then you can use different syntax for
// the FOR UPDATE clause.
if (intent.getAccessType() == AccessIntent.ACCESS_TYPE_READ) {
loadSQL = "SELECT * FROM" + tableName + "WHERE id = ?";
}
else {
loadSQL = "SELECT * FROM" + tableName + "WHERE id = ? FOR UPDATE";
}
SimpleKey key = (SimpleKey) getEntityContext().getPrimaryKey();
try {
ps = conn.prepareStatement(loadSQL);
ps.setInt(1, key.id);
rs = ps.executeQuery();
if (rs.next()) {
id = rs.getInt(1);
name = rs.getString(2);
}
else {
throw new EJBException("Cannot load id =" + key.id);
}
}
catch (SQLException sqle) {
throw new EJBException(sqle.getMessage());
}
finally {
try {
if (rs != null)
rs.close();
}
catch (Exception e) {
}
try {
if (ps != null)
ps.close();
}
catch (Exception e) {
}
try {
if (conn != null)
conn.close();
}
catch (Exception e) {
}
}
}
/**
* This method will use the AccessIntentService to get the access intent;
* then gets the isolation level from the DataStoreHelper
* and sets it in the connection spec; then uses this connection
* spec to get a connection which has the specific connection
* properties.
**/
private Connection getConnection()
throws java.sql.SQLException, javax.resource.ResourceException, EJBException {
// get current access intent object using EJB context
intent = aiService.getAccessIntent(myEntityCtx);
// Assume this bean only supports the pessimistic concurrency
if (intent.getConcurrencyControl()
!= AccessIntent.CONCURRENCY_CONTROL_PESSIMISTIC) {
throw new EJBException("Bean supports only pessimistic concurrency");
}
// determine correct isolation level for currently configured database
// using DataStoreHelper
int isoLevel =
WSCallHelper.getDataStoreHelper(ds).getIsolationLevel(intent);
connSpec = WSRRAFactory.createJDBCConnectionSpec();
connSpec.setTransactionIsolation(isoLevel);
// Get connection using connection spec
Connection conn = ((WSDataSource) ds).getConnection(connSpec);
return conn;
}