How can I fetch data or records by date column in Spring Boot?

user_15018228 0
Viewed 2100

Actually, I have Orders entity (table) having column and I want to filter the orders data according to createddatetime column. Basic scenario is, when a user select any date for example: createddate is 03-06-2021 and there are 3-4 records present on that date so it would be able to display total records present on that date from mysql database.

    @Builder.Default
    @Column(updatable=false)
    @JsonFormat(shape=JsonFormat.Shape.STRING,pattern="yyyy-MM-dd hh:mm:ss")
    private Date createddatetime=new Date();

I have created function in Orders JPA repositiry like this:

    List<Orders> findByCreateddatetimeOrderByCreateddatetimeDesc(Date createddate);

Below is my API controller code to fetch records on particular date. But on hitting this, I am getting no response (error response).

@RequestMapping(value = AkApiUrl.onedayorder, method = { RequestMethod.POST, RequestMethod.GET }, produces = {MediaType.APPLICATION_JSON_VALUE })
    public ResponseEntity<?> onedayorder(HttpServletRequest request, @RequestParam("limit") int limit, @RequestParam("createddate") String createddate ) {
        CustomResponse = ResponseFactory.getResponse(request);
        int min = limit - 1;
        int max = Constants.limitResult;
        try {
            List<Orders> order = null;
            List<Orders> totalorders = null;
            Pageable pageable = new PageRequest(min, max, Sort.Direction.ASC, "orderid");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Date onedayorder = sdf.parse(createddate);
            if(createddate != null && createddate != "") {
                order = ordersdao.findByCreateddatetimeOrderByCreateddatetimeDesc(onedayorder, pageable);
                totalorders = ordersdao.findByCreateddatetimeOrderByCreateddatetimeDesc(onedayorder);
            }else {
                order = ordersdao.findAllByStatus(1, pageable);
                totalorders = ordersdao.findAllByStatus(1);
            }
            if (order != null) {
                CustomResponse.setResponse(order);
                CustomResponse.setMaxlimit(Constants.limitResult);
                CustomResponse.setTotalorders(totalorders.size());
                CustomResponse.setStatus(CustomStatus.OK);
                CustomResponse.setStatusCode(CustomStatus.OK_CODE);
            }
        } catch (Exception e) {
            e.printStackTrace();
            CustomResponse.setResponse(null);
            CustomResponse.setStatus(CustomStatus.Error);
            CustomResponse.setStatusCode(CustomStatus.Error_CODE);
        }
        return new ResponseEntity<ResponseDao>(CustomResponse, HttpStatus.OK);
    }

I tried @Query to create custom query using like operator, but again faced type mismatch error. How can I fix this thing? Any suggestions would be appreciable and valuable.

Screenshot to show response

spring-data-jpa spring-boot jsp ajax java
2 Answers

It seems that the data is being persisted in a different format than what you are searching by. you can apply any of the following solutions:

1- make sure that the same date format is used

2- to ignore the time part of the date you can use greater that and pass two dates ,like (createdDate >= 2021-06-14 and createdDate < 2021-06-15)

3- split your search date input and use functions to search like this example

@Query("select e from Event e where year(e.eventDate) = ?1 and month(e.eventDate) = ?2")
List<Entity> getByYearAndMonth(int year, int month);
user_71790580

In your Database the date-pattern is "yyyy-MM-dd hh:mm:ss", so execute a range query like:

List<Orders> findByCreateddatetimeBetweenOrderByCreateddatetimeDesc(Date createddateBegin, Date createddateEnd);

and give the day, you want from midnight to midnight.

user_159362150
Related