IBM BPM, V8.0.1, All platforms > Authoring services in Integration Designer > Services and service-related functions > Access external services with adapters > Configure and using adapters > IBM WebSphere Adapters > JDBC > Overview of IBM WebSphere Adapter for JDBC > Technical overview

Business objects

A business object is a structure that consists of data, the action to be performed on the data, and additional instructions, if any, for processing the data. IBM WebSphere Adapter for JDBC uses business objects to represent tables and views in the database as well as the results of database queries, stored procedures, and stored functions. Business objects can also create a hierarchy of objects from your database and group unrelated tables. Your component communicates with the adapter using business objects.


How the adapter uses business objects

An integrated application uses business objects to access a database. The adapter converts the business objects in outbound requests into JDBC API calls to access the database. For inbound events, the adapter converts the data in the events into business objects, which are returned to the application.

The adapter uses business objects to represent the following types of objects in a database:

Some business objects do not represent database objects. These business objects include:

Adapters use some business objects for output. These business objects include:


How data is represented in business objects


For table or view business objects

Each column in the table or view is represented by a simple attribute of the table or view business object. A simple attribute is an attribute that represents a single value, such as a String, Integer, or Date. Other attributes represent a child business object or an array of child business objects.

Simple attributes within the same business object cannot be stored in different database tables; however, the following situations are possible:

A table business object always has a primary key, even if the corresponding database table does not have a primary key. The adapter uses the column specified in the primary key attribute when it retrieves table business objects. If you have defined foreign key reference in the database, the adapter automatically discovers and displays the parent-child relationship between the tables.

For example, consider tables CUSTOMER and ADDRESS, where CUSTOMER is the parent table and ADDRESS is the child table.

If you have defined a foreign key reference from ADDRESS to CUSTOMER in the database, the adapter automatically discovers the parent-child relationship displays the foreign key reference in the Specify the Configuration Properties for 'object' window. If the foreign key reference is from CUSTOMER to ADDRESS, the adapter automatically selects the Single cardinality check box and displays the foreign key reference. If there are multiple foreign key references defined for a table, the adapter generates only one foreign key relationship.

The adapter supports tables that have composite, or multiple primary keys. If a database table has one or more primary keys, the wizard sets the primary key property for those columns in the table business object. If the database table does not have a primary key, the external service wizard prompts you for primary key information when you discover and configure that business object. If there is a composite primary key reference, for instance CUSTOMER (pkey1, pkey2) > ADDRESS (fkey1, fkey2), the adapter associates ADDRESS (fkey1) with CUSTOMER (pkey1) and ADDRESS (fkey2) with CUSTOMER (pkey2).Specify a column that contains unique data, such as a sequence or identity column. Identity columns (known as serial columns in Informix ; JDBC adapter supports both serial and serial8) provide a way for the database to automatically generate a unique numeric value for each row in a table. A table can have a single column that is defined with the identity attribute. Examples of an identity column include order number, employee number, stock number, and incident number. Identity columns can be defined for tables in DB2 , Informix and Microsoft SQL Server only.

When you run the discovery process against a table in either a DB2 or Microsoft SQL Server database, and that table defines a column as an identity column, the generated business object for that table does not include the Unique Identifier attribute of the identity column. In this case, you need to edit the generated business object by adding the attribute to the application-specific information manually. You can do this through the assembly editor in IBM Integration Designer.

You do not need to add the attribute for the Unique Identifier manually if you ran the discovery process against a table in an Informix database. For Informix, the generated business object includes the Unique Identifier attribute of the serial column.

If the business object contains the Date, Time or Timestamp data type, the format of these types can be customized in the DateFormat application-specific information. The date format must follow the patterns defined in java.text.SimpleDateFormat. When an SQL Date, Time or Timestamp has to be converted to string and the other way around, and if you have customized the format for these types in the DateFormat application-specific information, the adapter uses this customized format.

For example, you can specify the date in the dd/MM/yy format and timestamp in the yyyy/MM/dd HH:mm format. If the DateFormat application-specific information is not specified, the adapter uses the default format. The default format for the Date type is "yyyy-MM-dd", Timestamp type is "yyyy-mm-dd hh:mm:ss.fffffffff", and Time type is "HH:mm:ss". During inbound processing, if the primary key is of the Date type, ensure that the application-specific information format specified in the business object for the primary key matches the value entered in the 'Object_Key' field of the WBIA_JDBC_EventStore table.

The format for Timestamp type is defined in the JDBC specification and it does not follow the SimpleDateFormat pattern.

Figure 1. The DateFormat application-specific information with the customized format

