In the SqlQuery API, we cannot specify the SELECT query, but the SqlFieldsQuery allows us to write the SELECT query. The following SqlFieldsQuery selects the name field of SoccerPlayer:
System.out.println("Find name of each soccer player");
SqlFieldsQuery fieldQry = new SqlFieldsQuery("select name from SoccerPlayer");
When we query an IgniteCache with a SqlFieldQuery, it returns a FieldsQueryCursor with a list of selected rows, and each row contains the list of fields mentioned in the SELECT clause. The following code snippet fetches the names:
FieldsQueryCursor<List<?>> playerNamecursor = playerCache.query(fieldQry);
playerNamecursor.forEach(name -> { System.out.println(name); });
Note that the playerNamecursor contains a list of names. The program prints the following output:
We can use ANSI SQL-99 aggregate functions such as MIN, MAX, AVG, SUM, and COUNT in our field query. The following code snippet fetches the AVG, MAX, and MIN salary:
System.out.println("Find average, max, min salary of players");
fieldQry = new SqlFieldsQuery("select avg(salary), max(salary), min(salary) from SoccerPlayer");
FieldsQueryCursor<List<?>> result = playerCache.query(fieldQry);
result.forEach(r -> {
System.out.println("avg=" + r.get(0) + " max=" + r.get(1) + " , min= " + r.get(2));
} );
The following is the program output:
We can also use the SQL group by clause to fetch the max and min salary for each club. The following field query joins the player and club cache, groups the players by club name, and then finds the max and min salary for each group:
System.out.println("Find max, min salary of players group by club");
fieldQry = new SqlFieldsQuery("select c.name , max(p.salary), min(p.salary) from SoccerPlayer p, "" + CLUB_SQL_CACHE + "".SoccerClub c where p.clubId = c.id group by c.name");
result = playerCache.query(fieldQry);
result.forEach(r -> {
System.out.println("Club =" + r.get(0) + " max=" + r.get(1) + " , min= " + r.get(2));
});
The following is the program output: