Search

Query Builder API (cds.ql)

The CDS Query Builder API allows to fluently construct CDS Query Language (CDS QL) statements, which can be handled by the Persistence Service or, on a lower level, be executed by the Data Store. It leverages Java lambda expressions to capture path expressions relative to the scope where they are used.

The Query Builder API can be used dynamically using Strings to refer to CDS entities and elements or in combination with the Model Reflection API. Furthermore, a static model can be generated from the CDS model, which can be used to write queries.

Select

SELECT statements with projections including path expressions such as author.name can be constructed as follows:

CqnSelect query = Select.from("my.bookshop.Books")
    .columns("title", "author.name");

Instead of defining projections through strings, lambda expressions can be used which allow using aliases through as:

Dynamic

Select.from("my.bookshop.Books")
    .columns(b -> b.get("title"),
             b -> b.get("author.name").as("author"));

Static

Select.from(Book_.class)
    .columns(b -> b.title(),
             b -> b.author().name().as("author"));

Lambda expressions also allow to apply an infix filter to a path expression. The following query selects the titles of all books and also inlines the name of the author but only if the author’s ID is larger than 1,000:

Dynamic

Select.from("my.bookshop.Books")
    .columns(b -> b.get("title"),
             b -> b.to("author").filter(a -> a.get("ID").gt(1000)).get("name"));

Static

Select.from(Book_.class)
    .columns(b -> b.title()),
             b -> b.author(a -> a.ID().gt(1000)).name());

Where clauses such as author.name = "Edgar Allen Poe" can also be defined using lambda expressions:

Dynamic

Select.from("my.bookshop.Books")
    .where(b -> b.get("author.name").eq("Edgar Allen Poe"));

Static

Select.from(Book_.class)
    .where(b -> b.author().name().eq("Edgar Allen Poe"));

In the where clause one can use different predicates. One of them is the in predicate, which allows to test if the value of an element matches any value in a given list.

Dynamic

Select.from("my.bookshop.Books")
    .where(b -> b.get("author.name").in("Edgar Allen Poe", "Ernest Hemingway"));

Static

Select.from(Book_.class)
    .where(b -> b.author().name().in("Edgar Allen Poe", "Ernest Hemingway"));

As an alternative to explicitly defining the where clause, matching can be used for a query-by-example style definition:

Map<String, Object> example = new HashMap<>();
example.put("author.name", "Edgar Allen Poe");

Select.from("my.bookshop.Books").matching(example);

Insert

INSERT statements can be constructed as follows. Data can be provided as a map or a list of maps.

Dynamic

Map<String, Object> book = new HashMap<>();
book.put("ID", 101);
book.put("title", "Capire");

CqnInsert insert = Insert.into("my.bookshop.Books").entry(book);

Static

CqnInsert insert = Insert.into(Book_.class).entry(book);

Deep Insert

The input data maps can contain maps or list of maps as values to build a deep insert for compositions, such as items of an order:

CDS model

entity Orders {
  key OrderNo : String;
  Items       : Composition of many OrderItems on Items.parent = $self;
  ...
}
entity OrderItems {
  key ID : Integer;
  book   : Association to Books;
  amount : Integer;
  ...
}

Deep insert

Map<String, Object> item;
item.put("ID", 1);
item.put("book_ID", 101);
item.put("amount", 1);
List<Map<String, Object>> items;
items.add(item);
Map<String, Object> order;
order.put("OrderNo", "1000");
order.put("Items", items);

CqnInsert insert = Insert.into(Order_.class).entry(order);

Update

UPDATE statements with new data given as a map can be constructed as follows:

Dynamic

Map<String, Object> entry = new HashMap<>();
entry.put("title", "Capire 2");

CqnUpdate update = Update.entity("my.bookshop.Books").data(entry)
    .where(b -> b.get("ID").eq(101));

Static

CqnUpdate update = Update.entity(Book_.class).data(entry)
    .where(b -> b.ID().eq(101));

Alternatively matching can be used instead of explicitly defining the where clause:

Map<String, Object> keys = new HashMap<>();
keys.put("ID", 101);

CqnUpdate update = Update.entity("my.bookshop.Books").data(entry)
    .matching(keys);

Upsert

An UPSERT is similar to an insert. However, in case the entry with the given ID already exists, it is equivalent to an update. Data can be provided as a map or a list of maps.

Dynamic

Map<String, Object> book;
book.put("ID", 101);
book.put("title", "Capire 2");

CqnUpsert upsert = Upsert.into("my.bookshop.Books").entry(book);

Static

CqnUpsert upsert = Upsert.into(Book_.class).entry(book);

Deep Upsert

The input data maps can contain maps or list of maps as values to build a deep upsert for compositions, such as items of an order. A deep upsert is equivalent to a cascading delete followed by a deep insert, that means the provided data has to be complete.

CDS Model

entity Orders {
  key OrderNo : String;
  Items       : Composition of many OrderItems on Items.parent = $self;
  ...
}
entity OrderItems {
  key ID : Integer;
  book   : Association to Books;
  amount : Integer;
  ...
}

Deep Upsert

Map<String, Object> item;
item.put("ID", 1);
item.put("book_ID", 101);
item.put("amount", 2);
List<Map<String, Object>> items;
items.add(item);
Map<String, Object> order;
order.put("OrderNo", "1000");
order.put("Items", items);

CqnUpsert upsert = Upsert.into(Order_.class).entry(order);

The upsert statement above would delete the order 1,000 including its order items and do a deep insert with item 1 as defined in the provided items list.

Delete

The DELETE operation is cascaded along the entity’s compositions and can be constructed as follows.

CDS Model

entity Orders {
  key OrderNo : String;
  Items       : Composition of many OrderItems on Items.parent = $self;
  ...
}
entity OrderItems {
  book   : Association to Books;
  ...
}

Dynamic

CqnDelete delete = Delete.from("my.bookshop.Orders")
    .where(b -> b.get("OrderNo").eq(1000));

The delete statement above would delete the order 1,000 including its order items, but no books since this relationship is modeled as an association.

Static

CqnDelete delete = Delete.from(Order_.class)
    .where(b -> b.OrderNo().eq(1000));

Alternatively matching can be used to identify the rows marked for deletion:

Map<String, Object> keys = new HashMap<>();
keys.put("OrderNo", 1000);

CqnDelete delete = Delete.from("my.bookshop.Orders").matching(keys);

Parsing CQN

CDS QL queries can also be constructed from a CQN string*:

String cqnQuery = "{'SELECT': {'from': {'ref': ['my.bookshop.Books']},
    'where': [{'ref': ['title']}, '=', {'val': 'Capire'}]}}";
CqnSelect query = Select.cqn(cqnQuery);

* For readability reasons, we used single quotes instead of double quotes as required by the JSON specification.

The constructed queries can then be modified using the query builder API:

String cqnQuery = ...
CqnSelect query = Select.cqn(cqnQuery).columns("price");

For Insert, Update and Delete this is supported as well.