Getting started with the Query Unit

by Michela Frigerio
5,468 views Published on Oct 24, 2011
Applies to: 7.2 or higher
Table of contents

Introduction

The Query Component is available starting from the WebRatio 5.1 version. It's a Utility Component that allows to execute a custom edited query using the HQL (Hibernate Query Language) or SQL (Structured Query Language). Moreover it can dynamically compose an HQL query depending on the incoming Domain Model identifiers. In the latter case this component must be used in conjunction with another Utility Component, the Domain Model Component.

This article shows all the available working modes for the Query Component showing an example for each mode. Use the attached project to review each chapter of this article.

Working with the Select (Static) Mode

Suppose you have an application and you want to show detailed information about companies including the number of employees and the number of offices.

The starting point of this example is the Page that will show the list of Companies. Add it to the Site View.

To perform the detailed query and show the results into the page let's add a Query Component inside the new page and call it Companies. In situations like this (Select (Static) Mode) the Query Component when placed into the page acts like a View Component.

Now let's configure its properties in order to satisfy the requirements.

Choose the DB source of your data. In this example the Entities involved are all mapped on a persistent database, so let's set the "Persistent DB" property choosing the database used by the application. If your example uses Volatile Entities you can set the "Volatile DB Scope" property.

The "Mode" property must be set to "Select (Static)".

The Language could be either HQL or SQL. We will see first the HQL version of the query.

If your set of data is big enough you can also set the "Block Factor" and "Block Window" properties to show for instance maximum 5 rows per page with a window of 2 pages.

The query can be specified directly inside the "Query Text" property that offers a text editor using the "Edit" button.

When using HQL you have to know the Hibernate names for Domain Model objects. To quickly know it right-click on an Entity and choose the Open Generated Code > Hibernate Mapping command. This will open the Hibernate Mapping file available only after the first generation of the project. Be careful to check the Hibernate Mapping names before proceeding with the example.

The query to be executed for our example is:

select c.name, c.country, c.vat, count(distinct e.oid) as employees, count(distinct o.oid) as offices
from Company c left join c.companyToEmployee e left join c.companyToOffice o
group by c.name, c.country, c.vat

To see the output of the query let’s add as many Query Output as columns retrieved.

A Query Output is a sub element of the Query Component that can be added right-clicking on the component and using the command Add > Query Output.

A Query Output requires a name and a type to display correctly the data retrieved.

Now if you Generate & Run the project you can see the result of the query:

To reproduce the same query but using SQL change the Language property to SQL and change the query as follows:

select c.name, c.country, c.vat, count(distinct e.oid) as employees, count(distinct o.oid) as offices
from company as c
left join employee as e on e.company_oid = c.oid
left join office as o on o.company_oid = c.oid
group by c.name, c.country, c.vat

When working in SQL mode remember to check the mapping names of Domain Model objects that can be seen in the Mapping tab of the Properties View of each Domain Model object.

Working with the Update Mode

Suppose you have an application and you want to set some offices as stores at the same time. One possibility could be using the Query Component in Update Mode.

The starting point for this example is a Page with a Checkable List of Offices that are not stores.

When working in Update Mode the Query Component behaves like an operation; so to perform the update query let’s add an Action Definition containing the Query Component; add also the OK and KO Ports and all the required flows.

Configure the Query Component setting the Mode property to Update.

The update query needs to know which Offices must be updated so a Query Input is needed.

Query Input is a sub element of the Query Component used to accept a parameter required by a query. It can be added right-clicking on the component and using the command Add > Query Input.

Let’s add one and call it officeKeys of type Integer as it carries the keys of the selected objects.

The update query, considering that the Language used is HQL is the following:

update Office as o set o.isStore = true
where o.oid in (:officeKeys)

Where ":officeKeys" is the reference to the incoming Query Input parameter.

Remember that for HQL you need to know the Hybernate Mapping of Domain Model objects while for SQL you need to change the Language property and know the Database Mapping of objects (available in the Mapping tab of the Properties View).

Then use the Input Port wizard on the Input Port to add the parameter and automatically bind it on the OK Flow.

This is the resulting IFML Model with the "Checked Oid" property of the Checkable List passed as input to the Set as Store Action.

Working with the Select (Dynamic) Mode

Suppose that you want to allow the user to create custom reports. This means that the user can build queries through a Form choosing tables, columns and predefined values.

This is possible combining the Domain Model Component and the Query Component.

The starting point for this example is to add a new Page into the Site View. This page will show a set of fields that the user will set to create the custom query.

Let’s add a Form Component called Customize Report, with two fields of different type: one Selection Field called From, and one Multi Selection Field called Select. The two fields are both mandatory and represents the query structure.

The "From" Selection Field must be preloaded with the list of all the available entities using the Domain Model Component. Add as many sub element of type Entity to the Domain Model Component as entities you want to query (in this example Company and Employee with no restrictions on attributes). Bind the Entity Labels and IDs respectively with the From label and output slots.

The selection of an entity using the "From" Selection Field fires an event that loads all of its attributes into the "Select" Multi Selection Field. Another Domain Model Component can be used for this purpose. It receives from a Normal Navigation Flow starting from the Form Component the input entity id and gives back on a Data Flow the list of Attributes; the Attribute Label will be shown while the Attribute Ids will be used as output. In this way all of the Attributes of the given Entity will be shown, if you want to refine the set of attributes you can add the same sub elements of type entity to the Domain Model Component and choose the items you want to see.

To let the event fire automatically on the selection of the Entity, let’s add an On Change Ajax event on the "From" Selection Field.

To perform the query let’s add a Query Component to the page and call it Dynamic Query. Set its "Mode" property to "Select (Dynamic)".

Add a Normal Navigation Flow between the Form and the Query called Compute. Bind on this flow the "Select" Multi Selection Field with the Display Attributes Ids input parameter.

To show the results of the Query Component in Select (Dynamic) Mode a layout change is required. The Component Layout should be changed to Dynamic, using the predefined style available in the WRDefault Style Project.

Now if you Generate & Run the project you can see the custom report page: