Okay so we made a filterable + sortable + pageable list before with Spring + Couchbase (you can read more about it here and here, I totally recommend you read those posts, cuz I’ll refer to them in this one).
Actually filters are meant to be in an AND relation between the parameters (most of the cases). But what if you want to add some ORs to your query? Or you want to filter on multiple columns by only one input field from the request parameter? I’ll show you how to implement an API easily, which is the resource for a filterable + sortable + pageable with more complext filter conditions.
As always, let’s start our journey at the controller
package com.wanari.specification.example.controller;
import com.wanari.specification.example.controller.dto.UserListRequest;
import com.wanari.specification.example.service.UserService;
import org.springframework.data.domain.Pageable;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping("/user")
public class UserController extends BaseController {
private final UserService userService;
public UserController(UserService userService) {
this.userService = userService;
}
@RequestMapping(
value = "",
method = RequestMethod.GET)
public ResponseEntity getAllUser(UserListRequest request, Pageable pageable) {
return userService.findAll(request, pageable).fold(
this::errorToResponse,
this::toResponse
);
}
}
Pretty much the same as in the CouchbaseQueryExecutor example app, but I know now that you can pass a Java Class parameter to the controller function, so Spring will automatically fill the properties from the query string. In case of name collision, all of the properties with the same name will be filled. **IMPORTANT **if you want to use your Java class like this it **MUST **have setters. In case you don’t add setters, then spring won’t bind the parameters to the properties (even if the properties are public).
So yeah, with this little difference our contorller looks the same (using vavr ofc :) ).
Let’s continue with the service
package com.wanari.specification.example.service;
import com.wanari.specification.example.controller.dto.UserDto;
import com.wanari.specification.example.controller.dto.UserListRequest;
import com.wanari.specification.example.controller.mapper.UserMapper;
import com.wanari.specification.example.controller.specification.UserListSpecification;
import com.wanari.specification.example.domain.User;
import com.wanari.specification.example.repository.UserRepository;
import com.wanari.specification.example.service.errors.ErrorResponse;
import io.vavr.control.Either;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
@Service
public class UserService {
private final UserRepository userRepository;
private final UserMapper userMapper;
private final UserListSpecification userListSpecification;
public UserService(UserRepository userRepository, UserMapper userMapper, UserListSpecification userListSpecification) {
this.userRepository = userRepository;
this.userMapper = userMapper;
this.userListSpecification = userListSpecification;
}
public Either<ErrorResponse, Page<UserDto>> findAll(UserListRequest request, Pageable pageable) {
Page<User> userPage = userRepository.findAll(userListSpecification.getFilter(request), pageable);
return Either.right(userPage.map(userMapper::map));
}
}
Pretty much the same again, we pass the filters and the pageable param to the service from the controller, and after we acquired the Entities we map it to Dtos. The UserListSpecification is the class where the magic happens :).
But before the UserListSpecification – let’s take a look at the UserRepository.
package com.wanari.specification.example.repository;
import com.wanari.specification.example.domain.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;
@Repository
public interface UserRepository extends JpaRepository<User, Long>, JpaSpecificationExecutor<User> {
}
Again and again, the same… With a little difference: now our UserRepository extends the JpaSpecificationExecutor, so we can filter our entities by a Specification with the following function
Page<T> findAll(Specification<T> spec, Pageable pageable);
All we have to do is make a specification from the UserListRequest. Let’s se how to do it.
I’m not going to talk too much about it, I think the code is self explanatory enough :)
package com.wanari.specification.example.controller.specification;
import org.springframework.data.jpa.domain.Specification;
public abstract class BaseSpecification<T, U> {
private final String wildcard = "%";
public abstract Specification<T> getFilter(U request);
protected String containsLowerCase(String searchField) {
return wildcard + searchField.toLowerCase() + wildcard;
}
}
package com.wanari.specification.example.controller.specification;
import com.wanari.specification.example.controller.dto.UserListRequest;
import com.wanari.specification.example.domain.User;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Component;
import static org.springframework.data.jpa.domain.Specifications.where;
@Component
public class UserListSpecification extends BaseSpecification<User, UserListRequest> {
@Override
public Specification<User> getFilter(UserListRequest request) {
return (root, query, cb) -> {
query.distinct(true);
return where(
where(firstNameContains(request.search))
.or(lastNameContains(request.search))
.or(emailContains(request.search))
)
.and(streetContains(request.street))
.and(cityContains(request.city))
.toPredicate(root, query, cb);
};
}
// ...
}
As it’s a higher level function, first you define the AND and OR relations with the specific parentheses (the example above is (firstName OR lastName OR email) AND street AND city).
We need the ‘query.distinct(true);’ because one user can have multiple addresses. To filter the address properties, you have to join addresses to users. What happens on the JPA level when you filter on addresses, and want to receive users with specific addresses? Let’s assume we have the following data in the database
CREATE TABLE sys_user (
id BIGSERIAL PRIMARY KEY NOT NULL,
email VARCHAR(256) NOT NULL,
first_name VARCHAR(256) NOT NULL,
last_name VARCHAR(256) NOT NULL
);
INSERT INTO sys_user VALUES (1, 'example1@wanari.com', 'John', 'Doe');
CREATE TABLE address (
id BIGSERIAL PRIMARY KEY NOT NULL,
user_id BIGSERIAL NOT NULL REFERENCES sys_user (id),
zip INT8 NOT NULL,
street VARCHAR(255) NOT NULL,
city VARCHAR(255) NOT NULL
);
INSERT INTO address VALUES (1, 1, 1061, 'Paladino Gardens Northwest', 'Budapest');
INSERT INTO address VALUES (2, 1, 5423, 'East Sturbridge Garth', 'Budapest');
INSERT INTO address VALUES (3, 1, 3426, 'North Hycrest Walk', 'Budapest');
So John Doe has 3 addresses in Budapest. Now if we join the two tables we’ll get 3 records, each containing John Doe’s data with each of the addresses. After that, if you return with only the users (in this case only John Doe), you’ll get that one user 3 times as result. So if you only want to get distinct result, then you should add that magical line. If you only want to filter users for example, you can have the specification like this:
package com.wanari.specification.example.controller.specification;
import com.wanari.specification.example.controller.dto.UserListRequest;
import com.wanari.specification.example.domain.User;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Component;
import static org.springframework.data.jpa.domain.Specifications.where;
@Component
public class UserListSpecification extends BaseSpecification<User, UserListRequest> {
@Override
public Specification<User> getFilter(UserListRequest request) {
return where(firstNameContains(request.search))
.or(lastNameContains(request.search))
.or(emailContains(request.search));
}
// ...
}
Let’s assume we have the first specification (with the addresses). Our request contains a search property which should filter on firstName, lastName and email column, and a city/street property that should filter on city/street. Only one thing remains before our our API is complete: tell JPA how to filter on the columns.
package com.wanari.specification.example.controller.specification;
import com.wanari.specification.example.controller.dto.UserListRequest;
import com.wanari.specification.example.domain.Address;
import com.wanari.specification.example.domain.User;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Component;
import javax.persistence.criteria.JoinType;
import javax.persistence.criteria.ListJoin;
@Component
public class UserListSpecification extends BaseSpecification<User, UserListRequest> {
// ...
private Specification<User> firstNameContains(String firstName) {
return userAttributeContains("firstName", firstName);
}
private Specification<User> lastNameContains(String lastName) {
return userAttributeContains("lastName", lastName);
}
private Specification<User> emailContains(String email) {
return userAttributeContains("email", email);
}
private Specification<User> userAttributeContains(String attribute, String value) {
return (root, query, cb) -> {
if(value == null) {
return null;
}
return cb.like(
cb.lower(root.get(attribute)),
containsLowerCase(value)
);
};
}
private Specification<User> cityContains(String city) {
return addressAttributeContains("city", city);
}
private Specification<User> streetContains(String street) {
return addressAttributeContains("street", street);
}
private Specification<User> addressAttributeContains(String attribute, String value) {
return (root, query, cb) -> {
if(value == null) {
return null;
}
ListJoin<User, Address> addresses = root.joinList("addresses", JoinType.INNER);
return cb.like(
cb.lower(addresses.get(attribute)),
containsLowerCase(value)
);
};
}
}
I wanted to make my example as clear as possible, so I didn’t add a lot of conditions with complex logic, just made a simple example application. If you want to know more about CriteriaBuilder take a look at its API.
After you added all of these, you can try your API with you favoirite REST client with the following URL:
localhost:8080/user?city=buda&street=paladino&search=john&sort=firstName,asc&page=0&size=2
(all parts of the query string are optional).
So if you need the entire source code to try these things out for yourself, look at the GitHub project!
If you wanna see more stuff from us, follow our Facebook or Twitter.
I hope this post was useful for you, see ya later! (smile)
UPDATE 11/27/2018: If you liked this post, you might also like the next one about our Solutions for a Filterable, Sortable, Pageable list in Spring.