Skip to content
On this page

Building CQL Statements

API to fluently build CQL statements in Java

Introduction

The CDS Query Language (CQL) statement builders allow to fluently construct CQL statements, which can be executed by CDS Services or the CDS Data Store.

Concepts

The CQL Statement Builders

Use the builder classes Select, Insert, Upsert, Update, and Delete to construct CQL statements. The following example shows a CQL query and how it's constructed with the Select builder:

sql
-- CQL
SELECT from bookshop.Books { title } where ID = 101
-- CQL
SELECT from bookshop.Books { title } where ID = 101
java
// Java CQL (dynamic)
Select.from("bookshop.Books").columns("title").byId(101);
// Java CQL (dynamic)
Select.from("bookshop.Books").columns("title").byId(101);

Instead of using strings to refer to CDS entities and elements, you can also build statements using constants and interfaces generated from the CDS model:

java
import static bookshop.Bookshop_.BOOKS;

// Java CQL (static)
Select.from(BOOKS).columns(b -> b.title()).byId(101);
import static bookshop.Bookshop_.BOOKS;

// Java CQL (static)
Select.from(BOOKS).columns(b -> b.title()).byId(101);

Using the 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.

TIP

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.

Lambda Expressions

To construct complex statements, the CQL builders leverage lambda expressions to fluently compose expressions and path expressions that are used in the statements' clauses.

sql
-- CQL
SELECT from bookshop.Books { title } where year < 2000
-- CQL
SELECT from bookshop.Books { title } where year < 2000
java
// Java CQL
Select.from(BOOKS)
    .columns(b -> b.title().as("Book"))
    .where(b -> b.year().lt(2000));
// Java CQL
Select.from(BOOKS)
    .columns(b -> b.title().as("Book"))
    .where(b -> b.year().lt(2000));

Here, the lambda expression b -> b.title().as("Book") references the element title of the entity Book b under the alias 'Book'. This aliased reference is put on the query's select list using the columns method.

The lambda expression b -> b.year().lt(2000) defines 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 selects books with authors starting with 'A'.

java
// Java CQL (static)
Select.from(BOOKS)
    .columns(b -> b.title(), b -> b.author().name().as("author"))
    .where(b -> b.author().name().startsWith("A"));

// Java CQL (dynamic)
Select.from("bookshop.Books")
    .columns(b -> b.get("title"), b -> b.get("author.name").as("author"))
    .where(b -> b.to("author").get("name").startsWith("A"));
// Java CQL (static)
Select.from(BOOKS)
    .columns(b -> b.title(), b -> b.author().name().as("author"))
    .where(b -> b.author().name().startsWith("A"));

// Java CQL (dynamic)
Select.from("bookshop.Books")
    .columns(b -> b.get("title"), b -> b.get("author.name").as("author"))
    .where(b -> b.to("author").get("name").startsWith("A"));

The CQL query accesses the name element of the Authors entity, which is reached from Books via the author association. In the dynamic CQL builders, you can follow associations and compositions using the to method or use get with a path using a dot to separate the segments.

Target Entity Sets

All [CDS Query Language (CQL)] 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:

java
import static bookshop.Bookshop_.BOOKS;

// static
Select.from(BOOKS);

// dynamic
Insert.into("bookshop.Books").entry(book);

Update.entity("bookshop.Authors").data(author);
import static bookshop.Bookshop_.BOOKS;

// static
Select.from(BOOKS);

// dynamic
Insert.into("bookshop.Books").entry(book);

Update.entity("bookshop.Authors").data(author);

The target entity set can also be defined by an entity reference, which allows using paths over associations and infix filters. Entity references can be defined inline using lambda expressions.

sql
-- CQL
SELECT from Orders[3].items { quantity, book.title as book }
-- CQL
SELECT from Orders[3].items { quantity, book.title as book }
java
// Java CQL
Select.from(ORDERS, o -> o.filter(o.id().eq(3)).items())
    .columns(i -> i.quantity(),
             i -> i.book().title().as("book"));
