The new Certification Class of REST With Spring is out:

>> CHECK OUT THE COURSE

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.

Go deeper into building a REST API with Spring:

>> CHECK OUT THE CLASSES

  • Magnus Lassi

    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

    • 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 to query it with more flexibility.
      The second note is that it’s not a “all or nothing” decision – you can restrict access by quite a few verticals: operation type (only allow a subset of safe operations), payload size (simply enforce paginated access so that you don’t run the risk of having a query return 1000 resources), resource (choose which of your resources support these queries).
      So – I’m definitely not saying – just open the floodgates and don’t give it a second though. I’m saying – there’s a lot of value in this type of access and, with careful consideration, you can open up your API cleanly and efficiently.
      Hope that makes sense, and thanks for the very interesting link. Cheers,
      Eugen.

  • Charlie

    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

    • 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

        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

        • 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

    Any ideas how to add pagination to queries?

    • 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

      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

    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.

    • 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

    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?

    • 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

      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.

  • Fernando Ferreira

    Hi,

    Great tutorial.

    I have a question, How can a hadle Enums ?
    I followed everything you showed but when I filter with Enums I get this error:

    org.springframework.dao.InvalidDataAccessApiUsageException

    And it says the parameter did not match a certain Enum type.
    I there a way around this ?

    This might help https://groups.google.com/forum/#!topic/querydsl/uVUW-hPZbuY but I couldn’t figure out how to do it.

    Thanks!

    • Hey Fernando,
      I’m not sure, I’ll have to reproduce the error and see. Unfortunately it’s going to be difficult to answer without looking at the code. The way to go here would be a PR on Github with a failing test – and I’d be happy to have a look.
      Cheers,
      Eugen.

      • Fernando Ferreira

        Thanks for your reply @baeldung:disqus

        I created a reduced version of the code here: https://gist.github.com/fernandodof/eca664081c5982b6e055512106256688

        The exception I’m getting is:

        Caused by: java.lang.IllegalArgumentException: Parameter value [ACTIVE] did not match expected type [UserStatus (n/a)]

        If I try to filter without user status it works fine.

  • Ramana

    Hi Eugen,
    We follow your great articles, we have implemented the same for our jpa entities but the matcher.find() is always returning false hence filter is not working. Is this pattern “(\w+?)(:|)(\w+?),” working for you for query like xxx/search=id:1

    • I’m glad you’re enjoying the site Ramana. That regex looks very interesting 🙂 – can you give me a full example URL (localhost is fine) to see exactly what you’re trying to do. Cheers,
      Eugen.

      • Ramana

        Hi Eugen, that issue was fixed, the predicate had to be customised for my case as one of the entity property is string though it holds numeric value.
        Thank you.

  • Brian Schupbach

    Thank you for the great post! However, I’ve ran into an issue when generating the Q classes. I think there is something off in my pom.xml but I’m receiving no errors with it.

    I’ve copied suggested plugin setup located here:

    https://github.com/querydsl/apt-maven-plugin/wiki/m2e-usage

    http://stackoverflow.com/questions/7160006/m2e-and-having-maven-generated-source-folders-as-eclipse-source-folders

    I’ve made sure that Maven is installed and changed my eclipse .ini file to run off of the JDK as mentioned here:

    http://stackoverflow.com/questions/27619304/you-need-to-run-build-with-jdk-or-have-tools-jar-on-the-classpath
    http://stackoverflow.com/questions/24482259/eclipse-issue-with-maven-build-and-jdk-when-generating-qclasses-in-querydsl
    http://stackoverflow.com/questions/24482259/eclipse-issue-with-maven-build-and-jdk-when-generating-qclasses-in-querydsl

    I’ve gone to the project folder and ran mvn generate-sources and added the folder to the classpath. Still, the project cannot find any Q classes that are generated and I do not see any generated within the target folder. If anyone has any insights, I would be greatly appreciative. Thanks

    • Grzegorz Piwowarek

      I have a possibly very stupid question. Have you tried running a mvn package or mvn build instead of generate-sources?

      • Brian Schupbach

        Thanks for the quick reply. I have tried mvn install, mvn package, and mvn generate-sources on the command line. I don’t believe mvn build is something that can be ran on the command line. Within Spring STS, I have ran mvn build and put as the goal “generate-sources”. Still no luck…

        • Grzegorz Piwowarek

          Well, triggering a mvn build manually at least allows to make sure that it’s not the problem with your IDE and mvn. Do you have your code somewhere on github? It’s hard to come up with any answers without seeing the actual project

          • I fully agree with Grzegorz – it’s hard to know what’s going on without looking at code. To make sure I understand the issue – this isn’t about Eclipse, it’s about the actual classes not getting generated right? In which case, I would suggest not focusing on Eclipse at all and just focusing on the Maven part (command line is fine).
            And, in that case, one way you can go about it is – start from a working project, where the classes are actually generated – and add in your own logic on top of that. That way you know you’re running a working configuration from the start.
            Hope that helps. Cheers,
            Eugen.

  • Gauthier Peel

    i am surprised that you are using QueryDsl 3. With boot lastest 1.4 you should be using QueryDSL 4. And many things have changed

    • Grzegorz Piwowarek

      Gaunthier, this article was published 2 years ago 🙂

  • Ernesto Murillo

    I have a question on how to access a list index

    ListPath listPath = entityPath.getList(“menu.menuInformations”, MenuInformation.class, QMenuInformation.class);
    result = result.or(listPath.get(0).name.containsIgnoreCase(searchText));

    When I do that the exception I get is an unexpected token here:

    left join order1.menu.menuInformations as order1_menu.menuInformations_0 with index(order1_menu.menuInformations_0) = ?1

    and that’s because the generate query is not valid.

    I have an Order that belongs to a Menu, and that menu have a list of MenuInformations, I need access to the MenuInformation on index 0. In the Entity I added this line

    @Config(listAccessors = true)

    But I’m still getting the same error.

    I have spring, with jpa and hibernate. Normal querys (numberPath, StringPath, ListPath) work great, I can even use listPaths with any() and compare, but in this case I only need to compare to one item, maybe is that I don’t need a ListPath, since I’m tryng to compare a String.

    The problem is the get index part.

    Any ideas?

    • Hey Ernesto,
      That’s an interesting question, but it’s unfortunately going to be difficult to answer without looking at the code. The way to go here would be a PR on Github with a failing test – and I’d be happy to have a look.
      Hope that helps. Cheers,
      Eugen.