Table and view business objects support the Create, Update, Delete, Retrieve, RetrieveAll, Exists, and ApplyChanges outbound operations. When running an Exists operation on a hierarchical table business object, only the top-level business object is queried.

Figure 3.shows a table business object that has one child table business object. The business object has simple attributes for each of the columns in the database table, plus a complex attribute pointing to a child business object.

Figure 3. A table business object with one child business object

For Oracle databases, the adapter supports user-defined or complex data types such as array, table, structure, or nested structure in table business objects. The type name and the child attribute details are automatically discovered and displayed for these types. The adapter processes these data types as child business objects of the table business object.

Figure 4. An Oracle table business object having user-defined or complex types as columns


For stored procedure and stored function business objects

In a business object for a stored procedure or stored function, all the input and output parameters for the stored procedure or stored function have corresponding attributes in the business object. If any of the input or output parameters is of a complex type, such as an array or structure, then the corresponding business object attribute is a child business object type with the child business object containing the attributes of the array or structure. If the stored procedure returns a result set, a child business object is created that contains the attributes of the returned result set.

If the business object contains the Date, Time or Timestamp data type, the format of these types can be customized in the DateFormat application-specific information. The date format must follow the patterns defined in java.text.SimpleDateFormat. When an SQL Date, Time or Timestamp has to be converted to string and the other way around, and if you have customized the format for these types in the DateFormat application-specific information, the adapter uses this customized format.

For example, you can specify the date in the dd/MM/yy format and timestamp in the yyyy/MM/dd HH:mm format. If the DateFormat application-specific information is not specified, the adapter uses the default format. The default format for the Date type is "yyyy-MM-dd", Timestamp type is "yyyy-mm-dd hh:mm:ss.fffffffff", and Time type is "HH:mm:ss". During inbound processing, if the primary key is of the Date type, ensure that the application-specific information format specified in the business object for the primary key matches the value entered in the 'Object_Key' field of the WBIA_JDBC_EventStore table.

The format for Timestamp type is defined in the JDBC specification and it does not follow the SimpleDateFormat pattern.

The business object for stored procedures and stored functions supports the Execute outbound operation.

The sample file below shows the structure of stored procedure business objects. The business objects, ScottStrtValues and ScottStrtValuesStrt, are generated from a stored procedure that has one input type and two output types. One of the output parameters is of the Struct data type. The external service wizard generates a business object, ScottStrtValuesStrt, for the Struct type and adds it as a child object to the parent business object, ScottStrtValues. For the attribute of type Struct in the parent business object, the ChildBOType application-specific information is set to Struct to indicate it is of type Struct. The ChildBOTypeName application-specific information is set to the value of the user-defined Struct type in the database. The following examples show the schema for the stored procedure.


Example of ScottStrtValues business object 

<?xml version="1.0" encoding="UTF-8" ?>
<schema xmlns="http://www.w3.org/2001/XMLSchema" targetNamespace=

"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/scottstrtvalues" xmlns:scottstrtvalues=

"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/scottstrtvalues" xmlns:jdbcasi=

"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata" xmlns:scottstrtvaluesstrt=

"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/scottstrtvaluesstrt">
<import namespace=
"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/scottstrtvaluesstrt" 

schemaLocation="ScottStrtvaluesStrt.xsd"/>
<import namespace="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata" 

schemaLocation="JDBCASI.xsd"/>

<annotation>
<appinfo source="commonj.connector.asi">
<asi:annotationSet xmlns:asi="commonj.connector.asi" asiNSURI=

"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata"/>
</appinfo>
</annotation>
<complexType name="ScottStrtvalues">
<annotation>
<appinfo source="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:JDBCBusinessObjectTypeMetadata xmlns:jdbcasi=

"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:SPName>SCOTT.STRTVALUES</jdbcasi:SPName>
<jdbcasi:MaxNumOfRetRS>0</jdbcasi:MaxNumOfRetRS>
<jdbcasi:ResultSet>false</jdbcasi:ResultSet>
</jdbcasi:JDBCBusinessObjectTypeMetadata>
</appinfo>
</annotation>
<sequence minOccurs="1" maxOccurs="1">
<element name="pkey" type="int" minOccurs="0" maxOccurs="1">
<annotation>
<appinfo source="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:JDBCAttributeTypeMetadata xmlns:jdbcasi=

"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:SPParameterType>IP</jdbcasi:SPParameterType>
</jdbcasi:JDBCAttributeTypeMetadata>
</appinfo>
</annotation>
</element>
<element name="fname" type="string" minOccurs="0" maxOccurs="1">
<annotation>
<appinfo source="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:JDBCAttributeTypeMetadata xmlns:jdbcasi=

