Search

Building CQN Queries

API to fluently build CDS QL statements in Java

Content

Introduction

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.

Concepts

The Query Builder’s Java API

Starting point for the construction of CDS QL statements are the builder classes Select, Insert, Upsert, Update, and Delete. These factories allow to create builder instances. Use these instances to compose the statements’ clauses.

Lambda Expressions

The Query Builder API leverages Java lambda expressions to fluently compose expressions and path expressions that are used by the statements’ clauses.

The following example shows a CQL query and how it’s composed by the Query Builder API:

--CQL query
SELECT from bookshop.Books { title } where year < 2000

// Query Builder API:
Select.from("bookshop.Books")
    .columns(b -> b.get("title")
    .where(b -> b.get("year").lt(2000));

Here, the lambda expression b -> b.get("title") is a function that accesses the element title in the book b. Hence, the element title is put on the query’s select list.

The function b -> b.get("year").lt(2000) builds a predicate that compares the book’s element ‘year’ with the value 2000, which is then used to define the where clause of the select statement.

Path Expressions

Use path expressions to access elements of related entities.

The following example shows a CQL query and how it’s composed by the Query Builder API:

--CQL query
SELECT from bookshop.Books { title, author.name }

// Query Builder API
Select.from("bookshop.Books")
    .columns(b -> b.get("title"), b -> b.to("author").get("name"));

The CQL query accesses the name element in the Authors entity, which is reached from Books via the association author. In the Query Builder API, navigate to a related entity using the to method.

Alternatively paths can be specified directly in get by separating the path segments with a dot:

// Query Builder API
Select.from("bookshop.Books")
    .columns(b -> b.get("author.name"));

Target Entity Sets

All CDS QL statements operate on a target entity set, which is specified via the from, into, and entity methods of Select/Delete, Insert/Upsert, and Update statements.

In the simplest case, the target entity set identifies a complete CDS entity set:

Select.from("bookshop.Books");

Map<String, Object> data = ...;
Insert.into("bookshop.Authors").data(data);

The target entity set can also be defined by an entity reference, which allows using paths over associations and filters. Entity references can be defined inline using lambda expressions in the Query Builder. The following example shows a CQL query and how it’s composed by the Query Builder API.

--CQL query
SELECT from Orders[23].items { amount, book.title as "book" } 

// Query Builder API
Select.from("bookshop.Orders", o -> o.filter(o.get("ID").eq(23))
                                     .to("items"));
      .columns(i -> i.get("amount"), 
               i -> i.get("book.title").as("book"));

The target entity set in the query is defined by the entity reference in the from clause. The reference targets the items of the order with ID 23. From this target entity set (of type Items), the query selects the amount and the title of the book.

In the Query Builder API, the lambda expression o.filter(o.get("ID").eq(23)).to("items") is evaluated relative to the root, Orders in this example. All lambda expressions that occur in the other clauses of the query are relative to the target entity set. In the example i -> i.get("amount") accesses the element amount of Items.

To target components of a structured document, we recommend using path expressions with infix filters.

Dynamic Usage Compared to Static Usage

The Query Builder API can be used dynamically. Use strings to refer to CDS entities and elements or use the corresponding components obtained from the Model Reflection API. Then, names and types of the entities and elements used in the composition of CDS QL queries can be checked only at runtime.

If you want to avoid this restriction, use a static model, generated from the CDS model, which can then be used to compose queries.

The following example compares using the Query Builder API in the dynamic and the static style:

// dynamic
Select.from("bookshop.Books")
    .columns(b -> b.get("title"),
             b -> b.get("author.name").as("authorName"))
    .where(b -> b.get("year").lt(2000));

// static
import static bookshop.Bookshop_.BOOKS;

Select.from(BOOKS)
    .columns(b -> b.title(),
             b -> b.author().name().as("authorName"))
    .where(b -> b.year().lt(2000));

Using a static model has several advantages:

  • The names of entities and elements are checked at design time.
  • Use code completion in the IDE.
  • Predicates and expressions can be composed in a type-safe way.
  • More compact code.

In general, it’s recommended to use the static style when implementing business logic that requires accessing particular elements of entities. Using the dynamic style is appropriate for generic code.

Parameters

The Query Builder API supports parameters for parameterized execution:

import static com.sap.cds.ql.CQL.param;

Select.from("bookshop.Authors")
	.columns("name").byId(param());
dataStore.execute(query, 111);

Select

Source

The source of the select statement determines the data set to which the query is applied. It’s specified by the from method.

From entity set

Typically a select statement selects from an entity set:

--CQL query
SELECT from bookshop.Books { title, author.name } 

// Query Builder API (dynamic usage)

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

From reference

The source can also be defined by a path expression referencing an entity set.

This query selects from the items of the order 23.

--CQL query
SELECT from Orders[23].items 

// Query Builder API (static usage)
import static bookshop.Bookshop_.ORDERS;

Select.from(ORDERS, o -> o.filter(o.ID().eq(23)).items());

From subquery

It’s also possible to execute a nested select where an outer query operates on the result of a subquery.

--CQL query
SELECT from (SELECT from Authors order by age asc limit 10) order by name

// Query Builder API
CqnSelect youngestAuthors = Select.from(AUTHORS).orderBy(a -> age()).limit(10);
Select.from(youngestAuthors).orderBy("name");

This subquery selects the youngest authors, which the outer query sorts by name.

Limitations:

  • The subquery must not expand to-many associations.
  • Associations aren’t propagated to the outer query and hence can’t be used there in path expressions.
  • The outer query can only be defined with the dynamic builder style.

Projections

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

CqnSelect query = Select.from("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("bookshop.Books")
    .columns(b -> b.get("title"),
             b -> b.get("author.name").as("authorName"));

// static
import static bookshop.Bookshop_.BOOKS;

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

The path expression b.author().name() is automatically evaluated at runtime. For an SQL data store, it’s converted to a LEFT OUTER join.

Using inline

If the select list contains multiple path expressions that follow the same association, using inline is an option and a shortcut notation:

// Java example
import static bookshop.Bookshop_.AUTHORS;

// using multiple path expressions
Select.from(AUTHORS)
    .columns(a -> a.name(),
             a -> a.books().title().as("book"),
             a -> a.books().isbn(),
             a -> a.books().year())
    .where(a -> name().eq("Bram Stoker"));

// using inline
Select.from(AUTHORS)
    .columns(a -> a.name(),
             a -> a.books().inline(
                      b -> b.title().as("book"),
                      b -> b.isbn(),
                      b -> b.year())
    .where(a -> name().eq("Bram Stoker"));

Both queries are equivalent and have the same result: a flat structure:

[
  {
    "name" : "Bram Stoker",
    "book" : "Dracula",
    "isbn" : "978-1503261389",
    "year" : 1897
  },
  {
    "name" : "Bram Stoker",
    "book" : "Miss Betty",
    "isbn" : "978-1079982206",
    "year" : 1898
  }
]

Using expand

The expand function enables query expansion of elements of associated entities. Using expand results in a deep data structure, where the expanded entities are nested substructures:

// Java example
// using expand
import static bookshop.Bookshop_.AUTHORS;

Select.from(AUTHORS)
    .columns(a -> a.name(),
             a -> a.books().expand(
                      b -> b.title().as("book"),
                      b -> b.isbn(),
                      b -> b.year())
    .where(a -> name().eq("Bram Stoker"));

It expands the elements title, isbn, and year of the Books entity into a substructure named books:

[
  {
    "name" : "Bram Stoker",
    "books" : 
    [
      {
        "book" : "Dracula",
        "isbn" : "978-1503261389",
        "year" : 1897
      }
      {
        "book" : "Miss Betty",
        "isbn" : "978-1079982206",
        "year" : 1898
      }
    ]
  }
]

If expand() is invoked on an association without parameters, it expands all nonassociation elements of the associated entity. The following query b.title() selects the title of the book and b.author.expand() expands all elements of the book’s author:

Select.from("bookshop.Books")
      .columns(b -> b.title(), 
               b -> b.author.expand());

Filtering

The Query Builder API offers different ways to restrict the result of a query by applying a filter.

Using byID

To find an entity identified by a given ID, the byId method can be used. This method adds a predicate to the query that filters out the entity with the given ID value. The byId method currently doesn’t support compound keys. The following example retrieves the Author entity with ID 0.

Select.from("bookshop.Authors").byId(0);

Using matching

matching is a query-by-example style alternative to explicitly define the where clause. This method adds a predicate to the query that filters out all entities where the elements’ values are equal to values given by a key-value map.

The following example uses ‘key’ as the element to be matched.

Select.from("bookshop.Books").matching(singletonMap("id", 42));

The key-value map object can contain keys based on path expressions, referring to elements of an associated entity. In the following example, bookshop.Books has a many to one association to the Author entity. Thus, author.name refers to the name element within the Author entity.

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

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

The search method adds a predicate to the query that filters out all entities where any searchable element contains a given search term or matches a search expression.

  1. Define searchable elements

    By default all elements of type cds.String of an entity are searchable. However, using the @cds.search annotation the set of elements to be searched can be defined. You can extend the search also to associated entities. For more information on @cds.search, please refer to Search Capabilities.

    Consider following CDS Entity. There are 2 elements, title and name, of type String, making them both searchable by default.

     entity Book {
   key ID : Integer;
   name   : String;
   title  : String;
 }

    In the following example, element title is included in @cds.search. Only this particular element is searchable then.

     @cds.search: {title}
 entity Book {
   key ID : Integer;
   name   : String;
   title  : String;
 }

  2. Construct queries with search

    Let’s consider the following Book entity once again:

     entity Book {
   key ID : Integer;
   name   : String;
   title  : String;
 }

  • Use search terms

    The following Select statement shows how to search for an entity containing the single search term “Allen”.

      // Book record - (ID, title, name) VALUES (1, "The greatest works of James Allen", "Unwin")

  Select.from("bookshop.Books")
          .columns("id", "name")
          .search("Allen");

    The element title is searchable, even though title isn’t selected.

  • Use search expressions

    It’s also possible to create a more complex search expression using AND, OR, and NOT operators. Following examples show how you can search for entities containing either term “Allen” or “Heights”.

      // Book records - 
  // (ID, title, name) VALUES (1, "The greatest works of James Allen", "Unwin")
  // (ID, title, name) VALUES (2, "The greatest works of Emily Bronte", "Wuthering Heights")

  Select.from("bookshop.Books")
          .columns("id", "name")
          .search(term -> term.has("Allen").or(term.has("Heights")));

Using where clause

In a where clause, leverage the full power of CDS QL expressions to compose the query’s filter:

Select.from("bookshop.Books")
	.where(b -> b.get("ID").eq(251).or(
              b.get("title").startsWith("Wuth")));

Grouping

The Query Builder API offers a way to group the results into summarized rows (in most cases these are aggregate functions) and apply certain criteria on it.

Let’s assume the following dataset for our examples:

ID NAME
100 Smith
101 Miller
102 Smith
103 Hugo
104 Smith

Group By

The groupBy clause groups by one or more elements and usually involves aggregate functions, such as count, sum, max, avg, and so on. It returns one row for each group.

In the following example, we select the authors’ name and, using the aggregate function count, determine how many authors with the same name exist in bookshop.Authors.

Select.from("bookshop.Authors")
		.columns(c -> c.get("name")), c -> func("count", c.get("name")).as("count")
		.groupBy(c -> c.get("name"));

If we execute the query on our dataset, we get the following result:

name count
Smith 3
Miller 1
Hugo 1

Having

To filter the grouped result, having is used. Both, having and where, filter the result before group by is applied and can be used in the same query.

The following example selects authors where count is higher than 2:

Select.from("bookshop.Authors")
		.columns(c -> c.get("name")), c -> func("count", c.get("name")).as("count")
		.groupBy(c -> c.get("name"))
		.having(c -> func("count", a.get("name")).gt(2));

If we execute the query on our dataset, we get the following result:

name count
Smith 3

Ordering and Pagination

The Query Builder API allows to specify the sort order of query results. The sort specification governs, according to which elements the result is sorted, and which sort order (ascending or descending) is applied.

By default Select returns the rows in no particular order.

Order By

To ensure a specific order in a query use orderBy, which allows sorting by one or more columns in ascending or descending order.

Select.from("bookshop.Books")
		.columns(c -> c.get("ID"), c -> c.get("title"))
		.orderBy(c -> c.get("ID").desc(), c -> c.get("title").asc());

On SAP HANA, the user locale is passed to the database, resulting in lexicographical sorting of string-based columns.

Pagination

Pagination (dividing the result set into discrete subsets of a certain size) can be achieved by using limit, which has the following optional parameters:

  • rows: A number of rows to be returned. It’s useful when dealing with large amounts of data, as returning all records in one shot can impact performance.
  • offset: A particular number of rows to be skipped.

The following example selects all books, skip the first 20 rows, and return only 10 subsequent books:

Select.from("bookshop.Books").limit(10, 20);

In this example, it’s assumed that the total number of books is more or equal to 20. Otherwise, result set is empty.

The pagination isn’t stateful. If rows are inserted or removed before a subsequent page is requested, the next page could contain rows that were already contained in a previous page or rows could be skipped.

Write Lock

Write Lock can be achieved by using Pessimistic Locking.

The following example shows how to build a select query with a write lock. The query tries to acquire a lock for a maximum of 5 seconds, as specified by an optional parameter timeout:

Select.from("bookshop.Books").where(b -> b.get("ID").eq(1)).lock(5);

Insert

The Insert statement inserts new data into a target entity set. An Insert statement is created by the Insert builder class.

The target of the insert is specified by the into method.

As in the following example, the target of the insert can be specified by a fully qualified entity name or by a CdsEntity you obtain from the Reflection API:

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

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

or it can be a path expression, for example:

import static bookshop.Bookshop_.BOOKS;
 
Map<String, Object> bookId = Collections.singletonMap("ID", 85);

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

CqnInsert insert = Insert.into(BOOKS, b -> b.matching(bookId)).publisher())
                         .entry(publisher);

Simple Insert

To insert a single entry, provide the data as a map to the entry method:

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

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

Bulk Insert

Insert also supports a bulk operation. Here the data is passed as an Iterable of maps to the entries method:

import static bookshop.Bookshop_.BOOKS;

Map<String, Object> b1;
b1.put("ID", 101);
b2.put("title", "Capire 1");

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

List<Map<String, Object>> data = new ArrayList<>();
data.add(b1);
data.add(b2);

CqnInsert insert = Insert.into(BOOKS).entries(data);

Deep Insert

To build a deep insert, the input data maps can contain maps or list of maps as values, such as items of an order. By default, the insert operation cascades over compositions only. To cascade it also over selected associations, use the @cascade annotation.

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;
  ...
}

Find this source also in cap/samples.

Java:

import static bookshop.Bookshop_.ORDERS;

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(ORDERS).entry(order);

Upsert

The Upsert statement either inserts a new entry or, if an entry with the same ID previously existed, completely replaces it with given data. An Upsert statement is created by the Upsert builder class.

Simple Upsert

To upsert a single entry, provide the data as a map to the entry method:

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

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

Bulk Upsert

The Upsert also supports a bulk operation. Here the data is passed as an Iterable of maps to the entries method:

import static bookshop.Bookshop_.BOOKS;

Map<String, Object> b1;
b1.put("ID", 101);
b2.put("title", "Capire 1");

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

List<Map<String, Object>> data = new ArrayList<>();
data.add(b1);
data.add(b2);

CqnUpsert upsert = Upsert.into(BOOKS).entries(data);

Deep Upsert

To build a deep upsert, the input data maps can contain maps or list of maps as values, such as items of an order. A deep upsert is equivalent to a cascading delete followed by a deep insert, which 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;
  ...
}

Find this source also in cap/samples.

Java:

import static bookshop.Bookshop_.ORDERS;

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(ORDERS).entry(order);

By default, insert and delete operations cascade over compositions only. In the example above, the Upsert statement deletes the order with id 1000 including its items and does a deep insert with item 1 as defined in the provided items list.

To enable cascading inserts and deletes over selected associations, use the @cascade annotation.

Update

The Update statement updates existing entities with new data. It either updates individual entities or all entities that match a filter, using searched update.

An Update statement is created by the Update builder class.

Define the Target

The target entity of the update is specified by the entity method.

The following example shows how to specify the target of the update using a fully qualified entity name or a CdsEntity you obtain from the Reflection API. Here the ID values are extracted from the data:

Map<String, Object> data = new HashMap<>();
data.put("ID", 100);
data.put("title", "CAP Matters");

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

In the following example, the target is identified by a structured type of the static model:

import static bookshop.Bookshop_.BOOKS;

CqnUpdate update = Update.entity(BOOKS).data(entry);

Provide the Data

The new data is provided as a map to the data method. Also accessor interfaces allow to construct the data in a typed way:

import static bookshop.Bookshop_.BOOKS;

Books book = Books.create();
book.setID(100);
book.setTitle("CAP Matters");

CqnUpdate update = Update.entity(BOOKS).data(book);

Update Individual Entities

Typically, Update is used to update individual entities with new data. There are different options to specify the ID of the entity to be updated.

Extracting the ID from the Data

In the simplest case, the ID value is part of the new data. Then it’s automatically extracted from the data. In the following example, the ID value 100 is extracted from the given data and the book with ID 100 will be updated:

Map<String, Object> data = new HashMap<>();
data.put("ID", 100);
data.put("title", "CAP Matters");

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

Using byId

If the ID value isn’t part of the data, it can be specified with the byId method:

Map<String, Object> data = Collections.singletonMap("title", "CAP Matters");

CqnUpdate update = Update.entity("bookshop.Books").data(entry).byId(100);

Using Matching

Alternatively, the ID values can be specified as a map using matching:

Map<String, Object> data = Collections.singletonMap("title", "CAP Matters");
Map<String, Object> keys = Collections.singletonMap("ID", 100);

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

Using Path Expression

The individual target entity, can also be directly specified using a path expression:

Map<String, Object> data = Collections.singletonMap("title", "CAP Matters");
Map<String, Object> keys = Collections.singletonMap("ID", 100);

CqnUpdate update = Update.entity("bookshop.Books", b -> b.matching(keys)).data(entry);

Bulk Update

Update multiple individual entities in a target entity set, using bulk update. The ID values are then extracted from the data, which is given as a collection of maps using the entries method. The following example illustrates this, using accessor interfaces:

Books b1 = Books.create();
b1.setID(100);
b1.setStock(10);
Books b2 = Books.create();
b2.setID(101);
b2.setStock(11);
List<Books> data = Arrays.asList(b1, b2);

CqnUpdate update = Update.entity("bookshop.Books").entries(data);

A bulk update can be executed more efficiently than individual update operations.

A bulk update is supported only if all entries contain the same elements. Deep bulk updates aren’t supported.

Deep Update

A deep update allows to update a single entity including its associated entities. In contrast to a deep upsert (replace), a deep update has patch semantics, that means, it’s sufficient to only provide values for the keys and the elements to be updated. Elements that aren’t contained in the update data keep their old value. However, for to-many compositions or associations, the provided list of entities has to be complete, entities that aren’t in the list will be removed and if compositions also deleted.

Given the following Order:

{
   "OrderNo": "1000",
   "status": "new",
   "createdAt": "2020-03-01T12:21:34.000Z",
   "items": [{"Id":1, "book":{"ID":100}, "amount":1}, 
             {"Id":2, "book":{"ID":200}, "amount":1}]
}

A deep update Update.entity("Orders").entry(order) with the following order data, which can be constructed using Maps, Lists and the generated accessor interfaces:

{
   "OrderNo": "1000",
   "status": "in process",
   "items": [{"Id":1, "amount":2}, 
             {"Id":3, "book":{"ID":300}, "amount":1}]
}

Results in the updated Order:

{
   "OrderNo": "1000",
   "status": "in process",
   "createdAt": "2020-03-01T12:21:34.000Z",
   "items": [{"Id":1, "book":{"ID":100}, "amount":2}, 
             {"Id":3, "book":{"ID":300}, "amount":1}]
}
  • Order status changed to “in process”
  • Item 1 amount changed to 2
  • Item 2 removed and since items is a composition also deleted
  • Item 3 created and added to items

To enable cascading insert, update and delete operations over associations, use the @cascade annotation.

Searched Update

Update all entities that match a filter specified with the where method, using a searched update. In the following example, the stock of all books with the title containing CAP is set to 1000:

Map<String, Object> data = Collections.singletonMap("stock", 1000);

CqnUpdate update = Update.entity("bookshop.Books")
                         .data(data)
                         .where(b -> b.get("title").contains("CAP"));

Not all data stores support a searched update.

Delete

The Delete operation 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));

