Implement custom query transactions for IBM InfoSphere Master Data Management Server

Customizing and extending MDM Server


IBM InfoSphere Master Data Management Server (MDM Server) provides a complex data model for party, account, and product master data, and a large number of services (known as transactions) for querying and modifying master data records. A variety of extension mechanisms are supported to allow the data model and transaction behavior to be customized, or to add completely new transactions if required.

MDM Server includes MDM Server Workbench, tooling that supports development of extensions to MDM Server. The workbench allows you to define the desired data model and transactions and generate the code required to implement the MDM Server extensions. For simple data model extensions, the generated code may meet your requirements without additional work. But if you require new transactions, you will need to customize the generated code to implement the transaction behavior.

Implementing transactions requires knowledge of the MDM Server API and frameworks, as well as general Java™ technology, J2EE, and SQL programming experience.

Learn the basic concepts needed to design new MDM Server transactions with a focus on implementing new queries. Step-by-step instructions are given for building three example transactions that illustrate simple techniques for implementing custom queries.


In this tutorial, you will learn:

  • The different transaction interface styles
  • The different transaction implementation styles
  • How to define new query transactions using the MDM Workbench
  • How to implement a query transaction as a business proxy
  • A technique for implementing simple queries on custom entities with static SQL
  • How to implement simple search transactions on custom entities with dynamic SQL


You need to be familiar with MDM Server and the MDM Server Workbench tooling, know how to use the workbench to develop simple data model extensions, and have experience of Java programming using the Eclipse development environment.

System requirements

To follow along, you will need an MDM Server development environment. This tutorial was developed using MDM Server Workbench V9.0.2 installed on Rational® Software Architect V7.5.5, with WebSphere® Application Server V6.1 and DB2®.

If you are using an earlier version of MDM Server, the same concepts and techniques are applicable, but some of the workbench features described here may not be available to you.

Implementing a custom query transaction

MDM Server provides several hundred transactions to query and modify master data. If you have a business requirement for a query that is not provided by any of the existing transactions and is not possible to achieve the requirement by customizing any of the existing transactions using the mechanisms supported by MDM Server, it may be appropriate to implement a new query transaction.

This might be because you have extended the MDM Server data model to support new types of record or because you need to query a combination of records not supported by any existing transaction.

To implement a new query transaction:

  1. Understand the requirements
  2. Design the transaction interface
  3. Decide how the transaction will be implemented
  4. Use the MDM Server Workbench to generate skeleton code for the transaction
  5. Customize the generated code to meet the requirements
  6. Deploy and test the new transaction

Choosing the transaction interface style

MDM Server supports two styles of transaction interfaces: Inquiry and Txn. This choice affects the format of the transaction request message and the way the transaction is implemented.

An Inquiry-style transaction accepts a number of string arguments, defined as a fixed set of named parameters.

An example of such a transaction is getPerson. To invoke getPerson using an XML over RMI message, the request XML looks like this:

Listing 1. getPerson RMI XML request
<TCRMService  xmlns:xsi="" \
        	<tcrmParam name="partyId">871122346130007978</tcrmParam>
        	<tcrmParam name="InquiryLevel">0</tcrmParam>

The parameters are partyId (the primary key of the party to return) and InquiryLevel, which indicates the amount of detail to return.

To invoke getPerson using the web service interface, with the same values, the request message looks like Listing 2 (omitting the SOAP envelope).

Listing 2. getPerson web service XML request

The Inquiry style should only be used for queries with a small number of parameters, where you will not be likely to modify the interface frequently. In general, the Txn style is considered best practice for new transactions. A Txn-style transaction accepts a single request object that defines the transaction parameters. The request object can have as many fields as required, including nested child objects.

An example of such a transaction is searchPerson. To invoke searchPerson using an XML over RMI message, the request XML might look like Listing 3.

Listing 3. searchPerson RMI XML request
<TCRMService xmlns:xsi="" \

