CouchBase: how can you make a filterable list in SpringData?

Sure it’s not Instagram filters; Couchbase filters are to be created here:

What is Couchbase?

Couchbase Server is a NoSQL document database with a distributed architecture for performance, scalability, and availability. It enables developers to build applications easier and faster by leveraging the power of SQL with the flexibility of JSON.

http://www.couchbase.com/nosql-databases/couchbase-server

Why do you need a generic way to make queries?

It is easy. Every CMS system needs filterable lists so the users can look at the entities they want. On the other hand, as a developer you don’t want to copy-paste a lot of boilerplate code. We are developing an application in Spring Data (Java8), and according to this stackoverflow question (thanks to Simon Baslé) we know that there is no Couchbase module yet. So basically I‘ll show you a way to build N1QL queries easily from dynamic parameters.

The CouchbaseQueryExecutor

Basically you want a controller function. Something like this:

@RequestMapping(value = "/user-tasks",
    method = RequestMethod.GET,
    produces = MediaType.APPLICATION_JSON_VALUE)
public ResponseEntity<List<UserTaskDTO>> getAllUserTasks(Pageable pageable, HttpServletRequest request) throws URISyntaxException {
    CouchbasePage<UserTask> cbpage = userTaskService.getUserTasks(request, pageable);
    HttpHeaders headers = PaginationUtil.generatePaginationHttpHeaders(cbpage);
    return new ResponseEntity<>(userTaskMapper.userTasksToUserTaskDTOs(cbpage.data), headers, HttpStatus.OK);
}

userTaskService.getUserTasks(…) will give you the entities matching the params in the request’s query string, and additionally you can provide an optional pageable param to the service if you want to make a page with Infinite-scroll, Pagination, or something like that on the UI. So yeah, this is what we want, but should we build the query matching the requirements explained earlier? Let’s take a look at the layers.

Controller

The controller should transform the request’s query string into some generic object, so all of the services’ find function parameter lists can be the same. You should drop all the unnecessary query string params, and transform them into a map. This oneliner will do it for you:

public class RequestUtil {
    public static Map<String, String> getParameterMapWithOnlyFirstValues(HttpServletRequest request) {
        return request.getParameterMap().entrySet().stream().collect(Collectors.toMap(Map.Entry::getKey, entry -> entry.getValue()[0]));
    }
}

This will ignore multiple query string params like ?param=asd&param=basd. You will only get the ‘asd’ with the key ‘param’ in the map. If you want to use multiple params with the same name (for example arrays) in your query string, remove this from the code. So the controller function now looks like this:

@RequestMapping(value = "/user-tasks",
    method = RequestMethod.GET,
    produces = MediaType.APPLICATION_JSON_VALUE)
public ResponseEntity<List<UserTaskDTO>> getAllUserTasks(Pageable pageable, HttpServletRequest request) throws URISyntaxException {
    CouchbasePage<UserTask> cbpage = userTaskService.getUserTasks(RequestUtil.getParameterMapWithOnlyFirstValues(request), pageable);
    HttpHeaders headers = PaginationUtil.generatePaginationHttpHeaders(cbpage);
    return new ResponseEntity<>(userTaskMapper.userTasksToUserTaskDTOs(cbpage.data), headers, HttpStatus.OK);
}

Your controller is done now. Let’s talk a bit about the other lines in this function.

userTaskMapper.userTasksToUserTaskDTOs(…) converts the entities to DTOs (because you don’t really want to send sensitive data to the client; like password, or the privilege to detonate printers… :) ).

PaginationUtil.generatePaginationHttpHeaders(cbpage) is responsible for generating the headers for our Infinite-scroll.

CouchbasePage contains information about the page such as totalElements, pageNumber, totalPages, size, and the data itself in a generic list.

public class CouchbasePage<T> {
    public List<T> data;
    public int totalElements;
    public int pageNumber;
    public int size;
    public int totalPages;
 
    public CouchbasePage(Pageable pageable) { //to convert Spring's Pageable into CouchbasePage
        pageNumber = pageable.getPageNumber();
        size = pageable.getPageSize();
    }
 
    public void calculateTotalPages() {
        totalPages = (totalElements - 1) / size + 1;
    }
}

Service

Let’s take a look at the UserTaskService. The service’s responsibility is to make filters from the request params (which is a Map<String, String> now). It can ignore fields if necessary, e.g. if there is a parameter named userId and the value is -1. You don’t want to filter negative IDs, because that would mean you want to give the currently logged user’s tasks back. In Java8 you can make each param transformation a oneliner with the following util class:

public class CouchbaseFilter extends HashMap<String, Object> {
 
    public void putIfNotEmpty(String key, String value) {
        if(!StringUtils.isEmpty(value)) {
            put(key, value);
        }
    }
 
    public <T> void putIfNotEmptyAndApply(String key, String value, Function<String, T> parser) {
        if(!StringUtils.isEmpty(value)) {
            put(key, parser.apply(value));
        }
    }
 
    public <T> void putIfConditionAndApply(String key, String value, Function<T, Boolean> condition, Function<String, T> parser) {
        if(!StringUtils.isEmpty(value)) {
            T parsedValue = parser.apply(value);
            if(condition.apply(parsedValue)) {
                put(key, parsedValue);
            }
        }
    }
 
    public void putCustom(String param1, String param2, BiFunction<String, String, CouchbaseFilterEntry> function) {
        CouchbaseFilterEntry entry = function.apply(param1, param2);
        if(entry.isDefined()) {
            put(entry.key, entry.value);
        }
    }
 
