Grouping and subquery behavior must also be specified in the table structure. The following steps show the table creation process, with examples.
1. Create the master table. If your query has a subquery, then this master table should be the table nested at the innermost level.
QueryTableMaster master = new QueryTableMaster("schema.name",
"abbreviation"); ,→
2. If necessary, create the joined or detail tables at this level:
QueryTable tableJoined = master.innerJoin("schema.name", "abbreviation") .onColumns("COL_DETAIL", "COL_MASTER");
QueryTable tableDetail = master.joinDetail("schema.name", "abbreviation") .onColumns("COL_DETAIL", "COL_MASTER")
.onColumns("COL_DETAIL_2", "COL_MASTER_2");
QueryTable tableDetailCustomJoin = master.joinDetail("schema.name", "abbr") .customOn("A = B");
Note that the examples before show different usages of the onColumns() and customOn() methods. These methods can be used alone or together in any joined or detail table, to specify the joining details.
3. If relevant, specify that the previous tables are nested into a subquery. For this, we retrieve our master table and call to the asSubquery() method:
QueryTableMaster higherLevelTable =
master.asSubquery("SUBQUERY_ABBREVIATION");
4. If relevant, specify that the previous tables are grouped. For this, we have two different options in our master table (which should be the highest level table declared, that is, if we did not skip step 3, then we should use the table created there):
QueryTableMaster groupedTable = higherLevelTable .groupBy(attribute1, attribute2);
QueryTableMaster groupedByGranularity = higherLevelTable .groupByGranularity(attribute1, attribute2) .addGranularity("gran1", attribute1)
.addGranularity("gran2", attribute1, attribute2) .defaultGranularity("gran1");
Note that attribute1 and attribute2 are QueryAttributes (see below) that must be de- clared as attributes in the nested tables, if a subquery is present, or in our master table if it is not.
The groupBy() function is used for default grouping. However, special methods for specific grouping are provided, such as groupByGranularity(), which is used when we wish to allow queries with the group[granularity] parameter. The value of this parameter specifies which one of the groupings defined above will apply, while the defaultGranularity() method is useful for specifying which one of the granularities should be applied by default.
If we want to add more depth (i.e., nest the subquery in another level), just go back to step 2 and use the current top-level table as the master table.
5. We save the top-level table in the attribute masterTable. A.1.3 Query attributes
Query attributes are the core of the query builder. Each query attribute roughly represents a attribute in the response object. All query attributes are subclasses of QueryAttribute. They are implemented using the decorator design pattern: the subclasses of QueryAttributeBase store the core information about the attribute, and can be decorated with subclasses of QueryAttribute
Decorator. These subclasses modify or extend the attribute behavior with respect to its definition in the SELECT clause, filtering, sorting...
After creating an attribute, it has to be added to the query builder via either the addIdentifying Attribute() or addAttribute() functions. These two methods do the same thing, but add IdentifyingAttribute() tells the query builder that this attribute should always appear in the SELECT clause, independently of whether the user requested them. For this reason, it should be used with attributes which are part of the identifier.
Attributes are created by decorating a base class with zero or more decorators, which alter the default behaviour on different ways (for example, they can change the filter translation, wrap the attribute into a function...). The following is a list of the base attributes:
Column attributes: for data coming from a table column.
Literal attributes: for literal data, they are initialized with a string that has to be valid SQL, since it is written directly into the query and not validated.
Dynamic attributes: sometimes, an attribute needs to be calculated from other attributes, but this is more easily done on the Java code. In that case, a dynamic attribute is created. It is a dummy attribute that is created with information about the attributes that it depends on. When this attribute is requested, the query builder adds all the underlying attributes to the query, even if those are not requested, since they will be needed later to compute the dynamic attribute.
Multi-argument function: used for attributes that are completely based on two or more other attributes, combined with a function or an operator. It is useful to use different at- tributes as building blocks, reducing significantly the complexity of the final attribute and enabling reuse of the parts. This is used, for example, when an attribute is calculated as the sum of two other attributes.
Furthermore, we provide a fifth type of base attribute. This is one special attribute used to rank the rows in groups of a specified size, or in a given number of equally-sized groups. All the members of a group will receive the same rank, and this value can be used later in a GROUP BY clause.
For the decorators, here are some examples:
Function: one of the simplest decorators, it wraps the attribute SELECT expression into a given function. Other (literal) arguments can be added to the function.
Analytic function: similar to the previous one, but adds the possibility of specifying an analytic clause, and the desired ORDER BY and PARTITION BY.
Filter conversions: maybe the most used of all the decorators, different conversions can be provided. A conversion defines the generation of the where condition when the attribute appears on a filter. Numbers, strings, dates, booleans, etc. are provided by default, while a generic class is provided to be subclassed, thus enabling custom filter conversion.
Sort literals: a literal passed as an argument replaces the default expression for this attribute in the ORDER BY clause.
Subquery attributes: used to generate an attribute from another attribute that was se- lected in a nested subquery. The query builder uses these attributes to determine what should and should not be shown in the subquery.
Subselect attributes: specifies that an attribute on a detail table should be selected with a subquery in the SELECT clause, instead of joining the table in the FROM clause. Useful for performance in some cases.