CODEWITHSUNDEEP
javaspringspring-bootspring-data-jpa
Allwin
Allwin

Reputation: 75

Filter the query result only and not the entire table

I am new to spring and I am trying to do something like this. Let us say my table has the following columns.

  1. task
  2. is_completed
  3. completd_at

The user will provide the following options in the query parameters.

  1. is_completed=true or false
  2. from_date = dd-mm-yyyy
  3. to_date = dd-mm-yyyy

I will check for each parameter one by one and then filter the table.

In Django, I can do something like this


tasks = Task.object.all() # All the tasks will be stored in tasks
tasks = tasks.filter(is_completed=True)  # completed tasks will be filtered from all tasks
tasks = tasks.filter(completed_at__gte=from_date, completed_date__lte=to_date)  # completed tasks will be filtered based on the completed date

How can I achieve this with spring JPA. Is there any way I can save the filtered results and query the filtered results again instead of querying the entire database?

That way I can check if the parameter has any value and check it like this

if (is_completed = True){
 // filter completed tasks
}

if (from_date ){
// filter completed tasks that are completed from or after this date
}

if (to_date ){
// filter completed tasks that are completed till this date
}

The problem with the current approach is I have to write SQL queries for each combination. This becomes complex when there are multiple parameters.

Upvotes: 0

Views: 198

Answers (1)

VaibS
VaibS

Reputation: 1893

Lets consider this is your Task class.

class Task {
    private String id;
    private boolean isCompleted;
    private LocalDate isCompletedAt;

    public Task(String id, boolean isCompleted, LocalDate isCompletedAt) {
        this.id = id;
        this.isCompleted = isCompleted;
        this.isCompletedAt = isCompletedAt;
    }

    public boolean isCompleted() {
        return isCompleted;
    }

    public LocalDate getCompletedAt() {
        return isCompletedAt;
    }

    @Override
    public String toString() {
        return "Task{" +
            "id='" + id + '\'' +
            ", isCompleted=" + isCompleted +
            ", isCompletedAt=" + isCompletedAt +
            '}';
    }
}

Below is the code using which you can filter the data:

class TaskFilter {

    public static void main(String[] args) {

        // Sample user input
        boolean isCompleted = true;
        LocalDate fromDate = LocalDate.parse("2020-11-04");
        LocalDate toDate = LocalDate.parse("2021-11-06");

        // Simulate data retrieved from JPA repository eg repository.findAll()
        List<Task> tasks = List.of(
            new Task("1", true, LocalDate.parse("2020-10-04")),
            new Task("2", false, LocalDate.parse("2010-12-02")),
            new Task("3", false, LocalDate.parse("2021-04-24")),
            new Task("4", true, LocalDate.parse("2021-03-12"))
        );

        // Create a stream on retrieved data
        Stream<Task> tasksStream = tasks.stream();

        // Filter that stream based on user input
        if(isCompleted) {
            tasksStream = tasksStream.filter(task -> task.isCompleted());
        }
        if(fromDate != null) {
            tasksStream = tasksStream.filter(task -> task.getCompletedAt().isAfter(fromDate));
        }
        if(toDate != null) {
            tasksStream = tasksStream.filter(task -> task.getCompletedAt().isBefore(toDate));
        }

        // Finally collect that in a list. This is a must operation, because stream is not executed unless terminal operation is called.
        List<Task> filteredTaskList = tasksStream.collect(Collectors.toList());

        System.out.println(filteredTaskList);
    }
}

Upvotes: 1

Related Questions