Part 3 - Derived queries and relations

Following the previous part in our Spring Data demo, where we had a look at CRUD operations, we will now take a look at AQL queries, which will be derived from custom methods within our repository interfaces.

Derived queries

Spring Data ArangoDB supports Queries derived from methods names by splitting it into its semantic parts and converting into AQL. The mechanism strips the prefixes find..By, get..By, query..By, read..By, stream..By, count..By, exists..By, delete..By, remove..By from the method and parses the rest. The “By” acts as a separator to indicate the start of the criteria for the query to be built. You can define conditions on entity properties and concatenate them with And and Or.

The complete list of part types for derived queries can be found here.

Simple findBy

Let’s start with an easy example. We want to find characters based on their surname.

The only thing we have to do is to add a method fundBySurname(String) to our CharacterRepository with a return type which allows the method to return multiple instances of Character. For more information on which return types are possible, take a look here.

public interface CharacterRepository extends ArangoRepository {
    Iterable findBySurname(String surname);
}

After we extended our repository we create a new CommandLineRunner and add it to our DemoApplication.

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

In the run() method we call our new method findBySurname(String) and try to find all characters with the surname ‘Lannister’.

package com.arangodb.spring.demo.runner;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.context.annotation.ComponentScan;
 
import com.arangodb.spring.demo.entity.Character;
import com.arangodb.spring.demo.repository.CharacterRepository;
 
@ComponentScan("com.arangodb.spring.demo")
public class DerivedQueryRunner implements CommandLineRunner {
 
    @Autowired
    private CharacterRepository repository;
 
    @Override
    public void run(final String... args) throws Exception {
        System.out.println("# Derived queries");
 
        System.out.println("## Find all characters with surname 'Lannister'");
        Iterable lannisters = repository.findBySurname("Lannister");
        lannisters.forEach(System.out::println);
    }
}

After executing the demo application we should see the following lines within our console output.

# Derived queries
## Find all characters with surname 'Lannister'
Character [id=238, name=Jaime, surname=Lannister, alive=true, age=36]
Character [id=240, name=Cersei, surname=Lannister, alive=true, age=36]
Character [id=253, name=Tyrion, surname=Lannister, alive=true, age=32]
Character [id=255, name=Tywin, surname=Lannister, alive=false, age=null]

Create an index

Indexes allow fast access to documents, provided the indexed attribute(s) are used in a query. To make findBySurname query faster, we can create an index on the surname field, adding the @PersistentIndex to the Character class:

@Document("characters")
@PersistentIndex(fields = {"surname"})
public class Character {

Next time we run our demo the related queries will benefit from the index and avoid performing a full collection scan.

More complex findBy

Now we’re creating some methods with more parts and have a look how they fit together. Lets also use some different return types. Again we simply add the methods in our CharacterRepository.

Collection findTop2DistinctBySurnameIgnoreCaseOrderByAgeDesc(String surname);
List findBySurnameEndsWithAndAgeBetweenAndNameInAllIgnoreCase(
        String suffix,
        int lowerBound,
        int upperBound,
        String[]nameList);

And the method calls in DerivedMethodRunner.

System.out.println("## Find top 2 Lannnisters ordered by age");
Collection top2 = repository.findTop2DistinctBySurnameIgnoreCaseOrderByAgeDesc("lannister");
top2.forEach(System.out::println);
 
System.out.println("## Find all characters which name is 'Bran' or 'Sansa' and it's surname ends with 'ark' and are between 10 and 16 years old");
List youngStarks = repository.findBySurnameEndsWithAndAgeBetweenAndNameInAllIgnoreCase("ark", 10, 16, new String[]{"Bran", "Sansa"});
youngStarks.forEach(System.out::println);

The new methods produce the following console output:

## Find top 2 Lannnisters ordered by age
Character [id=444, name=Jaime, surname=Lannister, alive=true, age=36]
Character [id=446, name=Cersei, surname=Lannister, alive=true, age=36]
## Find all characters which name is 'Bran' or 'Sansa' and it's surname ends with 'ark' and are between 10 and 16 years old
Character [id=452, name=Sansa, surname=Stark, alive=true, age=13]
Character [id=456, name=Bran, surname=Stark, alive=true, age=10]

Single entity result

With derived queries we can not only query for multiple entities, but also for single entities. If we expect only a single entity as the result we can use the corresponding return type.

Because we have a unique hash index on the fields name and surname we can expect only a single entity when we query for both.

For this example we add the method findByNameAndSurname(String, String) in CharacterRepository which return type is Character.

Character findByNameAndSurname(String name, String surname);

When we add the method call in DerivedMethodRunner we should take care of null as a possible return value.

System.out.println("## Find a single character by name & surname");
Character tyrion = repository.findByNameAndSurname("Tyrion", "Lannister");
if(tyrion != null){
    System.out.println(String.format("Found %s", tyrion));
}

At this point it is possible and recommended to use Optional<T> which was introduced with Java 8.

CharacterRepository:

Optional findByNameAndSurname(String name, String surname);

DerivedMethodRunner:

System.out.println("## Find a single character by name & surname");
Optional tyrion = repository.findByNameAndSurname("Tyrion", "Lannister");
tyrion.ifPresent(c -> System.out.println(String.format("Found %s", c)));

The console output should in both cases look like this:

##Found Character [id=974, name=Tyrion, surname=Lannister, alive=true, age=32]

countBy

Aside from findBy there are other prefixes supported – like countBy. In comparison to the previously used operations.collection(Character.class).count(); the countBy is able to include filter conditions.

With the following lines of code we are able to only count characters which are still alive.
CharacterRepository:

1	Integer countByAliveTrue();

DerivedMethodRunner:

System.out.println("## Count how many characters are still alive");
Integer alive = repository.countByAliveTrue();
System.out.println(String.format("There are %s characters still alive", alive));

removeBy

The last example for derived queries is removeBy. Here we remove all characters except those whose surname is ‘Stark’ and who are still alive.
CharacterRepository:

void removeBySurnameNotLikeOrAliveFalse(String surname);

DerivedMethodRunner:

SSystem.out.println("## Remove all characters except of which surname is 'Stark' and which are still alive");
repository.removeBySurnameNotLikeOrAliveFalse("Stark");
repository.findAll().forEach(System.out::println);

We expect only Arya, Bran and Sansa to be left.

## Remove all characters except of which surname is 'Stark' and which are still alive
Character [id=1453, name=Sansa, surname=Stark, alive=true, age=13]
Character [id=1454, name=Arya, surname=Stark, alive=true, age=11]
Character [id=1457, name=Bran, surname=Stark, alive=true, age=10]

Relations

Because ArangoDB as a multi-model database providing graphs as one of the key features, Spring Data ArangoDB also supports a feature set for it.

With the @Relations annotation we can define relationships between our entities. To demonstrate this we use our previously created entity Character.

First we have to add a field Collection<Character> childs annotated with @Relations(edges = ChildOf.class, lazy = true) to Character.

@Document("characters")
@PersistentIndex(fields = {"surname"})
public class Character {
 
