Specify date format in global/column search when database column is date (Oracle)

[perfectime94]

First of all thanks you a lot for this amazing library. I recently faced an issue when users wanted to filter globally by a date, our entity have a LocalDate which we show to user as dd/MM/yyyy but if user input that format into global search input it doesnt return result, i did some debug and saw that the generated query convert the date column of database to varchar2 and i dont have control of nls_date_format of database, so our database was returning dd/MM/yy instead of dd/MM/yyyy (user input), would there be a way to alter the first criterias to be able to specify the date format in the cast in case the column is a date?

Sorry if i didnt explained well enough my issue

https://github.com/darrachequesne/spring-data-jpa-datatables/blob/1f843312954eba7db250a6d15254944c9cb4d6ec/src/main/java/org/springframework/data/jpa/datatables/GlobalFilter.java#L37

[darrachequesne]

Hi! I think a custom specification should do the trick:

class DateSpecification implements Specification<MyEntity> {
  private final LocalDate value;

  DateSpecification(Column column) {
    String value = column.getSearch().getValue();
    column.setSearchable(false); // either here or in the table definition
    this.value = parseValue(value);
  }

  private LocalDate parseValue(String value) {
    if (hasText(value)) {
      try {
        return LocalDate.parse(value);
      } catch (DateTimeParseException e) {
        return null;
      }
    }
    return null;
  }

  @Override
  public Predicate toPredicate(Root<MyEntity> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
    Expression<LocalDate> expr = root.get("myColumn").as(LocalDate.class);
    if (this.value != null) {
      return criteriaBuilder.equal(expr, this.value);
    } else {
      return criteriaBuilder.conjunction();
    }
  }
}

And then:

@RestController
public class MyController {

  @RequestMapping(value = "/entities", method = RequestMethod.GET)
  public DataTablesOutput<MyEntity> list(@Valid DataTablesInput input) {
    return myRepository.findAll(input, new DateSpecification(input.getColumn("myField")));
  }
}

Reference: https://github.com/darrachequesne/spring-data-jpa-datatables/blob/main/README.md#specific-date

[perfectime94]

Hi, I tried that, but it doesn't work (or I did something wrong) what I found is that custom specification are added after the ones added by global search input made by GlobalFilter class.

What I did was set searchable: false in datatable initialization, then created the specification and pass it to findAll method but as I said early this specification is added in the second block of the generated query.

My datatable have only two columns, one is a string and the other one a Date, the generated query looked like this initially

SELECT COLUMN1, COLUMN2
FROM TABLE 
WHERE
(
  COLUMN1 CASTED TO VARCHAR2 LIKE FROM GLOBAL SEARCH VALUE
  OR
  COLUMN2(DATE) CASTED TO VARCHAR2 LIKE FROM GLOBAL SEARCH VALUE
)
AND
(
  CUSTOM SPECIFICATION
)

So when i did what you suggested i removed the second like in the first block of query and added on the second one but since the first block of where is only 1 column and none match a date as string pattern (dd/MM/yyyy) the count query return 0 so my specification dont do anything

[darrachequesne]

Oh, I see, so the date in the global search field, right? In that case, you'll need to clear the global search value, and only apply the specification:

@RestController
public class MyController {

  @RequestMapping(value = "/entities", method = RequestMethod.GET)
  public DataTablesOutput<MyEntity> list(@Valid DataTablesInput input) {
    LocalDate date = parseDate(input.getSearch().getValue());

    if (date != null) {
      input.getSearch().setValue("");

      return myRepository.findAll(input, (root, query, criteriaBuilder) -> {
        return criteriaBuilder.equal(root.get("myColumn").as(LocalDate.class), date);
      });
    }

    return myRepository.findAll(input);
  }

  private LocalDate parseDate(String value) {
    try {
      return LocalDate.parse(value);
    } catch (DateTimeParseException e) {
      return null;
    }
  }
}

[perfectime94]

Hi, yeah thats a posibility but then you lost the purpose of global search, i ended up making a query to change the nls_date_format previous to the findAll query so the cast to varchar have the required format, i looked at the object sent by datatable frontend ajax and it doesnt contain the column type, so the library cant know the type of specific column to address this issue