CODEWITHSUNDEEP
postgresqlspring-bootjpahibernate-criteria
Ullas Sharma
Ullas Sharma

Reputation: 450

Criteria Query for Date Filtering with Predicates

I'm trying to filter list of AudtiTrial (entity class) based on attributes. i'm using Criteria Builder for my class called Audit.java having the below attributes.

  @Column(name = "region")
  private String region;

  @Column(name = "module")
  private String module;

  @Column(name = "created_date", columnDefinition = "timestamp with time zone")
  @Temporal(TemporalType.TIMESTAMP)
  private Date createdDate;

after that using criteriaQuery for the Root class i.e Audit.java below is the code. in service class for filtering based on Predicates.

CriteriaQuery<AuditTrail> criteriaQuery = cb.createQuery(AuditTrail.class);
    Root<AuditTrail> from = criteriaQuery.from(AuditTrail.class);

    List<Predicate> predicateList = getFilterPRedicates(cb, from, auditTrial);

    Predicate[] activePredicates = predicateList.toArray(new Predicate[predicateList.size()]);
    Predicate filter = cb.and(activePredicates);

    criteriaQuery.where(filter);

    CriteriaQuery<AuditTrail> select = criteriaQuery.select(from);

    return entityManager.createQuery(select).getResultList();

and my helper function getFilterPRedicatesis as follows

public List<Predicates> getFilterPRedicates(AuditTrial auditTrial){
    if (auditTrial.getModifiedDate() != null && !auditTrial.getModifiedDate().toString().equals("")) {
          Predicate modifieddate = cb.equal(from.<Date>get("modifiedDate"), auditTrial.getModifiedDate());
          predicateList.add(modifieddate);
        }
return predicateList;
}

i'm calling the service from my controller class which accepts RequestParameters as Module, Region and createdDate. which inturn call service and returns the filtered data.

@GetMapping("/audit/filters")
  public AuditResponseObject getAudits(
      @RequestParam(value = "modifieddate", required = false, defaultValue = "")@DateTimeFormat(pattern="yyyy-MM-dd") Date  createdDate,
      @RequestParam(value = "module", required = false, defaultValue = "") String module,
      @RequestParam(value = "region", required = false, defaultValue = "") String region)
      {
       AuditTrail auditInfo = new AuditTrail();
     if (createddate != null && !createddate.toString().equals("")) {
      auditInfo.setCreatedDate(formatter.parse(formatter.format(createddate)));
    }
    auditInfo.setModule(module);
    auditInfo.setRegion(region);
    List<AuditTrail> auditList = auditTrailService.findByFilters(auditInfo);

    }

the dateformatter using is 

SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss aa");

The Data stored in Postgresql is Timestamp with timezone. and it's in format of

2019-06-11 17:57:38+05:30

Filtering is Working fine for Region and Module but it's not working for the createdDate. when i tried to hit API with createddate = 2019-06-11T12:27:38.000+0000 i.e CURL is below localhost:8080/api/audit/filters?createddate=2019-06-11T12%3A27%3A38.000%2B0000

log shows something like below

binding parameter [1] as [TIMESTAMP] - [Tue Jun 11 00:00:00 IST 2019]

but the entry for above given date is present in Database.So Filtering is not happening as expected. where i'm wrong please help? So it bassically should ignore the time portion of the value passed (T12:27:38.000+0000) in the url and should compare only date 2019-06-11 with the data present in Database. Any Help how to achieve it?

Any suggestions and corrections are welcomed. Thanks in advance.

Upvotes: 1

Views: 3425

Answers (1)

Johna
Johna

Reputation: 1894

The problem seems to be in your controller method's signature. You have @DateTimeFormat(pattern="yyyy-MM-dd") Date createdDate. So it bassically ignores the time portion of the value you pass (T12:27:38.000+0000) in the url.

You can try 'DateTimeFormat.ISO.DATE_TIME' like below.

@DateTimeFormat(iso ="DateTimeFormat.ISO.DATE_TIME") Date  createdDate

Upvotes: 1

Related Questions