r2dbc-fluent.adoc

Fluent Data Access API

The SQL API of DatabaseClient offers you maximum flexibility to run any type of SQL. DatabaseClient provides a more narrow interface for typical ad-hoc use-cases, such as querying, inserting, updating, and deleting data.

The entry points (insert(), select(), update(), and others) follow a natural naming schema based on the operation to be run. Moving on from the entry point, the API is designed to offer only context-dependent methods that lead to a terminating method that creates and runs a SQL statement. Spring Data R2DBC uses a R2dbcDialect abstraction to determine bind markers, pagination support and the data types natively supported by the underlying driver.

Consider the following simple query:

Flux<Person> people = databaseClient.select()
  .from(Person.class)                         (1)
  .fetch()
  .all();                                     (2)

1. Using Person with the from(…) method sets the FROM table based on mapping metadata. It also maps tabular results on Person result objects.

2. Fetching all() rows returns a Flux<Person> without limiting results.

The following example declares a more complex query that specifies the table name by name, a WHERE condition, and an ORDER BY clause:

Mono<Person> first = databaseClient.select()
  .from("legoset")                           (1)
  .matching(where("firstname").is("John")    (2)
    .and("lastname").in("Doe", "White"))
  .orderBy(desc("id"))                       (3)
  .as(Person.class)
  .fetch()
  .one();                                    (4)

1. Selecting from a table by name returns row results as Map<String, Object> with case-insensitive column name matching.

2. The issued query declares a WHERE condition on firstname and lastname columns to filter results.

3. Results can be ordered by individual column names, resulting in an ORDER BY clause.

4. Selecting the one result fetches only a single row. This way of consuming rows expects the query to return exactly a single result. Mono emits a IncorrectResultSizeDataAccessException if the query yields more than a single result.

Tip	You can directly apply Projections to result documents by providing the target type via as(Class<?>).

You can consume Query results in three ways:

• Through object mapping (for example, as(Class<T>)) by using Spring Data’s mapping-metadata.

• As Map<String, Object> where column names are mapped to their value. Column names are looked up in a case-insensitive way.

• By supplying a mapping BiFunction for direct access to R2DBC Row and RowMetadata.

You can switch between retrieving a single entity and retrieving multiple entities through the following terminating methods:

• first(): Consume only the first row, returning a Mono. The returned Mono completes without emitting an object if the query returns no results.

• one(): Consume exactly one row, returning a Mono. The returned Mono completes without emitting an object if the query returns no results. If the query returns more than one row, Mono completes exceptionally emitting IncorrectResultSizeDataAccessException.

• all(): Consume all returned rows returning a Flux.

• rowsUpdated: Consume the number of affected rows. It is typically used with INSERT,UPDATE, and DELETE statements.

Selecting Data

You can use the select() entry point to express your SELECT queries. The resulting SELECT queries support the commonly used clauses (WHERE and ORDER BY) and support pagination. The fluent API style let you chain together multiple methods while having easy-to-understand code. To improve readability, you can use static imports that let you avoid using the 'new' keyword for creating Criteria instances.

Methods for the Criteria Class

The Criteria class provides the following methods, all of which correspond to SQL operators:

• Criteria and (String column): Adds a chained Criteria with the specified property to the current Criteria and returns the newly created one.

• Criteria or (String column): Adds a chained Criteria with the specified property to the current Criteria and returns the newly created one.

• Criteria greaterThan (Object o): Creates a criterion by using the > operator.

• Criteria greaterThanOrEquals (Object o): Creates a criterion by using the >= operator.

• Criteria in (Object…​ o): Creates a criterion by using the IN operator for a varargs argument.

• Criteria in (Collection<?> collection): Creates a criterion by using the IN operator using a collection.

• Criteria is (Object o): Creates a criterion by using column matching (property = value).

• Criteria isNull (): Creates a criterion by using the IS NULL operator.

• Criteria isNotNull (): Creates a criterion by using the IS NOT NULL operator.

• Criteria lessThan (Object o): Creates a criterion by using the < operator.

• Criteria lessThanOrEquals (Object o): Creates a criterion by using the ⇐ operator.

• Criteria like (Object o): Creates a criterion by using the LIKE operator without escape character processing.

• Criteria not (Object o): Creates a criterion by using the != operator.

• Criteria notIn (Object…​ o): Creates a criterion by using the NOT IN operator for a varargs argument.

• Criteria notIn (Collection<?> collection): Creates a criterion by using the NOT IN operator using a collection.

You can use Criteria with SELECT, UPDATE, and DELETE queries.

Methods for SELECT operations

The select() entry point exposes some additional methods that provide options for the query:

• from (Class<T>): Specifies the source table by using a mapped object. By default, it returns results as T.

• from (String): Specifies the source table name. By default, it returns results as Map<String, Object>.

• as (Class<T>): Maps results to T.

• map (BiFunction<Row, RowMetadata, T>): Supplies a mapping function to extract results.

• project (String…​ columns): Specifies which columns to return.

• matching (Criteria): Declares a WHERE condition to filter results.

