Getting started with the Query Unit

From Wiki

• DB. One of the databases specified in the Data Model View. It represents the database on which you want the query to be executed.
• Mode. It specifies the operation mode of the unit. The possible values are:
  • Select (Static). It means that the query is a "Select" query and that it is directly provided to the unit through the Query Text or the Query File property.
  • Update. It means that the query is an "Update" query and that it is directly provided to the unit through the Query Text or the Query File property.
  • Select (Dynamic). It means that the query is a "Select" query and it is composed dynamically at runtime using the information coming from the Data Model. In this case the query is an HQL query, so the Language property is disabled.
• Language. It specifies the query language used to write the custom query. This property is enabled only when the Mode is set to "Select (Static)" or "Update".
• Query Text. The SQL/HQL query to execute.
• Query File. The WebContent file containing the SQL/HQL query to execute.
• Block Factor. A numerical value specifying how many results at a time are scrolled. If this property is unspecified or set to -1, all the available rows are returned. This property is enabled only when the Mode is set to "Select (Static)" or "Select (Dynamic)".
• Block Window. A numerical value specifying the maximum number of links available to jump to other result blocks. Assuming that N is the value of this property, the unit will show N-1 block links before the current block, and N-1 block links after the current block. If this property is unspecified or set to -1, all the block links are shown. This property is enabled only when the Mode is set to "Select (Static)" or "Select (Dynamic)".
• Max Results. The maximum number of rows to retrieve in the case of select. If unspecified, all rows are retrieved.
• Outputs Order. The order of the query output parameters specified for the current query unit. In order to work properly this order must be the same one of the select clause written in the custom query.

• Type. The data type of the query input parameter.
• Value. The default value in the case the input parameter value is not provided, it can be a single value or a pipe '|' separated list of values.
• Required. Indicates whether the query input is required or not.

• Type. The type of the query output parameter.

Based on the Mode property and on how the unit has been configured (in terms of Query Input elements), the Query Unit provides different Input Parameters:

• Fixed Input Parameters. The following Input Parameters are always exposed by the unit.
  • Block Factor. A numerical value specifying how many results at a time are scrolled. If this property is unspecified or set to -1, all the available rows are returned. This property is enabled only when the Mode is set to "Select (Static)" or "Select (Dynamic)".
  • Block Window. A numerical value specifying the maximum number of links available to jump to other result blocks. Assuming that N is the value of this property, the unit will show N-1 block links before the current block, and N-1 block links after the current block. If this property is unspecified or set to -1, all the block links are shown. This property is enabled only when the Mode is set to "Select (Static)" or "Select (Dynamic)".
  • Max Results. The maximum number of rows to retrieve in the case of select. If unspecified, all rows are retrieved.

• Select(Static) or Update. If the Query Unit has Query Inputs elements, it exposes one Input Parameter for each Query Input element. The Input Parameter has a fixed name which is QueryInput name.

• Select(Dynamic).
  • Display Attributes Identifiers. The qualified attribute identifiers to select retrieved from the Data Model.
  • Display Attributes Labels. The output labels identifying selected attributes retrieved from the Data Model.
  • Aggregate Operators. The aggregate operator of each selected attribute. If no aggregate operator is defined for one attribute the NULL value must be passed.
  • Condition Attributes Identifiers. The conditions qualified attribute identifiers.
  • Condition Attributes Predicates. The condition predicate of each attribute condition.
  • Condition Values. The condition value of each attribute condition.
  • Sort Criteria.The sort criteria of each selected attribute.
  • Group by. The grouping flag of each selected attribute (true or false).
  • Boolean Operator. The boolean operator to use in the case of multiple attribute conditions. The default value is "and".

Based on the Mode property and on how the unit has been configured (in terms of Query Output elements), the Query Unit provides different Output Parameters:

• Select(Static) or Select (Dynamic). If the Query Unit has Query Outputs elements, it exposes one Output Parameter for each Query Output element. The Output Parameter has a fixed name which is QueryOutput name.
• Update.
  • Update Result. The update result in terms of number of changed rows.

Working with the Select(Static) mode

Suppose you want to create an Index showing some information about the products plus the number of available images and the number of available combinations. Let's create the page that allows the user to see this list.

• 
  Add the "Query Unit Example" page to your Site View. In the Properties View check the Landmark property.
• Add a Query Unit to the page and name it "Query".
• In the Properties View configure the properties as shown in the image on the right.
  1. DB. Choose the only available database.
  2. Mode: Select(Static)
  3. Language: HQL
  4. Block Factor: 5
  5. Block Window: 2.
• Click on the Edit button next to the Query Text property. In the opening dialog write the HQL query. Note that you must know the name of the Hibernate name of the Data Model object you have to use in the query. You can retrieve this information looking at the Hibernate Mapping file of the interested entity. You can open this file right clicking on the entity in the Data Model and choosing the Open Generated Code -> Hibernate Mapping command. The code for our example is the following

select p.code, p.name, p.price , count(distinct b.OID) as imageCount, count(distinct c.OID) as combinationCount
from Product p left join p.product2BigImage b left join p.product2Combination c
group by p.code, p.name, p.price

