CODEWITHSUNDEEP
javaspringhibernatespring-mvcspring-data-jpa
Maciaz
Maciaz

Reputation: 1224

How to filter database records by month and year if they have LocalDate attribute inside?

I have a Workday object in my database. I would like to display the amount of hours (hoursWorked) an employee had worked per particular day on a Spring web app.

In order to do this, I need to filter the Workdays by date and that is month and year only. Then display them on kind of a calendar for the specific period chosen (i.e. 2018-01).

What is the best practice to do this? Have 2 dropdown lists on the web app. First to choose a month, second for a year. Then convert both strings to LocalDate? I'm quiet new to Spring and couldn't find a proper solution. 

public class Workday {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

private Integer hoursWorked;
private Integer advancePayment;
private LocalDate date;

@ManyToOne
private Employee employee;

---------------

So, this might be a little hard. I do have 4 HTML views:

I do have an EmployeeController that handles rendering all the views as well as handling the new Employee creation.

@Controller
public class EmployeeController {

private final EmployeeService employeeService;
private final WorkdayService workdayService;

public EmployeeController(EmployeeService employeeService, WorkdayService workdayService) {
    this.employeeService = employeeService;
    this.workdayService = workdayService;
}

//Renders Employees view. There's a list of employees with some details.
@RequestMapping("/employees")
public String showEmployees(Model model) {

    model.addAttribute("employees", employeeService.getEmployees());

    return "employees";
}

//Renders a specific Employee view that contains a specific Employee information. 
//Like name, address etc. There's also a list of hours worked a day that I want to filter.
//This view includes the <input type="month"> to actually get the yyyy-mm date required for filtering.

@RequestMapping("/employee/{id}")
public String showSpecificEmployee(@PathVariable String id, Model model){

    model.addAttribute("employee", employeeService.findEmployeeById(new Long(id)));
//Newly created date command object to retrieve the date String from the form
    model.addAttribute("date", new DateCommand());

    return "specificEmployee";
}

//A method rendering the new Employee forms and binding that Employee command object to it.
@GetMapping
@RequestMapping("/new_employee")
public String newEmployee(Model model){

    model.addAttribute("employee", new EmployeeCommand());

    return "newEmployee";
}

//Saving the Employee command object with forms information to the database and rendering the specific employee view with the new employee.
@PostMapping
@RequestMapping("employee")
public String saveOrEdit(@ModelAttribute EmployeeCommand commandObject){

    EmployeeCommand savedCommand = employeeService.saveEmployeeCommand(commandObject);

    return "redirect:/employee/" + savedCommand.getId();
}

//New employee view is also used for editing employees.
@GetMapping
@RequestMapping("employee/{id}/edit")
public String editEmployee(@PathVariable String id, Model model){

    model.addAttribute("employee", employeeService.findCommandById(new Long(id)));

    return  "newEmployee";
}

//A method used for deleting employees. Returns employees view.
@GetMapping
@RequestMapping("employee/{id}/delete")
public String deleteEmployee(@PathVariable String id, Model model){

    employeeService.deleteEmployee(new Long(id));
    model.addAttribute("employees", employeeService.getEmployees());

    return "employees";
}

//That's the one I need to figure out. Since the Date I want to get is on the specificEmployee view,  I completly do not know what to return and what @RequestMapping to use.
@PostMapping
@RequestMapping("/employee/{id}")
public String updateWorkmonth(@ModelAttribute String date){

    String data = date;
    System.out.println(data);

    return "redirect://employee/{id}";
}

}

And the HTML form code. Yet again I do not know where to th:action should direct to.

<form th:object="${date}" th:action="@{}">
         Choose a month:
         <input type="month" th:field="*{actualDate}">
         <input type="submit">
</form>

If you need anything else, feel free to ask. I may provide a github repo as well.

Upvotes: 0

Views: 2288

Answers (1)

locus2k
locus2k

Reputation: 2935

In your form you would want something where the user can input a start date and an end date. Those two dates will then be passed down to your controller.

In your get request you could do something like /employees?startDate=20180212&endDate=20180214

Then for your Controller:

@RequestMapping("/employees")
public String showEmployees(Model model, @RequestParam(value="startDate") String startDate, @RequestParam(value="endDate") String endDate) {
    SimpleDateFormat sdf = new SimpleDateFormat("YYYYMMDD");
    Date start = sdf.parse(startDate);
    Date end = sdf.parse(endDate);
    model.addAttribute("employees", employeeService.getEmployees(start, end));

    return "employees";
}

Then have logic to test to see if startDate and/or endDate are null before the parse and handle accordingly.

Then you can create a Spring query such as

List<Workday> findByDateBetween(Date date1, Date date2);

This will return a list of workdays that fall between the two dates.

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.query-creation

Upvotes: 1

Related Questions