The Price of all “Rest with Spring” course packages will increase by $50 next Friday:

>>> GET ACCESS NOW

1. Overview

In this tutorial, we’re looking at building a query language for a REST API using Spring Data JPA and Querydsl.

In the first two articles of this series, we built the same search/filtering functionality using JPA Criteria and Spring Data JPA Specifications.

So – why a query language? Because – for any complex enough API – searching/filtering your resources by very simple fields is simply not enough. A query language is more flexible, and allows you to filter down to exactly the resources you need.

2. Querydsl Configuration

First – let’s see how to configure our project to use Querydsl.

We need to add the following dependencies to pom.xml:

<dependency> 
    <groupId>com.querydsl</groupId> 
    <artifactId>querydsl-apt</artifactId> 
    <version>4.1.4</version>
    </dependency>
<dependency> 
    <groupId>com.querydsl</groupId> 
    <artifactId>querydsl-jpa</artifactId> 
    <version>4.1.4</version> 
</dependency>

We also need to configure the APT – Annotation processing tool – plugin as follows:

<plugin>
    <groupId>com.mysema.maven</groupId>
    <artifactId>apt-maven-plugin</artifactId>
    <version>1.1.3</version>
    <executions>
        <execution>
            <goals>
                <goal>process</goal>
            </goals>
            <configuration>
                <outputDirectory>target/generated-sources/java</outputDirectory>
                <processor>com.mysema.query.apt.jpa.JPAAnnotationProcessor</processor>
            </configuration>
        </execution>
    </executions>
</plugin>

3. The MyUser Entity

Next – let’s take a look at the “MyUser” entity which we are going to use in our Search API:

@Entity
public class MyUser {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String firstName;
    private String lastName;
    private String email;

    private int age;
}

4. Custom Predicate with PathBuilder

Now – let’s create a custom Predicate based on some arbitrary constraints.

We’re using PathBuilder here instead of the automatically generated Q-types because we need to create paths dynamically for more abstract usage:

public class MyUserPredicate {

    private SearchCriteria criteria;

    public BooleanExpression getPredicate() {
        PathBuilder<MyUser> entityPath = new PathBuilder<>(MyUser.class, "user");

        if (isNumeric(criteria.getValue().toString())) {
            NumberPath<Integer> path = entityPath.getNumber(criteria.getKey(), Integer.class);
            int value = Integer.parseInt(criteria.getValue().toString());
            switch (criteria.getOperation()) {
                case ":":
                    return path.eq(value);
                case ">":
                    return path.goe(value);
                case "<":
                    return path.loe(value);
            }
        } 
        else {
            StringPath path = entityPath.getString(criteria.getKey());
            if (criteria.getOperation().equalsIgnoreCase(":")) {
                return path.containsIgnoreCase(criteria.getValue().toString());
            }
        }
        return null;
    }
}

Note how the implementation of the predicate is generically dealing with multiple types of operations. This is because the query language is by definition an open language where you can potentially filter by any field, using any supported operation.

To represent that kind of open filtering criteria, we’re using a simple but quite flexible implementation – SearchCriteria:

public class SearchCriteria {
    private String key;
    private String operation;
    private Object value;
}

The SearchCriteria holds the details we need to represent a constraint:

  • key: the field name – for example: firstName, age, … etc
  • operation: the operation – for example: Equality, less than, … etc
  • value: the field value – for example: john, 25, … etc

5. MyUserRepository

Now – let’s take a look at our MyUserRepository.

We need our MyUserRepository to extend QueryDslPredicateExecutor so that we can use Predicates later to filter search results:

public interface MyUserRepository extends JpaRepository<MyUser, Long>, 
  QueryDslPredicateExecutor<MyUser>, QuerydslBinderCustomizer<QMyUser> {
    @Override
    default public void customize(
      QuerydslBindings bindings, QMyUser root) {
        bindings.bind(String.class)
          .first((SingleValueBinding<StringPath, String>) StringExpression::containsIgnoreCase);
        bindings.excluding(root.email);
      }
}

6. Combine Predicates

Next– let’s take a look at combining Predicates to use multiple constraints in results filtering.

In the following example – we work with a builder – MyUserPredicatesBuilder  – to combine Predicates:

public class MyUserPredicatesBuilder {
    private List<SearchCriteria> params;

    public MyUserPredicatesBuilder() {
        params = new ArrayList<>();
    }

    public MyUserPredicatesBuilder with(
      String key, String operation, Object value) {
  
        params.add(new SearchCriteria(key, operation, value));
        return this;
    }

    public BooleanExpression build() {
        if (params.size() == 0) {
            return null;
        }

        List<BooleanExpression> predicates = new ArrayList<>();
        MyUserPredicate predicate;
        for (SearchCriteria param : params) {
            predicate = new MyUserPredicate(param);
            BooleanExpression exp = predicate.getPredicate();
            if (exp != null) {
                predicates.add(exp);
            }
        }

        BooleanExpression result = predicates.get(0);
        for (int i = 1; i < predicates.size(); i++) {
            result = result.and(predicates.get(i));
        }
        return result;
    }
}

