5 Creating Business Services That Call Database Operations
This chapter contains the following topics:
-
Section 5.2, "Creating a Query Database Operation Business Service"
-
Section 5.3, "Creating an Insert Database Operation Business Service"
-
Section 5.4, "Creating an Update Database Operation Business Service"
-
Section 5.5, "Creating a Delete Database Operation Business Service"
5.1 Understanding Database Operations
Database operations include query, insert, update, and delete. Business services that publish insert, update, and delete database operations should be exposed for staging tables only. Staging tables are Z files (interface tables) that mimic JD Edwards EnterpriseOne tables. Some examples of Z files are F0101Z2 Address Book, F03012Z1 Customer Master, and F0401Z1 Supplier Master. Instead of directly updating a JD Edwards EnterpriseOne database table, data is updated to the appropriate Z file, where batch processes validate the data before updating the database. If you are not using a Z file, you should call a business function to process the data so that proper data validation can be implemented and data integrity maintained.
Many of the rules for business services that call database operations are the same as the rules for business services that call business functions, but some exceptions and differences exist. The exceptions and differences are discussed in this chapter for each of the different types of operations.
5.1.1 Data Types
The data types for the internal value objects for database operations include a long data type as well as all of the data types that are available for business function calls. You use the long data type in a database operation to show how many rows were updated, inserted, or deleted.
This table shows the data types for published value objects that expose database operations:
| Published Value Object Data Type | Usage |
|---|---|
| java.lang.String | Use for string and char fields. |
| java.util.Calendar | Use for all JDEDate and UTIME fields in JD Edwards EnterpriseOne. |
| java.lang.Integer | Use for MathNumeric fields defined with 0 decimals, for example, mnAddressNumber and mnShortItemNumber. |
| java.lang.BigDecimal | Use for MathNumeric fields defined with >0 decimals, for example, mnPurchaseUnitPrice. |
| java.lang.Boolean | Use for char fields specified only as true/false or 0/1 Boolean fields. |
| long | Use only in response value object for number of rows returned, number of rows inserted, number of rows updated, number of rows deleted, as returned from the database. |
This table shows the data types for internal value objects that expose database operations:
| Internal Value Object Data Type | Usage |
|---|---|
| oracle.e1.bssvfoundation.util.MathNumeric | Use for all fields declared as numeric in JD Edwards EnterpriseOne. |
| java.lang.Integer | Use for JD Edwards EnterpriseOne ID fields. |
| java.util.Date | Use for all JDEDate fields. |
| java.util.GregorianCalendar | Use for UTIME fields in JD Edwards EnterpriseOne. |
| long | Use only in response value object for number of rows inserted, number of rows updated, number of rows deleted, as returned from the database. |
5.1.1.1 Database Exceptions
The code that runs the database operation is generated within a try/catch block and catches a DBServiceException. The business service creates a new E1Message that returns database errors for data dictionary error item 005FIS. When you use the business service foundation code for E1Message, you can create a new message and use the sLineSeparator constant to take advantage of text substitution within the E1Message. The following sample code shows substituting the view name for one parameter and the exception text for the other. Without text substitution, the E1 DD Error Item Description reads:
Table - &1,&2
This code sample shows using text substitution:
"Exception in thread "main" oracle.e1.bssvfoundation.exception.BusinessServiceException: Error: Table/View - F0101Z2 Error during database operation: [DUPLICATE_KEY_ERROR] Duplicate key error obtained for table F0101Z2., at oracle.e1.bssv.JPR01002.AddressBook StagingManager.insertAddressBookStaging (AddressBookStagingManager.java:78) at oracle.e1.bssv.JPR01002.AddressBookStagingManager.insertAddress BookStaging(AddressBookStagingManager.java:39) at oracle.e1.bssv.JTR87011.AddressBookStagingTest.testInsertAddress BookZTable1Record(AddressBookStagingTest.java:71) at oracle.e1.bssv.JTR87011.AddressBookStagingTest.main(AddressBook StagingTest.java:110"
This sample shows the code that is generated by business service foundation:
private static final String QUERY_VIEW = "V0101XPI";
...
try {
//get dbService from context
IDBService dbService = context.getDBService();
//execute db select operation
resultSet = dbService.BSSVDBSelect(context, connection,
"V0101XPI", IDBService.DB_BSVW, selectDistinct,
maxReturnedRows, selectFields, sortOrder,
whereClause);
} catch (DBServiceException e) {
//take some action in response to the database exception
returnMessages.addMessage(new E1Message(context,
"005FIS",
QUERY_VIEW +
E1Message.sLineSeparator+e.getMessage()));
}
5.2 Creating a Query Database Operation Business Service
The query database operation uses the Database wizard Select operation over a table or business view to retrieve records from JD Edwards EnterpriseOne.
5.2.1 Published Value Object for Query
The published interface for a select query database operation requires an input value object and an output value object.
5.2.1.1 Naming Conventions
The naming convention for an input value object is to use the verb get to preface the type of data to retrieve, for example, GetAddressBook. The naming convention for an output value object is to use the verb show to preface the type of data retrieved, for example, ShowAddressbook.
5.2.1.2 Data Types and Structure
The input value object for a query database operation represents a where clause for the query. The output value object for a query database operation returns the query results in an array.
This code sample shows the structure for the show value object:
public class ShowAddressBook extends MessageValueObject implements
Serializable {
private AddressBook addressBook[];
...
}
5.2.1.3 Error Handling
Any warnings that occurred during business service processing are included with the results in the show value object. If an error occurs during processing, the error is returned to the published business service, and the published business service throws an exception. If no results are returned, a message, without an array of records, is returned.
If an error occurs in a utility that is called during the mapping from the published to internal value object, processing should be stopped and the error returned to the published business service, which can throw an exception. For example, if the Entity Processor fails to find entity ID when tax ID is passed in, the query will not process and an error will be returned to the published business service.
5.2.1.4 Class Diagram
The following class diagram shows the published business service objects for GetAddressBook:
Figure 5-1 Published business service, GetAddressBook, class diagram.
Description of "Figure 5-1 Published business service, GetAddressBook, class diagram."
5.2.2 Internal Value Object for Query
The internal value object for a query database operation contains two components, the where fields and the result fields.
The names that you use for variables in the internal value object are important because the generated code uses these names when calling the get and set methods for these objects.
This code sample shows the structure for the internal value object:
public class InternalGetAddressBook extends ValueObject{
private InternalGetAddressBookWhereFields queryWhereFields =
new InternalGetAddressBookWhereFields();
private ArrayList queryResults = null;
...
}
In the preceding code sample, the variables are named queryResults and queryWhereFields. The queryResults variable represents an array list that contains InternalShowAddressBook type objects. The InternalShowAddressBook value object extends InternalGetAddressBookWhereFields. In the code sample, no additional fields are added to the InternalShowAddressBook value object. However, more fields could be returned from the query than were allowed in the where clause.
This class diagram shows the business service objects for GetAddressBook:
Figure 5-2 Business service, GetAddressbook, class diagram.
Description of "Figure 5-2 Business service, GetAddressbook, class diagram."
5.2.3 Empty Where Clause and Max Rows Returned
Because some tables are too large to return all records without causing significant performance degradation, the recommended practice is to write a select statement that prevents empty where clauses or one that does not select all records. Code that is generated by the wizard follows this recommendation. When you create a query database operation, you must decide whether to allow an empty where clause. If you decide that an empty where clause is appropriate for a particular query, you must modify the generated code to accommodate the empty where clause.
You must include a MaxRowsReturned business service property for all query database operations. This business service property contains the maximum number of rows to be returned to the caller from the selected resultSet variable. The business service property value is passed to the database select statement for processing. If an exception is caught while the system retrieves the business service property, the business service should stop all processing and create an E1MessageList object to pass the exception to the published business service.
Business services interpret a value of 0 (zero) in the business service property to mean return all rows. You must add code to check whether the value returned is zero, and if so, pass a CONSTANT: DBService.DB_FETCH_ALL to the database select call instead of the actual value retrieved. If zero is passed to the select call, an exception will be thrown.
This code sample shows how to check for zero:
//Call access property constants for Max Query Rows to be returned.
//create long variable so it can be passed to bsfn call
//initialize to 1 in the event, the business service property
//call fails.
long maxReturnedRows = 0;
//Call to return Business Service Property - if fails to
//retrieve value, use default and continue.
try{
maxReturnedRows = Long.parseLong
(ServicePropertyAccess.getSvcPropertyValue(context,
SVC_PROPERTY_QUERY_MAX_ROWS));
//interpret property value of zero as "return all rows".
//Need to send constant to database call.
if (maxReturnedRows==0){
maxReturnedRows = DBService.DB_FETCH_ALL;
}
}
The MaxRowsReturned value does not eliminate the need to check for a null where clause. On a large table, the entire table is selected for processing regardless of how many records are returned to the caller. Because the select statement processes the entire table, performance can be affected.
5.3 Creating an Insert Database Operation Business Service
The insert database operation enables you to add information to a table or business view. You use the Insert database operation in the Database wizard to create an insert business service.
5.3.1 Published Value Object for Insert
The published interface for an insert database operation uses an input value object and an output value object.
5.3.1.1 Naming Conventions
The naming convention for an input value object is to use the verb insert to preface the type of data to be processed; for example, InsertAddressBookStaging. The naming convention for an output value object is to use the verb phrase ConfirmInsert to preface the information that is processed, for example, ConfirmInsertAddressBookStaging.
5.3.1.2 Data Types and Structure
The input value object for an insert database operation represents a data set to be inserted into a table. The output value object returns messages and the number of records inserted, which is represented as a long data type. The output value object also returns any warnings that occurred during business service processing. If an error occurs during processing, an error message is sent to the published business service, and the published business service throws an exception.
5.3.1.3 Class Diagram
The following class diagram shows the published business service objects for InsertAddressBookStaging:
Figure 5-3 Published business service, InsertAddressBookStaging, class diagram.
Description of "Figure 5-3 Published business service, InsertAddressBookStaging, class diagram."
5.3.2 Internal Value Object for Insert
The internal value object for an insert database operation includes an array list of records that need to be inserted. The array list contains a collection of InternalInsertAddressBook StagingFields objects.
The following class diagram shows the business service objects for InternalInsertAddressBook Staging:
Figure 5-4 Business service, InternalInsertAddressBookStaging, class diagram.
Description of "Figure 5-4 Business service, InternalInsertAddressBookStaging, class diagram."
5.3.3 Inserting Multiple Records
The business service method handles multiple records for an insert database operation; however, the generated code inserts one record at a time.
This code sample shows the business service method handling multiple records:
public static E1MessageList insertAddressBookStaging(IContext context,
IConnection connection,
InternalInsertAddressBookStaging internalVO){
//Call start internal method, passing the context (which was passed
//from published business service).
startInternalMethod(context, "insertAddressBookZTable",
internalVO);
//Create new message list for business service processing.
E1MessageList messages = new E1MessageList();
long numRowsInserted = 0;
if (internalVO.getInsertFields()!=null) {
for (int i = 0; i < internalVO.getInsertFields().size(); i++)
{
//call method (created by the wizard), which then
//executes Business Function or Database operation
E1MessageList insertMessages =
InsertToF0101Z2(context, connection,
internalVO.getInsertFields(i));
//add messages returned from E1 processing to business
//service message list.
messages.addMessages(insertMessages);
//if no errors occur while inserting, add to counter.
if (!insertMessages.hasErrors()) {
numRowsInserted++;
}
}
internalVO.setNumberRowsInserted(numRowsInserted);
}
//Call finish internal method passing context.
finishInternalMethod(context, "insertAddressBookZTable");
//Return E1MessageList containing errors and warnings that
//occurred during processing business service.
return messages;
This code sample shows the generated code for the database insert:
private static E1MessageList InsertToF0101Z2(IContext context,
IConnection connection, InternalInsertAddressBookStagingFields
internalVO) {
//create return object
E1MessageList returnMessages = new E1MessageList();
//specify columns to insert
BSSVDBField[] insertFields =
{new BSSVDBField("F0101Z2.EDUS"), // String – EdiUserId
new BSSVDBField("F0101Z2.EDBT"), // String – EdiBatchNumber
new BSSVDBField("F0101Z2.EDTN"), // String – EdiTransactNumber
new BSSVDBField("F0101Z2.EDLN"), // Numeric – EdiLineNumber
new BSSVDBField("F0101Z2.AN8"), // Numeric – AddressNumber
new BSSVDBField("F0101Z2.ALKY"), // String – AlternateAddressKey
new BSSVDBField("F0101Z2.TAX"), // String – TaxId
new BSSVDBField("F0101Z2.ALPH"), // String – NameAlpha
new BSSVDBField("F0101Z2.DC"), // String – DescripCompressed
new BSSVDBField("F0101Z2.MCU") // String – CostCenter
};
//specify insert values
Object[] insertValues =
{internalVO.getF0101Z2_EDUS(),
internalVO.getF0101Z2_EDBT(),
internalVO.getF0101Z2_EDTN(),
internalVO.getF0101Z2_EDLN(),
internalVO.getF0101Z2_AN8(),
internalVO.getF0101Z2_ALKY(),
internalVO.getF0101Z2_TAX(),
internalVO.getF0101Z2_ALPH(),
internalVO.getF0101Z2_DC(),
internalVO.getF0101Z2_MCU()
};
try {
//get dbService from context
IDBService dbService = context.getDBService();
//execute db insert operation
long numRecordsInserted =
dbService.BSSVDBInsert(context, connection, "F0101Z2",
IDBService.DB_TABLE, insertFields, insertValues);
} catch (DBServiceException e) {
//take some action in response to the database exception
returnMessages.addMessage(new E1Message(context, "005FIS",
TABLE_NAME + E1Message.sLineSeparator+e.getMessage()));
}
return returnMessages;
}
5.4 Creating an Update Database Operation Business Service
The update database operation enables you to modify existing information in a table or business view. You use the Update database operation in the Database wizard to create an update business service.
5.4.1 Published Value Object for Update
The published interface for an Update database operation uses an input value object and an output value object.
5.4.1.1 Naming Conventions
The naming convention for an update value object is to use the verb update to preface the type of data to be processed, for example, UpdateAddressBookStaging. The naming convention for an output value object is to use the verb phrase ConfirmUpdate to preface the information that is processed, for example, ConfirmUpdateAddressBookStaging.
5.4.1.2 Data Types and Structure
The input value object for an update database operation represents a where clause for the records to be updated and the fields that need to be updated for those records. The records and fields are represented by two separate components under the main value object class. The output value object returns messages about the processing that occurred and the number of records updated, which is represented as a long data type. The output value object also returns any warnings that occurred during business service processing. If an error occurs during processing, an error message is sent to the published business service, and the published business service throws an exception.
5.4.1.3 Class Diagram
This class diagram shows the published business service objects for UpdateAddressBookStaging:
Figure 5-5 Published business service, UpdateAddressBookStaging, class diagram.
Description of "Figure 5-5 Published business service, UpdateAddressBookStaging, class diagram."
5.4.2 Internal Value Object for Update
The internal value object for an update database operation contains a component that represents the where clause for the records to be updated and a component that represents the fields to be updated. The variable names updateWhereFields and updateFields for these components are important because the generated code assumes that the proper naming convention is used. The generated code should require minimal changes, if any.
This code sample shows the structure for the internal value object:
public class InternalUpdateAddressBookStaging extends ValueObject {
/**
* Internal VO representing the where clause for updating the
* F0101Z2 table.
*/
private InternalUpdateAddressBookStagingWhereFields
updateWhereFields = new InternalUpdateAddressBookStagingWhereFields();
/**
* Internal VO representing the fields to be updated in the F0101Z2
* table.
*/
private InternalUpdateAddressBookStagingFields updateFields = new
InternalUpdateAddressBookStagingFields();
/**
* Number of rows updated as returned by the database call.
*/
private long numberRowsUpdated = 0;
This code sample shows the generated code for the update database operation, with the updates that you are required to make in bold type:
private static E1MessageList UpdateF0101Z2(IContext context,
IConnection connection, InternalUpdateAddressBookStaging internalVO) {
//create return object
E1MessageList returnMessages = new E1MessageList();
//specify columns to update
BSSVDBField[] updateFields =
{new BSSVDBField("F0101Z2.ALPH"), // String - NameAlpha
new BSSVDBField("F0101Z2.DC"), // String - DescripCompressed
new BSSVDBField("F0101Z2.MCU") // String - CostCenter
};
//specify update values
Object[] updateValues =
{internalVO.getUpdateFields().getF0101Z2_ALPH(),
internalVO.getUpdateFields().getF0101Z2_DC(),
internalVO.getUpdateFields().getF0101Z2_MCU()
};
//specify condition records must meet to be updated
BSDBWhereField[] whereFields =
{new BSDBWhereField(null, new BSSVDBField("F0101Z2.EDUS"),
IDBService.EQUALS, internalVO.getUpdateWhereFields().getF0101Z2_EDUS()),
new BSDBWhereField(IDBService.AND, new BSSVDBField("F0101Z2.
EDBT"),
IDBService.EQUALS, internalVO.getUpdateWhereFields().getF0101Z2_EDBT()),
new BSDBWhereField(IDBService.AND, new BSSVDBField("F0101Z2.
EDTN"),
IDBService.EQUALS, internalVO.getUpdateWhereFields().getF0101Z2_EDTN()),
new BSDBWhereField(IDBService.AND, new BSSVDBField("F0101Z2.
EDLN"),
IDBService.EQUALS, internalVO.getUpdateWhereFields().
getF0101Z2_EDLN())};
BSSVDBWhereClauseBuilder whereClause =
new BSSVDBWhereClauseBuilder(context, whereFields);
try {
//get dbService from context
IDBService dbService = context.getDBService();
//execute db update operation
long numRecordsUpdated =
dbService.BSSVDBUpdate(context, connection, "F0101Z2",
IDBService.DB_TABLE, updateFields, updateValues, whereClause);
internalVO.setNumberRowsUpdated(numRecordsUpdated);
} catch (DBServiceException e) {
// take some action in response to the database exception
returnMessages.addMessage(new E1Message(context, "005FIS",
TABLE_NAME + E1Message.sLineSeparator+e.getMessage())); }
return returnMessages;
}
This class diagram shows the business service objects for UpdateAddressBookStaging:
Figure 5-6 Business service, UpdateAddressBookStaging, class diagram.
Description of "Figure 5-6 Business service, UpdateAddressBookStaging, class diagram."
5.5 Creating a Delete Database Operation Business Service
The delete database operation enables you to remove information in a table or business view. You use the Delete database operation in the Database wizard to create a delete business service.
5.5.1 Published Value Object for Delete
The published interface for a delete database operation uses an input value object and an output value object.
5.5.1.1 Naming Conventions
The naming convention for a delete input value object is to use the verb delete to preface the type of data to be processed, for example, DeleteAddressBookStaging. The naming convention for the delete output value object is to use the verb phrase ConfirmDelete to preface the type of data processed, for example, ConfirmDeleteAddressBookStaging.
5.5.1.2 Data Types and Structure
The input value object for a delete database operation represents a where clause for the records to be deleted. The input value object contains key fields to the table or business view. A value must be passed for each key field so that only one record at a time is selected for deletion. The where clause is not conditionally created based on whether a value is sent for a field. The delete operation should not be used for deleting all records at once; therefore, do not use a null where clause in the code.
The output value object for a delete database operation returns messages and the number of records deleted, which is represented as a long data type. The output value object also returns any warnings that occurred during business service processing. If an error occurs during processing, an error message is sent to the published business service, and the published business service throws an exception.
5.5.1.3 Class Diagram
This class diagram shows the published business service objects for DeleteAddressBookStaging:
Figure 5-7 Published business service, DeleteAddressBookStaging, class diagram.
Description of "Figure 5-7 Published business service, DeleteAddressBookStaging, class diagram."
5.5.2 Internal Value Object for Delete
The internal value object for a delete database operation includes the key fields that are required for selecting a record to be deleted and the numberRowsDeleted field.
The following class diagram shows the business service objects for DeleteAddressBookStaging:
Figure 5-8 Business service, DeleteAddressBookStaging, class diagram.
Description of "Figure 5-8 Business service, DeleteAddressBookStaging, class diagram."