4.8.2.8.5. Executing a JPQL Query

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.

An OAuth token must be placed to 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.