"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:SPParameterType>OP</jdbcasi:SPParameterType>
</jdbcasi:JDBCAttributeTypeMetadata>
</appinfo>
</annotation>
</element>
<element name="strt" type="scottstrtvaluesstrt:ScottStrtvaluesStrt" 

minOccurs="0" maxOccurs="1">
<annotation>
<appinfo source="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:JDBCAttributeTypeMetadata xmlns:jdbcasi=

"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:SPParameterType>OP</jdbcasi:SPParameterType>
<jdbcasi:ChildBOType>STRUCT</jdbcasi:ChildBOType>
<jdbcasi:ChildBOTypeName>STRUCT1</jdbcasi:ChildBOTypeName>
</jdbcasi:JDBCAttributeTypeMetadata>
</appinfo>
</annotation>
</element>
</sequence>
</complexType>
</schema>


Example of ScottStrtValuesStrt business object 

<?xml version="1.0" encoding="UTF-8" ?>
<schema xmlns="http://www.w3.org/2001/XMLSchema" targetNamespace=

"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/scottstrtvaluesstrt" xmlns:scottstrtvaluesstrt=

"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/scottstrtvaluesstrt" xmlns:jdbcasi=

"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<import namespace="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata" 

schemaLocation="JDBCASI.xsd"/>

<annotation>
<appinfo source="commonj.connector.asi">
<asi:annotationSet xmlns:asi="commonj.connector.asi" asiNSURI=

"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata"/>
</appinfo>
</annotation>
<complexType name="ScottStrtvaluesStrt">
<annotation>
<appinfo source="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:JDBCBusinessObjectTypeMetadata xmlns:jdbcasi=

"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:SPName>SCOTT.STRTVALUES</jdbcasi:SPName>
</jdbcasi:JDBCBusinessObjectTypeMetadata>
</appinfo>
</annotation>
<sequence minOccurs="1" maxOccurs="1">
<element name="name" type="string" minOccurs="0" maxOccurs="1">
<annotation>
<appinfo source="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:JDBCAttributeTypeMetadata xmlns:jdbcasi=

"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:SPParameterType></jdbcasi:SPParameterType>
</jdbcasi:JDBCAttributeTypeMetadata>
</appinfo>
</annotation>
</element>
<element name="title" type="string" minOccurs="0" maxOccurs="1">
<annotation>
<appinfo source="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:JDBCAttributeTypeMetadata xmlns:jdbcasi=

"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:SPParameterType></jdbcasi:SPParameterType>
</jdbcasi:JDBCAttributeTypeMetadata>
</appinfo>
</annotation>
</element>
<element name="dept_num" type="int" minOccurs="0" maxOccurs="1">
<annotation>
<appinfo source="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:JDBCAttributeTypeMetadata xmlns:jdbcasi=

"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:SPParameterType></jdbcasi:SPParameterType>
</jdbcasi:JDBCAttributeTypeMetadata>
</appinfo>
</annotation>
</element>
</sequence>
</complexType>
</schema>


For query business objects

A business object for a database query defines the SQL statement that performs the query and the parameters that the query requires. The query business object supports the RetrieveAll outbound operation.

As an example, assume a query business object to run the following SELECT statement: 

select C.pkey, C.fname, A.city from customer C, address A     WHERE (C.pkey = A.custid) AND (C.fname like ?)

The question mark (?) indicates an input parameter for the query. A query can have multiple parameters, each indicated in the SELECT statement by a question mark. Table 1.shows the attributes of the sample query business object. The query business object has simple attributes for each column to be extracted, a simple attribute for each parameter, and a "placeholder object" for the WHERE clause of the query, which holds the WHERE clause after parameter substitution.

Attributes of a query business object
Business object attribute Description
pkey Corresponds to database column PKEY in the Customer table
fname Corresponds to database column FNAME in the Customer table
city Corresponds to database column CITY in the Address table
parameter1 The parameter. There is one parameter for each ? (question mark) in the SELECT statement. In a SELECT statement with multiple parameters, subsequent parameters are named parameter2, parameter3, and so on.
jdbcwhereclause A placeholder object for the WHERE clause

If the business object contains the Date, Time or Timestamp data type, the format of these types can be customized in the DateFormat application-specific information. The date format must follow the patterns defined in java.text.SimpleDateFormat. When an SQL Date, Time or Timestamp has to be converted to string and the other way around, and if you have customized the format for these types in the DateFormat application-specific information, the adapter uses this customized format.