7. Test the Search Queries

Next – let’s test our Search API.

We’ll start by initializing the database with a few users – to have these ready and available for testing:

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = { PersistenceConfig.class })
@Transactional
@Rollback
public class JPAQuerydslIntegrationTest {

    @Autowired
    private MyUserRepository repo;

    private MyUser userJohn;
    private MyUser userTom;

    @Before
    public void init() {
        userJohn = new MyUser();
        userJohn.setFirstName("John");
        userJohn.setLastName("Doe");
        userJohn.setEmail("[email protected]");
        userJohn.setAge(22);
        repo.save(userJohn);

        userTom = new MyUser();
        userTom.setFirstName("Tom");
        userTom.setLastName("Doe");
        userTom.setEmail("[email protected]");
        userTom.setAge(26);
        repo.save(userTom);
    }
}

Next, let’s see how to find users with given last name:

@Test
public void givenLast_whenGettingListOfUsers_thenCorrect() {
    MyUserPredicatesBuilder builder = new MyUserPredicatesBuilder().with("lastName", ":", "Doe");

    Iterable<MyUser> results = repo.findAll(builder.build());
    assertThat(results, containsInAnyOrder(userJohn, userTom));
}

Now, let’s see how to find a user with given both first and last name:

@Test
public void givenFirstAndLastName_whenGettingListOfUsers_thenCorrect() {
    MyUserPredicatesBuilder builder = new MyUserPredicatesBuilder()
      .with("firstName", ":", "John").with("lastName", ":", "Doe");

    Iterable<MyUser> results = repo.findAll(builder.build());

    assertThat(results, contains(userJohn));
    assertThat(results, not(contains(userTom)));
}

Next, let’s see how to find user with given both last name and minimum age

@Test
public void givenLastAndAge_whenGettingListOfUsers_thenCorrect() {
    MyUserPredicatesBuilder builder = new MyUserPredicatesBuilder()
      .with("lastName", ":", "Doe").with("age", ">", "25");

    Iterable<MyUser> results = repo.findAll(builder.build());

    assertThat(results, contains(userTom));
    assertThat(results, not(contains(userJohn)));
}

Now, let’s see how to search for MyUser that doesn’t actually exist:

@Test
public void givenWrongFirstAndLast_whenGettingListOfUsers_thenCorrect() {
    MyUserPredicatesBuilder builder = new MyUserPredicatesBuilder()
      .with("firstName", ":", "Adam").with("lastName", ":", "Fox");

    Iterable<MyUser> results = repo.findAll(builder.build());
    assertThat(results, emptyIterable());
}

Finally – let’s see how to find a MyUser given only part of the first name – as in the following example:

@Test
public void givenPartialFirst_whenGettingListOfUsers_thenCorrect() {
    MyUserPredicatesBuilder builder = new MyUserPredicatesBuilder().with("firstName", ":", "jo");

    Iterable<MyUser> results = repo.findAll(builder.build());

    assertThat(results, contains(userJohn));
    assertThat(results, not(contains(userTom)));
}

8. UserController

Finally, let’s put everything together and build the REST API.

We’re defining a UserController that defines a simple method findAll() with a “search“ parameter to pass in the query string:

@Controller
public class UserController {

    @Autowired
    private MyUserRepository myUserRepository;

    @RequestMapping(method = RequestMethod.GET, value = "/myusers")
    @ResponseBody
    public Iterable<MyUser> search(@RequestParam(value = "search") String search) {
        MyUserPredicatesBuilder builder = new MyUserPredicatesBuilder();

        if (search != null) {
            Pattern pattern = Pattern.compile("(\w+?)(:|<|>)(\w+?),");
            Matcher matcher = pattern.matcher(search + ",");
            while (matcher.find()) {
                builder.with(matcher.group(1), matcher.group(2), matcher.group(3));
            }
        }
        BooleanExpression exp = builder.build();
        return myUserRepository.findAll(exp);
    }
}

Here is a quick test URL example:

http://localhost:8080/myusers?search=lastName:doe,age>25

And the response:

[{
    "id":2,
    "firstName":"tom",
    "lastName":"doe",
    "email":"[email protected]",
    "age":26
}]

9. Conclusion

This third article covered the first steps of building a query language for a REST API, making good use of the Querydsl library.

The implementation is of course early on, but it can easily be evolved to support additional operations.

The full implementation of this article can be found in the GitHub project – this is a Maven-based project, so it should be easy to import and run as it is.

The Price of all “Rest with Spring” course packages will increase by $50 next Friday:

>>> GET ACCESS NOW

