Skip to content

Latest commit

 

History

History
208 lines (162 loc) · 7.49 KB

r2dbc-sql.adoc

File metadata and controls

208 lines (162 loc) · 7.49 KB

Executing Statements

DatabaseClient provides the basic functionality of running a statement. The following example shows what you need to include for minimal but fully functional code that creates a new table:

Mono<Void> completion = client.execute("CREATE TABLE person (id VARCHAR(255) PRIMARY KEY, name VARCHAR(255), age INTEGER);")
        .then();

DatabaseClient is designed for convenient, fluent usage. It exposes intermediate, continuation, and terminal methods at each stage of the execution specification. The preceding example above uses then() to return a completion Publisher that completes as soon as the query (or queries, if the SQL query contains multiple statements) completes.

Note
 execute(…) accepts either the SQL query string or a query Supplier<String> to defer the actual query creation until execution.

Running Queries

SQL queries can return values or the number of affected rows. DatabaseClient can return the number of updated rows or the rows themselves, depending on the issued query.

The following example shows an UPDATE statement that returns the number of updated rows:

Mono<Integer> affectedRows = client.execute("UPDATE person SET name = 'Joe'")
        .fetch().rowsUpdated();

Running a SELECT query returns a different type of result, in particular tabular results. Tabular data is typically consumed by streaming each Row. You might have noticed the use of fetch() in the previous example. fetch() is a continuation operator that lets you specify how much data you want to consume.

Mono<Map<String, Object>> first = client.execute("SELECT id, name FROM person")
        .fetch().first();

Calling first() returns the first row from the result and discards remaining rows. You can consume data with the following operators:

  • first() return the first row of the entire result.

  • one() returns exactly one result and fails if the result contains more rows.

  • all() returns all rows of the result.

  • rowsUpdated() returns the number of affected rows (INSERT count, UPDATE count).

By default, DatabaseClient queries return their results as Map of column name to value. You can customize type mapping by applying an as(Class<T>) operator, as follows:

Flux<Person> all = client.execute("SELECT id, name FROM mytable")
        .as(Person.class)
        .fetch().all();

as(…) applies Convention-based Object Mapping and maps the resulting columns to your POJO.

Mapping Results

You can customize result extraction beyond Map and POJO result extraction by providing an extractor BiFunction<Row, RowMetadata, T>. The extractor function interacts directly with R2DBC’s Row and RowMetadata objects and can return arbitrary values (singular values, collections and maps, and objects).

The following example extracts the id column and emits its value:

Flux<String> names = client.execute("SELECT name FROM person")
        .map((row, rowMetadata) -> row.get("id", String.class))
        .all();
What about null?

Relational database results can contain null values. The Reactive Streams specification forbids the emission of null values. That requirement mandates proper null handling in the extractor function. While you can obtain null values from a Row, you must not emit a null value. You must wrap any null values in an object (for example, Optional for singular values) to make sure a null value is never returned directly by your extractor function.

Binding Values to Queries

A typical application requires parameterized SQL statements to select or update rows according to some input. These are typically SELECT statements constrained by a WHERE clause or INSERT and UPDATE statements that accept input parameters. Parameterized statements bear the risk of SQL injection if parameters are not escaped properly. DatabaseClient leverages R2DBC’s bind API to eliminate the risk of SQL injection for query parameters. You can provide a parameterized SQL statement with the execute(…) operator and bind parameters to the actual Statement. Your R2DBC driver then executes the statement by using prepared statements and parameter substitution.

Parameter binding supports two binding strategies:

  • By Index, using zero-based parameter indexes.

  • By Name, using the placeholder name.

The following example shows parameter binding for a query:

db.execute("INSERT INTO person (id, name, age) VALUES(:id, :name, :age)")
    .bind("id", "joe")
    .bind("name", "Joe")
    .bind("age", 34);
R2DBC Native Bind Markers

R2DBC uses database-native bind markers that depend on the actual database vendor. As an example, Postgres uses indexed markers, such as $1, $2, $n. Another example is SQL Server, which uses named bind markers prefixed with @.

This is different from JDBC, which requires ? as bind markers. In JDBC, the actual drivers translate ? bind markers to database-native markers as part of their statement execution.

Spring Data R2DBC lets you use native bind markers or named bind markers with the :name syntax.

Named parameter support leverages a R2dbcDialect instance to expand named parameters to native bind markers at the time of query execution, which gives you a certain degree of query portability across various database vendors.

The query-preprocessor unrolls named Collection parameters into a series of bind markers to remove the need of dynamic query creation based on the number of arguments. Nested object arrays are expanded to allow usage of (for example) select lists.

Consider the following query:

SELECT id, name, state FROM table WHERE (name, age) IN (('John', 35), ('Ann', 50))

The preceding query can be parametrized and executed as follows:

List<Object[]> tuples = new ArrayList<>();
tuples.add(new Object[] {"John", 35});
tuples.add(new Object[] {"Ann",  50});

db.execute("SELECT id, name, state FROM table WHERE (name, age) IN (:tuples)")
    .bind("tuples", tuples)
Note
 Usage of select lists is vendor-dependent.

The following example shows a simpler variant using IN predicates:

db.execute("SELECT id, name, state FROM table WHERE age IN (:ages)")
    .bind("ages", Arrays.asList(35, 50))

Statement Filters

You can register a Statement filter (StatementFilterFunction) through DatabaseClient to intercept and modify statements in their execution, as the following example shows:

db.execute("INSERT INTO table (name, state) VALUES(:name, :state)")
    .filter((s, next) -> next.execute(s.returnGeneratedValues("id")))
    .bind("name", …)
    .bind("state", …)

DatabaseClient exposes also simplified filter(…) overload accepting UnaryOperator<Statement>:

db.execute("INSERT INTO table (name, state) VALUES(:name, :state)")
    .filter(s -> s.returnGeneratedValues("id"))
    .bind("name", …)
    .bind("state", …)

db.execute("SELECT id, name, state FROM table")
    .filter(s -> s.fetchSize(25))

StatementFilterFunction allow filtering of the executed Statement and filtering of Result objects.