Native Queries

Learn to run the native SQL queries using the @oracle.spring.data.nosql.repository.Query annotation.

The @oracle.spring.data.nosql.repository.Query annotation enables you to execute the native SQL query.

public interface AuthorRepository extends NoSQLRepository<Author, Long> {
    @Query(value = "DECLARE $firstName STRING; 
        SELECT * FROM author WHERE first_name = $firstName")
    List<Author> findAuthorsByFirstName(@Param("$firstName") String firstName);

    @Query("DECLARE $firstName STRING; $last STRING; " +
        "SELECT * FROM Customer AS c " +
        "WHERE c.kv_json_.firstName = $firstName AND " +
        "c.kv_json_.lastName = $last")
    List<Customer> findCustomersWithLastAndFirstNosqlValues(
        @Param("$last") StringValue paramLast,
        @Param("$firstName") StringValue firstName
    );
}

Parameters are matched by name using the @org.springframework.data.repository.query.Param annotation. The @Param annotation value field must match exactly, including the '$' char, the name of the declared bind variable. If @Param annotation is not used an exception is thrown. All the parameters will get mapped according to the mapping rules mentioned in the Persistence Model section.

Note:

The second method findAuthorsWithLastAndFirstNosqlValues works with oracle.nosql.driver.values.StringValue. All FieldValue subclasses are supported for query parameters. FieldValue is the base class of all data items in the NoSQL SDK for Java. Each data item is an instance of FieldValue allowing access to its type and its value as well as additional utility methods that operate on FieldValue. On top of that, parameters of type FieldValue are also supported. For more information about FieldValue, see FieldValue.

For details on full query support in the Oracle NoSQL Database, see SQL Reference Guide.