rest-querydsl-multiple-tables

Posted on: 2019-10-08 2019-10-08
Tags: REST, Spring

REST Query Language Over Multiple Tables with Querydsl Web Support

REST
Spring

1. Overview

In this tutorial, we’ll continue with the second part of
Spring Data
Querydsl Web Support. Here, we’ll focus on associated entities and
how to create queries over HTTP.

Following the same configuration used in part one, we’ll create a
Maven-based project. Please refer to the original article to check how
to set up the basics.

2. Entities

First, let’s add a new entity (Address) creating a relationship
between the user and her address. We’ve used the OneToOne relationship
to keep it simple.

Consequently, we’ll have the following classes:

@Entity
public class User {

    @Id
    @GeneratedValue
    private Long id;

    private String name;

    @OneToOne(fetch = FetchType.LAZY, mappedBy = "user")
    private Address addresses;

    // getters & setters
}

@Entity
public class Address {

    @Id
    @GeneratedValue
    private Long id;

    private String address;

    private String country;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id")
    private User user;

    // getters & setters
}

[[Spring Data Repositories]]
=== 3. Spring Data Repositories

At this point, we have to create the Spring Data repositories, as usual,
one for each entity. Note that these repositories will have the Querydsl
configuration.

Let’s see the AddressRepository repository and explain how
the framework configuration works:

public interface AddressRepository extends JpaRepository<Address, Long>,
  QueryDslPredicateExecutor<Address>, QuerydslBinderCustomizer<QAddress> {

    @Override
    default void customize(QuerydslBindings bindings, QAddress root) {
        bindings.bind(String.class)
          .first((SingleValueBinding<StringPath, String>) StringExpression::eq);
    }
}

We’re overriding the customize() method to configure the default
binding. In this case, we’ll customize the default method binding to be
equals, for all String properties.

Once the repository is all set, we just have to add a @RestController
to manage the HTTP queries.

4. Query Rest Controller

In part one, we explained the Query@RestController
over user repository, here, we’ll just reuse it.

Also, we may want to query the address table; so for this, we’ll just
add a similar method:

@GetMapping(value = "/addresses", produces = MediaType.APPLICATION_JSON_VALUE)
public Iterable<Address> queryOverAddress(
  @QuerydslPredicate(root = Address.class) Predicate predicate) {
    BooleanBuilder builder = new BooleanBuilder();
    return addressRepository.findAll(builder.and(predicate));
}

Let’s create some tests to see how this works.

5. Integration Testing

We’ve included a test to prove how Querydsl works. For this, we
are using the MockMvc framework to simulate HTTP querying over
user joining this entity with the new one: address. Therefore, we
are now able to make queries filtering address attributes.

Let’s retrieve all users living in Spain:

/users?addresses.country=Spain

@Test
public void givenRequest_whenQueryUserFilteringByCountrySpain_thenGetJohn() throws Exception {
    mockMvc.perform(get("/users?address.country=Spain")).andExpect(status().isOk()).andExpect(content()
      .contentType(contentType))
      .andExpect(jsonPath("$", hasSize(1)))
      .andExpect(jsonPath("$[0].name", is("John")))
      .andExpect(jsonPath("$[0].address.address", is("Fake Street 1")))
      .andExpect(jsonPath("$[0].address.country", is("Spain")));
}

As a result, Querydsl will map the predicate sent over HTTP and
generates the following SQL script:

select user0_.id as id1_1_,
       user0_.name as name2_1_
from user user0_
      cross join address address1_
where user0_.id=address1_.user_id
      and address1_.country='Spain'

6. Conclusion

To sum up, we have seen that Querydsl offers to the web clients a very
simple alternative to create dynamic queries; another powerful use of
this framework.

In part I, we saw
how to retrieve data from one table; consequently, now, we can add
queries joining several tables, offering web-clients a better experience
filtering directly over HTTP requests they make.

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

getdocs

3043