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 accepts 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 named, or indexed parameters. Update and delete statements with named parameters can be executed in batch mode using multiple parameter sets.

    Named Parameters

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

    import static com.sap.cds.ql.CQL.param;
    
    CqnDelete delete = Delete.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(delete, paramValues);
    

    Indexed Parameters

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

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

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

    Batch Execution

    Update and delete statements with named parameters can be executed as batch with multiple parameter sets. The named parameters example from above can be expressed using batch delete with a single parameter and two value sets:

    import static com.sap.cds.ql.CQL.param;
    
    CqnDelete delete = Delete.from("bookshop.Books")
        .where(b -> b.get("ID").eq(param("ID")));
    
    Map paramSet1 = singletonMap("ID", 101);
    Map paramSet1 = singletonMap("ID", 102);
    
    Result result = service.run(query, asList(paramSet1, paramSet2));
    long deletedRows = result.rowCount();
    

    From the result of a batch update/delete the total number of updated/deleted rows can be determined by rowCount(), and rowCount(batchIndex) returns the number of updated/deleted rows for a specific parameter set of the batch. The number of batches can be retrieved via the batchCount() method. Batch updates also return the update data.

    The maximum batch size for update and delete can be configured via cds.sql.max-batch-size and has a default of 1000.

    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.

    ❗ Warning
    For inactive draft entities @cascade annotations are ignored.

    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

    The CDS Java SDK can resolve non-complex views to their underlying entity, which makes it possible to execute Insert, Upsert, Update and Delete operations on the view. Views can be resolved if the following conditions are met:

    The view definition does not use any other clause than columns and excluding, and the projection

    1. must include all key elements; with the exception of insert operations with generated UUID keys
    2. must include all elements with a not null constraint, unless they have a default value
    3. must not include functions and expressions
    4. must not include path expressions using to-many associations

    Path expressions navigating to-one associations, can be used in projections as shown by the Header view in the following example. The Header view includes the element country from the associated entity Address.

    // Supported
    entity Order as projection on bookshop.Order;
    entity Order as projection on bookshop.Order { ID, status as state };
    entity Order as projection on bookshop.Order excluding { status };
    entity Header as projection on bookshop.OrderHeader { key ID, address.country as country };
    

    If a view is too complex to be resolved by the CDS runtime, the operation is executed on the view and the behavior is database dependent. Views that cannot be resolved by the CDS runtime include the use of join, union and the where clause:

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

    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
    

    ###Using Native SQL

    CAP Java doesn’t have a dedicated API to execute native SQL Statements. However, when using Spring as application framework you can leverage Spring’s features to execute native SQL statements. See Execute SQL statements with Spring’s JdbcTemplate for more details.

    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