Previous Next JavaScript must be enabled to correctly display this content

## Derived Queries

Learn about the customized query creation feature supported by the Spring Data Framework.

Apart from those query methods that are provided by Spring's `PagingAndSortingRepository` interface, you can also define derived queries. Spring Data Framework has an inbuilt query creation feature. Spring Data Framework creates queries directly from the Java method name alone.

For example, if we have a Java method name with the following construct,

    List<Customer> findByFirstName(String firstName);

then the following derived query will be created automatically by the Spring Data Framework.

    declare $firstName String;

    SELECT * FROM Customer AS c WHERE c.kv_json_.firstName = $firstName;

The only requirement for this derived query to work is that this Java method must be defined in the interface that extends the `NosqlRepository` interface. The `NosqlRepository` interface extends the `Repository` interface which is responsible for the derived queries. The common prefixes from the Java method name are removed and the constraints of the query are parsed from the rest of the Java method name. For more information about Spring derived query creation, see Query Creation.

The Java methods with the prefixes `find…By`, `read…By`, `query…By`, `count…By`, `get…By`, `exists…By`, `delete…By`, and `remove…By` are considered as derived query methods by Spring Data Framework. Apart from these prefixes, the Java method name can also have other keywords. The following section provides the detailed derived query snippets that will be generated if the given keywords are used.

`And`

If a method name has the word `and` in the following construct,

    Iterable<Student> findByFirstNameAndLastName(String firstname, String lastname);

then the following derived query will be auto-created by the Spring Data Framework.

    declare $p_firstName String;
    $p_lastName String;

    SELECT * FROM Student AS s WHERE (
        s.kv_json_.firstName = $p_firstName AND s.kv_json_.lastName = $p_lastName)

Note:

The Oracle NoSQL Database SDK for Spring Data supports derived queries that use a combination of the logical operators (`and`, `or`). The generated query will follow the rules of operator precedence defined in the Oracle NoSQL Database SQL query language. For more information about the operator precedence in the Oracle NoSQL Database SQL query language, see Operator Precedence in the SQL Reference Guide.

`Or`

If a method name has the word or in the following construct,

    Iterable<Student> findByFirstNameOrLastName(String firstname, String lastname);

then the following derived query will be auto-created by the Spring Data Framework.

    declare $p_firstName String;
    $p_lastName String;

    SELECT * FROM Student AS s WHERE (
        s.kv_json_.firstName = $p_firstName OR s.kv_json_.lastName = $p_lastName)

Note:

The Oracle NoSQL Database SDK for Spring Data supports derived queries that use a combination of the logical operators (`and`, `or`). The generated query will follow the rules of operator precedence defined in the Oracle NoSQL Database SQL query language. For more information about the operator precedence in the Oracle NoSQL Database SQL query language, see Operator Precedence in the SQL Reference Guide.

`OrderBy (Asc/Desc)`

If a method name has the word orderby in the following construct,

    Iterable<Student> findByLastNameOrderByFirstNameAsc(String lastname);

then the following derived query will be created automatically by the Spring Data Framework.

    declare $p_lastName String;

    SELECT * FROM Student AS s
        WHERE s.kv_json_.lastName = $p_lastName ORDER BY s.kv_json_.firstName ASC

If a method name has the word orderby in the following construct,

    Iterable<Student> findByLastNameOrderByFirstNameDesc(String lastname);

then the following derived query will be created automatically by the Spring Data Framework.

    declare $p_lastName String;

    SELECT * FROM Student AS s
        WHERE s.kv_json_.lastName = $p_lastName ORDER BY s.kv_json_.firstName DESC

`First`

If a method name has the word first in the following construct,

    Page<Student> queryFirst5ByLastname(String lastname, Pageable pageable);

then the following derived query will be created automatically by the Spring Data Framework.

For more information about `Page`, see Page. For more information about `Pageable`, see Pageable.

    declare $p_lastName String;
    $kv_limit_ Long;
    $kv_offset_ Long;

    SELECT * FROM Student AS s
        WHERE s.kv_json_.lastName = $p_lastName LIMIT $kv_limit_ OFFSET $kv_offset_

`Top`

If a method name has the word top in the following construct,

    Slice<Student> findTop10ByLastName(String lastname, Pageable pageable);

then the following derived query will be created automatically by the Spring Data Framework.

For more information about `Slice`, see Slice.

    declare $p_lastName String;
    $kv_limit_ Long;
    $kv_offset_ Long;

    SELECT * FROM Student AS s
        WHERE s.kv_json_.lastName = $p_lastName LIMIT $kv_limit_ OFFSET $kv_offset_

For the complete list of supported keywords in query methods in Oracle NoSQL Database SDK for Spring Data, see Supported Keywords in Query Method.

The following is an example of an Oracle NoSQL Database repository. It must extend the `NosqlRepository` interface. The bounded types represent the entity type and the data type of the `ID` field.

    interface PersonRepository extends NosqlRepository<Person, Long> {
        List<Person> findByFirstNameAndLastName(String firstname, String lastname);
        List<Person> findByLastNameOrderByFirstNameDesc(String lastname);
    }