[[r2dbc.repositories.queries]]
= Query Methods

Most of the data access operations you usually trigger on a repository result in a query being run against the databases.
Defining such a query is a matter of declaring a method on the repository interface, as the following example shows:

.PersonRepository with query methods
====
[source,java]
----
interface ReactivePersonRepository extends ReactiveSortingRepository<Person, Long> {

  Flux<Person> findByFirstname(String firstname);                                   <1>

  Flux<Person> findByFirstname(Publisher<String> firstname);                        <2>

  Flux<Person> findByFirstnameOrderByLastname(String firstname, Pageable pageable); <3>

  Mono<Person> findByFirstnameAndLastname(String firstname, String lastname);       <4>

  Mono<Person> findFirstByLastname(String lastname);                                <5>

  @Query("SELECT * FROM person WHERE lastname = :lastname")
  Flux<Person> findByLastname(String lastname);                                     <6>

  @Query("SELECT firstname, lastname FROM person WHERE lastname = $1")
  Mono<Person> findFirstByLastname(String lastname);                                <7>
}
----

<1> The method shows a query for all people with the given `firstname`.
The query is derived by parsing the method name for constraints that can be concatenated with `And` and `Or`.
Thus, the method name results in a query expression of `SELECT … FROM person WHERE firstname = :firstname`.
<2> The method shows a query for all people with the given `firstname` once the `firstname` is emitted by the given `Publisher`.
<3> Use `Pageable` to pass offset and sorting parameters to the database.
<4> Find a single entity for the given criteria.
It completes with `IncorrectResultSizeDataAccessException` on non-unique results.
<5> Unless <4>, the first entity is always emitted even if the query yields more result rows.
<6> The `findByLastname` method shows a query for all people with the given last name.
<7> A query for a single `Person` entity projecting only `firstname` and `lastname` columns.
The annotated query uses native bind markers, which are Postgres bind markers in this example.
====

Note that the columns of a select statement used in a `@Query` annotation must match the names generated by the `NamingStrategy` for the respective property.
If a select statement does not include a matching column, that property is not set.
If that property is required by the persistence constructor, either null or (for primitive types) the default value is provided.

The following table shows the keywords that are supported for query methods:

[cols="1,2,3",options="header",subs="quotes"]
.Supported keywords for query methods
|===
| Keyword
| Sample
| Logical result

| `After`
| `findByBirthdateAfter(Date date)`
| `birthdate > date`

| `GreaterThan`
| `findByAgeGreaterThan(int age)`
| `age > age`

| `GreaterThanEqual`
| `findByAgeGreaterThanEqual(int age)`
| `age >= age`

| `Before`
| `findByBirthdateBefore(Date date)`
| `birthdate < date`

| `LessThan`
| `findByAgeLessThan(int age)`
| `age < age`

| `LessThanEqual`
| `findByAgeLessThanEqual(int age)`
| `age \<= age`

| `Between`
| `findByAgeBetween(int from, int to)`
| `age BETWEEN from AND to`

| `NotBetween`
| `findByAgeNotBetween(int from, int to)`
| `age NOT BETWEEN from AND to`

| `In`
| `findByAgeIn(Collection<Integer> ages)`
| `age IN (age1, age2, ageN)`

| `NotIn`
| `findByAgeNotIn(Collection ages)`
| `age NOT IN (age1, age2, ageN)`

| `IsNotNull`, `NotNull`
| `findByFirstnameNotNull()`
| `firstname IS NOT NULL`

| `IsNull`, `Null`
| `findByFirstnameNull()`
| `firstname IS NULL`

| `Like`, `StartingWith`, `EndingWith`
| `findByFirstnameLike(String name)`
| `firstname LIKE name`

| `NotLike`, `IsNotLike`
| `findByFirstnameNotLike(String name)`
| `firstname NOT LIKE name`

| `Containing` on String
| `findByFirstnameContaining(String name)`
| `firstname LIKE '%' + name +'%'`

| `NotContaining` on String
| `findByFirstnameNotContaining(String name)`
| `firstname NOT LIKE '%' + name +'%'`

| `(No keyword)`
| `findByFirstname(String name)`
| `firstname = name`

| `Not`
| `findByFirstnameNot(String name)`
| `firstname != name`

| `IsTrue`, `True`
| `findByActiveIsTrue()`
| `active IS TRUE`

| `IsFalse`,  `False`
| `findByActiveIsFalse()`
| `active IS FALSE`
|===

[[r2dbc.repositories.modifying]]
== Modifying Queries

The previous sections describe how to declare queries to access a given entity or collection of entities.
Using keywords from the preceding table can be used in conjunction with `delete…By` or `remove…By` to create derived queries that delete matching rows.

.`Delete…By` Query
====
[source,java]
----
interface ReactivePersonRepository extends ReactiveSortingRepository<Person, String> {

  Mono<Integer> deleteByLastname(String lastname);            <1>

  Mono<Void> deletePersonByLastname(String lastname);         <2>

  Mono<Boolean> deletePersonByLastname(String lastname);      <3>
}
----

<1> Using a return type of `Mono<Integer>` returns the number of affected rows.
<2> Using `Void` just reports whether the rows were successfully deleted without emitting a result value.
<3> Using `Boolean` reports whether at least one row was removed.
====

As this approach is feasible for comprehensive custom functionality, you can modify queries that only need parameter binding by annotating the query method with `@Modifying`, as shown in the following example:

[source,java,indent=0]
----
include::example$r2dbc/PersonRepository.java[tags=atModifying]
----

The result of a modifying query can be:

* `Void` (or Kotlin `Unit`) to discard update count and await completion.
* `Integer` or another numeric type emitting the affected rows count.
* `Boolean` to emit whether at least one row was updated.

The `@Modifying` annotation is only relevant in combination with the `@Query` annotation.
Derived custom methods do not require this annotation.

Modifying queries are executed directly against the database.
No events or callbacks get called.
Therefore also fields with auditing annotations do not get updated if they don't get updated in the annotated query.

Alternatively, you can add custom modifying behavior by using the facilities described in xref:repositories/custom-implementations.adoc[Custom Implementations for Spring Data Repositories].

[[r2dbc.repositories.queries.spel]]
=== Queries with SpEL Expressions

Query string definitions can be used together with SpEL expressions to create dynamic queries at runtime.
SpEL expressions can provide predicate values which are evaluated right before running the query.

Expressions expose method arguments through an array that contains all the arguments.
The following query uses `[0]`
to declare the predicate value for `lastname` (which is equivalent to the `:lastname` parameter binding):

[source,java,indent=0]
----
include::example$r2dbc/PersonRepository.java[tags=spel]
----

SpEL in query strings can be a powerful way to enhance queries.
However, they can also accept a broad range of unwanted arguments.
You should make sure to sanitize strings before passing them to the query to avoid unwanted changes to your query.

Expression support is extensible through the Query SPI: `org.springframework.data.spel.spi.EvaluationContextExtension`.
The Query SPI can contribute properties and functions and can customize the root object.
Extensions are retrieved from the application context at the time of SpEL evaluation when the query is built.

TIP: When using SpEL expressions in combination with plain parameters, use named parameter notation instead of native bind markers to ensure a proper binding order.
