Check date between two other dates spring data jpa

Question

1.00/5 (1 vote)

See more:

I am writing a search function from date to date. But when I click search it doesn't return any results and it doesn't give me any error.

file handover.html

HTML

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
	<meta charset="UTF-8">
	<meta http-equiv="X-UA-Compatible" content="IE=edge" />
	<meta name="_csrf" th:content="${_csrf.token}">
	<meta name="_csrf_header" th:content="${_csrf.headerName}">
	<title>HandOver</title>
	<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css"
          rel="stylesheet"
          integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC"
          crossorigin="anonymous">
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/flatpickr"></script>
	<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/flatpickr/dist/flatpickr.min.css">
</head>
<body>
	...
	<br /><br />

	<div class="container">
	    <div class="row">
	        <h1 class="text-center">Day shift and Night shift Handover</h1>
	    </div>
	    <br>
		<div class="row">
			<div class="col-lg-3">
				<a th:href = "@{/handovers/handover/new}" class="btn btn-primary btn-sm mb-3">Add New</a>
				<button class="btn btn-success btn-sm mb-3" onclick="exportToExcel()">Export to Excel</button>
			</div>
			<div class="col-lg-3">
			    <input type="text" id="fromDate" class="form-control datepicker" placeholder="From Date">
			</div>
			<div class="col-lg-3">
			    <input type="text" id="toDate" class="form-control datepicker" placeholder="To Date">
			</div>
			<div class="col-lg-3">
			    <button id="searchButton" class="btn btn-primary">Search</button>
			</div>
		</div>
		<table class="table table-striped table-bordered">
			<thead class="table-dark">
				<tr>
					<th><input type="checkbox" id="selectAll" /></th>
					<th>Date</th>
					<th>Type</th>
					<th>What</th>
					<th>Where</th>
					<th>Who</th>
					<th>Time</th>
					<th>When</th>
					<th>Due Date</th>
					<th>Status</th>
					<th>Others</th>
					<th>Files</th>
					<th>Actions</th>
				</tr>
			</thead>
			<tbody id="handoverTableBody">
				<tr th:each="handover : ${handover}">
					<td><input type="checkbox" th:value="${handover.id}" class="checkbox" /></td>
					<td th:text="${#dates.format(handover.date, 'yyyy-MM-dd')}"></td>
					<td th:text="${handover.type}"></td>
					<td th:text="${handover.what}"></td>
					<td th:text="${handover.where}"></td>
					<td th:text="${handover.who}"></td>
					<td th:text="${handover.times}"></td>
					<td th:text="${handover.when}"></td>
					<td th:text="${handover.dueDate}"></td>
					<td th:text="${handover.status}"></td>
					<td th:text="${handover.others}"></td>
					<td>
					    <span th:if="${handover.filePath != null and handover.filePath.trim().length() > 0}">
					        <a href="#" class="btn btn-info btn-sm" th:data-id="${handover.id}" th:data-filepaths="${handover.filePath}" onclick="downloadFile(this)">Download</a>
					    </span>
					    <span th:unless="${handover.filePath != null and handover.filePath.trim().length() > 0}">
					        No file
					    </span>
					</td>
					<td>
						<a th:href="@{/handovers/handover/edit/{id}(id=${handover.id})}" class="btn btn-primary btn-sm">Update</a>
						
						<a th:href="@{/handovers/handover/{id}(id=${handover.id})}" class="btn btn-danger btn-sm" th:if="${#authorization.expression('hasAuthority(''ROLE_ADMIN'')')}" onclick="confirmDelete(event)">Delete</a>
					</td>
				</tr>
			</tbody>
		</table>
	</div>
	
	<script src="https://unpkg.com/xlsx@0.14.1/dist/xlsx.full.min.js"></script>
	<script>
	    // Xử lý sự kiện khi checkbox selectAll thay đổi
	    document.getElementById('selectAll').addEventListener('change', function() {
	        var checkboxes = document.getElementsByClassName('checkbox');
	        for (var i = 0; i < checkboxes.length; i++) {
	            checkboxes[i].checked = this.checked;
	        }
	    });
	    
	    //export to excel
		...
		
		//download file upload 
		...
		
		// search for date to date 
		$(document).ready(function() {
		    // Khởi tạo datepicker cho ô ngày bắt đầu và ô ngày kết thúc
		    $(".datepicker").flatpickr();
		    
		    // Xử lý sự kiện khi nút tìm kiếm được nhấn
		    $("#searchButton").click(function() {
		        var fromDate = $("#fromDate").val();
		        var toDate = $("#toDate").val();

		        // Gửi yêu cầu Ajax để lấy dữ liệu thông tin trong khoảng thời gian từ ngày này đến ngày kia
		        $.ajax({
		            url: "/handovers/search",
		            type: "GET",
		            data: { fromDate: fromDate, toDate: toDate },
		            success: function(data) {
		            	console.log(data);
		                // Cập nhật nội dung bảng dữ liệu với kết quả tìm kiếm
		                $("#handoverTableBody").html(data);
		    
		            },
		            error: function() {
		                alert("Đã xảy ra lỗi khi tìm kiếm.");
		            }
		        });
		    });
		});
		
		
		//confirm delete
		function confirmDelete(event) {
			event.preventDefault();
			var deleteUrl = event.currentTarget.getAttribute("href");
			
			if(confirm("Are you sure you want to delete this item?")) {
				window.location.href = deleteUrl;
			}
		}
	</script>
