3.9.11.7. Executing a JPQL Query (GET)

Before the execution with the REST API a query must be described in the configuration file. The rest-queries.xml file must be created in the main package of the web module (e.g. com.company.sales). Then the file must be defined in the application properties file of the web module (web-app.properties).

cuba.rest.queriesConfig = +com/company/sales/rest-queries.xml

rest-queries.xml contents:

<?xml version="1.0"?>
<queries xmlns="http://schemas.haulmont.com/cuba/rest-queries.xsd">
    <query name="ordersAfterDate" entity="sales$Order" view="order-edit-view">
        <jpql><![CDATA[select o from sales$Order o where o.date >= :startDate and o.date <= :endDate]]></jpql>
        <params>
            <param name="startDate" type="java.util.Date"/>
            <param name="endDate" type="java.util.Date"/>
        </params>
    </query>
</queries>

To execute a JPQL query the following GET request must be executed:

http://localhost:8080/app/rest/v2/queries/sales$Order/ordersAfterDate?startDate=2016-11-01&endDate=2017-11-01

The request URL parts:

  • sales$Order - extracted entity name.

  • ordersAfterDate - a query name from the configuration file.

  • startDate and endDate - request parameters with the values.

Parameter value must be passed in a format defined for the corresponding datatype. For example:

  • if the query parameter type is java.util.Date, then the value pattern is taken from the DateTimeDatatype. By default it is yyyy-MM-dd HH:mm:ss.SSS

  • for java.sql.Date query parameter type, the value pattern is taken from the DateDatatype and it is yyyy-MM-dd by default

  • for java.sql.Time the datatype is TimeDatatype and the default format is HH:mm:ss

An OAuth token must be put in the Authorization header with the Bearer type.

The method returns a JSON array of extracted entity instances:

[
  {
    "_entityName": "sales$Order",
    "_instanceName": "00002",
    "id": "b2ad3059-384c-3e03-b62d-b8c76621b4a8",
    "date": "2016-12-31",
    "description": "New Year party set",
    "number": "00002",
    "items": [
      {
        "_entityName": "sales$OrderItem",
        "_instanceName": "Jack Daniels",
        "id": "0c566c9d-7078-4567-a85b-c67a44f9d5fe",
        "price": 50.7,
        "name": "Jack Daniels"
      },
      {
        "_entityName": "sales$OrderItem",
        "_instanceName": "Hennessy X.O",
        "id": "c01be87b-3f91-7a86-50b5-30f2f0a49127",
        "price": 79.9,
        "name": "Hennessy X.O"
      }
    ],
    "customer": {
      "_entityName": "sales$Customer",
      "_instanceName": "Morgan Collins",
      "id": "5d111245-2ed0-abec-3bee-1a196da92e3e",
      "firstName": "Morgan",
      "lastName": "Collins"
    }
  }
]

A full list of possible request parameters is available in the Swagger documentation.