    public JsonObject toJsonObject() {
        return JsonObject.from(this);
    }
}
public class CouchbaseFilterConditions {
    public static Boolean isPositive(Long number) {
        return number > 0;
    }
}

There are some useful functions. If you need any other generic transformation, you can implement your own. The functions do the following:

toJsonObject is the function that converts the map to JsonObject. The N1QL query function expects a JsonObject.

putIfNotEmpty – As simple as it seems. If not empty, puts the filter as a String. The type of the value is important, because N1QL is type-sensitive. If your filtered field’s type is not a string, then you should use

putIfNotEmptyAndApply – Same as putIfNotEmpty, but there is an additional method reference parameter. Before putting the filter into the map, this function applies the parser function.

putIfConditionAndApply – Of course ifNotEmpty is not the only condition that you want to implement. You can implement your own conditions in the CoucbaseFilterConditions class.

putCustom – Sometimes it’s not trivial whether or not you should put the filter. For complicated situations, there is the putCustom function. You can implement it with more parameters if you want. The last parameter is the most important, that is a method’s reference which contains the custom logic that you want to apply. Java8 has only Function and BiFunction functional interfaces defined, but if you want a TriFunction or so on, then you can easily define it by looking at Function and BiFunction’s source code.

As you see there is a CouchbaseFilterEntry class that helps the putCustom function. It returns the key and the value of a filter, and contains information about whether the filter is empty. Only not empty filter entries will be put in the filter.

public class CouchbaseFilterEntry {
    public String key;
    public Object value;
    private boolean isDefined = false;
 
    public CouchbaseFilterEntry(String key, Object value) {
        this.key = key;
        this.value = value;
        this.isDefined = true;
    }
 
    private CouchbaseFilterEntry() {
        this.isDefined = false;
    }
 
    public static CouchbaseFilterEntry empty() {
        return new CouchbaseFilterEntry();
    }
 
    public boolean isEmpty() {
        return isDefined;
    }
}

Okay so now we have a ton of util classes implemented, but why is it good? Because you don’t have to copy-paste a lot of code. Let’s compare them a bit!

private JsonObject filtersFromRequest(HttpServletRequest request) {
    JsonObject filters = defaultFilters();
 
    String taskFrequencyTypeEnum = request.getParameter("taskFrequencyTypeEnum");
    if(!StringUtils.isEmpty(taskFrequencyTypeEnum)) {
        filters.put("taskFrequencyTypeEnum", taskFrequencyTypeEnum);
    }
 
    String isImportantString = request.getParameter("isImportant");
    if(!StringUtils.isEmpty(isImportantString)) {
        filters.put("isImportant", Boolean.parseBoolean(isImportantString));
    }
 
    String title = request.getParameter("title");
    if(!StringUtils.isEmpty(title)) {
        filters.put("title" + CouchbaseQueryExecutor.CONTAINS_FILTER, title);
    }
 
    String deadlineFrom = request.getParameter("deadlineFrom");
    if(!StringUtils.isEmpty(deadlineFrom)) {
        filters.put("deadlineTime" + CouchbaseQueryExecutor.FROM_FILTER, Long.parseLong(deadlineFrom));
    }
 
    String deadlineTo = request.getParameter("deadlineTo");
    if(!StringUtils.isEmpty(deadlineTo)) {
        Date filterDate = new Date(Long.parseLong(deadlineTo));
        filters.put("deadlineTime" + CouchbaseQueryExecutor.TO_FILTER, DateUtils.makeItTheEndOfTheDay(filterDate).getTime());
    }
 
    String userId = request.getParameter("userId");
    if(!StringUtils.isEmpty(userId) && !"-1".equals(userId)) {
        filters.put("userId", Long.parseLong(userId));
    }
 
    String status = request.getParameter("status");
    String selectedTab = request.getParameter("selectedTab");
    if(StringUtils.isEmpty(selectedTab) || (!selectedTab.equals("assignedTasks") && !selectedTab.equals("finishedTasks"))) {
        selectedTab = "assignedTasks";
    }
    if(StringUtils.isNotEmpty(status)) {
        filters.put("taskStatusEnum", status);
    } else {
        if(selectedTab.equals("assignedTasks")) {
            filters.put("taskStatusEnum" + CouchbaseQueryExecutor.NOT_FILTER, TaskStatusEnum.FINISHED.name());
        } else if(selectedTab.equals("finishedTasks")) {
            filters.put("taskStatusEnum", TaskStatusEnum.FINISHED.name());
        }
    }
 
    return filters;
}
private JsonObject filtersFromParams(Map<String, String> params) {
    CouchbaseFilter filters = defaultFilters();
 
    filters.putIfNotEmpty(TASK_FREQUENCY_TYPE_FILTER, params.get(TASK_FREQUENCY_TYPE_ENUM_REQUEST_PARAM));
    filters.putIfNotEmpty(TITLE_CONTAINS_FILTER, params.get(TITLE_REQUEST_PARAM));
 
    filters.putIfNotEmptyAndApply(IS_IMPORTANT_FILTER, params.get(IS_IMPORTANT_REQUEST_PARAM), Boolean::parseBoolean);
    filters.putIfNotEmptyAndApply(DEADLINE_TIME_FROM_FILTER, params.get(DEADLINE_FROM_REQUEST_PARAM), Long::parseLong);
    filters.putIfNotEmptyAndApply(DEADLINE_TIME_TO_FILTER, params.get(DEADLINE_TO_REQUEST_PARAM), DateUtils::makeTimeStampTheEndOfTheDay);
 
    filters.putIfConditionAndApply(USER_ID_FILTER, params.get(USER_ID_REQUEST_PARAM), CouchbaseFilterConditions::isPositive, Long::parseLong);
 
    filters.putCustom(params.get(STATUS_REQUEST_PARAM), params.get(SELECTED_TAB_REQUEST_PARAM), this::statusFilterLogic);
 
    return filters.toJsonObject();
}
  