// static
import static bookshop.Bookshop_.ORDERS;

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

By default, delete operations are cascaded along compositions. In the example, the delete statement would delete the order with id 1000 including its items, but no books since this relationship is modeled as an association. To enable cascading deletes over selected associations, use the @cascade annotation.

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

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

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

Expressions

The Query Builder API supports using expressions in many places. Expressions consist of values, which can be used, for example, in Select.columns to specify the select list of the statement. Values can also be used in predicates that allow, for example, to specify filter criteria for Select or Delete statements.

Entity References

Entity references specify entity sets. They can be used to define the target entity set of a CDS QL statement. They can either be defined inline using lambda expressions in the Query Builder (see Target Entity Sets) or via the CQL.entity method. The following example shows an entity reference describing the set of authors that have published books in the year 2020:

import static com.sap.cds.ql.CQL.entity;

// bookshop.Books[year = 2020].author
StructuredType<?> authors = 
   entity("bookshop.Books").filter(b -> b.get("year").eq(2020)).to("author");

// SELECT from bookshop.Books[year = 2020].author { name }
Select.from(authors).columns("name");

Values

Use values in a query’s select list as well as in order-by. In addition, values are useful to compose filter expressions.

Element References

Element references reference elements of entities. To compose an element reference, the Query Builder API uses lambda expressions. Here the function b -> e.title() accesses the book’s title. The dynamic usage b.to("author").get("name") accesses the name of a book’s author, as a shortcut b.get("author.name") can be used.

