Search

    Executing CQN Queries

    API to execute CDS QL statements on services accepting CQN queries.

    Content

    Query Execution

    CDS QL statements can be executed using the run method of any service, that is accepting CQN queries:

    CqnService service = ...
    
    CqnSelect query = Select.from("bookshop.Books")
        .columns("title", "price");
    
    Result result = service.run(query);
    

    Parameterized Execution

    Queries, as well as update and delete statements, can be parameterized with positional, or named parameters.

    Named Parameters

    The following query uses two parameters named id1 and id2. The parameter values are given as a map:

    import static com.sap.cds.ql.CQL.param;
    
    CqnSelect query = Select.from("bookshop.Books")
        .where(b -> b.get("ID").eq(param("id1"))
                .or(b.get("ID").eq(param("id2"))));
    
    Map paramValues = new HashMap<>();
    paramValues.put("id1", 101);
    paramValues.put("id2", 102);
    
    Result result = service.run(query, paramValues);
    

    Indexed Parameters

    The following query uses two indexed parameters defined through param(i):

    import static com.sap.cds.ql.CQL.param;
    
    CqnSelect query = Select.from("bookshop.Books")
        .where(b -> b.get("ID").in(param(0), param(1)));
    
    Result result = service.run(query, 101, 102);
    

    Before the execution of the statement the values 101 and 102 are bound to the defined parameters.

    Querying Parameterized Views on SAP HANA

    To query views with parameters on SAP HANA, you need to build a select statement and execute it with the corresponding named parameters.

    Let’s consider the following Book entity and a parameterized view that returns the ID and title of Books with number of pages less than numOfPages:

    entity Book {
        key ID : Integer;
        title  : String;
        pages  : Integer;
    }
    
    entity BookView(numOfPages : Integer) as SELECT FROM Book {ID, title} WHERE pages < :numOfPages;
    

    The Java query that returns books with number of pages less than 200:

    CqnSelect query = Select.from("BookView");
    
    Result result = service.run(query, Collections.singletonMap("numOfPages", 200));
    

    Pessimistic Locking

    To ensure that data returned by query execution isn’t modified by a concurrent transaction, you can set an exclusive write lock on it. To do that:

    1. Start a transaction (either manually or let the framework take care of it).
    2. Query the data and set a lock on it.
    3. Perform the processing and modify the data inside the same transaction (if required).
    4. Commit (or roll back) the transaction, which releases the lock.

    To be able to query and lock the data until the transaction is completed, just call a lock() method and set an optional parameter timeout.

    In the following example, a book with ID 1 is selected and locked until the transaction is finished. Thus, one can avoid situations when other threads or clients are trying to modify the same data in the meantime:

    // Start transaction
    // Obtain and set a write lock on the book with id 1
    	service.run(Select.from("bookshop.Books").byId(1).lock());
    	...
    // Update the book locked earlier
    	Map<String, Object> data = Collections.singletonMap("title", "new title");
    	service.run(Update.entity("bookshop.Books").data(data).byId(1));
    // Finish transaction
    

    The lock() method has an optional parameter timeout that indicates the maximum number of seconds to wait for the lock acquisition. If a lock can’t be obtained within the timeout, a CdsLockTimeoutException is thrown. If timeout isn’t specified, a database-specific default timeout will be used.

    Data Manipulation

    The CQN API allows to manipulate data by executing insert, update, delete, or upsert statements.

    Update

    The update operation can be executed as follows:

    Map<String, Object> book = new HashMap<>();
    book.put("title", "CAP");
    
    CqnUpdate update = Update.entity("bookshop.Books").data(book).where(b -> b.get("ID").eq(101));
    long updateCount = service.run(update).rowCount();
    

    Working with Structured Documents

    It’s possible to work with structured data as the insert, update, and delete operations cascade along compositions.

    Changing the Cascading Behavior

    By default, insert, update, and delete operations are cascaded over compositions, whereas for associations these operations are not cascaded. This can be changed using the @cascade annotation.

    Annotating associations with @cascade: {insert, update, delete} enables deep updates using these associations. As a short form @cascade: {all} can be used. Given the following CDS model with two entities and an association between them, only insert and update operations are cascaded through author:

    entity Book {
      key ID : Integer;
      title  : String;
    
      @cascade: {insert, update}
      author : Association to Author;
    }
    
    entity Author {
      key ID : Integer;
      name   : String;
    }
    

    To disable cascading certain operations over compositions, set the cascade value to false. For example, @cascade: {update: false, delete: false} disables cascading update and delete operations for the annotated composition.

    Deep Insert / Upsert

    Insert and upsert statements for an entity have to include the keys and optionally data for the entity’s composition targets, which are then inserted or upserted along with the root entity. A deep upsert is equivalent to a cascading delete followed by a deep insert:

    Iterable<Map<String, Object>> books;
    
    CqnInsert insert = Insert.into("bookshop.Books").entries(books);
    Result result = service.run(insert);
    
    CqnUpsert upsert = Upsert.into("bookshop.Books").entries(books);
    Result result = service.run(upsert);
    

    Cascading Delete

    The delete operation is cascaded along the entity’s compositions. All composition targets that are reachable from the (to be deleted) entity are deleted as well.

    The following example deletes the order with ID 1000 including all its items:

    CqnDelete delete = Delete.from("bookshop.Orders").matching(singletonMap("OrderNo", 1000));
    long deleteCount = service.run(delete).rowCount();
    

    Updatable Views

    On some views, the runtime supports Insert, Upsert, Update, and Delete operations. If possible, it will resolve the projection to the underlying entity or view and perform the respective operation.

    Operations on views that can’t be resolved by the runtime are directly executed on the database. In this case, it’s database-dependent if the operation can be executed.

    Views using only columns and excluding clauses are updatable. For example:

    // Supported
    entity Order as projection on bookshop.Order;
    entity OrderExcluding as SELECT from bookshop.Order excluding {status};
    entity OrderStatus as projection on bookshop.Order {OrderNo, status as state};
    

    The columns clause must ensure the following for a view to be updatable:

    1. All elements with not null constraint must be included unless a default value is specified.
    2. The select list contains elements only, no functions, expressions or literals are used.
    3. All key elements are used in the projection. However, for Insert, if the key element’s value is generated, it need not be used in the projection.
    4. No element from an associated entity using path expressions can be used.
    // Supported
    entity AliasOrderHeader as projection on bookshop.OrderHeader { key HeaderID, createdAt, status as headerStatus, shippingAddress.country as shippingCountry };
    entity OrderWithHeader as projection on bookshop.Order excluding { items, fulfillment, fulfillment_id };
    

    Path expressions navigating to to-one associations, can be used in projections as shown by the AliasOrderHeader view in the previous sample. That view includes the element country from the associated entity ShippingAddress. As such, this will support Deep Insert/Update operations as well.

    All other clauses, when used with view definition, will render them not updatable. Only if the clause has native database support and can be executed by the corresponding database an update is possible. Utilizing JOINS or the where clause will classify the view to become read-only too, as shown by the JoinOrder and DeliveredOrders view respectively.

    // Unsupported
    entity JoinOrder as SELECT from bookshop.Order inner join bookshop.OrderHeader on Order.header.HeaderID = OrderHeader.HeaderID { Order.OrderNo, Order.items, OrderHeader.status };
    entity DeliveredOrders as select from bookshop.Order where status='delivered';
    

    Using I/O Streams in Queries

    As described in section Predefined Types it’s possible to stream the data, if the element is annotated with @Core.MediaType. The following example demonstrates how to allocate the stream for element coverImage, pass it through the API to an underlying database and close the stream.

    Entity Books has an additional annotated element coverImage : LargeBinary:

    entity Books {
      key ID : Integer;
      title  : String;
      ...
      @Core.MediaType
      coverImage : LargeBinary;
    }
    

    Java snippet for creating element coverImage from file IMAGE.PNG using java.io.InputStream:

    // Transaction started
    
    Result result;
    try (InputStream resource = getResource("IMAGE.PNG")) {
        Map<String, Object> book = new HashMap<>();
        book.put("title", "My Fancy Book");
        book.put("coverImage", resource);
    
        CqnInsert insert = Insert.into("bookshop.Books").entry(book);
        result = service.run(insert);
    }
    
    // Transaction finished
    

    Query Result Processing

    The result of a query is abstracted by the Result interface, which is an iterable of Row. A Row is just a Map augmented with some convenience methods.

    You can iterate over a Result:

    Result result = ...
    
    for (Row row : result) {
      System.out.println(row.get("title"));
    }
    

    Or process it with the Stream API:

    Result result = ...
    
    result.forEach(r -> System.out.println(r.get("title")));
    
    result.stream().map(r -> r.get("title")).forEach(System.out::println);
    

    If your query is expected to return exactly one row, you can access it with the single method:

    Result result = ...
    
    Row row = result.single();
    

    If it returns a result, like a find by id would, you can obtain it using first:

    Result result = ...
    
    Optional<Row> row = result.first();
    row.ifPresent(r -> System.out.println(r.get("title")));
    
    

    Typed Result Processing

    The element names and their types are checked only at runtime. Alternatively you can use interfaces to get typed access to the result data:

    interface Book {
      String getTitle();
      Integer getStock();
    }
    
    Row row = ...
    Book book = row.as(Book.class);
    
    String title = book.getTitle();
    Integer stock = book.getStock();
    

    Interfaces can also be used to get a typed list or stream over the result:

    Result result = ...
    
    List<Book> books = result.listOf(Book.class);
    
    Map<String, String> titleToDescription =
      result.streamOf(Book.class).collect(Collectors.toMap(Book::getTitle, Book::getDescription));
    

    For the entities defined in the data model, CAP Java SDK can generate interfaces for you through a Maven plugin.

    Getting Entity References

    If a result set row unambiguously originates from a single instance of an entity, a reference to this instance can be obtained by the row’s ref() method:

    // SELECT from Author[101]
    CqnSelect query = Select.from(AUTHOR).byId(101);
    Author authorData = service.run(query).single(Author.class);
    
    String authorName = authorData.getName();    // data access
    Author_ author    = authorData.ref();        // typed reference to Author[101]
    

    Similar for untyped results:

    Row authorData = service.run(query).single();
    StructuredType<?> author = authorData.ref(); // untyped reference to Author[101]
    

    Using these entity references you can easily write queries on the source entity, which can then be executed on the same or on a different service:

    Author_ author = authorData.ref();
    
    // SELECT from Author[101].books { sum(stock) as stock }
    CqnSelect q = Select.from(author.books())
         .columns(b -> func("sum", b.stock()).as("stock"));
    
    CqnInsert i = Insert.into(author.books())
         .entry("title", "The Work of " + authorData.getName());
    
    CqnUpdate u = Update.entity(author.books())
         .data("price", 7.90).where(b -> b.stock().lt(10));
    
    CqnDelete d = Delete.from(author.books())
         .where(b -> b.stock().lt(1));
    

    Introspecting the Row Type

    The rowType method allows to introspect the element names and types of a query’s Result. It returns a CdsStructuredType describing the result in terms of the Reflection API:

    CqnSelect query = Select.from(AUTHOR)
         .columns(a -> a.name().as("authorName"), a -> a.age());
    
    Result result = service.run(query);
    
    CdsStructuredType rowType = result.rowType();
    rowType.elements(); // "authorName", "age"
    rowType.getElement("age").getType().getQualifiedName();  // "cds.Integer"
    rowType.findElement("ID"); // Optional.empty()
    
    Show/Hide Beta Features