private CouchbaseFilterEntry statusFilterLogic(String status, String selectedTab) {
    if(StringUtils.isEmpty(selectedTab) || (!selectedTab.equals(ASSIGNED_TASKS_TAB_VALUE) && !selectedTab.equals(FINISHED_TASKS_TAB_VALUE))) {
        selectedTab = ASSIGNED_TASKS_TAB_VALUE;
    }
    if(StringUtils.isNotEmpty(status)) {
        return new CouchbaseFilterEntry(TASK_STATUS_ENUM_FILTER, status);
    } else {
        switch(selectedTab) {
            case ASSIGNED_TASKS_TAB_VALUE:
                return new CouchbaseFilterEntry(TASK_STATUS_ENUM_FILTER + CouchbaseQueryExecutor.IN_FILTER, availableStatusFilter());
            case FINISHED_TASKS_TAB_VALUE:
                return new CouchbaseFilterEntry(TASK_STATUS_ENUM_FILTER, TaskStatusEnum.FINISHED.name());
            default:
                return CouchbaseFilterEntry.empty();
        }
    }
}

The defaultFilters function returns a Map of the filters that every query must contain, for example if you have organizations in your app, and you don’t want them to see each other’s data, then a default filter can be the currently logged in user’s organizationId.

The two code snippets above are doing exactly the same. The string parameters were extracted to constant variables. Ignore that a little bit and take a look at the number of lines. 51 vs 34. That means 33% of code has disappeared. And what if you want to add a new parameter? You don’t have to copy-paste the line getting the value from the parameter, only that one line of code. The if condition checks whether you should put the param as a filter. I hope you like it too :) Using this is pretty simple.

@Service
public class UserTaskService {
 
    private final Logger log = LoggerFactory.getLogger(UserTaskService.class);
 
    @Inject
    private UserTaskRepository userTaskRepository;
 
    @Inject
    private UserService userService;
 
    private static final String TASK_FREQUENCY_TYPE_ENUM_REQUEST_PARAM = "taskFrequencyTypeEnum";
    private static final String TITLE_REQUEST_PARAM = "title";
    private static final String IS_IMPORTANT_REQUEST_PARAM = "isImportant";
    private static final String DEADLINE_FROM_REQUEST_PARAM = "deadlineFrom";
    private static final String DEADLINE_TO_REQUEST_PARAM = "deadlineTo";
    private static final String USER_ID_REQUEST_PARAM = "userId";
    private static final String STATUS_REQUEST_PARAM = "status";
    private static final String SELECTED_TAB_REQUEST_PARAM = "selectedTab";
 
    private static final String TASK_FREQUENCY_TYPE_FILTER = "taskFrequencyTypeEnum";
    private static final String TITLE_CONTAINS_FILTER = "title" + CouchbaseQueryExecutor.CONTAINS_FILTER;
    private static final String IS_IMPORTANT_FILTER = "isImportant";
    private static final String DEADLINE_TIME_FROM_FILTER = "deadlineTime" + CouchbaseQueryExecutor.FROM_FILTER;
    private static final String DEADLINE_TIME_TO_FILTER = "deadlineTime" + CouchbaseQueryExecutor.TO_FILTER;
    private static final String USER_ID_FILTER = "userId";
    private static final String TASK_STATUS_ENUM_FILTER = "taskStatusEnum";
    private static final String TENANT_ID_FILTER = "tenantId";
 
    private static final String ASSIGNED_TASKS_TAB_VALUE = "assignedTasks";
    private static final String FINISHED_TASKS_TAB_VALUE = "finishedTasks";
 
    private static JsonArray availableStatusFilter() {
        return JsonArray.from(
            TaskStatusEnum.AVAILABLE.name(),
            TaskStatusEnum.SELECTED.name(),
            TaskStatusEnum.DISCARDED.name()
        );
    }
 
    private CouchbaseFilter defaultFilters() {
        CouchbaseFilter filters = new CouchbaseFilter();
        User currentUser = userService.getCurrentUser();
 
        filters.put(TENANT_ID_FILTER, currentUser.getSelectedTenant().getId());
 
        return filters;
    }
 
    private JsonObject filtersFromParams(Map<String, String> params) {
        ...
    }
 
    private CouchbaseFilterEntry statusFilterLogic(String status, String selectedTab) {
        ...
    }
 
    public CouchbasePage<UserTask> getUserTasks(Map<String, String> params, Pageable pageable) {
        return userTaskRepository.getUserTasks(filtersFromParams(params), pageable);
    }
 
    public List<UserTask> getUserTasks(Map<String, String> params) {
        return userTaskRepository.getUserTasks(filtersFromParams(params));
    }
}

CouchBase: how can you make a filterable list in SpringData?

By Alex Sükein / October 24, 2016 / Leave a comment
Filed in: backend development, cloud 

CouchBase filters