    @Id // db document field: _key
    private String id;
 
    @ArangoId // db document field: _id
    private String arangoId;
 
    private String name;
    private String surname;
    private boolean alive;
    private Integer age;
    @Relations(edges = ChildOf.class, lazy = true)
    private Collection childs;
 
    // ...
 
}

Then we have to create an entity for the edge we stated in @Relations. Other than a normal entity annotated with @Document this entity will be annotated with @Edge. This allows Spring Data ArangoDB to create a edge collection in the database. Just like Character, ChildOf will also get a field for its id. To connect two Character entities it also gets a field from type Character annotated with @From and a field from type Character annotated with @To. ChildOf will be persisted in the database with the ids of these two Character.

package com.arangodb.spring.demo.entity;
 
import com.arangodb.springframework.annotation.Edge;
import com.arangodb.springframework.annotation.From;
import com.arangodb.springframework.annotation.To;
import org.springframework.data.annotation.Id;
 
@Edge
public class ChildOf {
 
    @Id
    private String id;
 
    @From
    private Character child;
 
    @To
    private Character parent;
 
    public ChildOf(final Character child, final Character parent) {
        super();
        this.child = child;
        this.parent = parent;
    }
 
    // setter & getter
 
    @Override
    public String toString() {
        return "ChildOf [id=" + id + ", child=" + child + ", parent=" + parent + "]";
    }
 
}

To save instances of ChildOf in the database we also create a repository for it the same way we created CharacterRepository.

package com.arangodb.spring.demo.repository;
 
import com.arangodb.spring.demo.entity.ChildOf;
import com.arangodb.springframework.repository.ArangoRepository;
 
public interface ChildOfRepository extends ArangoRepository {
 
}

Now we implement another CommandLineRunner called RelationsRunner and add it to our DemoApplication like we did with all the runners before.

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

In the newly created RelationsRunner we inject CharacterRepository and ChildOfRepository and built our relations. First we have to save some characters because we removed most of them within the previous chapter of this demo. To do so we use the static createCharacter() method from our CrudRunner. After we have successfully persisted our characters we want to save some relationships with our edge entity ChildOf. Because ChildOf requires instances of Character with id field set from the database we first have to find them in our CharacterRepository. To ensure we find the correct Character we use the derived query method findByNameAndSurename(String, String) which gives us one specific Character. Then we create instances of ChildOf and save them through ChildOfRepository.

 
import com.arangodb.spring.demo.entity.ChildOf;
import com.arangodb.spring.demo.repository.CharacterRepository;
import com.arangodb.spring.demo.repository.ChildOfRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.context.annotation.ComponentScan;
 
import java.util.Arrays;
 
@ComponentScan("com.arangodb.spring.demo")
public class RelationsRunner implements CommandLineRunner {
 
    @Autowired
    private CharacterRepository characterRepo;
    @Autowired
    private ChildOfRepository childOfRepo;
 
