OFBiz Entity Query API makes building database queries easier than ever before!
Developers: Tired of writing primitive codes? Good news–your lives just got easier. There’s now a way for developers to use Entity Query methods for quick and simple access to the Entity engine. We’ve taken a few references from the existing code base and made them easier to use with Entity Query methods.
Introduction
As per improvement task OFBIZ-4053, Scott Gray (Apache OFBiz – PMC members and active Committers ) has designed new API for executing database queries in much simpler way. EntityQuery implements the builder pattern in order to provide a powerful, flexible and simple multistep process for the construction of a database query. Because of this new API, developers can build the database queries in much simpler way. Let’s review some quick examples of the queries to understand how it is easy to build database queries with this new EntityQuery API.
[For more information on EntityQuery API, please refer the documentation available for Class EntityQuery.]
Case 01: Fetch all records from single entity
Let suppose we want to write the simplest query of fetching all records from ‘product’ entity, using Entity Engine method findList() we can do it as:
List<GenericValue> productList = delegator.findList("Product", null, null, null, null, false);
In the above method, at first look, we don’t understand what these ‘null’ parameters are for and it becomes difficult to recall the syntax for all of them at once.
Lets see how EntityQuery has made the life simpler for a developer:
List<GenericValue> productList = EntityQuery.use(delegator)
.from("Product")
.queryList();
Here, we can easily see what type of records we are going to get by looking at the methods. Also, there is no more confusion about multiple ‘null’ parameters as in Entity Engine methods.
Case 02: Select particular columns from the entity and ordering the records based on any column
Let’s go one level up and try to build some more complex queries. Let’s suppose I want to select ‘productId’ and ‘internalName’ fields in product entity and order them by the value of internal name. This query can be coded using Entity Engine methods as:
Set<String> fieldsToSelect = UtilMisc.toSet("productId", "internalName");
List<String> orderBy = UtilMisc.toList("internalName");
List<GenericValue> productList = delegator.findList("Product", null, fieldsToSelect, orderBy, null, false);
Here we can see that we need to explore the things in an upward direction in the code to understand which columns are selected and which column will be responsible for ordering the list. This has been made very easy in EntityQuery API as:
List<GenericValue> productList = EntityQuery.use(delegator)
.select("productId", "internalName")
.from("Product")
.orderBy("internalName")
.queryList();
The code above delivers an exact understanding of the query and its action.
Case 03: Conditional record fetching
Now let’s try some conditional statements which are frequently used. Let suppose we want to fetch all records from ‘ShoppingList’ entity which have the fields ‘shoppingListTypeId’ as ‘SLT_AUTO_REODR’ and ‘isActive’ as ‘Y’. We also want to order the result on the basis of ‘lastOrderedDate’ value. As per the Entity Engine method, this query can be coded as follows:
List<EntityExpr> exprs = UtilMisc.toList(EntityCondition.makeCondition("shoppingListTypeId", EntityOperator.EQUALS, "SLT_AUTO_REODR"),
EntityCondition.makeCondition("isActive", EntityOperator.EQUALS, "Y"));
EntityCondition cond = EntityCondition.makeCondition(exprs, EntityOperator.AND);
List<String> order = UtilMisc.toList("-lastOrderedDate");
EntityListIterator eli = null;
eli = delegator.find("ShoppingList", cond, null, null, order, null);
In this method, we need to look backward in code to see what condition is used for accessing the records.
The same query can be written using EntityQuery API as:
eli = EntityQuery.use(delegator)
.from("ShoppingList")
.where("shoppingListTypeId", "SLT_AUTO_REODR", "isActive", "Y")
.orderBy("-lastOrderedDate")
.queryIterator();
Here we can directly write the conditions inside the “where” clause, hence it becomes very easy for the developer to understand the conditions for fetching the records. It is clearly visible that amount of code lines has been reduced. For further reference re the “where” method of EntityQuery, you can refer EntityQuery API documentation.
Case 04: Query with various find options
If we use EntityFindOptions in Entity Engine methods, the query seems to be too complex to understand and to recognize what options have been used. Let’s try to build a query for fetching the records of different products from the ‘Product’ entity which are varient and non-virtual. We want to paginate the result, so the number of records to be fetched should be restricted. Also we want to order records according to ‘thruDate’. This query can be build using Entity Engine methods as follows:
EntityFindOptions findOpts = new EntityFindOptions(true, EntityFindOptions.TYPE_SCROLL_INSENSITIVE, EntityFindOptions.CONCUR_READ_ONLY, false);
findOpts.setMaxRows(highIndex);
List<EntityExpr> exprs = UtilMisc.toList(EntityCondition.makeCondition("isVirtual", EntityOperator.EQUALS, "N"),
EntityCondition.makeCondition("isVarient", EntityOperator.EQUALS, "Y"));
EntityCondition condition = EntityCondition.makeCondition(exprs, EntityOperator.AND);
List<String> order = UtilMisc.toList("-thruDate");
EntityListIterator pli = delegator.find("Product", condition, null, null, orderByFields, findOpts);
In the above code, we need to check which options are passed into the method. Let’s see how it quickly we get this done using EntityQuery API.
EntityListIterator pli = EntityQuery.use(delegator)
.from("Product")
.where("isVirtual", "N", "isVarient", "Y")
.orderBy("-thruDate")
.cursorScrollInsensitive()
.maxRows(highIndex)
.queryIterator();
Conclusion
We can clearly see that EntityQuery helps us to write neat and clean code which is highly reusable and easily understandable. Hence, as per the above cases and quick tips for Entity Query codes we tried making the life of a developer easier. Feel free to connect with us for more information on technology updates in the e-commerce ERP arena.
Experience the power and flexibility of OFBiz the HotWax Media way today!
HotWax Media is the leading global innovator of Enterprise Commerce solutions powered by Apache OFBiz. Contact us today for a free, expert consultation.