Sure it’s not Instagram filters; Couchbase filters are to be created here: What is Couchbase?

Couchbase Server is a NoSQL document database with a distributed architecture for performance, scalability, and availability. It enables developers to build applications easier and faster by leveraging the power of SQL with the flexibility of JSON.

http://www.couchbase.com/nosql-databases/couchbase-server Why do you need a generic way to make queries?

It is easy. Every CMS system needs filterable lists so the users can look at the entities they want. On the other hand, as a developer you don’t want to copy-paste a lot of boilerplate code. We are developing an application in Spring Data (Java8), and according to this stackoverflow question (thanks to Simon Baslé) we know that there is no Couchbase module yet. So basically I‘ll show you a way to build N1QL queries easily from dynamic parameters. The CouchbaseQueryExecutor

Basically you want a controller function. Something like this:

@RequestMapping(value = "/user-tasks",
    method = RequestMethod.GET,
    produces = MediaType.APPLICATION_JSON_VALUE)
public ResponseEntity<List<UserTaskDTO>> getAllUserTasks(Pageable pageable, HttpServletRequest request) throws URISyntaxException {
    CouchbasePage<UserTask> cbpage = userTaskService.getUserTasks(request, pageable);
    HttpHeaders headers = PaginationUtil.generatePaginationHttpHeaders(cbpage);
    return new ResponseEntity<>(userTaskMapper.userTasksToUserTaskDTOs(cbpage.data), headers, HttpStatus.OK);
}

userTaskService.getUserTasks(…) will give you the entities matching the params in the request’s query string, and additionally you can provide an optional pageable param to the service if you want to make a page with Infinite-scroll, Pagination, or something like that on the UI. So yeah, this is what we want, but should we build the query matching the requirements explained earlier? Let’s take a look at the layers. Controller

The controller should transform the request’s query string into some generic object, so all of the services’ find function parameter lists can be the same. You should drop all the unnecessary query string params, and transform them into a map. This oneliner will do it for you:

public class RequestUtil {
    public static Map<String, String> getParameterMapWithOnlyFirstValues(HttpServletRequest request) {
        return request.getParameterMap().entrySet().stream().collect(Collectors.toMap(Map.Entry::getKey, entry -> entry.getValue()[0]));
    }
}

This will ignore multiple query string params like ?param=asd&param=basd. You will only get the ‘asd’ with the key ‘param’ in the map. If you want to use multiple params with the same name (for example arrays) in your query string, remove this from the code. So the controller function now looks like this:

@RequestMapping(value = "/user-tasks",
    method = RequestMethod.GET,
    produces = MediaType.APPLICATION_JSON_VALUE)
public ResponseEntity<List<UserTaskDTO>> getAllUserTasks(Pageable pageable, HttpServletRequest request) throws URISyntaxException {
    CouchbasePage<UserTask> cbpage = userTaskService.getUserTasks(RequestUtil.getParameterMapWithOnlyFirstValues(request), pageable);
    HttpHeaders headers = PaginationUtil.generatePaginationHttpHeaders(cbpage);
    return new ResponseEntity<>(userTaskMapper.userTasksToUserTaskDTOs(cbpage.data), headers, HttpStatus.OK);
}

Your controller is done now. Let’s talk a bit about the other lines in this function.

userTaskMapper.userTasksToUserTaskDTOs(…) converts the entities to DTOs (because you don’t really want to send sensitive data to the client; like password, or the privilege to detonate printers… (smile) ).

PaginationUtil.generatePaginationHttpHeaders(cbpage) is responsible for generating the headers for our Infinite-scroll.

CouchbasePage contains information about the page such as totalElements, pageNumber, totalPages, size, and the data itself in a generic list.

public class CouchbasePage<T> {
    public List<T> data;
    public int totalElements;
    public int pageNumber;
    public int size;
    public int totalPages;
 
    public CouchbasePage(Pageable pageable) { //to convert Spring's Pageable into CouchbasePage
        pageNumber = pageable.getPageNumber();
        size = pageable.getPageSize();
    }
 
    public void calculateTotalPages() {
        totalPages = (totalElements - 1) / size + 1;
    }
}

Service

Let’s take a look at the UserTaskService. The service’s responsibility is to make filters from the request params (which is a Map<String, String> now). It can ignore fields if necessary, e.g. if there is a parameter named userId and the value is -1. You don’t want to filter negative IDs, because that would mean you want to give the currently logged user’s tasks back. In Java8 you can make each param transformation a oneliner with the following util class:

public class CouchbaseFilter extends HashMap<String, Object> {
 
    public void putIfNotEmpty(String key, String value) {
        if(!StringUtils.isEmpty(value)) {
            put(key, value);
        }
    }
 
    public <T> void putIfNotEmptyAndApply(String key, String value, Function<String, T> parser) {
        if(!StringUtils.isEmpty(value)) {
            put(key, parser.apply(value));
        }
    }
 
    public <T> void putIfConditionAndApply(String key, String value, Function<T, Boolean> condition, Function<String, T> parser) {
        if(!StringUtils.isEmpty(value)) {
            T parsedValue = parser.apply(value);
            if(condition.apply(parsedValue)) {
                put(key, parsedValue);
            }
        }
    }
 
    public void putCustom(String param1, String param2, BiFunction<String, String, CouchbaseFilterEntry> function) {
        CouchbaseFilterEntry entry = function.apply(param1, param2);
        if(entry.isDefined()) {
            put(entry.key, entry.value);
        }
    }
 
