7. Entities, View Entities, and Extended Entities – Apache OFBiz Development

Chapter 7. Entities, View Entities, and Extended Entities

We now come to the Model component of OFBiz—the "M" of the MVC. The Model component of the MVC is also known as the database. While the View portion takes care of presenting forms and user-interfaces and feedback to the end-user, the database stores all the information that the application is designed to store. If our application helps us as a business to serve our customers better, then our database will contain possibly a lifetime of data about our customers.

The Entity Engine in OFBiz is declarative. That is, it does not involve speaking in the native languages of many types of database systems that OFBiz supports. This means you only need to learn one Entity Engine in OFBiz, and you will be able to leverage any of the supported database systems.

In this chapter, we will be looking at:

  • Required administrative Entity Engine concepts (datasources and delegators)

  • The anatomy of an Entity

  • Creating and using our first Entity

  • Relations between Entities, and the corresponding Foreign Keys

  • Indices on Entity Fields

  • Relation types—"one" and "many" and "one-nofk"

  • The anatomy of a View Entity

  • Linking Entities (or even other View Entities) in a View Entity

  • Two types of Linking: Inner Joins and Outer Joins

  • Functions (example arithmetic, counting) on View Entity fields

  • Grouping for summary views

  • Complex aliases (where functions don't cut it)

  • Extending Entities

Entities

Entities in OFBiz, or rather in databases, are the basic units of a Model in the MVC framework. Simply speaking, an entity is a single database table. A table contains information about an entity in the real world, such as a person. Therefore, a table named Person would contain fields that describe a person.

The Structure of the Data Model

The OFBiz data model is far too big to be explained in this book. The design and concepts of most parts of the data model are based on models documented by Len Silverstone in The Data Model Resource Book, Volumes 1 and 2. This book clearly explains the underlying data structure and by reading it you will gain a better knowledge of the model that drives OFBiz.

Referencing Fields of an Entity

We generally refer to a field of an entity like this: <EntityName>.<FieldName>. The field planet in the entity PostalAddress will be referred to as PostalAddress.planet.

OFBiz Uses Relational Database Management Systems

OFBiz uses RDBMSs. RDBMSs use the "relational model". The relational model uses tables that may be related to one another. A simple example is a Person table that contains records having the field postalAddress. That field could point to a particular record in another table PostalAddress. In that way, we say that a person can be related to (or "can have") a postal address. Note how the table Person contains only fields that describe a person, and not fields like street that describe a postal address. That is how an entity should be defined, to describe only the entity itself (say Person) and not possibly associated entities (say PostalAddress).

Curious Trivia about the Relational Model

The word "relational" in a relational model does not mean, as you would probably expect it to, that tables relate to one another. Instead "relational" is a mathematical term for a "table". A database management system based on the relational model deals with tables. It just so happens that these tables can be related to one another to form complex structures (like a person having one or more postal addresses, each one having several pieces of furniture). The word "relationship" in an Entity-Relationship Model (ERM) refers to the relationship between entities. An ERM is a representation of structured data.

Entity Engine Concepts

There are a few concepts we need to quickly learn about before we proceed, concepts related to databases in OFBiz.

Datasources

A datasource is simply a "source of data". In OFBiz Derby is used as the default RDBMS. There can be multiple database schemas in one database instance, each schema housing a separate database of entities (say schemas people_records and address_records house records for people and addresses, respectively). For database systems that do not support schemas, separate database instances will need to be created instead for that same purpose.

Datasources are defined and configured in ${component:entity}\config\entityengine.xml. The component entity is in the folder ${OFBizInstallFolder}\framework\entity. The default datasource used in OFBiz is localderby:

<datasource name="localderby" helper-class="org.ofbiz.entity.datasource.GenericHelperDAO" schema-name="OFBIZ" field-type-name="derby" check-on-start="true" add-missing-on-start="true" use-pk-constraint-names="false" alias-view-columns="false">
<read-data reader-name="seed"/>
<read-data reader-name="demo"/>
<read-data reader-name="ext"/>
<inline-jdbc jdbc-driver="org.apache.derby.jdbc.EmbeddedDriver" jdbc-uri="jdbc:derby:ofbiz;create=true" jdbc-username="ofbiz" jdbc-password="ofbiz"
isolation-level="ReadCommitted"
pool-minsize="2" pool-maxsize="20"/>
</datasource>

In the datasource localderby, the Derby "database instance", so to speak, is in the folder ofbiz in the folder ${OFBizInstallFolder}\runtime\data\derby. That is specified by the string ofbiz (part of jdbc:derby:ofbiz;) in the attribute jdbc-uri of the element <inline-jdbc>. To create another database instance, simply create another datasource specifying a different folder, say jdbc:derby:another.

The schema name specified in the above datasource is OFBIZ. It is not necessary to create another database instance simply to have a separate datasource. Derby supports multiple schemas, so we can have segregated databases using multiple schemas.

However, in the case of Derby, it is often useful to have separate databases in separate database instances so we can back up and restore each one separately. Having two databases within two schemas inside a single database instance will mix the two into the same folder ofbiz in the folder ${OFBizInstallFolder}\runtime\data\derby, making it difficult to backup or restore either one independently of the other.

Entity Delegators

OFBiz uses an "entity delegator" to access the database(s). A delegator in software engineering is like an administrative object that delegates work to other objects (Delegation Pattern). An entity delegator's main purpose is to provide database access methods for the creation, retrieval, update, and deletion of data (CRUD). However, because it is a delegator, it doesn't perform those access tasks itself. Instead, it will determine which datasource to use for accessing which entity groups (explained next) of entities, and then delegate the actual task of accessing to those datasources.

Entity delegators are defined in ${component:entity}\config\entityengine.xml. An entity delegator is defined by a <delegator> element. By default, OFBiz webapps use the delegator named default:

<delegator name="default" entity-model-reader="main" entity-group-reader="main" entity-eca-reader="main" distributed-cache-clear-enabled="false">
<group-map group-name="org.ofbiz" datasource-name="localderby"/>
</delegator>

The above entity delegator handles only a single entity group named org.ofbiz. The datasource to use for this group is localderby. The other attributes of the <delegator> element will be discussed later.

Entity Groups

An entity group is a group of entities organized under a group name. The group name is used as a handle for entity delegators to determine which datasource to look into for which entities. Out of the box, only one entity group is ever used by all entity delegators defined for OFBiz: org.ofbiz. All entities in OFBiz are classified under that entity group.

The assigning of entities to entity groups is done in a file named entitygroup.xml in a folder named entitydef inside a component folder. One example is ${component:party}\entitydef\entitygroup.xml. To assign an entity PostalAddress to an entity group org.ofbiz, we would add:

<entity-group group="org.ofbiz" entity="PostalAddress"/>

Defining Our First Entity

Entities in OFBiz are by convention defined in files named entitymodel.xml inside the same entitydef (entity definitions) folder as the entitygroup.xml file. For example, the entity definitions for the component party are found in the file ${component:party}\entitydef\entitymodel.xml.

An entity is defined by an <entity> element. Let's create our own entity. In the folder ${component:learning}, create a new folder entitydef. In that new folder, create a new file named entitymodel.xml and enter into it this:

<?xml version="1.0" encoding="UTF-8"?>
<entitymodel xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation= "http://www.ofbiz.org/dtds/entitymodel.xsd">
<entity entity-name="Planet" package-name="org.ofbiz.learning">
<field name="planetId" type="id-ne"/>
<field name="planetName" type="name"/>
<field name="fromDate" type="date-time"/>
<field name="thruDate" type="date-time"/>
<prim-key field="planetId"/>
<index name="PLANET_NAME_IDX" unique="true">
<index-field name="planetName"/>
</index>
</entity>
</entitymodel>

Assigning Our Entity to an Entity Group

Now we need to put our entity under the entity group org.ofbiz. In the folder ${component:learning}\entitydef, create a new file entitygroup.xml and enter into it this:

<?xml version="1.0" encoding="UTF-8"?>
<entitygroup xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation= "http://www.ofbiz.org/dtds/entitygroup.xsd">
<entity-group group="org.ofbiz" entity="Planet"/>
</entitygroup>

Loading Our Entity into the Entity Engine

Finally, we need to tell OFBiz to load our entity definition files: entitymodel.xml and entitygroup.xml. We do that in the file ${component:learning}\ofbiz-component.xml. Right below the last <classpath> element, insert two <entity-resource> elements like this:

<entity-resource type="model" reader-name="main" loader="main" location="entitydef/entitymodel.xml"/>
<entity-resource type="group" reader-name="main" loader="main" location="entitydef/entitygroup.xml"/>

The <entity-resource> elements tell OFBiz to load our two entity definition files. Those files are called "resources" in the Java context. A resource is any file containing data that can be read by a Java application (OFBiz in this case). In general, a resource is like database data but cannot be changed at runtime. Whereas database data can be manipulated (by CRUD processes), resource data is hard-coded in physical files (like entitymodel.xml) and changes to these physical files will require a restart to take effect.

The attribute reader-name tells OFBiz to use an <entity-model-reader> named main. This is specified in the file ${component:entity}\config\entityengine.xml. It is not common to use more than one entity model reader or more than one entity delegator, so we shall not cover this in this book. The single entity model reader defined in OFBiz is named main.

The attribute loader points to a named <resource-loader>. Let us now insert a <resource-loader> element right above the first <classpath> element, like this:

<resource-loader name="main" type="component"/>

The <resource-loader> does not really create or define a loader. Actually, only one loader is present. Different <resource-loader> elements with different type attributes cause the single resource loader to function differently. For the above <resource-loader> named main of type component, the resource loader prefixes the component's root path (${component:learning} in this case) to the location attribute of an <entity-resource> when loading an <entity-resource>. For example, path entitydef\entitymodel.xml will be prepended to form ${OFBizInstallFolder}\hot-deploy\learning\entitydef\entitymodel.xml.

In general, the best practice is to use a <resource-loader> of type component, so that we can keep resource files neatly contained within the component's folder. To illustrate this neat organization, let us consider an alternative. Using a <resource-loader> of type file instead of component will require us to write our <resource-loader> like this:

<entity-resource type="model" reader-name="main" loader="main" location="./hot-deploy/learning/entitydef/entitymodel.xml"/>

where the "." in attribute location points to ${OFBizInstallFolder}.

Seeing Our First Entity

Restart OFBiz to put the new entity into the database. Open up the file ofbiz.log and observe the entries regarding the creation of our new entity:

2008-04-23 20:22:34,640 (main) [DatabaseUtil.java:853:INFO ] Getting Table Info From Database
2008-04-23 20:22:36,328 (main) [DatabaseUtil.java:988:INFO ] Getting Column Info From Database
2008-04-23 20:22:40,343 (main) [DatabaseUtil.java:351:WARN ] Entity [Planet] has no table in the database
2008-04-23 20:22:42,734 (main) [DatabaseUtil.java:364:INFO ] Created table [OFBIZ.PLANET]

To see the entity, navigate to the webtools component's Entity Data Maintenance screen by firing an https OFBiz main to webapp webtools and selecting Entity Data Maintenance from the list.

Look the entry for entity Planet and click its Fnd button (short for "Find").

From here on, we will refer to these screens as the Entity Data Maintenance screens. The screen below will be referred to as the entity data maintenance screen for the entity Planet.

Using Our First Entity

In the section called Changing the Looks in Chapter 2, we inserted a form field planet into the Edit Contact Information screen. Fire an https OFBiz request to webapp partymgr and bring up any party, say last name Researcher and first name OFBiz. Enter this party profile and edit (update) one of the existing Postal Addresses. Create a new one if there are none listed. The familiar form field planet should show up. It is currently a text field, for free-form text entry.

Creating a Drop-Down

Instead of a free-form text field, we will provide a drop-down list populated with values from the entity Planet that we just created. In the file ${component:learning}\widget\partymgr\OurPartyScreens.xml create a new screen widget called editcontactmech.planet.dropdown:

<screen name="editcontactmech.planet.dropdown">
<section>
<actions>
<entity-one entity-name="Planet" value-name="planet">
<field-map field-name="planetId" env-name="mechMap.postalAddress.planet"/>
</entity-one>
<entity-condition entity-name="Planet" filter-by-date="true" list-name="planets">
<order-by field-name="planetName"/>
</entity-condition>
</actions>
<widgets>
<platform-specific><html>
<html-template location="component://learning/webapp/partymgr/ party/editcontactmech.planet.dropdown.ftl"/>
</html></platform-specific>
</widgets>
</section>
</screen>

The <entity-condition> element queries the database and pulls down all records in the entity Planet. The list of records is stored in the list variable planets. The sub-element <order-by> tells the query to return the results sorted by the field Planet.planetName. The default sort order is ascending alphabetically. To get a descending order, set the attribute field-name to planetName DESC.

The filter-by-date attribute specifies that the query must filter out those records that are not active now. Inactive records have the fields fromDate and thruDate (both date-time fields) that don't frame the current time. Recall that the entity Planet has those two fields. This is a neat feature in OFBiz that provides for easy audit trail mechanism. Rather than deleting a record, it is merely expired. An empty fromDate is deemed to stretch to forever into the past; an empty thruDate covers forever into the future.

The <entity-one> element retrieves the record from entity Planet that has the field Planet.planetId with a value matching the postal address PostalAddress.planet field. If found, the record is placed into the variable planet. Note how the entity PostalAddress is related to the entity Planet via the field PostalAddress.planet. We will discuss relationships between entities later.

These elements in the <actions> element are actually Minilang—the OFBiz programming language. It will be discussed later in this book.

Now, we create the corresponding "html template". In the folder ${component:learning}\webapp\partymgr\party create a new file called editcontactmech.planet.dropdown.ftl and enter into it this:

<tr>
<td class="label">Planet</td>
<td>
<#assign fieldName = "planet"/>
<select name="${fieldName}">
<#if (planet)??>
<option value="${planet.planetId}"/>${(planet.planetName)!""} </option>
</#if>
<option></option>
<#if request.getParameter("${fieldName}")??>
<#assign requestPlanet = request.getParameter("${fieldName}")/>
</#if>
<#list planets as planet>
<option value="${planet.planetId}" <#if requestPlanet?? && requestPlanet == planet.planetId>selected</#if>>
${planet.planetName}
</option>
</#list>
</select>
</td>
</tr>

Lastly, we point editcontactmech.ftl to include the new screen widget. Edit ${webapp:party}\party\editcontactmech.ftl and replace (around line 185):

${screens.render("component://learning/widget/partymgr/ OurPartyScreens.xml#editcontactmech.extend")}

with this:

${screens.render("component://learning/widget/partymgr/ OurPartyScreens.xml#editcontactmech.planet.dropdown")}

Go back to the Edit Contact Information screen to see the new drop-down field Planet.

If we were building the equivalent of the above editcontactmech.planet.dropdown.ftl with a form widget, we would have done:

<form name="PlanetDropDown" type="single" skip-start="true" skip-end="true">
<field name="planetId" title="Planet" map-name="mechMap.postalAddress">
<drop-down allow-empty="true" current="selected">
<entity-options description="${planetName}" entity-name="Planet" key-field-name="planetId" filter-by-date="true">
<entity-order-by field-name="planetName"/>
</entity-options>
</drop-down>
</field>
</form>

Populating the Drop-Down

The drop-down is still empty. It is pulling its options population from the database entity Planet. Go to the entity data maintenance for the entity Planet. Click Create New Planet, then click Edit. Enter for fields planetId and planetName values of MARS and Mars, respectively. Create a second record with the values EARTH and Earth. Create a 3rd record with the values PLUTO and Pluto.

Go back to the Edit Postal Address screen to see the three values in the drop-down field Planet.

Expiring a Value

Suppose we now decide that Pluto is too far away to colonize. Rather than removing the planet from the database altogether, we expire it. That way, we can always remember that we had a value Pluto before. To expire Pluto, enter for its field thruDate a value that is before now, say last month.

Go back to the Edit Contact Information screen to see that there are two values left. Pluto has been expired.

Un-Expiring a Value

To un-expire a value, we can set in field thruDate a time far in the future. However, we would have to come back to un-expire the value again once that future time has come. The correct way to un-expire a record for good (until further notice) is to set the thruDate field to null (that means "empty" or "no value" in computing terminology).

Delete the planet Pluto's value for thruDate and leaving it blank, click Update to un-expire this record.

Anatomy of an <entity> Element

The attribute name of an <entity> element is required; it uniquely identifies the entity in the entire Entity Engine. There cannot be entities with similar names, even if they are placed into different entity groups or packages (explained next).

The attribute package-name simply serves as a taxonomical tool, allowing OFBiz to deal with entities, for example, under different packages differently. So far, it is only used by the Entity Engine's data synchronization tool, which is related to Online Analytical Processing (OLAP) and involves multiple database machines (one active and one for OLAP), something we won't be covering in this book.

An <entity> element can contain <field> elements, <prim-key> elements, <relation> elements, and <index> elements, in that order. There must be at least one <field> element.

Let's take another look at our Planet entity that we defined in the entitymodel.xml file:

<entity entity-name="Planet" package-name="org.ofbiz.learning">
<field name="planetId" type="id-ne"/>
<field name="planetName" type="name"/>
<field name="fromDate" type="date-time"/>
<field name="thruDate" type="date-time"/>
<prim-key field="planetId"/>
<index name="PLANET_NAME_IDX" unique="true">
<index-field name="planetName"/>
</index>
</entity>

The <field> Element

The <field> elements define fields in an entity. Each <field> element must at least have the attributes name and type defined. Obviously, the name attribute is required to provide a unique reference or handle on the field. The type attribute determines the data type that the field will and must contain.

Database Independence

Different databases have different data types, just like different languages have different vocabularies. But just as languages can have equivalent counter-parts, different databases also often have equivalent data types between them. OFBiz achieves database independence (ability to couple with different databases at the flip of a switch) by defining standard "OFBiz data types"—types that mean something to OFBiz. For each of these OFBiz data types, OFBiz has a mapping to an equivalent database specific data type. This way, OFBiz code needs only deal with a single set of data types—OFBiz data types. OFBiz is in charge of translating its own data types into database-specific ones.

The data type mappings are in the folder ${component:entity}\fieldtype. Supported databases are Derby, PostgreSQL, MySQL, MSSQL, Oracle, MaxDB, and many others. The folder contains one mapping file fieldtype<database-name>.xml for each database OFBiz supports (for example fieldtypederby.xml). Each mapping file is loaded in file ${component:entity}\config by a <field-type> element.

The "type" Attribute

Each <field> element must have a type attribute specified. The type attribute must have a value of one of the OFBiz data types. The full list of data types is listed in any of the mapping files, such as fieldtypederby.xml.

In that mapping file, each data type is defined by a <field-type-def> element. Here is one example:

<field-type-def type="date-time" sql-type="TIMESTAMP" java-type="java.sql.Timestamp">

The data type name is in attribute type. The attribute sql-type is the RDBMS-specific data type that this OFBiz data type maps to. The attribute java-type tells OFBiz which Java type to use to handle the data type.

The <prim-key> Element

Every record in an entity needs a unique identity or signature so that it can be referenced. This unique identity in database terms is known as a primary key. The primary key of an entity is defined by its <prim-key> elements.

There can be multiple <prim-key> elements. Multiple elements do not mean there are multiple ways to identify a record. Some entities may require more details to uniquely identify their records. Using the namespace analogy we used before, two people named "Peter Parker" will have the same values for the key that is their names. An additional key will be required to uniquely identify them, say a key that is their address of residence. If they happen to live in the same house, yet another key will be needed! Some countries require a unique identity number for their citizens.

A more realistic example in the computing context is an entity that represents a relationship between two other entities, say between Party (a person or group) and PostalAddress. We have an entity PartyContactMech that records which person lives where. The primary key for this entity would be two fields—the unique ID for Party and that for PostalAddress. A schema designed like that will mean a person can live in more than one location, and each location can house more than one person.

The <relation> Element

Relationships between entities are defined by <relation> elements, and are called entity relations. Using the PartyContactMech entity as an example, we know that the field partyId relates to the Party entity. We could therefore place a foreign key relationship on the PartyContactMech.partyId field and the Party.partyId field. We can now ensure that all records in the PartyContactMech entity are related to a party in the Party entity. Therefore the PartyContactMech entity cannot contain information on a party that does not have a record in the Party entity.

Creating the Field that Participates in a Relation

In the file ${component:party}\entitydef\entitmodel.xml, edit entity PostalAddress and insert below the existing field planet (which we added in the section called Changing the Data in Chapter 2) at line 962:

<field name="planet" type="name"></field>

a new field "planetId" like this:

<field name="planetId" type="id"></field>

Right now, this field planetId is just a field. We need to create a relation that uses this field.

Creating the Relation

We now create a relation between the entities PostalAddress and Planet. Edit the entity PostalAddress and insert right below the <prim-key> element at line 967:

<prim-key field="contactMechId"/>

a new <relation> element like this:

<relation type="one" fk-name="POST_ADDR_PLANET" rel-entity-name="Planet">
<key-map field-name="planetId"/>
</relation>

The foreign key that links the entity PostalAddress to the entity Planet is the field PostalAddress.planetId, as specified by the <key-map> sub-element. By default, the <key-map> links the related entities via fields with the same name as specified in the attribute field-name (PostalAddress.planetId and Planet.planetId in this case). It is possible to point the field PostalAddress.planetId to some other field by specifying an additional attribute rel-field-name with a value of say someOtherField.

Each <relation> element must have at least one <key-map> sub-element.

The type attribute specifies the type of relation. In our new relation, each PostalAddress record points to exactly one Planet record. There can be other types of relations, which we will discuss later.

The fk-name attribute specifies the name of this foreign key (the field PostalAddress.planetId), and can also be considered the name of this relation. Although OFBiz can automatically generate a foreign key name if none is specified, the generated name might be too long for some databases (some have a maximum length as short as 18 characters). It is best to have a consistent convention in naming relations. In our relation, the foreign key name POST_ADDR_PLANET suggests that it is a relation from a postal address to a planet.

The rel-entity-name attribute points to the other end of this relation, the entity that PostalAddress is related to. Restart OFBiz to let OFBiz pick up the new <relation> element in the XML definition file ${component:party}\entitydef\ entitymodel.xml.

Return to the OFBiz entity data maintenance screen for PostalAddress and select View Relations.

Accessing the Relation

Recall that we previously used the field PostalAddress.planet as the unofficial foreign key or link to the field Planet.planetId. In the following code, the variable mechMap.postalAddress is a PostalAddress record, so mechMap.postalAddress.planet will refer to the field planet in the entity PostalAddress. Look in the screen widget editcontactmech.planet.dropdown for this:

<entity-one entity-name="Planet" value-name="planet">
<field-map field-name="planetId" env-name="mechMap.postalAddress.planet"/>
</entity-one>

However, the relation we just created uses the field PostalAddress.planetId as the foreign key. Do we need to change mechMap.postalAddress.planet to mechMap.postalAddress.planetId? With the OFBiz entity relation framework, we just need to tell OFBiz that we want the relation Planet of the entity PostalAddress. We don't need to bother with finding out exactly which fields are linked up, planet or planetId.

In the screen editcontactmech.planet.dropdown, replace the <entity-one> element with this:

<get-related-one value-name="mechMap.postalAddress" relation-name="Planet" to-value-name="planet"/>

The above <get-related-one> element tells OFBiz we want to get the relation named Planet for the PostalAddress record we are holding in the variable mechMap.postalAddress. The related Planet record is placed into the variable planet. Test this and see it work.

The name of a relation is typically the name of the related entity, as defined by the attribute rel-entity-name of the element <relation>. Sometimes, there may be more than one relation to the same entity, such as when country codes and state codes are all stored in the entity Geo (for "Geographical Location"). In this case, the attribute title will distinguish between such relations by prefixing itself to the attribute rel-entity-name to form the relation name. In the entity PostalAddress, for example, there are two relations named CountryGeo and StateProvinceGeo to the entity Geo:

<relation type="one" fk-name="POST_ADDR_CGEO" title="Country" rel-entity-name="Geo">
<key-map field-name="countryGeoId" rel-field-name="geoId"/>
</relation>
<relation type="one" fk-name="POST_ADDR_SPGEO" title="StateProvince" rel-entity-name="Geo">
<key-map field-name="stateProvinceGeoId" rel-field-name="geoId"/>
</relation>

Since we are no longer using the field PostalAddress.planet, but using the field planetId instead, we should change our form field accordingly. Edit the file editcontactmech.planet.dropdown.ftl and replace line 3:

<#assign fieldName = "planet"/>

with:

<#assign fieldName = "planetId"/>

And finally we must change out the postalAddressAdvisory method in package org.ofbiz.learning.LearningEvents to this:

public static final String module = LearningEvents.class.getName();
public static String postalAddressAdvisory(HttpServletRequest request, HttpServletResponse response){
String partyId = request.getParameter("partyId");
Map mechMap = new HashMap();
ContactMechWorker.getContactMechAndRelated(request, partyId, mechMap);
GenericValue postalAddress = (GenericValue)mechMap.get("postalAddress");
if (postalAddress == null) return "notMars";
GenericValue planetGV = null;
try{
planetGV = postalAddress.getRelatedOne("Planet");
}catch(GenericEntityException e){
Debug.logError(e, "An Error Occurred", module);
}
String planet = (planetGV != null) ? planetGV.getString("planetId") : null;
if (planet == null || !planet.equalsIgnoreCase("Mars")) return "notMars";
return "isMars";
}

There are a number of new concepts in this code. Do not worry if they are not fully understood at the moment, they will all be explained later in the book. The following import statements must be added to the class:

import org.ofbiz.entity.GenericEntityException;
import org.ofbiz.entity.GenericValue;
import org.ofbiz.base.util.Debug;

The GenericValue is a Java class used by OFBiz as a holder for any arbitrary database record. A single GenericValue represents a single record. OFBiz uses Apache Log4J to produce the log files and the Debug class is a utility class that writes to the logs depending on the settings in the debug.properties file in the ${OFBizInstallFolder}\framework\base\config folder.

Note

Don't forget to shutdown OFBiz and recompile the learning component!

Updating the Database

Although OFBiz now recognizes that the relation has been added to the entity PostalAddress, the database is still unaware of it. As mentioned in the section called Updating the Database in Chapter 2, we merely have to restart OFBiz whenever we add new entities or entity fields. However, updating the database with foreign keys isn't so trivial.

There are some quirks here. If an entity has not yet been created, OFBiz will create it as well as any foreign keys in it. In our case, the entity PostalAddress already existed when we added the new relation. Deleting the entity is not a good option because we will lose all the existing data in there.

For now, we want to confirm that the foreign key name POST_ADDR_PLANET really does not yet exist in the database. Go to the entity data maintenance screen for the entity PostalAddress to look at the PostalAddress records.

Click on Find All to pull down around 13 records (more if you had played with OFBiz postal addresses a great deal). Pick the first record, say with contactMechId of 9000, and click on its View button. Click Edit, and enter into the field planetId a value that does not exist in the entity Planet, say VENUS. That is, enter any value that is not MARS, EARTH, or PLUTO. Click on Update. You'll see that the update was successful. This means that the foreign key POST_ADDR_PLANET is not in the database.

Change the value of the field planetId back to null (empty) or to any values existing in the entity Planet.planetId, such as EARTH. Having a PostalAddress record that points to a non-existent Planet record will prevent us from creating the foreign key in the database.

One of the main benefits of foreign keys is that most (if not all) databases help maintain "data integrity" with them. The database makes sure that all foreign keys point to something that exists, so that all foreign keys are meaningful. It is meaningless for foreign keys to point to a non-existent record, as well as misleading.

The <index> Element

The records in a database entity can possibly be stored in some random order, and arbitrary locations on the hard disk. Just like a directory or index of "people-to-address" helps us locate people in the real world scattered into various locations, an index in a database speeds up the retrieval of records.

Besides serving as a performance tool enhancing data retrieval speeds, indexes also allow us to enforce certain constraints, such as what we have in the entity Planet: no two planets can have the same value for the field planetName.

<index name="PLANET_NAME_IDX" unique="true">
<index-field name="planetName"/>
</index>

In each entity, indexes can be defined on any field. It is defined by an <index> element like this:

<index name="POSTAL_CODE_IDX">
<index-field name="postalCode"/>
</index>

It is likely that there is a common need to search postal addresses by postal code, hence the index to speed up data retrieval. Other fields in the entity PostalAddress that have indexes are address1, address2, and city.

Just as a foreign key has a name (defined in the attribute fk-name of a <relation> element), an index also has a name. It is defined in the attribute name.

The attribute unique, if true, will enforce a unique constraint over the fields defined under the index. Note that an index defined over multiple fields will only have its unique constraint violated if there are similar combinations of values of those fields. This is similar to a prior example in this chapter, where we explored primary keys defined over multiple fields (Person and PostalAddress). Primary keys inherently have a unique constraint.

The sub-element <index-field> specifies which entity field this index is defined on. In the above example, the index POSTAL_CODE_IDX is defined on the field PostalAddress.postalCode. Just like primary keys defined over multiple fields, indexes can also be defined over multiple fields.

It may seem tempting to go ahead and add an index on every field that is being looked up, or even those that may be looked up in the future, however careful consideration should be given as to which fields are indexed. If an index was placed on every field, the overhead of maintaining the indexes would far outweigh the benefits.

Indexes on Foreign Keys

It is often a good idea to index the foreign keys. Some databases like MySQL automatically create an index on foreign keys because:

"[A database] requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. The index on the foreign key is created automatically."

(This excerpt has been taken from the MySQL documentation on foreign keys http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html)

A "foreign key check" is a check by the database that all foreign keys refer to existing records. For example, if the entity Planet does not have a record with a planetId of VENUS, then there should be no records in PostalAddress that have a planetId with the value of VENUS. The database goes through all the records with the entity PostalAddress to do this check. Rather than hunting all over the hard disk for all the records of PostalAddress, a convenient index on the foreign key PostalAddress.planetId can quickly call up every record (that has a non-empty planetId) for checking.

OFBiz will automatically create indexes on foreign keys if the datasource has the attribute use-foreign-key-indices set to true, which is the case by default. The datasource localderby does not specify this attribute, so it is true by default.

A good practice is to avoid specifying indexes on foreign keys when developing in OFBiz. It will only add clutter, since OFBiz can be centrally configured (at the datasource definition) to automatically create indexes on foreign keys. Since many databases do create implicit indexes on foreign keys, you may also choose to turn off the use-foreign-key-indices so that indexes are not created doubly over the same fields.

Relation Types

There are primarily two types of entity relations: one-to-one and one-to-many. They are denoted by <relation type="one"> and <relation type="many"> elements, respectively. Entity relations are always defined as between two entities.

The first thing to note is the left side of the -to- where we define an entity relation. Of the many fields in a person's particulars, one of them, say the residence address field, may point to a postal address; the person record is the source of the relation. The postal address record minds its own business, and has no explicit link (no foreign key) pointing to the person; it is the destination of the relation. That is a good rule of thumb to keep in mind when deciding where to define entity relations.

We define entity relations on the source or referencing entity, with the relations pointing to the destination or referenced entity. A relation defined on an entity can be expressed like "points to related entity". A one-to-one relation means ‘points to one record of related entity'; a one-to-many relation means ‘points to many records of related entity'.

One-to-One Relations

A type "one" entity relation in OFBiz is defined by a <relation type="one"> element. It creates a foreign key with one or more of the entity's fields, as specified by the one or more <key-map> sub-elements.

The foreign key must contain the same fields in the same order as the primary key fields in the referenced entity. Every foreign key must either reference a unique record in the referenced entity, or be null and reference nothing at all.

One-to-Many Relations

A type "many" relation is defined by a <relation type="many"> element. No foreign key is created.

Inverse of One-to-One Relations

One-to-Many relations are often merely the inverse of One-to-One relations. It's like peering through the other end of the telescope. Say there are several records in the entity PostalAddress referring to a single record in Planet, which means several postal addresses are on the same planet. To each postal address, it is one postal address record referring to one planet record, hence the one-to-one relation. To the planet record, on the other hand, it sees many postal address records referring to one planet record that is itself.

From the perspective of the entity PostalAddress, it has a one PostalAddress record to one Planet record relation, a one-to-one relation with entity Planet. To the entity Planet, it has a one Planet record to many PostalAddress records relation, a one-to-many relation.

There is no need to define type "many" relations in such cases because one is implicitly created for every type "one" relation.

Since a pair of corresponding relations: type "one" and type "many", can be succinctly and sufficiently defined by a single type "one" relation, OFBiz does not create foreign keys for type "many" relations. It doesn't make sense to do so anyway, because a foreign key must point clearly to a unique referenced record rather than point ambiguously to multiple records. In fact, in many databases, there is no such thing as a foreign key for a one-to-many relation (some will argue that this should be the correct behavior for all databases).

To see an implicit type "many" relation in action, let's look at the entity Planet. the Entity Planet does not specify any relations at all, yet an implicit relation named PostalAddress does exist. To see it in action, create a new screen widget ShowPlanetManyRelation. In the folder ${component:learning}\widget\learning, insert into the file LearningScreens.xml this:

<screen name="ShowPlanetManyRelation">
<section>
<actions>
<!-- First, get all planets. -->
<entity-condition entity-name="Planet" filter-by-date="true" list-name="planets">
<order-by field-name="planetName"/>
</entity-condition>
</actions>
<widgets>
<!-- For each planet, show addresses -->
<iterate-section entry-name="planet" list-name="planets">
<section>
<actions>
<get-related value-name="planet" relation-name="PostalAddress" list-name="planetAddresses"/>
</actions>
<widgets>
<platform-specific><html>
<html-template location="component://learning/webapp/ learning/planetAddresses.ftl"/>
</html></platform-specific>
</widgets>
</section>
</iterate-section>
</widgets>
</section>
</screen>

In the folder ${webapp:learning}, create a new file planetAddresses.ftl and enter into it this:

<b>Planet: <i>${planet.planetName}</i></b>
<br/>
<table border="1">
<tr>
<td>contactMechId</td>
<td>Address</td>
<td>City</td>
<td>Postal Code</td>
</tr>
<#list planetAddresses as address>
<tr>
<td>${address.contactMechId}</td>
<td>${address.address1!""} ${address.address2!""}</td>
<td>${address.city!""}</td>
<td>${address.postalCode!""}</td>
</tr>
</#list>
</table>

In the folder ${webapp:learning}\WEB-INF, insert into the file controller.xml a new request map:

<request-map uri="ShowPlanetManyRelation">
<response name="success" type="view" value="ShowPlanetManyRelation"/>
</request-map>

and a new view map:

<view-map name="ShowPlanetManyRelation" type="screen" page="component://learning/widget/learning/ LearningScreens.xml#ShowPlanetManyRelation"/>

From the data maintenance screen for the entity PostalAddress, edit a few postal addresses to link them (via the field planetId) to any of the three planets: EARTH, MARS, and PLUTO. Fire an http OFBiz request ShowPlanetManyRelation to webapp learning to see the addresses listed according to the planets they are on.

Defining an explicit type "many" relation in the entity Planet is not necessary. In fact, OFBiz will even warn against that. In the folder ${component:learning}\entitydef, edit the file entitymodel.xml to add to the entity Planet this:

<relation type="many" rel-entity-name="PostalAddress">
<key-map field-name="planetId"/>
</relation>

Restart OFBiz. OFBiz will refuse to create the explicitly defined relation because it already exists implicitly. In the folder ${OFBizInstallFolder}\runtime\logs, open file ofbiz.log and find the following:

Entity [org.ofbiz.learning:Planet] already has identical relationship to entity [PostalAddress] title []; would auto-create: type [many] and fields [planetId]

One-to-One Relations with No Foreign Keys

These relations are defined by <relation type="one-nofk"> elements. They are legitimate type "one" relations, but without the accompanying foreign keys. They are not pseudo type "one" relations implemented with type "many" relations. Like type "one" relations, they must also point to the primary key in the referenced entity.

They are mostly used for archived records. Say we have a list of both local and out of town wedding guests who have attended a wedding in a small town. This attendance list will have a type "one-nofk" relation to the local town person's registry. Because this attendance list is merely an archived log, we don't want to have a foreign key created between it and the town person's registry.

View Entities

Generally speaking, a view entity is a collection of participating or member entities joined together by a number of relations. This collection forms a structure, where every member entity in a view entity is connected to the overall structure, no entity is left out. In short, a view entity provides a single view of a group of interconnected entities. This is very convenient for performing complex database queries that need to take such high-level views of data.

Technically speaking, a view entity could possibly be a complex hierarchy of relations between a number of member entities. The term hierarchy is used because the structure described by a view entity is not cyclical but is strictly top-down. This does not mean that a member entity will not be referenced more than once. A member entity can even be referenced in more than one level, say at a higher level and then again at a lower level, in effect giving the impression that the structure described is cyclical.

When we say hierarchy here, we are not talking about the member entities themselves, but about the roles instead. Each participating entity participates in a view entity in a certain capacity or role. Let's look at a simple example, a view entity containing two participating entities PersonL and HotelL:

<view-entity entity-name="GuestHotelOwnerViewL" package-name="org.ofbiz.learning">
<member-entity entity-alias="HotelGuest" entity-name="PersonL"/>
<member-entity entity-alias="Hotel" entity-name="HotelL"/>
<member-entity entity-alias="HotelOwner" entity-name="PersonL"/>
<view-link entity-alias="HotelGuest" rel-entity-alias="Hotel">
<key-map field-name="housedAt" rel-field-name="id"/>
</view-link>
<view-link entity-alias="Hotel" rel-entity-alias="HotelOwner">
<key-map field-name="ownedBy" rel-field-name="id"/>
</view-link>
</view-entity>

We use entity names PersonL and HotelL because the OFBiz base framework already has an entity named Person. The L is just an arbitrary naming convention we use here, which denotes our "learning" application.

A person can own one or more hotels, and each hotel can have one or more guests. A hotel owner and a hotel guest are both PersonL records. That would mean we have a view entity where PersonL is connected to HotelL is connected to PersonL. This seems cyclical. However, when we think in terms of roles, we get a hierarchy—a hotel owner owns one or more hotels which can house one or more hotel guests.

When we say top-down hierarchy, we take the first referencing entity as the "top" or first level. In the above example, that would be a hotel guest. Each hotel guest is housed at one hotel, each hotel is owned by one hotel owner. Each relation in a view entity, defined by a <view-link>, is usually like a type "one" <relation>, though it doesn't have to be.

Anatomy of a <view-entity>

A view entity is made up of four parts:

  • the member entities

  • the fields (just like fields in an entity)

  • the view links (the relations that connect member entities together)

  • the relations (just like the relations an entity has with other entities)

The <member-entity> Element

Usually, a view entity has two or more member entities. Each entity's participation or membership in a view entity is defined by a <member-entity> element. A sample is given here:

<member-entity entity-alias="HotelGuest" entity-name="PersonL"/>

Each member entity, say PersonL, must participate with a specified role, that role being defined by the attribute entity-alias. In the underlying RDBMS, what really happens is that an alias is created for the entity PersonL, hence the attribute name entity-alias. Since an alias is the name that a view entity uses to uniquely reference each participating entity, each alias name must be unique within the view entity. That is, we cannot have:

<member-entity entity-alias="HotelGuest" entity-name="PersonL"/>
<member-entity entity-alias="HotelGuest" entity-name="HotelL"/>

An entity can participate in a view entity more than once, each time with a different role. For example, the entity PersonL can be a hotel guest as well as a hotel owner:

<member-entity entity-alias="HotelGuest" entity-name="PersonL"/>
<member-entity entity-alias="HotelOwner" entity-name="PersonL"/>

As discussed, a view entity describes a hierarchy of member entities. By convention, member entities are listed in order according to the hierarchy described. The hierarchy is usually a chain of type "one" relations. For example, if a hotel guest is housed at one hotel, and a hotel is owned by one hotel owner, we will have:

<member-entity entity-alias="HotelGuest" entity-name="PersonL"/>
<member-entity entity-alias="Hotel" entity-name="HotelL"/>
<member-entity entity-alias="HotelOwner" entity-name="PersonL"/>

A view entity can have one member entity. Although it seems unnatural to have a view entity of just one member entity, this type of view entity is often employed to confine access to a subset of the entity's fields. Say we want to confine a particular application to seeing only a subset of fields in an entity, we would create a view entity with such a subset published. There will be more on this later.

Note that a hierarchy is like a tree, not a linear chain. The examples in this chapter just happen to be a linear chain.

The <alias> and <alias-all> Elements

Like an entity, a view entity has fields. In this respect, think of a view entity like you would a normal entity. In fact, a view entity is accessed or queried in the exact same way an entity is. A view entity GuestHotelOwnerView can have fields like guestFirstName and hotelName, which are referred to as fields GuestHotelOwnerView.guestFirstName and GuestHotelOwnerView.hotelName.

The <alias> Element

To define a view entity field, we use an <alias> element. Like an <entity-alias>, we're dealing with aliases here. In the folder ${component:learning}\entitydef, edit the file entitymodel.xml to add a new entity PersonL:

<entity entity-name="PersonL" package-name="org.ofbiz.learning">
<field name="personId" type="id-ne"></field>
<field name="firstName" type="name"></field>
<field name="lastName" type="name"></field>
<field name="housedAt" type="id"></field>
<field name="someExtraDetails" type="description"></field>
<prim-key field="personId"/>
<relation type="one-nofk" fk-name="PERSON_HOTEL" rel-entity-name="HotelL">
<key-map field-name="housedAt" rel-field-name="hotelId"/>
</relation>
</entity>

and a new entity"HotelL":

<entity entity-name="HotelL" package-name="org.ofbiz.learning">
<field name="hotelId" type="id-ne"></field>
<field name="hotelName" type="name"></field>
<field name="ownedBy" type="id"></field>
<field name="postalAddressId" type="id"></field>
<prim-key field="hotelId"/>
<relation type="one-nofk" fk-name="HOTEL_OWNER" rel-entity-name="PersonL">
<key-map field-name="ownedBy" rel-field-name="personId"/>
</relation>
</entity>

Say we want a view entity with five fields: guest name (first and last), hotel name, and hotel owner (first and last name). In entitymodel.xml, add a new view entity GuestHotelOwnerViewL:

<view-entity entity-name="GuestHotelOwnerViewL" package-name="org.ofbiz.learning">
<member-entity entity-alias="HotelGuest" entity-name="PersonL"/>
<member-entity entity-alias="Hotel" entity-name="HotelL"/>
<member-entity entity-alias="HotelOwner" entity-name="PersonL"/>
<alias entity-alias="HotelGuest" name="guestFirstName" field="firstName"/>
<alias entity-alias="HotelGuest" name="guestLastName" field="lastName"/>
<alias entity-alias="Hotel" name="hotelName" field="hotelName"/>
<alias entity-alias="HotelOwner" name="ownerFirstName" field="firstName"/>
<alias entity-alias="HotelOwner" name="ownerLastName" field="lastName"/>
<view-link entity-alias="HotelGuest" rel-entity-alias="Hotel">
<key-map field-name="housedAt" rel-field-name="hotelId"/>
</view-link>
<view-link entity-alias="Hotel" rel-entity-alias="HotelOwner">
<key-map field-name="ownedBy" rel-field-name="personId"/>
</view-link>
</view-entity>

Entries must also be added for these entities into the entitygroup.xml file in the same folder:

<entity-group group="org.ofbiz" entity="PersonL"/>
<entity-group group="org.ofbiz" entity="HotelL"/>
<entity-group group="org.ofbiz" entity="GuestHotelOwnerViewL"/>

Note that, currently, even view entities must be assigned to a group. The above view entity presents a neat view of two entities in three roles, each record with fields guestFirstName, guestLastName, hotelName, ownerFirstName, and ownerLastName.

Go to the data maintenance screen for the entity HotelL and enter two HotelL records like this:

Take note of the postalAddressId field. We are going to borrow some existing address records for our Hotel to save us from adding new ones. This makes Brad's hotel located in Los Angeles and Hank's hotel located in New York.

Go to the data maintenance screen for the entity PersonL and enter three PersonL records like this:

Go to the data maintenance screen for view entity GuestHotelOwnerViewL and click on Find All to see all records in the view:

Seeing the SQL Equivalent

It may be simpler to see what is happening here by examining the standard SQL equivalent.

SELECT HotelGuest.firstName as guestFirstName, HotelGuest.lastName as guestLastName, Hotel.hotelName, HotelOwner.firstName as ownerFirstName,
HotelOwner.lastName as ownerFirstName
FROM PersonL HotelGuest
INNER JOIN HotelL Hotel ON (HotelGuest.housedAt = Hotel.hotelId)
INNER JOIN PersonL HotelOwner ON (Hotel.ownedBy = HotelOwner.personId)

All underlying queries and communications to the database can be obtained by accessing the main Webtools page and selecting Adjust Debugging Levels, ticking the Verbose checkbox, and clicking OK. This change will only last the duration of this session. To make the change permanent, edit the debug.properties file in ${OFBizInstallFolder}\framework\base\config and set print.verbose=true.

OFBiz produces the following Query:

SELECT HotelGuest.FIRST_NAME, HotelGuest.LAST_NAME, Hotel.HOTEL_NAME, HotelOwner.FIRST_NAME, HotelOwner.LAST_NAME FROM (OFBIZ.PERSON_L HotelGuest INNER JOIN OFBIZ.HOTEL_L Hotel ON HotelGuest.HOUSED_AT = Hotel.HOTEL_ID)
INNER JOIN OFBIZ.PERSON_L HotelOwner ON Hotel.OWNED_BY = HotelOwner.PERSON_ID

The reason for the difference is that OFBiz produces SQL that will work on a number of databases and so the SQL produced is generic. Some databases may store case dependent table names and field names and some might not. For this reason, it was decided that all table names and field names be stored in upper case—removing the case dependency. This is completely removed from us. Unless we are directly querying the database we will never see this, as the framework allows us to use mixed case table and field names and will then convert accordingly, so:

hotelName becomes HOTEL_NAME

In this case the framework also takes care of the aliasing of field names.

Return to the main Webtools page, and select the Entity SQL Processor. This handy tool allows you type SQL in and check the results. This processor "bypasses" the framework changes to the field names and as such the table and field names must be referenced as they are physically stored (example, HOTEL_NAME).

The "all including" <alias-all> Element

The <alias-all> element includes all fields from the specified member entity. It has the ability to exclude some fields. It can also add a prefix to the included field names to generate meaningful alias names.

The equivalent of the above view entity using <alias-all> elements:

<view-entity entity-name="GuestHotelOwnerViewAllL" package-name="org.ofbiz.learning">
<member-entity entity-alias="HotelGuest" entity-name="PersonL"/>
<member-entity entity-alias="Hotel" entity-name="HotelL"/>
<member-entity entity-alias="HotelOwner" entity-name="PersonL"/>
<alias-all entity-alias="HotelGuest" prefix="guest">
<exclude field="id"/><exclude field="housedAt"/>
<exclude field="someExtraDetails"/>
</alias-all>
<alias-all entity-alias="Hotel" prefix="hotel">
<exclude field="hotelId"/><exclude field="ownedBy"/>
</alias-all>
<alias-all entity-alias="HotelOwner" prefix="owner">
<exclude field="personId"/><exclude field="housedAt"/>
<exclude field="someExtraDetails"/>
</alias-all>
<view-link entity-alias="HotelGuest" rel-entity-alias="Hotel">
<key-map field-name="housedAt" rel-field-name="hotelId"/>
</view-link>
<view-link entity-alias="Hotel" rel-entity-alias="HotelOwner">
<key-map field-name="ownedBy" rel-field-name="personId"/>
</view-link>
</view-entity>

Don't forget to add the entry into the relevant entitygroup.xml file!

Obviously, it only makes sense to use the <alias-all> element when there are more included fields than excluded ones.

As can be seen, Julia Roberts is housed at Brad's Hotel, and Brad Pitt himself is housed at Hank's Hotel. Tom Hanks is not housed anywhere.

The <view-link> Element

View links are the connections between member entities in a view entity. The number of view links in a view entity will always be one less than the number or member entities. If there were 10 people joined together by a single chain, there would be nine chain links.

A view link is defined by a <view-link>. A <view-link> has almost the same syntax and exactly the same structure as a <relation> element. A view link is, after all, really a relation between two member entities in a view entity. Note the similarity between:

<view-link entity-alias="HotelGuest" rel-entity-alias="Hotel">
<key-map field-name="housedAt" rel-field-name="hotelId"/>
</view-link>

and

<relation type="one" fk-name="PERSON_HOTEL" rel-entity-name="HotelL">
<key-map field-name="housedAt" rel-field-name="hotelId"/>
</relation>

Like the <relation>, each <view-link> element must have at least one <key-map> sub-element.

Inner Joins versus Outer Joins

The <view-link> has an attribute rel-optional to specify whether the relation between the joined entities is optional or not. If the relation is optional, then all records in the referencing member entity (specified by the attribute entity-alias) will be returned in a query to the view entity. If not, only records for which there are related records in the referenced entity (specified by the attribute rel-entity-alias) will be returned. To illustrate, consider these two view entities:

<view-entity entity-name="HotelGuests" package-name="org.ofbiz.learning">
<member-entity entity-alias="HotelGuest" entity-name="PersonL"/>
<member-entity entity-alias="Hotel" entity-name="HotelL"/>
<alias entity-alias="HotelGuest" name="guestFirstName" field="firstName"/>
<alias entity-alias="HotelGuest" name="guestLastName" field="lastName"/>
<alias entity-alias="Hotel" name="hotelName" field="hotelName"/>
<view-link entity-alias="HotelGuest" rel-entity-alias="Hotel">
<key-map field-name="housedAt" rel-field-name="hotelId"/>
</view-link>
</view-entity>

and

<view-entity entity-name="PersonHousingLocations" package-name="org.ofbiz.learning">
<member-entity entity-alias="HotelGuest" entity-name="PersonL"/>
<member-entity entity-alias="Hotel" entity-name="HotelL"/>
<alias entity-alias="HotelGuest" name="guestFirstName"
field="firstName"/>
<alias entity-alias="HotelGuest" name="guestLastName" field="lastName"/>
<alias entity-alias="Hotel" name="hotelName" field="hotelName"/>
<view-link entity-alias="HotelGuest" rel-entity-alias="Hotel" rel-optional="true">
<key-map field-name="housedAt" rel-field-name="hotelId"/>
</view-link>
</view-entity>

The first view entity will return PersonL records for which field PersonL.housedAt is linked to an existing Hotel record. PersonL records that have field PersonL.housedAt values of null (not residing in hotel) or some non-existent HotelL.hotelId will not be considered at all; the latter case occurs when the view link does not happen to mirror a type one relation. This view entity only considers hotel guests, and not persons who are currently not staying in a hotel.

The second view entity will return every Person record, regardless of whether the person is staying at a hotel or not. For every view entity record returned, a value of null in the field PersonHousingLocations.housedAt will mean that the person is currently not staying in a hotel. This view entity is concerned about the housing or hotel locations of all persons, not just hotel guests.

Tom Hanks is the only person not housed anywhere.

The first view entity performs an inner join between the entities PersonL and HotelL, or an equi-join specifically (a type of inner join). The second view entity performs a left outer join, a type of outer join where all the records of the left entity (PersonL in this case) are retained and considered.

The <relation> Element

The <relation> elements in a <view-entity> element are exactly like those in an <entity> element.

Applying Functions on Fields

View entity fields can also contain the result of functions, not just member entity field values. The functions available are as follows

  • count

  • count-distinct

  • min

  • max

  • sum

  • avg

  • upper

  • lower

To use a function, specify it in <alias> element's attribute function. Except for functions upper and lower, all the other functions are aggregate functions that deal with counting.

Counting the Number of Records

The function count counts the number of records. Say we want to count the number of records in the entity PostalAddress. In the folder ${component:learning}\entitydef, insert into the file entitymodel.xml a view entity TestAggregate:

<view-entity entity-name="TestAggregate" package-name="org.ofbiz.learning">
<member-entity entity-alias="PA" entity-name="PostalAddress"/>
<alias entity-alias="PA" name="contactMechId" function="count"/>
</view-entity>

Assign the view entity to the entity group org.ofbiz (edit file entitygroup.xml). Restart OFBiz and go to the data maintenance screen for the entity TestAggregate to see a single record displayed with a single field containing the total number of records for the entity PostalAddress.

Counting Distinct Records

To count the number of distinct records, we use the function count-distinct. Let's say we want to count the number of different cities in the PostalAddress records. We tell OFBiz to count the number of distinct city values. Go to the folder ${component:learning}\entitydef and insert a new view entity called TestCountDistinct into the file entitymodel.xml:

<view-entity entity-name="TestCountDistinct" package-name="org.ofbiz.learning">
<member-entity entity-alias="PA" entity-name="PostalAddress"/>
<alias entity-alias="PA" name="city" function="count-distinct"/>
</view-entity>

Assign the view entity to the entity group org.ofbiz and restart. Go to the data maintenance screen for the entity TestCountDistinct to see a single record displayed with a single field containing the total number of records for the entity PostalAddress with distinct city values. Or in short, the number of cities in those records.

Arithmetic Aggregate Functions

Arithmetic aggregate functions operate on numerical values:

  • min—get the minimum value of all selected records

  • max—get the maximum value of all selected records

  • sum—get the sum of the values of all selected records

  • avg—get the average of the values of all selected records

Uppercase and Lowercase Functions

Functions upper and lower are string transformation functions, not aggregate functions like the rest. Function upper transforms a string into uppercase; lower into lowercase. For example:

<view-entity entity-name="TestUpperLower" package-name="org.ofbiz.learning">
<member-entity entity-alias="PA" entity-name="PostalAddress"/>
<alias entity-alias="PA" name="upperToName" field="toName" function="upper"/>
<alias entity-alias="PA" name="lowerAddress1" field="address1" function="lower"/>
<alias-all entity-alias="PA"/>
</view-entity>

Grouping for Summary Views

When we want to group a set of data by a field, we use group-by in a view entity field.

To group by a field, set the attribute group-by of the element <alias> to true. Let's say we want to count the number of postal addresses by city:

<view-entity entity-name="TestGrouping" package-name="org.ofbiz.learning">
<member-entity entity-alias="PA" entity-name="PostalAddress"/>
<alias entity-alias="PA" name="count" field="contactMechId" function="count"/>
<alias entity-alias="PA" name="city" group-by="true"/>
</view-entity>

Enter the above view entity into the file entitymodel.xml in the folder ${component:learning}\entitydef. Also assign the view entity to the entity group org.ofbiz.

Restart OFBiz. Go to the entity data maintenance screen of view entity TestGrouping and click Find All. Go to the entity data maintenance screen of entity PostalAddress to see the postal addresses participating in the view entity, click Find All and see all eight cities (plus A City for person OFBiz Researcher) displayed, with the number of postal addresses for each city.

Complex Aliases

Complex aliases are fields in view entities that involve an operator that takes in two or more values, so it only makes sense for complex aliases to involve two or more fields. Examples are + and , where a typical expression could be fieldA + fieldB or fieldA - fieldB - fieldC.

Complex aliases are defined by <alias> elements that have a sub-element <complex-alias>. A <complex-alias> element should contain two or more <complex-alias-field> sub-elements. A <complex-alias-field> points to a member entity's field, where the attribute entity-alias specifies the member entity itself and the attribute field specifies a field in that member entity.

Nested <complex-alias> Elements

A <complex-alias> element can also contain another <complex-alias> element. You can think of a <complex-alias> element as adding parentheses around its fields, so "(a + b) * ((c - d) / e)" would be (note * is multiply and / is divide):

<member-entity entity-alias="EA" entity-name="SomeEntityAlias"/>
<alias name="complexComputedField">
<complex-alias operator="*">
<complex-alias operator="+">
<complex-alias-field entity-alias="EA" field="a"/>
<complex-alias-field entity-alias="EA" field="b"/>
</complex-alias>
<complex-alias operator="/">
<complex-alias operator="-">
<complex-alias-field entity-alias="EA" field="c"/>
<complex-alias-field entity-alias="EA" field="d"/>
</complex-alias>
<complex-alias-field entity-alias="EA" field="e"/>
</complex-alias>
</complex-alias>
</alias>

Operators are Database-Specific

Be careful when using complex aliases because the operator (attribute operator) is processed by OFBiz "as is". That is, whatever we enter for operator is passed straight to the underlying database in use at the time. Be sure to check that your database supports the operator you are using.

For example, it is not possible to use complex aliases to concatenate strings when using the database MySQL. MySQL does not recognize the || operator as a string concatenator. While the following will work with Derby or PostgreSQL, it won't with MySQL:

<view-entity entity-name="TestConcat" package-name="org.ofbiz.learning">
<complex-alias> elementoperators<member-entity entity-alias="PA" entity-name="PostalAddress"/>
<alias-all entity-alias="PA"/>
<alias name="addressFull">
<complex-alias operator="||">
<complex-alias-field entity-alias="PA" field="address1"/>
<complex-alias-field entity-alias="PA" field="address2"/>
</complex-alias>
</alias>
</view-entity>

While MySQL does allow for string concatenation with function CONCAT, OFBiz does not support that. The native MySQL syntax is CONCAT(string1, string2), so we might be tempted try to hack it with an operator value of ",". It won't work. We cannot do this:

<view-entity entity-name="TestMySQLConcat" package-name="org.ofbiz.learning">
<member-entity entity-alias="PA" entity-name="PostalAddress"/>
<alias-all entity-alias="PA"/>
<alias name="addressFull" function="concat">
<complex-alias operator=",">
<complex-alias-field entity-alias="PA" field="address1"/>
<complex-alias-field entity-alias="PA" field="address2"/>
</complex-alias>
</alias>
</view-entity>

The attribute function of element <alias> cannot be any value other than min, max, sum, avg, count, count-distinct, upper, or lower.

While OFBiz is largely database-independent, OFBiz was mostly developed and tested with Derby and PostgreSQL. OFBiz is most compatible with PostgreSQL.

Extending Entities

Entities can be extended by using <extend-entity> elements. Rather than specifying extensions or enhancements in the original entitymodel.xml files (the stock ones that came with OFBiz), we can put the extensions in our own entitymodel.xml file (like in the folder ${component:learning}\entitydef).

Here we take the chance to do some final clean up of the mess we made in Chapter 2, and also experiment with <extend-entity> at the same time. In folder ${component:learning}\entitydef, edit the file entitymodel.xml and insert this:

<extend-entity entity-name="PostalAddress">
<field name="planetId" type="id"></field>
<relation type="one" fk-name="POST_ADDR_PLANET" rel-entity-name="Planet">
<key-map field-name="planetId"/>
</relation>
</extend-entity>

Go to the folder ${component:party}\entitydef, edit the file entitymodel.xml and delete lines 962 - 963:

<field name="planet" type="name"></field>
<field name="planetId" type="id"></field>

and then lines 966 - 968:

<relation type="one" fk-name="POST_ADDR_PLANET" rel-entity-name="Planet">
<key-map field-name="planetId"/>
</relation>

We have now extended the entity PostalAddress without modifying the original OFBiz file.

For Java programmers, note that this is not the same as extending or sub-classing in Java programming. The original entity PostalAddress is extended; no new entity is created.

Summary

We have now covered all we need to know about creating entities, view entities, and relations between entities or view entities. We have learned much about the Entity Engine in OFBiz. Though this chapter seems a bit heavy, it is worth our time to get to grips with the Entity Engine. Learning the Entity Engine once and for all will allow us to use (or switch to) any of the databases supported by OFBiz.

In this chapter, we looked at:

  • Required Administrative Entity Engine concepts like datasources, entity delegators, and entity groups.

  • The anatomy of an Entity, that is the <entity> element, which can contain <field> elements, <prim-key> elements, <relation> elements, and <index> elements.

  • Creating our first entity, including the necessary administrative structure in entitygroup.xml and entitymodel.xml files, plus some additions to our "learning" component's component.xml file.

  • Using our first entity, creating a user-interface for it, and linking it in a relation to the entity PostalAddress.

  • Relations between entities via <relation> elements, and the corresponding foreign keys

  • Indexes on entity fields for fast lookup.

  • Relation Types—one, many, and one-nofk

  • The anatomy of a view entity, that is the <view-entity> element, which can contain <member-entity> elements, <alias> and <alias-all> elements, <view-link> elements, and even <relation> elements.

  • Linking entities (or even other view entities) into a view entity, via the <member-entity> elements and the <view-link> element.

  • Two Types of Linking: Inner Joins and Outer Joins, via the rel-optional attribute of <view-link> elements.

  • Functions (example arithmetic, counting) on view entity fields, via the function attribute of the <alias> (or more rarely, <complex-alias-field>) elements.

  • Grouping for summary views, via the group-by attribute of the <alias> elements.

  • Complex aliases (where functions don't cut it), via the <complex-alias> and <complex-alias-field> elements.

  • Extended entities, via <extend-entity> elements.

Having learned how to create the Entities and View Entities and the relations between them, we will next look at how to access them and the data in them. We will see how complicated queries can be constructed on the fly by using Dynamic View Entities. We will also learn how improve the application's performance by cutting down database traffic and the number of queries performed by using the Entity Engine cache.