How to write dynamic sql in Spring Data Azure Cosmos DB

user_888263 0
Viewed 1122

We are planning to use Spring Data Azure Cosmos DB v3. I see that we extend CosmosRepository interface and implement our repo and apply @Query annotation

@Query(value = "select * from c where c.firstName = @firstName and c.lastName = @lastName")
        List<User> getUsersByTitleAndValue(@Param("firstName") int firstName, @Param("lastName") String lastName);

But we have a need to generate the sql used in @Query dynamically

E.g

 String sql = "Select * from some table t"
    
    if(someCondition){
    sql.append(" where t.name="+ nameVar);
    }

How to achieve this in Spring Data Azure Cosmos DB v3 ?

spring-data azure-cosmosdb
2 Answers

you can do something like this:-

@Query(value = "select * from c where (@firstName=null or c.firstName=@firstName) and (@lastName=null or c.lastName=@lastName)")
        List<User> getUsersByFirstNameAndLastName(@Param("firstName") String firstName, @Param("lastName") String lastName);

This way it will ignore null parameters.

user_46301130

Spring Data Azure Cosmos DB exposes Azure Cosmos DB SQL API through the standard interface of the Spring Data framework. Spring Data framework is not oriented toward dynamic queries, in the sense that there is not a direct way to execute an arbitrary query string against the underlying datastore while staying within Spring Data's conventions. These sources suggest two ways to build dynamic queries programmatically or to define them as mappings:

https://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl/ http://shazsterblog.blogspot.com/2017/01/spring-data-jpa-with-dynamic-where.html

Alternatively - Spring Data Azure Cosmos DB is built on top of Azure Cosmos DB Java SDK for SQL API, which is our native Java SDK:

https://docs.microsoft.com/en-us/azure/cosmos-db/sql-api-sdk-java-v4

You can pull in a Java bean for our native SDK and use the native SDK for your dynamic queries.

user_130518710
Related