• orderBy (Order): Declares an ORDER BY clause to sort results.

• page (Page pageable): Retrieves a particular page within the result. It limits the size of the returned results and reads from an offset.

• fetch (): Transition call declaration to the fetch stage to declare result consumption multiplicity.

Inserting Data

You can use the insert() entry point to insert data. Similar to select(), insert() allows free-form and mapped object inserts.

Consider the following simple typed insert operation:

Mono<Void> insert = databaseClient.insert()
        .into(Person.class)                       (1)
        .using(new Person(…))                     (2)
        .then();                                  (3)

1. Using Person with the into(…) method sets the INTO table, based on mapping metadata. It also prepares the insert statement to accept Person objects for inserting.

2. Provide a scalar Person object. Alternatively, you can supply a Publisher to execute a stream of INSERT statements. This method extracts all non-null values and inserts them.

3. Use then() to insert an object without consuming further details. Modifying statements allow consumption of the number of affected rows or tabular results for consuming generated keys.

Inserts also support untyped operations, as the following example shows:

Mono<Void> insert = databaseClient.insert()
        .into("person")                           (1)
        .value("firstname", "John")               (2)
        .nullValue("lastname")                    (3)
        .then();                                  (4)

1. Start an insert into the person table.

2. Provide a non-null value for firstname.

3. Set lastname to null.

4. Use then() to insert an object without consuming further details. Modifying statements allow consumption of the number of affected rows or tabular results for consuming generated keys.

Methods for INSERT operations

The insert() entry point exposes the following additional methods to provide options for the operation:

• into (Class<T>): Specifies the target table using a mapped object. By default, it returns results as T.

• into (String): Specifies the target table name. By default, it returns results as Map<String, Object>.

• using (T): Specifies the object to insert.

• using (Publisher<T>): Accepts a stream of objects to insert.

• table (String): Overrides the target table name.

• value (String, Object): Provides a column value to insert.

• nullValue (String): Provides a null value to insert.

• map (BiFunction<Row, RowMetadata, T>): Supplies a mapping function to extract results.

• then (): Executes INSERT without consuming any results.

• fetch (): Transition call declaration to the fetch stage to declare result consumption multiplicity.

Updating Data

You can use the update() entry point to update rows. Updating data starts by specifying the table to update by accepting Update specifying assignments. It also accepts Criteria to create a WHERE clause.

Consider the following simple typed update operation:

Person modified = …

Mono<Void> update = databaseClient.update()
  .table(Person.class)                      (1)
  .using(modified)                          (2)
  .then();                                  (3)

1. Using Person with the table(…) method sets the table to update based on mapping metadata.

2. Provide a scalar Person object value. using(…) accepts the modified object and derives primary keys and updates all column values.

3. Use then() to update the rows of an object without consuming further details. Modifying statements also allow consumption of the number of affected rows.

Update also supports untyped operations, as the following example shows:

Mono<Void> update = databaseClient.update()
  .table("person")                           (1)
  .using(Update.update("firstname", "Jane")) (2)
  .matching(where("firstname").is("John"))   (3)
  .then();                                   (4)

1. Update the person table.

2. Provide a, Update definition of which columns to update.

3. The issued query declares a WHERE condition on firstname columns to filter the rows to update.

4. Use then() to update the rows of an object without consuming further details. Modifying statements also allow consumption of the number of affected rows.

Methods for UPDATE operations

The update() entry point exposes the following additional methods to provide options for the operation:

• table (Class<T>): Specifies the target table byusing a mapped object. Returns results by default as T.

• table (String): Specifies the target table name. By default, it returns results as Map<String, Object>.

• using `(T)`Specifies the object to update. It derives criteria itself.

• using (Update): Specifies the update definition.

• matching (Criteria): Declares a WHERE condition to indicate which rows to update.

• then (): Runs the UPDATE without consuming any results.

• fetch (): Transition call declaration to the fetch stage to fetch the number of updated rows.

Deleting Data

You can use the delete() entry point to delete rows. Removing data starts with a specification of the table to delete from and, optionally, accepts a Criteria to create a WHERE clause.

Consider the following simple insert operation:

Mono<Void> delete = databaseClient.delete()
  .from(Person.class)                       (1)
  .matching(where("firstname").is("John")   (2)
    .and("lastname").in("Doe", "White"))
  .then();                                  (3)

1. Using Person with the from(…) method sets the FROM table, based on mapping metadata.

2. The issued query declares a WHERE condition on firstname and lastname columns to filter rows to delete.

3. Use then() to delete rows from an object without consuming further details. Modifying statements also allow consumption of the number of affected rows.

Methods for DELETE operations

The delete() entry point exposes the following additional methods to provide options for the operation:

• from (Class<T>): Specifies the target table by using a mapped object. By default, it returns results as T.

• from (String): Specifies the target table name. By default, it returns results as Map<String, Object>.

• matching (Criteria): Declares a WHERE condition to define the rows to delete.

• then (): Runs the DELETE without consuming any results.

• fetch (): Transition call declaration to the fetch stage to fetch the number of deleted rows.