| synopsis | status | redirect_from | uacp |
|---|---|---|---|
API to execute CQL statements on services accepting CQN queries. |
released |
java/query-execution |
Used as link target from Help Portal at https://help.sap.com/products/BTP/65de2977205c403bbc107264b8eccf4b/9186ed9ab00842e1a31309ff1be38792.html |
{{ $frontmatter.synopsis }}
CDS Query Language (CQL) statements can be executed using the run method of any service that accepts CQN queries:
CqnService service = ...
CqnSelect query = Select.from("bookshop.Books")
.columns("title", "price");
Result result = service.run(query);Queries, as well as update and delete statements, can be parameterized with named, or indexed parameters. Update and delete statements with named parameters can be executed in batch mode using multiple parameter sets.
The following statement uses two parameters named id1 and id2. The parameter values are given as a map:
import static com.sap.cds.ql.CQL.param;
CqnDelete delete = Delete.from("bookshop.Books")
.where(b -> b.get("ID").eq(param("id1"))
.or(b.get("ID").eq(param("id2"))));
Map<String, Object> paramValues = new HashMap<>();
paramValues.put("id1", 101);
paramValues.put("id2", 102);
Result result = service.run(delete, paramValues);::: warning
The parameter value map must be of type Map<String, Object>, otherwise the map is interpreted as a single positional/indexed parameter value, which results in an error.
:::
The following statement uses two indexed parameters defined through param(i):
import static com.sap.cds.ql.CQL.param;
CqnDelete delete = Delete.from("bookshop.Books")
.where(b -> b.get("ID").in(param(0), param(1)));
Result result = service.run(delete, 101, 102);Before the execution of the statement the values 101 and 102 are bound to the defined parameters.
Update and delete statements with named parameters can be executed as batch with multiple parameter sets. The named parameters example from above can be expressed using batch delete with a single parameter and two value sets:
import static com.sap.cds.ql.CQL.param;
CqnDelete delete = Delete.from("bookshop.Books").byParams("ID");
Map<String, Object> paramSet1 = singletonMap("ID", 101);
Map<String, Object> paramSet1 = singletonMap("ID", 102);
Result result = service.run(query, asList(paramSet1, paramSet2));
long deletedRows = result.rowCount();From the result of a batch update/delete the total number of updated/deleted rows can be determined by rowCount(), and rowCount(batchIndex) returns the number of updated/deleted rows for a specific parameter set of the batch. The number of batches can be retrieved via the batchCount() method. Batch updates also return the update data.
The maximum batch size for update and delete can be configured via cds.sql.max-batch-size and has a default of 1000.
To query views with parameters on SAP HANA, build a select statement and execute it with named parameter values that correspond to the view's parameters.
Let's consider the following Books entity and a parameterized view BooksView, which returns the ID and title of Books with stock greater or equal to the value of the parameter minStock:
entity Books {
key ID : UUID;
title : String;
stock : Integer;
}
entity BooksView(minStock : Integer) as
SELECT from Books {ID, title} where stock >= :minStock;To query BooksView in Java, run a select statement and provide values for all view parameters:
CqnSelect query = Select.from("BooksView");
var params = Map.of("minStock", 100);
Result result = service.run(query, params);To add a hint clause to a statement, use the hints method and prefix the SAP HANA hints with hdb.:
CqnSelect query = Select.from(BOOKS).hints("hdb.USE_HEX_PLAN", "hdb.ESTIMATION_SAMPLES(0)");::: warning
Hints prefixed with hdb. are directly rendered into SQL for SAP HANA and therefore must not contain external input!
:::
The CQN API allows to manipulate data by executing insert, update, delete, or upsert statements.
The update operation can be executed as follows:
Map<String, Object> book = Map.of("title", "CAP");
CqnUpdate update = Update.entity("bookshop.Books").data(book).byId(101);
Result updateResult = service.run(update);The update Result contains the data that is written by the statement execution. Additionally to the given data, it may contain values generated for managed data and foreign key values.
The row count of the update Result indicates how many rows where updated during the statement execution:
CqnUpdate update = ...
long rowCount = service.run(update).rowCount();If no rows are touched the execution is successful but the row count is 0.
:::warning The setters of an update with expressions are evaluated on the database. The result of these expressions is not contained in the update result. :::
It's possible to work with structured data as the insert, update, and delete operations cascade along compositions.
By default, insert, update and delete operations cascade over compositions only. For associations, this can be enabled using the @cascade annotation.
::: warning
Cascading operations over associations isn't considered good practice and should be avoided.
:::
Annotating an association with @cascade: {insert, update, delete} enables deep updates/upserts through this association.
Given the following CDS model with two entities and an association between them, only insert and update operations are cascaded through author:
entity Book {
key ID : Integer;
title : String;
@cascade: {insert, update}
author : Association to Author;
}
entity Author {
key ID : Integer;
name : String;
}::: warning ❗ Warning
For inactive draft entities @cascade annotations are ignored.
:::
::: warning ❗ Warning
The @cascade annotation is not respected by foreign key constraints on the database. To avoid unexpected behaviour you might have to disable a FK constraint with @assert.integrity:false.
:::
Insert and upsert statements for an entity have to include the keys and (optionally) data for the entity's composition targets. The targets are inserted or upserted along with the root entity.
Iterable<Map<String, Object>> books;
CqnInsert insert = Insert.into("bookshop.Books").entries(books);
Result result = service.run(insert);
CqnUpsert upsert = Upsert.into("bookshop.Books").entries(books);
Result result = service.run(upsert);The delete operation is cascaded along the entity's compositions. All composition targets that are reachable from the (to be deleted) entity are deleted as well.
The following example deletes the order with ID 1000 including all its items:
CqnDelete delete = Delete.from("bookshop.Orders").matching(singletonMap("OrderNo", 1000));
long deleteCount = service.run(delete).rowCount();The CAP Java SDK aims to resolve statements on non-complex views and projections to their underlying entity. When delegating queries between Application Services and Remote Services, statements are resolved to the entity definitions of the targeted service. Using the Persistence Service, only modifying statements are resolved before executing database queries. This allows to execute Insert, Upsert, Update, and Delete operations on database views. For Select statements database views are always leveraged, if available.
Views and projections can be resolved if the following conditions are met:
- The view definition does not use any other clause than
columnsandexcluding. - The projection includes all key elements; with the exception of insert operations with generated UUID keys.
- The projection includes all elements with a
not nullconstraint, unless they have a default value. - The projection must not include calculated fields when running queries against a remote OData service.
- The projection must not include path expressions using to-many associations.
For Insert or Update operations, if the projection contains functions or expressions, these values are ignored. Path expressions navigating to-one associations, can be used in projections as shown by the Header view in the following example. The Header view includes the element country from the associated entity Address.
// Supported
entity Order as projection on bookshop.Order;
entity Order as projection on bookshop.Order { ID, status as state };
entity Order as projection on bookshop.Order excluding { status };
entity Header as projection on bookshop.OrderHeader { key ID, address.country as country };If a view is too complex to be resolved by the CDS runtime, the statement remains unmodified. Views that cannot be resolved by the CDS runtime include the use of join, union and the where clause.
- For the Persistence Service, this means the runtime attempts to execute the write operation on the database view. Whether this execution is possible is database dependent.
- For Application Services and Remote Services, the targeted service will reject the statement.
Example of a view that can't be resolved:
// Unsupported
entity DeliveredOrders as select from bookshop.Order where status = 'delivered';
entity Orders as select from bookshop.Order inner join bookshop.OrderHeader on Order.header.ID = OrderHeader.ID { Order.ID, Order.items, OrderHeader.status };Concurrency control allows protecting your data against unexpected concurrent changes.
Use optimistic concurrency control to detect concurrent modification of data across requests. The implementation relies on an ETag, which changes whenever an entity instance is updated. Typically, the ETag value is stored in an element of the entity.
In the OData protocol, the implementation relies on ETag and If-Match headers in the HTTP request.
The @odata.etag annotation indicates to the OData protocol adapter that the value of an annotated element should be used as the ETag for conflict detection:
{#on-update-example}
entity Order : cuid {
@odata.etag
@cds.on.update : $now @cds.on.insert : $now
modifiedAt : Timestamp;
product : Association to Product;
}An ETag can also be used programmatically in custom code. Use the CqnEtagPredicate to specify the expected ETag values in an update or delete operation. ETag checks are not executed on upsert. You can create an ETag predicate using the CQL.eTag or the StructuredType.eTag methods.
PersistenceService db = ...
Instant expectedLastModification = ...
CqnUpdate update = Update.entity(ORDER).entry(newData)
.where(o -> o.id().eq(85).and(
o.eTag(expectedLastModification)));
Result rs = db.execute(update);
if (rs.rowCount() == 0) {
// order 85 does not exist or was modified concurrently
}In the previous example, an Order is updated. The update is protected with a specified ETag value (the expected last modification timestamp). The update is executed only if the expectation is met.
::: warning Application has to check the result
No exception is thrown if an ETag validation does not match. Instead, the execution of the update (or delete) succeeds but doesn't apply any changes. Ensure that the application checks the rowCount of the Result and implement your error handling. If the value of rowCount is 0, that indicates that no row was updated (or deleted).
:::
A convenient option to determine a new ETag value upon update is the @cds.on.update annotation as in the example above. The CAP Java runtime automatically handles the @cds.on.update annotation and sets a new value in the data before the update is executed. Such managed data can be used with ETags of type Timestamp or UUID only.
We do not recommend providing a new ETag value by custom code in a @Before-update handler. If you do set a value explicitly in custom code and an ETag element is annotated with @cds.on.update, the runtime does not generate a new value upon update for this element. Instead, the value that comes from your custom code is used.
Alternatively, you can store ETag values in version elements. For version elements, the values are exclusively managed by the runtime without the option to set them in custom code. Annotate an element with @cds.java.version to advise the runtime to manage its value.
entity Order : cuid {
@odata.etag
@cds.java.version
version : Int32;
product : Association to Product;
}Compared to @cds.on.update, which allows for ETag elements with type Timestamp or UUID only, @cds.java.version additionally supports all integral types Uint8, ... Int64. For timestamp, the value is set to $now upon update, for elements of type UUID a new UUID is generated, and for elements of integral type the value is incremented.
Version elements can be used with an ETag predicate to programmatically check an expected ETag value. Moreover, if additionally annotated with @odata.etag, they can be used for conflict detection in OData.
If the update data contains a value for a version element, this value is used as the expected value for the version. This allows using version elements in a programmatic flow conveniently:
PersistenceService db = ...
CqnSelect select = Select.from(ORDER).byId(85);
Order order = db.run(select).single(Order.class);
order.setAmount(5000);
CqnUpdate update = Update.entity(ORDER).entry(order);
Result rs = db.execute(update);
if (rs.rowCount() == 0) {
// order 85 does not exist or was modified concurrently
}During the execution of the update statement it's asserted that the version has the same value as the version, which was read previously and hence no concurrent modification occurred.
The same convenience can be used in bulk operations. Here the individual update counts need to be introspected.
CqnSelect select = Select.from(ORDER).where(o -> amount().gt(1000));
List<Order> orders = db.run(select).listOf(Order.class);
orders.forEach(o -> o.setStatus("cancelled"));
Result rs = db.execute(Update.entity(ORDER).entries(orders));
for(int i = 0; i orders.size(); i++) if (rs.rowCount(i) == 0) {
// order does not exist or was modified concurrently
}If an ETag predicate is explicitly specified, it overrules a version value given in the data.
Use database locks to ensure that data returned by a query isn't modified in a concurrent transaction. Exclusive locks block concurrent modification and the creation of any other lock. Shared locks, however, only block concurrent modifications and exclusive locks but allow the concurrent creation of other shared locks.
To lock data:
- Start a transaction (either manually or let the framework take care of it).
- Query the data and set a lock on it.
- Perform the processing and, if an exclusive lock is used, modify the data inside the same transaction.
- Commit (or roll back) the transaction, which releases the lock.
To be able to query and lock the data until the transaction is completed, just call a lock() method and set an optional parameter timeout.
In the following example, a book with ID 1 is selected and locked until the transaction is finished. Thus, one can avoid situations when other threads or clients are trying to modify the same data in the meantime:
// Start transaction
// Obtain and set a write lock on the book with id 1
service.run(Select.from("bookshop.Books").byId(1).lock());
...
// Update the book locked earlier
Map<String, Object> data = Collections.singletonMap("title", "new title");
service.run(Update.entity("bookshop.Books").data(data).byId(1));
// Finish transactionThe lock() method has an optional parameter timeout that indicates the maximum number of seconds to wait for the lock acquisition. If a lock can't be obtained within the timeout, a CdsLockTimeoutException is thrown. If timeout isn't specified, a database-specific default timeout will be used.
The parameter mode allows to specify whether an EXCLUSIVE or a SHARED lock should be set.
The CDS compiler generates SQL DDL statements based on your CDS model, which include SQL views for all CDS views and projections. This means adding or changing CDS views requires a deployment of the database schema changes.
To avoid schema updates due to adding or updating CDS views, annotate them with @cds.persistence.skip. In this case the CDS compiler won't generate corresponding static database views. Instead, the CDS views are dynamically resolved by the CAP Java runtime.
entity Books {
key id : UUID;
title : String;
stock : Integer;
author : Association to one Authors;
}
@cds.persistence.skip // [!code focus]
entity BooksWithLowStock as projection on Books { // [!code focus]
id, title, author.name as author // [!code focus]
} where stock < 10; // [!code focus]At runtime, CAP Java resolves queries against runtime views until an entity is reached that isn't annotated with @cds.persistence.skip. For example, the CQL query
Select BooksWithLowStock where author = 'Kafka'is executed against SQL databases as
SELECT B.ID, B.TITLE, A.NAME as "author" FROM BOOKS B
LEFT OUTER JOIN AUTHORS A ON B.AUTHOR_ID = A.ID
WHERE B.STOCK < 10 AND A.NAME = ?::: tip Runtime views are supported for CDS projections. Constant values and expressions such as case when are currently ignored.
Complex views using aggregations or union/join/subqueries in FROM are not yet supported.
:::
As described in section Predefined Types it's possible to stream the data, if the element is annotated with @Core.MediaType. The following example demonstrates how to allocate the stream for element coverImage, pass it through the API to an underlying database and close the stream.
Entity Books has an additional annotated element coverImage : LargeBinary:
entity Books {
key ID : Integer;
title : String;
...
@Core.MediaType
coverImage : LargeBinary;
}Java snippet for creating element coverImage from file IMAGE.PNG using java.io.InputStream:
// Transaction started
Result result;
try (InputStream resource = getResource("IMAGE.PNG")) {
Map<String, Object> book = new HashMap<>();
book.put("title", "My Fancy Book");
book.put("coverImage", resource);
CqnInsert insert = Insert.into("bookshop.Books").entry(book);
result = service.run(insert);
}
// Transaction finishedCAP Java doesn't have a dedicated API to execute native SQL Statements. However, when using Spring as application framework you can leverage Spring's features to execute native SQL statements. See Execute SQL statements with Spring's JdbcTemplate for more details.
The result of a query is abstracted by the Result interface, which is an iterable of Row. A Row is a Map<String, Object> with additional convenience methods and extends CdsData.
You can iterate over a Result:
Result result = ...
for (Row row : result) {
System.out.println(row.get("title"));
}Or process it with the Stream API:
Result result = ...
result.forEach(r -> System.out.println(r.get("title")));
result.stream().map(r -> r.get("title")).forEach(System.out::println);If your query is expected to return exactly one row, you can access it with the single method:
Result result = ...
Row row = result.single();If it returns a result, like a find by id would, you can obtain it using first:
Result result = ...
Optional<Row> row = result.first();
row.ifPresent(r -> System.out.println(r.get("title")));The Row's getPath method supports paths to simplify extracting values from nested maps. This also simplifies extracting values from results with to-one expands using the generic accessor. Paths with collection-valued segments and infix filters are not supported.
CqnSelect select = Select.from(BOOKS).columns(
b -> b.title(), b -> b.author().expand()).byId(101);
Row book = dataStore.execute(select).single();
String author = book.getPath("author.name");A result row may contain null values for an element of the result if no data is present for the element in the underlying data store.
Use the get methods to check if an element is present in the result row:
if (row.get("name") == null) {
// handle mising value for name
}Avoid using containsKey to check for the presence of an element in the result row. Also, when iterating the elements of the row, keep in mind, that the data may contain null values:
row.forEach((k, v) -> {
if (v == null) {
// handle mising value for element v
}
});The element names and their types are checked only at runtime. Alternatively you can use interfaces to get typed access to the result data:
interface Book {
String getTitle();
Integer getStock();
}
Row row = ...
Book book = row.as(Book.class);
String title = book.getTitle();
Integer stock = book.getStock();Interfaces can also be used to get a typed list or stream over the result:
Result result = ...
List<Book> books = result.listOf(Book.class);
Map<String, String> titleToDescription =
result.streamOf(Book.class).collect(Collectors.toMap(Book::getTitle, Book::getDescription));For the entities defined in the data model, CAP Java SDK can generate interfaces for you through a Maven plugin.
For result rows that contain all key values of an entity, you get an entity reference via the ref() method. This reference addresses the entity via the key values from the result row.
// SELECT from Author[101]
CqnSelect query = Select.from(AUTHOR).byId(101);
Author authorData = service.run(query).single(Author.class);
String authorName = authorData.getName(); // data access
Author_ author = authorData.ref(); // typed reference to Author[101]Similar for untyped results:
Row authorData = service.run(query).single();
StructuredType<?> author = authorData.ref(); // untyped reference to Author[101]This also works for Insert and Update results:
CqnUpdate update = Update.entity(AUTHOR).data("name", "James Joyce").byId(101);
Author_ joyce = service.run(update).single(Author.class).ref();Using entity references you can easily write CDS QL statements targeting the source entity:
// SELECT from Author[101].books { sum(stock) as stock }
CqnSelect q = Select.from(joyce.books())
.columns(b -> func("sum", b.stock()).as("stock"));
CqnInsert i = Insert.into(joyce.books())
.entry("title", "Ulysses");
CqnUpdate u = Update.entity(joyce.biography())
.data("price", 29.95);
CqnDelete d = Delete.from(joyce.address())
.where(b -> b.stock().lt(1));The rowType method allows to introspect the element names and types of a query's Result. It returns a CdsStructuredType describing the result in terms of the Reflection API:
CqnSelect query = Select.from(AUTHOR)
.columns(a -> a.name().as("authorName"), a -> a.age());
Result result = service.run(query);
CdsStructuredType rowType = result.rowType();
rowType.elements(); // "authorName", "age"
rowType.getElement("age").getType().getQualifiedName(); // "cds.Integer"
rowType.findElement("ID"); // Optional.empty()