Business objects, IBM WebSphere Adapter for JDBC

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:

Tables and views
Synonyms and nicknames
Stored procedures and stored functions

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

Batch SQL business objects, which represent a series of user-defined insert, update, and delete statements.
Query business objects, which represent a user-defined SQL query to run against the database.
Wrapper business objects, which allow you group unrelated table and view objects into a single business object, and multiple stored procedures into a single business object.

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

Container business object, which contains the output from a RetrieveAll operation.
ExistsResult business object, which contains the output from an Exists operation.

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:

The database table can have more columns than the corresponding business object has simple attributes; that is, some columns in the database are not represented in the business object. Only those columns needed for your application's processing of the business object must be included in your design.
The business object can have more simple attributes than the corresponding database table has columns; that is, some attributes in the business object are not represented in the database. The attributes that do not have a representation in the database either have no application-specific information, are set with default values, or are parameters for stored procedures or stored functions.
The business object can represent a view that spans multiple database tables. The adapter can use such a business object when processing events triggered by changes to the database, such as Create, Update, and Delete operations. When processing business object requests, however, the adapter can use such a business object only for Retrieve and RetrieveAll requests.

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.

Note: 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".

Note: 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

DateFormat application-specific information to customize Date, Time or Timestamp data types

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 2 shows a table business object in the business object editor. The business object has an attribute for each of the columns in the database table. Because the table has no child business objects, all of the attributes are simple attributes.

Figure 2. A table business object with no child

The structure of a business object in the assembly editor, showing attributes and application-specific information

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

Two business objects in a parent-child relationship, showing the attributes and application-specific information of the parent

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

The structure of an Oracle table business object in the assembly editor showing child attributes

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.

Note: 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.

Table 1. 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

Note: 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 screen capture is described in the surrounding text.

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 screen capture of the sample business object is described in the surrounding text.

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

This figure shows the portion of the application-specific information of a batch SQL statement. The business object has metadata for two BatchSQL statements The first statement has a BatchSQLIndex property with the value 1 and an SQLStatement property that contains the UPDATE SQL statement described in the text above. The second statement has a BatchSQLIndex property with the value 2 and an SQLStatement property that contains the DELETE SQL statement from the earlier text.

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

This figure shows the assembly editor view of a wrapper business object that contains two table business objects. It is described in the surrounding text.

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 you need to use the outbound ApplyChanges operation
When adding business objects to a module created with an earlier version

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

This graphic is described in the surrounding text

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. For more information, 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/v8r0mx/index.jsp .