To invoke searchPerson using the web service interface, with the same values, the request message would look like Listing 4 (omitting the SOAP envelope).

Listing 4. searchPerson web service XML request

The Inquiry-style interface is only used for query transactions, while the Txn-style interface is always used for transactions that add and modify master data records and can also be used for queries.

Deciding how to implement the transaction

MDM Server supports two ways of implementing a transaction: as a Business Proxy class or as a Component method. Which you choose is not dependent on the interface style.

To understand the difference, a brief introduction to the architecture of the MDM Server transaction framework is required. The framework has three distinct layers, each of which can contribute to the implementation of a transaction.

Figure 1. Transaction processing layers
Screenshot shows layers: Business Proxy, Controller, Component
Screenshot shows layers: Business Proxy, Controller, Component

An MDM Server transaction like addPerson is implemented by a Controller class, which in turn calls one or more Component classes. When you call addPerson, by default, the base MDM Server Business Proxy class is used and it just delegates the transaction execution to the Party Controller class. It is possible to customize the behavior of the addPerson transaction by providing your own custom Business Proxy class and associating it with addPerson.

To implement a custom transaction, you can contribute a new Business Proxy class or new Controller and Component classes (you would never do both).

A Business Proxy class can include whatever Java code you like and also invoke other transactions at the Controller level. It should never directly call Component classes.

Component and Controller classes have methods corresponding to transactions and usually the Controller method just calls an equivalent Component method. For custom transactions implemented this way, you should ignore the Controller class and not add custom code to it so that the Controller and Component methods behave exactly the same way. Note that this cannot be assumed for MDM Server transactions; you will see different behavior from the Component-level addPerson method from that of the addPerson transaction.

Component methods can include whatever Java code you like and also call other Component methods.

The most common way of implementing a new transaction is as a Business Proxy. A Business Proxy is used to implement new transactions that mainly need to call existing MDM Server transactions, perhaps modifying the request or response. A Business Proxy is implemented in a single Java class.

An example of a situation where a Business Proxy would be the best way to implement a new query is where you can get the required function by calling existing transactions and combining the results into a single combined response.

For example, you might want to look up a party record and also return associated party demographics data, using the transactions getPerson and getPartyDemographicsByType.

The Component method implementation style actually means providing Controller and Component classes, but the transaction behavior is coded in a method in the Component class. This style is appropriate when the transaction implementation is closely related to your data model extensions or you want to call the new transaction from a behavior extension or other Component transaction. Implementation at the Component level allows you to make use of the MDM Server framework to implement the query, and although this style requires more Java classes, the workbench will generate all the skeleton code for you. You may then need to edit the generated classes to add the logic for the new query. In some situations, either approach could be used, in which case, use whichever seems most appropriate or easiest to implement. The following sections will include examples of both approaches.

Generating skeleton code with the MDM Server Workbench

Here, we will step through building a set of example query transactions using the MDM Workbench, which will be further developed in the following sections. To follow along, you need a development environment set up for extending MDM Server:

  1. The first step is to create a Hub Module project to contain the extensions. From the workbench menu, select File > New > Project … > Hub Module Project.
  2. Enter a name for the project and the base Java package name to use for generated code. A namespace URI for generated web services is automatically filled in for you, but you may want to change it.
  3. If this is the first module project you have created, you must also enter a Hub base name (this is used for the generation of XSD and properties files) and the database schema name.
  4. Press Finish to create the new project with those settings. The project is created, and the module model is open for editing.
  5. Click on the Model tab to start defining the extensions.
  6. For this example, define a new entity named XVehicle to hold information about vehicles owned by a party. In addition to the standard primary key field, the fields in this entity will be:
    • licenseNumber— A string to hold the license plate number for this vehicle.
    • vehicleType— A type code identifying the type of vehicle.
    • owningParty— A reference to the Party who owns this vehicle.
  7. In order to define the vehicleType type code, you also need to define a new code table XVehicleType.

