How to do an equi-join with a List in GemFire OQL

Products

• VMware Tanzu Gemfire

Issue/Introduction

The purpose of this article is to show how to do a join in GemFire between an Object List and a simple field. Like an integer, as this is not documented in the GemFire User's Guide or shown in the quickstart examples provided with the product installation.

Environment

Resolution

Usually you would do an equi-join between two simple fields of the same type like: select * from /Groups g, /Privileges p where p.Id=g.Id

select * from /Groups g, /Privileges p where p.Id=g.Id

where, the ID field in both regions are of type Integer.

But what if the relation between domain objects is one to many instead of one to one? This could be represented by a List of Integers on one of the objects. In this case it is not possible to join the List and Integer directly without a little tweak of the OQL:

select * from /Groups g, g.privID pid, /Privileges p where p.Id=pid

The general principle for navigating collections is by defining the collection with an alias in the FROM clause and then accessing the contained fields in the Where predicates. This OQL statement will return entries where Privilege ID, which is a simple Integer, is present in the Group List of Integers.

The attached zip contains 6 simple classes that show the join in action.

To run the sample follow these steps:

1. Unpack the zip file and import the classes into STS
2. In the ListJoinLocator change the GemFire system property to reflect the GemFire installation locally
3. Run the ListJoinLocator.
4. Run the ListJoinGemFireServer
5. Run the ListJoinDataPutter to generate test data in the regions
6. Run the ListJoinQueryClient to execute the join query

There are no cache.xml files needed and configuration is done in Java. The server log is written to the project directory.

 

Additional Information

ListJoinClasses.zip (There is no preview available with this file; download and use it as required.)