import static com.sap.cds.ql.CQL.literal;

Select.from(BOOKS)
      .columns(b -> b.title(),
               b -> b.author().name());

Literal Values

Specify values that are already known when the query is built. The literal method of CQL is used to create a literal value that can be used in the Query Builder API:

import static com.sap.cds.ql.CQL.literal;

Select.from(EMPLOYEE)
      .columns(e -> e.name())
      .where(e -> literal(50).gt(e.age());

Alternatively, the factory methods for comparison predicates directly accept Java values. The query could also be written as:

import static com.sap.cds.ql.CQL.literal;

Select.from(EMPLOYEE)
      .columns(e -> e.name())
      .where(e -> e.age().le(50));

Parameters

The param method can be statically imported from the helper class CQL. It provides an option to use a parameter marker in a query that is bound to an actual value only upon query execution. Using parameters you can execute a query multiple times with different parameter values.

Parameters are either positional or named. Using positional parameters means, the values are bound to the parameters according to their ordinal position in the query. Using named parameters means, the values are given as a map:

// positional
import static com.sap.cds.ql.CQL.param;

Select.from("bookshop.Authors")
      .columns("name").byId(param());
dataStore.execute(query, 111);

// named
import static com.sap.cds.ql.CQL.param;

Select.from("bookshop.Authors")
      .where(a -> a.firstName().eq(param("first")).and(
                  a.lastName().eq(param("last"))));

Map<String, Object> paramValues = new HashMap<>();
paramValues.put("first", "Paul");
paramValues.put("last", "Mueller");

dataStore.execute(query, paramValues);

We recommend using named parameters.

Scalar Functions

Scalar functions are values that are calculated from other values. This calculation can be executing a function on the underlying data store or applying an operation, like an addition, to its parameters. The Query Builder API supports the generic func function, as well as a number of build-in functions.

  • Generic Scalar Function

    The generic function func, creates a scalar function call that is executed by the underlying data store. The first argument, being the native query language function name, and the remaining arguments are passed on as arguments of the specified function. In the following example, the native query language count function is called on the name element. This function returns the count of number of elements with name Monika.

      import static com.sap.cds.ql.CQL.func;
  Select.from(EMPLOYEE)
    .columns(e -> e.name(), e -> func("COUNT", e.name()).as("count"))
    .where(e -> e.name().eq("Monika"));

  • To Lower

    The toLower function is a built-in string function for converting a given string value to lower case using the rules of the underlying data store.

      import static com.sap.cds.ql.CQL.toLower;
  Select.from(EMPLOYEE).columns(e -> e.name())
    .where(e -> e.name().endsWith(toLower("IKA")));

    In the following example, the toLower function is applied on the name element before applying the equals predicate.

      Select.from(EMPLOYEE).columns(e -> e.name())
    .where(e -> e.name().toLower().eq("monika"));

  • To Upper

    The toUpper function is a built-in string function for converting a given string value to upper case using the rules of the underlying data store.

      import static com.sap.cds.ql.CQL.toUpper;
  Select.from(EMPLOYEE).columns(e -> e.name())
    .where(e -> e.name().endsWith(toUpper("ika")));

    In the following example, the toUpper function is applied on the name element before applying the equals predicate.

      Select.from(EMPLOYEE).columns(e -> e.name())
    .where(e -> e.name().toUpper().eq("MONIKA"));

  • Substring

    The substring method creates an expression for substring extraction from a string value. Extract a substring from a specified starting position of either a given length or to the end of the string. The first position is zero.

      Select.from("bookshop.Authors")
    .columns(a -> a.get("name").substring(0,2))

    In the following example, the substring function is applied as part of a predicate to test whether a subset of characters matches a given string.

      Select.from("bookshop.Authors")
    .where(e -> e.get("name").substring(2).eq("ter"));

  • Plus

    Function plus creates an arithmetic expression to add a specified value to this value.

      // SELECT from Author {id + 2 as x : Integer}
  Select.from(AUTHOR)
    .columns(a -> a.id().plus(2).as("x"));

  • Minus Function minus creates an arithmetic expression to subtract a specified value with this value.

      Select.from("bookshop.Authors")
    .columns("name")
    .limit(a -> literal(3).minus(1));

  • Times

    Function times creates an arithmetic expression to multiply a specified value with this value. In the following example, p is an Integer parameter value passed when executing the query.

      Parameter<Integer> p = param("p");
  Select.from(AUTHOR)
    .where(a -> a.id().between(10, p.times(30)));

  • Divided By

    Function dividedBy creates an arithmetic expression to divide this value with the specified value.

      Select.from(AUTHOR)
    .where(a -> a.id().between(10, literal(30).dividedBy(2)));

Predicates

Predicates are expressions with a Boolean value, which are used in filters to restrict the result set or to specify a target entity set.

Comparison Operators

These comparison operators are supported:

Predicate Description Example
EQ Test if this value equals a given value. NULL values might be treated as unknown resulting in a three-valued logic as in SQL. 
Select.from("bookshop.Books")
  .where(b -> b.get("stock")
  .eq(15));
NE Test if this value is NOT equal to a given value. NULL values might be treated as unknown resulting in a three-valued logic as in SQL. 
Select.from("bookshop.Books")
  .where(b -> b.get("stock")
  .ne(25));
IS Test if this value equals a given value. NULL values are treated as any other value. 
Select.from("bookshop.Books")
  .where(b -> b.get("stock")
  .is(15));
IS NOT Test if this value is NOT equal to a given value. NULL values are treated as any other value. 
Select.from("bookshop.Books")
  .where(b -> b.get("stock")
  .isNot(25));
GT Test if this value is greater than a given value. 
Select.from("bookshop.Books")
  .where(b -> b.get("stock")
  .gt(5));
LT Test if this value is less than a given value. 
Select.from("bookshop.Books")
  .where(b -> b.get("stock")
  .lt(5));
LE Test if this value is less than or equal to a given value. 
Select.from("bookshop.Books")
  .where(b -> b.get("stock")
  .le(5));
IN Test if this value is equal to any value in a given list. 
Select.from("bookshop.Books")
  .where(b -> 
    b.get("author.name")
     .in("Poe", "Hemingway"));
BETWEEN Test if this value is between a range of values. 
Select.from("bookshop.Books")
  .where(b -> b.get("stock")
  .between(5,10));

Logical Operators

Predicates can be combined using logical operators:

Operator Description Example
AND Returns a predicate that represents a logical AND of this predicate and another. 
Select.from("bookshop.Authors")
.where(a -> 
  a.get("name").eq("Peter)
   .and(a.get("Id").eq(1)));
OR Returns a predicate that represents a logical OR of this predicate and another. 
Select.from("bookshop.Authors")
.where(a -> 
  a.get("name").eq("Peter)
   .or(a.get("Id").eq(1)));
NOT Returns a predicate that represents the logical negation of this predicate. 
Select.from("bookshop.Authors")
.where(a -> 
  not(a.get("Id").eq(3)));

Predicate Functions

These boolean-valued functions can be used in filters:

Operator Description Example
CONTAINS Test if this string value contains a given substring. 
Select.from(EMPLOYEE)
  .where(e -> e.name()
  .contains("oni"));
STARTS WITH Test if this string value starts with a given prefix. 
Select.from("bookshop.Books")
  .where(b -> b.get("title")
  .startsWith("The"));
ENDS WITH Test if this string value ends with a given suffix. 
Select.from("bookshop.Books")
  .where(b -> b.get("title")
  .endsWith("Raven"));

anyMatch/allMatch Predicate

The anyMatch and allMatch predicates are applied to an association and test if any instance/all instances of the associated entity set match a given condition.

This query selects the Authors that have written any book in the year 2000 that has a title starting with ‘X’:

import static bookshop.Bookshop_.AUTHORS;

Select.from(AUTHORS)
  .where(a -> a.books().anyMatch(b -> 
    b.year().eq(2000).and(b.title().startsWith("X"))))

The next query selects the Authors that have written only books in the year 2000:

Select.from(AUTHORS).where(a -> a.books().allMatch(b -> b.year().eq(2000)))

EXISTS Subquery

An EXISTS subquery is used to test if a subquery returns any records. Typically a subquery is correlated with the enclosing outer query. You construct an EXISTS subquery with the exists method, which takes a function that creates the subquery from a reference to the outer query. To access elements of the outer query from within the subquery, this outer reference must be used:

import static bookshop.Bookshop_.AUTHORS;
import static spaceflight.Astronautics_.ASTRONAUTS;

Select.from(AUTHORS)
  .where(auth -> auth.exists(outer -> 
      Select.from(ASTRONAUTS).where(astr -> astr.name().eq(outer.name())))
    )
  )

This query selects all authors with the name of an astronaut.

With an exists subquery, you can correlate entities that aren’t linked with associations.

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.

Copy & Modify CDS QL Statements

CDS QL statements can be copied and modified using the CQL.copy method and the CqnModifier.

Given the following query, you can construct a modified copy using CQL.copy:

// CQL: SELECT from Books where title = 'Capire'
CqnSelect query = Select.from("Books").where(b -> b.get("title").eq("Capire"));

By overriding the default implementations in CqnModifier, different parts of the CDS QL statement can be modified, for example, the where condition:

// CQL: SELECT from Books where title = 'Capire' or title = 'CAP Java SDK'

CqnSelect copy = CQL.copy(query, new CqnModifier() {
   @Override
   public Predicate where(Predicate where) {
      return where.or(CQL.get("title").eq("CAP Java SDK"));
   }
});

Using CQL.copy with the previously shown modifier, copies all parts of the query and modifies the where condition of the copy. The modifier appends or title = 'CAP Java SDK' to the original where predicate that is given as an argument in the modifier’s where method.

Similarly, other parts of the statement, such as the reference, can be modified. The following modifier takes a copy of the statement’s ref and sets a new filter year > 2000 on the root segment:

// CQL: SELECT from Books[year > 2000] where title = 'Capire'

CqnSelect copy = CQL.copy(query, new CqnModifier() {
   @Override
   public CqnStructuredTypeRef ref(StructuredTypeRef ref) {
      ref.rootSegment().filter(CQL.get("year").gt(2000));
      return ref;
   }
});
✉
Show/Hide Beta Features