Sort by:   newest | oldest | most voted
Magnus Lassi
Guest

Nice example! I think it’s great that there are many frameworks that can help with many situations. I think allowing custom queries against a REST API opens up several problems. Darrell Miller summed it up nicely: http://www.bizcoder.com/don-t-design-a-query-string-you-will-one-day-regret

Eugen Paraschiv
Guest
Hey Magnus, I agree with a lot of what that article lays out – it’s certainly important to understand that providing a query language will lead to a wider surface area for your API, and what that means for your system. The first thing I would note though is that a large family of APIs are very well suited for this kind of access; others – not so much, or at least not across the board – these kinds of operations will be to data intensive. So – you very much need to understand your system before allowing your users… Read more »
Charlie
Guest

Hi
This is a great tutorial, It really help me with my project. I tried to implement this aproach but when I try to acces to a ManyToMany property I got this error message: “org.hibernate.QueryException: illegal attempt to dereference collection”.
My test have something like this:
PromotionPredicatesBuilder builder = new UserPredicatesBuilder().with(“channels.idChannel”, “:”, “1”);
List result = promotionRepository.findAll(builder.build());

Using QTypes works fine:

QPromotion promotion = QPromotion.promotion;
List result = promotionRepository.findAll(promotion.channels.any().idChannel.eq(1));

But since I have more than one ManyToMany relationship this is not as flexible as PathBuilder aproach.

Is there any way to implement join operation using PathBuilder?

(Sorry for my bad english)

Cheers,

Charlie

Eugen Paraschiv
Guest

Hey Charlie,
First, I’m glad you’re putting the article to good use. Second, and very high level – it looks like you’re trying to access the idChannel property on a collection (which doesn’t itself have that property). However, without a working example (or at least more code) it’s hard to say for sure. I’d recommend either reproducing this with a test in my project over on github (and opening a PR), or asking the question on StackOverflow (with more detail). I’d be happy to take a look at either. Cheers,
Eugen.

Joey
Guest

Hey Eugen,
first thanks for your great article – you guided me on my first steps for a filter module using querydsl.

However I have a similar problem as charlie: As soon as I want to query after properties of a many-to-many relation I got a “java.lang.IllegalArgumentException: org.hibernate.QueryException: illegal attempt to dereference collection … with element property reference”.

If you need more information I would be pleased to send you my project.

Any Help is appreciated 🙂

Cheers,
Joey

Eugen Paraschiv
Guest

Hey Joey, that’s certainly interesting. Is that something you can reproduce on the current Github project of this article? If that’s possible, sure, let’s do that (with a failing test for example) – I’ll merge that in and have a look.
Cheers,
Eugen.

Dancia
Guest

Any ideas how to add pagination to queries?

Eugen Paraschiv
Guest

Hey Dancia – much like this entire implementation, you’ll have to build out support for that manually – there’s no shortcut when you’re doing these kinds of more advanced query languages. Cheers,
Eugen.

Sunil Sharma
Guest

Hi Dancia ,
Its very easy , Along with your Boolean expression you can pass Pageable object as well.
for eg. you can do something like
Pageable pageable = getPageable() // your pageable object

UserPredicatesBuilder builder = new UserPredicatesBuilder().with(“firstName”, “:”, “Adam”).with(“lastName”, “:”, “Fox”);

Page users = userRepo.findAll(builder.build,pageable)

Anchit Pancholi
Guest

Hi, Thanks for amazing tutorial, can you please explain me how can i use sum function with above approach, I tried so many ways but i didn’t find solution. Do you think is this a limitation?

select sum(qty)… from table group by product.

Eugen Paraschiv
Guest

Hey Anchit – let me see if I fully understand your question.
You need to use sum functions at the persistence level, or at the query language level, in the REST API?

Ernesto Murillo
Guest

Hi, great article thanks…

I have a question:

in this line,

Iterable results = repo.findAll(builder.build());

The method build() from the UserPredicatesBuilder returns a BooleanExpression, but there is no findAll(), that accepts a BooleanExpression as a parameter.

What am I missing?

Eugen Paraschiv
Guest

Hey Ernesto, glad you’re enjoying the site.
The QueryDslPredicateExecutor accepts a Predicte – which is what BooleanExpression is.
My suggestion is to have a look at the code in Github, you’ll see how everything works together much better.
Hope that helps. Cheers,
Eugen.

Max
Guest

Hey Ernesto,
I had the same issue – in my case the spring data version was not compatible to the querydsl version. Have a look at Eugen’s example project for the correct versions ( or as in my case: 1.3.3 Release for Spring data and 3.7.4 of querydsl)
Cheers,
Max.

Ernesto Murillo
Guest

That was the problem, the maven dependency I was using was incompatible
Changed the dependency and everything worked perfectly.
I’m using https://mvnrepository.com/artifact/com.querydsl
instead of the mysema

wpDiscuz