For example, you can specify the date in the dd/MM/yy format and timestamp in the yyyy/MM/dd HH:mm format. If the DateFormat application-specific information is not specified, the adapter uses the default format. The default format for the Date type is "yyyy-MM-dd", Timestamp type is "yyyy-mm-dd hh:mm:ss.fffffffff", and Time type is "HH:mm:ss". During inbound processing, if the primary key is of the Date type, ensure that the application-specific information format specified in the business object for the primary key matches the value entered in the 'Object_Key' field of the WBIA_JDBC_EventStore table.

The format for Timestamp type is defined in the JDBC specification and it does not follow the SimpleDateFormat pattern.

The following figure shows the business object for the sample query in the business object editor.

Figure 5. The attributes of a query business object

This figure shows the application-specific information for the query business object example. The SelectStatement application-specific information contains the SELECT statement.

Figure 6. The SELECT statement is saved in the business object application-specific information

For Oracle databases, the adapter supports complex data types such as array, table, structure, or nested structure in the query result of the business object. The adapter does not support these complex types as parameters in batch and query business objects.


For batch SQL business objects

A batch SQL business object defines the INSERT, UPDATE, and DELETE SQL statements that perform the database actions and the parameters that the statements require. The batch SQL business object supports the Execute outbound operation.

As an example, assume a batch SQL business object to run the following INSERT and DELETE statements: 

Insert into customer (pkey,ccode,fname,lname) values(?,?,?,?);
Delete From Customer where pkey=?

Each question mark (?) indicates a parameter for the statement. Each statement in a batch SQL business object can have multiple parameters, each indicated in the statement by a question mark. A batch SQL business object can have multiple statements, each with its own set of parameters. Figure 7.shows the format of the business object for the batch SQL business object with an INSERT and a DELETE statement, each of which has one or more parameters.

Figure 7. A batch SQL business object with two SQL statements

The business object has an attribute for each parameter in each statement, including statement1parameter1, statement2parameter1, and so on. It also has an attribute for the status of each statement, such as statement1status, statement2status, and so on. The statements themselves are stored as application-specific information about the business object, as shown in Figure 8.

Figure 8. The application-specific information of a batch SQL business object


For wrapper business objects

A wrapper business object enables you to manipulate unrelated table and view business objects in a single operation. The wrapper business object supports the Create, Delete, Retrieve, and Update outbound operations.

Figure 9. A wrapper business object that contains two table business objects

The wrapper business object contains a simple attribute for the primary key of each child business object. The name of the field is the string "wrap", followed by the database table name and the column name of the primary key of the table. The wrapper business object also contains a complex attribute for each table business object. The name of the attribute is the table name with the string "obj" appended. The type of the complex attribute is the name of the corresponding table business object.


Business graphs

You can optionally choose, during adapter configuration, to generate a business graph. In version 6.0.2, each top-level business object is contained in a business graph, which includes a verb that an application can use to specify additional information about the operation to be performed. Beginning version 7.0, business graphs are required only in these situations:

If business graphs exist, they are processed, but the verb is ignored for all operations except ApplyChanges.


How business objects are created

You create business objects by using the external service wizard, launched from IBM Integration Designer. The wizard connects to the database, discovers database objects, and displays them to you. You select the database objects for which you want to create business objects.

For example, you specify which schemas you want to examine. In those schemas, you select tables, views, stored procedures and functions, and synonyms and nicknames. In addition, you can create additional business objects.

For example, you can create a business object to represent the results of user-defined SELECT, INSERT, UPDATE, or DELETE statements that are run against the database. The wizard helps you build a hierarchy of business objects, using parent-child relationships and wrappers for unrelated business objects.

After you specify which business objects you want and define the hierarchy of those objects, the wizard then generates business objects to represent the objects that you selected. It also generates other artifacts needed by the adapter.

Figure 10. How business objects are created

In some instances, the wizard cannot completely configure a parent-child relationship. For these relationships, you use the business objects editor, launched from IBM Integration Designer, to modify or complete the definition of a business object hierarchy that was created by the wizard. See the instructions for using the business object editor to modify business objects in the IBM Integration Designer information center at the following link: http://publib.boulder.ibm.com/infocenter/dmndhelp/v8r0m1/index.jsp .

Technical overview


Related tasks:

Selecting and configuring tables, views, and synonyms or nicknames for outbound processing

Selecting and configuring query business objects

Discovering database objects for outbound processing

Discovering database objects for inbound processing

Selecting and configuring tables, views, and synonyms or nicknames for inbound processing


Related reference:

Business object information

Business object attributes