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 methodfindAuthorsWithLastAndFirstNosqlValues
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.