The Openbravo HQL Query Tool allows you to try a HQL query directly in the Openbravo web interface. This tool is targeted specifically for developers who need to easily try different HQL queries, it is not targeted for end users.
After installing this module the HQL Query Tool can be found in this menu: General Setup > Application. It allows you to query the whole database so this utility is only available to the System Administrator role.
The HQL Query Tool consists of two parts (see the image above):
- the query and query result on the left
- on the right, a convenience function to show all the properties of an entity and a link to the Openbravo developers guide for the selected entity. The listbox shows the tablename and then the entity name. Note this convenience function is not related to the query itself, it's only purpose is to allow a quick display of all properties of an Entity.
The HQL Query Tool will show the data in different formats:
- if a single business object (an Order, a Product) is queried then it will show the values of all the properties of the object, reference values (to other business objects) are shown with the id and the identifier of the other object.
- if the query returns multiple values per query result row (see the last queries in the examples below) then the reference value show the id, entityname and identifier of the referenced object.
- primitive values are shown by calling the toString() method on the object.
- NULL values are shown with the term NULL.
The values of reference properties are shown as hyperlinks. Clicking the hyperlink will show the entity using a simple template and the Openbravo webservice implementation. The details of an object itself can also be viewed by clicking on the value of the id property.
Some example queries:
- select sum(listPrice), avg(lineNetAmount) from OrderLine
- From Order
- select ol.salesOrder, ol.product From OrderLine ol where ol.product.id='1000006'
- select ol.product.id, sum(ol.listPrice), count(*) From OrderLine ol group by ol.product.id
- select ol.salesOrder.id, sum(ol.listPrice) From OrderLine ol group by ol.salesOrder.id order by ol.salesOrder.id