    public JsonObject toJsonObject() {
        return JsonObject.from(this);
    }
}
public class CouchbaseFilterConditions {
    public static Boolean isPositive(Long number) {
        return number > 0;
    }
}

There are some useful functions. If you need any other generic transformation, you can implement your own. The functions do the following:

toJsonObject is the function that converts the map to JsonObject. The N1QL query function expects a JsonObject.

putIfNotEmpty – As simple as it seems. If not empty, puts the filter as a String. The type of the value is important, because N1QL is type-sensitive. If your filtered field’s type is not a string, then you should use

putIfNotEmptyAndApply – Same as putIfNotEmpty, but there is an additional method reference parameter. Before putting the filter into the map, this function applies the parser function.

putIfConditionAndApply – Of course ifNotEmpty is not the only condition that you want to implement. You can implement your own conditions in the CoucbaseFilterConditions class.

putCustom – Sometimes it’s not trivial whether or not you should put the filter. For complicated situations, there is the putCustom function. You can implement it with more parameters if you want. The last parameter is the most important, that is a method’s reference which contains the custom logic that you want to apply. Java8 has only Function and BiFunction functional interfaces defined, but if you want a TriFunction or so on, then you can easily define it by looking at Function and BiFunction’s source code.

As you see there is a CouchbaseFilterEntry class that helps the putCustom function. It returns the key and the value of a filter, and contains information about whether the filter is empty. Only not empty filter entries will be put in the filter.

public class CouchbaseFilterEntry {
    public String key;
    public Object value;
    private boolean isDefined = false;
 
    public CouchbaseFilterEntry(String key, Object value) {
        this.key = key;
        this.value = value;
        this.isDefined = true;
    }
 
    private CouchbaseFilterEntry() {
        this.isDefined = false;
    }
 
    public static CouchbaseFilterEntry empty() {
        return new CouchbaseFilterEntry();
    }
 
    public boolean isEmpty() {
        return isDefined;
    }
}

Okay so now we have a ton of util classes implemented, but why is it good? Because you don’t have to copy-paste a lot of code. Let’s compare them a bit!

private JsonObject filtersFromRequest(HttpServletRequest request) {
    JsonObject filters = defaultFilters();
 
    String taskFrequencyTypeEnum = request.getParameter("taskFrequencyTypeEnum");
    if(!StringUtils.isEmpty(taskFrequencyTypeEnum)) {
        filters.put("taskFrequencyTypeEnum", taskFrequencyTypeEnum);
    }
 
    String isImportantString = request.getParameter("isImportant");
    if(!StringUtils.isEmpty(isImportantString)) {
        filters.put("isImportant", Boolean.parseBoolean(isImportantString));
    }
 
    String title = request.getParameter("title");
    if(!StringUtils.isEmpty(title)) {
        filters.put("title" + CouchbaseQueryExecutor.CONTAINS_FILTER, title);
    }
 
    String deadlineFrom = request.getParameter("deadlineFrom");
    if(!StringUtils.isEmpty(deadlineFrom)) {
        filters.put("deadlineTime" + CouchbaseQueryExecutor.FROM_FILTER, Long.parseLong(deadlineFrom));
    }
 
    String deadlineTo = request.getParameter("deadlineTo");
    if(!StringUtils.isEmpty(deadlineTo)) {
        Date filterDate = new Date(Long.parseLong(deadlineTo));
        filters.put("deadlineTime" + CouchbaseQueryExecutor.TO_FILTER, DateUtils.makeItTheEndOfTheDay(filterDate).getTime());
    }
 
    String userId = request.getParameter("userId");
    if(!StringUtils.isEmpty(userId) && !"-1".equals(userId)) {
        filters.put("userId", Long.parseLong(userId));
    }
 
    String status = request.getParameter("status");
    String selectedTab = request.getParameter("selectedTab");
    if(StringUtils.isEmpty(selectedTab) || (!selectedTab.equals("assignedTasks") && !selectedTab.equals("finishedTasks"))) {
        selectedTab = "assignedTasks";
    }
    if(StringUtils.isNotEmpty(status)) {
        filters.put("taskStatusEnum", status);
    } else {
        if(selectedTab.equals("assignedTasks")) {
            filters.put("taskStatusEnum" + CouchbaseQueryExecutor.NOT_FILTER, TaskStatusEnum.FINISHED.name());
        } else if(selectedTab.equals("finishedTasks")) {
            filters.put("taskStatusEnum", TaskStatusEnum.FINISHED.name());
        }
    }
 
    return filters;
}
private JsonObject filtersFromParams(Map<String, String> params) {
    CouchbaseFilter filters = defaultFilters();
 
    filters.putIfNotEmpty(TASK_FREQUENCY_TYPE_FILTER, params.get(TASK_FREQUENCY_TYPE_ENUM_REQUEST_PARAM));
    filters.putIfNotEmpty(TITLE_CONTAINS_FILTER, params.get(TITLE_REQUEST_PARAM));
 
    filters.putIfNotEmptyAndApply(IS_IMPORTANT_FILTER, params.get(IS_IMPORTANT_REQUEST_PARAM), Boolean::parseBoolean);
    filters.putIfNotEmptyAndApply(DEADLINE_TIME_FROM_FILTER, params.get(DEADLINE_FROM_REQUEST_PARAM), Long::parseLong);
    filters.putIfNotEmptyAndApply(DEADLINE_TIME_TO_FILTER, params.get(DEADLINE_TO_REQUEST_PARAM), DateUtils::makeTimeStampTheEndOfTheDay);
 
    filters.putIfConditionAndApply(USER_ID_FILTER, params.get(USER_ID_REQUEST_PARAM), CouchbaseFilterConditions::isPositive, Long::parseLong);
 
    filters.putCustom(params.get(STATUS_REQUEST_PARAM), params.get(SELECTED_TAB_REQUEST_PARAM), this::statusFilterLogic);
 
    return filters.toJsonObject();
}
  