• Add as many Query Output as the number of the columns retrieved by the query (name, code, price, imageCount, combinationCount).

You can generate the project and see the result. You can see an example in the following image.

Let's see how which are the changes needed to obtain the same result writing the query in SQL language. First of all, you have to change the Language property to SQL. Then change the Query Text using the following code

select p.code, p.name, p.price , count(distinct b.oid) as imageCount, count(distinct c.oid) as combinationCount
from product as p left join bigimage as b on (b.product_oid = p.oid) 
                  left join product_combination as pc on (pc.product_oid = p.oid)
                  left join combination as c on (pc.combination_oid = c.oid)
group by p.code, p.name, p.price

N.B. You can use the SQL language to write queries that involve tables which are not mapped over an entity or relationship in the Data Model of the project. The only constraint is that the tables must belong to the selected database.

Working with the Update mode

Suppose you want to create an index in which the user can select the products to set as offer. The modeled page is something like the following image

• 
  Add the "Offer Selection" page to your Site View. In the Properties View check the Landmark property.
• Add a Query Unit to the page and name it "Set as Offer".
• In the Properties View configure the properties as shown in the image on the right.
  1. DB. Choose the only available database.
  2. Mode: Update
  3. Language: HQL
• Add as many Query Input as the number of the where clause condition parameters. In this case there's only one parameter named "productOidList"
• Click on the Edit button next to the Query Text property. In the opening dialog write the HQL query. Note that you must know the name of the Hibernate name of the Data Model object you have to use in the query. You can retrieve this information looking at the Hibernate Mapping file of the interested entity. You can open this file right clicking on the entity in the Data Model and choosing the Open Generated Code -> Hibernate Mapping command. The code for our example is the following

update Product p set p.highlighted = true 
where p.OID in (:productOidList)

Working with the Select (Dynamic) mode

Suppose you want to create a Web application that allows the user to create custom reports. This means that the user can choose the Modules to add to the report, the Fields to see for each Module, the filters to apply, and so on. These information creates a custom query that, once executed, represents the custom report. Using the Data Model Unit and the Query Unit this is possible. You can download the Sample Project to test this feature.

First of all you have to construct a Data Model that it is able to store all the information necessary to create the custom report. A part of the Data Model represents the information that the Web application manages (in the example Category and Product). A group of entities stores the information retrieved with the Data Model Unit in order to show them to the user. Finally another group of entities stores the structure of the report before the query execution. You can see the latter group in the following image.

The main entity is the "Report V" entity in which are stored all the Modules belonging to the Report. Connected to it there's the "Field V" which stores all the attributes to visualize in the report for each selected Module. The "Filter Column V" entity stores all the filter that has to be applied in the query in order to get the report. The Web application must be modeled in order to fill these entities with the information necessary to build a report. Once this is done, you can run the report using the Query Unit.

• Add the "Report" Selector Unit. Choose "Report V" for the Entity property.
• Add the "Diplay Columns" Selector Unit. Choose "Field V" for the Entity property. Right click on the unit and choose the Add Relationship Role condition command. Write "report" for the condition name and then choose the relationship connecting the "Report V" with the "Field V" entity.
• Connect the "Report" unit with the "Display Columns" unit with a transport link.
• Add the "Filters" Selector Unit. Choose "Filter Column V" for the Entity property. Right click on the unit and choose the Add Relationship Role condition command. Write "report" for the condition name and then choose the relationship connecting the "Report V" with the "Filter Column V" entity.
• Connect the "Report" unit with the "Filters" unit with a transport link.
• Add the "Filters" MultiEntry Unit. Add three fields to the unit "Name", "Predicate" and "Value". Mark all these fields as "preloaded".
• Connect the "Filters" MultiEntry Unit and the "Filters" Selector Unit with a transport link. Double click on the link and use the Guess Coupling button to make the coupling.
• Add the "Report Result" Query Unit. In the Properties View choose:
  • DB. The only available database.
  • Mode. Select(Dynamic)
  • Language. HQL
  • Block Factor. 10
  • Block Window. 5
• Connect the "Display Columns" Selector Unit and the "Filters" Query Unit with a transport link. Double click on the link and couple:
  • the "Aggregate" attribute with the "Aggregate Operators" input parameter
  • the "ID" attribute with the "Display Attribute Ids" input parameter
  • the "Name" attribute with the "Display Attribute Labels" input parameter
  • the "Group by" attribute with the "Group by" input parameter
  • the "Order By" attribute with the "Sort Criteria" input parameter
• Connect the "Filters" Selector Unit and the "Filters" Query Unit with a transport link. Double click on the link and couple:
  • the "Id" attribute with the "Condition Attribute Ids" input parameter
  • the "Predicate" attribute with the "Condition Attribute Predicates" input parameter
  • the "Value" attribute with the "Condition Values" input parameter
• Connect the "Filters" MutiEntry Unit and the "Filters" Query Unit with a transport link. Double click on the link and couple the "Value" field with the "Condition Values" input parameter.