At this point, the model should look like this:

Figure 2. Example data model
Screenshot shows Explorer view of vehicle info
Screenshot shows Explorer view of vehicle info

The workbench supports five types of transaction coding patterns:

  1. Get Record
  2. Add Record
  3. Update Record
  4. Inquiry
  5. Txn

If you define a new entity, then default get, add, and update transactions are defined for it. The Add Record and Update Record transaction styles are only used for the default add and update transactions, and cannot be created in any other way.

Defining a custom Get Record transaction

The default get transaction — which queries the entity by its primary key — is a Get Record transaction. You can create additional Get Record transactions for a new entity, but you cannot create them for out-of-the-box MDM Server entities.

Get Record transactions are Inquiry-style, Component-implemented query transactions that select entity records by querying on one or more fields of that entity with a fixed SQL statement. For this style of transaction, the workbench will generate all the code for a basic working implementation and you can easily customize the generated SQL to fine-tune the query.

The Get Record style is convenient for simple query transactions on new entities. For queries and searches that operate on out-of-the-box MDM Server entities like Person, or that require more complex or dynamic SQL, you should create an Inquiry- or Txn-style transaction instead.

For XVehicle, define a new Get Record transaction to query vehicles by the license number field.

Right-click on XVehicle and select New > Get Record from the menu. Fill in the settings as shown below.

Figure 3. getVehiclesByLicense
Screenshot shows getVehiclesByLicense; check Multiple records returned, Query parameters: licenseNumber
Screenshot shows getVehiclesByLicense; check Multiple records returned, Query parameters: licenseNumber

Defining a custom Inquiry transaction

Inquiry and Txn transactions can be added to a new persistent entity, transient data object or a folder. Where they are placed in the model does not make any difference to the behavior. You should just define them where it seems logical and convenient.

As the names suggest, the difference between Inquiry and Txn transactions is the interface style.

Next, define a new Inquiry transaction, which is going to return details of a party and the vehicles they own. To do so, you first need to define a new transient data object to contain the response data, which will be a single Party record together with multiple vehicle records. Follow these steps to create the transient data object:

  1. Right-click on the VehicleInfo folder and select New > Transient Data Object.
  2. Name the new object PartyAndVehicles.
  3. Right-click on the PartyAndVehicles object, select New > Containment from the menu.
  4. Set the containment name to Party and select Party as the contained entity.
  5. Add another Containment to PartyAndVehicles.
  6. Set this containment name to be vehicles, the contained entity to be XVehicle and check the Many flag.

The model should now look like this:

Figure 4. PartyAndVehicles
Explorer view on left shows vehicles selected; on right are      properties of the selected element
Explorer view on left shows vehicles selected; on right are properties of the selected element

Now the Inquiry transaction can be defined:

  1. Right-click on the VehicleInfo folder and select New > Inquiry Transaction.
  2. Name the transaction getPartyAndVehicles.
  3. Set the response type to be PartyAndVehicles.
  4. Set the implementation to be Business Proxy.
  5. Right-click on getPartyAndVehicles and select New > Parameter from the menu.
  6. Set the parameter name to partyId and the type to Long.

The model now looks like Figure 5.

Figure 5. getPartyAndVehicles
Screenshot shows getPartyAndVehicles selected
Screenshot shows getPartyAndVehicles selected

An Inquiry can have any number of parameters (although it can cause problems at runtime if you have a lot of parameters, and I would recommend using the Txn style if you need to pass more than a handful of arguments), which define the arguments to the transaction. Although you can specify the types of the parameters in the model, they are always passed to the transaction as strings.

The getPartyAndVehicles transaction will use the partyId parameter to firstly get the Party record with that primary key, then get the vehicle records associated with that party. To make this easier, define another Get Record transaction (getVehiclesByParty), which will query vehicle records by partyId.

Defining a custom Txn transaction