private CouchbaseFilterEntry statusFilterLogic(String status, String selectedTab) {
    if(StringUtils.isEmpty(selectedTab) || (!selectedTab.equals(ASSIGNED_TASKS_TAB_VALUE) && !selectedTab.equals(FINISHED_TASKS_TAB_VALUE))) {
        selectedTab = ASSIGNED_TASKS_TAB_VALUE;
    }
    if(StringUtils.isNotEmpty(status)) {
        return new CouchbaseFilterEntry(TASK_STATUS_ENUM_FILTER, status);
    } else {
        switch(selectedTab) {
            case ASSIGNED_TASKS_TAB_VALUE:
                return new CouchbaseFilterEntry(TASK_STATUS_ENUM_FILTER + CouchbaseQueryExecutor.IN_FILTER, availableStatusFilter());
            case FINISHED_TASKS_TAB_VALUE:
                return new CouchbaseFilterEntry(TASK_STATUS_ENUM_FILTER, TaskStatusEnum.FINISHED.name());
            default:
                return CouchbaseFilterEntry.empty();
        }
    }
}

The defaultFilters function returns a Map of the filters that every query must contain, for example if you have organizations in your app, and you don’t want them to see each other’s data, then a default filter can be the currently logged in user’s organizationId.

The two code snippets above are doing exactly the same. The string parameters were extracted to constant variables. Ignore that a little bit and take a look at the number of lines. 51 vs 34. That means 33% of code has disappeared. And what if you want to add a new parameter? You don’t have to copy-paste the line getting the value from the parameter, only that one line of code. The if condition checks whether you should put the param as a filter. I hope you like it too (smile) Using this is pretty simple.

@Service
public class UserTaskService {
 
    private final Logger log = LoggerFactory.getLogger(UserTaskService.class);
 
    @Inject
    private UserTaskRepository userTaskRepository;
 
    @Inject
    private UserService userService;
 
    private static final String TASK_FREQUENCY_TYPE_ENUM_REQUEST_PARAM = "taskFrequencyTypeEnum";
    private static final String TITLE_REQUEST_PARAM = "title";
    private static final String IS_IMPORTANT_REQUEST_PARAM = "isImportant";
    private static final String DEADLINE_FROM_REQUEST_PARAM = "deadlineFrom";
    private static final String DEADLINE_TO_REQUEST_PARAM = "deadlineTo";
    private static final String USER_ID_REQUEST_PARAM = "userId";
    private static final String STATUS_REQUEST_PARAM = "status";
    private static final String SELECTED_TAB_REQUEST_PARAM = "selectedTab";
 
    private static final String TASK_FREQUENCY_TYPE_FILTER = "taskFrequencyTypeEnum";
    private static final String TITLE_CONTAINS_FILTER = "title" + CouchbaseQueryExecutor.CONTAINS_FILTER;
    private static final String IS_IMPORTANT_FILTER = "isImportant";
    private static final String DEADLINE_TIME_FROM_FILTER = "deadlineTime" + CouchbaseQueryExecutor.FROM_FILTER;
    private static final String DEADLINE_TIME_TO_FILTER = "deadlineTime" + CouchbaseQueryExecutor.TO_FILTER;
    private static final String USER_ID_FILTER = "userId";
    private static final String TASK_STATUS_ENUM_FILTER = "taskStatusEnum";
    private static final String TENANT_ID_FILTER = "tenantId";
 
    private static final String ASSIGNED_TASKS_TAB_VALUE = "assignedTasks";
    private static final String FINISHED_TASKS_TAB_VALUE = "finishedTasks";
 
    private static JsonArray availableStatusFilter() {
        return JsonArray.from(
            TaskStatusEnum.AVAILABLE.name(),
            TaskStatusEnum.SELECTED.name(),
            TaskStatusEnum.DISCARDED.name()
        );
    }
 
    private CouchbaseFilter defaultFilters() {
        CouchbaseFilter filters = new CouchbaseFilter();
        User currentUser = userService.getCurrentUser();
 
        filters.put(TENANT_ID_FILTER, currentUser.getSelectedTenant().getId());
 
        return filters;
    }
 
    private JsonObject filtersFromParams(Map<String, String> params) {
        ...
    }
 
    private CouchbaseFilterEntry statusFilterLogic(String status, String selectedTab) {
        ...
    }
 
    public CouchbasePage<UserTask> getUserTasks(Map<String, String> params, Pageable pageable) {
        return userTaskRepository.getUserTasks(filtersFromParams(params), pageable);
    }
 
    public List<UserTask> getUserTasks(Map<String, String> params) {
        return userTaskRepository.getUserTasks(filtersFromParams(params));
    }
}

I’ll talk later about CouchbaseQueryExecutor.CONTAINS_FILTER, FROM_FILTER, TO_FILTER and others

Repository