    @Override
    public void run(final String... args) throws Exception {
        System.out.println("# Relations");
        characterRepo.saveAll(CrudRunner.createCharacters());
 
        // first create some relations for the Starks and Lannisters
        characterRepo.findByNameAndSurname("Ned", "Stark").ifPresent(ned -> {
            characterRepo.findByNameAndSurname("Catelyn", "Stark").ifPresent(catelyn -> {
                characterRepo.findByNameAndSurname("Robb", "Stark").ifPresent(robb -> childOfRepo.saveAll(Arrays.asList(new ChildOf(robb, ned), new ChildOf(robb, catelyn))));
                characterRepo.findByNameAndSurname("Sansa", "Stark").ifPresent(sansa -> childOfRepo.saveAll(Arrays.asList(new ChildOf(sansa, ned), new ChildOf(sansa, catelyn))));
                characterRepo.findByNameAndSurname("Arya", "Stark").ifPresent(arya -> childOfRepo.saveAll(Arrays.asList(new ChildOf(arya, ned), new ChildOf(arya, catelyn))));
                characterRepo.findByNameAndSurname("Bran", "Stark").ifPresent(bran -> childOfRepo.saveAll(Arrays.asList(new ChildOf(bran, ned), new ChildOf(bran, catelyn))));
            });
            characterRepo.findByNameAndSurname("Jon", "Snow")
                    .ifPresent(bran -> childOfRepo.save(new ChildOf(bran, ned)));
        });
 
        characterRepo.findByNameAndSurname("Tywin", "Lannister").ifPresent(tywin -> {
            characterRepo.findByNameAndSurname("Jaime", "Lannister").ifPresent(jaime -> {
                childOfRepo.save(new ChildOf(jaime, tywin));
                characterRepo.findByNameAndSurname("Cersei", "Lannister").ifPresent(cersei -> {
                    childOfRepo.save(new ChildOf(cersei, tywin));
                    characterRepo.findByNameAndSurname("Joffrey", "Baratheon").ifPresent(joffrey -> childOfRepo.saveAll(Arrays.asList(new ChildOf(joffrey, jaime), new ChildOf(joffrey, cersei))));
                });
            });
            characterRepo.findByNameAndSurname("Tyrion", "Lannister")
                    .ifPresent(tyrion -> childOfRepo.save(new ChildOf(tyrion, tywin)));
        });
    }
}

Read relations within an entity

After we add @Relations(edges = ChildOf.class, lazy = true) Collection<Character> childs; in Character we can now load all children of a character when we fetch the character from the database. Let’s again use the method findByNameAndSurname(String, String) to find one specific character.

Add the following lines of code to the run() method of RelationsRunner.

characterRepo.findByNameAndSurname("Ned", "Stark").ifPresent(nedStark -> {
    System.out.println(String.format("## These are the childs of %s:", nedStark));
    nedStark.getChilds().forEach(System.out::println);
});

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

## These are the childs of Character [id=2547, name=Ned, surname=Stark, alive=false, age=41]:
Character [id=2488, name=Bran, surname=Stark, alive=true, age=10]
Character [id=2485, name=Arya, surname=Stark, alive=true, age=11]
Character [id=2559, name=Robb, surname=Stark, alive=false, age=null]
Character [id=2556, name=Jon, surname=Snow, alive=true, age=16]
Character [id=2484, name=Sansa, surname=Stark, alive=true, age=13]

findBy including relations

The field childs is not persisted in the character entity itself, it is represented by the edge ChildOf. Nevertheless, we can write a derived method which includes properties of all connected Character.

With the following two methods – added in CharacterRepository – we can query for Character which has a child with a given name and Character which has a child in an age between two given integers.

Iterable findByChildsName(String name);
 
Iterable findByChildsAgeBetween(int lowerBound, int upperBound);

Now we add a method that calls in RelationsRunner and search for all parents of ‘Sansa’ and all parents which have a child between 16 and 20 years old.

System.out.println("## These are the parents of 'Sansa'");
Iterable parentsOfSansa = characterRepo.findByChildsName("Sansa");
parentsOfSansa.forEach(System.out::println);
 
System.out.println("## These parents have a child which is between 16 and 20 years old");
Iterable childsBetween16a20 = characterRepo.findByChildsAgeBetween(16, 20);
childsBetween16a20.forEach(System.out::println);

The console output shows us that Ned and Catelyn are the parents of Sansa and that Ned, Jamie and Cersei have at least one child in the age between 16 and 20 years.

## These are the parents of 'Sansa'
Character [id=2995, name=Ned, surname=Stark, alive=false, age=41]
Character [id=2998, name=Catelyn, surname=Stark, alive=false, age=40]
## These parents have a child which is between 16 and 20 years old
Character [id=2995, name=Ned, surname=Stark, alive=false, age=41]
Character [id=2997, name=Jaime, surname=Lannister, alive=true, age=36]
Character [id=2999, name=Cersei, surname=Lannister, alive=true, age=36]