Query Builder API

API to fluently build CDS QL statements in Java

Content

• Introduction
• Select
• Insert
• Upsert
• Update
• Delete
• Parsing CQN

Introduction

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

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

Select

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

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

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

Dynamic

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

Static

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

Where Clause

In the below sections, we explore different kinds of predicates, values and functions available in the Builder API.

Filter Predicates

Find By ID

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

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

Matching - Query by Example

matching is a query-by-example style alternative to explicitly defining the where clause. It adds a predicate to the query that filters out all entities where the elements’ values are equal to values given by a key-value map. The example with key being the element being matched as shown below.

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

The key-value map object can contain path expression-based keys referring to elements of an associated entity. In the example below, bookshop.Books has a many to one association to author entity, thus author.name will refer to the name element within the author entity.

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

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

Search

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

If any element of an entity is annotated with @Search.defaultSearchElement, only elements with this annotation will be searched. Otherwise, all elements of type cds.String are searched.

// Book record - (ID, title, name) VALUES (1, "The greatest works of James Allen", "Unwin")
 
Select.from("bookshop.Books")
        .columns("id", "name")
        .search("Allen");

The sample book record mentioned above will be selected by the query, even though title isn’t explicitly mentioned but contains the search string.

Where Clause

Select.from("bookshop.Books")
  .where(b -> b.get("stock")
  .eq(15));

Select.from("bookshop.Books")
  .where(b -> b.get("stock")
  .ne(25));

Select.from("bookshop.Books")
  .where(b -> b.get("stock")
  .gt(5));

Select.from("bookshop.Books")
  .where(b -> b.get("stock")
  .lt(5));

Select.from("bookshop.Books")
  .where(b -> b.get("stock")
  .le(5));

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

Select.from("bookshop.Books")
  .where(b -> b.get("stock")
  .between(5,10));

Select.from("bookshop.Books")
  .where(b -> b.get("stock")
  .isNull());

Select.from("bookshop.Books")
  .where(b -> b.get("stock")
  .isNotNull());

Select.from(EMPLOYEE)
  .where(e -> e.name()
  .contains("oni"));

Select.from("bookshop.Books")
  .where(b -> b.get("title")
  .startsWith("The"));

Select.from("bookshop.Books")
  .where(b -> b.get("title")
  .endsWith("Raven"));

Select.from("bookshop.Authors")
.where(a -> 
  a.get("name").eq("Peter)
   .and(a.get("Id").eq(1)));

Select.from("bookshop.Authors")
.where(a -> 
  a.get("name").eq("Peter)
   .or(a.get("Id").eq(1)));

Select.from("bookshop.Authors")
.where(a -> 
  not(a.get("Id").eq(3)));

Values

Parameters

Param Function provides an option to specify a parameter value when executing a query built with Builder API.

import static com.sap.cds.ql.CqnBuilder.param;
Select.from("bookshop.Authors")
	.columns("name").byId(param());
dataStore.execute(query, 111);

Literal

Literal Function is used to specify a literal value in a query whose underlying datatype translation is provided by the Builder API.

import static com.sap.cds.ql.CqnBuilder.func;
Function<Employee_, CqnSelectListItem> name = e -> func("coalesce", e.name(), literal("unknown")).as("name");

Select.from(EMPLOYEE)
	.columns(e -> func("count", e.name()).as("count"), name)
	.groupBy(e -> e.name(), name)
	.having(e -> func("count", e.name()).gt(1));

Scalar Functions

The generic function func creates a scalar function that is to be 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 example below, the native query language count function is called on the name element, which will return the count of number of elements with name as Monika.

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

toLower

Represents a native query language-based 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.CqnBuilder.toLower;
Select.from(EMPLOYEE).columns(e -> e.name())
	.where(e -> e.name().endsWith(toLower("IKA")));

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

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

toUpper

Represents a native query language-based 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.CqnBuilder.toUpper;
Select.from(EMPLOYEE).columns(e -> e.name())
	.where(e -> e.name().endsWith(toUpper("ika")));

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

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

Substring

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

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

In the example below, 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 creates a CQN 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 creates a CQN Arithmetic expression to subtract a specified value with this value.

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

Times

Function creates a CQN Arithmetic expression to multiply a specified value with this value. In the example below, 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 creates a CQN Arithmetic expression to divide this value with the specified value. In the example below, 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.dividedBy(2)));
	
				OR
Select.from(AUTHOR)
	.where(a -> a.id().between(10, literal(30).dividedBy(2)));

Expand

The Expand function enables query expansion of elements of other associated entities. In the simple example below, all associated entity elements for Book entity are expanded.

Select.from("bookshop.Books")
      .columns(b -> b._all(), b -> b.expand())
	.where(b -> b.get("ID").eq(251));

The function also allows for expanding specific entity elements or associations as shown below.

Select.from("bookshop.Books")
      .columns(b -> b.to("Authors").expand("name"))
	.where(b -> b.get("author.Id").eq(1));
	
				OR

Select.from("bookshop.Publisher")
      .columns(p -> p.get("name"),
      		p -> p.to("books").expand(b -> b.get("title")));

Insert

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

Dynamic

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

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

Static

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

Deep Insert

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

CDS Model

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

Deep Insert

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

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

Upsert

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

Dynamic

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

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

Static

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

Deep Upsert

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

CDS Model

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

Deep Upsert

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

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

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

Update

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

Dynamic

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

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

Static

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

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

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

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

Delete

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

CDS Model

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

Dynamic

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

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

Static

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

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

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

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

Parsing CQN

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

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

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

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

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

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