Next define a search transaction for vehicle records. This is going to be a Txn-style transaction, and the request object will be a transient data object.

The fields to search on will be owning party name, owning party type (Person or Organization), license number, and vehicle type code.

Follow these steps to create the transient data object:

  1. Right-click on the VehicleInfo folder and select New > Transient Data Object.
  2. Name the new object XVehicleSearchRequest.
  3. Add the attribute partyName to the object, with type string.
  4. Add the attribute partyType to the object, with type string.
  5. Add the attribute licenseNumber to the object, with type string.
  6. Add the type code vehicleType to the object, referencing the XVehicleType code table.

To save typing, the fields licenseNumber and vehicleType can be copied from XVehicle and pasted in to the new object.

The model now looks like Figure 6.

Figure 6. XVehicleSearchRequest
Image shows XVehicldSearchRequest selected in explorer

Now define the search transaction:

  1. Right-click on the XVehicle entity and select New > Txn Transaction.
  2. Name the new transaction searchVehicle.
  3. Set the Action Category to be View to indicate that this is a query. (Txn transactions are also used for adds and updates.)
  4. Set the request type to be the XVehicleSearchRequest object.
  5. Multiple records returned should be checked.
  6. Set the response type to be XVehicle.
  7. The implementation type should be Component (this is the default).

The model now looks like Figure 7.

Figure 7. searchVehicle
Image shows Txn transaction pane with properties of searchVehicle
Image shows Txn transaction pane with properties of searchVehicle

The model is now fully defined, and code can be generated. Validate the model to check that no information is missing, then click on Generate implementation to generate skeleton code.

The following sections look in detail at each of the example query transactions and their implementation.

Implementing a query transaction as a Business Proxy

In the example model, the transaction getPartyAndVehicles was implemented as a Business Proxy. The implementation needs to call getParty and the new transaction (getVehiclesByParty), put all the returned records into a PartyAndVehicles object, and return that as the response.

When a Business Proxy transaction is defined in the model, skeleton code for the Business Proxy is generated into the Java package .compositeTxn. A single Java class is generated for each transaction, with the class name based on the transaction name.

For the example, the class is GetPartyAndVehiclesCompositeTxnBP.

A Business Proxy class extends the base class com.dwl.base.requestHandler.DWLTxnBP. The transaction logic should be coded within the following method.

Listing 5. Business Proxy execute method signature
public Object execute(Object inputObj) throws BusinessProxyException

The actual object passed in as the argument will depend on the transaction interface style. It will be one of:

  • com.dwl.base.requestHandler.DWLTransactionInquiry for Inquiry style
  • com.dwl.base.requestHandler.DWLTransactionPersistent for Txn-style transactions that modify the database
  • com.dwl.base.requestHandler.DWLTransactionSearch for Txn-style queries

The object returned by the method should be an instance of com.dwl.tcrm.common.TCRMResponse.

To call other transactions from the Business Proxy, you should create instances of DWLTransactionInquiry, DWLTransactionPersistent, or DWLTransactionSearch and pass them to the MDM Server framework for execution. This ensures that the transaction executes in just the same way as it would if called directly and that the framework is involved at the right points to handle transaction audit logging, validation, and other platform services.

To implement the example getPartyAndVehicles transaction, you need to get the transaction parameter values (in this case, one string, which is the partyId), as shown below.

