Building CDS QL Statements
API to fluently build CDS QL statements in Java
Content
Introduction
The CDS QL statement builders allow to fluently construct CDS QL statements, which can be executed by CDS Services or the CDS Data Store.
Concepts
The CDS QL Statement Builders
Use the builder classes Select
, Insert
, Upsert
, Update
, and Delete
to construct CDS QL statements.
The following example shows a CQL query and how it’s constructed with the Select
builder:
-- CQL
SELECT from bookshop.Books { title } where ID = 101
// Java CDS QL (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:
import static bookshop.Bookshop_.BOOKS;
// Java CDS QL (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.
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 CDS QL builders leverage lambda expressions to fluently compose expressions and path expressions that are used in the statements’ clauses.
-- CQL
SELECT from bookshop.Books { title } where year < 2000
// Java CDS QL
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 CDS QL (static)
Select.from(BOOKS)
.columns(b -> b.title(), b -> b.author().name().as("author"))
.where(b -> b.author().name().startsWith("A"));
// Java CDS QL (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 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:
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.
-- CQL
SELECT from Orders[3].items { quantity, book.title as book }
// Java CDS QL
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 QL 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
.
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:
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.
Select.from("bookshop.Authors").byId(101);
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.
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
:
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 CDS QL 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’.
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.
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.
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 CDS QL 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:
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.
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:
- If a value changes at runtime (due to computation or external input) you should use
val()
. - If a value does not change at runtime and doesn’t depend on user input consider using
constant()
.
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.
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) as youngestAuthors 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
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:
CqnSelect query = Select.from("bookshop.Books")
.columns("title", "author.name");
To define more complex projections and benefit from code completion, use lambda expressions:
// 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.
Deep Read with expand
Use expand
to read deeply structured documents and entity graphs into a structured result.
// 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
:
[
{
"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:
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:
[
{
"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:
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:
[
{
"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:
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:
Select.from(BOOKS).columns(b -> b.expand());
To optimize the execution of expands, make sure the following conditions are met:
For to-one expands the expand item list mustn’t contain literal values, expressions or nested to-many expands.
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 some cases the default query optimization for to-many expands can lead to issues. You can annotate the
association with @cds.java.expand: {using: 'parent-keys'}
to fall back to the non-optimized expand.
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 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:
[
{
"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:
// 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
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.
-
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
andname
, 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; }
-
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 thoughtitle
isn’t selected. -
Use search expressions
It’s also possible to create a more complex search expression using
AND
,OR
, andNOT
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
, 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
.
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:
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’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:
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:
name | nickname |
---|---|
William | Bill |
William | null |
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;
quantity : 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("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);
On SQL data stores the execution order of the generated insert statements is parent first.
Upsert
The Upsert statement either inserts a new entry or, if an entry with the same key values exists, completely replaces it with the given data.
The main use case of upsert is data replication. In contrast to Update, the upsert data has to be complete. If a value is missing, it’s set to the default value of the element or null if the element does not have a default value.
This includes elements annotated with @cds.on.insert
, such as createdAt
and createdBy
of the managed
aspect, which are also overwritten by upsert.
An Upsert
statement is created with the Upsert builder.
Single Upsert
To upsert a single entry, provide the data as a map to the entry method:
import static bookshop.Bookshop_.BOOKS;
import bookshop.Books;
Books book = Books.create();
book.setId(101);
book.setTitle("Capire 2");
CqnUpsert upsert = Upsert.into(BOOKS).entry(book);
Bulk Upsert
The Upsert
also supports bulk operations. Here the data is passed as an Iterable of maps to the entries method:
import static bookshop.Bookshop_.BOOKS;
import bookshop.Books;
Books b1 = Books.create();
b1.setId(101);
b1.setTitle("Odyssey");
Books b2 = Books.create();
b2.put("ID", 103);
b2.put("title", "Ulysses");
List<Books> 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.
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;
quantity : Integer;
...
}
Find this source also in cap/samples.
Java:
import static bookshop.Bookshop_.ORDERS;
import bookshop.Orders;
import bookshop.OrderItems;
OrderItems item = OrderItems.create();
item.setId(1);
item.setBookId(101);
item.setQuantity(2);
List<OrderItems> items;
items.add(item);
Orders order = Orders.create();
order.setOrderNo("1000");
order.setItems(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. The update data can be partial, elements without update values are not updated, elements annotated with @cds.on.update
are updated based on the annotation value.
Use the Update builder to create an update statement.
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;
import 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.
Updating key elements is not supported.
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:
Books book = Books.create();
book.setId(100);
book.setTitle("CAP Matters");
CqnUpdate update = Update.entity(BOOKS).data(book);
Using byId
If the ID value isn’t part of the data, it can be specified with the byId method:
CqnUpdate update = Update.entity("bookshop.Books").data("title", "CAP Matters").byId(100);
Using Matching
Alternatively, the ID values can be specified as a map using matching:
Map<String, Object> entry = Collections.singletonMap("title", "CAP Matters");
Map<String, Object> keys = Collections.singletonMap("ID", 100);
CqnUpdate update = Update.entity("bookshop.Books").data(entry).matching(keys);
Using byParams
To update 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:
import static bookshop.Bookshop_.BOOKS;
// using where
Update.entity(BOOKS).data("stock", 0)
.where(b -> b.title().eq(param("title"))
.and(b.author().name().eq(param("author.name"))));
// using byParams
Update.entity(BOOKS).data("stock", 0)
.byParams("title", "author.name");
Using Path Expression
The individual target entity, can also be directly specified using a path expression:
Map<String, Object> entry = 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);
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 also deleted if the delete operation cascades.
Given the following Order:
{
"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":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, "quantity":2},
{"Id":3, "book":{"ID":300}, "quantity":1}]
}
Results in the updated Order:
{
"OrderNo": "1000",
"status": "in process",
"createdAt": "2020-03-01T12:21:34.000Z",
"items": [{"Id":1, "book":{"ID":100}, "quantity":2},
{"Id":3, "book":{"ID":300}, "quantity":1}]
}
- Order
status
changed to “in process” - Item 1
quantity
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.
Bulk Update
Bulk updates allow to update multiple individual entities in a target entity set. The key values are extracted from the data, which is given as a collection of maps using the entries method. 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:
Orders o1 = Orders.create();
o1.setOrderNo(1);
o1.setStatus("canceled");
Orders o2 = Orders.create();
o2.setOrderNo(2);
o2.setStatus("in process");
Orders o3 = Orders.create();
o3.setOrderNo(3);
o3.put("header.comment", "Deliver with Order 2");
List<Orders> orders = Arrays.asList(o1, o2, o3);
CqnUpdate update = Update.entity("bookshop.Orders").entries(orders);
Bulk updates with entries updating the same elements can be executed more efficiently than individual update operations and bulk updates with heterogeneous update data.
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.
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
.
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:
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 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 val
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.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:
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.
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:
// 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");
// 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);
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 languagecount
function is called on thename
element. This function returns the count of number of elements with nameMonika
.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 thename
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 thename
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).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.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. |
|
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. |
|
IS | Test if this value equals a given value. NULL values are treated as any other value. |
|
IS NOT | Test if this value is NOT equal to a given value. NULL values are treated as any other value. |
|
GT | Test if this value is greater than a given value. |
|
LT | Test if this value is less than a given value. |
|
LE | Test if this value is less than or equal to a given value. |
|
IN | Test if this value is equal to any value in a given list. |
|
BETWEEN | Test if this value is between a range of values. |
|
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. |
|
OR | Returns a predicate that represents a logical OR of this predicate and another. |
|
NOT | Returns a predicate that represents the logical negation of this predicate. |
|
Predicate Functions
These boolean-valued functions can be used in filters:
Operator | Description | Example |
CONTAINS | Test if this string value contains a given substring. |
|
STARTS WITH | Test if this string value starts with a given prefix. |
|
ENDS WITH | Test if this string value ends with a given suffix. |
|
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’:
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’:
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”:
Select.from(AUTHORS).where(a -> a.books().publisher().allMatch(p -> p.name().eq("CAP Publications")));
This is equivalent to
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"))));
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;
// 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.
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"
:
// 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
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.
CQL Expression Trees
As an alternative to fluent API the CDS QL 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:
// 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:
// 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 Collector
s 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:
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
:
// 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
:
// 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
:
// 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.
Consider the LIKE_REGEXPR
predicate, which is an SAP HANA-specific function performing regular expression matching:
// CQL: SELECT from Books where title LIKE_REGEXPR 'Raven|Eleonora';
Select.from("bookshop.Books").where(booleanFunc("LIKE_REGEXPR", singletonList(val("Raven|Eleonora"))));
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
:
// 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);
Copying & Modifying CDS QL Statements
CDS QL 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
:
// 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 CDS QL statement can be modified, for example, the where
condition:
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'
.
// 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);
};
});
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:
// copy: SELECT from Books[year > 2000] where title = 'Capire'
CqnSelect copy = CQL.copy(query, new Modifier() {
@Override
public CqnStructuredTypeRef ref(StructuredTypeRef ref) {
ref.rootSegment().filter(CQL.get("year").gt(2000));
return ref;
}
});
The modifier can also be used to add or remove select list 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;
};
});
To modify the sort specification of the query, the orderBy
method of the Modifier
should be overridden:
// 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;
};
});