</body>
</html>

code controller:

Java

//search for date -> date
	@GetMapping("/handovers/search")
	public String searchHandovers(@RequestParam("fromDate") String fromDate,
	                              @RequestParam("toDate") String toDate,
	                              Model model) {
		Date sqlFromDate = Date.valueOf(fromDate);
	    Date sqlToDate = Date.valueOf(toDate);
	  List<Handover> handovers = handoverService.getHandoversInRange(sqlFromDate, sqlToDate);
	  model.addAttribute("handover", handovers);
	  return "handovers/handover :: handoverTableBody";
	}

code HandoverRepository :

Java

package com.thang.repository;

import java.sql.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.thang.entity.Handover;

public interface HandoverRepository extends JpaRepository<Handover, Long>{
	@Query("SELECT handover FROM Handover handover WHERE handover.date >= :fromDate AND handover.date <= :toDate")
    List<Handover> getHandoversInRange(@Param("fromDate") Date fromDate, @Param("toDate") Date toDate);
}

code HandoverServiceImpl :

Java

@Override
	public List<Handover> getHandoversInRange(Date fromDate, Date toDate) {
		
		return handoverRepository.getHandoversInRange(fromDate, toDate);
	}

What I have tried:

I tried to log the value fromDate and toDate it shows correctly, but when printing the value of data it goes blank.I hope everyone can help me

Posted 13-Jul-23 17:31pm

Thang Đai

Updated 14-Jul-23 0:33am

Andre Oosthuizen

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andre Oosthuizen · Answer 1 · 2023-07-14T00:32:00

There are a few issues you can check that might cause a no return on your date request. I have added comments in your code where you can check. I have also added the 'System.out.println' to debug all returned parameters in your controller, check each item returned for it's value and confirm that it is what you expected. You should really start using debug statements in your code, it will make life so much easier.

In your controller -

Java

@GetMapping("/handovers/search")
public String searchHandovers(@RequestParam("fromDate") String fromDate,
                              @RequestParam("toDate") String toDate,
                              Model model) {
    System.out.println("Received fromDate: " + fromDate);
    System.out.println("Received toDate: " + toDate);

    //The format of the fromDate and toDate should be 'yyyy-MM-dd'. Check that the date format matches the format expected by Date.valueOf() method.
    Date sqlFromDate = Date.valueOf(fromDate);
    Date sqlToDate = Date.valueOf(toDate);
    System.out.println("Converted fromDate: " + sqlFromDate);
    System.out.println("Converted toDate: " + sqlToDate);

    //The repository method is expecting java.sql.Date, but your method is passing java.util.Date. Make sure the handoverService.getHandoversInRange() method is defined with java.sql.Date parameters.
    List<Handover> handovers = handoverService.getHandoversInRange(sqlFromDate, sqlToDate);
    System.out.println("Retrieved handovers: " + handovers);

    //The attribute name in your model should match the one used in the Thymeleaf template.
    model.addAttribute("handover", handovers);

    //The view name should match the fragment ID used in the Thymeleaf template, assuming handovers/handover is the view name and handoverTableBody is the fragment ID.
    return "handovers/handover :: handoverTableBody";
}

In your HandoverRepository -

Java

public interface HandoverRepository extends JpaRepository<Handover, Long> {
    //Make sure your attribute names in the query match the ones defined in the Handover entity. Check that 'handover.date' corresponds to the correct field name in the Handover entity.
    @Query("SELECT handover FROM Handover handover WHERE handover.date >= :fromDate AND handover.date <= :toDate")
    List<Handover> getHandoversInRange(@Param("fromDate") Date fromDate, @Param("toDate") Date toDate);
}

In your HandoverServiceImpl -

Java

@Override
public List<Handover> getHandoversInRange(Date fromDate, Date toDate) {
    //Make sure the method name matches the one in the interface, i.e., 'getHandoversInRange'.
    return handoverRepository.getHandoversInRange(fromDate, toDate);
}