This page describes how to choose storage operations when working with many records. Choosing the type of a storage operation may have impact on used system resources, like memory allocation and database connections. Several tips are illustrated here with examples of how to use query cursors that let you iterate over the result set and bulk operations - storage operations that work on a set of records.
The standard
query method returns a whole result set as one
array of records. As a result, queries that return many records require much
memory, because all fetched records must be present in memory at the same time.
Quite often you need to access only one record at a time. In such cases the
queryCursor method
may be a better choice. It returns a
cursor or pointer to the current view
(entity instance) in the result
set. The cursor's next method is used to iterate over all entity
instances
in the result set. queryCursor, just like standard query
takes as input an
FndQueryRecord,
but note that the attributes skipRows and maxRows are
ignored. A view returned by the next
method contains simple and compound attributes depending on the include/exclude flags
set on the input record.
In the following example we will find all distinct zip codes for person addresses in Poland. We need a master-detail query because person addresses are stored as a detail array in PersonInfo entity. The master query will retrieve only persons that have at least one address in Poland. The detail query will retrieve all Polish addresses for a current person. Note that the same PersonInfoAddress query condition is used in the master query (as EXISTS_IN detail condition) as well as in the detail query. This will guarantee that only needed master records will be fetched from the database.
// get an entity handler for PersonInfo PersonInfoHandler handler = (PersonInfoHandler) PersonInfoHandlerFactory.getHandler(); // include only needed person attributes PersonInfo person = new PersonInfo(); person.excludeQueryResults(); person.personId.include(); person.addresses.include(); // include only needed address attributes PersonInfoAddress address = new PersonInfoAddress(); address.excludeQueryResults(); address.zipCode.include(); address.city.include(); // create query condition on address record address.country.setValue("PL"); // add the address condition as EXISTS_IN detail condition (will be used in master query) person.addCondition( person.addresses.createDetailCondition(address, FndQueryReferenceCategory.EXISTS_IN)); // add the address condition to the detail array (will be used in detail query) person.addresses.add(address); // open a cursor PersonInfoQueryCursor persons = handler.queryCursor(new FndQueryRecord(person)); // iterate over the result set and collect incoming zip codes Set postCodes = new HashSet(); PersonInfo pers; while((pers = persons.next()) != null) { for(int i = 0; i < pers.addresses.size(); i++) { PersonInfoAddress addr = pers.addresses(i); postCodes.add(addr.zipCode.getValue() + " " + addr.city.getValue()); } } // close the cursor persons.close();
When queryCursor is called the following master select statement
is executed. The master query returns every person that has at least one address
in Poland.
SELECT a1.PERSON_ID FROM ifsapp.PERSON_INFO_PUBLIC a1 WHERE EXISTS (SELECT 1 FROM ifsapp.PERSON_INFO_ADDRESS a2 WHERE a1.PERSON_ID = a2.PERSON_ID AND a2.COUNTRY_DB = :1) FndStatement: Binding parameters: 1: (TEXT) = PL
When next is called the next master record is fetched from the
database and the
following detail select statement is executed. The detail query returns all
Polish addresses for the current person. Here, the value of the first bind
variable JOSMSE is the ID of the current person.
SELECT a1.ZIP_CODE, a1.CITY FROM ifsapp.PERSON_INFO_ADDRESS a1 WHERE a1.PERSON_ID = :1 AND a1.COUNTRY_DB = :2 FndStatement: Binding parameters: 1: (TEXT) = JOSMSE 2: (TEXT) = PL
The queryCursor operation may perform better than the standard
query operation, because it uses less memory. However, if you need
to call a storage operation on every view returned by next, then
the standard query combined with
bulkSave may be a
better choice. Why? Because every call to a storage operation requires an
initialization of the database connection, which is a costly operation. So, if the number of returned records is not too
high you can consider using
the standard query method combined with bulkSave
method; the combination that invokes only two storage operations.
The bulkSave method saves multiple entity instances in a single call.
It takes as parameter an array of entity views. The array may contain
views in different states having attributes with different values.
The following example will compute and update person initials. First a standard query operation retrieves all
persons having undefined initials. Then, in a loop, a value of initials is
computed and set on every view returned by the query. Finally, the array with
modified entities is sent to bulkSave operation.
// get an entity handler for PersonInfo PersonInfoHandler handler = (PersonInfoHandler) PersonInfoHandlerFactory.getHandler(); // include only needed attributes PersonInfo person = new PersonInfo(); person.excludeQueryResults(); person.objId.include(); person.objVersion.include(); person.personId.include(); person.name.include(); // create condition on initials attribute (we don't want to overwrite existing values) person.addCondition(person.initials.createIsNullCondition()); // perform standard query opetaion PersonInfoArray arr = (PersonInfoArray) handler.query(new FndQueryRecord(person)); // iterate over query result for(int i = 0; i < arr.size(); i++) { PersonInfo pers = arr.get(i); // create initials by concatenating the first letter of every part of the person's name StringTokenizer words = new StringTokenizer(pers.name.getValue(), " "); StringBuffer buf = new StringBuffer(); while(words.hasMoreTokens()) buf.append(words.nextToken().charAt(0)); // set the person's initials (this makes the pers record dirty) pers.initials.setValue(buf.toString()); } // call storage operation handler.bulkSave(arr);
Combination of the standard query operation with bulkSave
is recommended only if the number (and size) of modified records is not too big.
If you work with a very big number of records this combination may consume a lot
of memory.
queryCursor and save in a loop is memory efficient
but gives many calls to storage operations. query and
bulkSave gives just two calls to storage operations but may consume a lot
of memory. The next section presents a way to combine the good sides of
queryCursor and bulkSave operations.
The following example will do the same task as the previous one: it will
compute and update person initials. It will use queryCursor to
fetch one record at a time. Then it will gather fetched and modified entities in
a temporary array until it reaches a specified size (see the variable
bulkSize). Then the bulk of entities is saved by calling bulkSave,
the temporary array is cleared and the process continues.
// get an entity handler for PersonInfo PersonInfoHandler handler = (PersonInfoHandler) PersonInfoHandlerFactory.getHandler(); // include only needed attributes PersonInfo person = new PersonInfo(); person.excludeQueryResults(); person.objId.include(); person.objVersion.include(); person.personId.include(); person.name.include(); // create condition on initials attribute (we don't want to overwrite existing values) person.addCondition(person.initials.createIsNullCondition()); // create a temporary array for persons int bulkSize = 100; PersonInfoArray arr = new PersonInfoArray(); // open a cursor PersonInfoQueryCursor persons = handler.queryCursor(new FndQueryRecord(person)); // iterate over the result set while((person = persons.next()) != null) { // create initials by concatenating the first letter of every part of the person's name StringTokenizer words = new StringTokenizer(person.name.getValue(), " "); StringBuffer buf = new StringBuffer(); while(words.hasMoreTokens()) buf.append(words.nextToken().charAt(0)); // set the person's initials and add the person to the array person.initials.setValue(buf.toString()); arr.add(person); // save the next bulk of persons if(arr.size() >= bulkSize) { handler.bulkSave(arr); arr.clear(); } } // save the last bulk of persons if(arr.size() > 0) handler.bulkSave(arr); // close the cursor persons.close();
queryCursor plus bulkSave in a loop is a beneficial
combination. It saves both memory and initialization of database connections. A
drawback is that it is more complicated than other combinations described in
this document.
When you want to query a set of entities and then update these entities with
the same attribute values, then probably the best option is to use
batchSave operation. It takes two views as input: a value view and a
condition view. batchSave performs a query based on the condition
view and then updates every entity in the result set with the attribute values
defined in the value view. It means that you can update many entity instances
(and optionally their details) in
one call to a storage operation.
batchSave has different implementations depending on the input
views. In case of LU entities without dirty LOBs and without custom storage
batchSave is especially effective. It generates one PLSQL block and
sends it to the database for execution, which means that the modified views are
never allocated in java VM memory. In other cases (native entities, dirty LOBs or
custom storage) batchSave is less efficient, but is probably a better option than
calling a storage operation in a loop.
The following example modifies the value of title attribute in many entity
instances in one call to a storage operation. The value record contains a new
value for title attribute. The condition record contains the current value of
this attribute. Note that
batchSave operation ignores include query result flags on the
condition record, all attributes necessary to properly perform the operation (OBJID,
OBJVERSION, primary and parent keys) are included automatically.
// get an entity handler for PersonInfo PersonInfoHandler handler = (PersonInfoHandler) PersonInfoHandlerFactory.getHandler(); // create value view and set the new value for title attribute PersonInfo personValue = new PersonInfo(); personValue.title.setValue("Senior Software Engineer"); // create condition view and set the current value for title attribute PersonInfo personCond = new PersonInfo(); personCond.title.setValue("Software Engineer"); // call storage operation handler.batchSave(personValue, personCond);
When batchSave is called the following PLSQL block is
generated. It performs a
query derived from the condition view and then, in a loop, sends every fetched record to procedure PERSON_INFO_API.Modify__.
declare tmpinfo_ varchar2(32767); tmpattr_ varchar2(32767); allinfo_ clob; begin declare attr_ constant varchar2(31) := :1; begin for R1 in ( SELECT a1.OBJID, a1.OBJVERSION, a1.PERSON_ID FROM ifsapp.PERSON_INFO_PUBLIC a1 WHERE a1.TITLE = :2 ) loop tmpattr_ := attr_; ifsapp.PERSON_INFO_API.Modify__(tmpinfo_, R1.objid, R1.objversion, tmpattr_, 'DO'); allinfo_ := allinfo_ || tmpinfo_; end loop; -- R1 end; :3 := allinfo_; end; FndStatement: Binding parameters: 1: (STRING) = TITLE Senior Software Engineer 2: (TEXT) = Software Engineer 3: (out LONG_TEXT) INFO
batchSave operation may be used to remove existing
entity instances. The next example removes many entities in one database call.
The value record has state set to REMOVED_RECORD. The condition record defines
the set of entity instances to be removed. The call to batchSave
removes the specified entities in one database call.
// get an entity handler for PersonInfo PersonInfoHandler handler = (PersonInfoHandler) PersonInfoHandlerFactory.getHandler(); // create value view and set its state to REMOVED_RECORD PersonInfo value = new PersonInfo(); value.setState(FndRecordState.REMOVED_RECORD); // create condition view and specify what person entities to remove PersonInfo cond = new PersonInfo(); ArrayList list = new ArrayList(); list.add("JOSMSE1X"); list.add("JOSMSE2X"); cond.addCondition(cond.personId.createInCondition(list)); // call storage operation handler.batchSave(value, cond);
When removing entities batchSave removes also entity details
marked with cascade-delete flag in the model. The complete PLSQL block generated
in debug mode for the
above example is here.
The next example shows how detail records may be modified by batchSave
operation. Suppose that you want to prolong validity of Polish delivery addresses until the end of the year.
First, we create a value view. Person addresses are stored as a detail array in PersonInfo
entity, so we need to create an address view, set its validTo
attribute and add it to the addresses array of the master entity.
As a next step we create an address condition view. We want to retrieve only
addresses in Poland, that have validTo attribute set to a not null
value lower than the end of this year. Also, we create a detail condition on the
address type view, because we want to retrieve only delivery addresses. Now, we
add the address condition to the master condition view (PersonInfo) as well as
to the detail condition view (PersonInfoAddress). This will guarantee that only
needed master records will be fetched from the database.
// get an entity handler for PersonInfo PersonInfoHandler handler = (PersonInfoHandler) PersonInfoHandlerFactory.getHandler(); // prepare timestamp that will be used both in value and condition records Timestamp endOfYear = Timestamp.valueOf("2007-12-31 00:00:00.00000000"); // create value view and set the new value for validTo attribute in detail view PersonInfo personValue = new PersonInfo(); PersonInfoAddress addressValue = new PersonInfoAddress(); addressValue.validTo.setValue(endOfYear); personValue.addresses.add(addressValue); // create master condition view PersonInfo personCond = new PersonInfo(); // create address condition view PersonInfoAddress addressCond = new PersonInfoAddress(); addressCond.country.setValue("PL"); addressCond.addCondition(addressCond.validTo.createLessThanCondition(endOfYear)); // create address type condition view and add it as EXISTS_IN detail condition on address view PersonInfoAddressType typeCond = new PersonInfoAddressType(); typeCond.addressTypeCode.setValue(AddressTypeCodeEnumeration.DELIVERY); addressCond.addCondition( addressCond.addressTypes.createDetailCondition(typeCond, FndQueryReferenceCategory.EXISTS_IN)); // add the address condition as EXISTS_IN detail condition (will be used in master query) personCond.addCondition( personCond.addresses.createDetailCondition(addressCond, FndQueryReferenceCategory.EXISTS_IN)); // add the address condition to the detail array (will be used in detail query) personCond.addresses.add(addressCond); // call storage operation handler.batchSave(personValue, personCond);
The complete PLSQL block generated in debug mode for the above example is here.
When an entity is a native entity and it's contains states
batchSave method generated with additional third text parameter for
modify the entity state attribute. This is not generated for LU entity and
native entity without states.
The following example modifies the entity state value of many entity instances in one call to a storage operation.
// call storage operation with change entity state value to "ACTIVE" handler.batchSave(personValue, personCond, "ACTIVE");
Use standard query when:
Use queryCursor when:
bulkSave in a loop)Use batchSave when:
Use bulkSave when:
batchSave for some reasonFinally, some general rules, which can help you in optimal selection and usage of storage operations: