4.7.1.2.4. Executing JPQL Query to Retrieve Data

To execute a query, the {host:port}/app-portal/api/query.<format> GET request should be performed with the following parameters:

  • e − the name of the entity.

  • q − a JPQL data query. The request may contain parameters. Their values are provided as values of same-named parameters of HTTP query.

  • s − the identifier of the current session.

  • view (optional) − the view, which should be used to load data.

  • max (optional) − maximum number of rows in resulting dataset (similar to JPA setMaxResults).

  • first (optional) − number of the first row of resulting dataset (similar to JPA setFirstResult).

format specifies the format of obtaining the result. It takes two values: xml or json.

Examples:

http://localhost:8080/app-portal/api/query.json?e=sales$Customer&q=select+c+from+sales$Customer+c&s=748e5d3f-1eaf-4b38-bf9d-8d838587367d&view=_local
http://localhost:8080/app-portal/api/query.json?e=sales$Customer&q=select+c+from+sales$Customer+c+where+c.name=:name&s=748e5d3f-1eaf-4b38-bf9d-8d838587367d&name=Smith

For each of the passed parameters, the type can be explicitly specified by adding the parameter of the same name and the _type suffix to the request. For example:

http://localhost:8080/app-portal/api/query.json?e=sales$Customer&q=select+c+from+sales$Customer+c+where+c.name=:name&s=748e5d3f-1eaf-4b38-bf9d-8d838587367d&name=Smith&name_type=string

Specifying parameter type is optional, however it allows you to avoid parsing errors if the system cannot determine the type automatically. Normally, the type should be specified only for string parameters, which for some reason have a more specific format types (dates, numbers, UUID), but must be interpreted as strings. The list of available types can be found in meta-model description (Help > Data Model) or by obtaining the HTML-description of the model.

A query can also be executed using POST request to the {host:port}/app-portal/api/query.<format>?s=<sessionId> URL, where <sessionId> is the identifier of the current session. In this case, the query and its parameters are passed in the request body as follows.

Example POST request of JSON format, the Content-Type header must be set to application/json:

http://localhost:8080/app-portal/api/query.json?s=748e5d3f-1eaf-4b38-bf9d-8d838587367d

The request body:

{
  "entity": "sales$Customer",
  "query": "select c from sales$Customer c where c.name=:name",
  "params": [
      {
        "name": "name",
        "value": "Smith",
        "type": "string"
      }
  ]
}

Example POST request of XML format, the Content-Type header must be set to text/xml:

http://localhost:8080/app-portal/api/query.xml?s=748e5d3f-1eaf-4b38-bf9d-8d838587367d

The request body:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<QueryRequest xmlns="http://schemas.haulmont.com/cuba/restapi-query-request-v2.xsd">
    <entity>sales$Customer</entity>
    <query>select c from sales$Customer c where c.name=:name</query>
    <params>
        <param>
            <name>name</name>
            <value>Smith</value>
            <type>string</type>
        </param>
    </params>
</QueryRequest>