In this section, we will use an affinity key to store the related objects together.  We will create two caches: player_sql_cache & club_sql_cache, to store the soccer players and clubs respectively, then use SQL queries to fetch details. The following class represents a SoccerPlayer:

public class SoccerPlayer implements Serializable {
     private static final long serialVersionUID = 1L;
     @QuerySqlField(index=true)
     private Long id;
     @QuerySqlField
     private String name;
     @QuerySqlField
     private double salary;
     @QuerySqlField(index=true)
     private Long clubId;

     public SoccerPlayer(Long id, String name, double salary, Long clubId) {
         super();
         this.id = id;
         this.name = name;
         this.salary = salary;
         this.clubId = clubId;
     }

    @Override
    public String toString() {
         return "Player [id=" + id + ", name=" + name + ", salary=" + salary + ", clubId=" + clubId         + "]";
    }
 
//getters/setters
}

The fields annotated with the @QuerySqlField Java annotation can be queried using the Ignite SQL query and field query API.

The following class represents a soccer club:

public class SoccerClub implements Serializable {
 private static final long serialVersionUID = 1L;
 @QuerySqlField(index = true)
 private final Long id;
 @QuerySqlField
 private final String name;
 public SoccerClub(Long id, String name) {
     super();
     this.id = id;
     this.name = name;
 }
 @Override public String toString() {
   return "Club [id=" + id + ", name=" + name + "]";
 }

//getters/setters
}

We will collocate the clubs and players; we need to create a Key class to store the players. The key class will map a player and a club, and @AffinityKeyMapped will tell Ignite to collocate them. The SoccerPlayerKey  will store a playerId and a clubId, and set the affinity on clubId. We need to override the equals and hashCode methods using the Eclipse/intelliJ plugin as the SoccerPlayerKey will be stored as a key:

public class SoccerPlayerKey implements Serializable {
   private static final long serialVersionUID = 1L;
   private final long playerId;
   @AffinityKeyMapped
   private final long clubId;
   public SoccerPlayerKey(long playerId, long clubId) {
       this.playerId = playerId;
       this.clubId = clubId;
   }
  //equals
  //hashcode
  //toString
}

Now, create a class to populate the cache with a collection of players:

public class SQLQueryCollocatedTest {
 private static final String CLUB_SQL_CACHE = "club_sql_cache";
 private static final String PLAYER_SQL_CACHE = "player_sql_cache";
 public static void main(String[] args) {
    IgniteConfiguration cfg = new IgniteConfiguration();
    cfg.setPeerClassLoadingEnabled(true);

Create a cache configuration for the players with key = SoccerPlayerKey, value = SoccerPlayer, and cache mode = PARTITIONED:

    CacheConfiguration<SoccerPlayerKey, SoccerPlayer> playerCacheConfig = new CacheConfiguration<>();
     playerCacheConfig.setName(PLAYER_SQL_CACHE);
     playerCacheConfig.setIndexedTypes(SoccerPlayerKey.class, SoccerPlayer.class);
     playerCacheConfig.setCacheMode(CacheMode.PARTITIONED);

Create a cache configuration for the clubs with the key = clubId (Long) and value= SoccerClub:

    CacheConfiguration<Long, SoccerClub> clubCacheConfig = new CacheConfiguration<>();
     clubCacheConfig.setName(CLUB_SQL_CACHE);
     clubCacheConfig.setIndexedTypes(Long.class, SoccerClub.class);
     clubCacheConfig.setCacheMode(CacheMode.PARTITIONED);

Set the cache configs to the Ignite configuration and start an Ignite instance:

    cfg.setCacheConfiguration(playerCacheConfig, clubCacheConfig); 
    try (Ignite ignite = Ignition.start(cfg)) {

Create (or get if it already exists) the playerCache and clubCache:

 IgniteCache<SoccerPlayerKey, SoccerPlayer> playerCache = Ignition.ignite()
 .getOrCreateCache(PLAYER_SQL_CACHE);
 IgniteCache<Long, SoccerClub> clubCache = Ignition.ignite().getOrCreateCache(CLUB_SQL_CACHE);

Populate the clubCache with two clubs, Barcelona and Chelsea:

SoccerClub barcelona = new SoccerClub(1l, "Barcelona");
SoccerClub chelsea = new SoccerClub(2l, "Chelsea");
clubCache.put(barcelona.getId(), barcelona);
clubCache.put(chelsea.getId(), chelsea);

Create three soccer players, Suarez and Messi for Barcelona, and Eden Hazard for Chelsea:

 long id = 1;
 SoccerPlayer suarez = new SoccerPlayer(id++, "Luis Suárez", 578699.00d, barcelona.getId());
 SoccerPlayer messi = new SoccerPlayer(id++, "Leo Messi", 200000.00d, barcelona.getId());
 SoccerPlayer hazard = new SoccerPlayer(id++, "Eden Hazard", 178999.00d, chelsea.getId());

Populate the playerCache with the players, and note that the for each player a SoccerPlayerKey is created with the player's IDand his club ID:

playerCache.put(new SoccerPlayerKey(suarez.getId(), suarez.getClubId()), suarez);
playerCache.put(new SoccerPlayerKey(messi.getId(), messi.getClubId()), messi);
playerCache.put(new SoccerPlayerKey(hazard.getId(), hazard.getClubId()), hazard);

Now, we are ready to try out our first SQL query. The next section will explore the SqlQuery API.