Couchbase stores documents. To filter only UserTasks in a query, I recommend you to add a type variable to every Couchbase document, for example in a common superclass. After you have that field, the Repository shall add that filter to the filters, so the query will only return the specified documents.

@Repository
public class UserTaskRepository {
 
    @Inject
    private CouchbaseQueryExecutor couchbaseQueryExecutor;
 
    private void addUserTaskTypeFilterTo(JsonObject filters) {
        filters.put(ApplicationConstants.SyncGatewayConstants.KEY_TYPE, ApplicationConstants.SyncGatewayConstants.TYPE_USER_TASK);
    }
 
    public CouchbasePage<UserTask> getUserTasks(JsonObject filters, Pageable pageable) {
        addUserTaskTypeFilterTo(filters);
        return couchbaseQueryExecutor.find(filters, pageable, UserTask.class);
    }
 
    public List<UserTask> getUserTasks(JsonObject filters) {
        addUserTaskTypeFilterTo(filters);
        return couchbaseQueryExecutor.find(filters, UserTask.class);
    }
}

The CouchbaseQueryExecutor

Now we have all our filters set up. We have params transformed from the query string to filters, we have default filters (if needed), we have type filter. All we need to do is compose the query, execute it, and we got our list of entities.

Earlier you saw the CouchbaseQueryExecutor.CONTAINS_FILTER for example. This is a postfix to the field name. The query executor will decide the kind of matching you want to run by looking at the postfixes. We use the following postfixes in our app, but if you want, you can define anything you want.

@Component
public class CouchbaseQueryExecutor {
 
    public static final String CONTAINS_FILTER = "_contains";
    public static final String FROM_FILTER = "_from";
    public static final String TO_FILTER = "_to";
    public static final String NOT_FILTER = "_not";
    public static final String IN_FILTER = "_in";
    private static final String IGNORE_CASE_ORDER = "_ignorecase";
 
    ...
 
    private Expression createExpression(String key) {
        String propertyKey = key;
 
        if(key.endsWith(CONTAINS_FILTER)) {
            propertyKey = key.substring(0, key.length() - CONTAINS_FILTER.length());
            return createContainsExpression(propertyKey, key);
        } else if(key.endsWith(FROM_FILTER)) {
            propertyKey = key.substring(0, key.length() - FROM_FILTER.length());
            return createGreaterThanOrEqualsExpression(propertyKey, key);
        } else if(key.endsWith(TO_FILTER)) {
            propertyKey = key.substring(0, key.length() - TO_FILTER.length());
            return createLessThanOrEqualsExpression(propertyKey, key);
        } else if(key.endsWith(NOT_FILTER)) {
            propertyKey = key.substring(0, key.length() - NOT_FILTER.length());
            return createNotEqualsExpression(propertyKey, key);
        } else if(key.endsWith(IN_FILTER)) {
            propertyKey = key.substring(0, key.length() - IN_FILTER.length());
            return createInExpression(propertyKey, key);
        } else {
            return createEqualsExpression(propertyKey, key);
        }
    }
  
    private Expression createInExpression(String propertyKey, String key) {
        return x(propertyKey).in("$" + key);
    }
 
    private Expression createGreaterThanOrEqualsExpression(String propertyKey, String key) {
        return x(propertyKey).gte("$" + key);
    }
 
    private Expression createLessThanOrEqualsExpression(String propertyKey, String key) {
        return x(propertyKey).lte("$" + key);
    }
 
    private Expression createEqualsExpression(String propertyKey, String key) {
        return x(propertyKey).eq("$" + key);
    }
 
    private Expression createNotEqualsExpression(String propertyKey, String key) {
        return x(propertyKey).ne("$" + key);
    }
 
    private Expression createContainsExpression(String propertyKey, String key) {
        return x("CONTAINS(LOWER(" + propertyKey + "), LOWER($" + key + "))");
    }
  
}

As you see the CouchbaseQueryExecutor’s createExpression checks if the key has any known postfix, and creates the expression accordingly. If none of the postfixes match, then it will be a simple EqualsExpression. After this we can build expressions from filters. Now we need to concat the expressions.

The “$” + key is the parameter placeholder. It is the key in the JsonObject so it contains the postfixes. Property key is the key without a postfix.

@Component
public class CouchbaseQueryExecutor {
    ...
    private Expression composeWhere(Expression bucketName, JsonObject params) {
        List<Expression> expressions = params.getNames()
            .stream()
            .map(this::createExpression)
            .collect(Collectors.toList());
 
        expressions.add(x("meta(" + bucketName + ").id NOT LIKE \"_sync:%\""));
 
        return expressions
            .stream()
            .reduce(Expression::and)
            .get();
    }
    ...
}

This function will concat the expressions and add a special expression that’s needed to query the Couchbase server through N1QL queries according to this stackoverflow question. Also according to this you CANNOT update/delete/insert with N1QL. It is only for querying the data. If you need to do any of those operations, use the SyncGateway API.

Okay so now we have the ‘where’ section of the query. The following code will compose the remaining part of the query for you.

@Component
public class CouchbaseQueryExecutor {
    ...
    private Statement createQueryStatement(JsonObject params, Pageable pageable) {
        Expression bucketName = i(couchbaseConfiguration.getBucketName());
        return selectWithMeta(bucketName)
            .from(bucketName)
            .where(composeWhere(bucketName, params))
            .orderBy(fromPageable(pageable))
            .limit(pageable.getPageSize())
            .offset(pageable.getOffset());
    }
  
 
 
