I am using Spring Data GemFire in a project and I am using a Repository to fetch query results from a cache as mentioned here: https://docs.spring.io/spring-gemfire/docs/1.3.3.RELEASE/reference/html/gemfire-repositories.html
Below is my Repository interface:
@Repository
@Region("someRegion")
public interface SomeRegionRepository extends GemfireRepository<CustomObject, Object> {
//Below works when id exists in gemfire region.
//But when id does not exists it
//gives java.lang.IllegalStateException: Unsupported query
@Query("select a.num from /someRegion a where a.id = $1")
Integer getNumById(String id);
//Below returns CustomObject instance when id exists in gemfire region.
//But when id does not exists it returns null (Expected)
CustomObject findById(String id);
}
Is there any way I can return 0 or some other value when there is no data found for that key in OQL query?
Or, is there any way I can get a null value so that I can handle it in the caller code when there is no data?
There is something called NVL (which allows returning other value/expressions in case first expression evaluates to null) in OQL specified here: https://gemfire.docs.pivotal.io/97/geode/developing/query_select/the_select_statement.html . But I am not able to get the right syntax of how to use it.
Please help. Thanks.
First, one house keeping item before we begin...
I see that you referenced Pivotal GemFire 9.7 in your GemFire documentation reference above, but then reference Spring Data GemFire (SDG) 1.3.3.RELEASE reference docs. I certainly hope you are not trying to use SDG 1.3.3.RELEASE with Pivotal GemFire 9.7. SDG 1.3.3 is quite date, based on Pivotal GemFire 7.0.1, and is no longer supported.
I suspect you are not using SDG 1.3.x, therefore you should probably always reference the latest documentation, available here, and specifically, here. Even a Google Search reveals the latest docs.
Also, you can refer to the Spring Data for Pivotal GemFire Version Compatibility Matrix for more details. Anyway...
So, I wrote a test class to better understand your UC.
In my test, I modeled a Person class with a queryable age field/property. I have a PersonRepository for the Person type, and I wrote a very similar query to your example above, to query a person by age, in an usual as well as null-safe manner.
The thing is, your desire to want to protect against the absence of results by returning null or 0 is ambiguous in the case of Repositories.
For 1, if you had returned multiple results (e.g. as in SELECT a.num FROM /SomeRegion a; i.e. without a predicate), and the results were not ordered, then you would have no way to know which value was null for which key unless you also returned the key in the result set.
Of course, that is not the case here and you did qualify the query by ID using a predicate (i.e. ... WHERE a.id = $1). However, in this case, it is not clear when looking at the query (i.e. SELECT a.num FROM ...), whether there was no result for the given ID (i.e. Key) or that simply num was null. You really don't know.
My test goes on to illustrate this point.
I have 2 people ["Jon Doe", "Jane Doe"], here. Jon has a declared age and Jane does not. Of course, both people exist in the cache (i.e. "People" Region).
When I query for Jon and assert his age, I get the expected result.
Alternatively, when I query for Jane and assert her age, I can get 1 of two values, either null or 0, as expected here (currently 0 because I am using the null-safe query). If I change the query to the usual query, then the return value for Jane's age would in fact be null, and I could assert as such.
However, when we get down to querying for a non-existing person, the behavior of GemFire is clear; there are no results. I illustrated this 2 different ways (other than using a Repository), using GemFire's QueryService API directly, and then again using SDG's convenient GemfireTemplate class, which simply wraps the GemFire Query API (it is also the method used by the Repository under the hood; really, the value Repositories add is the mapping/conversion capabilities).
As you can see, I must handle the presence of no results, for example.
In the the case of the Repository, because you have used a custom query, it is not immediately apparent to the Repository infrastructure, which part (i.e. intermediate result of the query) was null.
What if you had SELECT person.address.city.name FROM ...?
Is person null, address null or city null? How should this be handled consistently? The person might exit but the address might be null, which might warrant different behavior than if either person or city were null.
In fact, the Repository abstraction does handle null return values, as can be seen here.
So, where do we go from here?
We have several options:
You could perform and existence check, first...
return personRepository.existsById(bobDoe.getName()) ? personRepository.getAge(bobDoe.getName()) : 0;
You could handle this outside of a Repository in a DAO (perhaps adapting/decorating the Repository and delegating for simple query cases, rather than complex queries that involve using the @Query annotation, which should be used sparingly).
@Repository class PersonDao {
@Autowired
private PersonRepository personRepository;
Person findById(String name) {
return this.personRepository.findById(name).orElse(null);
}
Integer getAge(String name) {
// Either use the approach in #1, or do https://github.com/jxblum/contacts-application/blob/master/tests-example/src/test/java/example/tests/spring/data/gemfire/NullValueReturningRepositoryQueryMethodIntegrationTests.java#L143-L151.
}
...
}
Still
Another option would be for us to provide additional support in SD, something along the lines of...
@Nullable Integer getAge(String name);
Or perhaps...
Optional<Integer> getAge(String name);
Still this approach does not solve the ambiguity problem and will require more thought.
What would be really nice is if OQL handle a Elvis operator, something like (in my more complex example above)...
SELECT person?.address?.city?.name FROM /People ...
Anyway, I hope this gives you some ideas. I will need to think on #3 above more before moving forward.
If you have additional questions/feedback/ideas, please provide them in the comments or feel free to file a JIRA ticket.
Thanks!