Derived Queries

Apart from those query methods that are provided by Spring's PagingAndSortingRepository interface, you could 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 auto-created 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 should 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 on 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 could also have other keywords. The following section provides the detailed derived query snippets that would 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 on 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 on 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 auto-created 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 auto-created 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 auto-created by the Spring Data Framework.

For more information on Page, see Page. For more information on 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 auto-created by the Spring Data Framework.

For more information on 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);
}