Listing 6. Extract inquiry parameters
* @generated NOT
   public Object execute(Object inputObj) throws BusinessProxyException {
   DWLTransactionInquiry inputTxnObj = (DWLTransactionInquiry) inputObj;
   DWLControl control = inputTxnObj.getTxnControl();
   // Extract the request parameters. These will appear in the order
   // supplied.
   Vector parameters = inputTxnObj.getStringParameters();
   String partyId = (String)parameters.get(0);

Note: Remember to mark the method @generated NOT as soon as you start editing.

Now that you have the partyId, the next step is to call getParty. The Transaction Reference Guide has information on the required parameters for each transaction. In this case, the parameters are partyId, PartyType, and InquiryLevel. partyId you have. PartyType you don't know, but it is an optional parameter, so null is allowed. InquiryLevel determines the amount of detail returned; set this to 0 to get just the basic party record.

Listing 7. Call getParty
// Handle transaction "getParty"
Vector getPartyInput = new Vector();

// Prepare a new DWLTransactionInquiry instance.
DWLTransactionInquiry getPartyRequest = new DWLTransactionInquiry();

// Invoke the "getParty" transaction.
DWLResponse getPartyResponse = (DWLResponse)

Note: All parameters are passed as strings even if they are actually numbers. The order of the parameters must be correct.

If the call to getParty was successful, the next step is to call getVehiclesByParty. This requires one argument, which is the partyId again.

Listing 8. Call getVehiclesByParty
// Handle transaction "getVehiclesByParty"
Vector getVehiclesByPartyInput = new Vector();

// Prepare a new DWLTransactionInquiry instance.
DWLTransactionInquiry getVehiclesByPartyRequest = new

// Invoke the "getVehiclesByParty" transaction.
DWLResponse getVehiclesByPartyResponse = (DWLResponse)

Next, an instance of the object PartyAndVehicles must be constructed to contain the returned Party and Vehicle records.

Listing 9. Create response object
PartyAndVehiclesBObj mainOutput = new PartyAndVehiclesBObj();
// add party record
if( getPartyResponse != null ){
   	mainOutput.setTCRMPartyBObj( \
(TCRMPartyBObj) 					getPartyResponse.getData() );
// add vehicle records
if( getVehiclesByPartyResponse != null ){
    Vector<XVehicleBObj> getVehiclesByPartyOutput =\
 	(Vector<XVehicleBObj>) getVehiclesByPartyResponse.getData();
    if( getVehiclesByPartyOutput != null ){
    	for( XVehicleBObj vehicle: getVehiclesByPartyOutput ){

Finally, the response can be constructed and returned.

Listing 10. Return response
// Construct the response object.
DWLStatus outputStatus = new DWLStatus();
TCRMResponse outputTxnObj = new TCRMResponse();
return outputTxnObj;

For simplicity, I have left out all the error-handling code here (see Download for the full source code).

Implementing a Txn-style interface transaction is similar. The primary difference is that the argument to the execute method would be an instance of DWLTransactionSearch or DWLTransactionPersistent, instead of DWLTransactionInquiry. From this, you can acquire the request BObj by calling getTxnTopLevelObject().

The request BObj has an associated DWLControl object, representing the control header for the request. When you construct any new BObj, call setControl() on it with the DWLControl from the original request.

Customizing a Get Record transaction

Quite a lot of code is generated for a Get Record transaction, so it may appear complicated, but actually, this is the simplest way to implement a query against a new entity.

The generated code is executable without any customization. If you deploy the code generated for the VehicleInfo model and add some vehicle records, you can try running the getXVehicle and getVehiclesByLicense transactions.

The default get transaction (getXVehicle) is used to query records by primary key and is not usually customized.

The generated implementation for the query getVehiclesByLicense will return all the records that exactly match the passed license number value. It is fairly simple to customize this type of query if required. You can freely modify the query SQL to anything you like, without changing any other code, provided you observe the following rules:

  • The arguments must be kept the same in type and order, but you can change how they are used in the SQL.
  • The columns in the result set are mapped into fields in the result object. Don't change the order or add or remove result columns. Provided the mapping to the result object isn't affected, you could derive the result column in a different way.

The most likely kind of customization is to change the WHERE clause to use the arguments differently. To do so, you need to edit the generated InquiryData class for the entity that defines the Get Record transaction. In this case, this is the class XVehicleInquiryData in the VehicleInfo project. All the InquiryData classes are generated into the .entityObject package.

Two SQL statements are generated for each Get Record transaction; you can find them in static fields defined in the InquiryData class. For example, in XVehicleInquiryData, you should see:

Listing 11. Generated SQL
 * <!-- begin-user-doc -->
 * <!-- end-user-doc -->
 * @generated
 static final String getVehiclesByLicenseSql = "SELECT r.XVehiclepk_Id XVehiclepk_Id, 
 r.license_Number license_Number, \
r.vehicle_Type vehicle_Type, r.owning_Party owning_Party,
 LAST_UPDATE_TX_ID FROM XVEHICLE r WHERE r.license_Number = ? ";

There is also a similar field named getVehiclesByLicenseHistorySql.

The History SQL is used for the query when inquireAsOfDate is included in the request header, in order to query the history records instead of the live records. If you don't need to support history queries, you can ignore it.

A very simple change to the getVehiclesByLicense query will allow it to support wildcards in the license number, which will make it more useful.

Change the field getVehiclesByLicenseSql as shown below.

Listing 12. Customized SQL
   * <!-- begin-user-doc -->
   * <!-- end-user-doc -->
   * @generated NOT
  static final String getVehiclesByLicenseSql = "SELECT r.XVehiclepk_Id XVehiclepk_Id, 
  r.license_Number license_Number, \
r.vehicle_Type vehicle_Type, r.owning_Party owning_Party,
  1. In the WHERE clause, change = to LIKE. This allows wildcards to be used in the argument. And in the comment above the field, change the @generated annotation to @generated NOT. This will preserve the change you have made when code is generated from the model again.
  2. Save the file.
  3. Now go back to the VehicleInfo model and generate code again. This re-generates the XVehicleInquiryDataImpl class from the XVehicleInquiryData interface.

Each data class in the .entityObject package has a corresponding DataImpl class, which is the pureQuery-generated code that implements the SQL statements. Anytime you change the data interfaces, you need to make sure that the corresponding DataImpl class is re-generated. Running code generation from the model will do this.

Implementing a search transaction with dynamic SQL

The final, and most complex, example transaction to implement is searchVehicles. This is a Component-method Txn-style transaction that is going to use dynamic SQL to perform a search over vehicle records.

The search arguments are a party name (person's last name or organization name), party type (P or O), license number, and vehicle type code. Party name or license number must be supplied. Party type must be given if party name is used, and is ignored otherwise. Vehicle type is an optional argument. In its response, searchVehicles will return a list of vehicle records (XVehicleBObj).

If the record you are searching for contains a lot of data, and the search would often return a large number of results, you might want to return a transient data object containing summary information instead of the full record to avoid the overhead of serializing and deserializing data that would never be used by the client.

In this case, the vehicle record is small, so it is appropriate to return it directly as the search result. This design will also simplify the implementation because the generated code for querying vehicle records can be reused.

Implementation of a Component-method transaction begins with the Component class — in this case, VehicleInfoComponent in the .component Java package.

Every Component-method transaction modelled under the VehicleInfo folder is implemented by this class. For each transaction, you will see that two methods have been generated — in this case:

Listing 13. Component-method signatures
public DWLResponse searchVehicle(XVehicleSearchRequestBObj \
theBObj) throws DWLBaseException
public DWLResponse handleSearchVehicle(XVehicleSearchRequestBObj \
theBObj) throws Exception {

The implementation of the transaction logic should always be coded in the handle method, which is called by the MDM Server framework after the request object has been validated.

Only skeleton code will have been generated for the handleSearchVehicle method. Copy the implementation of the handleGetVehiclesByLicense method and paste it into handle SearchVehicles, then edit the code to match Listing 14.

Listing 14. handleSearchVehicle method body
public DWLResponse handleSearchVehicle(XVehicleSearchRequestBObj \
theBObj) throws Exception {
   DWLStatus status = new DWLStatus();
   DWLResponse response = createDWLResponse();
   DWLControl control = theBObj.getControl();
   // create BObjQuery
BObjQuery bObjQuery = getBObjQueryFactory().createXVehicleBObjQuery(
XVehicleBObjQuery.SEARCH_VEHICLES, control); // pass query parameters to BObjQuery bObjQuery.setParameter("licenseNumber", theBObj.getLicenseNumber(), \ java.sql.Types.VARCHAR); bObjQuery.setParameter("partyName", theBObj.getPartyName(), java.sql.Types.VARCHAR); bObjQuery.setParameter("partyType", theBObj.getPartyType(), java.sql.Types.VARCHAR); bObjQuery.setParameter("vehicleType", theBObj.getVehicleTypeType(), \ java.sql.Types.BIGINT); // ask framework to handle pagination boolean considerForPagination = PaginationUtils
.considerForPagintion(XVehicleBObj.class.getName(), control); control.setConsiderForPagintionFlag(considerForPagination); // run the query List list = bObjQuery.getResults(); // handle the results if (list.size() == 0) { return null; } Vector vector = new Vector(); for (Iterator it = list.iterator(); it.hasNext();) { XVehicleBObj o = (XVehicleBObj); // fill in type code values postRetrieveXVehicleBObj(o, "0", "ALL", control); vector.add(o); if (o.getStatus()==null) { o.setStatus(status); } response.addStatus(o.getStatus()); } response.setData(vector); return response; }

Note: Remember to mark the method @generated NOT before you start editing.

As it is, this code won't compile since the constant XVehicleBObjQuery.SEARCH_VEHICLES has not been defined yet, and you may have noticed that there is no SQL in sight, so some further work is needed.

The Component method delegates the actual query execution to an object of type BObjQuery. At:

BObjQuery bObjQuery = getBObjQueryFactory().createXVehicleBObjQuery(		
  XVehicleBObjQuery.SEARCH_VEHICLES, control);

bObjQuery will actually be an instance of XVehicleBObjQuery, which is a generated class located in the .bobj.query package. It is this class that determines the SQL to be executed and how the results are handled.

To construct a BObjQuery, you need to provide a constant indicating the type of query to be performed. Declare the new constant XVehicleBObjQuery.SEARCH_VEHICLES in the XVehicleBObjQuery class: public static final String SEARCH_VEHICLES = "SEARCH_VEHICLES";. The actual value does not matter, so long as it is unique in this class.

For the other generated query transactions, you won't see any SQL in the BObjQuery class. That's because the default behavior, handled by the base class GenericBObjQuery, is to get the SQL from the InquiryData interface class located in the .entityObject package. The pureQuery-generated DataImpl class handles executing the SQL and converting the results into Java objects.

For the new search transaction, it is necessary to build SQL dynamically instead of using static SQL from the XVehicleInquiryData interface class, so you are going to have to override how GenericBObjQuery operates. The first task is to figure out what SQL is required to implement the search and how to construct it dynamically.

In the XVehicleBObjQuery class, define the following static strings from which the required SQL can be pieced together.

Listing 15. Search SQL fragments
static final String baseXVehicleSql = "SELECT DISTINCT \
r.XVehiclepk_Id XVehiclepk_Id, r.license_Number license_Number,
r.vehicle_Type vehicle_Type, r.owning_Party owning_Party, \

static final String personNameFrom = ", PERSONNAME n";
static final String orgNameFrom = ", ORGNAME n";
static final String personNameWhere = " r.owning_Party = \
static final String orgNameWhere = " r.owning_Party = n.CONT_ID  AND n.ORG_NAME LIKE ?";
static final String licenseWhere = " r.license_Number LIKE ?";
static final String vehicleTypeWhere = " r.vehicle_Type = ?";

The result column's definition in baseXVehicleSQL is copied from XVehicleInquiryData.getXVehicleSQL. This is important because it allows you to reuse the generated pureQuery result handling code from the getXVehicle query to handle the search results.

Now to set up the SQL statement, override the base class method getSQLStatement() as follows.

Listing 16. getSQLStatement
protected String getSQLStatement() throws BObjQueryException {
   // override just the SEARCH_VEHICLES query
   if( queryName.equals(SEARCH_VEHICLES)){
    	StringBuffer buf = new StringBuffer();
      // get the parameter values
    	SQLParam partyType = (SQLParam) namedParams.get("partyType");
    	SQLParam partyName = (SQLParam) namedParams.get("partyName");
	SQLParam licenseNumber = (SQLParam)
namedParams.get("licenseNumber"); SQLParam vehicleType = (SQLParam) namedParams.get("vehicleType"); // if partyName is supplied if( partyType.getValue() != null
&& partyName.getValue() != null ){ if( partyType.getValue().equals("P")){ buf.append(personNameFrom); } else if( partyType.getValue().equals("O")){ buf.append(orgNameFrom); } } // start WHERE clause buf.append(" WHERE"); // count parameters to SQL int paramCount = 0; // if partyName is supplied if( partyType.getValue() != null
&& partyName.getValue() != null ){ if( partyType.getValue().equals("P")){ buf.append(personNameWhere); } else if( partyType.getValue().equals("O")){ buf.append(orgNameWhere); } // set positional parameter setParameter(paramCount, partyName.getValue()); paramCount++; // append parameter index to SQL, like ?1 buf.append( paramCount ); } if( licenseNumber.getValue() != null ){ if( paramCount == 1){ // append AND if there is already a parameter buf.append(" AND"); } buf.append(licenseWhere); // set positional parameter setParameter(paramCount, licenseNumber.getValue()); paramCount++; // append parameter index to SQL, like ?1 buf.append( paramCount ); } if( vehicleType.getValue() != null ){ buf.append(" AND"); buf.append(vehicleTypeWhere); // convert string to long Long vehicleTpCd = Long.valueOf((String)vehicleType.getValue()); // set positional parameter setParameter(paramCount, vehicleTpCd); paramCount++; // append parameter index to SQL, like ?1 buf.append( paramCount ); } return buf.toString(); } // all other queries, default behavior return super.getSQLStatement(); }

Notice in this code how numbers are inserted in the SQL to tell pureQuery where to use the parameters.

Finally, make sure that the results will be handled correctly. You can get the existing generated code to convert the result set into Java objects by telling the framework to handle the results of this new query in the same way as the standard get by primary key transaction. So long as the new query returns the same result columns, this will work just fine.

Override the method provideRowHandler, as shown in Listing 17.

Listing 17. proveRowHandler
protected RowHandler provideRowHandler(Class queryInterfaceClass) \
throws BObjQueryException {
    if( queryName.equals(SEARCH_VEHICLES)){
    	Object query;
	try {
	   // get the row handler for getXVehicle()
	   query = DataAccessFactory.getInquiry(queryInterfaceClass);
	   return DataAccessFactory.getRowHandler(query, XVEHICLE_QUERY);
	} catch (ServiceLocatorException e) {
         throw new BObjQueryException(e);
    // for all other queries, default behavior
    return super.provideRowHandler(queryInterfaceClass);

The basic implementation of the search transaction is done, although additional code could be written to validate the request parameters and to handle errors.

To convert the result set into Java objects yourself, you should override provideSQLStatement() instead of getSQLStatement() and add your result handling code to the ResultSetProcessor class instead of overriding provideRowHandler().


In this tutorial, you have seen how to:

  • Implement a query transaction as a Business Proxy.
  • Use a Get Record transaction to implement simple queries on a custom entity.
  • Implement a simple search transaction for a custom entity.

Downloadable resources

Related topics


Sign in or register to add and subscribe to comments.

Zone=Information Management
ArticleTitle=Implement custom query transactions for IBM InfoSphere Master Data Management Server