Part 4 - Query methods

Following the previous part in our Spring Data demo, where we had a look at derived queries, we will now take a look at repository methods with self written AQL.

When it comes to more complex use cases where a derived method would get way too long and become unreadable, queries using ArangoDB Query Language (AQL) can be supplied with the @Query annotation on methods in our repositories.

AQL supports the usage of bind parameters, thus allowing to separate the query text from literal values used in the query. There are three ways of passing bind parameters to the query in the @Query annotation.

Param annotation

To pass bind parameters to our query we can use the @Param annotation. With the @Param annotation, the argument will be placed in the query at the place corresponding to the value passed to the @Param annotation.

To demonstrate this we add another method to CharacterRepository:

@Query("FOR c IN characters FILTER c.surname == @surname SORT c.age ASC RETURN c")
Iterable getWithSurname(@Param("surname") String value);

Here we named our bind parameter surname and annotated our method parameter value with @Param("surname"). Only the value in @Param annotation has to match with our bind parameter, the method parameter name does not matter.

As you can see we used the collection name characters and not character in our query. Normally a collection would be named like the corresponding entity class. But as you probably remember we used @Document("characters") in Character which set the collection name to characters.

Now we create a new CommandLineRunner and add it to our DemoApplication.

package com.arangodb.spring.demo.runner;
 
import com.arangodb.spring.demo.entity.Character;
import com.arangodb.spring.demo.repository.CharacterRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
 
public class AQLRunner implements CommandLineRunner {
 
    @Autowired
    private CharacterRepository repository;
 
    @Override
    public void run(final String... args) throws Exception {
        System.out.println("# AQL queries");
 
        System.out.println("## Find all characters which are older than 21 (sort descending)");
        final Iterable older = repository.getOlderThan(21);
        older.forEach(System.out::println);
 
    }
 
}
DemoApplication:


Class[]runner=new Class[]{
        CrudRunner.class,
        ByExampleRunner.class,
        DerivedQueryRunner.class,
        RelationsRunner.class,
        AQLRunner.class
};

Add the following lines to AQLRunner.

System.out.println("## Find all characters with surname 'Lannister' (sort by age ascending)");
Iterable lannisters = repository.getWithSurname("Lannister");
lannisters.forEach(System.out::println);

The console output should give you all characters with surname Lannister.

## Find all characters with surname 'Lannister' (sort by age ascending)
Character [id=7613, name=Tywin, surname=Lannister, alive=false, age=null]
Character [id=7611, name=Tyrion, surname=Lannister, alive=true, age=32]
Character [id=7596, name=Jaime, surname=Lannister, alive=true, age=36]
Character [id=7598, name=Cersei, surname=Lannister, alive=true, age=36]

BindVars annotation

In addition to annotation @Param we can use a method parameter of type Map<String, Object> annotated with @BindVars as our bind parameters. We can then fill the map with any parameter used in the query.

Add to CharacterRepository:

@Query("FOR c IN @@col FILTER c.surname == @surname AND c.age > @age RETURN c")
Iterable getWithSurnameOlderThan(@Param("age") int value, @BindVars Map bindvars);

In this query we used three bind parameter @@col, @surname and @age. As you probably recognize one of our bind parameter is written with two @. This is a special type of bind parameter exists for injecting collection names. This type of bind parameter has a name prefixed with an additional @ symbol.

Furthermore, we can see that we used @Param for our bind parameter @age but not for @@col and @surname. These bind parameters have to be passed through the map annotated with @BindVars. It is also possible to use both annotations within one query method.

The method call looks as expected. We pass an integer for the bind parameter age and a map with the keys surname and @col to our new method.

System.out.println("## Find all characters with surname 'Lannister' which are older than 35");
Map bindvars = new HashMap<>();
bindvars.put("surname", "Lannister");
bindvars.put("@col", Character.class);
Iterable oldLannisters = repository.getWithSurnameOlderThan(35, bindvars);
oldLannisters.forEach(System.out::println);

One additional special handling for collection bind parameter is that we do not have to pass the collection name as a String to our method. We can pass the type – Character.class – to our method. Spring Data ArangoDB will then determine the collection name. This is very convenient if you have used an alternative collection name within the annotations @Document or @Edge.

The console output should be:

## Find all characters with surname 'Lannister' which are older than 35
Character [id=8294, name=Jaime, surname=Lannister, alive=true, age=36]
Character [id=8296, name=Cersei, surname=Lannister, alive=true, age=36]

QueryOptions annotation

Sometimes you want to be able to configure the query execution on a technical level. For this Spring Data ArangoDB provides the @QueryOptions annotation. With this annotation you are able to set something like a batch size to control the number of results to be transferred from the database server in one roundtrip and some other things.

For our example we want to return the number of found results. To achieve that we have to change the return type in our previously created method getWithSurnameOlderThan(int, Map): from Iterable<Character> to ArangoCursor<Character>. ArangoCursor provides a method getCount() which gives us the number of found results. But this value is only returned from the database when we set the flag count in our query options to true, so we also have to add the QueryOptions annotation to our method with count = true.

@Query("FOR c IN @@col FILTER c.surname == @surname AND c.age > @age RETURN c")
@QueryOptions(count = true)
Iterable getWithSurnameOlderThan(@Param("age") int value, @BindVars Map bindvars);

Now, when we change the type of our local variable oldLannisters in AQLRunner to ArangoCursor we can get the count value from it.

ArangoCursor oldLannisters = repository.getWithSurnameOlderThan(35, bindvars);
System.out.println(String.format("Found %s documents", oldLannisters.getCount()));
oldLannisters.forEach(System.out::println);

Our new console output should then look like this:

## Find all characters with surname 'Lannister' which are older than 35
Found 2 documents
Character [id=9012, name=Jaime, surname=Lannister, alive=true, age=36]
Character [id=9014, name=Cersei, surname=Lannister, alive=true, age=36]

Graph traversal

Let’s finish the query method topic of our demo with a graph traversal written in AQL where our edge ChildOf is involved.

The following query searches for every Character connected (through ChildOf) with the character to whom the passed id belongs to. This time we specified the edge collection within the query which we pass as a bind parameter with the @Param annotation.

CharacterRepository:

@Query("FOR v IN 1..2 INBOUND @arangoId @@edgeCol SORT v.age DESC RETURN DISTINCT v")
Set getAllChildsAndGrandchilds(@Param("arangoId") String arangoId, @Param("@edgeCol") Class edgeCollection);

Like we did before with Character.class in our map we use the type of ChildOf as parameter value. Because we want to find all children and grantchildren of Tywin Lannister we first have to find him to get his id which we can then pass to our query method.

AQLRunner:

System.out.println("## Find all childs and grantchilds of 'Tywin Lannister' (sort by age descending)");
repository.findByNameAndSurname("Tywin", "Lannister").ifPresent(tywin -> {
    Set childs = repository.getAllChildsAndGrandchilds(tywin.getArangoId(), ChildOf.class);
    childs.forEach(System.out::println);
});

After executing the demo again we can see the following console output:

## Find all childs and grantchilds of 'Tywin Lannister' (sort by age descending)
Character [id=11255, name=Tyrion, surname=Lannister, alive=true, age=32]
Character [id=11242, name=Cersei, surname=Lannister, alive=true, age=36]
Character [id=11253, name=Joffrey, surname=Baratheon, alive=false, age=19]
Character [id=11240, name=Jaime, surname=Lannister, alive=true, age=36]