Executing SQL Queries
SQL queries are useful when working with large volumes of data. A major advantage of using these queries is that they allow retrieving only the information needed, instead of loading the entire model object.
If the SearchCriteria object is used to search for data, the complete objects for that entity are retrieved, even if only, for example, the identifiers of the instance are needed. This means that more information than necessary is being loaded.
Instead, with the SQL queries available in the Deyel SDK, only the specific data required can be retrieved, making processing faster and more efficient.
Another major advantage of using SQL queries with the Deyel SDK is the ability to perform JOIN operations between entities, a functionality that is not available in SearchCriteria.
Searches for SQL Queries
Searches on SQL queries can be performed using the following objects.
QueryCriteria: object on which the different parts of the SQL show are added. The fields to retrieve from an entity instance can be specified with this object, whether to work or not with iteratives, as well as the search order and grouping, the limit conditions, and other operations detailed in the examples.
Form Service - executeQuery() method: service class of the form on which the search is carried out. Contains the executeQuery(formInstance, queryCriteria) operation, which takes as parameters the model object of the entity being worked with and the QueryCriteria object. Returns a QueryResult object as a result.
Form Model - getter ... QueryName(): model class of the form on which you want to execute the SQL query. It contains getters for each of the modeled fields, and these methods have the particularity of ending with QueryName. They should be used when adding the different parts of the query to be generated.
QueryResult: object that contains the results of the SQL query execution. They are grouped by column (entity field), which means that to interact with the QueryResult object, the field to retrieve should be indicated to get a list that represents each of the rows retrieved after the SQL query.
Methods
Methods for working with the various objects are described below.
Methods to execute with the QueryCriteria object
Method |
Description |
Parameters |
|---|---|---|
createSelect(pList) |
Creates a “SELECT” statement and adds the values in the pList parameter. |
Java.util.List type pList: : contains each of the fields to be retrieved from the form, excluding the iterative container fields |
createSelectWithIterative(pList, pMap) |
Creates a “SELECT” statement and adds the values in the pList and pMap parameter. |
Java.util.List type pList: contains each of the fields to be retrieved from the form, excluding the iterative container fields
Java.util.Map type pMap: contains, as a key, the iterative identifier. The iterative fields are added as a key value of the map within a java.util.List object |
addConditionLimits(pPageNumber,pPerPage) |
Adds limit conditions to the SQL statement along with the page to retrieve. |
pPageNumber: page number to retrieve
pPerPage: number of records to retrieve on the page. By default, it is 100, and at most, 10000 |
addGroupBy(pColumn) |
Adds the GROUP BY clause to the SQL statement. |
String pColumn: represents the identifier of the form field by which grouping is to be performed |
addGroupBy(pIterativeName, pColumn) |
Adds the GROUP BY clause to the SQL statement. |
String pIterativeName: indicates the identifier of the iterative to which the field indicated in pColumn belongs
String pColumn: represents the identifier of the form field by which grouping is to be performed |
addOrderCriteria(pColumn,pOrderDesc) |
Adds the ORDER BY clause to the SQL statement. |
String pColumn: represents the identifier of the form field by which sorting is to be performed
Boolean pOrderDesc: with value "True" indicates descending order, "False", ascending |
addOrderCriteria(pIterativeName,pColumn,pOrderDesc) |
Adds the ORDER BY clause to the SQL statement considering iterative fields. |
String pIterativeName: indicates the identifier of the iterative to which the field indicated in pColumn belongs
String pColumn: represents the identifier of the form field by which sorting is to be performed
Boolean pOrderDesc: with value "True" indicates descending order, "False", ascending |
addConditionBetween(pColumn,pValueFrom, pValueTo) |
Adds the BETWEEN condition to the statement. The value searched will be between the values indicated in the pValueFrom and pValueTo parameters. |
String pColumn: represents the identifier of the form field
Object Type pValueFrom: represents the initial value from which the match with the pColumn value is sought
Object Type pValueTo: represents the final value from which the match with the pColumn value is sought |
addConditionBetween(pIterativeName,pColumn,pValueFrom, pValueTo) |
Adds the BETWEEN condition to the statement. The value searched will be between the values indicated in the pValueFrom and pValueTo parameters. |
String pIterativeName: indicates the identifier of the iterative to which the field indicated in pColumn belongs
String pColumn: represents the identifier of the form field
Object Type pValueFrom: represents the initial value from which the match with the pColumn value is sought
Object Type pValueTo: represents the final value from which the match with the pColumn value is sought |
addConditionGreater(pColumn,pValue, useGreaterOrEqual) |
Adds the > or >= condition to the SQL statement. |
String pColumn: represents the identifier of the form field
Object Type pValue: contains the value being compared for the field contained in pColumn
Boolean useGreaterOrEqual: indicates whether or not the greater than or equal operator is used. With value "True", >=, "False", > is used |
addConditionGreater(pIterativeName, pColumn, pValue, useGreaterOrEqual) |
Adds the > or >= condition to the SQL statement. |
String pIterativeName: indicates the identifier of the iterative to which the field indicated in pColumn belongs
String pColumn: represents the identifier of the form field
Object Type pValue: contains the value being compared for the field contained in pColumn
Boolean useGreaterOrEqual: indicates whether or not the greater than or equal operator is used. With value "True", >=, "False", > is used |
addConditionLike(pColumn, pValue) |
Adds the LIKE condition to the SQL statement. |
String pColumn: represents the identifier of the form field
String pValue: contains the value for the LIKE condition |
addConditionLike(pIterativeName, pColumn, pValue) |
Adds the LIKE condition to the SQL statement. |
String pIterativeName: indicates the identifier of the iterative to which the field indicated in pColumn belongs
String pColumn: represents the identifier of the form field
String pValue: contains the value for the LIKE condition |
addConditionLower(pColumn, pValue, useLowerOrEqual) |
Adds the < or <= condition to the SQL statement. |
String pColumn: represents the identifier of the form field
Object Type pValue: contains the value being compared for the field contained in pColumn
Boolean useLowerOrEqual: indicates whether or not the greater than or equal operator is used. With value "True", <=, "False", < is used |
addConditionLower(pIterativeName, pColumn, pValue, useLowerOrEqual) |
Adds the < or <= condition to the SQL statement. |
String pIterativeName: indicates the identifier of the iterative to which the field indicated in pColumn belongs
String pColumn: represents the identifier of the form field
Object Type pValue: contains the value being compared for the field contained in pColumn
Boolean useLowerOrEqual: indicates whether or not the greater than or equal operator is used. With value "True", <=, "False", < is used |
addConditionNotEquals(pColumn, pValue) |
Adds the != condition to the SQL statement. |
String pColumn: represents the identifier of the form field
Object Type pValue: contains the value being compared for the field contained in pColumn |
addConditionNotEquals(pIterativeName, pColumn, pValue) |
Adds the != condition to the SQL statement. |
String pIterativeName: indicates the identifier of the iterative to which the field indicated in pColumn belongs
String pColumn: represents the identifier of the form field
Object Type pValue: contains the value being compared for the field contained in pColumn |
addConditionEquals(pColumn, pValue) |
Adds the = condition to the SQL statement. |
String pColumn: represents the identifier of the form field
Object Type pValue: contains the value being compared for the field contained in pColumn |
addConditionEquals(pIterativeName, pColumn, pValue) |
Adds the = condition to the SQL statement. |
String pIterativeName: indicates the identifier of the iterative to which the field indicated in pColumn belongs
String pColumn: represents the identifier of the form field
Object Type pValue: contains the value being compared for the field contained in pColumn |
addConditionNotNull(pColumn) |
Adds the IS NOT NULL condition to the SQL statement. |
String pColumn: represents the identifier of the form field that is required to be non-null. |
addConditionNotNull(pIterativeName,pColumn) |
Adds the IS NOT NULL condition to the SQL statement. |
String pIterativeName: indicates the identifier of the iterative to which the field indicated in pColumn belongs
String pColumn: represents the identifier of the form field that is required to be non-null. |
addConditionNullable(pColumn) |
Adds the IS NULL condition to the SQL statement. |
String pColumn: represents the identifier of the form field that is required to be null. |
addConditionNullable(pIterativeName,pColumn) |
Adds the IS NULL condition to the SQL statement. |
String pIterativeName: indicates the identifier of the iterative to which the field indicated in pColumn belongs
String pColumn: represents the identifier of the form field that is required to be null. |
addConditionNOTIN( pColumn, pValues) |
Adds the NOT IN condition to the SQL statement. |
String pColumn: represents the identifier of the form field
List pValues: This parameter is a value list (List) that will be used in the NOT IN clause of the SQL statement. Each value in this list represents a value that should be excluded from the results. |
addConditionNOTIN(pIterativeName, pColumn, pValues) |
Adds the NOT IN condition to the SQL statement. |
String pIterativeName: indicates the identifier of the iterative to which the field indicated in pColumn belongs
String pColumn: represents the identifier of the form field
List pValues: This parameter is a value list (List) that will be used in the NOT IN clause of the SQL statement. Each value in this list represents a value that should be excluded from the results. |
addConditionIN(pColumn, pValues) |
Adds the IN condition to the SQL statement.
|
String pColumn: represents the identifier of the form field
List pValues: This parameter is a value list (List) that will be used in the IN clause of the SQL statement. Each value in this list represents a value that should be included in the results. |
addConditionIN(pIterativeName, pColumn, pValues) |
Adds the IN condition to the SQL statement. |
String pIterativeName: indicates the identifier of the iterative to which the field indicated in pColumn belongs
String pColumn: represents the identifier of the form field
List pValues: This parameter is a value list (List) that will be used in the IN clause of the SQL statement. Each value in this list represents a value that should be included in the results. |
addJOIN(pColumn, pObjectRelation, pKeyObjectRelation) |
Adds the INNER JOIN clause to the SQL statement. A JOIN is performed where the pColumn field is equal to the pKeyObjectRelation field of the pObjectRelation object. |
String pColumn: represents the identifier of the form field with which the JOIN associated with the field represented by the pKeyObjectRelation field of the pObjectRelation object is sought
Object type pObjectRelation: represents the model object on which an INNER JOIN is executed
String pKeyObjectRelation: represents the form field identifier of the pObjectRelation model object |
addSum(pColumn) |
Adds the SUM function to the SQL statement. |
String pColumn: represents the identifier of the form field on which the SUM function is performed |
addSum(pIterativeName, pColumn) |
Adds the SUM function to the SQL statement. |
String pIterativeName: indicates the identifier of the iterative to which the field indicated in pColumn belongs
String pColumn: represents the identifier of the form field on which the SUM function is performed |
addCount(pColumn) |
Adds the COUNT function to the SQL statement. |
String pColumn: represents the identifier of the form field on which the COUNT function is performed |
addCount(pIterativeName, pColumn) |
Adds the COUNT function to the SQL statement. |
String pIterativeName: indicates the identifier of the iterative to which the field indicated in pColumn belongs
String pColumn: represents the identifier of the form field on which the COUNT function is performed |
addAVG(pColumn) |
Adds the AVG function to the SQL statement. |
String pColumn: represents the identifier of the form field on which the AVG function is performed |
addAVG(pIterativeName, pColumn) |
Adds the AVG function to the SQL statement. |
String pIterativeName: indicates the identifier of the iterative to which the field indicated in pColumn belongs
String pColumn: represents the identifier of the form field on which the AVG function is performed |
Methods to execute with the QueryCriteria object
Method |
Description |
Parameters |
|---|---|---|
getQueryResult(pColumn): List |
Returns a list containing the values corresponding to the field specified by pColumn. |
String pColumn: represents the identifier of the form field from which the retrieved values are to be obtained
|
getCountResult(pColumn):List |
Returns a list containing the results of the COUNT functions for the field specified by pColumn. |
String pColumn: represents the identifier of the form field for which the count was performed.
|
getSumResult(pColumn): List |
Returns a list containing the results of the SUM functions for the field specified by pColumn. |
String pColumn: represents the identifier of the form field for which the sum was performed.
|
getAVGResult(pColumn): List |
Returns a list with the average (AVG) corresponding to the field indicated in pColumn. |
String pColumn: represents the identifier of the form field from which the retrieved rows are to be obtained |
getExistsMoreResults(): boolean |
Returns a boolean value indicating whether there are more results that meet the conditions of the executed SQL statement and that can be processed. |
Method to execute with the Form Service object
Method |
Description |
Parameters |
|---|---|---|
executeQuery(formInstance, queryCriteria): QueryResult |
Executes an SQL statement based on the specified criteria and returns a QueryResult object with the corresponding results. |
formInstance: object of the entity model being worked with and on which the SQL statement is executed.
queryCriteria: QueryCriteria object that contains the SQL statement settings, configured through the methods available in that class. |
Execution Examples
1.Search
This example retrieves a list of account identifiers with active state, where the active state corresponds to code "1". It is defined that there are 15 records per reading page, and the number of pages to retrieve is specified. The results are ordered by company name in ascending order.
QueryCriteria xQueryCriteria = new QueryCriteria();
//Creating model object Account xAccount = new Account();
//Creating the service for the entity AccountService xAccountService = new AccountService(getApiClient());
//Selecting the identifier as the column to retrieve List<String> xColumns = new ArrayList<>(); xColumns.add(Account.getIdAccountQueryName());
//Configuring the query to select the specified columns. xQueryCriteria.createSelect(xColumns);
//Add conditions to the query xQueryCriteria.addConditionEquals(Account.getCdStatusQueryName(), "1"); xQueryCriteria.addConditionLimits(15, 1); xQueryCriteria.addOrderCriteria(Account.getDsCompanyQueryName(), false);
//Executing the query and obtaining the results QueryResult xQueryResult = xAccountService.executeQuery(xAccount, xQueryCriteria); List instancesResult = xQueryResult.getQueryResult(Account.getIdAccountQueryName());
|
2.Creating the "SELECT" statement
To create a query, two methods can be used to incorporate the corresponding SELECT statement into the QueryCriteria object, along with the fields to be retrieved from the entity.
First Method
createSelect(List<String> xColumns)
|
This method incorporates the “SELECT” statement to the QueryCriteria object and adds each of the entity's fields that have been previously added to the pColumns list. Each field added to the pColumns list must be a non-iterative field.
List<String> xColumns = new ArrayList<>(); xColumns.add(Account.getIdAccountQueryName());
//Creating the SELECT statement with the specified columns xQueryCriteria.createSelect(xColumns);
|
Second Method
|
This method allows adding fields that belong to an iterative container of the entity to the QueryCriteria object. Fields that do not belong to iteratives should be included in the pFilters list, while fields that belong to iteratives should be added to a separate list. This list must then be added to a variable of type “HashMap”, using the identifier of the iterative to which the fields belong as the key.
To work with the iterative fields and add them to the corresponding variable, it is necessary to use the iterative class, which is included within the form model object.
List<String> xColumns = new ArrayList<>(); xColumns.add(Account.getIdAccountQueryName());
//Selecting the dsEmail field from the eMailLine iterative container Map<String,List<String>> xMap = new HashMap<>(); List<String> xIterativeColumns = new ArrayList<>(); xIterativeColumns.add(Account.EMailLine.getDsEmailQueryName()); xMap.put(Account.getEMailLineQueryName(), xIterativeColumns);
//Creating the select xQueryCriteria.createSelectWithIterative(xColumns, xMap);
|
3.Limits of results
When working with SQL queries in the Deyel SDK, it is important to note that there is a limit to the number of results returned. This limit can be adjusted by the following method:
|
In this method, the first parameter, pPageNumber, indicates the number of the page to be retrieved, while the second parameter, pPerPage, specifies the maximum number of records per page.
By default, Deyel retrieves up to 100 records per page, and the maximum limit of records to retrieve per page is 10,000. If a higher value is set, the SQL query execution will fail.
4.Search conditions
Various search conditions can be added to the QueryCriteria object to perform SQL queries.
This example creates a QueryCriteria object and defines different search conditions using the "equal", "different", "greater", "greater or equal", "less", "less equal", "between", "like", "is null" and "is not null", “ in” and “not in” with the corresponding addCondition method.
For each type of condition, two methods are available:
•Method for fields belonging to iteratives: Used to add conditions to fields that are part of an iterative container.
•Method for fields not belonging to iteratives: Used to add conditions to fields that are not part of an iterative container.
Operator "Between"
Available Methods:
|
|
Example for a non-iterative field:
// Adds a condition so that the qtAnualRev field of the Account form is between the values 500,000 // and 800,000 xQueryCriteria.addConditionBetween(Account.getQtAnualRevQueryName(), 500000, 800000);
|
Example for an iterative field:
// Adds a condition so that the qtAmount field of the Product iteration in the Opportunity form is between the values 5,000 and 10,000 xQueryCriteria.addConditionBetween(Opportunity.getProductQueryName(), Opportunity.Product.getQtAmountQueryName(), 5000, 10000);
|
Operator "Greater Than", "Greater Than or Equal To"
The distinction between the use of the operator "greater than or equal to" and the operator "greater than" is made through the last parameter of the corresponding method.
Possible values for this parameter are:
•True: The operator used is "greater than or equal to" (>=).
•False The operator used is "greater than" (>=).
Available Methods:
|
|
Examples for a non-iterative field:
// Adds a condition so that the qtAnualRev field of the Account form is greater than 500,000 xQueryCriteria.addConditionGreater(Account.getQtAnualRevQueryName(), 500000, false);
|
// Adds a condition so that the qtAnualRev field of the Account form is greater than or equal to 500,000 xQueryCriteria.addConditionGreater(Account.getQtAnualRevQueryName(), 500000, true);
|
Examples for an iterative field:
// Adds a condition so that the qtAmount field of the Product iteration in the Opportunity form is // greater than 5,000 addConditionGreater(Opportunity.getProductQueryName(), Opportunity.Product.getQtAmountQueryName(), 5000, false);
|
// Adds a condition so that the qtAmount field of the Product iteration in the Opportunity form is greater than or equal to 5,000 xQueryCriteria.addConditionGreater(Opportunity.getProductQueryName(), Opportunity.Product.getQtAmountQueryName(), 5000, True);
|
Operator “Less Than" or “Less Than or Equal To”
The distinction between the use of the operator "less" and the operator "less than or equal to" is made through the last parameter of the corresponding method. Possible values for this parameter are:
•True: The operator used is "less than or equal to" (<=).
•False The operator used is "less than" (<).
Available Methods:
|
|
Examples for a non-iterative field:
// Adds a condition so that the qtAnualRev field of the Account form is less than 500,000 xQueryCriteria.addConditionLower(Account.getQtAnualRevQueryName(), 500000, false);
|
// Adds a condition so that the qtAnualRev field of the Account form is less than or equal to 500,000 xQueryCriteria.addConditionLower(Account.getQtAnualRevQueryName(), 500000, true);
|
Examples for an iterative field:
// Adds a condition so that the qtAmount field of the Product iteration in the Opportunity form is // less than 5,000 xQueryCriteria.addConditionLower(Opportunity.getProductQueryName(), Opportunity.Product.getQtAmountQueryName(), 5000, false);
|
// Adds a condition so that the qtAmount field of the Product iteration in the Opportunity form is less than or equal to 5,000 xQueryCriteria.addConditionLower(Opportunity.getProductQueryName(), Opportunity.Product.getQtAmountQueryName(), 5000, true);
|
Operator “Like”
Available Methods:
|
|
To use the “Like” condition in a SQL query, it is necessary to send the entire search pattern as a String type. This includes the “%” characters that are used as wildcards in the pattern.
Example for a non-iterative field:
// Non-iterative field xQueryCriteria.addConditionLike(Account.getDsCompanyQueryName(),"%ACME%");
|
Example for an iterative field:
// Adds a condition for the dsEmail field of the iterative eMailLine of the Account form to meet // with the pattern '%@gmail.com%' xQueryCriteria.addConditionLike(Account.getEMailLineQueryName(), Account.EMailLine.getDsEmailQueryName(), "%@gmail.com%");
|
Operator “Different”
Available Methods:
|
|
Example for a non-iterative field:
// Non-iterative field xQueryCriteria.addConditionNotEquals(Account.getDsCompanyQueryName(),"ACME");
|
Example for an iterative field:
// Iterative field // Adds a condition so that the dsEmail field of the iterative eMailLine of the Account form is not // equal to "example@gmail.com" xQueryCriteria.addConditionNotEquals(Account.getEMailLineQueryName(), Account.EMailLine.getDsEmailQueryName(), "example@gmail.com"); |
Operator "Equal"
Available Methods:
addConditionEquals(String pColumnName, Object pValue)
|
|
Example for a non-iterative field:
// Non-iterative field xQueryCriteria.addConditionNotEquals(Account.getDsCompanyQueryName(),"ACME");
|
Example for an iterative field:
// Adds a condition so that the dsEmail field of the iterative eMailLine of the Account form is equal to // "example@gmail.com" xQueryCriteria.addConditionEquals(Account.getEMailLineQueryName(), Account.EMailLine.getDsEmailQueryName(), "example@gmail.com");
|
Operator "Not Null"
Available Methods:
|
|
Example for a non-iterative field:
// Non-iterative field xQueryCriteria.addConditionNotNull(Account.getCdStatusQueryName());
|
Example for an iterative field:
// Iterative field // null xQueryCriteria.addConditionNotNull(Account.getEMailLineQueryName(), Account.EMailLine.getDsEmailQueryName(),;
|
Operator "Is Null"
Available Methods:
|
|
Example for a non-iterative field:
// Non-iterative field xQueryCriteria.addConditionNullable(Account.getCdStatusQueryName());
|
Example for an iterative field:
// Iterative field xQueryCriteria.addConditionNullable(Account.getEMailLineQueryName(), Account.EMailLine.getDsEmailQueryName(),;
|
Operator "In"
Available Methods:
|
|
Example for a non-iterative field:
// Non-iterative field // in xElements (4 and 6) List xElements = new ArrayList(); xElements.add(4); xElements.add(6); xQueryCriteria.addConditionIN(Account.getDsIndustryQueryName(), xElements);
|
Example for an iterative field:
// Iterative field List xCountries = new ArrayList(); xCountries.add("Argentina"); // Creates a new instance of QueryCriteria to add the condition xQueryCriteria = new QueryCriteria(); // Adds the condition that filters the dsCountry field of the LsAddress iterative in the Account form // is in the set of values included in xCountries. In this case, Argentina. xQueryCriteria.addConditionIN(Account.getLsAddressQueryName(), Account.LsAddress.getDsCountryQueryName(), xCountries);
|
Operator "Not In"
Available Methods:
|
|
Example for a non-iterative field:
// Non-iterative field // in xElements (4 and 6) List xElements = new ArrayList(); xElements.add(4); xElements.add(6); xQueryCriteria.addConditionNOTIN(Account.getDsIndustryQueryName(), xElements);
|
Example for an iterative field:
// Iterative field List xCountries = new ArrayList(); xCountries.add("Argentina"); // Creates a new instance of QueryCriteria to add the condition xQueryCriteria = new QueryCriteria(); // Adds a condition to verify that the dsCountry field of the LsAddress iterative in the Account form // is not within the set of values included in xCountries. In this case, Argentina. xQueryCriteria.addConditionNOTIN(Account.getLsAddressQueryName(), Account.LsAddress.getDsCountryQueryName(), xCountries);
|
5.Grouping
The use of grouping is useful to combine results with functions such as COUNT or SUM. It allows grouping the retrieved results according to the indicated field or column.
Available Methods:
|
|
Example for a non-iterative field:
// Non-iterative field xQueryCriteria.addGroupBy(Account.getCdStatusQueryName());
|
Example for an iterative field:
// Groups by the dsCountry field of the lsAddress iterative of the Account form xQueryCriteria.addGroupBy(Account.getLsAddressQueryName(), Account.LsAddress.getDsCountryQueryName());
|
6.Sorting
A sort order can be added to query results, either in ascending or descending order. This criterion is indicated by a boolean value in the last parameter of the available methods. Possible values are:
•True: Uses descending order.
•False Uses ascending order.
Available Methods:
|
|
Example for a non-iterative field:
// Non-iterative field xQueryCriteria.addOrderCriteria(Account.getQtAnualRevQueryName());
|
Example for an iterative field:
// Sorts by the dsCountry field of the lsAddress iterative of the Account form in ascending order QueryCriteria.addOrderCriteria(Account.getLsAddressQueryName(), Account.LsAddress.getDsCountryQueryName(), true);
|
7.Add Functions
Aggregation functions such as COUNT, SUM, or AVG can be added to the QueryCriteria object. These functions must be applied before using the createSelect or createSelectWithIterative methods.
“Count” function
Available Methods:
|
addCount(String pIterativeName, String pColumnName)
|
Example for a non-iterative field:
// Non-iterative field // to determine the number of accounts grouped by state QueryCriteria xQueryCriteria = new QueryCriteria(); List<String> xList = new ArrayList<>();
xList.add(Account.getCdStatusQueryName()); xQueryCriteria.createSelect(xList); xQueryCriteria.addGroupBy(Account.getCdStatusQueryName());
|
“SUM” function
Available Methods:
addSum(String pColumnName)
|
addSum(String pIterativeName, String pColumnName)
|
Example for a non-iterative field:
// Non-iterative field // determine the sum of the annual earnings of all accounts QueryCriteria xQueryCriteria = new QueryCriteria(); xQueryCriteria.addSum(Account.getQtAnualRevQueryName()); xQueryCriteria.createSelect(new ArrayList());
|
“AVG” function
Available Methods:
addAVG(String pColumnName)
|
addAVG(String pIterativeName, String pColumnName)
|
Example for a non-iterative field:
// Non-iterative field // the average of the annual earnings of all accounts. QueryCriteria xQueryCriteria = new QueryCriteria(); String xQTANUALREV = Account.getQtAnualRevQueryName(); xQueryCriteria.addAVG(xQTANUALREV); xQueryCriteria.createSelect(new ArrayList<>()); xQueryResult = xAccountService.executeQuery(new Acount(), xQueryCriteria);
|
Example for an iterative field:
// Iterative field xQueryCriteria = new QueryCriteria(); SalesNoteService xSalesNoteService = new SalesNoteService(getApiClient()); String xSalesPrice = SalesNote.Items.getQtSalesPriceQueryName(); xQueryCriteria.addAVG(SalesNotes.getItemsQueryName(), xSalesPrice); xQueryCriteria.addConditionEquals(SalesNotate.getDsNumberQueryName(),1); HashMap<String, List<String>> xMap = new HashMap<>(); xMap.put(SalesNote.getItemsQueryName(), new ArrayList<>()); xQueryCriteria.createSelectWithIterative(new ArrayList<>(), xMap); xQueryResult = xSaleNoteService.executeQuery(new SalesNote(), xQueryCriteria); xAVGList = xQueryResult.getAVGResult(xSalePrice);
|
8.Join between entities
Available Method:
|
Below is an example that gets the name of all accounts and the tickets associated with each of them. For each ticket, the ID and title are retrieved.
QueryCriteria xQueryCriteria = new QueryCriteria();
// Creates a list of fields to select List xList = new ArrayList<>();
// Creates instances of the entities Account xAccount = new Account(); Ticket xTicket = new Ticket();
// Creates an instance of the Account service using the API client AccountService xAccountService = new AccountService(getApiClient());
// Adds field names to the selection list xList.add(Ticket.getIdTicketQueryName()); xList.add(Ticket.getDsTitleQueryName()); xList.add(Account.getDsCompanyQueryName());
// Creates the selection with the specified fields xQueryCriteria.createSelect(xList);
// Adds the JOIN between Account and Ticket xQueryCriteria.addJOIN(Account.getIdAccountQueryName(),// Join field in Account xTicket, // Ticket Object (relation with the Ticket) Ticket.getIdTicketAccountQueryName() // Join field in Tickett );
// Execute the query using the Account service QueryResult xQueryResult = xAccountService.executeQuery(xAccount, xQueryCriteria); // Get the query results List <String> xTicketIds = xQueryResult.getQueryResult(Ticket.getIdTicketQueryName()); List <String> xTicketTitles = xQueryResult.getQueryResult(Ticket.getDsTitleQueryName()); List <String> xComapanyNames = xQueryResult.getQueryResult(Account.getDsCompanyQueryName());
// Iterates over the results and records the information for (int i = 0; i < xTicketIds.size(); i++) { log("Account : " + xCompanyNames.get(i) + ". Ticket number: " + xTicketIds.get(i) + ". Title: " + xTicketTitles.get(i)); }
|
9.Operating on the results
Once an SQL query is executed, the QueryResult object contains the results grouped by the retrieved column (entity field). To interact with these results, the following methods can be used:
First Method: Retrieves the results for a specific column from the SQL query.
|
This method allows retrieving the results for a specific column from the SQL query. Receives the name of the field from which to obtain results and returns a list containing the values corresponding to that column for each row retrieved in the query.
// Creates an instance of the QueryCriteria object to define the query criteria QueryCriteria xQueryCriteria = new QueryCriteria();
// Creates an instance of the service to execute the query AccountService xAccountService = new AccountService(getApiClient());
// Creates an instance of the Account entity Account xAccount = new Account();
// Defines the columns to be retrieved List xList = new ArrayList <> (); xList.add(Account.getDsCompanyQueryName()); // Adds the company name field to the selection list
// Sets the query selection criteria xQueryCriteria.createSelect(xList);
// Adds a condition to filter accounts whose state is "1" xQueryCriteria.addConditionEquals(Account.getCdStatusQueryName(), "1");
// Executes the query and gets the results QueryResult xQueryResult = xAccountService.executeQuery(xAccount, xQueryCriteria);
// Retrieves the list of company names from the query results List xComapanyNames = xQueryResult.getQueryResult(Account.getDsCompanyQueryName());
|
As seen in the last line of the example, the results for the required field are obtained. The size of the list corresponds to the number of rows that have met the SQL query criteria.
If more than one field was selected to be retrieved when generating the SQL query, the size of the corresponding lists will always be the same for each field. If, for example, in the “SELECT” statement, in addition to retrieving the dsCompany field, there is also a request to retrieve data from the qtAnualRev field, it is necessary to ask the QueryResult object for the results for each of these fields.
A structure that allows iteration over the elements can be used to operate on each of the elements of the list. For example, using a for loop:
log("Account name: " + xAccountNameResults.get(i) + ". Annual earnings: " + xQtAnualRevResults.get(i)); }
|
As observed, the sizes of the lists match. Therefore, if multiple fields are selected, it is sufficient to generate a single iterator and retrieve each necessary piece of data. These correspond to each row retrieved from the database.
Second Method: Retrieves the results for a specific column from the function "COUNT".
getCountResult(String pColumn): List
|
This method must be given the name of the field on which the count was performed. For example:
|
A list is returned because an SQL statement can include more than one COUNT function. It is iterated in the same way as in the example of the first method.
Third Method: Retrieves the results for a specific column from the function "SUM".
|
Similar to the COUNT function, to get the results of the SUM function, the method must be given the name of the field on which the sum was performed. For example:
|
A list is returned because an SQL statement can include more than one SUM function. It is iterated in the same way as in the example of the first method.
Fourth Method: Retrieves the result of the function "AVG".
|
Just like with the COUNT and SUM functions, to obtain the results of the AVG function, the method must receive the name of the field on which the AVG function was applied.
For example:
|
A list is returned, and if it is not empty, the result of the applied AVG function is obtained from position 0 of the list.
Fifth Method: Checks if more results have not been returned due to the limits established.
|
This method is essential to check if there are more results for the generated SQL query that have not been returned due to limits set, either by default or user-configured values.
If a query with more than 10,000 records retrieved is performed, the way to iterate over them is shown in the following example:
// There are more than 10,000 accounts that meet the state condition equal to “1” // Retrieve the corporate name of the accounts whose state is “1”
QueryCriteria xQueryCriteria = new QueryCriteria(); AccountService xAccountService = new AccountService(getApiClient()); Account xAccount = new Account();
List xList = new ArrayList(); xList.add(Account.getDsCompanyQueryName()); xQueryCriteria.createSelect(xList); xQueryCriteria.addConditionEquals(Account.getCdStatusQueryName(),"1");
boolean thereareMore = true; int page = 1; //page| 1 int perPage = 10000; //10,000 records per page
while(thereareMore){ xQueryCriteria.addConditionLimits(page, perPage); QueryResult xQueryResult = xAccountService.executeQuery( xAccount, xQueryCriteria); List xComapanyNameResults = xQueryResult.getQueryResult(Account.getDsCompanyQueryName());
for(int i=0; i < xCompanyNameResults.size(); i++){ log("Account company name: " + xCompanyNameResults.get(i)); } thereareMore = xQueryResult.getExistsMoreResults(); if(therearemore) { page++; } }
|
10,000 records are set to be retrieved per page, page number 1 is iterated over, the corresponding data is obtained, and the QueryResult object is shown to see if there are any pending results. If so, the page number is incremented to get the results from the next page.
