I've been working with Expresso 5.5 for the last couple of months. Two things I've learned, though they certainly don't qualify as 'best practices':
1. Expresso provides a nice way to manipulate the model by setting certain criteria and then retrieving all the rows that match such a criteria. However, this can be abused.
For example, here we look up all the Indo-European languages:
MyLangDBObject lookup = new MyLangDBObject(); lookup.setField("FAMILY", "Indo-European");
for (Iterator i = lookup.searchAndRetrieveList().iterator(); i.hasNext(); ) {
MyLangDBObject instance = (MyLangDBObject) i.next();
// process instance
}
(For more, see Chapter 6 of the Expresso Developer's Guide.)
This is all well and good, as long as there are not a significant number of objects that match your criteria. Because each object is retrieved from the database and plunked into an ArrayList, this method can use a tremendous amount of memory. A more memory efficient method of retrieving and processing a large number of rows is:
MyLangDBObject lookup = new MyLangDBObject();
lookup.setField("FAMILY", "Indo-European");
lookup.search();
Object[] keys = lookup.getFoundKeys();
MyLangDBObject instance = new MyLangDBObject();
for (int i = 0; i <= keys.length; i++ ) {
String key = keys[i].toString();
instance.clear();
instance.setField("ID");
instance.retrieve();
// process instance
}
The above code still creates a large List, but each entry in that list is much smaller. I'm not sure how to treat objects with multi valued keys. I just looked in the Expresso 5.5 DBObject class, and it looks like multiple keys are concatenated with '/' and returned as a single string; beware as that's not documented anywhere and I haven't tested it.
2. When you're doing complicated filtering, DBObjects let you add a number of 'and' clauses. For example, this code finds all the dead Indo-European languages from Asia:
MyLangDBObject lookup = new MyLangDBObject();
lookup.setField("FAMILY", "Indo-European");
lookup.setField("GEOGRAPHIC_AREA", "Asia");lookup.setField("TYPE", "dead");
for (Iterator i = lookup.searchAndRetrieveList().iterator(); i.hasNext(); ) {
MyLangDBObject instance = (MyLangDBObject) i.next();
// process instance
}
This approach works well for quite a number of cases. However, if you want to do anything more complicated, such as date ranges or 'or' rather than 'and' clauses, you have three options.
* You can call setCustomWhereClause(). This allows you to escape the abstraction and essentially drops you down to SQL. All well and good; this should probably be your primary means of doing more complicated filtering. (Unfortunately, in Expresso 5.5, JoinedDataObjects, an Expresso construct which joins multiple tables together and presents a unified view thereof, do not support the setCustomWhereClause method. Apparently Expresso 5.6 has added such support.) This code finds all the languages that are dead or are Indo-European:
MyLangDBObject lookup = new MyLangDBObject();
lookup.setCustomWhereClause(
"FAMILY = \"Indo-European\" OR TYPE = \"dead\"");
for (Iterator i = lookup.searchAndRetrieveList().iterator(); i.hasNext(); ) {
MyLangDBObject instance = (MyLangDBObject) i.next();
// process instance
}
* You can pull back the data and filter on it in the middleware server. This is a bad idea, since you're not only using java where SQL would be better used, you're also pulling back unneeded data. However, it is an option that will always work, though it may be slow. For example, if the setCustomWhereClause did not work, you could replicate the above example via this code:
MyLangDBObject lookup = new MyLangDBObject();
for (Iterator i = lookup.searchAndRetrieveList().iterator(); i.hasNext(); ) {
MyLangDBObject instance = (MyLangDBObject) i.next();
if (! ("Indo-European".equals(instance.getField("FAMILY"))||
"dead".equals(instance.getField("TYPE"))
) ) {
continue;
}
// process instance
}
* You can create a view and point the database object at the view instead of at the underlying tables. This is probably the cleanest, fastest method for a complicated where clause with read only data, since no unneeded data is returned by the database. This works for JoinedDataObjects as well. If you are making updates, however, views may or may not work.
Posted by moore at February 28, 2005 10:46 AM