The org.ofbiz.entity.util.EntityUtil has several methods useful for manipulating data returned from an Entity Engine call. This data, referred to as the "result set", is either a single GenericValue object, a Java list containing one or more GenericValue objects, or a null.
Tip
Using the EntityUtil works with result sets already in memory. This means that you should use caution when manipulating data in this way as you may run out of memory and/or degrade performance in short order. If you have a choice, it is almost always better to let the Entity Engine and database perform data filtering, ordering, and sorting (by way of conditional GenericDelegator find parameters) before bringing a result set into memory.
In this section, we take a look at some handy utilities provided by OFBiz to manipulate result sets.
There is a data modeling pattern often used within OFBiz where an entity will have three (or more) primary keys. One of the primary keys will uniquely represent a dated value. Using a unique date field as a primary key allows for many identical entries, with the only exception being the presence of a unique date value. This is great for auditing and tracking updates to a table. (Remember, you can't update a primary key field's value. If you want to change a primary key field's value, you must first remove the row from the database and then rewrite it).
From our bakery example, the RecipeIngredient entity uses this technique. By defining the fromDate field as a Timestamp value and as a primary key, it is possible to have the same ingredient included in the same recipe multiple times during the life of the recipe.
Why would you want to do that? Suppose we want to track the addition or modification of a specific ingredient within a recipe. Only by keeping the old ingredients around can we establish any history with our recipes.
Having multiple primary keys sometimes causes grief when searching for specific data, especially when you know all the primary keys but one. If you know all the primary keys, you may simply query by primary keys and be guaranteed that a single GenericValue object (or null) be returned. If you don't know all the primary keys for an entity, you are forced to get a list of values, many of which you may not be interested in.
OFBiz provides a handy utility that lets you easily get the first GenericValue off a result set list. In the following example, we first retrieve a list of GenericValue objects from the RecipeIngredient table (which, if you recall, has three primary keys). From that list, we select the first GenericValue.
GenericDelegator delegator =
(GenericDelegator) request.getAttribute("delegator");
List conditions =
UtilMisc.toList(EntityCondition.makeCondition("recipeId",
EntityOperator.NOT_EQUAL, null),
EntityCondition.makeCondition("recipeSeqNumber",
EntityOperator.NOT_EQUAL,
new Long(0)));
try {
List listOfEntities = delegator.findList("RecipeIngredient",
EntityCondition.makeCondition(conditions,EntityOperator.AND),
null,null,null,false);
// Get the first GenericValue on the list
GenericValue someEntity = EntityUtil.getFirst(listOfEntities);
}
catch(GenericEntityException ge) {
// Process errors
}
This method is often used together with an ordering operator as shown in the following code snippet to first order the return list by date, and then, knowing that the list is in dated order, return the most recent or the oldest dated value:
List conditions = UtilMisc.toList(EntityCondition.makeCondition(
"recipeId", EntityOperator.NOT_EQUAL, null),
EntityCondition.makeCondition(
"recipeSeqNumber",
EntityOperator.NOT_EQUAL,
new Long(0)));
try {
// Return the list where the most recent date in the "fromDate"
// field is first on the list
List listOfEntities = delegator.findList("RecipeIngredient",
EntityCondition.makeCondition(conditions,
EntityOperator.AND),
null, UtilMisc.toList("-fromDate"),null,
false);
GenericValue someEntity = EntityUtil.getFirst(listOfEntities);
// In this example, return a list where the oldest value in the
// "fromDate" field is first on the list
listOfEntities = delegator.findList("RecipeIngredient",
EntityCondition.makeCondition(conditions,
EntityOperator.AND),
null, UtilMisc.toList("+fromDate"),null,
false);
someEntity = EntityUtil.getFirst(listOfEntities);
}
catch(GenericEntityException ge) {
// Process Errors
}
In its simplest form, this method takes a list of GenericValue objects and compares each one against the given moment value, looking for objects where the default fromDate field value is either null, before, or equal to the moment value and the default thruDate field value is either null or after the moment thruDate value. In other words, the moment value falls between the values found in the fromDate and thruDate fields. This form of the method assumes that the entity has both a fromDate and a thruDate field defined. It just so happens that the RecipeIngredient table has just such fields defined. If our RecipeIngredient table has the following records in it:
|
RecipeIngredient | |||
|---|---|---|---|
|
recipeId |
ingredientId |
fromDate |
thruDate |
|
r001 |
10001 |
2009-01-01 00:00:00.000 |
2009-01-01 00:00:00.000 |
|
r001 |
10001 |
2009-03-05 00:00:00.000 |
2009-09-01 00:00:00.000 |
|
r001 |
10001 |
2009-09-09 00:00:00.000 |
2009-09-14 00:00:00.000 |
|
r001 |
10001 |
2009-09-15 00:00:00.000 | |
|
r001 |
10002 |
2008-01-01 00:00:00.000 | |
|
r001 |
10010 |
2010-01-01 00:00:00.000 | |
|
r002 |
10001 |
2008-01-01 00:00:00.000 |
2020-01-01 00:00:00.000 |
try {
// For this example, just get them all
List recipeIngredients =
delegator.findList("RecipeIngredient",null,null,null,null,false);
// First, find all the recipes and ingredients where the "active"
// ingredient was added to the recipe before January 1, 2009.
// These ingredients are still active if the thruDate is null
// (never made inactive) or if the thruDate is after the current
// date:
Timestamp moment = Timestamp.valueOf("2009-01-01 00:00:00.0");
List<GenericValue> alist =
EntityUtil.filterByDate(recipeIngredients, moment);
// Do something with the list
}
catch(GenericEntityException ge) {
// Process Errors
}
The code returns a list with the following GenericValue objects (where moment equals "2009-01-01 00:00:00.0"):
|
recipeId |
ingredientId |
fromDate |
thruDate |
|---|---|---|---|
|
r001 |
10002 |
2008-01-01 00:00:00.0 |
null |
|
r002 |
10001 |
2008-01-01 00:00:00.0 |
2020-01-01 00:00:00.0 |
This utility provides a convenient mechanism to collect a list of entities filtered by a moment in time. You will see this used frequently in the code where the moment is the current date in Timestamp format as shown here:
List<GenericValue> newList = EntityUtil.filterByDate(allParties, UtilDateTime.nowTimestamp());
You may also use this utility and pass the names of the fields that you'd like to use to compare the moment value against. For example, if we'd like to retrieve a list of all the rows from the RecipeIngredients result set where the value in the createdStamp is before a moment value of "2009-01-03 00:00:00.0" and the moment value is before the lastUpdatedStamp value, the following code snippet may be used as an example.
List recipeIngredients =
delegator.findList("RecipeIngredient",null,null,null,null,false);
Timestamp moment = Timestamp.valueOf("2009-01-03 00:00:00.0");
// False in this method indicates that all dated values are not the
// same.
try {
List<GenericValue> alist =
EntityUtil.filterByDate(recipeIngredients, moment,
"createdStamp","lastUpdatedStamp", false);
}
catch(GenericEntityException ge) {
// Process Errors
}
If we have the following three records in the recipeIngredients result set and a moment value equal to "2009-01-03 00:00:00.0":
|
recipeId |
ingredientId |
createdStamp |
lastUpdatedStamp |
|---|---|---|---|
|
r001 |
10001 |
2010-05-27 19:41:40.784 |
2010-05-28 13:02:14.55 |
|
r001 |
10001 |
2002-05-27 16:59:22.529 |
2010-05-28 13:02:15.561 |
|
r001 |
10001 |
2004-05-27 16:59:22.534 |
2010-05-28 13:02:33.21 |
The following two records/rows will be returned:
|
recipeId |
ingredientId |
createdStamp |
lastUpdatedStamp |
|---|---|---|---|
|
r001 |
10001 |
2002-05-27 16:59:22.529 |
2010-05-28 13:02:15.561 |
|
r001 |
10001 |
2004-05-27 16:59:22.534 |
2010-05-28 13:02:33.21 |
To quickly sort through a list of result sets and return a new list ordered by entity fields that are dated or numeric types, use the EntityUtil.orderBy() method. The following Java snippet example orders an existing list as described:
// To order the list in descending order where the oldest value is
// first on the list (null values are placed at the beginning of
// the list):
alist = EntityUtil.orderBy(recipeIngredients,
UtilMisc.toList("thruDate DESC"));
// Or, if you prefer:
alist = EntityUtil.orderBy(recipeIngredients,
UtilMisc.toList("-thruDate"));
alist.clear();
// To order a list in ascending order where the most recent
// time value is first, followed in ascending order by other values
// (and null values are all placed at the end of the list):
alist = EntityUtil.orderBy(recipeIngredients,
UtilMisc.toList("thruDate ASC"));
// Or, if you prefer:
alist = EntityUtil.orderBy(recipeIngredients,
UtilMisc.toList("+thruDate"));
alist.clear();
alist = EntityUtil.orderBy(recipeIngredients,
UtilMisc.toList("amount","fromDate DESC"));
// The orderBy method may also be used to order a list by non-dated
// fields. For example, the following will return a list of
// GenericValue objects ordered by value in the "amount" field.
// Amount is a numeric. By default, the final order is by
// ascending (lowest number first)
alist = EntityUtil.orderBy(recipeIngredients,
UtilMisc.toList("amount"));
// This call to orderBy will order the return list by the
// GenericValue with the largest value first on the list
alist = EntityUtil.orderBy(recipeIngredients,
UtilMisc.toList("amount DESC"));
// You may also order the list by a non-numeric and/or
// non-dated field:
alist = EntityUtil.orderBy(recipeIngredients,
UtilMisc.toList("ingredientId"));
// The ordering defaults to ascending (smallest value first).
// Results may vary if the value in the target orderBy field does
// not have a collation sequence that makes sorting sense.
The following are valid ordering directives supported by the orderBy method:
|
OrderBy specifier |
Sort order |
|---|---|
|
|
descending |
|
|
ascending |
|
|
descending |
|
|
ascending |
For a very handy utility to quickly assemble a list of field values from an existing result set, try the EntityUtil.getFieldListFromEntityList(). For example, if we have a list of RecipeIngredient GenericValues and we quickly want a new list of just the ingredientIds, we could do something like the following:
GenericDelegator delegator =
(GenericDelegator) request.getAttribute("delegator");
try {
// We are just getting started with the Entity Engine Java API
// So, make the most basic of calls to get all the records/rows
// from the RecipeIngredient table
List<GenericValue> recipeIngredients =
delegator.findList("RecipeIngredient",null,null,null,null,false);
// Get a list of all the ingredientIds from recipeIngredients
// Note: true means do not put duplicates in the return list
// false means put all values in return list
List<String> alist =
EntityUtil.getFieldListFromEntityList(recipeIngredients,
"ingredientId", true);
}
catch(GenericEntityException ge) {
// Process Error
}
Any time you have a list of entities where the entities are part of a one-to-one or one-to-many relationship, you can easily get related entities using the EntityUtil.getRelated() method. The following code snippet is an example:
GenericDelegator delegator =
(GenericDelegator) request.getAttribute("delegator");
try {
List<GenericValue> recipeIngredients =
delegator.findList("RecipeIngredient",null,null,null,null,false);
// Since the Ingredient entity is related to the
// RecipeIngredient entity We can easily get all the entities
// that are related to this entity using the getRelated()
// method. We don't need to make another GenericDelegator call.
// The following returns a list of Ingredient table rows where
// the ingredientId field is related to the field value in
// recipeIngredients.
List<GenericValue> ingredients =
EntityUtil.getRelated("Ingredient", recipeIngredients);
}
catch(GenericEntityException ge) {
// Process Error
}
As our data extraction needs get more sophisticated, we can avail ourselves of ever more precise filtering by using the EntityCondition object to create "condition" statements that further filter the values we are looking for. For example, if we wish to find recipes that have a specific set of ingredients, we could use:
try {
// We are just getting started with the Entity Engine Java API
// So, make the most basic of calls to get all the records/rows
// from the RecipeIngredient table
List<GenericValue> recipeIngredients =
delegator.findList("RecipeIngredient",null,null,null,null,false);
// Make up a list of arbitrary ingredients just to show how
// this works
List<String> arbitraryIngredients =
UtilMisc.toList("10001", "11121", "10020", "1005");
// This condition method returns all the GenericValue objects
// from the recipeIngredient list where the value in
// RecipeIngredient.ingredientId = any of the values in the
// arbitraryIngredients list
List<GenericValue> aList =
EntityUtil.filterByCondition(recipeIngredients,
EntityCondition.makeCondition("ingredientId",
EntityOperator.IN, arbitraryIngredients));
// This call will only return values NOT in the
// arbitraryIngredients list
List<GenericValue> bList =
EntityUtil.filterOutByCondition(recipeIngredients,
EntityCondition.makeCondition("ingredientId",
EntityOperator.IN, arbitraryIngredients));
}
catch(GenericEntityException ge) {
// Process Errors
}
There are all sorts of variations on the filterByCondition() method, limited only by your imagination in making up condition statements. We leave it as an exercise to the reader to experiment and come up with more ways.