As promised, I have committed ERXQuery to my Wonder clone on GitHub, i.e. rparada/wonder. I just need help creating the pull request. :-)
It turns out that I created first a pull request for ERXExtensions.java unrelated to ERXQuery. Now when I tried to create the pull request for ERXQuery it just seems to show the previous pull request. So I’m not sure what is going on. I don’t have much experience with GitHub.
Anyways, the ERXQuery is an additive change to Wonder and consists of only three files: ERXQuery.java, ERXQueryAttributes and ERXQueryEOAttribute. It’s the last two commits in rparada/wonder.
I took the time to polish the API a bit more. I think the API came out nicer than what I had the first time around. Specially the single variadic select() method that allows you to specify EOAttributes, Strings, ERXKeys, or any Iterable containing these. Same for groupBy(). And the a similar technique with the orderBy() which can take EOSortOrderings or any Iterable (ERXSortOrderings, NSArray, List, Collection).
The documentation is more extensive now. I have included here a copy of the ERXQuery class from the javadoc tab in my Eclipse.
I have tested with ORACLE which is the easiest for me to test. It would be great to hear others using this with other databases (h2, derby, mssql, mysql, frontbase, etc.)
ERXQuery.java
Overview
This class has a fluent API that mimics a select statement:
NSArray<NSDictionary<String,Object>> records =
ERXQuery.create()
.select (keys)
.from (entity)
.where (qualifier)
.groupBy (groupings)
.having (havingQualifier)
.orderBy (sortings)
.fetch();
It allows you to use EOF/Wonder higher-level constructs (qualifiers, attributes, orderings, key paths, ERXKeys, etc.) to create a query that looks like this:
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
Specifying the Attributes to Fetch
The select() method is very flexible and powerful. It accepts a variable number of objects of different types that specify the attributes to fetch. These objects can be EOAttributes, ERXKeys, Strings. You may also specify NSArray or List objects containing any combination of these (EOAttributes, ERXKeys, Strings).
The ERXKeys and String objects correspond to keys and key paths to the attributes to fetch, i.e. "customer.name". The keys and key paths can also be relationships to objects, i.e. "customer" which translate into a fetch of foreign keys used to build object faults and return them in the results.
You may call the select() method multiple times to keep adding to the list of attributes to fetch.
Using Ad Hoc Attributes
It is very common to aggregate attributes in these queries. For this purpose, you may want to create what ERXQuery refers to as ad hoc attributes. These attributes have a definition but are not physically attached to the entity. You can use the ERXQueryAttributes class to easily create multiple ad hoc attributes. The definition of the attribute can reference relationships and attributes as shown below. If you just want to create a single ad hoc attribute you may use the ERXQueryEOAttribute class.
// Using a single query against the order entity to count the number of
// orders and line items that match an order qualifier.
ERXQueryAttributes attributes = ERXQueryAttributes.create(orderEntity)
.add("itemCount", "COUNT(DISTINCT lineItems.lineItemID)", "intNumber")
.add("orderCount", "COUNT(DISTINCT orderID)", "intNumber");
ERXQuery query =
ERXQuery.create()
.select (attributes)
.from (orderEntity)
.where (qualifier);
// Fetch into a dictionary
NSDictionary<String,Object> row = query.fetch().lastObject();
int orderCount = ((Number) row.objectForKey("orderCount")).intValue();
int itemCount = ((Number) row.objectForKey("itemCount")).intValue();
Fetching Results into a Custom Class
It is useful to fetch results into objects of a custom class. This allows you to have type checking on the getter methods and add methods for computed values on the data fetched. For the example above you could have fetched the results into a custom class as follows:
// Fetch into object instances of the a custom Result class
Result result = query.fetch(editingContext, Result.class).lastObject();
int orderCount = result.orderCount();
int itemCount = result.itemCount();
The Result custom class would have to be defined as shown below. The constructor may keep the mutable dictionary passed in to the constructor or make an immutable copy from it as shown below.
public static class Result {
NSDictionary<String,Object> data;
public Result(EOEditingContext ec, NSMutableDictionary<String,Object> row) {
data = "">
}
public int itemCount() {
return ((Number) data.objectForKey("itemCount")).intValue();
}
public int orderCount() {
return ((Number) data.objectForKey("orderCount")).intValue();
}
}
}
In general, fetching into a custom class can be done in several ways: // If your custom class has a constructor that takes an editing context and
// a mutable dictionary then it is very simple:
NSArray<Foo> objs = query.fetch(editingContext, Foo.class);
// Using java 8 or later you may use a lambda _expression_:
NSArray<Foo> objs = query.fetch(editingContext, (ec, row) -> new Foo(ec, row));
// You may also create an implementation of the RecordConstructor
// functional interface and pass it into the fetch method:
ERXQuery.RecordConstructor<Foo> recordConstructor =
new ERXQuery.RecordConstructor<Foo> {
@Override
public Foo constructRecord(EOEditingContext ec, NSMutableDictionary row) {
return new Foo(ec, row);
}
};
NSArray objs = query.fetch(editingContext, recordConstructor)
}
Augmenting Row Values
You can have entries from a dictionary added in to the rows fetched from the database. The mutable dictionary passed in to the record constructor will contain the data fetched along with the keys/values from this recordInitializationValues dictionary.
NSDictionary<String,Object> recordInitializationValues = new NSDictionary<>((Object)2017, "preferredYear");
NSArray<Foo> objs = query.fetch(editingContext, recordInitializationValues, Foo.class);
Foo aFoo = objs.lastObject();
int preferredYear = aFoo.preferredYear(); // i.e. 2017
Defining Ad Hoc Attributes in the Entity
An alternate way to define your ad hoc attributes is to define them in your entity and flagging them as non-class properties. Unlike ERXQueryEOAttribute objects, these attributes will be instances of EOAttribute and reside in your entity. They may be a bit distracting when looking at the entity if you have a lot but this method allows you to reuse all the existing attributes and relationships already defined in the entity and does not require code for creating the attributes.
One incovenience is that eogeneration templates do not generate ERXKeys for non-class properties. However, this problem could be overcome by enhancing the eogeneration templates to generate ERXKeys for derived non-class property attributes.
// Fetch last year's customer order totals exceeding $1000 in descending order
NSArray<OrderSummary> lastYearTopSales =
ERXQuery.create()
.select (Order.CUSTOMER) // customer to-one
.select (Order.SUM_TOTAL_AMOUNT) // non-class property defined as SUM(totalAmount)
.from (Order.ENTITY_NAME)
.where (lastYearQualifier)
.groupBy (Order.CUSTOMER)
.having (Order.SUM_TOTAL_AMOUNT.greaterThan(1000.00))
.orderBy (Order.SUM_TOTAL_AMOUNT.desc())
.fetch(editingContext, OrderSummary.class);
// Peek at top sale record
OrderSummary topSale = ERXArrayUtilities.firstObject(lastYearTopSales);
if (topSale != null) {
System.out.println("Customer " + topSale.customer().fullName()
+ " ordered " + moneyFormatter.format(topSale.sumTotalAmount()));
}
}
It would be nice to enhance the eogeneration templates to also create a custom class for fetching the results, i.e. WonderEntitySummary.java and _WonderEntitySummary.java with the getters for attributes/relationships in the entity including derived non-class properties. These templates would be used when the entity has a user info key with ERXQuery.enabled=yes.
Limitations
Ad hoc attributes created with ERXQueryAttributes or ERXQueryEOAttribute are not physically attached to an entity. When EOF generates SQL for a qualifier it calls sqlStringForSQLExpression(q,e) where q is an EOQualifier and e is an EOSQLExpression. Qualifiers then try to reach the attribute by following the qualifier's referenced keys starting with the entity of the EOSQLExpression, i.e. e.entity().
The current workaround used by ERXQuery is to temporarily add to the entity any ad hoc attributes referenced by the qualifiers. This typically happens with the havingQualifier which normally references the ad hoc attributes corresponding to aggregated attributes. For example, "sumTotalAmount" defined as "SUM(totalAmount)" could be used in a having qualifier:
// When grouping orders by customer and fetching sumTotalAmount we may want to have
// this having qualifier so that we only fetch the groups totaling more than 1000.
EOQualifier havingQualifier = ERXQ.greaterThan("sumTotalAmount", new BigDecimal(1000.0));
However, if you were to define your "sumTotalAmount" attribute in your entity as a derived non-class property with definition "SUM(totalAmount)" then ERXQuery doesn't have to add the attribute to the entity.
Defaults for Behavior Properties
- er.extensions.eof.ERXQuery.useBindVariables=false
- er.extensions.eof.ERXQuery.useEntityRestrictingQualifiers=true
- er.extensions.eof.ERXQuery.removesForeignKeysFromRowValues=true
- Author:
- Ricardo J. Parada