// Java CQL
Select.from(ORDERS, o -> o.filter(o.id().eq(3)).items())
    .columns(i -> i.quantity(),
             i -> i.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 3 via an infix filter. From this target entity set (of type OrderItems), the query selects the quantity and the title of the book. Infix filters can be defined on any path segment using the filter method, which overwrites any existing filter on the path segment. Defining an infix filter on the last path segment is equivalent to adding the filter via the statement's where method. However, inside infix filters, path expressions are not supported.

In the [CDS Query Language (CQL)] builder, the lambda expression o -> o.filter(o.id().eq(3)).items() is evaluated relative to the root entity Orders (o). All lambda expressions that occur in the other clauses of the query are relative to the target entity set OrderItems, for example, i -> i.quantity() accesses the element quantity of OrderItems.

TIP

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

Filters

Besides using infix filters in path expressions, the Select, Update, and Delete builders support filtering the target entity set via the where method. Using where is equivalent to defining an infix filter on the last segment of a path expression in the statement's from / entity clause. For statements that have both, an infix filter on the last path segment and a where filter, the resulting target filter is the conjunction (and) of the infix filter and the where filter. For simple filters, you can use byId, matching, or byParams as an alternative to where. All of these filter methods overwrite existing filters, except for infix filters.

Using where

Using the where method, you can define complex predicate expressions to compose the filter:

java
Select.from(BOOKS)
   .where(b -> b.author().name().eq("Twain")
     .and(b.title().startsWith("A").or(b.title().endsWith("Z"))));
Select.from(BOOKS)
   .where(b -> b.author().name().eq("Twain")
     .and(b.title().startsWith("A").or(b.title().endsWith("Z"))));

Using byID

To find an entity with a single key element via its key value, you can use the byId method. The following example retrieves the Author entity with key 101.

java
Select.from("bookshop.Authors").byId(101);
Select.from("bookshop.Authors").byId(101);

TIP

The byId method isn't supported for entities with compound keys.

Using matching

matching is a query-by-example style alternative to 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 filter map. The filter map can contain path keys, referring to elements of an associated entity. In the following example, bookshop.Books has a to-one association to the Author entity and the path author.name refers to the name element within the Author entity.

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

Select.from("bookshop.Books").matching(filter);
Map<String, Object> filter = new HashMap<>();
filter.put("author.name", "Edgar Allen Poe");
filter.put("stock", 0);

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

Using byParams

byParams simplifies filtering by parameters as an alternative to where and CQL.param:

java
import static bookshop.Bookshop_.BOOKS;

// using where
Select.from(BOOKS)
    .where(b -> b.title().eq(param("title"))
           .and(b.author().name().eq(param("author.name"))));

// using byParams
Select.from(BOOKS).byParams("title", "author.name");
import static bookshop.Bookshop_.BOOKS;

// using where
Select.from(BOOKS)
    .where(b -> b.title().eq(param("title"))
           .and(b.author().name().eq(param("author.name"))));

// using byParams
Select.from(BOOKS).byParams("title", "author.name");

Parameters

The CQL builders support parameters in the where clause and in infix filters for parameterized execution:

The following example selects the books of the Author with name 'Jules Verne'.

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

CqnSelect q = Select.from(BOOKS).where(b -> b.author().name().eq(param(0)));
dataStore.execute(q, "Jules Verne");
import static com.sap.cds.ql.CQL.param;

CqnSelect q = Select.from(BOOKS).where(b -> b.author().name().eq(param(0)));
dataStore.execute(q, "Jules Verne");

As an alternative, the where clauses can be constructed using the byParams method.

java
CqnSelect q = Select.from(BOOKS).byParams("author.name");
dataStore.execute(q, singletonMap("author.name", "Jules Verne"));
CqnSelect q = Select.from(BOOKS).byParams("author.name");
dataStore.execute(q, singletonMap("author.name", "Jules Verne"));

Parameterized infix filters can be constructed using the filterByParams method. Path expressions are not supported. The following example selects the books of the Author with ID 101.

java
CqnSelect q = Select.from(AUTHORS, o -> o.filterByParams("ID").books());
dataStore.execute(q, singletonMap("ID", 101));
CqnSelect q = Select.from(AUTHORS, o -> o.filterByParams("ID").books());
dataStore.execute(q, singletonMap("ID", 101));

Constant and Non-Constant Literal Values

In addition to parameters, the CQL builders also support literal values, which are already known at design time. These can be constructed using CQL.constant() for constant literals and CQL.val() for non-constant literals:

java
import static com.sap.cds.ql.CQL.val;

Select.from(BOOKS).columns(b -> b.title(), val("available").as("status"))
	.where(b -> b.stock().gt(0));
import static com.sap.cds.ql.CQL.val;

Select.from(BOOKS).columns(b -> b.title(), val("available").as("status"))
	.where(b -> b.stock().gt(0));

In case your application runs against a SQL datastore, for example SAP HANA, the CDS runtime takes literal values constructed with CQL.val(value) as a hint to bind the value to a parameter marker. The binding is handled implicitly and not explicitly as with CQL.param().

The CQL.constant(value) method gives the hint that the literal value should be handled as a constant. For SQL datastores this means that the value is rendered directly into the SQL statement.

java
import static com.sap.cds.ql.CQL.constant;

Select.from(BOOKS).columns(b -> b.title())
	.where(b -> b.cover().eq(constant("paperback")));
import static com.sap.cds.ql.CQL.constant;

Select.from(BOOKS).columns(b -> b.title())
	.where(b -> b.cover().eq(constant("paperback")));

It strongly depends on your application's domain model and business logic, which one of the methods is to be preferred. As a rule of thumb:

  • Use val() for values that change at runtime or depend on external input.
  • Only use constant() for values that don't change at runtime and don't depend on external input.

With constant literals directly rendered into the statement, a SQL datastore has better options optimizing the statement. On the other hand, using constant literals limits the data store's options to cache statements.

WARNING

Constant literals are directly rendered into SQL and therefore must not contain external input!

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:

sql
--CQL query
SELECT from bookshop.Books { title, author.name }
--CQL query
SELECT from bookshop.Books { title, author.name }
java
// Query Builder API (dynamic usage)

CqnSelect query = Select.from("bookshop.Books")
    .columns("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.

sql
--CQL query
SELECT from Orders[23].items
--CQL query
SELECT from Orders[23].items
java
// Query Builder API (static usage)
import static bookshop.Bookshop_.ORDERS;

Select.from(ORDERS, o -> o.filter(o.ID().eq(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.

sql
--CQL query
SELECT from (SELECT from Authors order by age asc limit 10) as youngestAuthors order by name
--CQL query
SELECT from (SELECT from Authors order by age asc limit 10) as youngestAuthors order by name
java
// Query Builder API
CqnSelect youngestAuthors = Select.from(AUTHORS).orderBy(a -> age()).limit(10);
Select.from(youngestAuthors).orderBy("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

By default, Select statements return all elements of the target entity. You can change this by defining a projection via the columns method of the Select builder. Elements can be addressed via their name, including path expressions such as author.name:

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

To define more complex projections and benefit from code completion, use lambda expressions:

java
// dynamic
Select.from("bookshop.Books")
    .columns(b -> b.get("title"),
             b -> b.get("author.name").as("authorName"));
// dynamic
Select.from("bookshop.Books")
    .columns(b -> b.get("title"),
             b -> b.get("author.name").as("authorName"));
java
// static
import static bookshop.Bookshop_.BOOKS;

Select.from(BOOKS)
    .columns(b -> b.title(),
             b -> b.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.

Deep Read with expand

Use expand to read deeply structured documents and entity graphs into a structured result.

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

Select.from(AUTHORS)
    .columns(a -> a.name().as("author"),
             a -> a.books().expand(
                      b -> b.title().as("book"),
                      b -> b.year());
// Java example
// using expand
import static bookshop.Bookshop_.AUTHORS;

Select.from(AUTHORS)
    .columns(a -> a.name().as("author"),
             a -> a.books().expand(
                      b -> b.title().as("book"),
                      b -> b.year());

It expands the elements title, and year of the Books entity into a substructure with the name of the association books:

json
[
  {
    "author" : "Bram Stoker",
    "books" :
    [
      {
        "title" : "Dracula",
        "year" : 1897
      },
      {
        "title" : "Miss Betty",
        "year" : 1898
      }
    ]
  }, ...
]
[
  {
    "author" : "Bram Stoker",
    "books" :
    [
      {
        "title" : "Dracula",
        "year" : 1897
      },
      {
        "title" : "Miss Betty",
        "year" : 1898
      }
    ]
  }, ...
]

To only expand entities that fulfill a certain condition, use infix filters on the association:

java
Select.from(AUTHORS)
    .columns(a -> a.name(),
             a -> a.books()
                   .filter(b -> b.year().eq(1897))
                   .expand(b -> b.title())
    .where(a -> name().in("Bram Stroker", "Edgar Allen Poe"));
Select.from(AUTHORS)
    .columns(a -> a.name(),
             a -> a.books()
                   .filter(b -> b.year().eq(1897))
                   .expand(b -> b.title())
    .where(a -> name().in("Bram Stroker", "Edgar Allen Poe"));

This query expands only books that were written in 1897:

json
[
  {
    "name" : "Bram Stoker",
    "books" : [ { "title" : "Dracula" } ]
  },
  {
    "name" : "Edgar Allen Poe",
    "books" : [ ]
  }
]
[
  {
    "name" : "Bram Stoker",
    "books" : [ { "title" : "Dracula" } ]
  },
  {
    "name" : "Edgar Allen Poe",
    "books" : [ ]
  }
]

Expands can be nested and have an alias, for example, to further expand the publisher names of the author's books:

java
Select.from(AUTHORS)
    .columns(a -> a.name(),
             a -> a.books().as("novels").expand(
                      b -> b.title(),
                      b -> b.publisher().expand(p -> p.name()));
Select.from(AUTHORS)
    .columns(a -> a.name(),
             a -> a.books().as("novels").expand(
                      b -> b.title(),
                      b -> b.publisher().expand(p -> p.name()));

Which returns a deeply structured result:

json
[
  {
    "name" : "Bram Stoker",
    "novels" :
    [
      {
        "title" : "Dracula",
        "publisher" : { "name": "Constable" }
      }, ...
    ]
  }, ...
]
[
  {
    "name" : "Bram Stoker",
    "novels" :
    [
      {
        "title" : "Dracula",
        "publisher" : { "name": "Constable" }
      }, ...
    ]
  }, ...
]

To expand all non-association elements of an associated entity, use the expand() method without parameters after the association you want to expand. For example, the following query expands all elements of the book's author:

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

To expand all first level associations of an entity, use expand() on the entity level:

java
Select.from(BOOKS).columns(b -> b.expand());
Select.from(BOOKS).columns(b -> b.expand());
Optimized Expand Execution

For to-one expands:

  • The expand item list mustn't contain any literal value.
  • The expand item list mustn't contain expression.

For to-many expands:

  • The on condition of the association must only use equality predicates and conjunction (AND).
  • The from clause isn't a subquery.
  • The where clause doesn't contain path expressions.
  • The query doesn't use groupBy or distinct.
  • The columns/items clause must contain at least one element reference.

In case the default query optimization leads to issues, annotate the association with @cds.java.expand: {using: 'parent-keys'} to fall back to the unoptimized expand execution and make sure the parent entity has all key elements exposed.

Flattened Results with inline

To flatten deeply structured documents or include elements of associated entities into a flat result, you can use inline as a short notation for using multiple paths.

java
// 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().year());

// using inline
Select.from(AUTHORS)
    .columns(a -> a.name(),
             a -> a.books().inline(
                      b -> b.title().as("book"),
                      b -> b.year());
// 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().year());

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

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

json
[
  {
    "name" : "Bram Stoker",
    "book" : "Dracula",
    "year" : 1897
  },
  {
    "name" : "Bram Stoker",
    "book" : "Miss Betty",
    "year" : 1898
  }
]
[
  {
    "name" : "Bram Stoker",
    "book" : "Dracula",
    "year" : 1897
  },
  {
    "name" : "Bram Stoker",
    "book" : "Miss Betty",
    "year" : 1898
  }
]

Managed Associations on the Select List

To select the key elements of a managed to-one association's target entity, simply put the association on the select list. This will return the target key elements as structured result:

java
// dynamic
Select.from("bookshop.Books")
      .columns(b -> b.get("author"));

// static
import static bookshop.Bookshop_.BOOKS;

CqnSelect q = Select.from(BOOKS)
    .columns(b -> b.author());

Row book = dataStore.execute(q).single();
Object authorId = book.get("author.Id"); // path access
// dynamic
Select.from("bookshop.Books")
      .columns(b -> b.get("author"));

// static
import static bookshop.Bookshop_.BOOKS;

CqnSelect q = Select.from(BOOKS)
    .columns(b -> b.author());

Row book = dataStore.execute(q).single();
Object authorId = book.get("author.Id"); // path access

TIP

Only to-one associations that are mapped via the primary key elements of the target entity are supported on the select list. The execution is optimized and gives no guarantee that the target entity exists, if this is required use expand or enable integrity constraints on the database.

Filtering and Searching

The Select builder supports filtering the target entity set via where, byId, matching and byParams. In contrast to infix filters, where filters of Select statements support path expressions. Additionally, Select supports search clauses.

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, refer to Search Capabilities.

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

cds
entity Book {
  key ID : Integer;
  name   : String;
  title  : String;
}
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
@cds.search: {title}
entity Book {
  key ID : Integer;
  name   : String;
  title  : String;
}
@cds.search: {title}
entity Book {
  key ID : Integer;
  name   : String;
  title  : String;
}
  1. Construct queries with search

Let's consider the following Book entity once again:

cds
entity Book {
  key ID : Integer;
  name   : String;
  title  : String;
}
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".

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

Select.from("bookshop.Books")
        .columns("id", "name")
        .search("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".

java
// 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")));
// 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 Query Language (CQL)] expressions to compose the query's filter:

java
Select.from("bookshop.Books")
	.where(b -> b.get("ID").eq(251).or(
              b.get("title").startsWith("Wuth")));
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:

IDNAME
100Smith
101Miller
102Smith
103Hugo
104Smith

Group By

The groupBy clause groups by one or more elements and usually involves aggregate functions, such as count, countDistinct, 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.

java
import com.sap.cds.ql.CQL;

Select.from("bookshop.Authors")
	.columns(c -> c.get("name"), c -> CQL.count(c.get("name")).as("count"))
	.groupBy(g -> g.get("name"));
import com.sap.cds.ql.CQL;

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

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

namecount
Smith3
Miller1
Hugo1

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:

java
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", c.get("name")).gt(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", c.get("name")).gt(2));

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

namecount
Smith3

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.

java
Select.from("bookshop.Books")
    .columns(c -> c.get("ID"), c -> c.get("title"))
    .orderBy(c -> c.get("ID").desc(), c -> c.get("title").asc());
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's locale is passed to the database, resulting in locale-specific sorting of string-based columns.

By default, null values come before non-null values when sorting in ascending order and after non-null values when sorting in descending order. Use the ascNullsLast and descNullsFirst methods if you need to change this behavior.

The following query would sort null values for the element nickname last:

java
Select.from("bookshop.Person")
    .orderBy(p -> p.get("name").asc(), p -> c.get("nickname").ascNullsLast());
Select.from("bookshop.Person")
    .orderBy(p -> p.get("name").asc(), p -> c.get("nickname").ascNullsLast());

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

namenickname
WilliamBill
Williamnull

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:

java
Select.from("bookshop.Books").limit(10, 20);
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.

TIP

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.

Pessimistic Locking

Use the lock() method to enforce Pessimistic Locking.

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

java
Select.from("bookshop.Books").byId(1).lock(5);
...
Update.entity("bookshop.Books").data("price", 18).byId(1);
Select.from("bookshop.Books").byId(1).lock(5);
...
Update.entity("bookshop.Books").data("price", 18).byId(1);

To set a shared (read) lock specify the lock mode SHARED in the lock method:

java
import static com.sap.cds.ql.cqn.CqnLock.Mode.SHARED;

Select.from("bookshop.Books").byId(1).lock(SHARED);
import static com.sap.cds.ql.cqn.CqnLock.Mode.SHARED;

Select.from("bookshop.Books").byId(1).lock(SHARED);

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:

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

CqnInsert insert = Insert.into("bookshop.Books").entry(book);
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:

java
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);
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);

Single Insert

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

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

CqnInsert insert = Insert.into("bookshop.Books").entry(book);
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:

java
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);
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);

TIP

A bulk insert can also perform deep inserts.

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:

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

Find this source also in cap/samples.

Java:

java
import static bookshop.Bookshop_.ORDERS;

Map<String, Object> item;
item.put("ID", 1);
item.put("book_ID", 101);
item.put("quantity", 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);
import static bookshop.Bookshop_.ORDERS;

Map<String, Object> item;
item.put("ID", 1);
item.put("book_ID", 101);
item.put("quantity", 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);

TIP

On SQL data stores the execution order of the generated insert statements is parent first.

Upsert

Upsert updates existing entity records from the given data or inserts new ones if they don't exist in the database. Upsert statements are created with the Upsert builder and are translated into DB native upsert statements by the CAP runtime whenever possible.

The main use case of upsert is data replication.

If upsert data is incomplete only the given values are updated or inserted, which means the Upsert statement has "PATCH semantics".

WARNING

Even if an entity doesn't exist in the database:
→ Upsert is not equivalent to Insert.

The following actions are not performed on Upsert:

  • UUID key values are not generated.
  • The @cds.on.insert annotation is not handled.
  • Elements are not initialized with default values if the element's value is not given.
  • Generic CAP handlers, such as audit logging, are not invoked.

Upsert statements don't have a where clause. Just as with bulk Updates and Inserts, the key values of the entity that is upserted are extracted from the data.

TIP

The upsert data must contain all key elements of the entity.

Single Upsert

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

java
import static bookshop.Bookshop_.BOOKS;
import bookshop.Books;

Books book = Books.create();
book.setId(101);
book.setTitle("CAP for Beginners");

CqnUpsert upsert = Upsert.into(BOOKS).entry(book);
import static bookshop.Bookshop_.BOOKS;
import bookshop.Books;

Books book = Books.create();
book.setId(101);
book.setTitle("CAP for Beginners");

CqnUpsert upsert = Upsert.into(BOOKS).entry(book);

Bulk Upsert

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

java
import static bookshop.Bookshop_.BOOKS;
import bookshop.Books;

Books b1 = Books.create(101);
b1.setTitle("Odyssey");

Books b2 = Books.create(103);
b2.put("title", "Ulysses");

List<Books> data = Arrays.asList(b1, b2);

CqnUpsert upsert = Upsert.into(BOOKS).entries(data);
import static bookshop.Bookshop_.BOOKS;
import bookshop.Books;

Books b1 = Books.create(101);
b1.setTitle("Odyssey");

Books b2 = Books.create(103);
b2.put("title", "Ulysses");

List<Books> data = Arrays.asList(b1, b2);

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

TIP

Bulk upserts with entries updating/inserting the same set of elements can be executed more efficiently than individual upsert operations and bulk upserts with heterogeneous data.

Deep Upsert

Upsert can operate on deep document structures modeled via compositions, such as an Order with many OrderItems. Such a Deep Upsert is similar to Deep Update, but it creates the root entity if it doesn't exist and comes with some limitations as already mentioned.

The full set and delta representation for to-many compositions are supported as well.

WARNING

Upsert doesn't allow changing the key of a child of a composition of one.

Update

Use the Update statement to update existing entities with new data. The update data can be partial (patch semantics), elements without update values keep their old value, except for elements annotated with @cds.on.update, which are updated with the annotation value.

Depending on the filter condition, the Update can target individual or multiple entity records.

TIP

Check the row count of the update result to get the number of updated records. It is 0 if no entity matched the filter condition.

Use the Update builder to create an update statement.

Updating Individual Entities

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

In the following example, the update target is an entity of the static model. The update data is provided as a map to the data method, using accessor interfaces to construct the data in a typed way. The filter condition of the update is constructed from the key values in the update data:

java
import static bookshop.Bookshop_.BOOKS;
import bookshop.Books;

Books book = Books.create();
book.setId(100); // key value filter in data
book.setTitle("CAP Matters");

CqnUpdate update = Update.entity(BOOKS).data(book);
import static bookshop.Bookshop_.BOOKS;
import bookshop.Books;

Books book = Books.create();
book.setId(100); // key value filter in data
book.setTitle("CAP Matters");

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

As an alternative to adding the key values to the data, you can use the byId filter for entities with a single key element or matching for entities with compound key.

java
Update.entity(BOOKS)
   .data("title", "CAP Matters").byId(100);
Update.entity(BOOKS)
   .data("title", "CAP Matters").byId(100);

Furthermore, you can use filters in path expressions to specify the update target:

java
Update.entity(BOOKS, b -> b.matching(Books.create(100)))
   .data("title", "CAP Matters");
Update.entity(BOOKS, b -> b.matching(Books.create(100)))
   .data("title", "CAP Matters");

Deep Update

Use deep updates to update document structures. A document structure comprises a single root entity and one or multiple related entities that are linked via compositions into a contained-in-relationship. Linked entities can have compositions to other entities, which become also part of the document structure.

By default, only target entities of compositions are updated in deep updates. Nested data for managed to-one associations is used only to set the reference to the given target entity. This can be changed via the @cascade annotation.

For to-many compositions there are two ways to represent changes in the nested entities of a structured document: full set and delta. In contrast to full set representation which describes the target state of the entities explicitly, a change request with delta payload describes only the differences that need to be applied to the structured document to match the target state. For instance, in deltas, entities that are not included remain untouched, whereas in full set representation they are deleted.

Full Set Representation

In the update data, nested entity collections in full set representation have to be complete. All pre-existing entities that are not contained in the collection are deleted. The full set representation requires the runtime to execute additional queries to determine which entities to delete and is therefore not as efficient to process as the delta representation.

Given the following Order:

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

Do a deep update Update.entity(ORDERS).data(order) with the following order data:

json
{
   "OrderNo": "1000",
   "status": "in process",
   "items": [{"Id":1, "quantity":2},
             {"Id":4, "book":{"ID":400}, "quantity":4}]
}
{
   "OrderNo": "1000",
   "status": "in process",
   "items": [{"Id":1, "quantity":2},
             {"Id":4, "book":{"ID":400}, "quantity":4}]
}

Constructed using CdsData, CdsList and the generated accessor interfaces.

See the result of the updated Order:

json
{
   "OrderNo": "1000",
   "status": "in process",
   "createdAt": "2020-03-01T12:21:34.000Z",
   "items": [{"Id":1, "book":{"ID":100}, "quantity":2},
             {"Id":4, "book":{"ID":400}, "quantity":4}]
}
{
   "OrderNo": "1000",
   "status": "in process",
   "createdAt": "2020-03-01T12:21:34.000Z",
   "items": [{"Id":1, "book":{"ID":100}, "quantity":2},
             {"Id":4, "book":{"ID":400}, "quantity":4}]
}
  • Order status changed to "in process"
  • Item 1 quantity changed to 2
  • Items 2 and 3 removed from items and deleted
  • Item 4 created and added to items

Delta Representation

In delta representation, nested entity collections in the update data can be partial: the runtime only processes entities that are contained in the collection but entities that aren't contained remain untouched. Entities that shall be removed need to be included in the list and explicitly marked for removal.

Using the same sample Order as in the previous full-set chapter, do a deep delta update with the following update data:

java
import static com.sap.cds.CdsList.delta;

Order order = Order.create(1000);
order.setStatus("in process");
OrderItem item1 = OrderItem.create(1);
item1.setQuantity(2);
OrderItem item2 = OrderItem.create(2);
OrderItem item4 = OrderItem.create(4);
item4.setBook(Book.create(400));
item4.setQuantity(4);

// items delta with order item 2 marked for removal
order.setItems(delta(item1, item2.forRemoval(), item4));

Update.entity(ORDER).data(order);
import static com.sap.cds.CdsList.delta;

Order order = Order.create(1000);
order.setStatus("in process");
OrderItem item1 = OrderItem.create(1);
item1.setQuantity(2);
OrderItem item2 = OrderItem.create(2);
OrderItem item4 = OrderItem.create(4);
item4.setBook(Book.create(400));
item4.setQuantity(4);

// items delta with order item 2 marked for removal
order.setItems(delta(item1, item2.forRemoval(), item4));

Update.entity(ORDER).data(order);

Create delta collections via CdsList and CdsData.

The deep update with order items in delta representation has similar effects as the update with items in full set representation. The only difference is that OrderItem 3 is not deleted.

Bulk Update: Update Multiple Entity Records with Individual Data

To update multiple entity records with individual update data, use the entries method and provide the key values of the entities in the data. The individual update entries can be deep. The following example illustrates this, using the generated accessor interfaces. The statement updates the status of order 1 and 2 and the header comment of order 3:

java
Orders o1 = Orders.create(1);
o1.setStatus("canceled");

Orders o2 = Orders.create(2);
o2.setStatus("in process");

Orders o3 = Orders.create(3);
o3.put("header.comment", "Deliver with Order 2");

List<Orders> orders = Arrays.asList(o1, o2, o3);
CqnUpdate update = Update.entity(ORDERS).entries(orders);
Orders o1 = Orders.create(1);
o1.setStatus("canceled");

Orders o2 = Orders.create(2);
o2.setStatus("in process");

Orders o3 = Orders.create(3);
o3.put("header.comment", "Deliver with Order 2");

List<Orders> orders = Arrays.asList(o1, o2, o3);
CqnUpdate update = Update.entity(ORDERS).entries(orders);

TIP

In general, a bulk update can be executed more efficiently than multiple individual updates, especially if all bulk update entries update the same set of elements.

Update Multiple Entity Records with the same Data

To update multiple entity records with the same update data, use searched or batch updates.

Searched Update

Use the where clause or matching to update all entities that match the filter with the same update data. In the following example, the stock of all books with the title containing CAP is set to 100:

java
Update.entity(BOOKS).data("stock", 100)
   .where(b -> b.title().contains("CAP"));
Update.entity(BOOKS).data("stock", 100)
   .where(b -> b.title().contains("CAP"));

Parameterized Batch Update

Use CQL.param in the where clause or byParams to create a parameterized update statement to execute the statement with one or multiple parameter value sets.

java
// using where
CqnUpdate update = Update.entity(BOOKS).data("stock", 0)
    .where(b -> b.title().eq(CQL.param("title"))
           .and(b.author().name().eq(CQL.param("author.name"))));

// using byParams
CqnUpdate update = Update.entity(BOOKS).data("stock", 0)
    .byParams("title", "author.name");

Map<String, Object> paramSet1 = new HashMap<>();
paramSet1.put("author.name", "Victor Hugo");
paramSet1.put("title", "Les Misérables");
Map<String, Object> paramSet2 = new HashMap<>();
paramSet2.put("author.name", "Emily Brontë");
paramSet2.put("title", "Wuthering Heights");

Result result = service.run(update, asList(paramSet1, paramSet2));
// using where
CqnUpdate update = Update.entity(BOOKS).data("stock", 0)
    .where(b -> b.title().eq(CQL.param("title"))
           .and(b.author().name().eq(CQL.param("author.name"))));

// using byParams
CqnUpdate update = Update.entity(BOOKS).data("stock", 0)
    .byParams("title", "author.name");

Map<String, Object> paramSet1 = new HashMap<>();
paramSet1.put("author.name", "Victor Hugo");
paramSet1.put("title", "Les Misérables");
Map<String, Object> paramSet2 = new HashMap<>();
paramSet2.put("author.name", "Emily Brontë");
paramSet2.put("title", "Wuthering Heights");

Result result = service.run(update, asList(paramSet1, paramSet2));

Delete

The Delete operation can be constructed as follows:

cds
// CDS model
entity Orders {
  key OrderNo : String;
  Items       : Composition of many OrderItems on Items.parent = $self;
  ...
}
entity OrderItems {
  book   : Association to Books;
  ...
}
// CDS model
entity Orders {
  key OrderNo : String;
  Items       : Composition of many OrderItems on Items.parent = $self;
  ...
}
entity OrderItems {
  book   : Association to Books;
  ...
}
java
// dynamic
CqnDelete delete = Delete.from("my.bookshop.Orders")
    .where(b -> b.get("OrderNo").eq(1000));
// dynamic
CqnDelete delete = Delete.from("my.bookshop.Orders")
    .where(b -> b.get("OrderNo").eq(1000));
java
// static
import static bookshop.Bookshop_.ORDERS;

CqnDelete delete = Delete.from(ORDERS)
    .where(b -> b.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.

Using matching

As an alternative to where, you can use matching to define the delete filter based on a map. In the following example, the entity bookshop.Article has a composite primary key made up of ID and journalID.

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

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

// using matching
CqnDelete delete = Delete.from("bookshop.Article").matching(params);

// using where
CqnDelete delete = Delete.from("bookshop.Article")
	.where(t -> t.get("ID").eq(param("ID"))
	.and(t.get("journalID").eq(101)));

// execution
Map<String, Object> row1 = singletonMap("ID", 1);
Map<String, Object> row2 = singletonMap("ID", 2);
dataStore.execute(delete, asList(row1, row2));
import static com.sap.cds.ql.CQL.param;

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

// using matching
CqnDelete delete = Delete.from("bookshop.Article").matching(params);

// using where
CqnDelete delete = Delete.from("bookshop.Article")
	.where(t -> t.get("ID").eq(param("ID"))
	.and(t.get("journalID").eq(101)));

// execution
Map<String, Object> row1 = singletonMap("ID", 1);
Map<String, Object> row2 = singletonMap("ID", 2);
dataStore.execute(delete, asList(row1, row2));

Using byParams

To delete multiple records of an entity you can use byParams as an alternative to parameters in matching/where. The records are then identified by the parameter values, which are given on statement execution:

java
import static bookshop.Bookshop_.BOOKS;

// using where
Delete.from(BOOKS)
    .where(b -> b.title().eq(param("title"))
           .and(b.author().name().eq(param("author.name"))));

// using byParams
Delete.from(BOOKS).byParams("title", "author.name");
import static bookshop.Bookshop_.BOOKS;

// using where
Delete.from(BOOKS)
    .where(b -> b.title().eq(param("title"))
           .and(b.author().name().eq(param("author.name"))));

// using byParams
Delete.from(BOOKS).byParams("title", "author.name");

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 CQL 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:

java
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");
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");

You can also get entity references from the result of a CDS QL statement to address an entity via its key values in other statements.

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.

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

Select.from(BOOKS)
      .columns(b -> b.title(),
               b -> b.author().name());
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 val method of CQL is used to create a literal value that can be used in the Query Builder API:

java
import static com.sap.cds.ql.CQL.val;

Select.from(EMPLOYEE)
      .columns(e -> e.name())
      .where(e -> val(50).gt(e.age());
import static com.sap.cds.ql.CQL.val;

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

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

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

Use CQL.constant if the literal value shall be treated as constant.


List Values

Combine multiple values with CQL.list to a list value (row value), which you can use in comparisons.

For example, the following query returns all sales after Q2/2012:

java
import static com.sap.cds.ql.CQL.list;
import static com.sap.cds.ql.CQL.get;
import static com.sap.cds.ql.CQL.val;
import static com.sap.cds.ql.CQL.comparison;

...

CqnListValue props = list(get("year"), get("quarter"));
CqnListValue vals  = list(val(2012), val(2));
CqnSelect q = Select.from(SALES).where(comparison(props, GT, vals));
import static com.sap.cds.ql.CQL.list;
import static com.sap.cds.ql.CQL.get;
import static com.sap.cds.ql.CQL.val;
import static com.sap.cds.ql.CQL.comparison;

...

CqnListValue props = list(get("year"), get("quarter"));
CqnListValue vals  = list(val(2012), val(2));
CqnSelect q = Select.from(SALES).where(comparison(props, GT, vals));

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 indexed or named. Using indexed parameters means, the values are bound to the parameters according to their index. Using named parameters means, the values are given as a map:

java
// indexed
import static com.sap.cds.ql.CQL.param;

Select.from("bookshop.Authors")
      .where(a -> a.firstName().eq(param(0)).and(
                  a.lastName().eq(param(1))));
dataStore.execute(query, "Paul", "Mueller");
// indexed
import static com.sap.cds.ql.CQL.param;

Select.from("bookshop.Authors")
      .where(a -> a.firstName().eq(param(0)).and(
                  a.lastName().eq(param(1))));
dataStore.execute(query, "Paul", "Mueller");
java
// 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);
// 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);

TIP

When using named parameters, Update and Delete statements can be executed as batch) with multiple parameter sets.

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.

    java
    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"));
    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.

    java
    import static com.sap.cds.ql.CQL.toLower;
    Select.from(EMPLOYEE).columns(e -> e.name())
      .where(e -> e.name().endsWith(toLower("IKA")));
    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.

    java
    Select.from(EMPLOYEE).columns(e -> e.name())
      .where(e -> e.name().toLower().eq("monika"));
    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.

    java
    import static com.sap.cds.ql.CQL.toUpper;
    Select.from(EMPLOYEE).columns(e -> e.name())
      .where(e -> e.name().endsWith(toUpper("ika")));
    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.

    java
    Select.from(EMPLOYEE).columns(e -> e.name())
      .where(e -> e.name().toUpper().eq("MONIKA"));
    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.

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

    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.

    java
    Select.from("bookshop.Authors")
      .where(e -> e.get("name").substring(2).eq("ter"));
    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.

    java
    // SELECT from Author {id + 2 as x : Integer}
    Select.from(AUTHOR)
      .columns(a -> a.id().plus(2).as("x"));
    // 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.

    java
    Select.from("bookshop.Authors")
      .columns("name")
      .limit(a -> literal(3).minus(1));
    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.

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

    java
    Select.from(AUTHOR)
      .where(a -> a.id().between(10, literal(30).dividedBy(2)));
    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 filter condition. They are supported in filter conditions of Select, Update and Delete statements.

This query selects the Authors that have written any book in the year 2000 that is published by a publisher starting with 'X':

java
import static bookshop.Bookshop_.AUTHORS;

Select.from(AUTHORS)
  .where(a -> a.books().anyMatch(b ->
    b.year().eq(2000).and(b.publisher().name().startsWith("X"))));
import static bookshop.Bookshop_.AUTHORS;

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

The next statement deletes all Authors that have published all their books with publisher 'A':

java
Delete.from(AUTHORS).where(a -> a.books().allMatch(b -> b.publisher().name().eq("A")));
Delete.from(AUTHORS).where(a -> a.books().allMatch(b -> b.publisher().name().eq("A")));

The reference, to which anyMatch/allMatch is applied, may navigate multiple path segments. The following query selects all authors, for which the publisher of all books is named "CAP Publications":

java
Select.from(AUTHORS).where(a -> a.books().publisher().allMatch(p -> p.name().eq("CAP Publications")));
Select.from(AUTHORS).where(a -> a.books().publisher().allMatch(p -> p.name().eq("CAP Publications")));

This is equivalent to

java
Select.from(AUTHORS).where(a -> a.books().allMatch(b -> b.publisher().name().eq("CAP Publications")));
Select.from(AUTHORS).where(a -> a.books().allMatch(b -> b.publisher().name().eq("CAP Publications")));

Like in the previous example, a reference used in a match predicate filter may navigate to-one associations. Nested match predicates need to be used, if you want to express a condition in a match predicate filter on a reference that navigates to-many associations. The following example selects authors that have written a book where the word "unicorn" occurs on all pages:

java
Select.from(AUTHORS).where(a -> a.books().anyMatch(
    b -> b.pages().allMatch(p ->
        p.text().contains("unicorn"))));
Select.from(AUTHORS).where(a -> a.books().anyMatch(
    b -> b.pages().allMatch(p ->
        p.text().contains("unicorn"))));

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:

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

// fluent style
Select.from(AUTHORS)
  .where(author -> author.exists($outer ->
      Select.from(ASTRONAUTS).where(astro -> astro.name().eq($outer.name())))
    )
  )
import static bookshop.Bookshop_.AUTHORS;
import static spaceflight.Astronautics_.ASTRONAUTS;

// fluent style
Select.from(AUTHORS)
  .where(author -> author.exists($outer ->
      Select.from(ASTRONAUTS).where(astro -> astro.name().eq($outer.name())))
    )
  )

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

TIP

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

When using the tree-style API the outer query is addressed by the special reference name "$outer":

java
// tree style
CqnSelect subquery =
  Select.from("Astronauts")
        .where(a -> a.get("name").eq(CQL.get("$outer.name")));
Select.from("Authors").where(CQL.exists(subquery));
// tree style
CqnSelect subquery =
  Select.from("Astronauts")
        .where(a -> a.get("name").eq(CQL.get("$outer.name")));
Select.from("Authors").where(CQL.exists(subquery));

Parsing CQN

CQL queries can also be constructed from a CQN string*:

java
String cqnQuery = "{'SELECT': {'from': {'ref': ['my.bookshop.Books']},
    'where': [{'ref': ['title']}, '=', {'val': 'Capire'}]}}";
CqnSelect query = Select.cqn(cqnQuery);
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:

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

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

CQL Expression Trees

As an alternative to fluent API the CQL statement can be built, copied, and modified using CQL Interface, which allows to build and reuse the parts of the statement.

Composing Predicates

As opposed to fluent API it's possible to build the queries in a tree-style. Consider the following example:

java
// CQL: SELECT from Books where year >= 2000 and year <= 2010

                        AND
                         |
               +---------+---------+
               |                   |
               =>                 <=
               |                   |
          +----+----+         +----+----+
          |         |         |         |
        year       2000      year     2010

import static com.sap.cds.ql.CQL.*;
import com.sap.cds.sql.cqn.CqnComparisonPredicate;

CqnValue year = get("year");
CqnPredicate filter = and(comparison(year, Operator.GE, val(2000)), comparison(year, Operator.LE, val(2010)));
// CQL: SELECT from Books where year >= 2000 and year <= 2010

                        AND
                         |
               +---------+---------+
               |                   |
               =>                 <=
               |                   |
          +----+----+         +----+----+
          |         |         |         |
        year       2000      year     2010

import static com.sap.cds.ql.CQL.*;
import com.sap.cds.sql.cqn.CqnComparisonPredicate;

CqnValue year = get("year");
CqnPredicate filter = and(comparison(year, Operator.GE, val(2000)), comparison(year, Operator.LE, val(2010)));

In the previous example using the CQL.and, a predicate limiting the year between 2000 and 2010 was built.

Using CQL Interface can be handy when the part of the statement should be built on the fly based on some condition. The following example demonstrates that, showing the usage of a CQL.in expression:

java
// CQL: SELECT from Books where year >= 2000 and year <= 2010
//                         OR
//      SELECT from Books where year in (2000, 2001, ...)

List<Integer> years = ...;
List<Value<Integer>> yearValues = years.stream().map(y -> val(y)).collect(toList());
CqnElementRef year = CQL.get("year");

CqnPredicate filter;
if (years.isEmpty()) {
   filter = and(comparison(year, Operator.GE, val(2000)), comparison(year, Operator.LE, val(2010)));
} else {
   filter = CQL.in(year, yearValues);
}

Select.from("bookshop.Books").where(filter);
// CQL: SELECT from Books where year >= 2000 and year <= 2010
//                         OR
//      SELECT from Books where year in (2000, 2001, ...)

List<Integer> years = ...;
List<Value<Integer>> yearValues = years.stream().map(y -> val(y)).collect(toList());
CqnElementRef year = CQL.get("year");

CqnPredicate filter;
if (years.isEmpty()) {
   filter = and(comparison(year, Operator.GE, val(2000)), comparison(year, Operator.LE, val(2010)));
} else {
   filter = CQL.in(year, yearValues);
}

Select.from("bookshop.Books").where(filter);

Connecting Streams of Predicates

You can leverage the Java Stream API to connect a stream of predicates with AND or OR using the Collectors withAnd or withOr. In this example we build a predicate that tests if a Person matches any first name/last name pair in a list:

java
List<Name> names = ...
CqnPredicate filter =
  names.stream()
       .map(n -> CQL.and(
           CQL.get("firstName").eq(n.first()),
           CQL.get("lastName").eq(n.last())))
       .collect(CQL.withOr());
List<Name> names = ...
CqnPredicate filter =
  names.stream()
       .map(n -> CQL.and(
           CQL.get("firstName").eq(n.first()),
           CQL.get("lastName").eq(n.last())))
       .collect(CQL.withOr());

Working with Select List Items

In addition to CQL.get, which creates a reference to a particular element, it's also possible to reference all elements using CQL.star method and use the expands as well. The next example demonstrates how to select all elements of Book and expand elements of associated Author of the book with CQL.to(...).expand:

java
// SELECT from Books {*, author {*}}

Expand<?> authorItems = CQL.to("author").expand();
Select.from("bookshop.Books").columns(CQL.star(), authorItems);
// SELECT from Books {*, author {*}}

Expand<?> authorItems = CQL.to("author").expand();
Select.from("bookshop.Books").columns(CQL.star(), authorItems);

Using Functions and Arithmetic Expressions

CQL Interface provides multiple well-known functions such as: min, max, average, and so on. The following example shows how to use the function call to query the min and max stock of the Books:

java
// CQL: SELECT from Books { MIN(stock) as minStock, MAX(stock) as maxStock }

CqnElementRef stock = CQL.get("stock");
Select.from("bookshop.Books").columns(
   CQL.min(stock).as("minStock"),
   CQL.max(stock).as("maxStock"));
// CQL: SELECT from Books { MIN(stock) as minStock, MAX(stock) as maxStock }

CqnElementRef stock = CQL.get("stock");
Select.from("bookshop.Books").columns(
   CQL.min(stock).as("minStock"),
   CQL.max(stock).as("maxStock"));

In addition to that it's also possible to build a custom function using CQL.func:

java
// CQL: SELECT from Books { LENGTH(title) as titleLength }

CqnElementRef title = CQL.get("title");
Select.from("bookshop.Books").columns(func("LENGTH", title).as("titleLength"));
// CQL: SELECT from Books { LENGTH(title) as titleLength }

CqnElementRef title = CQL.get("title");
Select.from("bookshop.Books").columns(func("LENGTH", title).as("titleLength"));

Other than CQL.func, which returns a value, the CQL.booleanFunc constructs the function, which returns a predicate and thus can be used in where clause of a query. In the following example, SAP HANA function CONTAINS is used to execute fuzzy search on the column of the entity:

java
Select.from("bookshop.Books")
   .where(e -> booleanFunc("CONTAINS",
            Arrays.asList(CQL.get(Books.TITLE).asRef(), val("Wuthering"), plain("FUZZY(0.5)"))));
Select.from("bookshop.Books")
   .where(e -> booleanFunc("CONTAINS",
            Arrays.asList(CQL.get(Books.TITLE).asRef(), val("Wuthering"), plain("FUZZY(0.5)"))));

Assume the Book has an element price : Decimal. One can calculate the discount price by subtracting the fixed value. This can be done using CQL.expression:

java
// CQL: SELECT from Books { *, price - 5 as discountPrice }

CqnSelectListValue discountPrice = CQL.expression(
   CQL.get("price"), Operator.SUB, CQL.val(5)).as("discountPrice"); // Price reduced by 5
Select.from("bookshop.Books").columns(CQL.star(), discountPrice);
// CQL: SELECT from Books { *, price - 5 as discountPrice }

CqnSelectListValue discountPrice = CQL.expression(
   CQL.get("price"), Operator.SUB, CQL.val(5)).as("discountPrice"); // Price reduced by 5
Select.from("bookshop.Books").columns(CQL.star(), discountPrice);

When using custom functions or expressions, you sometimes want to ensure that the return value is typed with a specific CDS type. You can use a CDL cast for this, by leveraging the type method. By default, values returned by custom functions or expressions are not typed. If no explicit CDL cast is applied, the representation of the return value in Java is dependent on the database and its JDBC driver implementation. In the following example, the result of the ADD_SECONDS function is ensured to be represented as a CDS Timestamp type. This ensures the return value is typed as an Instant in Java.

java
// CQL: SELECT from Books { ADD_SECONDS(modifiedAt, 30) as addedSeconds : Timestamp }

CqnElementRef modified = CQL.get("modifiedAt");
Select.from("bookshop.Books").columns(
   CQL.func("ADD_SECONDS", modified, CQL.constant(30))
      .type(CdsBaseType.TIMESTAMP).as("addedSeconds"));
// CQL: SELECT from Books { ADD_SECONDS(modifiedAt, 30) as addedSeconds : Timestamp }

CqnElementRef modified = CQL.get("modifiedAt");
Select.from("bookshop.Books").columns(
   CQL.func("ADD_SECONDS", modified, CQL.constant(30))
      .type(CdsBaseType.TIMESTAMP).as("addedSeconds"));

Copying & Modifying CQL Statements

CQL statements can be copied and modified using the CQL.copy method and the CQN Modifier.

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

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

By overriding the default implementations of the CQN Modifier, different parts of the CQL statement can be modified.

Modify the Where Clause

java
import com.sap.cds.ql.CQL;

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

CqnSelect copy = CQL.copy(query, new Modifier() {
   @Override
   public Predicate where(Predicate where) {
      return where.or(CQL.get("title").eq("CAP Java SDK"));
   }
});
import com.sap.cds.ql.CQL;

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

CqnSelect copy = CQL.copy(query, new Modifier() {
   @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.

To modify all occurrences of a comparison predicate, the comparison method can be used. The following modifier replaces the value of the title comparison with 'CAP'.

java
// copy: SELECT from Books where title = 'CAP'

CqnSelect copy = CQL.copy(query, new Modifier() {
   @Override
   public Predicate comparison(Value<?> lhs, Operator op, Value<?> rhs) {
      if (lhs.isRef() && lhs.asRef().displayName().equals("title")) {
         rhs = CQL.val("CAP");
      }
      return CQL.comparison(lhs, op, rhs);
   };
});
// copy: SELECT from Books where title = 'CAP'

CqnSelect copy = CQL.copy(query, new Modifier() {
   @Override
   public Predicate comparison(Value<?> lhs, Operator op, Value<?> rhs) {
      if (lhs.isRef() && lhs.asRef().displayName().equals("title")) {
         rhs = CQL.val("CAP");
      }
      return CQL.comparison(lhs, op, rhs);
   };
});

Modify a ref

Other parts of the statement, such as the ref or its segments can't be modified directly. Instead a new ref must be created. The following modifier makes a copy of the statement's ref and sets a new filter year > 2000 on the root segment:

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

CqnSelect copy = CQL.copy(query, new Modifier() {
   @Override
   public CqnStructuredTypeRef ref(StructuredTypeRef ref) {
      List<? extends Segment> segments = new ArrayList<>(ref.segments());
      Segment root = CQL.refSegment(ref.firstSegment(), CQL.get("year").gt(2000));
      segments.set(0, root);
      return CQL.to(segments).asRef();
   }
});
// copy: SELECT from Books[year > 2000] where title = 'Capire'

CqnSelect copy = CQL.copy(query, new Modifier() {
   @Override
   public CqnStructuredTypeRef ref(StructuredTypeRef ref) {
      List<? extends Segment> segments = new ArrayList<>(ref.segments());
      Segment root = CQL.refSegment(ref.firstSegment(), CQL.get("year").gt(2000));
      segments.set(0, root);
      return CQL.to(segments).asRef();
   }
});

Modify the Select List

The modifier can also be used to add or remove select list items:

java
// copy: SELECT from Books { title, author { name }} where title = 'Capire'

CqnSelect copy = CQL.copy(query, new Modifier() {
   @Override
   public List<CqnSelectListItem> items(List<CqnSelectListItem> items) {
      items.add(CQL.get("title"));                // add title
      items.add(CQL.to("author").expand("name")); // expand author name
      return items;
   };
});
// copy: SELECT from Books { title, author { name }} where title = 'Capire'

CqnSelect copy = CQL.copy(query, new Modifier() {
   @Override
   public List<CqnSelectListItem> items(List<CqnSelectListItem> items) {
      items.add(CQL.get("title"));                // add title
      items.add(CQL.to("author").expand("name")); // expand author name
      return items;
   };
});

Modify the Order-By Clause

To modify the sort specification of the query, the orderBy method of the Modifier should be overridden:

java
// copy: SELECT from Books ORDER BY title desc

CqnSelect copy = CQL.copy(query, new Modifier() {
   @Override
   public List<CqnSortSpecification> orderBy(List<CqnSortSpecification> orderSpec) {
      orderSpec.add(CQL.get("title").desc());	// add title
      return orderSpec;
   };
});
// copy: SELECT from Books ORDER BY title desc

CqnSelect copy = CQL.copy(query, new Modifier() {
   @Override
   public List<CqnSortSpecification> orderBy(List<CqnSortSpecification> orderSpec) {
      orderSpec.add(CQL.get("title").desc());	// add title
      return orderSpec;
   };
});