    private FromPath selectWithMeta(Expression bucketName) {
        return select(bucketName + " as data, meta(" + bucketName + ").id AS id ");
    }
  
    private Sort[] fromPageable(Pageable pageable) {
        List<Sort> orderBy = new ArrayList<>();
        pageable.getSort().forEach(pageableOrder -> {
            switch(pageableOrder.getDirection()) {
                case ASC:
                    if(pageableOrder.getProperty().endsWith(IGNORE_CASE_ORDER)) {
                        String property = pageableOrder.getProperty().substring(0, pageableOrder.getProperty().length() - IGNORE_CASE_ORDER.length());
                        orderBy.add(Sort.asc(lowerCase(property)));
                    } else {
                        orderBy.add(Sort.asc(pageableOrder.getProperty()));
                    }
                    break;
                case DESC:
                    if(pageableOrder.getProperty().endsWith(IGNORE_CASE_ORDER)) {
                        String property = pageableOrder.getProperty().substring(0, pageableOrder.getProperty().length() - IGNORE_CASE_ORDER.length());
                        orderBy.add(Sort.desc(lowerCase(property)));
                    } else {
                        orderBy.add(Sort.desc(pageableOrder.getProperty()));
                    }
                    break;
            }
        });
        return orderBy.toArray(new Sort[orderBy.size()]);
    }
    ...
}

fromPageable is a function that will compose the order by section from the Spring Data Pageable object. Ordering has a postfix, too if you want to sort ignoring the letter case. If the client sends property_ignorecase sorting, then the CouchbaseQueryExecutor will order ignoring case… really… :D

You need the meta.id to filter out ids starting with ‘_sync:’

Limit and offset are trivial. if you have entities 1, 2, 3, 4, 5, offset is 1, limit is 2, then you’ll get 2, 3 only.

If you don’t want to make the response pageable the following createQueryStatement function will help:

@Component
public class CouchbaseQueryExecutor {
    ...
    private Statement createQueryStatement(JsonObject params) {
        Expression bucketName = i(couchbaseConfiguration.getBucketName());
        return selectWithMeta(bucketName)
            .from(bucketName)
            .where(composeWhere(bucketName, params));
    }
    ...
}

After all of this we have the queryStatement ready with the parameter placeholders, where the placeholder’s keys are the keys in the JsonObject. We have only 2 things left. The first is parametrizing the query, and the second is transforming the response to POJO:

@Component
public class CouchbaseQueryExecutor {
    ...
    public <T> CouchbasePage<T> find(JsonObject params, Pageable pageable, Class<T> clazz) {
        CouchbasePage<T> page = new CouchbasePage<>(pageable);
        CouchbaseTemplate template = createTemplate();
 
        Statement query = createQueryStatement(params, pageable);
        N1qlQuery queryWithParameter = N1qlQuery.parameterized(query, params);
 
        page.data = convertToDataList(template.findByN1QLProjection(queryWithParameter, LinkedHashMap.class), clazz);
        page.totalElements = count(params);
        page.calculateTotalPages();
 
        return page;
    }
 
    public <T> List<T> find(JsonObject params, Class<T> clazz) {
        CouchbaseTemplate template = createTemplate();
 
        Statement query = createQueryStatement(params);
        N1qlQuery queryWithParameter = N1qlQuery.parameterized(query, params);
 
        return convertToDataList(template.findByN1QLProjection(queryWithParameter, LinkedHashMap.class), clazz);
    }
 
    private <T> List<T> convertToDataList(List<LinkedHashMap> queriedList, Class<T> clazz) {
        return queriedList.stream()
            .map(hashMap -> {
                LinkedHashMap data = (LinkedHashMap) hashMap.get("data");
                data.put("_id", hashMap.get("id"));
                return objectMapper.convertValue(data, clazz);
            })
            .collect(Collectors.toList());
    }
 
    public Integer count(JsonObject params) {
        CouchbaseTemplate template = createTemplate();
 
        Statement query = createCountStatement(params);
        N1qlQuery queryWithParams = N1qlQuery.parameterized(query, params);
        LinkedHashMap countMap = ((LinkedHashMap) template.findByN1QLProjection(queryWithParams, Object.class).get(0));
 
        return ((Integer) countMap.get("count"));
    }
 
    private Statement createCountStatement(JsonObject params) {
        Expression bucketName = i(couchbaseConfiguration.getBucketName());
        return count(bucketName)
            .from(bucketName)
            .where(composeWhere(bucketName, params));
    }
 
    private FromPath count(Expression bucketName) {
        return select("count(*) as count, meta(" + bucketName + ").id AS id ");
    }
    ...
}

You can see the Paged and non-Paged type of the find query.

Why do we need the convertToDataList function? After you execute the query, you get a LinkedHashMap like this:

{
    id: id-of-the-entity
    data: data-without-id-but-can-be-converted-to-POJO
}

That function will take the id and put it into the data. After that you can convert it to POJO.

If you want to see the full code, or use CouchbaseQueryExecutor as a lib checkout TeamWanari’s github repo https://github.com/TeamWanari/couchbase-query-executor

Last thoughts

I hope my post was useful for you. If you want to be notified about any changes in the lib, follow the GitHub repo, or if you have any suggestions, please make an issue :)

member photo

His favorite technologies are AngularJS and Java 8. He's been at Wanari as a full stack developer for almost 3 years.

Latest post by Alex Sükein

Solutions for a filterable sortable pageable list in Spring